Python之sqlite3模块的使用
1、sqlite3数据库的创建
#!/usr/bin/env python3 # encoding: utf-8 import sqlite3 import os db_filename = 'todo.db' db_id_new = not os.path.exists(db_filename) conn = sqlite3.connect(db_filename) if db_id_new: print('需要创建数据库') else: print('数据库已经存在，') conn.close()
运行效果
[root@python-mysql mnt]# python3 sqlite3_createdb.py 需要创建数据库 [root@python-mysql mnt]# ll -rw-r--r-- 1 root root 280 Jan 12 16:53 sqlite3_createdb.py -rw-r--r-- 1 root root 0 Jan 12 16:53 todo.db [root@python-mysql mnt]# python3 sqlite3_createdb.py 数据库已经存在，
2、sqlite3数据库的创建完成后，初始化表结构
-- 创建项目表 create table project ( name text primary key, description text, deadline date ); -- 创建任务表 -- auto-generated definition create table task ( id integer not null primary key autoincrement, priority integer default 1, details text, status text, deadline date, completed_on date, project text not null references project );
#!/usr/bin/env python3 # encoding: utf-8 import sqlite3 import os db_filename = 'todo.db' schema_filename = 'todo_schme.sql' db_id_new = not os.path.exists(db_filename) with sqlite3.connect(db_filename) as conn: if db_id_new: print('初始化表结构...') with open(schema_filename, 'rt',encoding='utf-8') as rf: schema = rf.read() conn.executescript(schema) print('往初始化表插入数据') conn.executescript(""" insert into project (name, description, deadline) values ('pymotw', 'Python Module of the Week', '2016-11-01'); insert into task (details, status, deadline, project) values ('write about select', 'done', '2016-04-25', 'pymotw'); insert into task (details, status, deadline, project) values ('write about random', 'waiting', '2016-08-22', 'pymotw'); insert into task (details, status, deadline, project) values ('write about sqlite3', 'active', '2017-07-31', 'pymotw'); """) else: print('数据库已经存在，')
运行效果
[root@python-mysql mnt]# python3 sqlite3_create_schema.py 初始化表结构... 往初始化表插入数据 [root@python-mysql mnt]# ll -rw-r--r-- 1 root root 1146 Jan 12 17:28 sqlite3_create_schema.py -rw-r--r-- 1 root root 5120 Jan 12 17:28 todo.db -rw-r--r-- 1 root root 473 Jan 12 17:24 todo_schme.sql
3、查询单表的数据
#!/usr/bin/env python3 # encoding: utf-8 import sqlite3 db_filename = 'todo.db' with sqlite3.connect(db_filename) as conn: # 获取连接 cursor = conn.cursor() cursor.execute('select * from task') for row in cursor.fetchall(): print(row)
运行效果
(1, 1, 'write about select', 'done', '2016-04-25', None, 'pymotw') (2, 1, 'write about random', 'waiting', '2016-08-22', None, 'pymotw') (3, 1, 'write about sqlite3', 'active', '2017-07-31', None, 'pymotw')
4、查询单表的数据指定返回最大的行数
认识两个方法： fetchone() #返回一行数据 fetchmany(Max_Num) #返回指定的的最大行数
#!/usr/bin/env python3 # encoding: utf-8 import sqlite3 db_filename = 'todo.db' with sqlite3.connect(db_filename) as conn: # 获取连接 cursor = conn.cursor() cursor.execute('select * from task') one_row = cursor.fetchone() print(one_row) print('*' * 40) for row in cursor.fetchmany(2): print(row)
运行效果
(1, 1, 'write about select', 'done', '2016-04-25', None, 'pymotw') **************************************** (2, 1, 'write about random', 'waiting', '2016-08-22', None, 'pymotw') (3, 1, 'write about sqlite3', 'active', '2017-07-31', None, 'pymotw')
5、查询每一个列的元数据
#!/usr/bin/env python3 # encoding: utf-8 import sqlite3 db_filename = 'todo.db' with sqlite3.connect(db_filename) as conn: # 获取连接 cursor = conn.cursor() cursor.execute('select * from task') for d in cursor.description: print(d)
运行效果
('id', None, None, None, None, None, None) ('priority', None, None, None, None, None, None) ('details', None, None, None, None, None, None) ('status', None, None, None, None, None, None) ('deadline', None, None, None, None, None, None) ('completed_on', None, None, None, None, None, None) ('project', None, None, None, None, None, None) #注意：由于sqlite3对插入到数据库的数据没有类型或大小约束，所以只填入列名值
6 、设置行对象，通过索引或名字获取行的值
#!/usr/bin/env python3 # encoding: utf-8 import sqlite3 db_filename = 'todo.db' with sqlite3.connect(db_filename) as conn: # 获取连接 conn.row_factory = sqlite3.Row # 设置行的工厂 cursor = conn.cursor() cursor.execute(""" select name, description, deadline from project where name = 'pymotw' """) name, description, deadline = cursor.fetchone() print('获取一行数据，通过索引： {} {} {}'.format(description, name, deadline)) cursor.execute(""" select id, priority, status, deadline, details from task where project = 'pymotw' order by deadline """) print('\n最多获取5行的数据，通过名字获取值：') for row in cursor.fetchmany(5): print('{:2d} [{:d}] {:<25} [{:<8}] ({})'.format( row['id'], row['priority'], row['details'], row['status'], row['deadline'], ))
运行效果
获取一行数据，通过索引： Python Module of the Week pymotw 2016-11-01 最多获取5行的数据，通过名字获取值： 1 [1] write about select [done ] (2016-04-25) 2 [1] write about random [waiting ] (2016-08-22) 3 [1] write about sqlite3 [active ] (2017-07-31)
7、查询条件使用位置参数，防止SQL注入的示例
#!/usr/bin/env python3 # encoding: utf-8 import sqlite3 db_filename = 'todo.db' with sqlite3.connect(db_filename) as conn: # 获取连接 cursor = conn.cursor() query = """ select id, priority, details, status, deadline from task where project = ? """ cursor.execute(query, ('pymotw',)) for row in cursor.fetchall(): print(row)
运行效果
(1, 1, 'write about select', 'done', '2016-04-25') (2, 1, 'write about random', 'waiting', '2016-08-22') (3, 1, 'write about sqlite3', 'active', '2017-07-31')
8、查询条件使用命名参数，防止SQL注入的示例
#!/usr/bin/env python3 # encoding: utf-8 import sqlite3 db_filename = 'todo.db' with sqlite3.connect(db_filename) as conn: # 获取连接 cursor = conn.cursor() query = """ select id, priority, details, status, deadline from task where project = :project_name """ cursor.execute(query, ({'project_name': 'pymotw'})) for row in cursor.fetchall(): print(row)
运行效果
(1, 1, 'write about select', 'done', '2016-04-25') (2, 1, 'write about random', 'waiting', '2016-08-22') (3, 1, 'write about sqlite3', 'active', '2017-07-31')
9、数据库数据的更新
#!/usr/bin/env python3 # encoding: utf-8 import sqlite3 db_filename = 'todo.db' with sqlite3.connect(db_filename) as conn: # 获取连接 cursor = conn.cursor() query = """ update task set status=:status where id = :id """ cursor.execute(query, ({'status': 'active', 'id': 2}))
10、批量插入数据
csv表格数据如下：
#!/usr/bin/env python3 # encoding: utf-8 import sqlite3 import csv db_filename = 'todo.db' csv_data = 'data.csv' insert_sql = """ insert into task (details, priority, status, deadline, project) values (:details, :priority, 'active', :deadline, :project) """ with open(csv_data, 'rt') as csv_file: csv_reader = csv.DictReader(csv_file) # 返回字典类型 with sqlite3.connect(db_filename) as conn: cursor = conn.cursor() cursor.executemany(insert_sql, csv_reader) # 批量插入数据
运行效果
11、查看返回结果数据的类型
#!/usr/bin/env python3 # encoding: utf-8 import sqlite3 db_filename = 'todo.db' sql = "select id, details, deadline from task" def show_deadline(conn): conn.row_factory = sqlite3.Row cursor = conn.cursor() cursor.execute(sql) row = cursor.fetchone() for col in ['id', 'details', 'deadline']: print(col, '*' * 3, row[col], type(row[col])) with sqlite3.connect(db_filename) as conn: # 没有设置sqlite3.PARSE_DECLTYPES，默认会把date类型当成str show_deadline(conn) with sqlite3.connect(db_filename, detect_types=sqlite3.PARSE_DECLTYPES) as conn: # 设置sqlite3.PARSE_DECLTYPES，会把str类型转date类型 show_deadline(conn)
运行效果
id *** 1 <class 'int'> details *** write about select <class 'str'> deadline *** 2016-04-25 <class 'str'> id *** 1 <class 'int'> details *** write about select <class 'str'> deadline *** 2016-04-25 <class 'datetime.date'>
12、自定义数据库的类型
import pickle import sqlite3 db_filename = 'todo.db' def adapter_func(obj): """序列化""" print('适配器函数 {}'.format(obj)) return pickle.dumps(obj) def converter_func(data): """反序列化""" print('转换函数 {}'.format(data)) return pickle.loads(data) class MyObj(object): def __init__(self, arg): self.arg = arg def __str__(self): return 'MyObj({})'.format(self.arg) # 注册操作类型的函数 sqlite3.register_adapter(MyObj, adapter_func) sqlite3.register_converter('MyObj', converter_func) to_save = [ (MyObj('这个是一个值的保存'),), (MyObj(42),), ] with sqlite3.connect(db_filename, detect_types=sqlite3.PARSE_DECLTYPES) as conn: # 如果表不存在，则创建一张表 conn.execute(""" create table if not exists obj( id integer primary key autoincrement not null, data MyObj ) """) cursor = conn.cursor() # 批量插入数据 cursor.executemany("insert into obj(data) values (?)", to_save) # 查询数据 cursor.execute('select id,data from obj') for obj_id, obj in cursor.fetchall(): print('数据: {} {}'.format(obj_id, obj)) print('数据的类型:', type(obj))
运行效果
适配器函数 MyObj(这个是一个值的保存) 适配器函数 MyObj(42) 转换函数 b'\x80\x03c__main__\nMyObj\nq\x00)\x81q\x01}q\x02X\x03\x00\x00\x00argq\x03X\x1b\x00\x00\x00\xe8\xbf\x99\xe4\xb8\xaa\xe6\x98\xaf\xe4\xb8\x80\xe4\xb8\xaa\xe5\x80\xbc\xe7\x9a\x84\xe4\xbf\x9d\xe5\xad\x98q\x04sb.' 转换函数 b'\x80\x03c__main__\nMyObj\nq\x00)\x81q\x01}q\x02X\x03\x00\x00\x00argq\x03K*sb.' 数据: 1 MyObj(这个是一个值的保存) 数据的类型: <class '__main__.MyObj'> 数据: 2 MyObj(42) 数据的类型: <class '__main__.MyObj'>
13、自定义数据库列的类型
import pickle import sqlite3 db_filename = 'todo.db' def adapter_func(obj): """序列化""" print('适配器函数 {}'.format(obj)) return pickle.dumps(obj) def converter_func(data): """反序列化""" print('转换函数 {}'.format(data)) return pickle.loads(data) class MyObj(object): def __init__(self, arg): self.arg = arg def __str__(self): return 'MyObj({})'.format(self.arg) # 注册操作类型的函数 sqlite3.register_adapter(MyObj, adapter_func) sqlite3.register_converter('MyObj', converter_func) to_save = [ (MyObj('这个是一个值的保存'),), (MyObj(42),), ] with sqlite3.connect(db_filename, detect_types=sqlite3.PARSE_COLNAMES) as conn: # 如果表不存在，则创建一张表 conn.execute(""" create table if not exists obj2( id integer primary key autoincrement not null, data text ) """) cursor = conn.cursor() # 批量插入数据 cursor.executemany("insert into obj2(data) values (?)", to_save) # 查询数据 cursor.execute('select id,data as "pickle [MyObj]" from obj2') for obj_id, obj in cursor.fetchall(): print('数据: {} {}'.format(obj_id, obj)) print('数据的类型:', type(obj))
运行效果
适配器函数 MyObj(这个是一个值的保存) 适配器函数 MyObj(42) 转换函数 b'\x80\x03c__main__\nMyObj\nq\x00)\x81q\x01}q\x02X\x03\x00\x00\x00argq\x03X\x1b\x00\x00\x00\xe8\xbf\x99\xe4\xb8\xaa\xe6\x98\xaf\xe4\xb8\x80\xe4\xb8\xaa\xe5\x80\xbc\xe7\x9a\x84\xe4\xbf\x9d\xe5\xad\x98q\x04sb.' 转换函数 b'\x80\x03c__main__\nMyObj\nq\x00)\x81q\x01}q\x02X\x03\x00\x00\x00argq\x03K*sb.' 数据: 1 MyObj(这个是一个值的保存) 数据的类型: <class '__main__.MyObj'> 数据: 2 MyObj(42) 数据的类型: <class '__main__.MyObj'>
14、sqlite事务之commit()
import sqlite3 db_filename = 'todo.db' def create_table(conn): conn.execute(""" create table project( name varchar (32), description varchar (255) ); """) def show_projects(conn): cursor = conn.cursor() cursor.execute('select name,description from project'); for name, desc in cursor.fetchall(): print(name, desc) with sqlite3.connect(db_filename) as conn1: create_table(conn1) print('改变之前') show_projects(conn1) cursor1 = conn1.cursor() cursor1.execute(""" insert into project (name,description) values ('cyc','yes desc'); """) show_projects(conn1) # 还没有提交数据，创建连接查看数据 with sqlite3.connect(db_filename) as conn2: show_projects(conn2) conn1.commit() # 提交数据后，创建连接查看数据 with sqlite3.connect(db_filename) as conn3: show_projects(conn3)
运行效果
改变之前
cyc yes desc
提交数据后
cyc yes desc
15、sqlite事务之rollback()
import sqlite3 db_filename = 'todo.db' def create_table(conn): conn.execute(""" create table project( name varchar (32), description varchar (255) ); """) def show_projects(conn): cursor = conn.cursor() cursor.execute('select name,description from project'); for name, desc in cursor.fetchall(): print(name, desc) with sqlite3.connect(db_filename) as conn: print('改变之前') show_projects(conn) try: cursor = conn.cursor() cursor.execute(""" delete from project where name='cyc'; """) print('删除数据之后') show_projects(conn) raise RuntimeError("提交数据运行时的异常") except Exception as err: print('Error:', err) conn.rollback() else: conn.commit() print('回滚后的数据') show_projects(conn)
运行效果
改变之前
cyc yes desc
删除数据之后
Error: 提交数据运行时的异常
回滚后的数据
cyc yes desc
16、sqlite事务隔离级别的示例
import sqlite3 import threading import logging import sys import time logging.basicConfig( level=logging.DEBUG, format='%(asctime)s (%(threadName)s -10s) %(message)s' ) # 数据库文件名 db_filename = 'todo.db' # 从命令行中，输入事务的隔离级别 isolation_level = sys.argv[1] def create_test_table(): with sqlite3.connect(database=db_filename) as conn: conn.execute(""" create table task( priority int ) """) def writer(): with sqlite3.connect(database=db_filename, isolation_level=isolation_level) as conn: cursor = conn.cursor() cursor.execute(""" update task set priority=priority+1; """) logging.debug('writer 同步等待') ready.wait() logging.debug('writer 睡眠1秒') time.sleep(1) conn.commit() logging.debug('writer 提交数据') def reader(): with sqlite3.connect(database=db_filename, isolation_level=isolation_level) as conn: cursor = conn.cursor() logging.debug('reader 同步等待') ready.wait() logging.debug('reader 退出等待') cursor.execute(""" select * from task; """) logging.debug('reader 查询task表的所有数据') all_data = cursor.fetchall() print(all_data) logging.debug('所有的数据 ') if __name__ == '__main__': # 创建测试表 # create_test_table() ready = threading.Event() threads = [ threading.Thread(target=reader, name='Reader 1'), threading.Thread(target=reader, name='Reader 2'), threading.Thread(target=writer, name='writer 1'), threading.Thread(target=writer, name='writer 2'), ] # 列表推导式，启动线程 [task.start() for task in threads] # 睡眠1秒 time.sleep(1) logging.debug('设置就绪') ready.set() # 列表推导式，等待所有线程运行完成 [task.join() for task in threads]
运行效果
延时 DEFERRED，会锁定数据库，在修改数据的时候，开启锁定一次，我们可以发现数据更新，但获取到的数据不是最新的
python sqlite3_isolation_levels.py DEFERRED 2020-04-08 08:20:16,966 (Reader 2 -10s) reader 同步等待 2020-04-08 08:20:16,966 (Reader 1 -10s) reader 同步等待 2020-04-08 08:20:16,967 (writer 2 -10s) writer 同步等待 [(5,)] 2020-04-08 08:20:17,965 (MainThread -10s) 设置就绪 2020-04-08 08:20:17,966 (Reader 2 -10s) reader 退出等待 2020-04-08 08:20:17,966 (writer 2 -10s) writer 睡眠1秒 2020-04-08 08:20:17,967 (Reader 1 -10s) reader 退出等待 2020-04-08 08:20:17,968 (Reader 2 -10s) reader 查询task表的所有数据 2020-04-08 08:20:17,969 (Reader 2 -10s) 所有的数据 2020-04-08 08:20:17,969 (Reader 1 -10s) reader 查询task表的所有数据 2020-04-08 08:20:17,970 (Reader 1 -10s) 所有的数据 [(5,)] 2020-04-08 08:20:19,132 (writer 2 -10s) writer 提交数据 2020-04-08 08:20:19,139 (writer 1 -10s) writer 同步等待 2020-04-08 08:20:19,139 (writer 1 -10s) writer 睡眠1秒 2020-04-08 08:20:20,220 (writer 1 -10s) writer 提交数据
立即 IMMEDIATE 修改数据时，开始锁定数据库，在事务提交之前避免，其他游标修改数据库。如果数据库有复杂的写操作，
而且阅读器多于书写器，那么这种模式就很适合，因为在事务进行时不会阻塞阅读器。
python sqlite3_isolation_levels.py IMMEDIATE 2020-04-08 08:25:57,302 (Reader 1 -10s) reader 同步等待 2020-04-08 08:25:57,302 (Reader 2 -10s) reader 同步等待 2020-04-08 08:25:57,304 (writer 2 -10s) writer 同步等待 2020-04-08 08:25:58,303 (MainThread -10s) 设置就绪 2020-04-08 08:25:58,303 (writer 2 -10s) writer 睡眠1秒 2020-04-08 08:25:58,304 (Reader 1 -10s) reader 退出等待 2020-04-08 08:25:58,305 (Reader 2 -10s) reader 退出等待 2020-04-08 08:25:58,308 (Reader 2 -10s) reader 查询task表的所有数据 2020-04-08 08:25:58,310 (Reader 1 -10s) reader 查询task表的所有数据 2020-04-08 08:25:58,311 (Reader 1 -10s) 所有的数据 [(9,)] [(9,)] 2020-04-08 08:25:58,312 (Reader 2 -10s) 所有的数据 2020-04-08 08:25:59,472 (writer 2 -10s) writer 提交数据 2020-04-08 08:25:59,476 (writer 1 -10s) writer 同步等待 2020-04-08 08:25:59,476 (writer 1 -10s) writer 睡眠1秒 2020-04-08 08:26:00,658 (writer 1 -10s) writer 提交数据
互斥 EXCLUSIVE 模式会对所有阅读器和书写器锁定数据库。如果数据库性能很重要则应该限制使用这种模式,
python sqlite3_isolation_levels.py EXCLUSIVE 2020-04-08 09:49:30,023 (Reader 2 -10s) reader 同步等待 2020-04-08 09:49:30,023 (Reader 1 -10s) reader 同步等待 2020-04-08 09:49:30,026 (writer 2 -10s) writer 同步等待 2020-04-08 09:49:31,023 (MainThread -10s) 设置就绪 2020-04-08 09:49:31,023 (Reader 1 -10s) reader 退出等待 2020-04-08 09:49:31,024 (writer 2 -10s) writer 睡眠1秒 2020-04-08 09:49:31,024 (Reader 2 -10s) reader 退出等待 2020-04-08 09:49:32,199 (writer 2 -10s) writer 提交数据 [(12,)] [(12,)] 2020-04-08 09:49:32,246 (Reader 2 -10s) reader 查询task表的所有数据 2020-04-08 09:49:32,246 (Reader 2 -10s) 所有的数据 2020-04-08 09:49:32,248 (Reader 1 -10s) reader 查询task表的所有数据 2020-04-08 09:49:32,248 (Reader 1 -10s) 所有的数据 2020-04-08 09:49:32,285 (writer 1 -10s) writer 同步等待 2020-04-08 09:49:32,285 (writer 1 -10s) writer 睡眠1秒 2020-04-08 09:49:33,467 (writer 1 -10s) writer 提交数据
自动提交 isolation_level=None ，这会启用自动提交模式。启用自动提交时，每个execute()调用会在语句完成时立即提交。
自动提交模式很适合持续时间短的事务，如果向一个表插入少量数据。数据库锁定时间尽可能短，所以线程间竞争的可能性更小。
import logging import sqlite3 import sys import threading import time logging.basicConfig( level=logging.DEBUG, format='%(asctime)s (%(threadName)-10s) %(message)s', ) db_filename = 'todo.db' isolation_level = None # autocommit mode def writer(): with sqlite3.connect( db_filename, isolation_level=isolation_level) as conn: cursor = conn.cursor() cursor.execute('update task set priority = priority + 1') logging.debug('waiting to synchronize') ready.wait() # synchronize threads logging.debug('PAUSING') time.sleep(1) def reader(): with sqlite3.connect( db_filename, isolation_level=isolation_level) as conn: cursor = conn.cursor() logging.debug('waiting to synchronize') ready.wait() # synchronize threads logging.debug('wait over') cursor.execute('select * from task') logging.debug('SELECT EXECUTED') cursor.fetchall() logging.debug('results fetched') if __name__ == '__main__': ready = threading.Event() threads = [ threading.Thread(name='Reader 1', target=reader), threading.Thread(name='Reader 2', target=reader), threading.Thread(name='Writer 1', target=writer), threading.Thread(name='Writer 2', target=writer), ] [t.start() for t in threads] time.sleep(1) logging.debug('setting ready') ready.set() [t.join() for t in threads]
python sqlite3_autocommit.py 2020-04-08 12:36:12,983 (Reader 1 -10s) reader 同步等待 2020-04-08 12:36:12,983 (Reader 2 -10s) reader 同步等待 2020-04-08 12:36:13,076 (writer 1 -10s) writer 同步等待 2020-04-08 12:36:13,172 (writer 2 -10s) writer 同步等待 2020-04-08 12:36:13,985 (MainThread -10s) 设置就绪 [(15,)][(15,)] 2020-04-08 12:36:13,985 (writer 2 -10s) writer 睡眠1秒 2020-04-08 12:36:13,985 (writer 1 -10s) writer 睡眠1秒 2020-04-08 12:36:13,985 (Reader 1 -10s) reader 退出等待 2020-04-08 12:36:13,985 (Reader 2 -10s) reader 退出等待 2020-04-08 12:36:13,985 (Reader 1 -10s) reader 查询task表的所有数据 2020-04-08 12:36:13,985 (Reader 2 -10s) reader 查询task表的所有数据 2020-04-08 12:36:13,985 (Reader 2 -10s) 所有的数据 2020-04-08 12:36:13,985 (Reader 1 -10s) 所有的数据 2020-04-08 12:36:14,985 (writer 2 -10s) writer 提交数据 2020-04-08 12:36:14,986 (writer 1 -10s) writer 提交数据
17、内存中的数据库和导出数据库内容
import sqlite3 def create_table(): return """ create table task( name varchar (32), details varchar (255), status varchar (255), deadline varchar (255), project varchar (255) ) """ #:memory: 在内存中创建数据库 with sqlite3.connect(':memory:') as conn: conn.row_factory = sqlite3.Row conn.executescript(create_table()) print('初始化数据') conn.execute(""" insert into task (name, details, deadline) values ('pymotw', 'Python Module of the Week','2010-11-01') """) data = [ ('write about select', 'done', '2010-10-03', 'pymotw'), ('write about random', 'waiting', '2010-10-10', 'pymotw'), ('write about sqlite3', 'active', '2010-10-17', 'pymotw'), ] conn.executemany(""" insert into task (details, status, deadline, project) values (?, ?, ?, ?) """, data) print('导出数据:') for text in conn.iterdump(): print(text)
运行效果
初始化数据 导出数据: BEGIN TRANSACTION; CREATE TABLE task( name varchar (32), details varchar (255), status varchar (255), deadline varchar (255), project varchar (255) ); INSERT INTO "task" VALUES('pymotw','Python Module of the Week',NULL,'2010-11-01',NULL); INSERT INTO "task" VALUES(NULL,'write about select','done','2010-10-03','pymotw'); INSERT INTO "task" VALUES(NULL,'write about random','waiting','2010-10-10','pymotw'); INSERT INTO "task" VALUES(NULL,'write about sqlite3','active','2010-10-17','pymotw'); COMMIT;
18、利用Python自定义SQL的函数实现加密解密的示例
import sqlite3 import codecs db_filename = 'todo.db' def create_table(): return """ create table task( id integer , details varchar (255) ); """ def encrypt(s): print('Encrypt {!r}'.format(s)) return codecs.encode(s, 'rot-13') def decrypt(s): print('Decrypt {!r}'.format(s)) return codecs.encode(s, 'rot-13') with sqlite3.connect(db_filename) as conn: # 创建测试表 # conn.execute(create_table()) # 创建加解密函数 conn.create_function('encrypt', 1, encrypt) conn.create_function('decrypt', 1, decrypt) cursor = conn.cursor() print('原数据:') query = "select id,details from task" cursor.execute(query) for row in cursor.fetchall(): print(row) print('\n加密...') query = "update task set details = encrypt(details)" cursor.execute(query) for row in cursor.fetchall(): print(row) print('\n查询加密后的值:') query = "select id, details from task" cursor.execute(query) for row in cursor.fetchall(): print(row) print('\n解密查询...') query = "select id, decrypt(details) from task" cursor.execute(query) for row in cursor.fetchall(): print(row) print('\n解密...') query = "update task set details = decrypt(details)" cursor.execute(query)
运行效果
原数据: (1, '哈哈') (2, '可呵') 加密... Encrypt '哈哈' Encrypt '可呵' 查询加密后的值: (1, '哈哈') (2, '可呵') 解密查询... Decrypt '哈哈' Decrypt '可呵' (1, '哈哈') (2, '可呵') 解密... Decrypt '哈哈' Decrypt '可呵'
19、自定义正则函数，过滤查询的结果
import re import sqlite3 db_filename = 'todb.db' # def create_table(): # return """ # create table task( # id int, # details varchar (32) # ) # """ def regexp(pattern, input): """ 自定义正则匹配函数 :param pattern:正则表达式 :param input:从数据库查询出来的内容 :return: """ return bool(re.match(pattern, input)) with sqlite3.connect(db_filename) as conn: # 首次运行创建数据库和表结构 # conn.execute(create_table()) # 设置返回的结果，为行的对象 conn.row_factory = sqlite3.Row # 创建自定义函数 conn.create_function('regexp', 2, regexp) cursor = conn.cursor() # 定义正则表达式 pattern = '.*[hH]ello.*' # 执行查询语句,并且用自定义函数进行过滤 cursor.execute("select * from task where details regexp :pattern", {'pattern': pattern}) # 获取将查询得到的结果编历 for row in cursor.fetchall(): id, details = row print('{:d} {:<25}'.format(id, details))
数据库的数据源
id details 1,a Hello World c 2,d Hello World 3,e Hello World 4,asdf asdfads
运行结果
1 a Hello World c 2 d Hello World 3 e Hello World
20、Python自定义汇聚函数
import sqlite3 import collections db_filename = 'todb.db' class Mode(object): """ 该类的作用，就是统计数据库某列出现次数最高的相同内容 """ def __init__(self): self.counter = collections.Counter() def step(self, value): print('step({!r})'.format(value)) self.counter[value] += 1 def finalize(self): result, count = self.counter.most_common(1)[0] print('finalize() -> {!r} ({} times)'.format( result, count )) return result with sqlite3.connect(db_filename) as conn: # 自定义汇聚函数 conn.create_aggregate('mode', 1, Mode) cursor = conn.cursor() cursor.execute(""" select mode(details) from task """) row = cursor.fetchone() print('mode(id) is', row[0])
源数据
id details
1,a Hello World c 2,Hello World 3,Hello World 4,asdf asdfads
运行结果
step('a Hello World c') step('Hello World') step('Hello World') step('asdf asdfads') finalize() -> 'Hello World' (2 times) mode(id) is Hello World Process finished with exit code 0
21、sqlite3的线程和连接共享
import sqlite3 import threading db_filename = 'todb.db' isolation_level = None def reader(conn): print('Starting Thread') try: cursor = conn.cursor() cursor.execute('select * from task') cursor.fetchall() print('result fetched') except Exception as err: print('ERROR:', err) if __name__ == '__main__': with sqlite3.connect(db_filename, isolation_level=isolation_level) as conn: task = threading.Thread(name='Reader 1', target=reader, args=(conn,)) task.start() task.join()
运行结果
Starting Thread ERROR: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 9284 and this is thread id 15660.
总结：
1、sqlite3不支持线程共享连接。
2、每个线程必须创建自己的数据库连接。
21、sqlite3限制对数据的访问
import sqlite3 db_filename = 'todb.db' def authorizer_func(action, table, column, sql_location, ignore): """ 自定义认证的规则 :param action:操作数据的动作 :param table:表名 :param column:列名 :param sql_location: :param ignore: :return: """ print('action={},table={},column={},sql_location={},ignore={}'.format( action, table, column, sql_location, ignore )) response = sqlite3.SQLITE_OK if action == sqlite3.SQLITE_SELECT: print('sqlite3 查询权限') response = sqlite3.SQLITE_OK elif action == sqlite3.SQLITE_READ: print('sqlite3 列权限访问 {} {} 从 {}'.format( table, column, sql_location )) if column == 'id': print('id列的权限忽略') response = sqlite3.SQLITE_IGNORE elif column == 'details': print('details列的权限禁止') response = sqlite3.SQLITE_DENY return response with sqlite3.connect(db_filename) as conn: conn.row_factory = sqlite3.Row conn.set_authorizer(authorizer_func) cursor = conn.cursor() # 使用SQLITE_IGNORE忽略权限，正常访问,不过返回的数据是None cursor.execute(""" select id from task; """) for obj in cursor.fetchall(): print(list(obj)) print(20 * '#') # 使用SQLITE_DENY禁止权限 cursor.execute(""" select details from task; """) for obj in cursor.fetchall(): print(list(obj))
运行结果
Traceback (most recent call last): File "D:/Program Files/JetBrains/PyCharmData/PythonStudent/tornado_Test/test.py", line 53, in <module> """) sqlite3.DatabaseError: access to task.details is prohibited action=21,table=None,column=None,sql_location=None,ignore=None sqlite3 查询权限 action=20,table=task,column=id,sql_location=main,ignore=None sqlite3 列权限访问 task id 从 main id列的权限忽略 [None] [None] [None] [None] #################### action=21,table=None,column=None,sql_location=None,ignore=None sqlite3 查询权限 action=20,table=task,column=details,sql_location=main,ignore=None sqlite3 列权限访问 task details 从 main details列的权限禁止
