Python操作SQLite/MySQL/LMDB/LevelDB

分享一篇來自簡書的文章，Python操作四種類型的資料庫，各個步驟都有具體代碼貼出來，非常詳細，原文在：Python操作資料庫。

1. 概述

1.1 前言

最近用Caffe跑自己的數據集，需要學習LMDB和LevelDB，趁此機會複習了SQLite和MySQL的使用，一起整理在此。

代碼：https://github.com/liquidconv/py4db

1.2 環境

使用Ubuntu 14.04，Python 2.7.6。

2. SQLite

2.1 準備

SQLite是一種嵌入式資料庫，它的資料庫就是一個文件。Python 2.5x以上版本內置了SQLite3，使用時直接import sqlite3即可。

2.2 操作流程

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

• 通過sqlite3.open()創建與資料庫文件的連接對象connection；
• 通過connection.cursor()創建游標對象cursor；
• 通過cursor.execute()執行SQL語句；
• 通過connection.commit()提交當前的事務，或者通過cursor.fetchall()獲得查詢結果；
• 通過connection.close()關閉與資料庫文件的連接。

詳細的sqlite3模塊API可以看這裡：

http://www.runoob.com/sqlite/sqlite-python.html

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

2.3 操作實例

2.3.1 建立資料庫與建立表

直接來看例子：

#!/usr/bin/env pythonnnimport sqlite3nnconn = sqlite3.connect("test.db");nc = conn.cursor();nc.execute("CREATE TABLE IF NOT EXISTS students (sid INTEGER PRIMARY KEY, name TEXT)");nconn.commit();nconn.close();n

這裡conn是與資料庫文件test.db的連接對象，c是conn的游標對象，通過c.execute()執行建表操作，創建了簡單的學生信息表（學號，名字），通過conn.commit()提交，最後用conn.close()關閉連接。

conn.open()發現文件不存在時會自動創建，這裡使用了文件「test.db」，也可以使用「:memory:」建立內存資料庫。

2.3.2 插入、刪除、修改

為了便於多次運行，直接使用了內存資料庫：

#!/usr/bin/env pythonnnimport sqlite3nnconn = sqlite3.connect(":memory:");nc = conn.cursor();nc.execute("CREATE TABLE students (sid INTEGER PRIMARY KEY, name TEXT)");nconn.commit();nnc.execute("INSERT INTO students VALUES(?, ?)", (1, "Alice"));nc.execute("INSERT INTO students VALUES(?, ?)", (2, "Bob"));nc.execute("INSERT INTO students VALUES(?, ?)", (3, "Peter"));nnc.execute("DELETE FROM students WHERE sid = ?", (1, ));nnc.execute("UPDATE students SET name = ? WHERE sid = ?", ("Mark", 3));nnconn.commit();nconn.close();n

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

「?」是sqlite3中的佔位符，execute時會用第二個參數元組裡的元素按順序替換。官方文檔里建議出於安全考慮，不要直接用python做字元串拼接。

另外注意不需要每次execute後調用commit。

2.3.3 查詢

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

c.execute("SELECT * FROM students");nprint c.fetchall();n

運行一下，輸出結果為：

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

2.3.4 完整的例子

把上面的操作寫成函數形式：

#!/usr/bin/env pythonn#-*-coding:utf-8 -*-nnimport sqlite3nimport os, sysnndef initialize(conn):n c = conn.cursor();n c.execute("CREATE TABLE students (sid INTEGER PRIMARY KEY, name TEXT)");n conn.commit();nndef insert(conn, sid, name):n c = conn.cursor();n t = (sid, name);n c.execute("INSERT INTO students VALUES (?, ?)", t);n conn.commit();nndef delete(conn, sid):n c = conn.cursor();n t = (sid, );n c.execute("DELETE FROM students WHERE sid = ?", t);n conn.commit();nndef update(conn, sid, name):n c = conn.cursor();n t = (name, sid);n c.execute("UPDATE students SET name = ? WHERE sid = ?", t);n conn.commit();nndef display(conn):n c = conn.cursor();n c.execute("SELECT * FROM students");n print c.fetchall();nndb_name = ":memory:";nconn = sqlite3.connect(db_name);nninitialize(conn);nnprint "Insert 3 records."ninsert(conn, 1, "Alice");ninsert(conn, 2, "Bob");ninsert(conn, 3, "Peter");ndisplay(conn);nnprint "Delete the record where sid = 1."ndelete(conn, 1);ndisplay(conn);nnprint "Update the record where sid = 3."nupdate(conn, 3, "Mark");ndisplay(conn);nnconn.close();n

