How to operate SQLite databases using Python

How to operate SQLite databases using Python

1. Import the Python SQLITE database module

After Python2.5, SQLite3 is built into the built-in module, which saves us the effort of installation, just import it ~

Import sqlite3

2. Create/open a database

When the connect function is called, specify the database name. If the specified database exists, open the database directly. If the database does not exist, create a new database and open it again.

Cx = sqlite3.connect ("E:/test. db ")

You can also create a database in the memory.

Con = sqlite3.connect (": memory :")

3. database connection object

The object cx returned when a database is opened 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

For commit (), if isolation_level is the default isolation level, this command is required for each database operation. You can also set isolation_level = None to change to the automatic submission mode.

How to operate SQLite databases using Python

4. query a database with a cursor

We need to use the cursor object SQL statement to query the database and obtain the query object. Use the following method to define a cursor.

Cu = cx. cursor ()

The cursor object has the following operations:

Execute () -- execute SQL statement execute.pdf -- execute multiple SQL statements close () -- close the cursor fetchone () -- retrieve a record from the result and direct the cursor to the next record fetchiterator () -- fetch multiple records from the results fetchall () -- retrieve all records from the results scroll () -- cursor scroll

1. Create a table

Cu.exe cute ("create table catalog (id integer primary key, pid integer, name varchar (10) UNIQUE, nickname text NULL )")

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, and a nickname is NULL by default.

2. Insert data

Avoid writing the following statements:

# Never do this -- insecure will cause injection attacks

Pid = 1, 200

C.exe cute ("... where pid = '% S'" % pid)

The correct method is as follows. If t is only a single value, t = (n,) should also be used because the tuples are immutable.

For t in [(, 'abc', 'yu '), (, 'ba', 'xu')]:

Cx.exe cute ("insert into catalog values (?,?,?,?) ", T)

Insert two rows of data, but note that the data takes effect only after the data is submitted. We use the database connection object cx to submit the commit and roll back the data.

Cx. commit ()

3. Query

Cu.exe cute ("select * from catalog ")

Use the fetch function of the cursor to extract the queried data, for example:

In [10]: cu. fetchall ()

Out [10]: [(0, 10, u 'abc', u 'yu '), (1, 20, u 'ba', u 'xu')]

If we use cu. fetchone (), first the first item in the list is returned, and then the second item is returned.

4. Modify

In [12]: cu.exe cute ("update catalog set name = 'boys' where id = 0 ")

In [13]: cx. commit ()

Note: Submit after data modification

5. Delete

Cu.exe cute ("delete from catalog where id = 1 ")

Cx. commit ()

6. Use Chinese

Make sure that your IDE or system is UTF-8 encoded by default and u is added before Chinese characters.

X = u'yu'

Cu.exe cute ("update catalog set name =? Where id = 0 ", x)

Cu.exe cute ("select * from catalog ")

Cu. fetchall ()

[(0, 10, U' \ u9c7c ', u'yu'), (1, 20, u'ba', u'xu ')]

To display the Chinese font, print each string in sequence.

In [26]: for item in cu. fetchall ():

...: For element in item:

...: Print element,

...: Print

....:

0 10 Yu

1 20 CBA Xu

7. Row Type

Row provides an index-based and name-sensitive method to access columns without memory overhead. The original article is as follows:

Sqlite3.Row provides both index-based and case-insensitive name-based access to columns with almost no memory overhead. it will probably be better than your own dicm dictionary-based approach or even a db_row based solution.

Detailed introduction to Row objects

Class sqlite3.Row

A Row instance serves as a highly optimized row_factory for Connection objects. It tries to mimic a tuple in most of its features.

It supports mapping access by column name and index, iteration, representation, equality testing and len ().

If two Row objects have exactly the same columns and their members are equal, they compare equal.

Changed in version 2.6: Added iteration and equality (hashability ).

Keys ()

This method returns a tuple of column names. Immediately after a query, it is the first member of each tuple in Cursor. description.

New in version 2.6.

The following is an example

In [30]: cx. row_factory = sqlite3.Row

In [31]: c = cx. cursor ()

In [32]: c.exe cute ('select * from catalog ')

Out [32]:

In [33]: r = c. fetchone ()

In [34]: type (r)

Out [34]:

In [35]: r

Out [35]:

In [36]: print r

(0, 10, U' \ u9c7c ', u'yu ')

In [37]: len (r)

Out [37]: 4

In [39]: r [2] # query using Indexes

Out [39]: U' \ u9c7c'

In [41]: r. keys ()

Out [41]: ['id', 'pid ', 'name', 'nickname']

In [42]: for e in r:

...: Print e,

....:

0 10 Yu

Query using column keywords

In [43]: r ['id']

Out [43]: 0

In [44]: r ['name']

Out [44]: U' \ u9c7c'