sqlite3 遵循Python PDB-API 2.0 接口规范的SQLite库(2)

13.13.3 Cursor 对象
一个cursor实例拥有如下的属性与方法：
l       execute( sql, [parameters])
执行一个SQL语句。SQL语句可以是参数化的（用占位符来代替具体的字面值）。Sqlite3支持两种类型的占位符：问号与命名。
下面的例子展示了如果使用问号：
import sqlite3
con = sqlite3.connect("mydb")
cur = con.cursor()
who = "Yeltsin"
age = 72
cur.execute("select name_last, age from people where name_last=? and age=?", (who, age))
print cur.fetchone()
下面的例子展示了如何使用命名：
import sqlite3
con = sqlite3.connect("mydb")
cur = con.cursor()
who = "Yeltsin"
age = 72
cur.execute("select name_last, age from people where name_last=:who and age=:age",
    {"who": who, "age": age})
print cur.fetchone()
方法execute()只能执行一条单独的SQL语句。如果你想要执行超过一条语句，将会引发一个warning。可以使用executescript()方法来在一次调用中执行多条SQL语句。
l       executemany( sql, seq_of_parameters)
使用seq_of_parameters中的值来多次调用SQL语句，seq_of_parameters可以是序列或映射。Sqlite3模块也支持会用迭代器生成参数来代替使用序列。
import sqlite3
class IterChars:
    def __init__(self):
        self.count = ord('a')
    def __iter__(self):
        return self
    def next(self):
        if self.count > ord('z'):
            raise StopIteration
        self.count += 1
        return (chr(self.count - 1),) # this is a 1-tuple
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table characters(c)")
theIter = IterChars()
cur.executemany("insert into characters(c) values (?)", theIter)
cur.execute("select c from characters")
print cur.fetchall()
下面是一个使用迭代生成器的例子：
import sqlite3
def char_generator():
    import string
    for c in string.letters[:26]:
        yield (c,)
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table characters(c)")
cur.executemany("insert into characters(c) values (?)", char_generator())
cur.execute("select c from characters")
print cur.fetchall()
l       executescript( sql_script)
这是一个非标准的可以在一次调用方便地执行多句SQL语句的方法。它首先发出一个COMMIT语句，之后执行SQL语句。
参数sql_script可以是一个bytestring或Unicode编码的字符串。
示例如下：
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.executescript("""
    create table person(
        firstname,
        lastname,
        age
    );
    create table book(
        title,
        author,
        published
    );
    insert into book(title, author, published)
    values (
        'Dirk Gently''s Holistic Detective Agency',
        'Douglas Adams',
        1987
    );
    """)
l       rowcount
虽然sqlite3模块中的cursor类实现了这个属性，但是数据库引擎自己对于“行数”统计的能力还是要强得多。
对于SELECT语句，rowcount总是为None，因为我们在所有行被取回前不能确定总的行数。
对于DELETE语句，当你在DELETE FROM 语句中没有使用任何条件的话，rowcount为0，
对于executemany语句，所有进行修改的行数都会被总计到rowcount中。
作为Python DB API规范的要求，在没有executeXX()方法被执行的情况下，或者在最近的一次操作中不能确定行数，则rowcount属性值将会是-1。
13.13.4 SQLite与Python类型
13.13.4.1 简介
SQLite只支持以下的内建类型：NULL, INTEGER, REAL, TEXT, BLOB.
因此，以下的Python类型可以毫无问题地传入SQLite。
Python type             SQLite type
None                   NULL
int                     INTEGER
long                   INTEGER
float                    REAL
str (UTF8-encoded)        TEXT
unicode                 TEXT
buffer                   BLOB
下表说明了默认情况下SQLite类型如何被转换为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
Sqlite3的类型系统可以有两种扩展方法：你可以通过对象适配器来将Python对象转换并存储到Sqlite数据库中，可以通过转换器将SQLite类型转换为Python对象。
13.13.4.2 使用适配器将其它Pyton类型转换到SQLite数据库中
象前面所说，SQLite本身只支持很少的类型。要在SQLite程序中使用其它的Python类型，你必须要将它们适配成为一种SQLite支持的数据类型：NoneType, int, long, float, str, unicode, buffer。
Sqlite3模块使用Python对象适配器，就象在PEP246中所述的一样。所使用的协议为PrepareProtocol。
有两种方法可以将一个定制Python类型适配成为SQLite支持的类型。
13.13.4.2.1 让你的对象具有自我适配能力
对于你自己定义的类，这是个比较好的办法，让我们假设你有一个类：
class Point(object):
    def __init__(self, x, y):
        self.x, self.y = x, y
现在你想将point信息存储在一个SQLite列中。首先，你要选择一个SQLite支持的类型来表示这个point对象。我们可以使用str类型，并坐标X与Y之间加入一个分号。然后，你需要在类中定义一个方法 __conform__(self, protocol)，用来返回转换后的值。记住参数protocol要使用PrepareProtocol。
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(":memory:")
cur = con.cursor()
p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print cur.fetchone()[0]
13.13.4.2.2 注册一个适配器callable
另外一种可能是创建一个函数来将类型转换成表示它的字符串，并通过register_adapter来注册它。
注意：被适配的类型/类必须是一个新式样的class，就是说object类必须是它的基类之一。
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]
sqlite3模块有两个针对Python内建类型datetime.date及datetime.datetime的默认适配器。现在假设我们想要存储datetime.datetime对象。
import sqlite3
import datetime, time
def adapt_datetime(ts):
    return time.mktime(ts.timetuple())
