Advanced Python Learning (iii)

This section uses SQLite to understand database operations

-------------------------

Database support

Using simple plain text can only implement a fallback function, the need to introduce a database to complete more powerful functions, this section uses the simple database SQLite.

SQLite and Pysqlite

SQLite is a very well-known open source embedded database software, it can be embedded in other programs to use, and provide SQL interface for querying, very convenient. Its official site is http://www.sqlite.org.

And Pysqlite is a SQLite API for Python, it makes everything for the operation of SQLite is extremely simple

After the python2.5 version, the advantage of SQLite is that one of its packaging (Pysqlite) has been included in the standard library, so we can use it directly.

How to get Started

You can import SQLite as a module named Sqlite3. You can then create a connection to the database file----If the file does not exist, it is created----by providing a file name:

>>> Import Sqlite3>>> conn = Sqlite3.connect (' somedatabase.db ') # CREATE DATABASE >>>cu = Conn.cursor () #能获得连接的游标 # Create datasheet >>> cu.execute ("" "CREATE TABLE catalog (ID integer primary key, pid integer, name Varch AR (Ten) UNIQUE) "" "
#插入两条数据 >>> Cu.execute ("INSERT into catalog values (0,0, ' name1 ')") >>> Cu.execute ("INSERT INTO catalog VALUES (1,0, ' name2 ') ") >>> Conn.commit ()
#选择 (SELECT) >>> cu.execute ("SELECT * from Catalog") >>> Cu.fetchall () [(0, 0, ' name1 '), (1, 0, ' name2 ')]& Gt;>> Cu.execute ("SELECT * from catalog where id = 1") >>> Cu.fetchall () [(1, 0, ' name2 ')] #修改 (update) >&gt ;> cu.execute ("Update Catalog set name= ' name2′where id = 0″)
>>> Cx.commit ()
>>> Cu.execute ("SELECT * from Catalog")
>>> Cu.fetchone () (0, 0, ' name2′)
#删除 (delete) >>> cu.execute ("Delete from catalog where id = 1″)
>>> Cx.commit ()
>>> Cu.execute ("SELECT * from Catalog")
>>> Cu.fetchall ()
[(0, 0, ' name2 ')]

Connection

In order to use the underlying database system, you must first connect to it, at which point you need to use the Connect function with the name, which has multiple parameters, and which parameter depends on the database.

Common parameters for the Connect function:

The Connect function returns the Connection object. This object represents the current and database session. The methods supported by the connection object are as follows;

Connection object Method:

The Commit method is always available, but it has no effect if the database does not support transactions. If the connection is closed but there are uncommitted transactions, they are rolled back implicitly---but only if the database supports rollback.

The rollback method may not be available because not all databases support transactions (transactions are a series of actions). If available, you can "undo" any uncommitted transactions.

The cursor method introduces us to another topic: a Cursor object. Scan rows of SQL queries through cursors and examine the results. Cursor connections support More methods and may be better used in programs.

Cursor:

CU = Conn.cursor ()

A cursor that can be connected to execute a SQL query.

Conn.commit ()

Make sure that you have committed the commit before you complete the insert and make certain changes so that you can actually save the changes to the file.

Cursor Object methods:

Cursor Object properties:

Cu.fetchone ()

Fetchall () returns all the data in the result set as a list of a tuple. Each tuple element is arranged in the order in which the table is built. Note that the cursor is stateful, and it can record the number of records that are currently taken to the result, so you can typically traverse the result set only once. In the above case, if the execution of Fetchone () is returned as empty. This is a point to be aware of when testing.

Conn.close ()

You can commit the database every time you modify it, instead of just submitting it when you are ready to close it, use the Close method when you are ready to close the data.

---------------

Reference:

Pysqlite Concise Tutorial: http://blog.donews.com/limodou/archive/2004/07/05/37895.aspx

SQLite transcoding problem: http://blog.sina.com.cn/s/blog_68013e850100w66d.html

Advanced Python Learning (iii)