1. 概述
1.1 前言
最近用Caffe跑自己的數(shù)據(jù)集绰播，需要學(xué)習(xí)LMDB和LevelDB仗颈，趁此機(jī)會(huì)復(fù)習(xí)了SQLite和MySQL的使用黍瞧，一起整理在此诸尽。
代碼：https://github.com/liquidconv/py4db
1.2 環(huán)境
使用Ubuntu 14.04，Python 2.7.6印颤。
2. SQLite
2.1 準(zhǔn)備
SQLite是一種嵌入式數(shù)據(jù)庫(kù)您机，它的數(shù)據(jù)庫(kù)就是一個(gè)文件。Python 2.5x以上版本內(nèi)置了SQLite3年局，使用時(shí)直接import sqlite3即可际看。
2.2 操作流程
概括地講，操作SQLite的流程是：
- 通過(guò)sqlite3.open()創(chuàng)建與數(shù)據(jù)庫(kù)文件的連接對(duì)象connection矢否；
- 通過(guò)connection.cursor()創(chuàng)建光標(biāo)對(duì)象cursor仲闽；
- 通過(guò)cursor.execute()執(zhí)行SQL語(yǔ)句；
- 通過(guò)connection.commit()提交當(dāng)前的事務(wù)僵朗，或者通過(guò)cursor.fetchall()獲得查詢結(jié)果赖欣；
- 通過(guò)connection.close()關(guān)閉與數(shù)據(jù)庫(kù)文件的連接。
詳細(xì)的sqlite3模塊API可以看這里：
http://www.runoob.com/sqlite/sqlite-python.html
總結(jié)起來(lái)就是用cursor.execute()執(zhí)行SQL語(yǔ)句验庙，改變數(shù)據(jù)（插入顶吮、刪除、修改）時(shí)用connection.commit()提交變更粪薛，查詢數(shù)據(jù)時(shí)用cursor.fetchall()得到查詢結(jié)果云矫。
2.3 操作實(shí)例
2.3.1 建立數(shù)據(jù)庫(kù)與建立表
直接來(lái)看例子：
#!/usr/bin/env python
import sqlite3
conn = sqlite3.connect("test.db");
c = conn.cursor();
c.execute("CREATE TABLE IF NOT EXISTS students (sid INTEGER PRIMARY KEY, name TEXT)");
conn.commit();
conn.close();
這里conn是與數(shù)據(jù)庫(kù)文件test.db的連接對(duì)象，c是conn的光標(biāo)對(duì)象汗菜，通過(guò)c.execute()執(zhí)行建表操作让禀，創(chuàng)建了簡(jiǎn)單的學(xué)生信息表（學(xué)號(hào)，名字）陨界，通過(guò)conn.commit()提交巡揍，最后用conn.close()關(guān)閉連接。
conn.open()發(fā)現(xiàn)文件不存在時(shí)會(huì)自動(dòng)創(chuàng)建菌瘪，這里使用了文件“test.db”腮敌，也可以使用“:memory:”建立內(nèi)存數(shù)據(jù)庫(kù)。
2.3.2 插入俏扩、刪除糜工、修改
為了便于多次運(yùn)行，直接使用了內(nèi)存數(shù)據(jù)庫(kù)：
#!/usr/bin/env python
import sqlite3
conn = sqlite3.connect(":memory:");
c = conn.cursor();
c.execute("CREATE TABLE students (sid INTEGER PRIMARY KEY, name TEXT)");
conn.commit();
c.execute("INSERT INTO students VALUES(?, ?)", (1, "Alice"));
c.execute("INSERT INTO students VALUES(?, ?)", (2, "Bob"));
c.execute("INSERT INTO students VALUES(?, ?)", (3, "Peter"));
c.execute("DELETE FROM students WHERE sid = ?", (1, ));
c.execute("UPDATE students SET name = ? WHERE sid = ?", ("Mark", 3));
conn.commit();
conn.close();
做的事情還是非常簡(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之后加上：
c.execute("SELECT * FROM students");
print c.fetchall();
運(yùn)行一下，輸出結(jié)果為：
fetchall()返回的是記錄數(shù)組略号，可以通過(guò)WHERE子句做更細(xì)致的選擇刑峡。
2.3.4 完整的例子
把上面的操作寫成函數(shù)形式：
#!/usr/bin/env python
#-*-coding:utf-8 -*-
import sqlite3
import os, sys
def initialize(conn):
c = conn.cursor();
c.execute("CREATE TABLE students (sid INTEGER PRIMARY KEY, name TEXT)");
conn.commit();
def insert(conn, sid, name):
c = conn.cursor();
t = (sid, name);
c.execute("INSERT INTO students VALUES (?, ?)", t);
conn.commit();
def delete(conn, sid):
c = conn.cursor();
t = (sid, );
c.execute("DELETE FROM students WHERE sid = ?", t);
conn.commit();
def update(conn, sid, name):
c = conn.cursor();
t = (name, sid);
c.execute("UPDATE students SET name = ? WHERE sid = ?", t);
conn.commit();
def display(conn):
c = conn.cursor();
c.execute("SELECT * FROM students");
print c.fetchall();
db_name = ":memory:";
conn = sqlite3.connect(db_name);
initialize(conn);
print "Insert 3 records."
insert(conn, 1, "Alice");
insert(conn, 2, "Bob");
insert(conn, 3, "Peter");
display(conn);
print "Delete the record where sid = 1."
delete(conn, 1);
display(conn);
print "Update the record where sid = 3."
update(conn, 3, "Mark");
display(conn);
conn.close();
運(yùn)行一下，輸出結(jié)果為：
之后用的例子都是這個(gè)簡(jiǎn)單的學(xué)生信息表(學(xué)號(hào)玄柠，姓名)突梦。
3. MySQL
3.1 準(zhǔn)備
安裝MySQL：
apt-get install mysql-server
apt-get install mysql-client
apt-get install libmysqlclient-dev
安裝MySQLdb：
apt-get install python-mysqldb
使用時(shí)import MySQLdb（注意大小寫）。
3.2 操作流程
同為關(guān)系型數(shù)據(jù)庫(kù)羽利，MySQL的操作方法和SQLite是大同小異的宫患。建立連接對(duì)象與光標(biāo)對(duì)象，用execute()執(zhí)行SQL語(yǔ)句这弧，commi()提交事物娃闲，fetchall()獲得查詢結(jié)果虚汛。
3.3 操作實(shí)例
直接看MySQL版本的完整例子：
#!/usr/bin/env python
#-*-coding: utf-8-*-
import MySQLdb
import os, sys
def initialize(conn):
c = conn.cursor();
c.execute('''CREATE TABLE IF NOT EXISTS students (
sid INT(4) PRIMARY KEY, name VARCHAR(10)
)''');
conn.commit();
def insert(conn, sid, name):
c = conn.cursor();
t = (sid, name);
c.execute("INSERT INTO students VALUES (%s, %s)", t);
conn.commit();
def delete(conn, sid):
c = conn.cursor();
t = (sid, );
c.execute("DELETE FROM students WHERE sid = %s", t);
conn.commit();
def update(conn, sid, name):
c = conn.cursor();
t = (name, sid);
c.execute("UPDATE students SET name = %s WHERE sid = %s", t);
conn.commit();
def display(conn):
c = conn.cursor();
c.execute("SELECT * FROM students");
print c.fetchall();
try:
conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "");
except Exception, e:
print e;
sys.exit();
c = conn.cursor();
c.execute("CREATE DATABASE IF NOT EXISTS test");
conn.commit();
conn.select_db("test");
initialize(conn);
print "Insert 3 records."
insert(conn, 1, "Alice");
insert(conn, 2, "Bob");
insert(conn, 3, "Peter");
display(conn);
print "Delete the record where sid = 1."
delete(conn, 1);
display(conn);
print "Update the record where sid = 3."
update(conn, 3, "Mark");
display(conn);
c.execute("DROP DATABASE test");
conn.commit();
conn.close();
對(duì)比后可以發(fā)現(xiàn)區(qū)別僅是建立連接時(shí)參數(shù)復(fù)雜一些，同時(shí)需要用select_db()選擇數(shù)據(jù)庫(kù)皇帮。
運(yùn)行一下卷哩，輸出結(jié)果為：
4. LMDB
4.1 準(zhǔn)備
學(xué)習(xí)LMDB的時(shí)候不禁想到知乎上的提問(wèn)“有哪些名人長(zhǎng)期生活在其他名人的光環(huán)下”，說(shuō)實(shí)話感覺(jué)查它的人基本都是為了用Caffe……
Anyway属拾，LMDB和SQLite/MySQL等關(guān)系型數(shù)據(jù)庫(kù)不同将谊，屬于key-value數(shù)據(jù)庫(kù)（把LMDB想成dict會(huì)比較容易理解），鍵key與值value都是字符串渐白。
安裝：
pip install lmdb
使用時(shí)import lmdb尊浓。
4.2 操作流程
概況地講，操作LMDB的流程是：
- 通過(guò)env = lmdb.open()打開環(huán)境
- 通過(guò)txn = env.begin()建立事務(wù)
- 通過(guò)txn.put(key, value)進(jìn)行插入和修改
- 通過(guò)txn.delete(key)進(jìn)行刪除
- 通過(guò)txn.get(key)進(jìn)行查詢
- 通過(guò)txn.cursor()進(jìn)行遍歷
- 通過(guò)txn.commit()提交更改
4.3 操作實(shí)例
4.3.1 建立環(huán)境
#!/usr/bin/env python
import lmdb
env = lmdb.open("students");
運(yùn)行一下纯衍，查看當(dāng)前目錄的變化：
可以看到當(dāng)前目錄下多了students目錄栋齿，里面有data.mdb和lock.mdb兩個(gè)文件。
4.3.2 插入托酸、刪除褒颈、修改
插入與修改都用put實(shí)現(xiàn)，刪除用delete實(shí)現(xiàn)励堡。
#!/usr/bin/env python
import lmdb
env = lmdb.open("students");
txn = env.begin(write = True);
txn.put(str(1), "Alice");
txn.put(str(2), "Bob");
txn.put(str(3), "Peter");
txn.delete(str(1));
txn.put(str(3), "Mark");
txn.commit();
注意用txn = env.begin()創(chuàng)建事務(wù)時(shí)谷丸，有write = True才能夠?qū)憯?shù)據(jù)庫(kù)。
4.3.3 查詢
查單條記錄用get(key)应结，遍歷數(shù)據(jù)庫(kù)用cursor刨疼。
直接在上面的代碼commit()之后加上：
txn = env.begin();
print txn.get(str(2));
for key, value in txn.cursor():
print (key, value);
運(yùn)行一下，輸出結(jié)果為：
注意上次commit()之后要用env.begin()更新txn鹅龄。
4.3.4 完整的例子
#!/usr/bin/env python
import lmdb
import os, sys
def initialize():
env = lmdb.open("students");
return env;
def insert(env, sid, name):
txn = env.begin(write = True);
txn.put(str(sid), name);
txn.commit();
def delete(env, sid):
txn = env.begin(write = True);
txn.delete(str(sid));
txn.commit();
def update(env, sid, name):
txn = env.begin(write = True);
txn.put(str(sid), name);
txn.commit();
def search(env, sid):
txn = env.begin();
name = txn.get(str(sid));
return name;
def display(env):
txn = env.begin();
cur = txn.cursor();
for key, value in cur:
print (key, value);
env = initialize();
print "Insert 3 records."
insert(env, 1, "Alice");
insert(env, 2, "Bob");
insert(env, 3, "Peter");
display(env);
print "Delete the record where sid = 1."
delete(env, 1);
display(env);
print "Update the record where sid = 3."
update(env, 3, "Mark");
display(env);
print "Get the name of student whose sid = 3."
name = search(env, 3);
print name;
env.close();
os.system("rm -r students");
運(yùn)行一下揩慕，輸出結(jié)果為：
5. LevelDB
5.1 準(zhǔn)備
同為key-value數(shù)據(jù)庫(kù)，LevelDB的資料比LMDB豐富太多了扮休。值得一提的是LevelDB實(shí)現(xiàn)時(shí)用到了SkipList迎卤，以后有機(jī)會(huì)要親自實(shí)現(xiàn)一下。
安裝：
pip install py-leveldb
使用時(shí)import leveldb玷坠。
5.2 操作流程
LevelDB操作時(shí)類似與LMDB蜗搔，使用Put/Get/Delete谆奥，但是更加簡(jiǎn)單（不需要事務(wù)txn和commit提交）抢腐，同時(shí)支持范圍迭代器RangeIter。
5.3 操作實(shí)例
來(lái)看LevelDB版本的完整例子：
#!/usr/bin/env python
#-*-coding: utf-8-*-
import leveldb
import os, sys
def initialize():
db = leveldb.LevelDB("students");
return db;
def insert(db, sid, name):
db.Put(str(sid), name);
def delete(db, sid):
db.Delete(str(sid));
def update(db, sid, name):
db.Put(str(sid), name);
def search(db, sid):
name = db.Get(str(sid));
return name;
def display(db):
for key, value in db.RangeIter():
print (key, value);
db = initialize();
print "Insert 3 records."
insert(db, 1, "Alice");
insert(db, 2, "Bob");
insert(db, 3, "Peter");
display(db);
print "Delete the record where sid = 1."
delete(db, 1);
display(db);
print "Update the record where sid = 3."
update(db, 3, "Mark");
display(db);
print "Get the name of student whose sid = 3."
name = search(db, 3);
print name;
os.system("rm -r students");
運(yùn)行一下薄坏，輸出結(jié)果為：
此外兄渺，由于沒(méi)有commit()操作缝龄，leveldb中用WriteBatch實(shí)現(xiàn)多條更改一次提交，直接copy示例代碼如下：
batch = leveldb.WriteBatch();
batch.Put('hello', 'world');
batch.Put('hello again', 'world');
batch.Delete('hello');
db.Write(batch, sync = True);
6. 學(xué)習(xí)總結(jié)
這次學(xué)習(xí)四種數(shù)據(jù)庫(kù)操作時(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ù)庫(kù)铅辞，操作時(shí)創(chuàng)建連接對(duì)象connection與光標(biāo)對(duì)象cursor，通過(guò)execute執(zhí)行SQL語(yǔ)句萨醒，commit提交變更斟珊，fetch得到查詢結(jié)果；LMDB與LevelDB都是K-V數(shù)據(jù)庫(kù)富纸，操作時(shí)建立與數(shù)據(jù)庫(kù)的連接囤踩，用put/delete改變數(shù)據(jù)，用get獲取數(shù)據(jù)晓褪，區(qū)別是LMDB中有事務(wù)需要commit堵漱，LevelDB不需要。
7. 參考資料
- SQLite
- SQLite教程：
http://www.runoob.com/sqlite/sqlite-python.html - SQLite全面學(xué)習(xí)：
http://blog.jobbole.com/92796/ - Python文檔關(guān)于sqlite3的介紹：
https://docs.python.org/2/library/sqlite3.html
- SQLite教程：
- MySQL
- LMDB
- Creating an LMDB database in Python：
http://deepdish.io/2015/04/28/creating-lmdb-in-python/ - Python lmdb：
http://blog.csdn.net/ayst123/article/details/44077903 - lmdb 0.87 documentation：
http://lmdb.readthedocs.org/en/latest/
- Creating an LMDB database in Python：
- LevelDB
- py-leveldb示例代碼：
http://www.oschina.net/p/py-leveldb?fromerr=G5QJs7l1 - Having a look at LevelDB：
http://skipperkongen.dk/2013/02/14/having-a-look-at-leveldb/
- py-leveldb示例代碼：