sqlite3.register_adapter(datetime.datetime, adapt_datetime)
con = sqlite3.connect(":memory:")
cur = con.cursor()
now = datetime.datetime.now()
cur.execute("select ?", (now,))
print cur.fetchone()[0]
13.13.4.3 将SQLite值转换为Python定制类型
通过编写一个适配器可以让你将Python类型送入SQLite。但是要想让它发挥作用，我们还得完成从SQLite转换到Python的过程。
让我们回到Point 类。我们在SQLite中用分号分隔的格式存储了X与Y坐标信息。
首先，我们将定义一个转换函数，接收一个字符串作为参数，并从中构建一个Point对象。
注意：转换器函数总是用一个字符串参数来调用，而不管当初你送入SQLite的类型。
注意：转换器的名字是大小写敏感的。
    def convert_point(s):
        x, y = map(float, s.split(";"))
        return Point(x, y)
现在你要让sqlite3模块知道数据库中的那列数据是一个Point。有两种方法来实现：
l       通过声明类型隐含地表示
l       通过列名字明确地表示
两种方法都由模块常量来描述，还记得在13.13.1部分，常量PARSE_DECLTYPES与PARSE_COLNAMES。
下面的例子展示了这两种方法：
import sqlite3
class Point(object):
    def __init__(self, x, y):
        self.x, self.y = x, y
    def __repr__(self):
        return "(%f;%f)" % (self.x, self.y)
def adapt_point(point):
    return "%f;%f" % (point.x, point.y)
def convert_point(s):
    x, y = map(float, s.split(";"))
    return Point(x, y)
# Register the adapter
sqlite3.register_adapter(Point, adapt_point)
# Register the converter
sqlite3.register_converter("point", convert_point)
p = Point(4.0, -3.2)
#########################
# 1) 使用声明类型
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test(p point)")
cur.execute("insert into test(p) values (?)", (p,))
cur.execute("select p from test")
print "with declared types:", cur.fetchone()[0]
cur.close()
con.close()
#######################
# 1) 使用列名字
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(p)")
cur.execute("insert into test(p) values (?)", (p,))
cur.execute('select p as "p [point]" from test')
print "with column names:", cur.fetchone()[0]
cur.close()
con.close()
13.13.4.4 默认适配器与转换器
在datatime模块中有针对date与datetime的默认适配器。它们将会被按ISO date或ISO timestamps格式被传入到SQLite中。
注册好的默认转换器为datetime.date类型使用名字date，为类型datetime.datetime使用名字timestamp。
这样，大部分情况下，你可以在Python中使用date或timestamps而不需要任何额外的处理。同时，适配器使用的格式也与正在试验中的SQLite date/time函数保持兼容。
以下是示例：
import sqlite3
import datetime
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES )
cur = con.cursor()
cur.execute("create table test(d date, ts timestamp)")
today = datetime.date.today()
now = datetime.datetime.now()
cur.execute("insert into test(d, ts) values (?, ?)", (today, now))
cur.execute("select d, ts from test")
row = cur.fetchone()
print today, "=>", row[0], type(row[0])
print now, "=>", row[1], type(row[1])
cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"')
row = cur.fetchone()
print "current_date", row[0], type(row[0])
print "current_timestamp", row[1], type(row[1])
13.13.5 控制事务
默认情况，sqlite3模块在数据修改语句（DML，如INSERT，UPDATE， DELETE， REPLACE）开始执行前隐含地打开一个事务，并在开始一个非数据修改语句（任何非SELECT， INSERT， UPDATE， DELETE， REPLACE的语句）前确认事务。
所以若你在一个事务内，并且发出一个像CREATE TABLE，VACUUM，PRAGMA这样的命令，sqlite3将会在执行它们之前隐含地结束事务。有两个理由要这样做，第一个是一些这样的命令不能在事务中处理，另一个是pysqlite必须要对事务状态保持跟踪。
通过connect调用时的isolation_level，或者connection对象的isolation_level属性，我们都可以控制pysqlite隐含使用那种类型的”BEGIN”语句（或根本不执行此类语句）。
若你想使用autocommit模式，将isolation_level设置为None。
要不然保持isolation_level的默认值，这将导致一个清楚的”BEGIN”语句，或者设成SQLite支持的值之一：DEFERRED, IMMEDIATE or EXCLUSIVE。
因为sqlite3模块需要对事务状态保持跟踪，你还不应该在SQL中使用OR ROLLBACK或ON CONFLICT ROLLBACK语句，而要捕捉IntegrityError错误，再由你自己调用connection的回滚方法。
13.13.6 高效使用pysqlite
13.13.6.1 使用快捷方法
使用connection对象的非标准的execute，executemany，及executescript方法，可以写出更加简洁的代码，因为不再需要明确地创建Cursor对象。Cursor对象会被隐含地创建，并由这些方法执行完后返回。我们可以只用一次调用就可以执行一句SELECT语句，并直接迭代处理结果集。
import sqlite3
persons = [
    ("Hugo", "Boss"),
    ("Calvin", "Klein")
    ]
con = sqlite3.connect(":memory:")
# Create the table
con.execute("create table person(firstname, lastname)")
# Fill the table
con.executemany("insert into person(firstname, lastname) values (?, ?)", persons)
# Print the table contents
for row in con.execute("select firstname, lastname from person"):
    print row
# Using a dummy WHERE clause to not let SQLite take the shortcut table deletes.
print "I just deleted", con.execute("delete from person where 1=1").rowcount, "rows"
13.13.6.2 使用列名而不是列索引来访问列数据
Sqlite3模块一个有用的功能是内建的sqlite3.Row类，它被设计作为一个row_factory。
用这个类封闭的row可以通过列索引（像tuple）或列名（大小写敏感）来访问。
import sqlite3
con = sqlite3.connect("mydb")
con.row_factory = sqlite3.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"]