Python Network Programming Learning Note (ix): Database client Db-api

I. Overview of DB-API

Python supports a number of different databases. Because different seller servers result in different network protocols for database communication. In earlier versions of Python, each database had its own Python module, all of which worked in different ways and provided different functions. This method is not easy to write code that can run on multiple database server types, so the DB-API library function is generated. In Db-api, all the modules that connect to the database provide a common interface, even if the underlying network protocols are different. This is similar to JDBC and ODBC in Java.
Db-api Download Address: http://wiki.python.org/moin/DatabaseProgramming, the current version is 2.0, the support database includes IBM DB2, Firebird (and Interbase), Informix, Ingres, MySQL, Oracle, PostgreSQL, SAP DB (also known as "MaxDB"), Microsoft SQL Server, Sybase, and more.

Second, the database connection

1. PostgreSQL

There are several modules that can be used to connect Python with PostgreSQL, which is mainly about using PSYCOPG.
The download address is: http://initd.org/psycopg/download/. If you do not have PostgreSQL, you can download it from the following address: http://www.postgresql.org/. (For more detailed introduction of the installation of PostgreSQL, see http://wenku.baidu.com/view/8e32d10c6c85ec3a87c2c500.html. ) Connect to the PostgreSQL database:

The code is as follows:

Import PSYCOPG2
Print "Connecting to test" # #test为数据库名
Dbh=psycopg2.connect (' Dbname=test user=postgres ')
Print "Connection Successful"

2. MySQL

For Mysql,python interface is known mysqldb or Mysql-python, download address: http://sourceforge.net/projects/mysql-python/. Unlike PostgreSQL, the MySQLdb Connect () function can take a variety of parameters, as follows:

Parameters	Description
User	User name, default to the currently logged on user.
passwd	User password, no default.
Db	The database name of the connection.
Host	The database host name.
Port	TCP port, default is 3306.

For example, connect the test database:

The code is as follows:

Import MySQLdb
Print "Connecting ..."
Dbh=mysqldb.connect (db= "test")
Print "Connection successful."
Dbh.close ()

Three, simple operation (with PostgreSQL as an example)

Here is an example of PostgreSQL for creating tables, querying tables, and so on. In the example, the database name is test, the user name is Postgres, a table name is entered, the data is inserted into the table, and the query is made. As follows, a note has been made:

The code is as follows:

Import PSYCOPG2
Print "Connecting to test"
Dbh=psycopg2.connect (' Dbname=test user=postgres ')
Print "Connection Successful"
Cur=dbh.cursor () #建立一个cursor对象, return data in dictionary form
A=raw_input (' table list: ') # Enter table name
Cur.execute ("CREATE table%s (myID integer unique,filename varchar (255))"%a) # Build table containing a field filename
B=1c= ' 201210310540 '
Cur.execute ("INSERT into%s VALUES (%d,%s)"% (a,b,c)) #向表中插入记录b, C
Cur.execute ("Select * from%s"%a) #查询表中内容
Rows=cur.fetchall () # Get all rows in the result set
For row in rows:
Print row
Dbh.commit () #以上对数据库的操作事务生效
Dbh.close ()

1. Business

Most databases support transactions, and transactions can put multiple changes to the database in one command. In the example above, when the commit () command has not been executed, the above operation on the database will not take effect. There is also a function rollback (), which can effectively discard the last commit () or rollback () changes. This function is very effective when it finds an error and wants to discard a transaction that has already been issued. For databases that do not support transactions, the changes are executed immediately, and commit () does nothing, but rollback () will error.

2. Efficiency

The performance of the event is largely dependent on the different servers, and generally, committing after each individual command is the slowest way to update the database, but if large data is committed at a time, it can cause a buffer overflow in the server. Therefore, the number of submissions should be handled reasonably.

Four, the parameter style
In the example above, the same type format as printf () is used. But in fact, in Db-api, different databases support different parameters, you must choose the appropriate method, otherwise the program will not execute. The following method lets you know which type format is currently supported.

The code is as follows:

>>> Import PSYCOPG2
>>> Print Psycopg2.paramstyle

