Python web programming learning notes (9): Database client DB-API

1. DB-API Overview

Python supports many different databases. The network protocol for communication with databases varies depending on the seller's server. In earlier versions of python, each database has its own python module. All these modules work in different ways and provide different functions. This method is not easy to write code that can run in a variety of Database Server types, so the DB-API library function is generated. In the DB-API, all Modules connected to the database provide a common interface even if the underlying network protocol is different. This is similar to JDBC and ODBC in JAVA.
DB-API: IBM DB2, Firebird (and Interbase), Informix, Ingres, MySQL, Oracle, PostgreSQL, sap db (also known as "MaxDB"), Microsoft SQL Server, Sybase, etc.

2. Database Connection

1. PostgreSQL

Several modules can be used to connect python to PostgreSQL. Here we mainly introduce the use of psycopg.
Yes: http://initd.org/psycopg/download /. If PostgreSQL is not available, download it from: http://www.postgresql.org /. (For more information about PostgreSQL installation, see http://wenku.baidu.com/view/8e32d10c6c85ec3a87c2c500.html .) Connect to the PostgreSQL database:

Copy codeThe Code is as follows:
Import psycopg2
Print "connecting to test" # test is the database name
Dbh = psycopg2.connect ('dbname = test user = s s ')
Print "connection successful"


2. MySQL

For MySQL, the python interface is known as MySQLdb or MySQL-Python, http://sourceforge.net/projects/mysql-python /. Different from PostgreSQL, The MySQLdb connect () function can contain various parameters as follows:

Parameters	Description
User	User name. The default value is the current logon user.
Passwd	User Password, no default.
Db	The name of the connected database.
Host	Database Host Name.
Port	TCP port. The default value is 3306.

For example, connect to the test database:

Copy codeThe Code is as follows:
Import MySQLdb
Print "connecting ..."
Dbh = MySQLdb. connect (db = "test ")
Print "connection successful ."
Dbh. close ()

Iii. Simple operations (taking PostgreSQL as an example)

The following uses PostgreSQL as an example to describe how to create and query tables. In this example, the database name is test and the username is S. Enter a table name, insert data to the table, and query the data. The details are as follows:

