數(shù)據持久性 | Data Persistence sqlite3

2.5版本中的新功能(néng)。

SQLite是一(yī)個C庫，它提供了(le)一(yī)個輕量級的基于磁盤的數(shù)據庫，它不需要(yào)單獨的服務器進程，并允許使用SQL查詢語言的非标準變體訪問(wèn)數(shù)據庫。一(yī)些應用程序可(kě)以使用SQLite進行(xíng)內(nèi)部數(shù)據存儲。也可(kě)以使用SQLite對應用程序進行(xíng)原型設計，然後将代碼移植到更大的數(shù)據庫，如(rú)PostgreSQL或Oracle。

sqlite3模塊由GerhardHäring編寫。它提供了(le)一(yī)個符合PEP 249描述的DB-API 2.0規範的SQL接口。

要(yào)使用該模塊，您必須首先創建一(yī)個Connection代表數(shù)據庫的對象。這(zhè)裏的數(shù)據将被存儲在example.db文件中：

import sqlite3

conn = sqlite3.connect('example.db')

複制

您還可(kě)以提供特殊名稱:memory:以在RAM中創建數(shù)據庫。

一(yī)旦你有了(le)Connection，你可(kě)以創建一(yī)個Cursor對象并調用它的execute()方法來執行(xíng)SQL命令：

c = conn.cursor()

# Create table