運行一下，輸出結果為：

之後用的例子都是這個簡單的學生信息表(學號，姓名)。

3. MySQL

3.1 準備

安裝MySQL：

apt-get install mysql-servern apt-get install mysql-clientn apt-get install libmysqlclient-devn

安裝MySQLdb：

apt-get install python-mysqldbn

使用時import MySQLdb（注意大小寫）。

3.2 操作流程

同為關係型資料庫，MySQL的操作方法和SQLite是大同小異的。建立連接對象與游標對象，用execute()執行SQL語句，commi()提交事物，fetchall()獲得查詢結果。

3.3 操作實例

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

#!/usr/bin/env pythonn#-*-coding: utf-8-*-nnimport MySQLdbnimport os, sysnndef initialize(conn):n c = conn.cursor();n c.execute(CREATE TABLE IF NOT EXISTS students (n sid INT(4) PRIMARY KEY, name VARCHAR(10)n ));n conn.commit();nndef insert(conn, sid, name):n c = conn.cursor();n t = (sid, name);n c.execute("INSERT INTO students VALUES (%s, %s)", t);n conn.commit();nndef delete(conn, sid):n c = conn.cursor();n t = (sid, );n c.execute("DELETE FROM students WHERE sid = %s", t);n conn.commit();nndef update(conn, sid, name):n c = conn.cursor();n t = (name, sid);n c.execute("UPDATE students SET name = %s WHERE sid = %s", t);n conn.commit();nndef display(conn):n c = conn.cursor();n c.execute("SELECT * FROM students");n print c.fetchall();nntry:n conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "");nexcept Exception, e:n print e;n sys.exit();nnc = conn.cursor();nc.execute("CREATE DATABASE IF NOT EXISTS test");nconn.commit();nconn.select_db("test");nninitialize(conn);nnprint "Insert 3 records."ninsert(conn, 1, "Alice");ninsert(conn, 2, "Bob");ninsert(conn, 3, "Peter");ndisplay(conn);nnprint "Delete the record where sid = 1."ndelete(conn, 1);ndisplay(conn);nnprint "Update the record where sid = 3."nupdate(conn, 3, "Mark");ndisplay(conn);nnc.execute("DROP DATABASE test");nconn.commit();nconn.close();n

對比後可以發現區別僅是建立連接時參數複雜一些，同時需要用select_db()選擇資料庫。

運行一下，輸出結果為：

4. LMDB

4.1 準備

學習LMDB的時候不禁想到知乎上的提問「有哪些名人長期生活在其他名人的光環下」，說實話感覺查它的人基本都是為了用Caffe……

Anyway，LMDB和SQLite/MySQL等關係型資料庫不同，屬於key-value資料庫（把LMDB想成dict會比較容易理解），鍵key與值value都是字元串。

安裝：

pip install lmdbn

使用時import lmdb。

4.2 操作流程

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

• 通過env = lmdb.open()打開環境
• 通過txn = env.begin()建立事務
• 通過txn.put(key, value)進行插入和修改
• 通過txn.delete(key)進行刪除
• 通過txn.get(key)進行查詢
• 通過txn.cursor()進行遍歷
• 通過txn.commit()提交更改

4.3 操作實例

4.3.1 建立環境

#!/usr/bin/env pythonnnimport lmdbnnenv = lmdb.open("students");n

運行一下，查看當前目錄的變化：

可以看到當前目錄下多了students目錄，裡面有data.mdb和lock.mdb兩個文件。

4.3.2 插入、刪除、修改

插入與修改都用put實現，刪除用delete實現。

#!/usr/bin/env pythonnnimport lmdbnnenv = lmdb.open("students");ntxn = env.begin(write = True);nntxn.put(str(1), "Alice");ntxn.put(str(2), "Bob");ntxn.put(str(3), "Peter");nntxn.delete(str(1));nntxn.put(str(3), "Mark");nntxn.commit();n

注意用txn = env.begin()創建事務時，有write = True才能夠寫資料庫。

4.3.3 查詢

查單條記錄用get(key)，遍歷資料庫用cursor。

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

txn = env.begin();nprint txn.get(str(2));nnfor key, value in txn.cursor():n print (key, value);n

運行一下，輸出結果為：

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

4.3.4 完整的例子

