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()