Python Network Programming Learning Notes (ix): Database client Db-api_python

I. Overview of DB-API

Python supports a number of different databases. Because the different seller server causes the network protocol which communicates with the database to be different. In earlier versions of Python, each database had its own Python module, all of which worked in a different way and provided 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 Db-api, all modules that connect to a database provide a common interface, even if they are different from the underlying network protocols. 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, support database includes IBM DB2, Firebird (and InterBase), Informix, Ingres, MySQL, Oracle, PostgreSQL, SAP DB (also known as "MaxDB"), Microsoft SQL Server, Sybase, and so on.

Second, the database connection

1, PostgreSQL

There are several modules that can complete the Python-PostgreSQL connection, which is mainly about using PSYCOPG.
The download address is: http://initd.org/psycopg/download/. If there is no PostgreSQL, you can download it from the following address: http://www.postgresql.org/. (about PostgreSQL installation, etc. more detailed introduction, can see http://wenku.baidu.com/view/8e32d10c6c85ec3a87c2c500.html.) ) to connect to the PostgreSQL database:

Copy Code code as follows:

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

2, MySQL

For Mysql,python interfaces are 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 that defaults 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:

Copy Code code as follows:

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

Three, simple operation (take PostgreSQL as an example)

Here take PostgreSQL as an example to create tables, query tables, and so on. In the example, the database named Test, the username is postgres, enter a table name, insert the data into the table, and query. As follows, a note has been made:

Copy Code code as follows:

Import PSYCOPG2
Print "Connecting to test"
Dbh=psycopg2.connect (' Dbname=test user=postgres ')
Print "Connection Successful"
Cur=dbh.cursor () #建立一个cursor对象, return data to dictionary form
A=raw_input (' table list: ') # Enter table name
Cur.execute ("CREATE table%s (myID integer unique,filename varchar (255))"%a) # Generate 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 the rows in the result set
For row in rows:
Print row
Dbh.commit () #以上对数据库的操作事务生效
Dbh.close ()

1. Business

Most databases support transactions, and transactions can place multiple changes to the database in one command. In the example above, when the commit () command is not executed, none of the above operations on the database takes effect. There is also a function rollback (), which can effectively discard changes after the last commit () or rollback (). This function works very well when it finds errors and wants to discard transactions that have already been issued. For databases that do not support transactions, changes are executed immediately, commit () do nothing, but rollback () will complain.

2. Efficiency

The performance of the event depends on a large number of different servers, generally speaking, after each individual command is submitted is the slowest way to update the database, but if a large number of submissions will cause a buffer overflow server. Therefore, the number of submissions should be handled reasonably.

Four, the parameter style
In the example above, the same type format was used for printf (). But in fact, in Db-api, the different database supports the parameter wind is different, must choose the appropriate method, otherwise the program will not execute. The following method lets you know the type format that is currently supported.

Copy Code code as follows:

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

Pyformat This result shows that the Pyformat format is currently supported.

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

Qmark	Represents a Question-mark style. Each digit of the data in the instruction string is replaced with a question mark, which is given in the form of a list or tuple. For example: INSERT into ch14 VALUES (?,?).
Format	Uses the same type format as printf () and does not support the extension of Python for the specified parameter. It takes a list or tuple to convert. Example: INSERT into ch14 VALUES (%d,%s)
Numeric	Represents a numeric style. Each digit of the data in the instruction string is replaced by a colon followed by a number (the number begins with 1), and the argument is given in the form of a list or tuple. For example: INSERT into Ch14 VALUES (: 1,: 2)
Named	Represents a named style. Similar to numeric, but replaces numbers with names after a colon. 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. Repeat instructions
1, execute and Executemany ()

Example:

Insert the following data into the test database:

Twelve
Thirteen
Fourteen
Fifteen

(1) Execute strips insert

Copy Code code 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 method is too inefficient.

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

Copy Code code 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 two '},
{' 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 the instructions need to be executed. If the data is large, this is a problem, it will occupy all the memory resources of the system. If Executemany () does not meet your needs, it is possible to achieve performance optimizations in addition to execute (). According to DB-API, the database backend can perform optimizations when execute () is called periodically. 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 does not expect execute () to run faster than Executemany (). But if you can't use Executemany (), this is the best option.

Vi. Fetchall, Fetchmany, fetchone access to data

Fetchall (self): receives all returned result rows.

Fetchmany (self, Size=none): Receives the size bar to return the result row. If the value of size is greater than the number of result rows returned, the Cursor.arraysize bar data is returned.

Fetchone (self): Returns a result row.

Vii. get metadata (meta data)

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

Calculate the number of lines
There are two kinds of methods, one is Len (), one is using ROWCOUNT.

Copy Code code 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 ()