DB-API
Python提供了统一的DB-API接口来实现对数据库的访问,DB-API接口屏蔽了访问不同数据库的所有底层细节,Python应用程序调用DB-API接口可以实现对不同数据库的访问。
DB-API接口主要包含三个类模块,分别是Connection、Cursor、Exception。Connection类模块主要实现数据库的连接、数据库操作事务的提交和回滚,同时Connection会创建一个Cursor对象;Cursor类模块可以执行SQL命令,对SQL查询的结果集进行处理;Exception类模块为数据库操作过程中出现的异常和错误提供信息。
Connection类模块
连接数据库
Connection类模块提供了connect构造函数,用于创建一个Connection对象,该对象将连接正在运行的数据库。下表是调用connect方法时,需要传入的参数。
连接数据库样例代码如下:import pymysql as mysqlconn = mysql .connect(dsn='myhost:MYDB', user='guido', password='234$')
其中,conn用于接收connect方法返回的Connection对象,“myhost:MYDB”是数据库服务器的访问地址,“guido”是登录数据库服务器的用户名,“234$”是登录数据库服务器的用户密码。
关闭数据库
当需要关闭数据库连接时,可以调用Connection对象的close方法来关闭数据库连接,释放数据库连接资源。
关闭数据库连接样例代码如下:conn.close()
提交数据库事务
如果需要将挂起的数据库事务提交到数据库,可以调用Connection对象的commit方法来提交挂起的事务。
提交事务样例代码如下:conn. commit()
回滚当前事务
如果提交到数据库的事务在处理过程中发生了异常或出错,需要对已进行的数据库操作进行回滚处理,此时可以调用Connection对象的rollback方法来回滚当前提交的事务。
回滚事务样例代码如下:conn. rollback ()
创建Cursor对象
数据库连接成功后,需要获取Cursor对象,通过Cursor对象来执行SQL命令。调用Connection对象的cursor方法来创建一个Cursor对象。
创建Cursor对象样例代码如下:cursor = conn.cursor()
Cursor类模块
执行SQL命令
对数据库的访问主要是对数据库表记录的增删改查,增删改查都需要执行SQL命令。调用Cursor对象的execute方法可以执行SQL命令。下表是调用execute方法时,需要传入的参数。
执行SQL命令样例代码如下:# 获取Cursor对象cursor = conn..cursor()# SQL 查询sql = "select * from table_name where 1=1"try:# 执行SQL语句cursor.execute(sql)# 提交到数据库执行except:# 发生错误时回滚
Cursor对象的其它主要方法
下表列出了Cursor对象的其它主要方法。
Cursor对象的属性
下表列出了Cursor对象的主要属性。
Exception类模块
Exception类模块包括了数据库访问过程可能出现的异常和错误信息,其异常和错误信息见下表。
在程序中如何使用DB-API接口?
Python为不同数据库管理系统提供了对应的DB-API接口,本课程以MySQL数据库为例,其它数据库系统可使用对应的DB-API接口。
安装MySQL数据库接口pymysql
pymysql是Python提供的,用于连接MySQL数据库的DB-API接口。在程序使用pymysql之前,需要先安装pymysql。
Windows 10操作系统安装pymysql,启动Windows命令行窗口,在窗口输入下面的命令:
pip install pymysql
ubuntu操作系统安装pymysql,启动命令行终端,在终端窗口输入下面的命令:
在程序中使用pymysql
# 导入pymysql# 使用try语句捕获数据库错误# 连接数据库conn = mysql.connect(host='localhost',user='root',password='123456',database='shop')print(conn)except Exception as e:# 连接失败,输出错误信息print(e)
数据库表的增删改查
查询数据库表记录
Cursor对象执行SQL查询语句后,会以结果集的方式返回,查询到的数据库所有记录都会存储在结果集中。
案例1:查询数据库表所有记录
查询shop数据库shoper表的所有记录。# 定义数据库连接函数def connect_database():password='~123456q',return connreturn "error"# 程序入口if __name__ == '__main__':conn = connect_database()if conn == "error":print("数据库连接错误")else:# 获取Curso对象# 使用 execute 方法执行SQL查询语句cursor.execute("SELECT * FROM shoper")# 获取所有结果集result = cursor.fetchall()# 遍历结果集的每行记录for row in result:print(row)# 关闭数据库连接finally:cursor.close()
代码解读
代码使用import语句导入pymysql库。
代码定义了函数connect_database(),该函数调用pymysql库的connect函数连接本地MySQL数据库,并返回创建的Connection对象。函数内部使用了try语句来捕获连接数据库时可能会发生的异常,如果异常被捕获,函数输出异常信息,并返回error。
主体代码调用connect_database()函数连接数据库并创建Connection对象,调用Connection对象的cursor方法获取Cursor对象,调用Cursor对象execute方法执行SQL查询语句,execute方法会返回SQL查询语句执行后的结果集,返回的数据类型是元组,遍历元组可以输出结果集中的每条记录。
添加数据库表记录
添加数据库表记录是指在数据库表中添加新的记录,添加记录操作是通过SQL的INSERT语句完成的,Cursor对象的execute方法执行SQL的INSERT语句。所有SQL语句执行完成后,调用Cursor对象的commit方法向数据库提交事务,如果事务发生异常,需要在except语句块内,调用Cursor对象的rollback方法进行事务回滚。
案例1:添加记录到数据库表
import traceback# 定义添加的记录shopers = ({"id": "0010","userId": "001","name": "华东旗舰店","brief": "数字产品","log": "","shopMoney":0,"createDate":"2020-05-12"},{"id": "0011","name": "华南旗舰店","brief": "家电产品","createDate":"2020-05-12"} \# 定义记录添加函数def add(conn,cursor):# 添加记录SQL语句sql = "INSERT INTO shoper(id,userId,name,brief,log,shopMoney,createDate)\VALUES ('%s','%s','%s','%s','%s',%d,'%s')"# 执行 sql 插入语句for shop in shopers:cursor.execute(sql % (shop["id"],shop["userId"],shop["name"],shop["brief"],shop["log"],shop["shopMoney"],shop["createDate"]))# 对提交到数据库的事务进行回滚traceback.print_exc()add(conn,cursor)# rowcount是Cursor对象的属性# rowcount记录了最近操作或影响的记录行数print("%d记录添加成功" % (cursor.rowcount))
代码导入了traceback模块,用于输出数据库发生异常时,输出详情的错误信息。
在添加数据库表记录之前,使用元组来存储需要添加的记录数据,元组的每一个元素为一条记录,每条记录采用字典存储,字典的key是数据库表的字段名称,字典的value是字段的值。
代码定义了add函数,add函数完成数据库表记录的添加。在add函数内部使用了try语句,用于捕获添加数据库表记录可能发生的异常,并输出异常信息,异常信息帮助定位发生的异常的原因。
在add函数内部,定义了SQL查询语句,并使用for循环将待添加的所有记录数据绑定到SQL语句中,调用Cursor的execute方法执行SQL语句。
SQL语句执行后,并没有提交到数据库,还需要调用Cursor的commit方法来提交数据库事务,数据库对提交的事务进行统一处理。
更新数据库表记录
数据库表记录的更新操作是指在数据库表中更新原有记录,更新操作是通过SQL的UPDATE语句完成的,Cursor对象execute方法执行SQL更新语句。
案例1:数据表记录的更新sql = "update shoper set brief = '%s' where id='0009'" % ("电脑、服务器")print("%d记录更新成功" % (cursor.rowcount))
修改数据库表记录需要使用SQL的UPDATE语句,调用Cursor对象的execute方法执行SQL语句,SQL语句执行后,并没有提交到数据库,只是形成了一个数据库事务,还需要再调用Cursor对象的commit方法提交事务,数据库系统执行提交的事务,数据表记录才更新完成。
删除数据库表记录
数据库表记录的删除操作是指在数据库表中删除符合条件的记录,删除操作是通过SQL的DELETE语句完成的。
案例1:删除符合条件的数据库表记录
sql = "delete from shoper where id='0010'"print("%d记录删除成功" % (cursor.rowcount))
删除数据库表记录需要使用SQL的DELETE语句,DELETE语句一般都要添加WHERE子句,进行条件删选,如果没有WHERE子句,将删除数据库表的所有记录。