Python sqlite3 using the detailed

The Python SQLite database is a very small embedded open source database software, which means that there is no independent maintenance process, and all maintenance comes from the program itself. It uses a file to store the entire database, which is very convenient to operate. Its greatest advantage is the ease of use, function compared to other large databases, there are some gaps. But in performance, SQLite is not inferior. Perfectly formed, SQLite implements most sql-92 standards, such as transaction, trigger, and complex queries.

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, conn

Let's step into the SQLite in Python.

One, the Python SQLite database import module:

Import Sqlite3

Second, create the database/Open the 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 it does not exist, create a new one and open it again. This application is well understood.

Third, the database connection object:

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

Four, the use of Python SQLite database cursors:

cursors provide a flexible means of manipulating data retrieved from a table, in essence, . Cursors are always associated with a sql  SELECT statement. Because the 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 When you decide to process a result set, you must declare a cursor that points to the result set. If you have written a program that handles a file in C, then , and the file handle represents 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 relational database management system is essentially a set-oriented , there is no representation in SQLite that describes a single record in a table, unless you use the where  clause to restrict only one record from being selected. So we . 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; As a set-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 executemany--execute multiple SQL statements close ()--close cursor fetchone ()--Take a record from the result and point the cursor to the next record Fetchmany ()--take multiple records from the result Fetchall ()--Remove all records from the results scroll ()--cursor scrolling Use the Python SQLite database to do something about the database we built above:

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. Insert 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, query:
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, modify:
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.

Python sqlite3 use in detail