前言

python通過sqlite3模塊來(lái)操作sqlite。sqlite3是python的一個(gè)標(biāo)準(zhǔn)庫(kù)并不需要安裝，sqlite3是一個(gè)SQLite數(shù)據(jù)庫(kù)DB-API2.0接口模塊。

建立連接

要想使用這個(gè)模塊，必須先創(chuàng)建一個(gè)Connection對(duì)象， 它代表數(shù)據(jù)庫(kù)。

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

上面的例子中，數(shù)據(jù)將存儲(chǔ)在example.db文件中，如果文件存在則打開文件，如果文件不存在將新建一個(gè)example.db數(shù)據(jù)庫(kù)文件，你也可以通過sqlite3.connect(":memory:")來(lái)創(chuàng)建一個(gè)內(nèi)存中的數(shù)據(jù)庫(kù)，通常情況下這沒啥意義。

connect函數(shù)詳解

函數(shù)簽名：

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

• database: 準(zhǔn)備打開的數(shù)據(jù)庫(kù)文件的路徑,也可以用":memory:"在內(nèi)存中打開一個(gè)數(shù)據(jù)庫(kù)。
• timeout: 當(dāng)一個(gè)數(shù)據(jù)庫(kù)被多個(gè)連接訪問的時(shí)候，如果其中一個(gè)進(jìn)程修改這個(gè)數(shù)據(jù)庫(kù)，在這個(gè)連接的事務(wù)提交之前，這個(gè)數(shù)據(jù)庫(kù)將被一直鎖定，timeout參數(shù)指定了等待鎖定釋放的超時(shí)時(shí)間，超過之后會(huì)引發(fā)一個(gè)異常，默認(rèn)為5s
• detect_types:默認(rèn)是0,即關(guān)閉類型檢測(cè)。SQLite原生支持5種類型：TEXT INTEGER REAL BLOB和NULL，如果想要用其他類型必須自行添加相應(yīng)的支持，使用該參數(shù)和模塊級(jí)別的register_converter()函數(shù)注冊(cè)轉(zhuǎn)換器來(lái)簡(jiǎn)單實(shí)現(xiàn)。該參數(shù)可能值還包括：
  • sqlite3.PARSE_DECLTYPES:設(shè)置為這個(gè)常量后，sqlite3模塊將解析它返回的每一列的聲明類型，比如integer primary key,它會(huì)解析出integer，如果是number(10)他會(huì)解析出number。然后，他會(huì)在裝換器字典里查找那個(gè)類型注冊(cè)的裝換器函數(shù)，并調(diào)用它。
  • sqlite3.PARSE_COLNAMES:設(shè)置為這個(gè)常量后，sqlite接口將解析它放回的每一列的列名，會(huì)在其中查找 [mytype] 這個(gè)形式的字符串，然后用‘mytype’來(lái)決定那個(gè)列的類型。它會(huì)嘗試在轉(zhuǎn)換器字典中查找‘mytype’鍵對(duì)應(yīng)的轉(zhuǎn)換器函數(shù)，然后用這個(gè)轉(zhuǎn)換器函數(shù)返回的值來(lái)做為列的類型。
• isolation_level:用于設(shè)置sqlite3模塊中事務(wù)控制的行為
• check_same_thread:默認(rèn)情況下為True，只有當(dāng)前的線程可以使用該連接。如果設(shè)置為False，則多個(gè)線程可以共享放回的連接。當(dāng)多個(gè)線程使用同一個(gè)連接的時(shí)候，用戶應(yīng)該把寫操作序列化，以避免數(shù)據(jù)損壞。
• factory:connect是一個(gè)工廠函數(shù)，默認(rèn)情況下使用了sqlite3模塊中它自己的Connection類，你也通過繼承Connection類來(lái)創(chuàng)建自己的連接類，然后將其賦值給factory來(lái)使用。
• cached_statements:sqlite3模塊在內(nèi)部使用語(yǔ)句緩存來(lái)避免SQL解析開銷，默認(rèn)情況下緩存100條語(yǔ)句

其返回的是一個(gè)Connection對(duì)象。

使用連接

當(dāng)有了一個(gè)Connection對(duì)象后，你就可以創(chuàng)建Cursor游標(biāo)對(duì)象，通過游標(biāo)對(duì)象你就可以對(duì)整個(gè)數(shù)據(jù)庫(kù)進(jìn)行增刪改查了。

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()
conn.close()

讓我們來(lái)逐一解釋上面的語(yǔ)句：

• conn.cursor(): 調(diào)用Connection對(duì)象的cursor()函數(shù)，放回一個(gè)cursor對(duì)象
• c.execte():該方法用于執(zhí)行SQL語(yǔ)句
• conn.commit():用于提交當(dāng)前事務(wù)，如果沒有調(diào)用這個(gè)方法，那么從上一次執(zhí)行commit()以來(lái)的所有的變化其他該數(shù)據(jù)連接上都是不可見的。
• conn.close():關(guān)閉數(shù)據(jù)庫(kù)連接， 注意他并不會(huì)自動(dòng)調(diào)用commit()方法，如果在關(guān)閉數(shù)據(jù)庫(kù)連接之前沒有調(diào)用commit()那么你的修改將會(huì)丟失。

