«
»

pysqlite使用指南

pysqlite使用指南

最后更新: pysqlite 2.3.0

目录

1 简介

本使用指南并非Python、SQL、SQLite的入门指南；而是pysqlite功能的介绍，并且包含了使用模式的示例代码。这个指南将会指导你使用Python的数据库接口和SQLite的文档。

2 Python数据库接口2.0兼容

2.1 不兼容的选项

pysqlite包含不兼容的DB API 2.0的功能，如下：

  • 在cursor.description中并不包含类型信息

    cursor.description是一个元组包含如下字段(name,type_code,display_size,internal_size,precision,scale,null_ok)来定义每个列。特定的数据库接口需要至少name和type_code，但是有时cursor.description是内置的，pysqlite不只能检测到任何类型。所以，pysqlite只在cursor.description中填入name字段。其他的字段全部设置为None。

  • 没有类型对象

    同样的，模块级并没有STRING、BINARY、NUMBER、DATETIME、ROWID，并且也没什么用。

2.2 不支持的可选功能

Cursor 类
不支持nextset方法。这个方法没有实现是因为数据库引擎也没有实现单个游标返回多个结果集。

2.3 有名无实支持的可选功能

Cursor 类

  • arraysize 属性

    作为最基本的需求，这个属性的值用于支持 fetchmany 方法。不过改变这个值也没什么关系，因为数据库引擎根本就只支持一次获取一行。

  • setinputsizes 方法

    尽管提供了这个方法，但是什么都不做。

  • setoutputsize 方法

    什么活都不干的方法。

2.4 扩展与警告

pysqlite提供了大量的功能来实现对Python DB API的基本支持。大部分扩展在已经在本节的Native Database Engine Features and Extensions Beyond the Python DB API中有所介绍。

  • connect 函数

    参数 database 指定SQLite数据库的文件。一般指定文件系统的绝对或相对路径。

    这个链接函数支持如下可选参数：

    • timeout 当多个连接同时存取数据库时，并且其中一个进程修改了数据库，SQLite数据库会锁定(locked)直到事务被提交。这个超时参数指定连接会等待这个锁定多长时间，并抛出异常。缺省的超时参数是5.0秒。例如: 
      sqlite.connect(database="mydb",timeout=10.0)
    • isolation_level pysqlite将会缺省的以”BEGIN”语句开始一个事务，当使用DML语句如INSERT/UPDATE/DELETE/REPLACE。一些用户并不想pysqlite暗自打开事务，而希望使用自动提交模式。其他用户想要pysqlite打开不同类型的事务，例如”BEGIN IMMEDIATE”。查看 控制事务 了解更多细节。注意你通过设置isolation_level属性可以选择不同的isolation级别。例如: 
      # 打开自动提交模式
con=sqlite.connect("mydb",isolation_level=None)
# 将isolation_level设置到"IMMEDIATE"
con.isolation_level="IMMEDIATE"
    • detect_types SQLite本来支持的类型包括TEXT、INTEGER、FLOAT、BLOB、NULL。如果你想使用其他类型，你需要自己添加支持。 detect_types 参数和使用自定义的 converters 来使用 register_converter 函数允许你做到这些。 detect_types 缺省是0，就是关闭，你可以设置 PARSE_DECLTYPES 和 PARSE_COLNAMES 的组合来改变设置的类型。参见 SQLite与Python类型 来了解更多。
      • sqlite.PARSE_DECLTYPES 这会

Warning

pause

3 简要入门

这个教会你如何初步的使用pysqlite。而并非是一个Python Database API入门，也不是覆盖其他使用的方面。

3.1 连接到数据库

例子1

连接到数据库文件 mydb

from pysqlite2 import dbapi2 as sqlite
con=sqlite.connect("mydb")

例子2

创建一个内存数据库:

from pysqlite2 import dbapi2 as sqlite
con=sqlite.connect(":memory:")

3.2 执行SQL语句

为了执行这一节，我们需要一个数据库按照如下定义:

