《Python程序设计基础》 课件第7章 数据库

第7章数据库数据库连接数据库的增删改操作数据库连接数据库操作的封装学习目标掌握掌握掌握掌握数据库的创建、查看、选择与删除的操作方法12掌握数据库的添加、查询、修改与删除、编程应用3掌握数据库操作的封装目录数据库查询7.27.1数据库连接7.3数据库的增、删、改操作数据库操作的封装7.4知识架构7.1数据库连接1Python数据库接口介绍2PyMySQL的安装3创建数据库连接4学生实践练习知识架构7.2数据库查询1SQL语句执行2查询方法介绍3学生实践练习7.1数据库连接1

Python数据库接口介绍

Python标准数据库接口为PythonDB-API，PythonDB-API为开发人员提供了数据库应用编程接口。

PythonDB-API使用流程如下：引入API模块→获取与数据库的连接→执行SQL语句和存储过程→关闭数据库连接。

在本章中，我们将以MySQL数据库的接口为例进行讲解。支持MySQL数据库操作的第三方库有很多，常见的有MySQLConnector（是MySQL官方的纯Python驱动）、MySQLdb（目前只支持到Python2.7版本）和PyMySQL（其使用方法和MySQLdb几乎相同，PyMySQL支持Python3.x，而MySQLdb不支持3.x版本）7.1数据库连接2

PyMySQL的安装

在PyCharm工具中选择“File”→“Settings”菜单，打开PyCharm设置界面。

单击右侧的“+”图标，进入添加第三方库的界面，在搜索框中输入“pymysql”搜索PyMySQL库7.1数据库连接2

PyMySQL的安装

在界面的左侧列表中选择PyMySQL，单击“InstallPackage”按钮安装PyMySQL库。

PyMySQL库也可以使用pip命令安装，使用pip命令安装需要打开cmd命令行工具（在Windows搜索框中搜索cmd），在命令行工具中输入“pipinstallpymysql”，系统会自动下载安装7.1数据库连接3

创建数据库连接

importpymysql

#导入pymysql模块conn=pymysql.connect(host='<数据库IP地址>',port=<端口号>,user='<用户名>'passwd='<密码>',db='<数据库名>',charset='utf8')使用PyMySQL创建数据库连接与JDBC类似，通过PyMySQL模块中的connect()函数获取数据库连接对象7.1数据库连接3创建数据库连接

示例7.1

假设，本地已有MySQL数据库test，用户名为root，密码为123456，则使用PyMySQL创建数据库连接importpymysql#导入PyMySQL库#创建数据库连接，注意密码参数passwd不要写成passwordconn=pymysql.connect(host='',port=3306,user='root',passwd='123456',

db='test',charset='utf8')print(conn)7.1数据库连接3创建数据库连接

！运行成功的结果如下示例7.1是数据库连接成功的示例，如果连接失败，会提示以下3种信息：（1）如果传入的端口不正确，在异常的堆栈信息的最后会提示“Can'tconnecttoMySQLserveron''([Errno10061])”。（2）如果传入的密码不正确，异常信息中将提示“Accessdeniedforuser'root'@'localhost'(usingpassword:YES)”。（3）传入的数据库名不正确，异常信息中将提示“Unknowndatabase'test'”。7.1数据库连接4学生实践练习

1．需求说明安装PyMySQL库，使用PyMySQL获取数据库连接对象。熟悉数据库端口错误、用户名或密码错误及数据库名错误这三种情况下的信息提示。2．实现思路（1）使用PyCharm工具安装PyMySQL库或使用pip命令进行安装。（2）使用PyMySQL模块中的connect()函数连接MySQL数据库。在函数中传入数据库连接的详细信息。（3）连接成功后，分别修改数据库端口、用户名或密码及数据库名，查询这三种情况的信息提示知识架构7.2数据库查询1SQL语句执行2查询方法介绍3学生实践练习7.2数据库查询1

SQL语句执行

（1）创建数据库连接，并返回一个数据库连接对象，代码如下：importpymysql#导入PyMySQL库#创建数据库连接，connect()函数返回数据连接对象conn=pymysql.connect(host='',

port=3306,user='root',passwd='123456',db='mydb',charset='utf8')（2）使用数据库连接对象中的cursor()函数获取游标对象，代码如下cursor=conn.cursor()7.2数据库查询1

SQL语句执行

（3）调用游标对象中的execute()函数执行SQL语句，该函数返回影响的行数。如查询某个表记录，则返回查询的记录数。如果为添加、删除和修改操作，则返回改变的记录数，代码如下：effectedRows=cursor.execute(<sql语句>)（4）提交数据库连接，如果不提交将无法保存新建或者修改的数据，代码如下：mit()（5）关闭游标，释放资源，代码如下：cursor.close()（6）关闭连接，释放资源，代码如下：conn.close()7.2数据库查询1

