Python中使用sqlite3数据库

Jan 4, 2014

本文代码基于 python 2.x 版本

SQLite是一个小巧的关系型数据库，支持SQL语法。它是嵌入式的数据库，不需要安装后台的服务，或者说它是一个文件型的数据库。只需要在程序中加载几个相关文件，就可以使用。相比于MSSQL、MYSQL这种（中）大型的数据库来说，使用起来可谓相当方便。

sqlite3，即SQLite的第3个主版本，目前最新的版本是3.8.2。Python中默认自带了sqlite3模块，用户不需要安装sqlite就可以直接导入sqlite3模块以使用。

开始 #

Python 中使用sqlite3的具体步骤如下：

• (1) 导入sqlite3模块
• (2) 使用sqlite3.connect()建立数据库连接
• (3) 使用数据库连接对象执行SQL语句
• (4)关闭数据库连接

下面是一个使用sqlite3最基本的代码

#-*- coding: utf-8 -*-
import sqlite3 #导入sqlite3模块

conn = sqlite3.connect('example.db')#建立数据库连接
conn.execute(”'Create Table Users(name TEXT,age INTEGER)”')#执行sql
conn.commit()#提交更改
conn.close() #关闭数据库连接

代码详解 #

import sqlite3 #

导入sqlite3模块。Python自带，无需安装，使用相当方便。

sqlite3.connect() #

该函数用来建立数据库连接，其参数列表是(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements])。除第一个参数database外，其它参数可选。

• database：指定要建立连接的数据库名。如果设置为“:memory:”，则建立一个内存中的数据库，不会存放到磁盘。
• timeout：如果数据库正在被其它连接使用，则其它连接的transaction（事务）提交之前，该数据库会被锁定。timeout指示如果要连接的数据库被锁定了，最长等待时间。默认5秒。
• detect_types：默认为0，即不进行类型检查。该参数与register_converter()配合来增加新的数据库类型。
• isolation_level：事务的隔离等级。默认为None，即自动提交模式，或者是“DEFERRED”、 “IMMEDIATE” 、 “EXCLUSIVE”中的一个。
• check_same_thread：
• factory：用户自己继承自Connect类的一个数据库连接类。（默认情况下是使用sqlite3提供的Connect类）
• cached_statements：缓存sql语句条数。默认为100条。（sqlite中使用了sql语句缓存来提高效率） 如果要连接的数据库不存在，该函数会新建一个数据库。sqlite3.connect()函数执行成功，会返回一个sqlite3.Connection对象（conn）

Connection对象 #

数据库连接的对象[1]。其主要方法有execute()执行SQL语句、executemany()执行多条SQL语句、rollback()回滚、cursor()获取cursor对象、close()关闭连接…

Connection.execute() #

该函数用来执行一条SQL语句。参数列表(sql[, parameters])。这是一个非标准的快捷方法，它创建一个临时的cursor（游标）对象，然后使用该cursor对象的execute()方法来执行该条SQL语句。但是它不能获取SQL语句的返回结果（如select），所以通常用来执行没有返回结果的SQL语句。如上面代码的Create Table语句。如果需要返回执行结果，则需要使用Cursor对象。

Connection.commit() #

该函数用来提交当前的事务（transaction）。如果你不调用该函数，自上次调用commit()之后你所做的更改对其它的数据库连接对象是不可见的。推荐在执行对数据库有更改的SQL语句后，立即调用commit()。

Connection.close() #

该函数用来关闭数据库连接对象。

Cursor对象 #

上面说过，Connection对象的execute()函数执行SQL语句其实是内部创建了一个Cursor对象，而且Connection.execute()不能返回执行结果集。如果要返回执行结果，比如select语句的查询结果集，则使用Cursor对象。

Cursor对象是Connection对象的一个域。使用Cursor对象执行SQL语句的示例代码如下：

#-*- coding: utf-8 -*-
import sqlite3 #导入sqlite3模块

conn = sqlite3.connect('example.db')#建立数据库连接
cursor = conn.cursor() #获取cursor对象

#创建
cursor.execute(”'Create Table Users(name TEXT primary key,age INTEGER)”' )
conn.commit()

#插入
cursor.execute(”'insert into Users values ('Jarvis',25) ”')
cursor.execute(”'insert into Users values ('Chu',25) ”')
conn.commit()

#查询
cursor.execute(”'select * from Users”')
for row in cursor:
print row, row[0], row[1]
#输出结果为
# (u'Jarvis', 25) Jarvis 25
# (u'Chu', 25) Chu 25

conn.close()

Cursor.execute() #

用来执行SQL语句。如果要在SQL语句加入变量，有两种方式，如下代码所示。

#该段代码摘选自官方文档
import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table people (name_last, age)")

who = "Yeltsin"
age = 72

# This is the qmark style:
cur.execute("insert into people values (?, ?)", (who, age))

# And this is the named style:
cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})

print cur.fetchone()

Cursor.executescript() #

用来执行一段SQL脚本代码。这样就可以不局限于一条SQL语句，可以执行一段SQL代码

Cursor.fetchone()、Cursor.fetchmany()、Cursor.fetchall()

都是用来取回查询结果集。不同之处在于每次取回的数量。fetchone每次返回查询结果集中的一行，并自动移至下一行，如果没有数据，则返回None；fetchmany(size)返回指定数目行；fetchall()返回查询结果集的所有行。

数据类型 #

sqlite3中只有5种原生数据类型：NULL，INTEGER，REAL，TEXT，BLOB，分别表示空、整数、实数、文本、数据块。sqlite3中的数据类型。Python中的数据类型与sqlite3中的类型对应关系如下官方文档：

Python类型到sqlite3类型的默认对应转换关系

sqlite3类型到Python类型的默认对应转换关系

如果需要存储其它的Python数据类型，比如我们自定义的class，则需要通过适配器（adapter）来完成。具体方法有两种：

(1) 让对象本身自适应 #

比如有一个Point类，有两个参数x,y。如果我们需要sqlite3存储为x;y的形式，则需要在Point类中增加__conform__(self, protocol)函数，使用return语句返回要存储的形式。如下代码所示

import sqlite3

class Point(object):
    def __init__(self, x, y):
        self.x, self.y = x, y

    def __conform__(self, protocol):
        if protocol is sqlite3.PrepareProtocol:
            return "%f;%f" % (self.x, self.y)

con = sqlite3.connect("test.db")#:memory:
cur = con.cursor()

cur.execute(”'create table Test(data)”')

p = Point(4.0, -3.2)
cur.execute(”'insert into Test values(?)”',(p,))
#p在数据库中存储为：4.000000;-3.200000
con.commit()

cur.execute("select * from Test")
print cur.fetchone()[0]
#输出 4.000000;-3.200000

(2) 注册一个适配器回调函数 #

使用register_adapter()函数注册一个回调函数，声明对某个Python类型如果作转换。示例代码如下

import sqlite3

class Point(object):
    def __init__(self, x, y):
        self.x, self.y = x, y

    def adapt_point(point):
        return "%f;%f" % (point.x, point.y)

sqlite3.register_adapter(Point, adapt_point)

con = sqlite3.connect(":memory:")
cur = con.cursor()

p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print cur.fetchone()[0]

更多内容，参阅官方文档