<<python Basic Tutorials >> Learning Notes | 13th Chapter | Database support

Note: This chapter is relatively simple to introduce, but examples are used, mainly to master if connected, using the database, and to do the example of SQLite

------

Python database API

To solve the compatibility problem between various database modules in Python, a standard DB API has now been passed. The current API version (2.0) is defined in the Python Database API Specification v2.0 in PEP249.

Abnormal

In order to handle errors as accurately as possible, some exceptions are defined in the API. They are defined in a hierarchy so that multiple exceptions can be captured through a except block.

Joins and cursors

In order to use the underlying database system, you must connect to it. You will need to use the Connect function, which has multiple parameters.

The Connect function returns the Connection object. This object represents the current and database session. The connection object supports the following methods:

Rollback: Must be supported by this database, otherwise not available

Commit: Always available, but does not work if a database does not support transactions

Cursor: Cursors object that executes SQL queries through cursors and examines the results. Cursors support more methods.

Type:

The database has different requirements for inserting values into columns of a certain type, in order to be able to interoperate correctly with the underlying SQL database.

The following is an example of the Sqlite3 module:

SQLite and Pysqlite

This selects the small database engine SQLite, which does not need to run as a standalone server, and is not based on a centralized database storage mechanism, but directly on local files. Starting with Python 2.5, The advantage of SQLite is that one of its packaging pysqlite is already included in the database and does not need to be installed separately. sqlite website Download Source: http://sqlite.org

------

Entry:

#导入模块

>>>import Sqlite3

#建立连接, the file does not exist and is created

>>>conn = Sqlite3.connect (' test.db ')

#获得连接的游标

>>>curs = Conn.cursor ()

#提交事务 so that the operation will remain on the local

>>>conn.commit ()

#关闭数据库

>>>conn.close ()

------

Example:

Prepare data: the site: on the Http://www.ars.usda.gov/nutrientdata home page, click the USDA National Nutrient Database for standard reference link, The ASCII persisted zip data file, reserved to the local, ABBREV.txt file, is formatted roughly as follows:

~23451~^~beef,nz,imp,brisket Point End,ln & fat,raw~^70.11^

~23452~^~beef,nz,imp,chuck Eye Roll,ln & fat,ckd,brsd~^55.95^

Fields are split with ^, numeric fields contain numbers, and text segments include ~ String values. You can use Line.split (' ^ ') to parse a line of text into multiple fields. For other numeric fields, use float (field). The following is the complete script and output:

• Code Listing 1: Importing data into the local database

Import Sqlite3def Convert (value):    if Value.startswith (' ~ '):        return Value.strip (' ~ ')    if not value:        Value = ' 0 '    return float (value) conn = Sqlite3.connect (' food.db ') Curs = Conn.cursor () curs.execute ("' CREATE TABLE Food (ID          TEXT PRIMARY key,desc        text,water       FLOAT,      kcal        float,protein     float,fat         Float,ash         float,carbs       float,fiber       float,sugar       FLOAT)    ') query = ' INSERT into food VALUES ( ?,?,?,?,?,?,?,?,?,?)' For line in open (' ABBREV.txt '): Fields    = line.split (' ^ ')    vals   = [convert (f) to F in Fields[:10]]    Curs.execute (query,vals) conn.commit () Conn.close ()

• Code Listing 2: Food database query procedure

Import sqlite3,sysconn = Sqlite3.connect (' food.db ') Curs = conn.cursor () query = ' SELECT * from food WHERE%s '% sys.argv[1] Print Querycurs.execute (query) names = [f[0] for F in curs.description]for Row in Curs.fetchall (): For    pair in Zip (name S,row):        print '%s:%s '% pair    print

Output Result:

+++++++++++++++++++++++++++++++++++++++++++++++++

D:\>python food_query.py ID

id:23449
Desc:beef,nz,imp,brisket NAVAL End,ln & Fat,raw
water:53.33
kcal:345.0
protein:15.81
fat:31.27
ash:0.61
carbs:0.0
fiber:0.0
sugar:0.0

id:23450
Desc:beef,nz,imp,brisket Point End,ln & FAT,CKD,BRSD
water:54.66
kcal:250.0
protein:31.94
fat:13.6
ash:0.78
carbs:0.0
fiber:0.0
sugar:0.0

+++++++++++++++++++++++++++++++++++++++++++++++++

D:\>python food_query.py "kcal<=100 and fiber >=0 ORDER by Sugar"

id:09512
Desc:grape juc,cnd OR btld,w/added VIT C & CA
water:84.51
kcal:62.0
protein:0.37
fat:0.13
ash:0.22
carbs:14.77
fiber:0.2
sugar:14.2

<<python Basic Tutorials >> Learning Notes | 13th Chapter | Database support