Python database Operations (SQLite)

About SQLite

Unlike the common client/server architecture paradigm, the SQLite engine is not a separate process for the program to communicate with, but rather a major part of connecting to a program. So the main communication protocol is the direct API call within the programming language. This has a positive effect on total consumption, delay time, and overall simplicity. The entire database (definitions, tables, indexes, and data itself) is stored in a single file on the host host. There are indeed some gaps compared to other large databases. But including transactions (transaction), which represent atomicity, consistency, isolation, and durability (ACID), triggers (trigger) and most complex queries are supported. In addition, the language binding is also very good: including php,python,c/c++ and so on.

For more information, see: Http://zh.wikipedia.org/wiki/SQLite

Using SQLite3 in Python

(The following information has been picked up to: http://anony3721.blog.163.com/blog/static/5119742010716104442536/)

Python's database module has a unified interface standard, so database operations have a unified pattern, basically the following steps (assuming the database module named DB):

　　1. Create a database connection with Db.connect, assuming the connection object is conn

2. If the database operation does not need to return results, it is directly used Conn.execute query, depending on the database transaction isolation level, may modify the database needs Conn.commit

　　3. If you need to return query results, use Conn.cursor to create the cursor object cur, cur.execute query the database, and return the query results with Cur.fetchall/cur.fetchone/cur.fetchmany. Depending on the database transaction isolation level, it is possible to modify the database to Conn.commit

4. Close Cur,close ()

A Python SQLite database import module:

Import Sqlite3

Two CREATE DATABASE/Open database:

CX = Sqlite3.connect ("e:/test.db") in fact, we do not need to explicitly create a SQLite database, when calling the Connect function, specify the library name, if the specified database exists directly open the database, if not exist Create a new one and open it again. This application is well understood.

Three Database connection objects:

The object that is returned when you open the database CX is a database connection object that can do the following:

Commit ()--Transaction commit

Rollback ()--Transaction rollback

Close ()--Close a database connection

Cursor ()--Create a cursor

Use of the four Python SQLite database cursors:

Cursors provide a flexible means of manipulating data retrieved from a table, essentially, a cursor is essentially a mechanism for extracting one record at a time from a result set that includes multiple data records. Cursors are always associated with an SQL selection statement. Because a cursor consists of a result set (which can be 0, one, or multiple records retrieved by a related selection statement) and a cursor position that points to a particular record in the result set. When you decide to process a result set, you must declare a cursor that points to the result set. If you have ever written a program that handles a file in C, the cursor is just like the file handle you get when you open the file, and the file handle can represent the file as long as the file opens successfully. For cursors, the rationale is the same. Visible cursors are capable of processing the result set from the underlying table in a manner similar to that of a traditional program reading a flat file, rendering the data in the table to the program as a flat file.
we know that the relational database management system is essentially set-oriented , and there is no expression in SQLite that describes a single record in a table unless a WHERE clause is used to restrict only one record from being selected. So we have to use cursors to handle single-record-oriented data processing. Thus, cursors allow the application to perform the same or different operations on each row of the rows result set returned by the query statement select, rather than doing the same operation on the entire result set at a time; it also provides the ability to delete or update data in the table based on the cursor position It is the cursor that is used as a collection-oriented database management system and the line-oriented programming of the two links, so that two data processing methods can communicate. The use of cursor cursors is highlighted below. In fact, all SQL statements are executed under the cursor object.

First, define a cursor:

CU = Cx.cursor () defines a cursor as such. The cursor object has the following actions:

Execute ()--Execute SQL statement

xecutemany--executing multiple SQL statements

Close ()--close cursor

Fetchone ()--Takes a record from the result and points the cursor to the next record

Fetchmany ()--take multiple records from the results

Fetchall ()--Remove all records from the results

Scroll ()--cursor scrolling

Here are some things you can do with the database we've built using the Python SQLite database:

1 Build Table:
Cu.execute (' CREATE TABLE catalog (ID integer primary key,pid integer,name varchar UNIQUE) ') The above statement creates a table called catalog, It has a primary key ID, a PID, and a name,name can not be duplicated.

2 Inserting data:
Cu.execute ("INSERT into catalog values (0, 0, ' name1 ')") Cu.execute ("INSERT into catalog values (1, 0, ' hello ')") simply insert two rows of data , but it should be recalled that only after submission can it take effect. We use the database connection object CX to commit commit and rollback rollback operations.
Cx.commit ()

3 Queries:
Cu.execute ("SELECT * from Catalog") to extract the queried data, use the cursor's fetch*** function, such as:
Print Cu.fetchall () returns the following results:
[(0, 0, u ' name1 '), (1, 0, u ' hello ')] if we use Cu.fetchone (), first return the first item in the list, use again, then return to the second item, and then go down.

4 Modifications:
Cu.execute ("Update Catalog set name= ' name2 ' where id = 0")

Cx.commit () Note that after you modify the data, commit

5 Delete:
Cu.execute ("Delete from catalog where id = 1") Cx.commit () above simple operations react to the basic points of the Python SQLite database operation, here donuts. Then, SQLite is powerful and not limited to this , its support for SQL advanced features and its small and flexible features make SQLite popular among developers in many fields.

Example:

Exemplary Exemplar 1

Import Sqlite3

con = sqlite3.connect (' d:/mydatabase.db3 ')
cur = con.cursor ()
Cur.execute (' CREATE TABLE foo (o_id INTEGER PRIMARY KEY, Fruit varchar (), veges varchar (30)) ')
Con.commit ()
Cur.execute (' INSERT into foo ' (o_id, fruit, veges) VALUES (NULL, "apple", "broccoli")
Con.commit ()
Print Cur.lastrowid

Cur.execute (' SELECT * from foo ')
Print Cur.fetchall ()

Exemplary Exemplar 2

#-*-encoding:gb2312-*-
Import Sqlite3

conn = Sqlite3.connect ("d:/aaa.db")
Conn.isolation_level = None #这个就是事务隔离级别, the default is to commit to modify the database, set to none automatically each time the modification is committed, otherwise ""
# Here's how to create a table
conn.execute ("CREATE table if not exists T1 (ID integer primary key autoincrement, name varchar (+), info varchar (12 8)) ")
# Insert Data
Conn.execute ("INSERT into T1 (name,info) VALUES (' Zhaowei ', ' only a Test ')")
# If the isolation level is not autocommit, you need to perform a commit manually
Conn.commit ()
# Gets the cursor object
cur = conn.cursor ()
# Query with cursors to get results
Cur.execute ("select * from T1")
# Get all results
res = Cur.fetchall ()
print ' row: ', Cur.rowcount
# cur.description is a description of the structure of the table
print ' desc ', cur.description
# The result returned with Fetchall is a two-dimensional list
For line in Res:
For F on line :
print F,
Print
print '-' *60

Cur.execute (" select * from T1 ")
# This query only takes one result, which is the one-dimensional list
res = Cur.fetchone ()
F in res:
print
res = Cur.fetchone ()

F in res:
print

Cur.close ()
Conn.close ()

Python database Operations (SQLite)