CREATE TABLE people
(
    name_last   varchar(20),
    age         integer
);
INSERT INTO people (name_last,age) VALUES ('Yeltsin',72);
INSERT INTO people (name_last,age) VALUES ('Putin',51);

例子1

这个例子显示了一个打印 people 表格内容的最简单的例子:

from pysqlite2 import dbapi2 as sqlite
# 创建数据库连接到文件"mydb"
con=sqlite.connect("mydb")
# 获取游标对象
cur=con.cursor()
# 执行SELECT语句
cur.execute("SELECT * FROM people ORDER BY age")
# 获取所有行并显示
print cur.fetchall()

输出:

[(u'Putin', 51), (u'Yeltsin', 72)]

例子2

如下是另一个小例子展示了如何单行显示记录:

from pysqlite2 import dbapi2 as sqlite
con=sqlite.connect("mydb")
cur=con.cursor()
SELECT="SELECT name_last,age FROM people ORDER BY age, name_last"
# 1. 第一种显示记录的方法
cur.execute(SELECT)
for (name_last,age) in cur:
    print '%s is %d years old.'%(name_last,age)
# 2. 第二种显示记录的方法
cur.execute(SELECT)
for row in cur:
    print '%s is %d years old.'%(row[0], row[1])

输出:

Putin is 51 years old.
Yeltsin is 72 years old.
Putin is 51 years old.
Yeltsin is 72 years old.

例子3

如下的程序以表格的方式打印表格内容:

from pysqlite2 import dbapi2 as sqlite
FIELD_MAX_WIDTH=20
TABLE_NAME='people'
SELECT="SELECT * FROM %s ORDER BY age,name_last"%TABLE_NAME
con=sqlite.connect("mydb")
cur=con.cursor()
cur.execute(SELECT)
#打印表头
for fieldDesc in cur.description:
    print fieldDesc[0].ljust(FIELD_MAX_WIDTH),
print #结束表头行
print '-'*78

#每行打印一次值
fieldIndices=range(len(cur.description))
for row in cur:
    for fieldIndex in fieldIndices:
        fieldValue=str(row[fieldIndex])
        print fieldValue.ljust(FIELD_MAX_WIDTH),
    print

输出:

name_last               age
---------------------------------------------
Putin                   51
Yeltsin                 72

例子4

插入人员信息到 people 表格:

from pysqlite2 import dbapi2 as sqlite
con=sqlite.connect("mydb")
cur=con.cursor()
newPeople=(
    ('Lebed',53),
    ('Zhirinovsky',57),
)
for person in newPeople:
    cur.execute("INSERT INTO people (name_last,age) VALUES (?,?)",person)
#修改之后必须明确的提交
con.commit()

注意参数化的SQL语句。当处理重复语句时，这会更快并产生更少的错误，相对于手动生成SQL语句。

而上面的核心语句:

for person in newPeople:
    cur.execute("INSERT INTO people (name_last,age) VALUES (?,?)",person)

可以被重写为:

cur.executemany("INSERT INTO people (name_last,age) VALUES (?,?)",newPeople)

在例子4的后面的打印结果为:

name_last                 age
-------------------------------------------
Putin                     51
Lebed                     53
Zhirinovsky               57
Yeltsin                   72

4 数据库引擎的本地功能和Python DB API的扩展功能

4.1 创建用户自定义函数

SQLite支持用户自定义函数。使用pysqlite，你使用连接的 create_function 方法来创建新函数:

def create_function(self,name,numparams,func)

如下是参数说明：

  • name

    SQL函数的名字

  • numparams

    函数接收的参数数量，-1是接收不限制数量的参数

  • func

    Python函数

函数可以返回任何pysqlite支持的SQLite类型：unicode、str、int、long、float、buffer和None。任何用户自定义函数中的异常都会导致SQL语句的执行中断。

例子:

from pysqlite2 import dbapi2 as sqlite
import md5
def md5sum(t):
    return md5.md5(t).hexdigest()
