Python操作SQLite/MySQL/LMDB/LevelDB
admin
2023-07-30 20:44:57
0次

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 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是与数据库文件test.db的连接对象，c是conn的光标对象，通过c.execute()执行建表操作，创建了简单的学生信息表（学号，名字），通过conn.commit()提交，最后用conn.close()关闭连接。

conn.open()发现文件不存在时会自动创建，这里使用了文件“test.db”，也可以使用“:memory:”建立内存数据库。

2.3.2 插入、删除、修改

为了便于多次运行，直接使用了内存数据库：

#!/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();

做的事情还是非常简单易懂的，向学生信息表中插入（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\");
print c.fetchall();

运行一下，输出结果为：


test_query.py

fetchall()返回的是记录数组，可以通过WHERE子句做更细致的选择。

2.3.4 完整的例子

把上面的操作写成函数形式：

#!/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();

运行一下，输出结果为：


test_sqlite.py

之后用的例子都是这个简单的学生信息表(学号，姓名)。

3. MySQL

3.1 准备

安装MySQL：

 apt-get install mysql-server
 apt-get install mysql-client
 apt-get install libmysqlclient-dev

安装MySQLdb：

apt-get install python-mysqldb

使用时import MySQLdb（注意大小写）。

3.2 操作流程

同为关系型数据库，MySQL的操作方法和SQLite是大同小异的。建立连接对象与光标对象，用execute()执行SQL语句，commi()提交事物，fetchall()获得查询结果。

3.3 操作实例

直接看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();

对比后可以发现区别仅是建立连接时参数复杂一些，同时需要用select_db()选择数据库。
运行一下，输出结果为：


test_mysql.py

4. LMDB

4.1 准备

学习LMDB的时候不禁想到知乎上的提问“有哪些名人长期生活在其他名人的光环下”，说实话感觉查它的人基本都是为了用Caffe……

Anyway，LMDB和SQLite/MySQL等关系型数据库不同，属于key-value数据库（把LMDB想成dict会比较容易理解），键key与值value都是字符串。

安装：

pip install lmdb

使用时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 python

import lmdb

env = lmdb.open(\"students\");

运行一下，查看当前目录的变化：


set_env.py

可以看到当前目录下多了students目录，里面有data.mdb和lock.mdb两个文件。

4.3.2 插入、删除、修改

插入与修改都用put实现，删除用delete实现。

#!/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()创建事务时，有write = True才能够写数据库。

4.3.3 查询

查单条记录用get(key)，遍历数据库用cursor。

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

txn = env.begin();
print txn.get(str(2));

for key, value in txn.cursor():
    print (key, value);

运行一下，输出结果为：


test_query.py

注意上次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\");

运行一下，输出结果为：


test_lmdb.py

5. LevelDB

5.1 准备

同为key-value数据库，LevelDB的资料比LMDB丰富太多了。值得一提的是LevelDB实现时用到了SkipList，以后有机会要亲自实现一下。

安装：

pip install py-leveldb

使用时import leveldb。

5.2 操作流程

LevelDB操作时类似与LMDB，使用Put/Get/Delete，但是更加简单（不需要事务txn和commit提交），同时支持范围迭代器RangeIter。

5.3 操作实例

来看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\");

运行一下，输出结果为：


test_leveldb.py

此外，由于没有commit()操作，leveldb中用WriteBatch实现多条更改一次提交，直接copy示例代码如下：

batch = leveldb.WriteBatch();
batch.Put(\'hello\', \'world\');
batch.Put(\'hello again\', \'world\');
batch.Delete(\'hello\');
db.Write(batch, sync = True);

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/

上一篇：【同行说技术】Python开发、调试、爬虫类工具大全

下一篇：Tornado 4.3 文档翻译: 用户指南-模板和UI

相关内容

热门资讯

500 行 Python 代码... 语法分析器描述了一个句子的语法结构，用来帮助其他的应用进行推理。自然语言引入了很多意外的歧义，以我们...
定时清理删除C:\Progra... C:\Program Files (x86)下面很多scoped_dir开头的文件夹 写个批处理 定...
65536是2的几次方 计算2... 65536是2的16次方：65536=2⁶ 65536是256的2次方：65536=256 6553...
Mobi、epub格式电子书如... 在wps里全局设置里有一个文件关联，打开，勾选电子书文件选项就可以了。
scoped_dir32_70... 一台虚拟机C盘总是莫名奇妙的空间用完，导致很多软件没法再运行。经过仔细检查发现是C:\Program...
pycparser 是一个用... `pycparser` 是一个用 Python 编写的 C 语言解析器。它可以用来解析 C 代码并构...
小程序支付时提示：appid和... [Q]小程序支付时提示：appid和mch_id不匹配 [A]小程序和微信支付没有进行关联，访问“小...
python绘图库Matplo... 本文简单介绍了Python绘图库Matplotlib的安装，简介如下： matplotlib是pyt...
Prometheus+Graf... 一，Prometheus概述 1，什么是Prometheus？Prometheus是最初在Sound...
微信小程序使用slider实现... 众所周知哈，微信小程序里面的音频播放是没有进度条的，但最近有个项目呢，客户要求音频要有进度条控制，所...