python網絡編程學習筆記(九)：數據庫客戶端 DB-API

2019-11-25 18:22:57

字體：大中小

來源：轉載

供稿：網友

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