con=sqlite.connect(":memory:")
con.create_function("md5",1,md5sum)
cur=con.cursor()
cur.execute("SELECT md5(?)", ("foo",))
print cur.fetchone()[0]

4.2 创建用户自定义聚合体类型

SQLite支持用户自定义聚合类型函数。使用

Warning

pause

4.3 创建和使用对比函数

Warning

pause

4.4 检查语句完整性

Warning

pause

4.5 启用SQLite的共享缓存

Warning

pause

4.6 设置认证回调函数

Warning

pause

5 SQLite与Python类型

5.1 介绍

详见 http://sqlite.org/datatype3.html

Warning

pause

5.2 使用Python类型的SQLite数据库存储适配器

Warning

pause

5.3 转换SQLite值到Python类型

Warning

pause

5.4 缺省的pysqlite适配器和转换器

Warning

pause

6 控制事务

缺省时，pysqlite在DML语句(INSERT/UPDATE/DELETE/REPLACE)语句之前自动开启一个事务，并且在一个非DML语句或非DQL语句之前自动提交(除了SELECT/INSERT/UPDATE/DELETE/REPLACE)语句之外。

所以如果你在一个事务当中，要执行类似 CREATE TABLE … ， VACUUM ， PRAGMA 之类的命令，则pysqlite会在执行这些命令之前提交。有两个原因需要这么做。首先，大多数此类命令并不是工作在事务当中的。第二个原因是pysqlite需要保持对事务状态的跟踪，无论事务处于活跃状态与否。

你可以自己控制何种类型的”BEGIN”语句会自动执行(没有，或者所有)，通过设置 connect 的 isolation_level 参数，或者连接对象的 isolation_level 属性。

如果你想要开启 autocommit 模式，设置 isolation_level 为None。

否则让这个值为缺省，将会使用平坦的”BEGIN”语句。或者设置一个SQLite支持的隔离(isolation)级别：DEFERRED(延时)、IMMEDIATE(立即)或EXCLUSIVE(独占)。

因为pysqlite需要对事务状态进行跟踪，所以你将不能使用 OR ROLLBACK 或 ON CONFLICT ROLLBACK 。作为交换，捕捉 IntegrityError 异常并手动调用连接的 rollback 方法。

7 高效的使用pysqlite

7.1 使用命令的捷径

使用非标准的连接的 execute() 、 executemany() 、 executescript() 方法，你的代码可以更加简明，因为你不需要再创建多余的游标对象了。作为交换，游标对象会隐含的创建并使用。这种方法，你可以直接在对象之上使用SELECT语句并使用序列方法。:

from pysqlite2 import dbapi2 as sqlite
persons=[
    ("Hugo","Boss"),
    ("Calvin","Klein")
    ]
con=sqlite.connect(":memory:")
#创建表格
con.execute("CREATE TABLE person (firstname,lastname)")
#填写表格
con.executemany("INSERT INTO person(firstname,lastname) VALUES (?,?)",
    persons)
#打印表格内容
for row in con.execute("SELECT firstname,lastname FROM person"):
    print row
#使用一个哑WHERE子句来让SQLite删除表格
print "I just deleted",con.execute("DELETE FROM person WHERE 1=1").rowcount,"rows"

7.2 使用列名而不是序号存取字段

一个pysqlite2.1.0的新功能是新的内建的sqlite.Row类设计作为记录工厂。 Rows包装了这个类并允许同时使用列序号(类似元组)和大小写不敏感的列名:

from pysqlite2 import dbapi2 as sqlite
con=sqlite.connect("mydb")
con.row_factory=sqlite.Row
cur=con.cursor()
cur.execute("SELECT name_last,age FROM people")
for row in cur:
    assert row[0] == row["name_last"]
    assert row["name_last"] == row["nAmE_lAsT"]
    assert row[1] == row["age"]
    assert row[1] == row["Age"]

This entry was posted on Tuesday, September 11th, 2007 at 19:44 and is filed under Database, Python. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply