用Python操作數(shù)據(jù)庫的最詳細(xì)示例

1. 概述

1.1 前言

最近用Caffe跑自己的數(shù)據(jù)集，需要學(xué)習(xí)LMDB和LevelDB，趁此機(jī)會(huì)復(fù)習(xí)了SQLite和MySQL的使用，一起整理在此。

1.2 環(huán)境

使用Ubuntu 14.04，Python 2.7.6。

2. SQLite

2.1 準(zhǔn)備

SQLite是一種嵌入式數(shù)據(jù)庫，它的數(shù)據(jù)庫就是一個(gè)文件。Python 2.5x以上版本內(nèi)置了SQLite3，使用時(shí)直接import sqlite3即可。

2.2 操作流程

概括地講，操作SQLite的流程是：

通過sqlite3.open()創(chuàng)建與數(shù)據(jù)庫文件的連接對(duì)象connection；

通過connection.cursor()創(chuàng)建光標(biāo)對(duì)象cursor；

通過cursor.execute()執(zhí)行SQL語句；

通過connection.commit()提交當(dāng)前的事務(wù)，或者通過cursor.fetchall()獲得查詢結(jié)果；

通過connection.close()關(guān)閉與數(shù)據(jù)庫文件的連接。

總結(jié)起來就是用cursor.execute()執(zhí)行SQL語句，改變數(shù)據(jù)（插入、刪除、修改）時(shí)用connection.commit()提交變更，查詢數(shù)據(jù)時(shí)用cursor.fetchall()得到查詢結(jié)果。

2.3 操作實(shí)例

2.3.1 建立數(shù)據(jù)庫與建立表

直接來看例子：

這里conn是與數(shù)據(jù)庫文件test.db的連接對(duì)象，c是conn的光標(biāo)對(duì)象，通過c.execute()執(zhí)行建表操作，創(chuàng)建了簡(jiǎn)單的學(xué)生信息表（學(xué)號(hào)，名字），通過conn.commit()提交，最后用conn.close()關(guān)閉連接。

conn.open()發(fā)現(xiàn)文件不存在時(shí)會(huì)自動(dòng)創(chuàng)建，這里使用了文件“test.db”，也可以使用“:memory:”建立內(nèi)存數(shù)據(jù)庫。

2.3.2 插入、刪除、修改

為了便于多次運(yùn)行，直接使用了內(nèi)存數(shù)據(jù)庫：

做的事情還是非常簡(jiǎn)單易懂的，向?qū)W生信息表中插入（1，Alice）、（2，Bob）、（3，Peter）三條記錄，刪除（1，Alice），修改（3，Peter）為（3，Mark）。

“?”是sqlite3中的占位符，execute時(shí)會(huì)用第二個(gè)參數(shù)元組里的元素按順序替換。官方文檔里建議出于安全考慮，不要直接用python做字符串拼接。

另外注意不需要每次execute后調(diào)用commit。

2.3.3 查詢

直接在上面的代碼commit之后加上：

運(yùn)行一下，輸出結(jié)果為：

test_query.py

fetchall()返回的是記錄數(shù)組，可以通過WHERE子句做更細(xì)致的選擇。

2.3.4 完整的例子

把上面的操作寫成函數(shù)形式：

運(yùn)行一下，輸出結(jié)果為：

test_sqlite.py

之后用的例子都是這個(gè)簡(jiǎn)單的學(xué)生信息表(學(xué)號(hào)，姓名)。

3. MySQL

3.1 準(zhǔn)備

安裝MySQL：

安裝MySQLdb：

使用時(shí)import MySQLdb（注意大小寫）。

3.2 操作流程

同為關(guān)系型數(shù)據(jù)庫，MySQL的操作方法和SQLite是大同小異的。建立連接對(duì)象與光標(biāo)對(duì)象，用execute()執(zhí)行SQL語句，commi()提交事物，fetchall()獲得查詢結(jié)果。

3.3 操作實(shí)例

直接看MySQL版本的完整例子：

對(duì)比后可以發(fā)現(xiàn)區(qū)別僅是建立連接時(shí)參數(shù)復(fù)雜一些，同時(shí)需要用select_db()選擇數(shù)據(jù)庫。

運(yùn)行一下，輸出結(jié)果為：

test_mysql.py

4. LMDB

4.1 準(zhǔn)備

學(xué)習(xí)LMDB的時(shí)候不禁想到知乎上的提問“有哪些名人長(zhǎng)期生活在其他名人的光環(huán)下”，說實(shí)話感覺查它的人基本都是為了用Caffe……

Anyway，LMDB和SQLite/MySQL等關(guān)系型數(shù)據(jù)庫不同，屬于key-value數(shù)據(jù)庫（把LMDB想成dict會(huì)比較容易理解），鍵key與值value都是字符串。

安裝：

使用時(shí)import lmdb。

4.2 操作流程

概況地講，操作LMDB的流程是：

通過env = lmdb.open()打開環(huán)境

通過txn = env.begin()建立事務(wù)

通過txn.put(key, value)進(jìn)行插入和修改

通過txn.delete(key)進(jìn)行刪除

通過txn.get(key)進(jìn)行查詢

通過txn.cursor()進(jìn)行遍歷

通過txn.commit()提交更改

4.3 操作實(shí)例

4.3.1 建立環(huán)境

運(yùn)行一下，查看當(dāng)前目錄的變化：

set_env.py

可以看到當(dāng)前目錄下多了students目錄，里面有data.mdb和lock.mdb兩個(gè)文件。

4.3.2 插入、刪除、修改

插入與修改都用put實(shí)現(xiàn)，刪除用delete實(shí)現(xiàn)。

注意用txn = env.begin()創(chuàng)建事務(wù)時(shí)，有write = True才能夠?qū)憯?shù)據(jù)庫。

4.3.3 查詢

查單條記錄用get(key)，遍歷數(shù)據(jù)庫用cursor。

直接在上面的代碼commit()之后加上：

運(yùn)行一下，輸出結(jié)果為：

test_query.py

注意上次commit()之后要用env.begin()更新txn。

4.3.4 完整的例子

運(yùn)行一下，輸出結(jié)果為：

test_lmdb.py

5. LevelDB

5.1 準(zhǔn)備

同為key-value數(shù)據(jù)庫，LevelDB的資料比LMDB豐富太多了。值得一提的是LevelDB實(shí)現(xiàn)時(shí)用到了SkipList，以后有機(jī)會(huì)要親自實(shí)現(xiàn)一下。

安裝：

使用時(shí)import leveldb。

5.2 操作流程

LevelDB操作時(shí)類似與LMDB，使用Put/Get/Delete，但是更加簡(jiǎn)單（不需要事務(wù)txn和commit提交），同時(shí)支持范圍迭代器RangeIter。

5.3 操作實(shí)例

來看LevelDB版本的完整例子：

運(yùn)行一下，輸出結(jié)果為：

test_leveldb.py

此外，由于沒有commit()操作，leveldb中用WriteBatch實(shí)現(xiàn)多條更改一次提交，直接copy示例代碼如下：

6. 學(xué)習(xí)總結(jié)

這次學(xué)習(xí)四種數(shù)據(jù)庫操作時(shí)，是按照SQLite -> MySQL -> LMDB -> LevelDB的順序，所以研究SQLite與LMDB花了較長(zhǎng)時(shí)間，而MySQL與LevelDB很快就搞定了。某種意義上，學(xué)習(xí)技術(shù)和背單詞一樣，當(dāng)前掌握的單詞越多，背新單詞就越容易——因?yàn)榭梢园研聠卧~和已經(jīng)掌握的同義詞聯(lián)系在一起，在腦海里聚成簇。

最后回顧一下，SQLite與MySQL都是關(guān)系型數(shù)據(jù)庫，操作時(shí)創(chuàng)建連接對(duì)象connection與光標(biāo)對(duì)象cursor，通過execute執(zhí)行SQL語句，commit提交變更，fetch得到查詢結(jié)果；LMDB與LevelDB都是K-V數(shù)據(jù)庫，操作時(shí)建立與數(shù)據(jù)庫的連接，用put/delete改變數(shù)據(jù)，用get獲取數(shù)據(jù)，區(qū)別是LMDB中有事務(wù)需要commit，LevelDB不需要。