Pyformat This result can be seen, currently supports the Pyformat format.

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

Qmark	Represents the Question-mark style. Each bit of the data in the instruction string is replaced with a question mark, and the parameter is given as a list or a tuple. For example: INSERT into ch14 VALUES (?,?).
Format	Using the same type format as printf (), the extension for the specified parameter python is not supported. It takes a list or a tuple to convert. Example: INSERT into ch14 VALUES (%d,%s)
Numeric	Represents the numeric style. Each bit of the data in the instruction string is replaced by a colon followed by a number (the number starts with 1), and the parameter is given as a list or a tuple. Example: INSERT into Ch14 VALUES (: 1,: 2)
Named	Represents the named style. Similar to numeric, but replaces numbers with names after colons. With a dictionary for conversion. Example: INSERT into ch14 VALUES (: number,: Text)
Pyformat	Supports Python-style parameters, with dictionary for conversion. For example: INSERT into ch14 VALUES (% (number) d,% (text) s).

V. Repetitive directives
1. Execute and Executemany ()

Example:

Insert the following data into the test database:

Twelve
Thirteen
Fourteen
Fifteen

(1) Execute strips insert

The code is as follows:

Cur.execute ("INSERT into Test VALUES (' twelve ')")
Cur.execute ("INSERT into Test VALUES (' thirteen ')")
Cur.execute ("INSERT into Test VALUES (' fourteen ')")
Cur.execute ("INSERT into Test VALUES (fifteen ')")

This approach is too inefficient.

(2) the Executemany () function takes a record of a command and a list of instructions to run. Each record on the list is either a list or a dictionary.

The code is as follows:

Import PSYCOPG2
Print "Connecting to test"
Dbh=psycopg2.connect (' Dbname=test user=postgres ')
Print "Connection Successful"
Cur=dbh.cursor ()
rows = ({' num ': 0, ' text ': ' Zero '},
{' num ': 1, ' text ': ' Item One '},
{' num ': 2, ' text ': ' Item ', '},
{' num ': 3, ' text ': ' Three '})
Cur.executemany ("INSERT into Test VALUES (% (num) d,% (text) s)", rows)
Dbh.commit ()
Dbh.close ()

The main disadvantage of Executemany () is that all records are placed in memory before they are required to execute the instruction. If the data is large, this is a problem, it will occupy all the memory resources of the system. If Executemany () is not sufficient, it is possible to achieve performance optimizations in addition to execute (). According to Db-api, when execute () is called periodically, the database backend can perform optimizations. But its first argument must point to the same object, not a string that contains the same value, that is, the same string object in memory. As with Executemany (), this does not guarantee optimization, and you cannot expect execute () to run faster than Executemany (). But if you can't use Executemany (), this is one of the best options.

Vi. Fetchall, Fetchmany, Fetchone get Data

Fetchall (self): receives all the returned result rows.

Fetchmany (self, Size=none): Receives a size bar that returns the result row. If the value of size is greater than the number of result rows returned, the cursor.arraysize data is returned.

Fetchone (self): Returns a result row.

Vii. acquisition of metadata (metadata)

The English name of the metadata is "Metadata", which is "data about data". As in the above 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)

The code is as follows:

Import PSYCOPG2
Print "Connecting to Bbstime"
Dbh=psycopg2.connect (' Dbname=bbstime user=postgres ')
Print "Connection Successful"
Cur=dbh.cursor ()

Cur.execute ("SELECT * from ASD")

For column in Cur.description:
Print column

Dbh.close ()

Eight, calculate the number of rows
There are two methods, one is to use Len (), and one is to use rowcount.

The code is as follows:

Import PSYCOPG2
Print "Connecting to Bbstime"
Dbh=psycopg2.connect (' Dbname=bbstime user=postgres ')
Print "Connection Successful"
Cur=dbh.cursor ()
Cur.execute ("SELECT * FROM Test")
Rows=cur.fetchall ()
Print len (rows) #利用len来计算行数
print "Rows:", cur.rowcount# uses rowcount to calculate the number of rows
Dbh.close ()

