Sqlite3

1. 创建一个Connection对象用于表示一个db,代码如下：

   import sqlite3conn = sqlite3.connect(‘example.db‘)#如果要在RAM中创建db，可以使用特定名字:memory:

   
   当有多个connection同时访问db并且有一个进程在修改db时，db一直是被lock的，直到transaction被提交，connect()的常用参数:
   • timeout参数指定连接等待多久然后抛出异常，默认是5秒.
   • isolation_level:设置如何开启tranction,默认遇到DML语句时打开transaction,遇到non-DML语句时提交transaction
   • cached_statements:本次connection可以在缓存中存放的语句数，默认为100
   • uri:boolean型，使用uri指定db,可以指定连接的option,如以只读方式打开db
     db = sqlite3.connect(‘file:path/to/database?mode=ro‘, uri=True)
2. 创建一个Cursor对象用于执行sql命令,要注意sql语句的安全，另外对于execute,executemany和executescript函数可以直接使用Connection对象调用，函数内会自动创建cursor，并返回cursor对象

   c=conn.cursor()# Never do this -- insecure! symbol = ‘RHAT‘ c.execute("SELECT * FROM stocks WHERE symbol = ‘%s‘" % symbol) # Do this instead,注意元组中只有一个元素时后面要有逗号#sql语句中的替换符为? t = (‘RHAT‘,) c.execute(‘SELECT * FROM stocks WHERE symbol=?‘, t) print(c.fetchone()) # Larger example that inserts many records at a time purchases = [(‘2006-03-28‘, ‘BUY‘, ‘IBM‘, 1000, 45.00), (‘2006-04-05‘, ‘BUY‘, ‘MSFT‘, 1000, 72.00), (‘2006-04-06‘, ‘SELL‘, ‘IBM‘, 500, 53.00), ] c.executemany(‘INSERT INTO stocks VALUES (?,?,?,?,?)‘, purchases)

3. 如果使用execute执行完查询语句后，获取数据可以使用如下方法:
   • 将cursor当做一个iterator,使用for语句每次获取一行row数据
   • 使用fetchone(),每次获取一行row数据,最后返回None
   • 使用fetchall(),获取row的list,没有数据则返回None
4. 将sqlite不支持的python类型加到sqlite中,首先要将该类型转换成sqlite支持的类型，通常用str。具体方法有两种:
   • 实现类的__conform__方法
     

     __conform__import sqlite3class Point:    def __init__(self, x, y):        self.x, self.y = x, y    def __conform__(self, protocol):     #注意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])

   • 使用sqlite3.register_adapter为类注册一个adapter方法,将类型转换成str,sqlite3 module内部为python的内置类型datetime.date和datetime.datetime提供了adapter,将它们转换为iso。
     

     adapterimport sqlite3import datetimeimport timedef 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])

     
     还可以使用sqlite3.register_converter为类注册一个convert方法，将数据库中的值转换为python类型。为了使sqlite3知道从数据库中查询出的数据的实际类型需要设置sqlite.connect方法的detect_types参数，值有两个:
     • 通过declared type(隐式方法)
     • 通过列名(显示方法)
       

       detect_typeimport sqlite3class Point:    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)).encode(‘ascii‘)def convert_point(s):    x, y = list(map(float, s.split(b";")))    return Point(x, y)# Register the adaptersqlite3.register_adapter(Point, adapt_point)# Register the convertersqlite3.register_converter("point", convert_point)p = Point(4.0, -3.2)########################## 1) Using declared typescon = 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) Using column namescon = 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()

5. class sqlite3.Row:表示一行数据，可以使用列名或者索引下标访问某列的值
   
   

python常用库