6.8 與關(guān)系型數(shù)據(jù)庫(kù)的交互

問(wèn)題

你想在關(guān)系型數(shù)據(jù)庫(kù)中查詢、增加或刪除記錄。

解決方案

Python中表示多行數(shù)據(jù)的標(biāo)準(zhǔn)方式是一個(gè)由元組構(gòu)成的序列。例如：

stocks = [
    ('GOOG', 100, 490.1),
    ('AAPL', 50, 545.75),
    ('FB', 150, 7.45),
    ('HPQ', 75, 33.2),
]

依據(jù)PEP249，通過(guò)這種形式提供數(shù)據(jù)，可以很容易的使用Python標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)API和關(guān)系型數(shù)據(jù)庫(kù)進(jìn)行交互。所有數(shù)據(jù)庫(kù)上的操作都通過(guò)SQL查詢語(yǔ)句來(lái)完成。每一行輸入輸出數(shù)據(jù)用一個(gè)元組來(lái)表示。

為了演示說(shuō)明，你可以使用Python標(biāo)準(zhǔn)庫(kù)中的 sqlite3 模塊。如果你使用的是一個(gè)不同的數(shù)據(jù)庫(kù)(比如MySql、Postgresql或者ODBC)，還得安裝相應(yīng)的第三方模塊來(lái)提供支持。不過(guò)相應(yīng)的編程接口幾乎都是一樣的，除了一點(diǎn)點(diǎn)細(xì)微差別外。

第一步是連接到數(shù)據(jù)庫(kù)。通常你要執(zhí)行 connect() 函數(shù)，給它提供一些數(shù)據(jù)庫(kù)名、主機(jī)、用戶名、密碼和其他必要的一些參數(shù)。例如：

>>> import sqlite3
>>> db = sqlite3.connect('database.db')
>>>

為了處理數(shù)據(jù)，下一步你需要?jiǎng)?chuàng)建一個(gè)游標(biāo)。一旦你有了游標(biāo)，那么你就可以執(zhí)行SQL查詢語(yǔ)句了。比如：

>>> c = db.cursor()
>>> c.execute('create table portfolio (symbol text, shares integer, price real)')
<sqlite3.Cursor object at 0x10067a730>
>>> db.commit()
>>>

為了向數(shù)據(jù)庫(kù)表中插入多條記錄，使用類似下面這樣的語(yǔ)句：

>>> c.executemany('insert into portfolio values (?,?,?)', stocks)
<sqlite3.Cursor object at 0x10067a730>
>>> db.commit()
>>>

為了執(zhí)行某個(gè)查詢，使用像下面這樣的語(yǔ)句：

>>> for row in db.execute('select * from portfolio'):
...     print(row)
...
('GOOG', 100, 490.1)
('AAPL', 50, 545.75)
('FB', 150, 7.45)
('HPQ', 75, 33.2)
>>>

如果你想接受用戶輸入作為參數(shù)來(lái)執(zhí)行查詢操作，必須確保你使用下面這樣的占位符?來(lái)進(jìn)行引用參數(shù)：

>>> min_price = 100
>>> for row in db.execute('select * from portfolio where price >= ?',
                          (min_price,)):
...     print(row)
...
('GOOG', 100, 490.1)
('AAPL', 50, 545.75)
>>>

討論

在比較低的級(jí)別上和數(shù)據(jù)庫(kù)交互是非常簡(jiǎn)單的。你只需提供SQL語(yǔ)句并調(diào)用相應(yīng)的模塊就可以更新或提取數(shù)據(jù)了。雖說(shuō)如此，還是有一些比較棘手的細(xì)節(jié)問(wèn)題需要你逐個(gè)列出去解決。

一個(gè)難點(diǎn)是數(shù)據(jù)庫(kù)中的數(shù)據(jù)和Python類型直接的映射。對(duì)于日期類型，通常可以使用 datetime 模塊中的 datetime 實(shí)例，或者可能是 time 模塊中的系統(tǒng)時(shí)間戳。對(duì)于數(shù)字類型，特別是使用到小數(shù)的金融數(shù)據(jù)，可以用 decimal 模塊中的 Decimal 實(shí)例來(lái)表示。不幸的是，對(duì)于不同的數(shù)據(jù)庫(kù)而言具體映射規(guī)則是不一樣的，你必須參考相應(yīng)的文檔。

另外一個(gè)更加復(fù)雜的問(wèn)題就是SQL語(yǔ)句字符串的構(gòu)造。你千萬(wàn)不要使用Python字符串格式化操作符(如%)或者 .format() 方法來(lái)創(chuàng)建這樣的字符串。如果傳遞給這些格式化操作符的值來(lái)自于用戶的輸入，那么你的程序就很有可能遭受SQL注入攻擊(參考 http://xkcd.com/327 )。查詢語(yǔ)句中的通配符？指示后臺(tái)數(shù)據(jù)庫(kù)使用它自己的字符串替換機(jī)制，這樣更加的安全。

不幸的是，不同的數(shù)據(jù)庫(kù)后臺(tái)對(duì)于通配符的使用是不一樣的。大部分模塊使用?或%s，還有其他一些使用了不同的符號(hào)，比如:0或:1來(lái)指示參數(shù)。同樣的，你還是得去參考你使用的數(shù)據(jù)庫(kù)模塊相應(yīng)的文檔。一個(gè)數(shù)據(jù)庫(kù)模塊的 paramstyle 屬性包含了參數(shù)引用風(fēng)格的信息。

對(duì)于簡(jiǎn)單的數(shù)據(jù)庫(kù)數(shù)據(jù)的讀寫問(wèn)題，使用數(shù)據(jù)庫(kù)API通常非常簡(jiǎn)單。如果你要處理更加復(fù)雜的問(wèn)題，建議你使用更加高級(jí)的接口，比如一個(gè)對(duì)象關(guān)系映射ORM所提供的接口。類似 SQLAlchemy 這樣的庫(kù)允許你使用Python類來(lái)表示一個(gè)數(shù)據(jù)庫(kù)表，并且能在隱藏底層SQL的情況下實(shí)現(xiàn)各種數(shù)據(jù)庫(kù)的操作。