Python Basic Tutorial Summary 12--Database

1. Python Database API

Many databases that support SQL standards have corresponding client modules in Python. In order to switch (compatible) between different modules that provide the same functionality (essentially the same), Python specifies a standard DB API. Currently the latest version of the API is 2.0,

1.1 Global variables

Any database module that supports the 2.0 version of the DB API defines 3 global variables that describe the properties of the module:

APILevel            Use of the Python DB API version threadsafety        module thread-safe level       Paramstyle the          parameter style used in SQL queries

1) apilevel is a string constant that provides the version number of the API being used, for the DB API 2.0 version

• The value may be ' 1.0 ',
• It could be ' 2.0 ';

  2）threadsafetyThis is only a reference value when using multi-threading, with a value range of 0~3 integers.

• 0 means that the module is not shared at all
• 1 means that the thread itself can share the module, but not share the connection
• 3 indicates that the module is completely thread-safe

　3）paramstyleIndicates how parameters are stitched into an SQL query when performing multiple similar queries:

• ' Format ' represents the standard string formatting
• ' Pyformat ' represents an extended format code, using a dictionary concatenation
• ' Qmark ' means using a question mark
• ' Numberic ' denotes a field using: 1 or: 2 format
• ' Named ' Indicates a field using: Name format

1.2 Exceptions

Some exception classes (hierarchies) are defined in the API, which can be used to catch multiple exceptions through the except block. If you want to know more about DB API exceptions, consider this: http://www.python.org/dev/peps/pep-0249/#exceptions, the following is the exception used in the DB API:

=============================================================================== Exception                       Super class               description                              = ==============================================================================StandarError                                All generic base class waring                  Standarerror        Generic superclass that throws error Standarerror all error conditions when a non-fatal error occurs interfaceerror          Error Databaseerror the base class for error related to database               errors on interfaces rather than databases               dataerror               databaseerror       Database-related issues, For example, value out        of range Operationalerror databaseerror       database internal operation error Integrityerror          databaseerror       Relationship integrity is affected, For example, key check failed internalerror           databaseerror       Database internal error, such as illegal cursor programningerror        databaseerror       user Programming error, For example, not found table Notsupportederror       databaseerror       Request unsupported features (such as rollback)

1.3 Connections and cursors

Using a connect function to connect to a database, the function has multiple parameters, depending on the corresponding database. The following are the specific parameter meanings (how to use keyword parameters when using):

================================================== Parameter name         description                                  ==================================== ==============DSN         database Source name, given parameter indicates database dependency (required) user username  (optional)                           passowrd    password (        optional) host Host name (optional)    database                                    name (optional)

The function returns a Connection object that represents the current and database session, supported by the following methods:

============================================================= Method Name                 description                                  ========================= ==================================== Close() when the             connection is closed, the connection object and its cursors are not available commit ()            commits the pending transaction if supported. Otherwise, rollback () cannot be used to rollback a          pending transaction cursor ()            returns a connected Cursor object

　　rollbackmethod if you use a database that supports it, all uncommitted transactions are revoked. Instead cursor , the method returns a cursor object that can execute the SQL query and examine the results through the cursor. Cursors support more methods than joins, and are also more user-friendly, following an overview of the cursor methods:

====================================================================================Name Description====================================================================================Callproc (name[,params]) When the cursor is closed with the given name and parameter (optional) call to the named Database program Close (), the cursor is not available for execute (oper[,params]) to perform SQL operations, optional parameter Executemany (oper, PSEQ) performs a SQL operation on each parameter in the sequence Fetchone () checks The next row in the query result set is saved as a sequence or nonefetchmany ([size]) to get multiple rows in the result set of the queries, and the default size of Arraysizefetchall () will all The line (remaining) as a sequence of sequences nextset () jumps to the next available result set (optional) setinputsizes (sizes) is a pre-defined memory area for the parameter setoutputs Ize (size[, col]) sets the buffer size for the obtained large database value

The cursor object also contains some features:

================================================== name                   description                                  ===================================== =============Description         result column describes the sequence, read-only rowcount the number of            rows in the result, read-only arraysize           fetchmany the number of rows returned, default to 1

1.4 Types

The DB API defines the correspondence between types and database types in Python. By means of special types and values of constructors and constants (singleton mode), as follows (some modules may not exactly follow these definitions):

================================================== name                                  description                                  ===================================== ============= Date(year, month, day)              creates an object that holds a date value time (hour, minute, second)          creates an object that holds the value of the period timestamp (Y, Mon, D, h , Min, s)     creates an object that holds a timestamp datefromticks (ticks)                creates an object that holds the number of seconds since the epoch Timefromticks (ticks)                Creates an object that holds a time value from a number of seconds timestampfromticks (ticks) creates an object that holds a timestamp           value from seconds binary (string)                      Create an object that holds a binary string value string                              describes a binary column (such as long or raw) binary                              description Number column Mumber                              description Number column datetime                            description date /  Time column rowID                               description row ID column

2. SQLite and Pysqlite

SQLite is a small database engine that is not based on a centralized database storage mechanism, but directly acts on local files. After Python 2.5, SQLite's wrapper (Pysqlite) has been included in the standard library (the SQLite database has been included in the latest Python version and does not need to be installed separately).

2.1 Simple Example

To import SQLite as a module named Sqlite3, you can create a database file (which does not exist, you can specify a path), and connect to the database, here is a simple example:

1 #--coding:utf-8 --2import sqlite334 conn = Sqlite3.connect ( ' mydatabase.db ' )5 curs = conn.cursor () # get cursor 6conn.commit () # Commit changes 7 Conn.close ()  # Close Connection

2.2 Creating and populating tables

Using the following example to learn if you are creating and inserting data into a table, the database used here is a database based on the text format provided by the USDA Nutrition Database Lab, where the file address is:/http Sr23-nutrient-calculator.googlecode.com/svn-history/r9/trunk/abbrev.txt, examples are as follows:

1 Import Sqlite32 3 def convert (value):4     ifValue.startswith ('~'):5         returnValue.strip ('~')6     ifNot value:7Value ='0'8     return float(value)9 Tenconn = Sqlite3.connect ('food.db') OneCurs =conn.cursor () A  -Curs.execute (" " - CREATE TABLE Food ( the ID TEXT PRIMARY KEY, - desc TEXT, - Water FLOAT, - kcal FLOAT, + protein FLOAT, - Fat FLOAT, + Ash FLOAT, A Carbs FLOAT, at Fiber FLOAT, - Sugar FLOAT -     ) -     " ") -  -query = R'INSERT into Food VALUES (?,?,?,?,?,?,?,?,?,?)' in  -  forLineinchOpen'ABBREV.txt'): toFields = Line.split ('^') +Vals = [Convert (f) forFinchfields[0:Ten]] - Curs.execute (query,vals) the  * Conn.commit () $Conn.close ()

2.3 Searching and processing results

Querying the database content only requires creating a connection and obtaining a cursor for that connection, execute executing the SQL query by method, and fetchall extracting the results using methods such as the following:

1 # food_query.py2 Import Sqlite3,sys3 4conn = Sqlite3.connect ('food.db')5Curs =conn.cursor ()6 7query ='SELECT * from food WHERE%s'% sys.argv[1]8 Print Query9 curs.execute (query)Tennames = [f[0] forFinchCurs.description] One  forRowinchCurs.fetchall (): A      forPairinchZip (names,row): -Print'%s:%s'%pair -Print

Run the program and pass the query parameters through the command line:

　　　　python food_query.py "kcal <=100 AND fiber >= 10 ORDER BY sugar"

Python Basic Tutorial Summary 12--Database