A detailed explanation of how Python operates the SQLite database

This article mainly introduced the Python operation SQLite database method, more detailed analysis of the Python installation of SQLite database module and for the SQLite database of common operating skills, the need for friends can refer to the following

The examples in this article describe how Python operates the SQLite database. Share to everyone for your reference, as follows:

Simple Introduction to SQLite

SQLite database is a very small embedded open source database software, that is, there is no independent maintenance process, all the maintenance is from the program itself. It is a relational database management system that adheres to acid, it is designed to be embedded, and has been used in many embedded products, it occupies very low resources, in the embedded device, may only need hundreds of K of memory is enough. It can support Windows/linux/unix and so on mainstream operating system, and can be combined with many programming languages, such as TCL, C #, PHP, Java, and ODBC interface, also compared to MySQL, PostgreSQL, the two open source world-renowned database management system, is processing faster than they do. The first alpha version of SQLite was born in May 2000. So far there have been 10 years, SQLite also ushered in a version of SQLite 3 has been released.

Installation and use

1. Import the Python SQLite database module

After the Python2.5, built-in SQLite3, has become a built-in module, which gives us to save the installation of Kung Fu, just import can ~

Import Sqlite3

2. Create/Open Database

When calling the Connect function, specify the name of the library, if the specified database exists, open the database directly, and if it does not exist, create a new one and open it.

CX = Sqlite3.connect ("e:/test.db")

You can also create a database in memory.

con = Sqlite3.connect (": Memory:")

3. 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

With respect to commit (), if the Isolation_level isolation level defaults, you will need to use the command for each operation of the database, and you can set isolation_level=none so that it becomes autocommit mode.

4. Querying the database with cursors

We need to query the database using the Cursor object SQL statement to get the query object. Define a cursor by using the following method.

Cu=cx.cursor ()

The cursor object has the following actions:

①execute ()--Execute SQL statement
②executemany--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

1. Build a table

Copy the Code code as follows:

Cu.execute ("CREATE TABLE catalog (ID integer primary key,pid integer,name varchar (ten) unique,nickname text NULL)")

The above statement creates a table called catalog, which has a primary key ID, a PID, and a name,name that cannot be duplicated, and a nickname default is null.

2. Inserting data

Be careful to avoid the following wording:

# never does this--insecure will cause an injection attack pid=200c.execute ("... where pid = '%s '"% pid)

The correct approach is to use the form t= (n,) if T is just a single value, because tuples are immutable.

For T-in[(0,10, ' abc ', ' Yu '), (1,20, ' CBA ', ' Xu ')]:  cx.execute ("INSERT into catalog values (?,?,?,?)", T)

Simply insert two rows of data, but you need to be reminded that it will not take effect until you submit it. We use the database connection object CX to commit commit and rollback rollback operations.

Cx.commit ()

3. Enquiry

Cu.execute ("SELECT * from Catalog")

To extract the queried data, use the FETCH function of the cursor, such as:

In [ten]: Cu.fetchall () out[10]: [(0, Ten, u ' abc ', U ' Yu '), (1, +, U ' CBA ', U ' Xu ')]

If we use Cu.fetchone (), we first return the first item in the list and use it again, then we return to the second item and go down.

4. Modifications

in [+]: Cu.execute ("Update Catalog set name= ' boy ' where id = 0") in []: Cx.commit ()

Note that after you modify the data, submit

5. Delete

Cu.execute ("Delete from catalog where id = 1") Cx.commit ()

6. Use Chinese

Please make sure your IDE or system default encoding is Utf-8, and add u before Chinese

X=u ' Fish ' cu.execute ("Update Catalog set name=? WHERE id = 0 ", x) Cu.execute (" SELECT * from Catalog ") Cu.fetchall () [(0, Ten, U ' \u9c7c ', U ' Yu '), (1, +, U ' CBA ', U ' Xu ')]

If you want to display a Chinese font, you need to print out each string in turn

in [+]: for item in Cu.fetchall ():  ....:   for element in item: ....  :     print element,  ....:   Print ....  : 0 10 fish Yu1 CBA Xu

7.Row type

Row provides an index-based and case-sensitive approach to accessing columns with little memory overhead. The original text reads as follows:

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

A detailed description of the Row object

Class Sqlite3. Row
A Row instance serves as a highly optimized row_factory for Connection objects. It tries to mimic a for a tuple in the most of its features.
It supports mapping access by column name and index, iteration, representation, equality testing and Len ().
If the Row objects has exactly the same columns and their members is 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's the first member of each tuple in cursor.description.
New in version 2.6.

The following examples illustrate

in [+]: cx.row_factory = sqlite3. Rowin []: c = cx.cursor () in [+]: C.execute (' select * from Catalog ') out[32]: <sqlite3. Cursor object at 0x05666680>in ["]: R = C.fetchone () in [over]: type (r) out[34]: <type ' sqlite3. Row ' >in []: rout[35]: <sqlite3. Row object at 0x05348980>in [approx]: print R (0, Ten, U ' \u9c7c ', U ' Yu ') in [PNS]: Len (r) out[37]: 4In [G]: r[2]      #使用索引查询Out [G]: U ' \u9c7c ' in [+]: R.keys () out[41]: [' id ', ' pid ', ' Name ', ' nickname ']in []: for E in R:  ....:   print E,  .....: 0 10 Fish Yu

Keyword query using columns

in [+]: r[' id ']out[43]: 0In []: r[' name ']out[44]: U ' \u9c7c '