单元8 数据库访问与使用（电子教案）

单元8数据库访问与使用【任务8-1】创建、新增、查询、删除SQLite数据表【任务描述】（1）在PyCharm集成开发环境中创建项目Unit08。（2）在项目Unit08创建Python程序文件8-1.py。（3）自定义getInsertSql()函数用于返回SQL插入语句，自定义execInsert()函数用于向数据表中插入多条记录。（4）创建SQLite数据库文件“电子商务.db”（5）在SQLite数据库“电子商务.db”中如果“用户表”已存在，先删除该数据表，然后再重新创建数据表“用户表”，该数据表包括用户ID、用户编号、用户名称、密码4个字段，各字段的数据类型及长度见程序文件“8-1.py”中的代码。（6）向数据表“用户表”中插入5条记录。（7）查询用户名为“admin”，密码为“666”的记录。【任务实施】1．创建PyCharm项目Unit08成功启动PyCharm后，在指定位置“D:\PycharmProject\”，创建PyCharm项目Unit08。2．创建Python程序文件8-1.py电子活页8-2程序8-1.py代码在PyCharm电子活页8-2程序8-1.py代码同时PyCharm主窗口显示程序文件“8-1.py”的代码编辑窗口，在该程序文件的代码编辑窗口也自动添加了模板内容。3．编写Python程序代码在新建文件“8-1.py”的代码编辑窗口已有模板注释内容下面输入程序代码。importsqlite3fieldName=["用户ID","用户编号","用户名称","密码"]userData=[(1,"2020011","admin","666"),(2,"2020012","better","888"),(3,"2020013","向前","123456"),(4,"2020014","寻找","123"),(5,"2020015","向好汉","1456")]#SQL创建数据表语句,使用三个双引号,多行字符串定义可以让程序更加清晰，规整，可读性更好sqlCreateTable="""CreateTableifnotexists用户表(用户IDint(10)primarykey,用户编号varchar(10),用户名称varchar(30),密码varchar(20))"""defgetInsertSql():#SQL插入语句strInsert="""insertinto用户表(用户ID,用户编号,用户名称,密码)values(?,?,?,?)"""returnstrInsertdefexecInsert():i=0foriteminuserData:i+=1print("插入第",i,"条记录，数据为：",item[0],item[1],item[2],item[3])cursor.execute(getInsertSql(),(item[0],item[1],item[2],item[3]))try:#连接到SQLite数据库#数据库文件是电子商务.db，如果文件不存在，会自动在当前目录创建conn=sqlite3.connect("电子商务.db")#创建一个游标对象Cursorcursor=conn.cursor()cursor.execute("droptableifexists用户表")#执行一条SQL语句，创建userData表cursor.execute(sqlCreateTable)execInsert()mit()strSelect="select*from用户表where用户名称=?and密码=?"cursor.execute(strSelect,("admin","666"))cursor.execute("select*from用户表")rows=cursor.fetchall()print("数据表userData.db的记录数量：",len(rows))exceptExceptionaserror:print(error)finally:#关闭游标cursor.close()#关闭Connectionconn.close()单击工具栏中【保存】按钮，保存程序文件“8-1.py”。4．运行Python程序在PyCharm主窗口选择【Run】菜单，在弹出的下拉菜单中选择【Run】菜单项。在弹出的【Run】对话框中选择“8-1”选项，程序“8-1.py”开始运行。程序8-1.py的运行结果如下所示。插入第1条记录，数据为：12020011admin666插入第2条记录，数据为：22020012better888插入第3条记录，数据为：32020013向前123456插入第4条记录，数据为：42020014寻找123插入第5条记录，数据为：52020015向好汉1456数据表userData.db的记录数量：5【任务8-2】查询、更新、删除用户数据表中的数据【任务描述】（1）在项目Unit08创建Python程序文件8-2.py。（2）在程序文件8-2.py中自定义多个函数：initDb()函数用于创建数据库文件“电子商务.db”和一个游标对象，getSelectSql()函数用于返回SQL查询语句，getUserInfo()函数用于获取指定用户名称和密码的记录数，getUpdateSql()函数用于返回SQL修改数据语句，getDeleteSql()函数用于返回满足指定条件的SQL删除记录语句。（3）连接到SQLite数据库“电子商务.db”。（4）从数据表“用户表”中查询符合指定条件的记录。（5）将数据表“用户表”中用户ID为“1”对应记录的“密码”修改为“666”。（6）删除数据表“用户表”中用户名称为“向前”的记录。【任务实施】1．创建Python程序文件8-2.py在PyCharm项目“Unit08”中，新建Python程序文件“8-2.py”，同时PyCharm主窗口显示程序文件“8-2.py”的代码编辑窗口，在该程序文件的代码编辑窗口也自动添加了模板内容。2．编写Python程序代码在新建文件“8-2.py”的代码编辑窗口已有模板注释内容下面输入程序代码。importsqlite3definitDb():#数据库文件是电子商务.db，如果文件不存在，会自动在当前目录创建conn=sqlite3.connect("电子商务.db")#创建一个游标对象Cursorcursor=conn.cursor()returnconn,cursordefgetSelectSql(condition):#SQL查询语句strSelect="select*from用户表"+conditionreturnstrSelectdefgetUserInfo(name,password):#SQL查询语句strSelect="select用户名称,密码from用户表\where用户名称=?and密码=?"cursor.execute(strSelect,(name,password))rows=cursor.fetchall()n=len(rows)returnndefgetUpdateSql():#SQL修改数据语句strUpdate="update用户表set密码=?where用户ID=?"returnstrUpdatedefgetDeleteSql(condition):#SQL删除数据语句strDelete="deletefrom用户表"+conditionreturnstrDeletetry:#连接到SQLite数据库conn,cursor=initDb()#初始化pymysql#查看查询语句运行结果cursor.execute(getSelectSql("where用户名称==?"),("admin",))rows=cursor.fetchmany()print("用户表符合条件的查询结果记录数：",len(rows))num=getUserInfo("better","888")print("用户表符合条件的查询结果记录数：",num)#查看修改语句运行结果cursor.execute(getUpdateSql(),("666",1))cursor.execute(getSelectSql(""))rows=cursor.fetchmany(3)print("用户表的记录数：",len(rows))#查看删除语句运行结果cursor.execute(getDeleteSql("where用户名称=?"),("向前",))cursor.execute(getSelectSql(""))rows=cursor.fetchall()print("用户表的记录数：",len(rows))exceptExceptionaserror:print(error)finally:#关闭游标cursor.close()#关闭Connectionconn.close()单击工具栏中【保存】按钮，保存程序文件“8-2.py”。3．运行Python程序在PyCharm主窗口选择【Run】菜单，在弹出的下拉菜单中选择【Run】菜单项。在弹出的【Run】对话框中选择“8-2”选项，程序“8-2.py”开始运行。程序8-2.py的运行结果如下所示。用户表符合条件的查询结果记录数：1用户表符合条件的查询结果记录数：1用户表的记录数：3用户表的记录数：4【任务8-3】创建books数据表并显示数据表的结构信息【任务描述】（1）在项目Unit08创建Python程序文件8-3.py。（2）连接已存在的MySQL数据库“eCommerce”。（3）在MySQL数据库“eCommerce”中创建数据表“books”。（4）输出数据表“books”的结构信息。【任务实施】在PyCharm项目Unit08中创建Python程序文件8-3.py。在程序文件8-3.py中编写程序代码，实现所需功能。importpymysql#打开数据库连接conn=pymysql.connect("localhost","root","123456","eCommerce")#使用cursor()方法创建一个游标对象cursorcursor=conn.cursor()#使用execute()方法执行SQL，如果表存在则删除cursor.execute("DropTableIfExistsbooks")#如果数据表books已存在，先删除该数据表#使用预处理语句创建表sql="""CreateTableifnotexistsbooks(商品IDinteger(8)NotNullAuto_Increment,商品编号Varchar(12)NotNull,图书名称Varchar(50)NotNull,价格Decimal(8,2)DefaultNull,ISBNVarchar(13)NotNull,作者Varchar(30)Null,出版社Varchar(12)Null,出版日期Varchar(10)DefaultNull,版次Int(1),PrimaryKey(商品ID))Engine=MyIsamAuto_Increment=1DefaultCharset=utf8;"""#执行SQL语句try:cursor.execute(sql)exceptExceptionaserror:print(error)#使用execute()方法执行SQL查询cursor.execute("Select*frombooks")#使用fetchall()方法获取数据表的全部记录.data=cursor.fetchall()print("数据表中记录数:",len(data))#显示每列的详细信息desc=cursor.descriptionforitemindesc:print("数据的结构信息：:",item)#获取表头print("数据表的字段名:",",".join([item[0]foritemindesc]))#关闭数据库连接conn.close()程序8-3.py的运行结果如下所示。数据表中记录数:0数据的结构信息：:('商品ID',3,None,8,8,0,False)数据的结构信息：:('商品编号',253,None,48,48,0,False)数据的结构信息：:('图书名称',253,None,200,200,0,False)数据的结构信息：:('价格',246,None,10,10,2,True)数据的结构信息：:('ISBN',253,None,52,52,0,False)数据的结构信息：:('作者',253,None,120,120,0,True)数据的结构信息：:('出版社',253,None,48,48,0,True)数据的结构信息：:('出版日期',253,None,40,40,0,True)数据的结构信息：:('版次',3,None,1,1,0,True)数据表的字段名:商品ID,商品编号,图书名称,价格,ISBN,作者,出版社,出版日期,版次【任务8-4】在books数据表中批量添加多条记录【任务描述】（1）在项目Unit08创建Python程序文件8-4.py。（2）连接已存在的MySQL数据库“eCommerce”。（3）定义列表bookData，该列表的元素为元组，包含6个元素，每一个元素存放一本图书的相关数据，对应数据表中一条记录的数据。（4）在数据表“books”中插入6条记录数据。【任务实施】在PyCharm项目Unit08中创建Python程序文件8-4.py。在程序文件8-4.py中编写程序代码，实现所需功能。importpymysql#打开数据库连接conn=pymysql.connect("localhost","root","123456","eCommerce",charset="utf8")#使用cursor()方法创建一个游标对象cursorcursor=conn.cursor()cursor.execute("deletefrombooks")#数据列表bookData=[("12528944","PPT设计从入门到精通",79.00,\"9787115454614","张晓景","人民邮电出版社","2019-1-1",1),("12563157","给Python点颜色青少年学编程",59.80,\"9787115512321","佘友军","人民邮电出版社","2019-9-1",1),("12451724","Python从入门到项目实践（全彩版）",99.80,\"9787569226614","明日科技","吉林大学出版社","2018-8-1",1),("12520987","乐学Python编程-做个游戏很简单",69.80,\"9787302519867","王振世","清华大学出版社","2019/4/1",1),("12550531","Python编程锦囊（全彩版）",79.80,\"9787569250244","明日科技","吉林大学出版社","2019-6-1",1),("12325352","Python程序设计",39.60,\"9787040493726","黄锐军","高等教育出版社","2018-3-1",1),]try:#执行sql语句，插入多条数据cursor.executemany("InsertIntobooks(商品编号,图书名称,价格,ISBN,作者,出版社,出版日期,版次)\values(%s,%s,%s,%s,%s,%s,%s,%s)",bookData)#提交数据mit()exceptExceptionaserror:#发生错误时回滚conn.rollback()print(error)#关闭数据库连接conn.close()程序8-4.py的代码成功运行，则表示向数据表books成功插入6条记录。【任务8-5】自定义函数实现MySQL数据表新增、修改、删除与查询的综合操作【任务描述】（1）在项目Unit08创建Python程序文件8-5.py。（2）自定义多个函数，分别实现所需功能：MySQL()函数用于连接Mysql数据库“eCommerce”；closeSql()函数用于关闭数据库；insertData()函数用于向数据表中灵活插入记录数据；selectAllData()函数用于灵活获取数据表中的全部记录；selectData()函数用于根据指定查询条件灵活获取数据表中符合条件的记录数据；selectDataOrder()函数用于灵活获取数据表中有序的记录数据；updateData()函数用于灵活更新数据表中符合指定条件的数据；deleteData()函数用于灵活删除符合指定条件的记录；deleteAllData()函数用于清空数据表。（3）定义列表bookData，该列表的元素为元组，包含6个元素，每一个元素存放一本图书的相关数据，对应数据表中一条记录的数据。（4）删除数据表“books”中原有的全部记录。（5）向数据表“books”中插入6条图书数据。（6）调用自定义函数获取数据表“books”中有序记录。（7）从数据表“books”中查询出版社名称为“人民邮电出版社”的所有记录。（8）将数据表“books”中人民邮电出版社出版图书的价格修改为原价格的90%。（9）将数据表“books”中价格低于40元的记录删除。【任务实施】在PyCharm项目Unit08中创建Python程序文件8-5.py。在程序文件8-5.py中编写程序代码，实现所需功能。importpymysqlclassMySQL(object):#连接数据库definitDb(self):#连接数据库self.conn=pymysql.connect(host="localhost",user="root",password="123456",db="eCommerce",port=3306,charset="utf8")returnself.conn#关闭数据库defcloseSql(self):self.conn.close()#排行数据插入方法，该方法可以根据更换表名插入排行数据definsertData(self,cur,value,table):#插入数据的sql语句sqlInsert="InsertInto{table}\(商品编号,图书名称,价格,ISBN,作者,出版社,出版日期,版次)\values(%s,%s,%s,%s,%s,%s,%s,%s)".format(table=table)try:#执行sql语句cur.executemany(sqlInsert,value)#提交mit()exceptExceptionaserror:#错误回滚self.conn.rollback()#输出错误信息print(error)defselectAllData(self,cur,column,table):querySql="Select{column}From{table}"\.format(column=column,table=table)cur.execute(querySql)#执行sql语句results=cur.fetchall()#获取查询的所有记录returnresults#返回查询信息defselectData(self,cur,column,table,where,val1):querySql="Select{column}From{table}Where{where}"\.format(column=column,table=table,where=where)cur.execute(querySql,(val1,))#执行sql语句results=cur.fetchall()#获取查询的所有记录returnresults#返回查询信息defselectDataOrder(self,cur,column,table,val1,val2):querySql="Select{column}From{table}Orderby{field1},{field2}desc"\.format(column=column,table=table,field1=val1,field2=val2)cur.execute(querySql)#执行sql语句results=cur.fetchall()#获取查询的所有记录returnresults#返回查询信息#更新数据表中的数据defupdateData(self,cur,table,up,where,value1,value2):sqlUpdate="Update{table}Set{up}Where{where}".format(table=table,up=up,where=where)try:cur.execute(sqlUpdate,(value1,value2))#执行sql语句#提交mit()exceptExceptionaserror:#错误回滚self.conn.rollback()#输出错误信息print(error)defdeleteData(self,cur,table,where,value):#插入数据的sql语句sqlDelete="deletefrom{table}Where{where}"\.format(table=table,where=where)try:#执行sql语句cur.executemany(sqlDelete,(value,))#提交mit()exceptExceptionaserror:#错误回滚self.conn.rollback()#输出错误信息print(error)#清空数据表defdeleteAllData(self,cur,table):sqlDelete="Truncatetable{table}".format(table=table)try:cur.execute(sqlDelete)#像sql语句传递参数#提交mit()exceptExceptionaserror:#回滚self.conn.rollback()#输出错误信息print(error)#数据列表bookData=[("12528944","PPT设计从入门到精通",79.00,\"9787115454614","张晓景","人民邮电出版社","2019-1-1",1