文章目录
13.1Python数据库API
所有数据库的大多数基本功能都相同,因此从理论上说,对于使用其中一种数据库的程序,很容易对其进行修改以使用另一种数据库。问题是即便不同模块提供的功能大致相同,它们的接口(API)也是不同的。为解决Python数据库模块存在的这种问题,人们一致同意开发一个标准数据库API(DB API)
13.1.1全局变量
所有与DB API2.0兼容的数据库模块都必须包含三个全局变量,它们描述了模块的特征。通过检查这些变量,看看给定的模块是否是程序能够接受的。如果不是,就显示合适的错误消息并退出或者引发异常。
Python DB API的模块属性
变 量 名 | 描 述 |
---|---|
apilevel | 使用的Python DB API版本 |
threadsafety | 模块的线程安全程度如何 |
paramstyle | 在SQL查询中使用哪种参数风格 |
- API级别(apilevel):是一个字符串常量,指出了使用的API版本。DB API 2.0指出,这个变量的值为’1.0’或’2.0’。
- 线程安全程度(threadsafety):是一个0~3(含)的整数。
0表示线程不能共享模块,而3表示模块是绝对线程安全的。1表示线程可共享模块本身,但不能共享连接(参见13.1.3节),而2表示线程可共享模块和连接,但不能共享游标。 - 参数风格(paramstyle):表示当你执行多个类似的数据库查询时,如何在SQL查询中插入参数。
13.1.2异常
异 常 | 超 类 | 描 述 |
---|---|---|
tandardError | 所有异常的超类 | |
arning | StandardError | 发生非致命问题时引发 |
rror | StandardError | 所有错误条件的超类 |
nterfaceError | Error | 与接口(而不是数据库)相关的错误 |
atabaseError | Error | 与数据库相关的错误的超类 |
ataError | DatabaseError | 与数据相关的问题,如值不在合法的范围内 |
perationalError | DatabaseError | 数据库操作内部的错误 |
ntegrityError | DatabaseError | 关系完整性遭到破坏,如键未通过检查 |
nternalError | DatabaseError | 数据库内部的错误,如游标无效 |
rogrammingError | DatabaseError | 用户编程错误,如未找到数据库表 |
otSupportedError | DatabaseError | 请求不支持的功能如回滚 |
13.1.3连接和游标
import sqlite3
con=sqlite3.connect('D:\Data\database.db')#连接对象
cur=con.cursor()#游标对象
要使用底层的数据库系统,必须先连接到它,为此可使用名称贴切的函数connect。这个函数接受多个参数具体是哪些取决于要使用的数据库。
函数connect的常用参数
参数名 | 描述 | 是否可选 |
---|---|---|
dsn | 数据源名称,具体含义随数据库而异 | 否 |
user | 用户名 | 是 |
password | 用户密码 | 是 |
host | 主机名 | 是 |
database | 数据库名称 | 是 |
函数connect返回一个连接对象
连接对像的方法
方法名 | 描述 |
---|---|
close() | 关闭连接对象。之后,连接对象及其游标将不可用 |
commit() | 提交未提交的事务——如果支持的话;否则什么都不做 |
rollback() | 回滚未提交的事务(可能不可用) |
cursor() | 返回连接的游标对象 |
游标对象的方法
名称 | 描述 |
---|---|
callproc(name[, params]) | 使用指定的参数调用指定的数据库过程(可选) |
close() | 关闭游标。关闭后游标不可用 |
execute(oper[, params]) | 执行一个SQL操作——可能指定参数 |
executemany(oper, pseq) | 执行指定的SQL操作多次,每次都序列中的一组参数 |
fetchone() | 以序列的方式取回查询结果中的下一行;如果没有更多的行,就返回None |
fetchmany([size]) | 取回查询结果中的多行,其中参数size的值默认为arraysize |
fetchall() | 以序列的序列的方式取回余下的所有行 |
nextset() | 跳到下一个结果集,这个方法是可选的 |
setinputsizes(sizes) | 用于为参数预定义内存区域 |
setoutputsize(size[, col]) | 为取回大量数据而设置缓冲区长度 |
注意:取数的时候注意游标所在位置,例如fetchall是取游标之后的所有行。
游标对象的属性
名 称 | 描 述 |
---|---|
description | 由结果列描述组成的序列(只读) |
rowcount | 结果包含的行数(只读) |
arraysize | fetchmany返回的行数,默认为1 |
13.1.4类型
对于插入到某些类型的列中的值,底层SQL数据库可能要求它们满足一定的条件。为了能够与底层SQL数据库正确地互操作,DB API定义了一些构造函数和常量(单例),用于提供特殊的类型和值。
每个模块都必须实现表13-7所示的构造函数和特殊值。有些模块可能没有完全遵守这一点。
名称 | 描述 |
---|---|
Date(year, month, day) | 创建包含日期值的对象 |
Time(hour, minute, second) | 创建包含时间值的对象 |
Timestamp(y, mon, d, h, min, s) | 创建包含时间戳的对象 |
DateFromTicks(ticks) | 根据从新纪元开始过去的秒数创建包含日期值的对象 |
TimeFromTicks(ticks) | 根据从新纪元开始过去的秒数创建包含时间值的对象 |
imestampFromTicks(ticks) | 根据从新纪元开始过去的秒数创建包含时间戳的对象 |
Binary(string) | 创建包含二进制字符串值的对象 |
STRING | 描述基于字符串的列(如CHAR) |
BINARY | 描述二进制列(如LONG或RAW) |
NUMBER | 描述数字列 |
DATETIME | 描述日期/时间列 |
ROWID | 描述行ID列 |
13.2SQLite和PySQLite(以及pymysql)
SQLite小型数据库引擎。它不需要作为独立的服务器运行,且可直接使用本地文件,而不需要集中式数据库存储机制。
13.2.1起步
要使用Python标准库中的SOLite,可通过导入模块sqlite3来导入它。
import sqlite3 #导入模块
conn = sqlite3.connect('somedatabase.db') #建立连接
curs = conn.cursor() #获取游标
conn.commit() #提交修改(如果修改了数据)
conn.close() #关闭连接
为了防止没有及时关闭,可以使用上下文管理器
with sqlite3.connect('somedatabase.db') as conn:
curs = conn.cursor() #获取游标
conn.commit() #提交修改(如果修改了数据)
13.2.2数据库应用程序演示
目标:将文档转化成数据库
写一个数据转化文件
###importdata.py 将数据导入数据库
import sqlite3
conn=sqlite3.connect('st.db')#创建连接
curs=conn.cursor()#获得游标
#定义一个去除两边~的函数
def convert(value):
if value.startswith('~'):
return value.strip('~')
if not value:#如果value是空值,则返回0
value='0'
return float(value)
#执行游标——新建表
curs.execute('''
create table stu11(
id text primary key,
name text,
county test,
age float)
''')
query='insert into stu11 values (?,?,?,?)' #qmark风格使用问号来标记字段
for line in open('sql.txt'):# 逐行读取
fields=line.split('^') #每一行的文本用^分隔成列表
vals=[convert(f) for f in fields[:4]] #将该列表的每个元素执行convert函数后返回新的列表,取前4个元素([:4]中4不含)
curs.execute(query,vals)#执行游标 ——存储数据
print(vals)
conn.commit()#提交连接,针对改变数据库的删除、插入等操作,查询不需要提交
conn.close()#断开连接
----------------------------------------------------------------
['07276', 'Jack', 'China', '25']
['07274', 'May', 'Japan', '21']
这样新建的st.db存储有stu11这张表,表中有上述两行数据
写一个查询文件
#query.py
import sqlite3,sys
conn=sqlite3.connect('st.db')#创建连接
curs=conn.cursor()#获得游标
query='select * from stu11 where '+sys.argv[1]#sys.argv[1]外部给与的第一个参数
print(query)
curs.execute(query)
names=[f[0] for f in curs.description]#curs.description查看表结构 f[0]得到域的名字/列名
for row in curs.fetchall():#curs.fetchall()得到执行sql语句后返回的记录
for pair in zip(names,row):#zip缝合序列
print('{}:{}'.format(*pair))
print()
分解:
import sqlite3,sys
conn=sqlite3.connect('st.db')#创建连接
curs=conn.cursor()#获得游标
curs.execute('select * from stu11')
curs.description#查看表结构
---------------------------------------------------------------
(('id', None, None, None, None, None, None),
('name', None, None, None, None, None, None),
('county', None, None, None, None, None, None),
('age', None, None, None, None, None, None))
-------------------------------------------------------------------
curs.fetchall()#得到执行语句后返回的记录
[('07276', 'Jack', 'China', 25.0), ('07274', 'May', 'Japan', 21.0)]
#zip缝合函数
a=(('id', None, None, None, None, None, None),
('name', None, None, None, None, None, None),
('county', None, None, None, None, None, None),
('age', None, None, None, None, None, None))
aa=[f[0] for f in a]
b=[('07276', 'Jack', 'China', 25.0), ('07274', 'May', 'Japan', 21.0)]
for bb in b:
print(list(zip(aa,bb)))
---------------------------------------------------
[('id', '07276'), ('name', 'Jack'), ('county', 'China'), ('age', 25.0)]
[('id', '07274'), ('name', 'May'), ('county', 'Japan'), ('age', 21.0)]
延伸:使用pymysql连接MySQL数据库
pymysql是第三方库,Python2中则使用mysqldb。 mysql-connector 是 MySQL 官方提供的驱动器。
安装命令:pip install PyMySQL
import pymysql
con=pymysql.connect('localhost','root','123456','execise')#(主机名,用户名,密码,数据库名)与本地MYSQL数据库一致
cur=con.cursor()
q='select * from student;'
cur.execute(q)
cur.fetchall()
#con.close()