Copy codeThe Code is as follows:
Import psycopg2
Print "connecting to test"
Dbh = psycopg2.connect ('dbname = test user = s s ')
Print "connection successful"
Cur = dbh. cursor () # create a cursor object. The returned data is in the dictionary format.
A = raw_input ('table list: ') # Name of the input table
Cur.exe cute ("create table % s (myid integer UNIQUE, filename varchar (255)" % a) # generate a TABLE that contains a field filename
B = 1c = '000000'
Cur.exe cute ("insert into % s VALUES (% d, % s)" % (a, B, c) # INSERT records B, c to the table
Cur.exe cute ("SELECT * FROM % s" % a) # query table content
Rows = cur. fetchall () # obtain all rows in the result set
For row in rows:
Print row
Dbh. commit () # The above takes effect for database operation transactions
Dbh. close ()

1. Transactions

Most databases support transactions. transactions can place multiple changes to the database in one command. In the preceding example, the preceding operations on the database do not take effect if the commit () command is not executed. There is also a function rollback (), which can effectively discard the changes after the last execution of commit () or rollback. This function is very effective when an error is found and you want to discard the transaction that has been issued. For databases that do not support transactions, changes are immediately executed, and commit () does nothing, but rollback () reports an error.

2. Efficiency

The performance of event execution depends on different servers. Generally, submitting a single command is the slowest way to update the database, however, if a large amount of data is submitted at a time, the server will experience buffer Overflow. Therefore, the number of submissions should be processed reasonably.

Iv. Parameter Style
In the preceding example, the same type format as printf () is used. But in fact, in the DB-API, different databases support different parameter wind network, You must select the appropriate method, otherwise the program will not execute. The following method shows the supported type formats.

Copy codeThe Code is as follows:
>>> Import psycopg2
>>> Print psycopg2.paramstyle

The pyformat result shows that the pyformat is currently supported.

For the DB-API specification, In the use frequency from small to large order of introduction:

Qmark	Indicates the question-mark style. Each bit of data in the instruction string is replaced by a question mark, and the parameter is given in the form of list or tuple. Example: insert into ch14 VALUES (?, ?).
Format	The same type format as printf () does not support the extension of the specified Python parameter. It carries a list or tuple for conversion. Example: insert into ch14 VALUES (% d, % s)
Numeric	Indicates the numeric style. Each bit of data in the instruction string is replaced by a colon followed by a number (the number starts with 1). The parameter is given in the form of list or tuple. Example: insert into ch14 VALUES (: 1,: 2)
Named	Named style. Similar to numeric, but the number is replaced by the name after the colon. A dictionary is used for conversion. Example: insert into ch14 VALUES (: number,: text)
Pyformat	Supports Python-style parameters, with a dictionary for conversion. For example, insert into ch14 VALUES (% (number) d, % (text) s ).

5. Duplicate commands
1. execute and executetasks ()

Example:

Insert the following data into the test database:

12 Twelve
13 Thirteen
14 Fourteen
15 teen

(1) execute Insert entries

Copy codeThe Code is as follows:
Cur.exe cute ("insert into test VALUES (12, 'twelve ')")
Cur.exe cute ("insert into test VALUES (13, 'thirteen ')")
Cur.exe cute ("insert into test VALUES (14, 'Fourteen ')")
Cur.exe cute ("insert into test VALUES (15, 'deleteen ')")

This method is too inefficient.

(2) The executetasks () function includes a command and a record of command running. Each record in the list is either a list or a dictionary.

Copy codeThe Code is as follows:
Import psycopg2
Print "connecting to test"
Dbh = psycopg2.connect ('dbname = test user = s s ')
Print "connection successful"
Cur = dbh. cursor ()
Rows = ({'num': 0, 'text': 'zero '},
{'Num': 1, 'text': 'item one '},
{'Num': 2, 'text': 'item two '},
{'Num': 3, 'text': 'Three '})
Cur.exe cutees ("insert into test VALUES (% (num) d, % (text) s)", rows)
Dbh. commit ()
Dbh. close ()

The main drawback of executetasks () Is that all records are stored in the memory before executing commands. If the data is big, this is a problem. It occupies all the memory resources of the system. If executeute () cannot meet the requirements, it is possible to achieve performance optimization in addition to execute. The database backend can be optimized when execute () is called cyclically, according to The DB-API instructions. However, its first parameter must point to the same object, rather than a string containing the same value, that is, the same string object in memory. Like executepipeline (), this does not guarantee optimization, and it cannot expect that execute () runs faster than executepipeline. However, if executeworkflow () cannot be used, this is the best choice.

6. fetchall, fetchtasks, and fetchone to obtain data

Fetchall (self): receives all returned result rows.

Fetchmany (self, size = None): receives the size of returned results rows. If the size value is greater than the number of returned results rows, the returned cursor. arraysize data is returned.

Fetchone (self): returns a result line.

VII. Obtain metadata)

The English name of Metadata is "Metadata", which is "data about data ". In the preceding example, the result of Metadata is:

Column (name = 'id', type_code = 23, display_size = None, internal_size = 4, precision = None, scale = None, null_ OK = None)
Column (name = 'filename', type_code = 1043, display_size = None, internal_size = 255, precision = None, scale = None, null_ OK = None)

Copy codeThe Code is as follows:
Import psycopg2
Print "connecting to bbstime"
Dbh = psycopg2.connect ('dbname = bbstime user = s s ')
Print "connection successful"
Cur = dbh. cursor ()

Cur.exe cute ("SELECT * FROM asd ")

For column in cur. description:
Print column

Dbh. close ()

8. Number of calculated rows
There are two methods: len () and rowcount.

Copy codeThe Code is as follows:
Import psycopg2
Print "connecting to bbstime"
Dbh = psycopg2.connect ('dbname = bbstime user = s s ')
Print "connection successful"
Cur = dbh. cursor ()
Cur.exe cute ("SELECT * FROM test ")
Rows = cur. fetchall ()
Print len (rows) # Use len to calculate the number of rows
Print "rows:", cur. rowcount # Use rowcount to calculate the number of rows
Dbh. close ()