1.创建数据表
程序只要通过数据库连接对象打开游标，接下来就可以用游标来执行 DDL语句， DDL 语句负责创建表、修改表或删除表使用 connect()方法打开或创建一个数据库。例如:
import sqlite3
# 创建或打开数据库
conn = sqlite3.connect('first.db')
创建完数据库就该创建数据库表。具体操作如下:
import sqlite3
# 创建或打开数据库
conn = sqlite3.connect('first.db')
# 获取游标
c = conn.cursor()
# 执行DDL语句创建数据表
c.execute('''create table user_tb(
_id integer primary key autoincrement,
name text,
pass text,
gender text)''')
# 执行DLL语句创建数据表
c.execute('''create table order_tb(
_id integer primary key autoincrement,
item_name text,
item_price real,
item_number real,
user_id integer,
foreign key(user_id) references user_tb(_id) )''')
# 关闭游标
c.close()
# 关闭连接
conn.close()
SQLite 内部只支持 NULL ,INTEGER ,REAL( 浮点数)、 TEXT (文本)和BLOB(大二 进制对象)这5种数据类型，但实际上 SQLite 完全可以接受 varchar(n）、 char(n),decimal(p,s)等数据类型，只不过 SQLite 会在运算或保存时将它们转换为上面5种数据类型中相应的类型。
除此之外，SQLite还有 个特点,它允许把各种类型的数据保存到任何类型的 段中，可以把字符串类型的值存入 INTEGER类型的字段中， 也可以把数值类型的值存入布尔类型的字段种 ……但有一种情况例外,被定义为 “ INTEGER PRIMARY KEY ”的字段只能存储 64 位整数，当使用这种宇段保存除整数以外的其他类型数据时， SQLite 会产生错误。
在编写建表语句时可以省略各数据列后面的类型声明。例如:
create table user_tb
(
_id integer primary key autoincrement,
name ,
pass ,
gender
);
2.使用序列重复执行DML语句
使用游标的execute () 方法也可以执行 DML的insert,update,delete 语句。这样即可对数据库执行插入、修改和删除数据操作。
如下示范向数据库的两张表中分别插入一条数据:
import sqlite3
conn = sqlite3.connect('first.db')
c = conn.cursor()
# 插入表数据
c.execute('insert into user_tb values(null, ?, ?, ?)',
('孙悟空', '123456', 'male'))
c.execute('insert into order_tb values(null, ?, ?, ?, ?)',
('鼠标', '34.2', '3', 1))
# 提交事务
conn.commit()
c.close()
conn.close()
由于Python的SQLite 数据库 API默认是开启了事务的 ，因此必须调用conn.commit()来提交事务。否则,程序对数据库所做的修改（包括插入数据、修改数据、删除数据）不会生效。
程序使用 executemany()方法，则可以多次执行同 一条SQL 语句。例如:
import sqlite3
conn = sqlite3.connect('first.db')
c = conn.cursor()
c.executemany('insert into user_tb values(null, ?, ?, ?)',
(('sun', '123456', 'male'),
('bai', '123456', 'female'),
('zhu', '123456', 'male'),
('niu', '123456', 'male'),
('tang', '123456', 'male')))
conn.commit()
c.close()
conn.close()
调用executemany()方法执行 insert语句，但调用该方法的第二个参数是个元组，该元组的每个元素都代表执行该 insert语句。在执行 insert 语句时这些元素负责为语句中的“？”占位符赋值。
如下示范如何重复执行update语句:
import sqlite3
conn = sqlite3.connect('first.db')
c = conn.cursor()
c.executemany('update user_tb set name=? where _id=?',
(('小孙', 2),
('小白', 3),
('小猪', 4),
('小牛', 5),
('小唐', 6)))
# 通过rowcount获取被修改的记录
print('修改的记录条数为: ', c.rowcount)
conn.commit()
c.close()
conn.close()
4.执行查询
select 语句执行完成后可以得到查询结果，因此程序可通过游标的 fetchone ()、 fetchmany(n),fetchall()来获取查询结果。fetchone()用于获取一条记录, fetchmany(n)用于获取n条记录， fetchall() 用于获取全部记录。例如:
import sqlite3
conn = sqlite3.connect('first.db')
c = conn.cursor()
c.execute('select * from user_tb where _id > ?', (2, ))
# 此处的crowcount会返回-1
# 根据Python DB API规范的要求，rowcount属性为-1，以防在游标上未执行executeXX（）或接口无法确定最后一个操作的行数”。
# 这包括SELECT语句，因为在获取所有行之前，我们无法确定查询产生的行数。
# 这意味着所有 SELECT语句都不会有rowcount。
print('查询返回的记录数: ', c.rowcount)
# 通过游标的description属性获取列信息
for col in (c.description):
print(col[0], end='\t')
print('\n--------------------------------')
while True:
# 获取一条数据,每行数据都是一个元组
row = c.fetchone()
# 如果获取的row为None,则退出循环
if not row:
break
print(row)
print(row[1] + '-->' + row[2])
c.close()
conn.close()
import sqlite3
conn = sqlite3.connect('first.db')
c = conn.cursor()
c.execute('select * from user_tb where _id > ?', (2, ))
# 此处的crowcount会返回-1
# 根据Python DB API规范的要求，rowcount属性为-1，以防在游标上未执行executeXX（）或接口无法确定最后一个操作的行数”。
# 这包括SELECT语句，因为在获取所有行之前，我们无法确定查询产生的行数。
# 这意味着所有 SELECT语句都不会有rowcount。
print('查询返回的记录数: ', c.rowcount)
# 通过游标的description属性获取列信息
for col in (c.description):
print(col[0], end='\t')
print('\n--------------------------------')
while True:
# 获取三条数据,返回一个由三条数据组成的列表
rows = c.fetchmany(3)
# 如果获取的rows为None,则退出循环
if not rows:
break
# 再次使用循环遍历所获取的列表rows,读出数据
for r in rows:
print(r)
c.close()
conn.close()
在程序中应该尽量避免使用 fetchall()来获取查询返回的全部记录。这是因为程序可能并不清楚实际查询会返回多少条记录，如果查询返回的记录数量太多，那么调用 fetchall()次获取全部记录可能会导致内存开销过大，情况严重时可能导致系统崩溃。
5.事务控制
事务是由一步或几步数据库操作序列组成的逻辑执行单元，这一系列操作要么全部执行，要么全部放弃执行。程序和事务是两个不同的概念。一般而言，在一段程序中可能包含多个事务。
事务具备4种特性：原子性（Atomicity ）、一致性 (Consistency ）、 隔离性（ Isolation ）和持续性(Durability) 。这4种特性也简称为 ACID 。
- 原子性:事务是应用中最小的执行单位，事务是应用中不可再分的最小逻辑执行体。
- 一致性:事务执行的结果，必须使数据库从一种一致性状态变到另一种一致性状态 。当数据库只包含事务成功提交的结果时，数据库处于一致性状态。如果系统运行发生中断，某个事务尚未完成而被追中断，而该未完成的事务对数据库所做的修改己被写入数据库中，此时数据库就处于一种不正确的状态。因此,一致性是通过原子性来保证的。
- 隔离性:各个事务的执行互不干扰，任意一个事务的内部操作对其他井发的事务都是隔离。并发执行的事务之间不能看到对方的中间状态，它们不能互相影响。
- 持续性:事务一旦提交，对数据所做的任何改变都要记录到永久存储器中，通常就是保存到物理数据库中。
当事务所包含的任意一个数据库操作执行失败后，应该回滚(rollback )事务，使在该事务中所做的修改全部失效。事务回滚有两种方式：显式回滚和自动回滚。
- 显式回滚:调用数据库连接对象的 rollback
- 自动回滚:系统错误，或者强行退出
6.执行SQL脚本
SQLite 数据库模块游标对象还包含了一个 executescript()方法，它可以执行一段SQL 脚本。例如:
import sqlite3
conn = sqlite3.connect('first.db')
c = conn.cursor()
c.executescript('''
insert into user_tb values(null, '武松', '3444', 'male');
insert into user_tb values(null, '送姜', '44444', 'male');
create table item_tb(_id integer primary key autoincrement,
name,
price);
''')
conn.commit()
c.close()
conn.close()
上面程序调用 executescript()方法执行一段复杂的 SQL 脚本，在这段 SQL 脚本中包含了两条 insert 语句，该语句负责向user_tb 表中插入记录，还使用 create 语句创建了一个数据表。
为了简化编程， SQLite数据库模块还为数据库连接对象提供了如下3个方法:
- execute(sql[, parameters ］)：执行 SQL 语句。
- executemany(sql[, parameters]) ：根据序列重复执行 SQL 语句
- executescript(sql_script)：执行SQL脚本。
这3个方法与游标对象所包含的3个方法完全相同。在用法上与游标对象的 3个方法完全相同。
7.创建自定义函数
数据库连接对象还提供了 create_function( name, num _params，func)方法,该方法用于注册一个自定义函数。
- name 参数：指定注册的自定义函数的名字。
- num_params ：指定自定义函数所需参数的个数。
- func ：指定自定义函数对应的函数。
下面程序使用 create_function()方法为 SQL 语句注册一个自定义函数，然后程序就可以在 SQL语句中使用该自定义函数。
import sqlite3
# 定义一个普通函数,准备注册为SQL语句中的自定义函数
def reverse_ext(st):
# 对字符串反转,前后添加方括号
return '[' + st[::-1] + ']'
conn = sqlite3.connect('first.db')
# 调用create_function注册自定义函数enc
conn.create_function('enc', 1, reverse_ext)
c = conn.cursor()
c.execute('insert into user_tb values(null, ?, enc(?), ?)',
('送姜', '123456', 'female'))
conn.commit()
c.close()
conn.close()
8.创建聚集函数
- sum（）：统计总和。
- avg（）： 统计平均值。
- count（）：统计记录条数。
- max （）： 统计最大值。
- min （） ：统计最小值。
如果程序需要在 SQL 语句中使用与其他业务相关的聚集函数，则可使用数据库连接对象所提供的 create_aggregate(name, num_params, aggregate_class )方法，该方法用于注册一个自定义的聚集函数。该方法包含3个参数:
- name:指定自定义聚集函数的名字。
- num_params ：指定聚集函数所需的参数。
- aggregate_class ：指定聚集函数的实现类。该类必须实现 step(self ,params … . ）和 finalize(self) 方法，其中step ()方法对于查询所返回的每条记录各执行一次,finalize(self) 方法只在最后执行一次，该方法的返回值将作为聚集函数最后的返回值。
假设需要查询 user_tb 表中长度最短的密码，此时就需要用到自定义的聚集函数。下面程序使用create_aggregate()方法为 SQL 语句注册一个自定义的聚集函数，然后程序就可以在 SQL 语句中使用该自定义的聚集函数。
import sqlite3
# 定义一个普通函数,准备注册为SQL语句中的自定义聚集函数
class MinLen:
def __init__(self):
self.min_len = None
def step(self, value):
# 如果self.min_len还未赋值,则直接将当前value赋值给self.min_len
if self.min_len is None:
self.min_len = value
return
# 找到一个长度更短的value,用此value代替self.min_len
if len(self.min_len) > len(value):
self.min_len = value
def finalize(self):
return self.min_len
conn = sqlite3.connect('first.db')
# 调用create_aggregate注册自定义聚集函数min_len
conn.create_aggregate('min_len', 1, MinLen)
c = conn.cursor()
c.execute('select min_len(pass) from user_tb')
print(c.fetchone()[0])
conn.commit()
c.close()
conn.close()
9.创建比较函数
在标准的 SQL 语句中提供了order by 子句，该子句用于对查询结果进行排序，但这种排序只会按默认的排序规则进行，如果程序需要按业务相关规则进行排序，则需要创建自定义的比较函数。
如果程序需要在 SQL 语句中使用与业务相关的比较函数，则可使用数据库连接对象所提供的create_ collation(name, callable)方法，该方法用于注册一个自定义的比较函数。该方法包含两个参数:
- name:指定自定义比较函数的名字。
- callable :指定自定义比较函数对应的函数。 主函数包含两个参数，并对这两个参数进行大小比较，如果该方法返回正整数，系统认为第一个参数更大；如果返回负整数，系统认为第二个参数更大；如果返回0，系统认为两个参数相等。
假设要求对 use_tb 表中的 pass 进行排序，但考虑到 pass 前面采用了加密：第 一个字符和最一个字符都是方括号，因此程序会对 pass 列去掉前后两个方括号之后再进行排序。所以，程序需要自定义比较函数，该函数将会把字符串的第一个字符和最后一个字符去掉后比较大小。
import sqlite3
# 把字符串的第一个字符和最后一个字符去掉
def my_collate(st1, st2):
if st1[1: -1] == st2[1: -1]:
return 0
elif st1[1: -1] > st2[1: -1]:
return 1
else:
return -1
conn = sqlite3.connect('first.db')
# 调用create_collation注册自定义比较函数:sub_cmp
conn.create_collation('sub_cmp', my_collate)
c = conn.cursor()
c.execute('select * from user_tb order by pass collate sub_cmp')
# 用for-in循环遍历游标
for row in c:
print(row)
conn.commit()
c.close()
conn.close()
5748
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
