Python操作SQLite/MySQL/LMDB/LevelDB

1. 概述

1.1 前言

最近用Caffe跑自己的数据集，需要学习LMDB和LevelDB，趁此机会复习了SQLite和MySQL的使用，一起整理在此。

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();

运行一下，输出结果为：

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();