c.execute('''CREATE TABLE stocks

(date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data

c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes

conn.commit()

# We can also close the connection if we are done with it.

# Just be sure any changes have been committed or they will be lost.

conn.close()

複制

您保存的數(shù)據是持久性數(shù)據，可(kě)在以後的會話中使用：

import sqlite3

conn = sqlite3.connect('example.db')

c = conn.cursor()

複制

通(tōng)常你的SQL操作(zuò)需要(yào)使用Python變量的值。你不應該使用Python的字符串操作(zuò)來組裝你的查詢，因為(wèi)這(zhè)樣做(zuò)是不安全的; 它會使您的程序容易受到SQL注入攻擊（有關可(kě)能(néng)出錯的幽默示例，請(qǐng)參閱https://xkcd.com/327/）。

而是使用DB-API的參數(shù)替換。将?作(zuò)為(wèi)占位符，無論你想使用的值，然後提供值的元組作(zuò)為(wèi)第二個參數(shù)光(guāng)标的execute()方法。（其他(tā)數(shù)據庫模塊可(kě)能(néng)使用不同的占位符，例如(rú)%s或:1）。例如(rú)：

# Never do this -- insecure!

symbol = 'RHAT'

c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead

t = ('RHAT',)

c.execute('SELECT * FROM stocks WHERE symbol=?', t)

print c.fetchone()

# Larger example that inserts many records at a time

purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),

('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),

('2006-04-06', 'SELL', 'IBM', 500, 53.00),

]

c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

複制

要(yào)在執行(xíng)SELECT語句後檢索數(shù)據，可(kě)以将遊标作(zuò)為(wèi)叠代器，調用遊标的fetchone()方法來檢索單個匹配的行(xíng)，或者調用fetchall()獲取匹配行(xíng)的列表。

這(zhè)個例子使用叠代器形式：

>>> for row in c.execute('SELECT * FROM stocks ORDER BY price'):

print row

(u'2006-01-05', u'BUY', u'RHAT', 100, 35.14)

(u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)

(u'2006-04-06', u'SELL', u'IBM', 500, 53.0)

(u'2006-04-05', u'BUY', u'MSFT', 1000, 72.0)

複制

1.模塊功能(néng)和(hé)常量

sqlite3.version

此模塊的版本号，作(zuò)為(wèi)字符串。這(zhè)不是SQLite庫的版本。

sqlite3.version_info

該模塊的版本号，作(zuò)為(wèi)整數(shù)的元組。這(zhè)不是SQLite庫的版本。

sqlite3.sqlite_version

運行(xíng)時(shí)SQLite庫的版本号，作(zuò)為(wèi)字符串。

sqlite3.sqlite_version_info

運行(xíng)時(shí)SQLite庫的版本号，作(zuò)為(wèi)整數(shù)的元組。

sqlite3.PARSE_DECLTYPES

該常數(shù)用于與函數(shù)的detect_types參數(shù)一(yī)起使用connect()。

設置它會使sqlite3模塊解析它返回的每一(yī)列的聲明(míng)類型。它會解析出聲明(míng)類型的第一(yī)個單詞，即對于“整數(shù)主鍵”，它将解析出“整數(shù)”，或者對于“編号（10）”，它将解析出“編号”。然後，對于該列，它将查看(kàn)轉換器字典并使用在那(nà)裏注冊的轉換器函數(shù)。

sqlite3.PARSE_COLNAMES

該常數(shù)用于與函數(shù)的detect_types參數(shù)一(yī)起使用connect()。

設置這(zhè)使得SQLite接口解析它返回的每一(yī)列的列名。它會在那(nà)裏尋找一(yī)個形成mytype的字符串，然後決定'mytype'是該列的類型。它會嘗試在轉換器字典中找到'mytype'條目，然後使用在那(nà)裏找到的轉換器函數(shù)返回值。找到的列名Cursor.description隻是列名的第一(yī)個單詞，也就是說，如(rú)果您'as "x [datetime]"'在SQL中使用類似的名稱，那(nà)麽我們将解析出所有內(nèi)容，直到列名稱的第一(yī)個空格為(wèi)止：列名稱将簡單地(dì)為(wèi)“x”。

sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements])

打開(kāi)到SQLite數(shù)據庫文件數(shù)據庫的連接。您可(kě)以使用":memory:"打開(kāi)數(shù)據庫連接到駐留在RAM而不是磁盤上(shàng)的數(shù)據庫。

當數(shù)據庫被多個連接訪問(wèn)時(shí)，其中一(yī)個進程修改了(le)數(shù)據庫，SQLite數(shù)據庫被鎖定，直到該事務被提交。該超時(shí)參數(shù)指定連接應該多長(cháng)時(shí)間(jiān)等待鎖消失，直到引發異常。超時(shí)參數(shù)的默認值是5.0（五秒）。

有關isolation_level參數(shù)，請(qǐng)參閱對象的Connection.isolation_level屬性Connection。

SQLite本機僅支持TEXT，INTEGER，REAL，BLOB和(hé)NULL類型。如(rú)果你想使用其他(tā)類型，你必須自(zì)己添加對它們的支持。該detect_types參數(shù)和(hé)使用自(zì)定義轉換器與模塊級的注冊register_converter()功能(néng)，讓你輕松做(zuò)到這(zhè)一(yī)點。

detect_types默認為(wèi)0（即關閉，沒有類型檢測），您可(kě)以将其設置為(wèi)任意組合PARSE_DECLTYPES并PARSE_COLNAMES打開(kāi)類型檢測。

默認情況下(xià)，sqlite3模塊将其Connection類用于連接調用。但(dàn)是，您可(kě)以繼承這(zhè)個Connection類，并connect()通(tōng)過為(wèi)工(gōng)廠參數(shù)提供您的類來使用您的類。

有關詳細信息，請(qǐng)參閱本手冊的SQLite和(hé)Python類型部分。

該sqlite3模塊在內(nèi)部使用一(yī)個語句緩存來避免SQL解析開(kāi)銷。如(rú)果要(yào)顯式設置為(wèi)連接緩存的語句數(shù)，可(kě)以設置cached_statements參數(shù)。目前實施的默認設置是緩存100條語句。

sqlite3.register_converter(typename, callable)

注冊一(yī)個可(kě)調用的字符串，将數(shù)據庫中的字符串轉換為(wèi)自(zì)定義的Python類型。可(kě)調用将為(wèi)類型為(wèi)typename的所有數(shù)據庫值調用。賦予參數(shù)detect_types中的connect()該類型檢測是如(rú)何工(gōng)作(zuò)的功能(néng)。請(qǐng)注意，查詢中typename和(hé)類型的名稱必須匹配！

sqlite3.register_adapter(type, callable)

注冊可(kě)調用以将自(zì)定義Python類型類型轉換為(wèi)SQLite支持的類型之一(yī)。可(kě)調用callable接受Python值作(zuò)為(wèi)單個參數(shù)，并且必須返回以下(xià)類型的值：int，long，float，str（UTF-8編碼），unicode或緩沖區(qū)。

sqlite3.complete_statement(sql)

返回True如(rú)果字符串SQL包含由分号終止一(yī)個或多個完整的SQL語句。它不驗證SQL在語法上(shàng)是否正确，隻是沒有未關閉的字符串文本，并且語句以分号結尾。

這(zhè)可(kě)以用來為(wèi)SQLite構建一(yī)個shell，如(rú)下(xià)例所示：

# A minimal SQLite shell for experiments

import sqlite3

con = sqlite3.connect(":memory:")

con.isolation_level = None

cur = con.cursor()

buffer = ""

print "Enter your SQL commands to execute in sqlite3."

print "Enter a blank line to exit."

while True:

line = raw_input()

if line == "":

break

buffer += line

if sqlite3.complete_statement(buffer):

try:

buffer = buffer.strip()

cur.execute(buffer)

if buffer.lstrip().upper().startswith("SELECT"):

print cur.fetchall()

except sqlite3.Error as e:

print "An error occurred:", e.args[0]

buffer = ""

con.close()

複制

sqlite3.enable_callback_tracebacks(flag)

默認情況下(xià)，你不會在用戶定義的函數(shù)，聚合，轉換器，授權者回調等中獲得任何回溯。如(rú)果你想調試它們，你可(kě)以調用此标志設置為(wèi)的函數(shù)True。之後，你會從(cóng)回調中獲得回溯sys.stderr。用于False再次禁用該功能(néng)。

2.連接對象

class sqlite3.Connection

SQLite數(shù)據庫連接具有以下(xià)屬性和(hé)方法：

isolation_level

獲取或設置當前的隔離級别。None用于自(zì)動提交模式或“延遲”，“立即”或“獨占”之一(yī)。有關更詳細的解釋，請(qǐng)參閱控制交易部分。

cursor(factory=Cursor)

遊标方法接受一(yī)個可(kě)選的參數(shù)工(gōng)廠。如(rú)果提供，它必須是可(kě)返回的實例Cursor或其子類。

commit()

此方法提交當前事務。如(rú)果您不調用此方法，則自(zì)從(cóng)上(shàng)次調用以來執行(xíng)的任何操作(zuò)commit()都(dōu)無法從(cóng)其他(tā)數(shù)據庫連接看(kàn)到。如(rú)果您想知道(dào)為(wèi)什麽您沒有看(kàn)到您寫入數(shù)據庫的數(shù)據，請(qǐng)檢查您是否忘記調用此方法。

rollback()

此方法回滾自(zì)上(shàng)次調用以來對數(shù)據庫所做(zuò)的任何更改commit()。

close()

這(zhè)将關閉數(shù)據庫連接。請(qǐng)注意，這(zhè)不會自(zì)動調用commit()。如(rú)果您隻是在不commit()先調用的情況下(xià)關閉數(shù)據庫連接，則更改将會丢失！

execute(sql[, parameters])

這(zhè)是一(yī)個非标準的快(kuài)捷方式，它通(tōng)過調用遊标方法創建中間(jiān)遊标對象，然後execute使用給定的參數(shù)調用遊标的方法。

executemany(sql[, parameters])

這(zhè)是一(yī)個非标準的快(kuài)捷方式，它通(tōng)過調用遊标方法創建中間(jiān)遊标對象，然後executemany使用給定的參數(shù)調用遊标的方法。

executescript(sql_script)

這(zhè)是一(yī)個非标準的快(kuài)捷方式，它通(tōng)過調用遊标方法創建中間(jiān)遊标對象，然後executescript使用給定的參數(shù)調用遊标的方法。

create_function(name, num_params, func)

創建一(yī)個可(kě)以從(cóng)SQL語句中以後使用下(xià)面的功能(néng)名稱的用戶定義函數(shù)的名稱。num_params是函數(shù)接受的參數(shù)的個數(shù)，func是一(yī)個可(kě)調用的Python，它被稱為(wèi)SQL函數(shù)。

該函數(shù)可(kě)以返回SQLite支持的任何類型：unicode，str，int，long，float，buffer和(hé)None。

例：

import sqlite3

import md5

def md5sum(t):

return md5.md5(t).hexdigest()

con = sqlite3.connect(":memory:")

con.create_function("md5", 1, md5sum)

cur = con.cursor()

cur.execute("select md5(?)", ("foo",))

print cur.fetchone()[0]

複制

create_aggregate(name, num_params, aggregate_class)

創建用戶定義的聚合函數(shù)。

聚合類必須實現一(yī)個step方法，該方法接受參數(shù)num_params的數(shù)量，以及一(yī)個finalize将返回聚合的最終結果的方法。

該finalize方法可(kě)以返回SQLite支持的任何類型：unicode，str，int，long，float，buffer和(hé)None。

例：

import sqlite3

class MySum:

def __init__(self):

self.count = 0

def step(self, value):

self.count += value

def finalize(self):

return self.count

con = sqlite3.connect(":memory:")

con.create_aggregate("mysum", 1, MySum)

cur = con.cursor()

cur.execute("create table test(i)")

cur.execute("insert into test(i) values (1)")

cur.execute("insert into test(i) values (2)")

cur.execute("select mysum(i) from test")

print cur.fetchone()[0]

複制

create_collation(name, callable)

用指定名稱創建一(yī)個排序規則并可(kě)調用。可(kě)調用将傳遞兩個字符串參數(shù)。如(rú)果第一(yī)個的順序低(dī)于第二個，則返回-1，如(rú)果順序相等，則返回0;如(rú)果第一(yī)個的順序高于第二個，則返回1。請(qǐng)注意，這(zhè)會控制排序（SQL中的ORDER BY），因此您的比較不會影響其他(tā)SQL操作(zuò)。

請(qǐng)注意，可(kě)調用函數(shù)将以Python字節串的形式獲取其參數(shù)，通(tōng)常以UTF-8編碼。

以下(xià)示例顯示了(le)“錯誤方式”排序的自(zì)定義歸類：

import sqlite3

def collate_reverse(string1, string2):

return -cmp(string1, string2)

con = sqlite3.connect(":memory:")

con.create_collation("reverse", collate_reverse)

cur = con.cursor()

cur.execute("create table test(x)")

cur.executemany("insert into test(x) values (?)", [("a",), ("b",)])

cur.execute("select x from test order by x collate reverse")

for row in cur:

print row

con.close()

複制

要(yào)移除排序規則，請(qǐng)create_collation使用None可(kě)調用方式進行(xíng)調用：

con.create_collation("reverse", None)

複制

interrupt()

您可(kě)以從(cóng)不同的線程調用此方法以中止可(kě)能(néng)在連接上(shàng)執行(xíng)的任何查詢。該查詢将中止，調用者将得到一(yī)個異常。

set_authorizer(authorizer_callback)

這(zhè)個例程注冊一(yī)個回調。每次嘗試訪問(wèn)數(shù)據庫中的一(yī)列表時(shí)，都(dōu)會調用該回調。SQLITE_OK如(rú)果允許訪問(wèn)，SQLITE_DENY則應該返回回調，如(rú)果整個SQL語句應該中止并出現錯誤，SQLITE_IGNORE并且該列應被視(shì)為(wèi)NULL值。這(zhè)些常量在sqlite3模塊中可(kě)用。

回調的第一(yī)個參數(shù)表示要(yào)授權哪種操作(zuò)。第二個和(hé)第三個參數(shù)将是參數(shù)或None取決于第一(yī)個參數(shù)。第四個參數(shù)是數(shù)據庫的名稱（“main”，“temp”等）（如(rú)果适用）。第五個參數(shù)是負責訪問(wèn)嘗試的最內(nèi)部觸發器或視(shì)圖的名稱，或者None如(rú)果此訪問(wèn)嘗試直接來自(zì)輸入SQL代碼。

請(qǐng)參考SQLite文檔，了(le)解第一(yī)個參數(shù)的可(kě)能(néng)值以及第二個和(hé)第三個參數(shù)的含義，具體取決于第一(yī)個參數(shù)。sqlite3模塊中提供了(le)所有必需的常量。

set_progress_handler(handler, n)

這(zhè)個例程注冊一(yī)個回調。該回調函數(shù)針對SQLite虛拟機的每n個指令進行(xíng)調用。如(rú)果您想在長(cháng)時(shí)間(jiān)運行(xíng)期間(jiān)從(cóng)SQLite調用，例如(rú)更新GUI，這(zhè)很(hěn)有用。

如(rú)果要(yào)清除以前安裝的任何進度處理程序，請(qǐng)使用Nonefor 處理程序調用該方法。

2.6版本中的新功能(néng)。

enable_load_extension(enabled)

此例程允許/禁止SQLite引擎從(cóng)共享庫加載SQLite擴展。SQLite擴展可(kě)以定義新的函數(shù)，聚合或全新的虛拟表實現。一(yī)個衆所周知的擴展是與SQLite一(yī)起發布的全文搜索擴展。

可(kě)裝載的擴展名默認是禁用的。見(jiàn)[1]。

2.7版本的新功能(néng)。

import sqlite3

con = sqlite3.connect(":memory:")

# enable extension loading

con.enable_load_extension(True)

# Load the fulltext search extension

con.execute("select load_extension('./fts3.so')")

# alternatively you can load the extension using an API call:

# con.load_extension("./fts3.so")

# disable extension laoding again

con.enable_load_extension(False)

# example from SQLite wiki

con.execute("create virtual table recipe using fts3(name, ingredients)")

con.executescript("""

insert into recipe (name, ingredients) values ('broccoli stew', 'broccoli peppers cheese tomatoes');

insert into recipe (name, ingredients) values ('pumpkin stew', 'pumpkin onions garlic celery');

insert into recipe (name, ingredients) values ('broccoli pie', 'broccoli cheese onions flour');

insert into recipe (name, ingredients) values ('pumpkin pie', 'pumpkin sugar flour butter');

""")

for row in con.execute("select rowid, name, ingredients from recipe where name match 'pie'"):

print row

複制

load_extension(path)

此例程從(cóng)共享庫中加載SQLite擴展。您必須先啓用擴展加載，enable_load_extension()然後才能(néng)使用此例程。

可(kě)裝載的擴展名默認是禁用的。見(jiàn)[1]。

2.7版本的新功能(néng)。

row_factory

您可(kě)以将此屬性更改為(wèi)可(kě)接受的遊标，将遊标和(hé)原始行(xíng)作(zuò)為(wèi)元組接受，并返回實際結果行(xíng)。這(zhè)樣，您可(kě)以實現更高級的返回結果方式，例如(rú)返回也可(kě)以按名稱訪問(wèn)列的對象。

例：

import sqlite3

def dict_factory(cursor, row):

d = {}

for idx, col in enumerate(cursor.description):

d[col[0]] = row[idx]

return d

con = sqlite3.connect(":memory:")

con.row_factory = dict_factory

cur = con.cursor()

cur.execute("select 1 as a")

print cur.fetchone()["a"]

複制

如(rú)果返回一(yī)個元組不夠，而你想要(yào)對列進行(xíng)基于名稱的訪問(wèn)，則應該考慮設置row_factory為(wèi)高度優化的sqlite3.Row類型。Row提供基于索引和(hé)不區(qū)分大小寫的基于名稱的訪問(wèn)，幾乎沒有內(nèi)存開(kāi)銷。它可(kě)能(néng)會比您自(zì)己的基于自(zì)定義字典的方法或甚至基于db_row的解決方案更好。

text_factory

使用此屬性，您可(kě)以控制為(wèi)TEXT數(shù)據類型返回哪些對象。默認情況下(xià)，此屬性設置為(wèi)unicode，sqlite3模塊将返回Unicode對象TEXT。如(rú)果您想要(yào)返回字節串，可(kě)以将其設置為(wèi)str。

出于效率原因，還有一(yī)種方法可(kě)以僅返回非ASCII數(shù)據的Unicode對象，否則返回字節串。要(yào)激活它，請(qǐng)将此屬性設置為(wèi)sqlite3.OptimizedUnicode。

您還可(kě)以将其設置為(wèi)接受單個字符串參數(shù)并返回結果對象的任何其他(tā)可(kě)調用對象。

有關說明(míng)，請(qǐng)參閱以下(xià)示例代碼：

import sqlite3

con = sqlite3.connect(":memory:")

cur = con.cursor()

AUSTRIA = u"\xd6sterreich"

# by default, rows are returned as Unicode

cur.execute("select ?", (AUSTRIA,))

row = cur.fetchone()

assert row[0] == AUSTRIA

# but we can make sqlite3 always return bytestrings ...

con.text_factory = str

cur.execute("select ?", (AUSTRIA,))

row = cur.fetchone()

assert type(row[0]) is str

# the bytestrings will be encoded in UTF-8, unless you stored garbage in the

# database ...

assert row[0] == AUSTRIA.encode("utf-8")

# we can also implement a custom text_factory ...

# here we implement one that will ignore Unicode characters that cannot be

# decoded from UTF-8

con.text_factory = lambda x: unicode(x, "utf-8", "ignore")

cur.execute("select ?", ("this is latin1 and would normally create errors" +

u"\xe4\xf6\xfc".encode("latin1"),))

row = cur.fetchone()

assert type(row[0]) is unicode

# sqlite3 offers a built-in optimized text_factory that will return bytestring

# objects, if the data is in ASCII only, and otherwise return unicode objects

con.text_factory = sqlite3.OptimizedUnicode

cur.execute("select ?", (AUSTRIA,))

row = cur.fetchone()

assert type(row[0]) is unicode

cur.execute("select ?", ("Germany",))

row = cur.fetchone()

assert type(row[0]) is str

複制

total_changes

返回自(zì)數(shù)據庫連接打開(kāi)以來已修改，插入或删除的數(shù)據庫行(xíng)總數(shù)。

iterdump

返回一(yī)個以SQL文本格式轉儲數(shù)據庫的叠代器。在保存內(nèi)存數(shù)據庫以供日後恢複時(shí)很(hěn)有用。該函數(shù)提供了(le)與sqlite3 shell中的.dump命令相同的功能(néng)。

2.6版本中的新功能(néng)。

例：

# Convert file existing_db.db to SQL dump file dump.sql

import sqlite3, os

con = sqlite3.connect('existing_db.db')

with open('dump.sql', 'w') as f:

for line in con.iterdump():

f.write('%s\n' % line)

複制

3.遊标對象

class sqlite3.Cursor

一(yī)個Cursor實例具有以下(xià)屬性和(hé)方法。

execute(sql[, parameters])

執行(xíng)一(yī)條SQL語句。SQL語句可(kě)能(néng)是參數(shù)化的（即占位符而不是SQL文字）。該sqlite3模塊支持兩種占位符：問(wèn)号（qmark樣式）和(hé)命名占位符（命名樣式）。

以下(xià)是兩種樣式的示例：

import sqlite3

con = sqlite3.connect(":memory:")

cur = con.cursor()

cur.execute("create table people (name_last, age)")

who = "Yeltsin"

age = 72

# This is the qmark style:

cur.execute("insert into people values (?, ?)", (who, age))

# And this is the named style:

cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})

print cur.fetchone()

複制

execute()隻會執行(xíng)一(yī)條SQL語句。如(rú)果您嘗試使用它執行(xíng)多個語句，則會引發警告。使用executescript()，如(rú)果你想用一(yī)個調用執行(xíng)多個SQL語句。

executemany(sql, seq_of_parameters)

針對在序列sql中找到的所有參數(shù)序列或映射執行(xíng)SQL命令。該sqlite3模塊還允許使用叠代器産生參數(shù)而不是序列。

import sqlite3

class IterChars:

def __init__(self):

self.count = ord('a')

def __iter__(self):

return self

def next(self):

if self.count > ord('z'):

raise StopIteration

self.count += 1

return (chr(self.count - 1),) # this is a 1-tuple

con = sqlite3.connect(":memory:")

cur = con.cursor()

cur.execute("create table characters(c)")

theIter = IterChars()

cur.executemany("insert into characters(c) values (?)", theIter)

cur.execute("select c from characters")

print cur.fetchall()

複制

以下(xià)是使用生成器的較簡單示例：

import sqlite3

import string

def char_generator():

for c in string.lowercase:

yield (c,)

con = sqlite3.connect(":memory:")

cur = con.cursor()

cur.execute("create table characters(c)")

cur.executemany("insert into characters(c) values (?)", char_generator())

cur.execute("select c from characters")

print cur.fetchall()

複制

executescript(sql_script)

這(zhè)是一(yī)次執行(xíng)多個SQL語句的非标準便利方法。它首先發布一(yī)個COMMIT語句，然後執行(xíng)它作(zuò)為(wèi)參數(shù)獲取的SQL腳本。

sql_script可(kě)以是一(yī)個字節串或一(yī)個Unicode字符串。

例：

import sqlite3

con = sqlite3.connect(":memory:")

cur = con.cursor()

cur.executescript("""

create table person(

firstname,

lastname,

age

);

create table book(

title,

author,

published

);

insert into book(title, author, published)

values (

'Dirk Gently''s Holistic Detective Agency',

'Douglas Adams',

1987

);

""")

複制

fetchone()

獲取查詢結果集的下(xià)一(yī)行(xíng)，返回單個序列，或者None沒有更多數(shù)據可(kě)用時(shí)。

fetchmany([size=cursor.arraysize])

獲取查詢結果的下(xià)一(yī)組行(xíng)，并返回一(yī)個列表。沒有更多行(xíng)可(kě)用時(shí)返回空列表。

每次調用要(yào)獲取的行(xíng)數(shù)由size參數(shù)指定。如(rú)果沒有給出，遊标的數(shù)組大小決定了(le)要(yào)獲取的行(xíng)數(shù)。該方法應該嘗試獲取大小參數(shù)所指示的行(xíng)數(shù)。如(rú)果由于指定的行(xíng)數(shù)不可(kě)用而無法執行(xíng)此操作(zuò)，則可(kě)能(néng)會返回更少(shǎo)的行(xíng)。

請(qǐng)注意，大小參數(shù)涉及性能(néng)方面的考慮因素。為(wèi)了(le)獲得最佳性能(néng)，通(tōng)常最好使用arraysize屬性。如(rú)果使用size參數(shù)，那(nà)麽最好從(cóng)一(yī)次fetchmany()調用到下(xià)一(yī)次調用保持相同的值。

fetchall()

獲取查詢結果的所有（剩餘）行(xíng)，并返回一(yī)個列表。請(qǐng)注意，遊标的arraysize屬性可(kě)能(néng)會影響此操作(zuò)的性能(néng)。沒有行(xíng)可(kě)用時(shí)返回空列表。

rowcount

雖然模塊的Cursor類sqlite3實現了(le)這(zhè)個屬性，但(dàn)數(shù)據庫引擎自(zì)己對确定“受影響的行(xíng)”/“所選擇的行(xíng)”的支持是古怪的。

對于executemany()陳述，修改的數(shù)量被總結成rowcount。

按照Python DB API Spec的要(yào)求，rowcount如(rú)果executeXX()遊标上(shàng)沒有執行(xíng)任何操作(zuò)，或者上(shàng)一(yī)次操作(zuò)的行(xíng)數(shù)不能(néng)被界面确定，則屬性“為(wèi)-1 ”。這(zhè)包括SELECT語句，因為(wèi)我們無法确定查詢産生的行(xíng)數(shù)，直到獲取所有行(xíng)。

使用3.6.5之前的SQLite版本，rowcount如(rú)果您DELETE FROM table沒有任何條件，則設置為(wèi)0 。

lastrowid

此隻讀屬性提供最後修改行(xíng)的rowid。隻有在您INSERT使用該execute()方法發布聲明(míng)時(shí)才會設置。對于調用以外(wài)INSERT或executemany()調用時(shí)的操作(zuò)，lastrowid設置為(wèi)None。

description

此隻讀屬性提供最後一(yī)個查詢的列名稱。為(wèi)了(le)與Python DB API保持兼容，它會為(wèi)每個元組的最後六項所在的每列返回一(yī)個7元組None。

它被設置為(wèi)SELECT沒有任何匹配行(xíng)的語句。

connection

這(zhè)個隻讀屬性提供Connection了(le)Cursor對象使用的SQLite數(shù)據庫。一(yī)個Cursor通(tōng)過調用創建的對象con.cursor()将有一(yī)個connection引用屬性CON：

>>> con = sqlite3.connect(":memory:")

>>> cur = con.cursor()

>>> cur.connection == con

True

複制

4.行(xíng)對象

class sqlite3.Row

Row實例用作(zuò)一(yī)個高度優化的row_factory用于Connection對象。它試圖模仿大部分功能(néng)中的元組。

它支持按列名和(hé)索引進行(xíng)映射訪問(wèn)，叠代，表示，相等性測試和(hé)len()。

如(rú)果兩個Row對象具有完全相同的列并且它們的成員(yuán)相等，則它們相等。

在版本2.6中進行(xíng)了(le)更改：添加了(le)叠代和(hé)等式（hashability）。

keys()

此方法返回列名稱的列表。在查詢之後，它立即成為(wèi)每個元組的第一(yī)個成員(yuán)Cursor.description。

2.6版本中的新功能(néng)。

我們假設我們按照上(shàng)面給出的例子初始化一(yī)個表格：

conn = sqlite3.connect(":memory:")

c = conn.cursor()

c.execute('''create table stocks

(date text, trans text, symbol text,

qty real, price real)''')

c.execute("""insert into stocks

values ('2006-01-05','BUY','RHAT',100,35.14)""")

conn.commit()

c.close()

複制

現在我們插入Row：

>>> conn.row_factory = sqlite3.Row

>>> c = conn.cursor()

>>> c.execute('select * from stocks')

<sqlite3.Cursor object at 0x7f4e7dd8fa80>

>>> r = c.fetchone()

>>> type(r)

<type 'sqlite3.Row'>

>>> r

(u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.14)

>>> len(r)

5

>>> r[2]

u'RHAT'

>>> r.keys()

['date', 'trans', 'symbol', 'qty', 'price']

>>> r['qty']

100.0

>>> for member in r:

... print member

...

2006-01-05

BUY

RHAT

100.0

35.14

複制

5. SQLite和(hé)Python類型

5.1。介紹

SQLite的原生支持以下(xià)幾種類型：NULL，INTEGER，REAL，TEXT，BLOB。

因此可(kě)以将以下(xià)Python類型發送給SQLite，而不會有任何問(wèn)題：

這(zhè)是默認情況下(xià)SQLite類型轉換為(wèi)Python類型的方式：

sqlite3模塊的類型系統可(kě)以通(tōng)過兩種方式進行(xíng)擴展：可(kě)以通(tōng)過對象适配将其他(tā)Python類型存儲在SQLite數(shù)據庫中，并且可(kě)以讓sqlite3模塊通(tōng)過轉換器将SQLite類型轉換為(wèi)不同的Python類型。

5.2。使用适配器在SQLite數(shù)據庫中存儲其他(tā)Python類型

如(rú)前所述，SQLite本身隻支持一(yī)組有限的類型。要(yào)将其他(tā)Python類型與SQLite一(yī)起使用，您必須将它們調整為(wèi)sqlite3模塊支持的SQLite類型之一(yī)：NoneType，int，long，float，str，unicode，buffer之一(yī)。

有兩種方法可(kě)以使sqlite3模塊将自(zì)定義的Python類型改為(wèi)支持的類型之一(yī)。

5.2.1。讓你的對象适應自(zì)己

如(rú)果你自(zì)己寫課程，這(zhè)是一(yī)個很(hěn)好的方法。假設你有這(zhè)樣的課程：

class Point(object):

def __init__(self, x, y):

self.x, self.y = x, y

複制

現在您想要(yào)将該點存儲在單個SQLite列中。首先，您必須首先選擇一(yī)種支持的類型來表示該點。我們隻需使用str并使用分号分隔坐(zuò)标。然後你需要(yào)給你的班級一(yī)個__conform__(self, protocol)必須返回轉換後的值的方法。參數(shù)協議(yì)将會是PrepareProtocol。

import sqlite3

class Point(object):

def __init__(self, x, y):

self.x, self.y = x, y

def __conform__(self, protocol):

if protocol is sqlite3.PrepareProtocol:

return "%f;%f" % (self.x, self.y)

con = sqlite3.connect(":memory:")

cur = con.cursor()

p = Point(4.0, -3.2)

cur.execute("select ?", (p,))

print cur.fetchone()[0]

複制

5.2.2。注冊可(kě)調用的适配器

另一(yī)種可(kě)能(néng)性是創建一(yī)個将類型轉換為(wèi)字符串表示并将函數(shù)注冊的函數(shù)register_adapter()。

注釋

适應的類型/類别必須是新式類别，即它必須具有object作(zuò)為(wèi)其基礎之一(yī)。

import sqlite3

class Point(object):

def __init__(self, x, y):

self.x, self.y = x, y

def adapt_point(point):

return "%f;%f" % (point.x, point.y)

sqlite3.register_adapter(Point, adapt_point)

con = sqlite3.connect(":memory:")

cur = con.cursor()

p = Point(4.0, -3.2)

cur.execute("select ?", (p,))

print cur.fetchone()[0]

複制

該sqlite3模塊有兩個用于Python內(nèi)置datetime.date和(hé)datetime.datetime類型的默認适配器。現在讓我們假設我們想存儲datetime.datetime不是ISO表示的對象，而是作(zuò)為(wèi)一(yī)個Unix時(shí)間(jiān)戳。

import sqlite3

import datetime, time

def adapt_datetime(ts):

return time.mktime(ts.timetuple())

sqlite3.register_adapter(datetime.datetime, adapt_datetime)

con = sqlite3.connect(":memory:")

cur = con.cursor()

now = datetime.datetime.now()

cur.execute("select ?", (now,))

print cur.fetchone()[0]

複制

5.3。将SQLite值轉換為(wèi)自(zì)定義Python類型

編寫适配器可(kě)讓您将自(zì)定義Python類型發送到SQLite。但(dàn)為(wèi)了(le)讓它變得非常有用，我們需要(yào)讓Python到SQLite來Python的往返工(gōng)作(zuò)。

輸入轉換器。

讓我們回到Point課堂上(shàng)。我們将通(tōng)過分号分隔的x和(hé)y坐(zuò)标存儲為(wèi)SQLite中的字符串。

首先，我們将定義一(yī)個轉換器函數(shù)，該函數(shù)接受字符串作(zuò)為(wèi)參數(shù)并Point從(cóng)中構造一(yī)個對象。

注意

無論使用哪種數(shù)據類型将值發送給SQLite，轉換器函數(shù)總是使用字符串調用。

def convert_point(s):

x, y = map(float, s.split(";"))

return Point(x, y)

複制

現在您需要(yào)讓sqlite3模塊知道(dào)您從(cóng)數(shù)據庫中選擇的內(nèi)容實際上(shàng)是一(yī)個點。有兩種方法可(kě)以做(zuò)到這(zhè)一(yī)點：

隐式地(dì)通(tōng)過聲明(míng)的類型

顯式地(dì)通(tōng)過列名稱

這(zhè)兩種方法在節模塊函數(shù)和(hé)常數(shù)描述，在用于常量的條目PARSE_DECLTYPES和(hé)PARSE_COLNAMES。

以下(xià)示例說明(míng)了(le)這(zhè)兩種方法。

import sqlite3

class Point(object):

def __init__(self, x, y):

self.x, self.y = x, y

def __repr__(self):

return "(%f;%f)" % (self.x, self.y)

def adapt_point(point):

return "%f;%f" % (point.x, point.y)

def convert_point(s):

x, y = map(float, s.split(";"))

return Point(x, y)

# Register the adapter

sqlite3.register_adapter(Point, adapt_point)

# Register the converter

sqlite3.register_converter("point", convert_point)

p = Point(4.0, -3.2)

#########################

# 1) Using declared types

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)

cur = con.cursor()

cur.execute("create table test(p point)")

cur.execute("insert into test(p) values (?)", (p,))

cur.execute("select p from test")

print "with declared types:", cur.fetchone()[0]

cur.close()

con.close()

#######################

# 1) Using column names

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)

cur = con.cursor()

cur.execute("create table test(p)")

cur.execute("insert into test(p) values (?)", (p,))

cur.execute('select p as "p [point]" from test')

print "with column names:", cur.fetchone()[0]

cur.close()

con.close()

複制

5.4。默認适配器和(hé)轉換器

日期時(shí)間(jiān)模塊中有日期和(hé)日期時(shí)間(jiān)類型的默認适配器。它們将作(zuò)為(wèi)ISO日期/ ISO時(shí)間(jiān)戳發送到SQLite。

默認轉換器的名稱為(wèi)“date”，datetime.date名稱為(wèi)“timestamp” datetime.datetime。

這(zhè)樣，在大多數(shù)情況下(xià)，您可(kě)以使用Python中的日期/時(shí)間(jiān)戳，而不需要(yào)額外(wài)的操作(zuò)。适配器的格式也與實驗性SQLite日期/時(shí)間(jiān)函數(shù)兼容。

以下(xià)示例演示了(le)這(zhè)一(yī)點。

import sqlite3

import datetime

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)

cur = con.cursor()

cur.execute("create table test(d date, ts timestamp)")

today = datetime.date.today()

now = datetime.datetime.now()

cur.execute("insert into test(d, ts) values (?, ?)", (today, now))

cur.execute("select d, ts from test")

row = cur.fetchone()

print today, "=>", row[0], type(row[0])

print now, "=>", row[1], type(row[1])

cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"')

row = cur.fetchone()

print "current_date", row[0], type(row[0])

print "current_timestamp", row[1], type(row[1])

複制

如(rú)果存儲在SQLite中的時(shí)間(jiān)戳記的分數(shù)部分長(cháng)于6個數(shù)字，則其值将被時(shí)間(jiān)戳轉換器截斷為(wèi)微(wēi)秒精度。

6.控制交易

默認情況下(xià)，sqlite3模塊在數(shù)據修改語言（DML）語句（即INSERT/ UPDATE/ DELETE/ REPLACE）之前隐式打開(kāi)事務，并在非DML非查詢語句（即非SELECT上(shàng)述任何內(nèi)容）之前隐式提交事務。

所以，如(rú)果你是一(yī)個事務中，并發出這(zhè)樣的命令CREATE TABLE ...，VACUUM，PRAGMA，該sqlite3模塊将隐式執行(xíng)該命令之前提交。這(zhè)樣做(zuò)有兩個原因。首先，這(zhè)些命令中的某些命令在事務中不起作(zuò)用。另一(yī)個原因是sqlite3需要(yào)跟蹤事務狀态（如(rú)果事務處于活動狀态）。

你可(kě)以控制BEGINsqlite3通(tōng)過調用isolation_level參數(shù)connect()或通(tōng)過isolation_level連接屬性隐式執行(xíng)哪種語句（或者根本不執行(xíng)）。

如(rú)果您想要(yào)自(zì)動提交模式，請(qǐng)設置isolation_level為(wèi)None。

否則，将其保留為(wèi)默認值，這(zhè)将導緻一(yī)個簡單的“BEGIN”語句，或将其設置為(wèi)SQLite支持的隔離級别之一(yī)：“DEFERRED”，“IMMEDIATE”或“EXCLUSIVE”。

7.高效地(dì)使用sqlite3

7.1。使用快(kuài)捷方式

使用非标準的execute()，executemany()并且executescript()該方法的Connection對象，您的代碼可(kě)以更簡潔，因為(wèi)你不必創建（通(tōng)常是多餘的）書(shū)面Cursor明(míng)确對象。相反，Cursor對象是隐式創建的，這(zhè)些快(kuài)捷方法返回遊标對象。這(zhè)樣，您可(kě)以執行(xíng)一(yī)個SELECT語句并直接使用該Connection對象上(shàng)的一(yī)次調用直接對其進行(xíng)叠代。

import sqlite3

persons = [

("Hugo", "Boss"),

("Calvin", "Klein")

]

con = sqlite3.connect(":memory:")

# Create the table

con.execute("create table person(firstname, lastname)")

# Fill the table

con.executemany("insert into person(firstname, lastname) values (?, ?)", persons)

# Print the table contents

for row in con.execute("select firstname, lastname from person"):

print row

print "I just deleted", con.execute("delete from person").rowcount, "rows"

複制

7.2。按名稱而不是按索引訪問(wèn)列

該sqlite3模塊的一(yī)個有用功能(néng)是sqlite3.Row設計用作(zuò)行(xíng)工(gōng)廠的內(nèi)置類。

使用此類包裝的行(xíng)可(kě)以通(tōng)過索引（如(rú)元組）訪問(wèn)，也可(kě)以通(tōng)過名稱不區(qū)分大小寫：

import sqlite3

con = sqlite3.connect(":memory:")

con.row_factory = sqlite3.Row

cur = con.cursor()

cur.execute("select 'John' as name, 42 as age")

for row in cur:

assert row[0] == row["name"]

assert row["name"] == row["nAmE"]

assert row[1] == row["age"]

assert row[1] == row["AgE"]

複制

7.3。使用連接作(zuò)為(wèi)上(shàng)下(xià)文管理器

2.6版本中的新功能(néng)。

連接對象可(kě)以用作(zuò)自(zì)動提交或回滾事務的上(shàng)下(xià)文管理器。如(rú)果發生異常，交易将回滾; 否則，交易承諾：

import sqlite3

con = sqlite3.connect(":memory:")

con.execute("create table person (id integer primary key, firstname varchar unique)")

# Successful, con.commit() is called automatically afterwards

with con:

con.execute("insert into person(firstname) values (?)", ("Joe",))

# con.rollback() is called after the with block finishes with an exception, the

# exception is still raised and must be caught

try:

with con:

con.execute("insert into person(firstname) values (?)", ("Joe",))

except sqlite3.IntegrityError:

print "couldn't add Joe twice"

複制

8.常見(jiàn)問(wèn)題

8.1。多線程

較舊(jiù)的SQLite版本在共享線程之間(jiān)的連接時(shí)遇到了(le)問(wèn)題。這(zhè)就是為(wèi)什麽Python模塊不允許在線程之間(jiān)共享連接和(hé)遊标的原因。如(rú)果你仍然嘗試這(zhè)樣做(zuò)，你将在運行(xíng)時(shí)得到一(yī)個異常。

唯一(yī)的例外(wài)是調用該interrupt()方法，這(zhè)隻有在從(cóng)不同的線程調用時(shí)才有意義。

注

1

（1，2）默認情況下(xià)，sqlite3模塊不是由可(kě)加載擴展支持構建的，因為(wèi)某些平台（特别是Mac OS X）具有無需此功能(néng)編譯的SQLite庫。要(yào)獲得可(kě)加載的擴展支持，您必須修改setup.py并删除設置SQLITE_OMIT_LOAD_EXTENSION的行(xíng)。