python網路編程學習筆記(九)：資料庫用戶端 DB-API

一、DB-API概述

python支援很多不同的資料庫。由於不同的賣家伺服器導致和資料庫通訊的網路通訊協定各有不同。在python的早期版本中，每一種資料庫都帶有自己的python模組，所有這些模組以不同的方式工作，並提供不同的函數。這種方法不便於編寫能夠在多種資料庫伺服器類型中啟動並執行代碼，於是DB-API庫函數產生。在DB-API中，所有串連資料庫的模組即便是底層網路通訊協定不同，也會提供一個共同的介面。這一點和JAVA中的JDBC和ODBC類似。
DB-API下載地址：http://wiki.python.org/moin/DatabaseProgramming，目前版本是2.0，支援資料庫包括IBM DB2、Firebird (and Interbase) 、Informix、Ingres、MySQL、Oracle 、PostgreSQL 、SAP DB (also known as "MaxDB") 、Microsoft SQL Server 、Sybase 等。

二、資料庫連接

1、PostgreSQL

有幾個模組可以完成python與PostgreSQL的聯結，這裡主要介紹使用psycopg。
下載地址是：http://initd.org/psycopg/download/。如果沒有PostgreSQL，可以從以下地址下載：http://www.postgresql.org/。(關於PostgreSQL的安裝等更加詳細的介紹，可以見http://wenku.baidu.com/view/8e32d10c6c85ec3a87c2c500.html。)串連PostgreSQL資料庫：

代碼如下:

import psycopg2
print "connecting to test"##test為資料庫名
dbh=psycopg2.connect('dbname=test user=postgres')
print "connection successful"

2、MySQL

對於MySQL，python的介面是已知的MySQLdb或者MySQL-Python，下載地址：http://sourceforge.net/projects/mysql-python/。與PostgreSQL不同的是，MySQLdb connect()函數可以帶各種參數，具體如下：

參數	說明
user	使用者名稱，預設為當前登入使用者。
passwd	使用者密碼，沒有預設的。
db	串連的資料庫名。
host	資料庫主機名稱。
port	TCP連接埠，預設是3306。

舉例，串連test資料庫：

代碼如下:

import MySQLdb
print "connecting..."
dbh=MySQLdb.connect(db="test")
print "connection successful."
dbh.close()

三、簡單操作（以ＰostgreSQL為例）

這裡以ＰostgreSQL為例介紹建立表、查詢表等操作。例子中資料庫名為test，使用者名稱為postgres，輸入一個表名，向表中插入資料並進行查詢。具體如下，已進行了注示：

代碼如下:

import psycopg2
print "connecting to test"
dbh=psycopg2.connect('dbname=test user=postgres')
print "connection successful"
cur=dbh.cursor()#建立一個cursor對象，返回資料為字典形式
a=raw_input('table list:')＃輸入表名
cur.execute("CREATE TABLE %s(myid integer UNIQUE,filename varchar(255))" %a)＃產生表，包含一個欄位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()＃獲得結果集中的所有行
for row in rows:
print row
dbh.commit()#以上對資料庫的操作事務生效
dbh.close()

１、事務

多數資料庫支援事務，事務可以將多條對資料庫的改動放在一條命令中。在上面的例子中，當未曾執行commit()命令時，以上對資料庫的操作均不會生效。另外還有一個函數rollback()，這個函數可以有效放棄上一次執行commit()或者rollback()之後的改動。這個函數在發現錯誤，並想放棄已經發出的事務時，非常有效。對於不支援事務的資料庫，改變會立刻執行，commit()什麼也不做，但rollback()會報錯。

２、效率

執行事件的效能很大程式上取決於不同的伺服器，一般來說，在每個單獨的命令後都提交是更新資料庫最慢的方法，但如果一次提交很大資料又會使伺服器產生buffer溢出。因此，應該合理處理提交的數量。

四、參數風格
在上面的例子中，使用了printf()一樣的類型格式。但實際上，在DB-API中，不同的資料庫所支援的參數風絡不同，必須選擇合適的方法，否則程式不會執行。下面的方法，可以知道當前所支援的類型格式。

代碼如下:

>>> import psycopg2
>>> print psycopg2.paramstyle

pyformat這一結果可以看出，當前支援pyformat格式。

針對DB-API說明書，以使用頻度由小變大的順序介紹：

qmark	表示question-mark風格。指令字串中的資料的每一位都被用一個問號替換，參數以list或tuple的形式給出。例如：INSERT INTO ch14 VALUES (?, ?)。
format	使用和printf()一樣的類型格式，不支援對於指定參數Python的副檔名。它帶一個list或tuple來轉換。例如：INSERT INTO ch14 VALUES(%d, %s)
numeric	表示numeric風格。指令字串中的資料的每一位都被一個後面是數位冒號替換（數字以1開始），參數以list或tuple的形式給出。例如：INSERT INTO ch14 VALUES(:1, :2)
named	表示named風格。和numeric類似，但是在冒號後面用名稱取代數字。帶一個dictionary用來轉換。例如：INSERT INTO ch14 VALUES(:number, :text)
pyformat	支援Python風格的參數，帶dictionary用來轉換。例如：INSERT INTO ch14 VALUES(%(number)d, %(text)s)。

五、重複指令
１、execute和executemany()

例子：

將下面的資料插入到test資料庫中：

12 Twelve
13 Thirteen
14 Fourteen
15 Fifteen

（１）execute一條條插入

代碼如下:

cur.execute("INSERT INTO test VALUES (12, 'Twelve')")
cur.execute("INSERT INTO test VALUES (13, 'Thirteen')")
cur.execute("INSERT INTO test VALUES (14, 'Fourteen')")
cur.execute("INSERT INTO test VALUES (15, 'Fifteen')")

這種方法過於低效。

（２）executemany()函數帶一個指令和一列指令啟動並執行記錄。列表上的每條記錄要麼是一個list,要麼是一個dictionary。

代碼如下:

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()

executemany()主要的缺點是，在需要執行指令前把所有的記錄放在記憶體中。如果資料大的話，這就是一個問題，它會佔有系統的所有記憶體資源。如果executemany()不能滿足需要，那麼除了execute()之外，還是有可能取得效能最佳化的。根據DB-API說明，當execute()被周期性調用時，資料庫後端可以執行最佳化。但是它的第一個參數必須指向同一個對象，而不是一個含有相同值的字串，即在記憶體中的同一個字串對象。和executemany()一樣，這樣並不能保證最佳化，並且也不能期望execute()運行得比executemany()快。但是如果不能使用executemany()，這就是一個最好的選擇。

六、fetchall、fetchmany、fetchone擷取資料

fetchall(self):接收全部的返回結果行。

fetchmany(self, size=None):接收size條返回結果行.如果size的值大於返回的結果行的數量,則會返回cursor.arraysize條資料。

fetchone(self):返回一條結果行。

七、擷取metadata(中繼資料)

中繼資料的英文名稱是“Metadata"，它是“關於資料的資料”。如在上面的例子中，Metadata的結果為：

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=postgres')
print "connection successful"
cur=dbh.cursor()

cur.execute("SELECT * FROM asd")

for column in cur.description:
print column

dbh.close()

八、計算行數
方法有兩種，一種是用len()，一種是用rowcount。

代碼如下:

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#利用rowcount來計算行數
dbh.close()

