第十三章數據庫支持 · Python 精要教程

# 第十三章數據庫支持 > 來源：http://www.cnblogs.com/Marlowes/p/5537223.html > 作者：Marlowes 使用簡單的純文本文件只能實現有限的功能。沒錯，使用它們可以做很多事情，但有時需要額外的功能。你可能想要自動序列化，這時可以選擇`shelve`模塊(見第十章)和`pickle`(與`shelve`模塊關系密切)。但有時，可能需要比這更強大的特性。例如，可能想自動地支持數據并發訪問——想讓幾個用戶同時對基于磁盤的數據進行讀寫而不造成任何文件損壞這類的問題。或者希望同時使用多個數據字段或屬性進行復雜的搜索，而不是只通過`shelve`做簡單的單鍵查找。解決的方案有很多，但是如果要處理的數據量巨大而同時還希望其他程序員能輕易理解的話，選擇相對來說更標準化的_數據庫_(database)可能是個好主意。本章會對Python的Database API進行討論，這是一種連接SQL數據庫的標準化方法；同時也會展示如何用API執行一些基本的SQL命令。最后一節會對其他可選的數據庫技術進行討論。我不打算把這章寫成關系型數據庫或SQL語言的教程。多數數據庫的文檔(比如PostgreSQL、MySQL，以及本章用到的SQLite數據庫)都應該能提供相關的基礎知識。如果以前沒用過關系型數據庫，也可以訪問 http://www.sqlcourse.com ，或者干脆網上搜一下相關主題，或查看由Clare Churcher著的_Beginning SQL Queries_(Apress，2008年出版)。當然，本章使用的簡單數據庫(SQLite)并不是唯一的選擇。還有一些流行的商業數據庫(比如Oracle或Microsoft SQL Server)以及很多穩定且被廣泛使用的開源數據庫可供選擇(比如MySQL、PostgreSQL和Firebird)。第二十六章中使用了PostgreSQL，并且介紹了一些MySQL和SQLite的使用指導。關于其他Python包支持的數據庫，請訪問 http://www.python.org/topics/database/ ，或者訪問[Vaults of Parnassus的數據庫分類](http://www.vex.net/parnassus)。關系型(SQL)數據庫不是唯一的數據庫類別。還有不少類似于[ZODB](http://wiki.zope.org/ZODB)的對象數據庫、類似[Metakit](http://www.equi4.com/metakit/python.html)基于表的精簡數據庫，和類似于[BSD DB](http://docs.python.org/lib/module-bsddb.html)的更簡單的_鍵-值_數據庫。本章著重介紹低級數據庫的交互，你會發現幾個高級庫可以幫助完成一些復雜的工作(例如，參見 http://www.sqlalchemy.org 或者 http://www.sqlobject.org ，或者在網絡上搜索Python的對象-關系映射)。 ## 13.1 Python數據庫API 支持SQL標準的可用數據庫有很多，其中多數在Python中都有對應的客戶端模塊(有些數據庫甚至有多個模塊)。所有數據庫的大多數基本功能都是相同的，所以寫一個程序來使用其中的某個數據庫是很容易的事情，而且“理論上”該程序也應該能在別的數據庫上運行。在提供相同功能(基本相同)的不同模塊之間進行切換時的問題通常是它們的接口(API)不同。為了解決Python中各種數據庫模塊間的兼容問題，現在已經通過了一個標準的DB API。目前的API版本(2.0)定義在PEP249中的[Python Database API Specification v2.0](http://python.org/peps/pep-0249.html)中。本節將對基本概念做一綜述。并且不會提到API的可選部分，因為它們不見得對所有數據庫都適用。可以在PEP中找到更多的信息，或者可以訪問官方的Python維基百科中的[數據庫編程指南](http://wiki.python.org/moin/DatabaseProgramming)。如果對API的細節不感興趣，可以跳過本節。 ### 13.1.1 全局變量任何支持2.0版本DB API的數據庫模塊都必須定義3個描述模塊特性的全局變量。這樣做的原因是API設計得很靈活，以支持不同的基礎機制、避免過多包裝，可如果想讓程序同時應用于幾個數據庫，那可是件麻煩事了，因為需要考慮到各種可能出現的狀況。多數情況下，比較現實的做法是檢查這些變量，看看給定的數據庫模塊是否能被程序接受。如果不能，就顯示合適的錯誤信息然后退出，例如拋出一些異常。3種全局變量如表13-1所示。表13-1 Python DB API的模塊特性 ``` apilevel　　　　　　　　　　　所使用的Python DB API版 threadsafety　　　　　　　　　模塊的線程安全等級 paramstyle　　　　　　　　　　在SQL查詢中使用的參數風格 ``` API級別(`apilevel`)是個字符串常量，提供正在使用的API版本號。對DBAPI 2.0版本來說，其值可能是'1.0'也可能是'2.0'。如果這個變量不存在，那么模塊就不適用于2.0版本，根據API應該假定當前使用的是DB API 1.0。在程序中提供對其他可能值的支持沒有壞處，誰知道呢，說不定什么時候DBAPI的3.0版本就出來了。線程安全性等級(`threadsafety`)是個取值范圍為0~3的整數。0表示線程完全不共享模塊，而3表示模塊是完全線程安全的。1表示線程本身可以共享模塊，但不對連接共享(參見13.1.3節)。如果不使用多個線程(多數情況下可能不會這樣做)，那么完全不用擔心這個變量。參數風格(`paramstyle`)表示在執行多次類似查詢的時候，參數是如何被拼接到SQL查詢中的。值`'format'`表示標準的字符串格式化(使用基本的格式代碼)，可以在參數中進行拼接的地方插入`%s`。而值`'pyformat'`表示擴展的格式代碼，用于字典拼接中，比如`%(foo)`。除了Python風格之外，還有第三種接合方式：`'qmark'`的意思是使用問號，而`'numeric'`表示使用`:1`或者`:2`格式的字段(數字表示參數的序號)，而`'named'`表示`:foobar`這樣的字段，其中`foobar`為參數名。如果參數風格看起來有些讓人迷惑，別擔心。對于基礎程序來說，不會用到這些參數，如果需要了解特定的數據庫接口如何處理參數，在相關的文檔中會進行解釋。 ### 13.1.2 異常為了能盡可能準確地處理錯誤，API中定義了一些異常類。它們被定義在一種層次結構中，所以可能通過一個`except`塊捕捉多種異常。(當然要是你覺得一切都能運行良好，或者根本不在乎程序因為某些事情出錯這類不太可能發生的時間而突然停止運行，那么完全可以忽略這些異常) 異常的層次如表13-2所示。在給定的數據庫模塊中異常應該是全局可用的。關于這些異常的深度描述，請參見API規范(也就是前面提到的PEP)。表13-2 在DB API中使用的異常 ``` 異常　　　　　　　　　　超類　　　　　　　　　　描述 StandardError　　　　　　　　　　　　　　　　　所有異常的泛型基類 Warning　　　　　　　　 StandardError　　　　　在非致命錯誤發生時引發 Error　　　　　　　　　 StandardError　　　　　所有錯誤條件的泛型超類 InterfaceError　　　　　Error　　　　　　　　　關于接口而非數據庫的錯誤 DatabaseError　　　　　 Error　　　　　　　　　與數據庫相關的錯誤的基類 DataError　　　　　　　 DatabaseError　　　　　與數據庫相關的問題，比如值超出范圍 OperationalError　　　　DatabaseError　　　　　數據庫內部操作錯誤 IntegrityError　　　　　DatabaseError　　　　　關系完整性受到影響，比如鍵檢查失敗 InternalError　　　　　 DatabaseError　　　　　數據庫內部錯誤，比如非法游標 ProgrammingError　　　　DatabaseError　　　　　用戶編程錯誤，比如未找到表 NotSupportedError　　　 DatabaseError　　　　　請求不支持的特性，比如回滾 ``` ### 13.1.3 連接和游標為了使用基礎數據庫系統，首先必須連接到它。這個時候需要使用具有恰當名稱的`connect`函數，該函數有多個參數，而具體使用哪個參數取決于數據庫。API定義了表13-3中的參數作為準則，推薦將這些參數作為關鍵字參數使用，并按表中給定的順序傳遞它們。參數類型都應為字符串。表13-3 connect函數的常用參數 ``` 參數名　　　　　　　　　　描述　　　　　　　　　　是否可選 dsn　　　　　　　　　　　數據源名稱，給出該參數表示數據庫依賴　　　否 user　　　　　　　　　　　用戶名　　　　　　　　　　　　　　　　　　是 password　　　　　　　　用戶密碼　　　　　　　　　　　　　　　　是 host　　　　　　　　　　　主機名　　　　　　　　　　　　　　　　　　是 database　　　　　　　　　數據庫名　　　　　　　　　　　　　　　　是 ``` 13.2.1節以及第二十六章會介紹使用`connect`函數的具體的例子。 `connect`函數返回連接對象。這個對象表示目前和數據庫的會話。連接對象支持的方法如表13-4所示。 13-4 連接對象方法 ``` close()　　　　　　　　　　　　　　　　　　　關閉連接之后，連接對象和它的游標均不可用 commit()　　　　　　　　　　　　　　　　　　　如果支持的話就提交掛事務，否則不做任何事 rollback()　　　　　　　　　　　　　　　　　　回滾掛起的事務(可能不可用) cursor()　　　　　　　　　　　　　　　　　　　返回連接的游標對象 ``` `rollback`方法可能不可用，因為不是所有的數據庫都支持事務(_事務_是一系列動作)。如果可用，那么它就可以“撤銷”所有未提交的事務。 `commit`方法總是可用的，但是如果數據庫不支持事務，它就沒有任何作用。如果關閉了連接但還有未提交的事務，它們會隱式地回滾——但是只有在數據庫支持回滾的時候才可以。所以如果不想完全依靠隱式回滾，就應該每次在關閉連接前進行提交。如果提交了，那么就用不著擔心關閉連接的問題，它會在進行垃圾收集時自動關閉。當然如果希望更安全一些，就調用`close`方法，也不會敲很多次鍵盤。 `cursor`方法將我們引入另外一個主題：游標對象。通過游標執行SQL查詢并檢查結果。游標比連接支持更多的方法，而且可能在程序中更好用。表13-5給出了游標方法的概述，表13-6則是特性的概述。表13-5 游標對象方法 ``` callproc(name[, params])　　　　使用給定的名稱和參數(可選)調用已命名的數據庫程序 close()　　　　　　　　　　　　關閉游標之后，游標不可用 execute(oper[, params])　　　　執行SQL操作，可能使用參數 executemany(oper, pseq)　　　　對序列中的每個參數執行SQL操作 fetchone()　　　　　　　　　　　把查詢的結果集中的下一行保存為序列，或者None fetchmany([size])　　　　　　　獲取查詢結果集中的多行，默認尺寸為arraysize fetchall()　　　　　　　　　　　將所有(剩余)的行作為序列的序列 nextset()　　　　　　　　　　　跳至下一個可用的結果集(可選) setinputsizes(sizes)　　　　　　為參數預先定義內存區域 setoutputsize(size[, col])　　　為獲取的大數據值設定緩沖區尺寸 ``` 表13-6 游標對象特性 ``` description　　　　　　　　　結果列描述的序列，只讀 rowcount　　　　　　　　　　　結果中的行數，只讀 arraysize　　　　　　　　　　 fetchmany中返回的行數，默認為1 ``` 其中一些方法會在下面詳細介紹，而有些(比如`setinputsizes`和`setoutputsizes`)則不會提到。更多細節請查閱PEP。 ### 13.1.4 類型數據庫對插入到具有某種類型的列中的值有不同的要求，是為了能正確地與基礎SQL數據庫進行交互操作，DB API定義了用于特殊類型和值的構造函數以及常量(單例模式)。例如，如果想要在數據庫中增加日期，它應該用相應的數據庫連接模塊的`Date`構造函數來建立。這樣數據庫連接模塊就可以在幕后執行一些必要的轉換操作。所有模塊都要求實現表13-7中列出的構造函數和特殊值。一些模塊可能不是完全按照要求去做，例如`sqlite3`模塊(接下來會討論)并不會輸出表13-7中的特殊值(通過`ROWIP`輸出`STRING`)。表13-7 DB API構造函數和特殊值 ``` Date(year, month, day)　　　　　　　　創建保存日期值的對象 Time(hour, minute, second)　　　　　　創建保存時間值的對象 Timestamp(y, mon, d, h, min, s)　　　創建保存時間戳值的對象 DateFromTicks(ticks)　　　　　　　　　創建保存自新紀元以來秒數的對象 TimeFromTicks(ticks)　　　　　　　　　創建保存來自秒數的時間值的對象 TimestampTicks(ticks)　　　　　　　　創建保存來自秒數的時間戳的對象 Binay(string)　　　　　　　　　　　創建保存二進制字符串值的對象 STRING　　　　　　　　　　　　　　　　描述基于字符串的列類型(比如CHAR) BINARY　　　　　　　　　　　　　　　　描述二進制列(比如LONG或RAW) NUMBER　　　　　　　　　　　　　　　　描述數字列 DATETIME　　　　　　　　　　　　　　　描述日期/時間列 ROWID　　　　　　　　　　　　　　　　描述行ID列 ``` ## 13.2 SQLite和PySQLite 之前提到過，可用的SQL數據庫引擎有很多，而且都有相應的Python模塊。多數數據庫引擎都作為服務器程序運行，連安裝都需要管理員權限。為了降低練習Python DB API的門檻，這里選擇了小型的數據庫引擎SQLite，它并不需要作為獨立的服務器運行，并且不基于集中式數據庫存儲機制，而是直接作用于本地文件。在最近的Python版本中(從2.5開始)，SQLite的優勢在于它的一個包裝(PySQLite)已經被包括在標準庫內。除非是從源碼開始編譯Python，可能數據庫本身也已經包括在內。讀者也可以嘗試13.2.1節介紹的程序段。如果它們可以工作，那么就不用單獨安裝PySQLite和SQLite了。 _注：如果讀者沒有使用PySQLite的標準庫版本，那么可能還需要修改`import`語句，請參考相關文檔獲取更多信息。_ **獲取PySQLite** 如果讀者正在使用舊版Python，那么需要在使用SQLite數據庫前安裝PySQLite，可以從官方網站下載。對于帶有包管理系統的Linux系統，可能直接從包管理器章獲得PYSQLite和SQLite。針對PYSQLite的Windows二進制版本實際上包含了數據庫引擎(也就是SQLite)，所以只要下載對應Python版本的PYSQLite安裝程序，運行就可以了。如果使用的不是Windows，而操作系統也沒有可以找到PYSQLite和SQLite的包管理器的話，那么就需要PYSQLite和SQLite的源代碼包，然后自己進行編譯。如果使用的Python版本較新，那么應該已經包含PySQLite。接下來需要的可能就是數據庫本身SQLite了(同樣，它可能也包含在內了)。可以從SQLite的網站 http://sqlite.org 下載源代碼(確保得到的是已經完成自動代碼生成的包)，按照README文件中的指導進行編譯即可。在之后編譯PYSQLite時，需要確保編譯過程可以訪問SQLite的庫文件和include文件。如果已經在某些標準位置安裝了SQLite，那么可能SQLite發布版的安裝腳本可以自己找到它，在這種情況下只需執行下面的命令： ``` python setup.py build python setup.py install ``` 可以只用后一個命令，讓編譯自動進行。如果出現大量錯誤信息，可能是安裝腳本找不到所需文件。確保你知道庫文件和`include`文件安裝到了哪里，將它們顯式地提供給安裝腳本。假設我在`/home/mlh/sqlite/current`目錄中原地編譯SQLite，那么頭文件和庫文件應該可以在`/home/mlh/sqlite/current/src`和`/home/mlh/sqlite/current/build/lib`中找到。為了讓安裝程序能使用這些路徑，需要編輯安裝腳本`setup.py`。在這個文件中可以設定變量`include_dirs`和`library_dirs`： ``` include_dirs = ['/home/mlh/sqlite/current/src'] include_dirs = ['/home/mlh/sqlite/current/build/lib'] ``` 在重新綁定變量之后，剛才說過的安裝過程應該可以正常進行了。 ### 13.2.1 入門可以將SQLite作為名為`sqlite3`的模塊導入(如果使用的是標準庫中的模塊)。之后就可以創建一個到數據庫文件的連接——如果文件不存在就會被創建——通過提供一個文件名(可以是文件的絕對或者相對路徑)： ``` >>> import sqlite3 >>> conn = sqlite3.connect("somedatabase.db") ``` 之后就能獲得連接的游標： ``` >>> curs = conn.cursor() ``` 這個游標可以用來執行SQL查詢。完成查詢并且做出某些更改后確保已經進行了提交，這樣才可以將這些修改真正地保存到文件中： ``` >>> conn.commit() ``` 可以(而且是應該)在每次修改數據庫后都進行提交，而不是僅僅在準備關閉時才提交，準備關閉數據庫時，使用`close`方法： ``` >>> conn.close() ``` ### 13.2.2 數據庫應用程序示例我會建立一個小型營養成分數據庫作為示例程序，這個程序基于[USDA的營養數據實驗室提供的數據](http://www.ars.usda.gov/nutrientdata)。在他們的主頁上點擊USDA National Nutrient Database for Standard Reference鏈接，就能看到很多以普通文本形式(ASCII)保存的數據文件，這就是需要的內容。點擊Download鏈接，下載標題"Abbreviated"下方的ASCII鏈接所指向的ASCII格式的`zip`文件。此時應該得到一個`zip`文件，其中包含`ABBREV.txt`文本文件和描述該文件內容的PDF文件。 `ABBREV.txt`文件中的數據每行都有一個數據記錄，字段以脫字符(`^`)進行分割。數字字段直接包含數字，而文本字段包括由波浪號(`~`)括起來的字符串值，下面是一個示例行，為了簡短起見刪除了一部分： ``` ~01252~^~CHEESE ... ,PAST PROCESS, ... ^~1 slice, (3/4 oz)~^0 ``` 用`line.split("^")`可以很容易地將這樣一行文字解析為多個字段。如果字段以波浪號開始，就能知道它是個字符串，可以用`field.strip("~")`獲取它的內容。對于其他的(數字)字段來講可以使用`float(field`)，除非字段是空的。下面一節中的程序將演示把ASCII文件中的數據移入SQL數據庫，然后對其進行一些有意思的查詢。 _注：這個示例程序有意提供一個簡單的例子。有關相對高級的用于Python的數據庫的例子，參見第二十六章。_ 1.創建和填充表為了真正地創建數據庫表并且向其中插入數據，寫個完全獨立的一次性程序可能是最簡單的方案。運行一次后就可以忘了它和原始數據源(`ABBREV.txt`文件)，盡管保留它們也是不錯的主意。代碼清單13-1中的程序創建了叫做`food`的表和適當的字段，并且從`ABBREV.txt`中讀取數據。之后分解析(行分解為多個字段，并使用應用函數`convert`每個字段進行轉換)，然后通過調用`curs.execute`執行SQL的`INSERT`語句將文本字段中的值插入到數據庫中。 _注：也可以使用`curs.executemany`，然后提供一個從數據文件中提取的所有行的列表。這樣做在本例中只會帶來輕微的速度提升，但是如果使用通過網絡連接的客戶機/服務器SQL系統，則會大大地提高速度。_ ``` import sqlite3 def convert(value): if value.startwith("~"): return value.strip("~") if not value: value = 0 return float(value) conn = sqlite3.connect("foo.db") curs = conn.cursor() curs.execute(""" CREATE TABLE food ( id TEXT PRIMARY KEY, desc TEXT, water FLOAT, kcal FLOAT, protein FLOAT, fat FLOAT, ash FLOAT, carbs FLOAT, fiber FLOAT, sugar FLOAT ) """) query = "INSERT INTO food VALUES (?,?,?,?,?,?,?,?,?,?)" for line in open("ABBREV.txt"): fields = line.split("^") vals = [convert(f) for f in fields[:field_count]] curs.execute(query, vals) conn.commit() conn.close() ``` `importdata.py` _注：在代碼清單13-1中使用了`paramstyle`的“問號”版本，也就是會使用問號作為字段標記。如果使用舊版本的PySQLite，那么久需要使用`%`字符。_ 當運行這個程序(將`ABBREV.txt`放在同一目錄)時，它會創建一個叫做`food.db`的新文件，它會包含數據庫中的所有數據。鼓勵讀者們多嘗試修改這個例子，例如使用其他的輸入、加入`print`語句，等等。 2.搜索和處理結果使用數據庫很簡單。再說一次，需要創建連接并且獲得該鏈接的游標。使用`execute`方法執行SQL查詢，用`fetchall`等方法提取結果。代碼清單13-2展示了一個將SQL SELECT條件查詢作為命令行參數，之后按記錄格式打印出返回行的小程序。可以用下面的命令嘗試這個程序： ``` $ python food_query.py "kcal <= 100 AND fiber >= 10 ORDER BY sugar" ``` 運行的時候可能注意到有個問題。第一行，生橘子皮(raw orange peel)看起來不含任何糖分(糖分值為0)。這是因為在數據文件中這個字段丟失了。可以改進剛才的導入腳本檢測條件，然后插入None來代替真實的值來表示丟失的數據。可以使用如下條件： ``` "kcal <= 100 AND fiber >= 10 AND sugar ORDER BY sugar" ``` 請求在任何返回行中包含實際數據的“糖分”字段。這方法恰好也適用于當前的數據庫，它會忽略糖分為0的行。 _注：使用ID搜索特殊的食品項，比如用08323搜索Cocoa Pebble的時候可能會出現問題。原因在于SQLite以一種相當不標準的方式處理它的值。在其內部所有的值實際上都是字符串，一些轉換和檢查在數據庫和Python API間進行。通常它工作得很順利，但有時候也會出錯，例如下面這種情況：如果提供值08323，它會被解釋為數字8323，再轉換為字符串"8323"——一個不存在的ID。可能期待這里拋出異常或者其他什么錯誤信息，而不是這種毫無用處的非預期行為。但如果小心一些，一開始就用字符串"08323"來表示ID，就可以工作正常了。_ ``` import sqlite3 import sys conn = sqlite3.connect("foo.db") curs = conn.cursor() query = "SELECT * FROM food WHERE %s" % sys.argv[1] print query curs.execute(query) names = [f[0] for f in curs.description] for row in curs.fetchall(): for pair in zip(names, row): print "%s: %s" % pair print ``` `food_query.py` ## 13.3 小結本章簡要介紹了創建和關系型數據庫交互的Python程序。這段介紹相當簡短，因為掌握了Python和SQL以后，那么兩者的結合——Python DB API也就容易掌握了。下面是本章一些概念。 ? Python DB API：提供了簡單、標準化的數據庫接口，所有數據庫的包裝模塊都應當遵循這個接口，以易于編寫跨數據庫的程序。 ? 連接：連接對象代表的是和SQL數據庫的通信連接。使用`cursor`方法可以從它那獲得獨立的游標。通過連接對象還可以提交或者回滾事務。在處理完數據庫之后，連接可以被關閉。 ? 游標：用于執行查詢和檢查結果。結果行可以一個一個地獲得，也可以很多個(或全部)一起獲得。 ? 類型和特殊值：DB API標準制定了一組構造函數和特殊值的名字。構造函數處理日期和時間對象，以及二進制數據對象。特殊值用來表示關系型數據庫的類型，比如`STRING`、`NUMBER`和`DATETIME`。 ? SQLite：小型的嵌入式SQL數據庫，它的Python包裝叫做PYSQLite。它速度快，易于使用，并且不需要建立單獨的服務器。 ### 13.3.1 本章的新函數本章涉及的新函數如表13-8所示。表13-8 本章的新函數 ``` connect(...) 連接數據庫，返回連接對象 ``` ### 13.3.2 接下來學什么堅持不懈數據庫處理是絕大多數程序(如果不是大多數，那就是大型程序系統)的重要部分。下一章會介紹另外一個大型程序系統都會用到的組件，即網絡。