Python network programming learning notes (9): Database Client

ArticleDirectory

• 1. DB-API Overview
• 2. Database Connection
• Iii. Simple operations (taking PostgreSQL as an example)
• Iv. Parameter Style
• 5. Duplicate commands
• 6. fetchall, fetchtasks, and fetchone to obtain data
• VII. Obtain metadata)
• 8. Number of calculated rows

Reprint Please note: @ small Wuyi http://www.cnblogs.com/xiaowuyi

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 compile and run in multiple database server typesCodeSo 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 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:

ImportPsycopg2Print "Connecting to test"## Test indicates the database name.DBH = psycopg2.connect ('Dbname = test user = 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:

ImportMysqldbPrint "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:

 Import  Psycopg2 Print   "  Connecting to test  "  DBH = Psycopg2.connect ( '  Dbname = test user = s  '  )  Print   "  Connection successful  "  Cur = DBH. cursor () #  Create a cursor object and return data in dictionary form A = raw_input ( '  Table list:  '  )#Input table name cur.exe cute (  "  Create Table % s (myid integer unique, filename varchar (255 ))  " % A) # generate a table that contains a field filenameb = 1

C = '  201210310540  '  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  " %) #  Query table content Rows = Cur. fetchall () # obtain all rows in the result set  For Row In  Rows:  Print  Rowdbh. 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

High Performance of event executionProgramIt depends on different servers. Generally, after each command is submitted, it is the slowest method to update the database. However, if a large amount of data is submitted at a time, the server will cause 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.

 
>>>ImportPsycopg2>>>PrintPsycopg2.paramstylepyformat
 

This result shows that the pyformat format 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

Cur.exe cute ("Insert into test values (12, 'twelve ')"Export cur.exe cute ("Insert into test values (13, 'thirteen ')"Export cur.exe cute ("Insert into test values (14, 'Fourteen ')"Export 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.

 Import Psycopg2  Print   "  Connecting to test  "  DBH = Psycopg2.connect ( '  Dbname = test user = 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 cute.pdf (  "  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)

 Import  Psycopg2  Print   "  Connecting to bbstime  " DBH = Psycopg2.connect ( '  Dbname = bbstime user = 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.

 Import  Psycopg2  Print   "  Connecting to bbstime  "  DBH = Psycopg2.connect ( '  Dbname = bbstime user = 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 ()