#!/usr/bin/env pythonnnimport lmdbnimport os, sysnndef initialize():n env = lmdb.open("students");n return env;nndef insert(env, sid, name):n txn = env.begin(write = True);n txn.put(str(sid), name);n txn.commit();nndef delete(env, sid):n txn = env.begin(write = True);n txn.delete(str(sid));n txn.commit();nndef update(env, sid, name):n txn = env.begin(write = True);n txn.put(str(sid), name);n txn.commit();nndef search(env, sid):n txn = env.begin();n name = txn.get(str(sid));n return name;nndef display(env):n txn = env.begin();n cur = txn.cursor();n for key, value in cur:n print (key, value);nnenv = initialize();nnprint "Insert 3 records."ninsert(env, 1, "Alice");ninsert(env, 2, "Bob");ninsert(env, 3, "Peter");ndisplay(env);nnprint "Delete the record where sid = 1."ndelete(env, 1);ndisplay(env);nnprint "Update the record where sid = 3."nupdate(env, 3, "Mark");ndisplay(env);nnprint "Get the name of student whose sid = 3."nname = search(env, 3);nprint name;nnenv.close();nnos.system("rm -r students");n

運行一下，輸出結果為：

5. LevelDB

5.1 準備

同為key-value資料庫，LevelDB的資料比LMDB豐富太多了。值得一提的是LevelDB實現時用到了SkipList，以後有機會要親自實現一下。

安裝：

pip install py-leveldbn

使用時import leveldb。

5.2 操作流程

LevelDB操作時類似與LMDB，使用Put/Get/Delete，但是更加簡單（不需要事務txn和commit提交），同時支持範圍迭代器RangeIter。

5.3 操作實例

來看LevelDB版本的完整例子：

#!/usr/bin/env pythonn#-*-coding: utf-8-*-nnimport leveldbnimport os, sysnndef initialize():n db = leveldb.LevelDB("students");n return db;nndef insert(db, sid, name):n db.Put(str(sid), name);nndef delete(db, sid):n db.Delete(str(sid));nndef update(db, sid, name):n db.Put(str(sid), name);nndef search(db, sid):n name = db.Get(str(sid));n return name;nndef display(db):n for key, value in db.RangeIter():n print (key, value);nndb = initialize();nnprint "Insert 3 records."ninsert(db, 1, "Alice");ninsert(db, 2, "Bob");ninsert(db, 3, "Peter");ndisplay(db);nnprint "Delete the record where sid = 1."ndelete(db, 1);ndisplay(db);nnprint "Update the record where sid = 3."nupdate(db, 3, "Mark");ndisplay(db);nnprint "Get the name of student whose sid = 3."nname = search(db, 3);nprint name;nnos.system("rm -r students");n

運行一下，輸出結果為：

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

batch = leveldb.WriteBatch();nbatch.Put(hello, world);nbatch.Put(hello again, world);nbatch.Delete(hello);ndb.Write(batch, sync = True);n

6. 學習總結

這次學習四種資料庫操作時，是按照SQLite -> MySQL -> LMDB -> LevelDB的順序，所以研究SQLite與LMDB花了較長時間，而MySQL與LevelDB很快就搞定了。某種意義上，學習技術和背單詞一樣，當前掌握的單詞越多，背新單詞就越容易——因為可以把新單詞和已經掌握的同義詞聯繫在一起，在腦海里聚成簇。

最後回顧一下，SQLite與MySQL都是關係型資料庫，操作時創建連接對象connection與游標對象cursor，通過execute執行SQL語句，commit提交變更，fetch得到查詢結果；LMDB與LevelDB都是K-V資料庫，操作時建立與資料庫的連接，用put/delete改變數據，用get獲取數據，區別是LMDB中有事務需要commit，LevelDB不需要。

7. 參考資料

• SQLite
• SQLite教程：
  http://www.runoob.com/sqlite/sqlite-python.html
• SQLite全面學習：
  http://blog.jobbole.com/92796/
• Python文檔關於sqlite3的介紹：
  https://docs.python.org/2/library/sqlite3.html
• MySQL
• MySQLdb模塊：

  http://www.oschina.net/code/snippet_16840_1811

• Python操作MySQL：
  http://www.cnblogs.com/rollenholt/archive/2012/05/29/2524327.html
• 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/
• 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/

我的公眾號，有興趣的小夥伴可以關注下：

http://weixin.qq.com/r/AykDG7rEk_fsrf5493wR (二維碼自動識別)