SQL语句执行

示例7.2在PyMySQL数据库中创建user用户表。importPyMySQL#创建数据库连接conn=pymysql.connect(host='',

port=3306,user='root',passwd='123456',db='mydb',

charset='utf8')cursor=conn.cursor()#获取游标对象#定义创建user表的SQL语句，使用三引号'''表示格式化字符串sql='''CREATETABLEuser(

useridint(11)NOTNULLauto_increment,

usernamevarchar(60)NOTNULL,

userpassvarchar(60)defaultNULL,

PRIMARYKEY(userid))'''rows=cursor.execute(sql)#执行SQL语句mit()#提交数据库连接，如果是增、删、改操作，必须提交print("user表创建成功")cursor.close()#关闭游标对象conn.close()#关闭数据库连接7.2数据库查询1

SQL语句执行

创建user用户表的运行结果如图7.2

数据库查询1

SQL语句执行

在PyMySQL数据库中的user用户表中添加3条数据cursor.execute("insertintouservalues(1,'zhangsan','zhangsan')")cursor.execute("insertintouservalues(2,'lisi','lisi')")cursor.execute("insertintouservalues(3,'wangwu','wangwu')")7.2数据库查询1

SQL语句执行

注意在提交执行操作mit()语句之前添加以上语句，否则添加数据不成功。添加成功后的user表数据如图：7.2数据库查询2

查询方法介绍

7.2.1小节中介绍了SQL语句的执行步骤，数据库的查询操作步骤与SQL语句的执行步骤基本相同，在使用execute()函数执行查询的SQL语句后，游标对象中提供了fetchone()函数、fetchmany(size)函数及fetchall()函数这三个函数用来获取剩余结果中的数据，使用代码如下：row_1=cursor.fetchone()#获取剩余结果的第一行数据row_2=cursor.fetchmany(3)#获取剩余结果前三行数据row_3=cursor.fetchall()#获取剩余结果所有数据7.2数据库查询2

查询方法介绍

示例7.3读取用户表user中的记录。importpymysql#创建数据库连接conn=pymysql.connect(host='',

port=3306,user='root',passwd='123456',db='mydb',

charset='utf8')cursor=conn.cursor()#获取游标对象#cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)#获取字典类型的数据rows=cursor.execute("select*fromuser")#执行SQL语句print("查询到%d条用户记录数"%(rows))results=cursor.fetchall()#获取所有的记录forrowIteminresults:#循环遍历每一行记录

print("用户编号：%d用户名：%s密码：%s"%(rowItem[0],rowItem[1],

rowItem[2]))mit()#提交数据库连接，如果是增、删、改操作，必须提交cursor.close()#关闭游标对象conn.close()#关闭数据库连接7.2数据库查询2查询方法介绍

读取用户表user中的记录，结果如图：7.2数据库查询3学生实践练习

1．需求说明（1）创建product表，建表的SQL语句如下：

CREATETABLEproduct(

productidint(11)NOTNULLauto_increment,productnamevarchar(60)NOTNULL,priceintdefault0,PRIMARYKEY(productid))（2）使用SQL语句插入三条测试数据，代码如下：insertintoproduct(productname,price)values('mi6',2399);insertintoproduct(productname,price)values('meizupro7',2999);insertintoproduct(productname,price)values('vivox9s',2499);（3）查询该表的所有记录。7.2数据库查询3学生实践练习

2．实现思路（1）定义一个函数getConn()获取数据库的连接对象。（2）定义executeSql()函数获取游标对象，通过游标对象的execute()函数分别执行建表、SQL插入及查询的SQL语句。如果是查询的SQL语句，则通过cursor.fetchall()语句获取所有的记录。知识架构7.3数据库的增、删、改操作1数据库增、删、改操作2事务机制3学生实践练习7.3数据库的增、删、改操作1

数据库增、删、改操作

！

数据库的增、删、改操作步骤与SQL语句执行步骤基本一致，不同之处在于execute()函数中执行的SQL语句不一致。

如果一次插入多条记录需要调用cursor对象的executemany()函数，代码如下：effect_row=cursor.executemany("insertintoproduct(productname,price)values(%s,%s)",[('mi6',2399),('meizupro7',2999),('vivox9s',2499)])在PyMySQL库中提供了对存储过程的调用，代码如下：cursor.callproc('p1',args=(1,22,3,4))#有参的存储过程调用cursor.execute("select@p1,@_p1_1,@_p1_2,@_p1_3")#获取执行完存储的参数，参数以@开头7.3数据库的增、删、改操作1

数据库增、删、改操作

【注意】有些黑客利用现有应用程序，将SQL命令注入到后台执行的SQL语句中，它可以通过在Web表单中输入SQL语句，得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行SQL语句，这种现象称为SQL的注入，如访问Default.aspx?jobid=1'or'1'='1地址时，传到后台通过字符串拼接进行查询时的SQL语句变成SELECT*FROMjobsWHEREjob_id='1'OR'1'='1'，导致查询出所有的记录。因此强烈建议使用PyMySQL提供的参数化语句，代码如下：row_count=cursor.execute("select*fromuserwhereusername=%sanduserpass=%s",(user,passwd))7.3数据库的增、删、改操作1

数据库增、删、改操作

示例7.3定义一个执行SQL语句的方法，分别执行添加多行记录、修改和删除操作importpymysqlimporttypesdefexecuteSQL(sql,params):

#创建数据库连接，注意密码参数passwd不要写成passwordconn=pymysql.connect(host='',

port=3306,user='root',passwd='123456',

db='mydb',charset='utf8')

cursor=conn.cursor()

effectedNum=0#影响的记录数

iftype(params)==list:#判断params是否为list列表类型

effectedNum=cursor.executemany(sql,params)#为list列表类型执行多条记录

else:

effectedNum=cursor.execute(sql,params)

mit()

cursor.close()

conn.close()

returneffectedNum7.3数据库的增、删、改操作1

数据库增、删、改操作

示例7.3定义一个执行SQL语句的方法，分别执行添加多行记录、修改和删除操作#添加多行记录num1=executeSQL("insertintoproduct(productname,price)values(%s,%s)",

[('mi6',2399),('meizupro7',2999),('vivox9s',2499)])num2=executeSQL("insertintoproduct(productname,price)values(%s,%s)",('minote2',

1799))num3=executeSQL("updateproductsetprice=%swhereproductname=%s",(1999,'mi6'))num4=executeSQL("deletefromproductwhereproductname=%s",('meizupro7'))print("四次操作影响的记录数分别为%d,%d,%d,%d"%(num1,num2,num3,num4))7.3数据库的增、删、改操作1

数据库增、删、改操作

添加多行记录、修改和删除操作的运行结果如图7.3数据库的增、删、改操作2

事务机制

原子性（Atomicity）：

一个事务是一个不可分割的工作单位，对于事务中包括的诸多操作，要么都做，要么都不做一致性（Consistency）：

事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的

事务处理可以确保除非事务性单元内的所有操作都能成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个不是全部成功就是全部失败的单元，可以简化错误恢复并使应用程序更加可靠。事务具有4个属性：原子性、一致性、隔离性和持久性。这4个属性通常被称为ACID特性7.3数据库的增、删、改操作2

事务机制

隔离性（Isolation）：

一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。持久性（Durability）：

持续性也称永久性（Permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。7.3数据库的增、删、改操作2

事务机制

示例7.5在示例7.4的基础上，设表中已有“zhangsan”和“lisi”两个账户，模拟“zhangsan”账户向“lisi”转账50。importpymysqldeftransfer():#转账的方法

#创建数据库连接

conn=pymysql.connect(host='',

port=3306,user='root',passwd='123456',

db='mydb',charset='utf8')

cursor=conn.cursor()

try:

balanceQuerysql="selectbalancefromaccountwhereaccount=%s"

cursor.execute(balanceQuerysql,("zhangsan"))

results=cursor.fetchone()

balance=0

iflen(results)>0:

balance=results[0]7.3数据库的增、删、改操作2

事务机制

ifbalance>50:

#zhangsan账户余额需要减去50

cursor.execute("updateaccountsetbalance=balance-50"

"whereaccount=%s",("zhangsan"))

#lisi账户余额增加50

cursor.execute("updateaccountsetbalance=balance+50"

"whereaccount=%s",("lisi"))

cursor.execute("select*fromaccount")#查询所有记录

results=cursor.fetchall()#获取所有的记录

print(results)

cmit()#提交事务

exceptExceptionase:

print(e)

conn.rollback()#回滚事务，之前的修改操作都不生效

finally:#无论是否产生异常都将关闭游标和数据库连接对象

cursor.close()

conn.close()if__name__=='__main__':#类似于java的main函数，也可以直接调用transfer()

transfer()7.3数据库的增、删、改操作2

事务机制

模拟“zhangsan”账户向“lisi”转账50，运行结果如图7.3数据库的增、删、改操作2

事务机制

模拟“zhangsan”账户向“lisi”转账50，运行结果如图7.3数据库的增、删、改操作2

事务机制

模拟“zhangsan”账户向“lisi”转账50，运行结果如图7.3数据库的增、删、改操作3

学生实践练习

根据提示操作实现数据库的增、删、改操作。使用7.2节实践练习的product表进操作，效果如图1．需求说明2．实现思路（1）定义一个函数connDb()，用来获取数据库连接及游标对象。（2）定义一个函数executeSQL()，执行增、删、改操作。（3）定义一个函数query()，执行查询操作。（4）在循环中提示选择操作类型，根据操作类型执行对应的操作知识架构7.4数据库操作的封装1封装介绍2对数据库操作进行封装3学生实践练习7.4数据库操作的封装1

封装介绍

对数据库操作进行封装可以重复使用封装的对象或方法，不需要每次都编写打开数据库连接、获取游标对象及关闭数据库连接等步骤的代码，从而可以大大减少代码量，提高代码的可维护性。

在数据库的封装过程中，我们将使用with简化连接过程。在读取文件时，文件对象中包含__enter__()函数和__exit__()函数，因此可以使用with语句。__enter__()函数在with触发时执行，__exit__()是退出时执行。使用with读取文件代码如下：withopen('filename','r')asf:

printf.readline()7.4数据库操作的封装1

封装介绍

示例7.6针对一些需要关逻辑的代码，构造成with的模式classecho:

def__enter__(self):

print('with语句被触发了')

def__exit__(self,*args):

print('with语句执行完退出')withecho()ase:

print('with要执行的内容')示例7.6针对一些需要关逻辑的代码，构造成with的模式7.4数据库操作的封装1

封装介绍

构造with模式的运行结果如图7.4数据库操作的封装1

封装介绍

示例7.7使用yield分别输出1～3的平方defcreateGenerator():

foriinrange(3):#循环遍历0～2三个数字

yieldi*igenerator=createGenerator()#方法返回一个生成器forjingenerator:

print(j)7.4数据库操作的封装1

封装介绍

使用yield分别输出1～3的平方，运行结果如图7.4数据库操作的封装1

封装介绍

contextlib是个比with简洁且提供上下文机制的模块，它是通过Generator装饰器实现的，不再是采用__enter__()和__exit__()的机制。contextlib中的contextmanager作为装饰器来提供一种针对函数级别的上下文管理机制7.4数据库操作的封装1

封装介绍

示例7.8使用with关键字和yield关键字封装contextlib提供的上下文机制fromcontextlibimportcontextmanager@contextmanagerdefmake_context():

print('方法开始')

try:

yield"生成器的内容"

exceptRuntimeErroraserr:

print('出现异常',err)

finally:

print('方法退出')withmake_context()asvalue:

print(value)7.4数据库操作的封装1

封装介绍

操作contextlib提供的上下文机制，运行结果如图7.4数据库操作的封装2

对数据库操作进行封装

在数据库操作过程中，每次都需要对数据库进行连接和关闭，比较麻烦，每进行一次数据库连接都会耗费系统的资源和时间。通过对数据库操作的封装尽量减少该问题的发生，有助于提高代码的可重复利用性，减少代码量，让数据库操作使用变得更加简单。

数据库的操作封装可以使用上一阶段介绍的contextlib模块提供的上下文机制解决。

使用contextlib模块对数据库的操作进行封装，为了在一次连接和关闭的操作中能执行多次数据库的增、删、改、查操作，对数据库的封装需要使用with语句获取游标对象。每执行一次with语句都将开启一个新的数据库连接。与之前的方式相比，在with语句中执行多次数据库操作都只需要创建一次数据库连接和关闭连接。7.4数据库操作的封装2

对数据库操作进行封装

示例7.9将数据库操作封装成DBHelper.py文件importpymysqlimportcontextlibclassDBHelper():

def__init__(self):

pass

#定义上下文管理器，连接后自动关闭连接

@contextlib.contextmanager

defcursor(self,host='',port=3306,user='root',

passwd='123456',db='mydb',charset='utf8'):

#创建数据库连接

conn=pymysql.connect(host=host,port=port,user=user,

passwd=passwd,db=db,charset=charset)

#以字典的形式获取数据

cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)

print("-------------------")

7.4数据库操作的封装2

对数据库操作进行封装

try:

yieldcursor

mit()#操作成功后提交事务

exceptExceptionase:

print(e)

conn.rollback()#出现异常进行回滚操作

finally:

cursor.close()

conn.close()

defqueryAll(self,cursor,sql,params=None):#查询所有的记录

cursor.execute(sql,params)

returncursor.fetchall()

#执行SQL语句，返回影响的记录数

defexecute(self,cursor,sql,params=None):

effectedNum=cursor.execute(sql,params)

returneffectedNumif__name__=="__main__":

#封装使用

dbHelper=DBHelper()