Python database Operations (SQLite)

Python database Operations (SQLite)

Unlike the common client/server architecture paradigm, theSQLite 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. Use DB. Connect creates a database connection, assuming that the connection object is conn
2. If the database operation does not need to return results, use Conn directly. execute query, depending on the level of database transaction isolation,

It is possible to modify the database Conn. Commit
3. Use conn If you need to return the query results. the cursor creates a Cursor object cur, querying the database through Cur.execute ,

Returns the result of the query with Cur.fetchall/cur.fetchone/cur.fetchmany. Depending on the database transaction isolation level,

You may need to modify the database Conn.commit
4. Close cur, close()

One, pythonsqlite 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 Pythonsqlite database cursors:

Cursors provide a flexible means of manipulating data retrieved from a table, in essence,A cursor is actually a mechanism to extract one record at a time from a result set that includes multiple data records。 Cursors are always associated with an SQL selection statement. Because the cursor isresult set(Can be 0, one, or multiple records retrieved from a related selection statement) and a result set that points to a specific recordcursor PositionComposition When When you decide to process a result set, you must declare a cursor that points to the result set. , the file handle can represent the file as long as the file is open 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.

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 below, use the Pythonsqlite database to do something about the database we built above:

1, Build table:
Cu.execute (' CREATE TABLE catalog (ID integer primary key,pidinteger,name varchar (UNIQUE) ') 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 catalogvalues (1, 0, ' hello ')") simply inserts two rows of data, However, it is necessary to be reminded that it will not take effect until it is submitted. 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 Pythonsqlite 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:

Exemplaryexemplar 1

Importsqlite3

Con =sqlite3.connect (' d:/mydatabase.db3 ')
Cur =con.cursor ()
Cur.execute (' CREATE tablefoo (o_id INTEGER PRIMARY KEY, Fruit VARCHAR (), Vegesvarchar (30)) ')
Con.commit ()
Cur.execute (' INSERT intofoo (o_id, fruit, veges) VALUES (NULL, "apple", "broccoli")
Con.commit ()
Printcur.lastrowid

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

Exemplaryexemplar 2

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

conn =sqlite3.connect ("d:/aaa.db")
Conn.isolation_level = none# This is the transaction isolation level, the default is that you need to commit to modify the database, set to none automatically each time the modification is committed, otherwise ""
# Below is the creation of a table
Conn.execute ("CREATE table if not exists t1 (ID integer primary keyautoincrement, name varchar (+), info varchar) ")
# Insert Data
Conn.execute (" INSERT into T1 (name,info) VALUES (' Zhaowei ', ' only Atest ')
# If the isolation level is not autocommit, you need to do a commit manually
Conn.commit ()
# Gets the cursor object
cur = conn.cursor ()
# Query with a cursor to get to the result
Cur.execute ("select * from T1")
# Get all results
res = Cur.fetchall ()
print ' row: ', Cur.rowcount
# cur.description is a description of the table structure
print ' desc ', cur.description
# The result returned with Fetchall is a two-dimensional list of
for line in Res:
    for F inline:
        print F,
    print
print '-' *60

Cur.execute ("Select *from T1")
# This query only takes one result, which is a one-dimensional list
res = Cur.fetchone ()
print ' row: ', Cur.rowcount
For F in Res:
Printf
Print
# Take another line
res = Cur.fetchone ()
print ' row: ', Cur.rowcount
For F in Res:
Printf
Print
print '-' *60

Cur.close ()
Conn.close ()

Python database Operations (SQLite)