Python database programming SQLite

I. Installation
Download the installation package from the pysqlite homepage.
Pysqlite download http://code.google.com/p/pysqlite/downloads/list

2. Create a database/open a database
SQLite uses files as databases. You can specify the location of database files.

>>> Import sqlite3
>>> Cx = sqlite3.connect ("D:/test. DB ")
Cx = sqlite3.connect (': Memory:') # This is based on the memory.

You can use SQLite connect to create a database file. When the database file does not exist, it is automatically created. If the file already exists, open the file. CX is the database connection object.

III. Basic database objects
3.1 database connection object

Like the preceding CX is a database connection object, it can perform the following operations:

Commit () -- transaction commit
Rollback () -- transaction rollback
Close () -- close a database connection
Cursor () -- create a cursor

3.2 All SQL statements of the cursor object must be executed under the cursor object.

Cu = Cx. cursor () defines a cursor. The cursor object has the following operations:

Execute () -- execute an SQL statement
Executemany -- execute multiple SQL statements
Close () -- close the cursor
Fetchone () -- get a record from the result
Fetchrecords () -- retrieve multiple records from the results
Fetchall () -- retrieve multiple records from the result
Scroll () -- cursor scroll

For object methods, go to the python homepage to view detailed database API documentation. But what exactly does pysqlite support dB APIs?ProgramI don't know. All the operations I listed are supported, but I have not used them all.

Iv. Examples
4.1 database creation

We already have it. We will not repeat it. (If you are interested in these examples, you can try it directly in the python interaction environment)

4.2 create a table

>>> Cu = Cx. cursor ()
>>> Cu.exe cute ("" create table catalog (ID integer primary key, PID integer, name varchar (10) Unique )""")
The preceding statement creates a table named catalog, which has a primary key ID, a PID, and a name and name that cannot be repeated.

For the data types supported by SQLite, see version 2 datatypes?

4.3 insert)

>>> Cu.exe cute ("insert into catalog values (0, 0, 'name1 ')")
>>> Cu.exe cute ("insert into catalog values (1, 0, 'Hello ')")
>>> Cx. Commit ()
If you want to, you can always use the Cu cursor object. Note that the transaction statement commit () or rollback () must be used to modify the data, and the object is the database connection object. CX is used here.

4.4 select)

>>> Cu.exe cute ("select * From catalog ")
>>> Cu. fetchall ()
[(0, 0, 'name2'), (1, 0, 'Hello')]
Fetchall () returns all the data in the result set, and the result is a list of tuple. Each tuple element is arranged in the order of fields in the table creation. Note that a cursor is stateful and records the first few records of the result. Therefore, you can traverse the result set only once. In the preceding case, if fetchone () is executed, null is returned. Note This during testing.

>>> Cu.exe cute ("select * From catalog where id = 1 ")
>>> Cu. fetchone ()
(1, 0, 'Hello ')
If no statement is modified for the database, you do not need to execute the transaction statement after execution.

4.5 Update (modify)

>>> Cu.exe cute ("Update catalog set name = 'name2' where id = 0 ")
>>> Cx. Commit ()
>>> Cu.exe cute ("select * From catalog ")
>>> Cu. fetchone ()
(0, 0, 'name2') 4.6
Delete)

>>> cu.exe cute ("delete from catalog where id = 1")
>> Cx. commit ()
>> cu.exe cute ("select * From catalog")
>> Cu. fetchall ()
[(0, 0, 'name2')]