Python中使用sqlite3数据库

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最基本的代码

[python]#-*- 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() #关闭数据库连接[/python]

代码详解

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语句的示例代码如下:

[python]#-*- 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()[/python]

Cursor.execute()

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

[python]#该段代码摘选自官方文档
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()[/python]

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类型的默认对应转换关系

Python type SQLite type
None NULL
int INTEGER
long INTEGER
float REAL
str (UTF8-encoded) TEXT
unicode TEXT
buffer BLOB

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

SQLite type Python type
NULL None
INTEGER int or long, depending on size
REAL float
TEXT depends on text_factory, unicode by default
BLOB buffer

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

(1) 让对象本身自适应

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

[python]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[/python]

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

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

[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][/python]

更多内容,参阅官方文档

作者:JarvisChu
原文链接:Python中使用sqlite3数据库
版权声明:自由转载-非商用-非衍生-保持署名 | Creative Commons BY-NC-ND 3.0

发表评论