Python database operations

Database operations have become very useful in Python. With an API standard, the following describes how to use this framework definition. This framework includes the following parts:
Module Interface
Connection object
Cursor object
DBI secondary object
Data Types and definitions
Tips for implementation
Changes from 1.0 to 2.0
Example
Module Interface
The format of parameters in connect (parameters...) is as follows:

Dsn Data Source Name
User username (optional)
Password (optional)
Host Name (optional)
Database Name (optional)

For example:
Connect (dsn = 'myhost: mydb', user = 'guid', password = '100
This Standard specifies the following global variables:

Apilevel: indicates the version of the DB-API, Which is '1. 0' and '2. 0'. If not defined, the default value is '1. 0'
Threadsafety:

0 Threads may not share the module.

1 Threads may share the module, but not connections.

2 Threads may share the module and connections.

3 Threads may share the module, connections and cursors.
Paramstyle: used to represent the parameter passing methods. There are five kinds:
'Qmark' question mark style. e. G'... WHERE name =? '
'Numeric 'number, placeholder style. e. G'... WHERE name =: 1'
'Named' naming style. e. G' WHERE name =: name'
'Format' ansi c printf style. e. G'... WHERE name = % s'
'Pyformat' Python extension notation. e. G'... WHERE name = % (name) s'
Exception class: StandardError
|__ Warning
|__ Error
|__ InterfaceError
|__ DatabaseError
|__ DataError
|__ OperationalError
|__ IntegerityError
|__ InternalError
|__ ProgrammingError
|__ NotSupportedError

Connection object
The connection object includes the following methods:

. Close ()
Close connection
. Commit ()
Used for commit operations in transaction processing
. Rollback ()
Used for rollback operations in transaction processing
. Cursor ()
Get a cursor
Cursor object
The cursor object contains the following attributes and methods:

. Description
A list (name, type_code, display_size, internal_size, precision, scale, null_ OK). This attribute is available only after data is obtained. Otherwise, it will be null.
. Rowcount
Indicates the number of rows returned. If the executeXXX () method is not executed or this method is not implemented in this module,-1 is returned.
. Callproc (procname [, parameters])
(This is an optional method. It should be because not all databases support stored procedures)
. Close ()
Close cursor
. Execute (operation [, parameters])
Prepare and execute a database operation (including query and command)
. Executeparameters (operation, seq_of_parameters)
Prepare a database command and execute the command multiple times according to the parameter
. Fetchone ()
Returns the query result of the first row.
. Fetchmany ([size = cursor. arraysize])
Returns the value of a specified row.
. Fetchall ()
Returns all query results.
. Arraysize
The value of this parameter indicates the number of rows that fetchbatch obtains by default.
Data Types and definitions
Define common data types. If you do not use them, do not analyze them first.
Remarks
Of course, what we need to know is that this is only a standard, which is defined in the standard in general. However, there are many specific implementations that we need to master, however, if we have mastered these standards, the general operation will not be a problem.

Below are several database-related URLs.

Database Topic Guide
The Python Database Wizard provides good materials, including API definition and driver connection.
SQL/"> MSSQL driver
Is the MSSQL driver.
Example
The following example uses MSSQL as a sample, but it can be used with other drivers. This is very similar to the database operations of Perl, this makes it easy for us to port different databases.

1. query data

Import MSSQL
Db = MSSQL. connect ('SQL Server ip', 'username', 'Password', 'db _ name ')
C = db. cursor ()
SQL = 'select top 20 rtrim (ip), rtrim (dns) from detail'
C.exe cute (SQL)
For f in c. fetchall ():
Print "ip is % s, dns is % s" % (f [0], f [1])

2. Insert data
SQL = 'insert into detail values ('192. 168.0.1 ', 'www .dns.com.cn ')
C.exe cute (SQL)

3. An example of ODBC

Import dbi, odbc # ODBC modules
Import time # standard time module
Dbc = odbc. odbc (# open a database connection
'Sample/monty/spam' # 'datasource/user/password'
)

Crsr = dbc. cursor () # create a cursor
Crsr.exe cute (# execute some SQL
"""
SELECT country_id, name, insert_change_date
FROM country
Order by name
"""
)
Print 'column descriptions: '# show Column descriptions
For col in crsr. description:
Print '', col
Result = crsr. fetchall () # fetch the results all at once
Print '/nFirst result row:/N', result [0] # show first result row
Print '/nDate conversions:' # play with dbiDate object
Date = result [0] [-1]
Fmt = '%-25 s %-20s'
Print fmt % ('standard string: ', str (date ))
Print fmt % ('Seconds since epoch: ', float (date ))
TimeTuple = time. localtime (date)
Print fmt % ('time tuple: ', timeTuple)
Print fmt % ('user defined: ', time. strftime (' % d % B % y', timeTuple ))
------------------------------- Output --------------------------------
Column descriptions: www.2cto.com
('Country _ id', 'number', 12, 10, 10, 0, 0)
('Name', 'string', 45, 45, 0, 0)
('Insert _ change_date ', 'date', 19, 19, 0, 0, 1)

First result row:
(24L, 'argentina ', <DbiDate object at 7f1c80>)
Date conversions:
Standard string: Fri Dec 19 01:51:53 1997
Seconds since epoch: 882517913.0
Time tuple: (1997, 12, 19, 1, 51, 53, 4,353, 0)
User defined: 19 December 1997