我們通常的使用流程就是這樣的。

Connection對(duì)象

屬性：

• isolation_level:獲取或設(shè)置當(dāng)前默認(rèn)的隔離級(jí)別
• in_transaction:這是一個(gè)只讀屬性，如果是在活動(dòng)事務(wù)中(即數(shù)據(jù)庫(kù)發(fā)生了改變但是還沒有commit時(shí))返回True，一旦你commit后返回False。
• cursor():返回一個(gè)Cursor對(duì)象
• commit():提交當(dāng)前事務(wù)，如果沒有調(diào)用這個(gè)方法，那么從上一次提交commit()以來(lái)所有的變化在其他數(shù)據(jù)庫(kù)連接中不可見
• rollback():回滾從上一次調(diào)用commit()以來(lái)所有數(shù)據(jù)庫(kù)的改變
• close():關(guān)閉數(shù)據(jù)庫(kù)連接，注意它不會(huì)自動(dòng)調(diào)用commit()方法，如果在關(guān)閉之間沒有調(diào)用commit()那么修改將會(huì)丟失
• execute(sql):這是一個(gè)非標(biāo)準(zhǔn)的快捷方法， 它會(huì)調(diào)用cursor()方法來(lái)創(chuàng)建Cursor對(duì)象，并且用給定的參數(shù)來(lái)調(diào)用Cursor對(duì)象的execute()方法，最后返回的是這個(gè)游標(biāo)對(duì)象。
• executemany():這也是一個(gè)非標(biāo)準(zhǔn)的快捷方法，對(duì)應(yīng)Cursor對(duì)象的executemany()方法
• executescript():同上，對(duì)應(yīng)Cursor對(duì)象的executescript()方法

Cursor對(duì)象

• execute(sql):執(zhí)行SQL語(yǔ)句，可以參數(shù)化SQL語(yǔ)句，sqlite3模塊支持兩種占用符：?jiǎn)柼?hào)和命名占用符。需要注意的是，該方法一次僅僅能執(zhí)行一條SQL語(yǔ)句，如果想要執(zhí)行多條語(yǔ)句請(qǐng)使用executescript()方法

In [1]: import sqlite3
In [2]: con = sqlite3.connect('example.db')
In [3]: data = '2018-01-08'
In [4]: trans = 'BUY'
In [5]: symbol = 'RHAT'
In [6]: qty = '120'
In [7]: price = '12.1'
In [8]: cur = con.cursor()
In [9]: cur.execute('insert into stocks values (?,?,?,?,?)',(data,trans,symbol,qty,price))
Out[9]: <sqlite3.Cursor at 0x233f51c1e30>
In [10]: con.commit()
In [16]: cur.execute('select * from stocks where date=:data and trans=:buy',{'data':data,'buy':trans})
Out[16]: <sqlite3.Cursor at 0x233f51c1e30>
In [17]: print(cur.fetchone())
('2018-01-08', 'BUY', 'RHAT', 120.0, 12.1)

• executescript(sql_script):一次執(zhí)行多個(gè)SQL語(yǔ)句的快捷方法
• fetchone():獲取查詢結(jié)果集的下一行，返回單個(gè)序列，如果沒有更多數(shù)據(jù)可用，則返回None
• fetchall():獲取查詢結(jié)果集的所有(剩余)行，并返回一個(gè)列表。

In [18]: cur.execute('select * from stocks')
Out[18]: <sqlite3.Cursor at 0x233f51c1e30>
In [19]: cur.fetchone()
Out[19]: ('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
In [20]: cur.fetchone()
Out[20]: ('2006-01-05', 'BUY', 'CHE', 200.0, 1.2)
In [21]: cur.fetchone()
Out[21]: ('2018-01-08', 'BUY', 'RHAT', 120.0, 12.1)
In [22]: cur.fetchone()
In [23]: cur.execute('select * from stocks')
Out[23]: <sqlite3.Cursor at 0x233f51c1e30>
In [24]: cur.fetchone()
Out[24]: ('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
In [25]: cur.fetchall()
Out[25]:
[('2006-01-05', 'BUY', 'CHE', 200.0, 1.2),
 ('2018-01-08', 'BUY', 'RHAT', 120.0, 12.1)]

• close():關(guān)閉這個(gè)Cursor對(duì)象

SQLite與python類型

SQLite原生支持一下類型：NULL INTEGER REAL TEXT BLOB
這五種類型對(duì)應(yīng)于python中的None int float str bytes,也就是說將這些類型發(fā)送給SQLite不會(huì)出現(xiàn)任何問題。而且sqlite3模塊默認(rèn)也是這么轉(zhuǎn)換的。