Python程序设计基础（第3版）-习题及答案 【ch11】数据库操作

第十一章数据库操作1.答：importsqlite3#创建数据库文件Businessdbconn=sqlite3.connect('Businessdb.db')cursor=conn.cursor()#创建Info表cursor.execute('''CREATETABLEIFNOTEXISTSInfo(product_idTEXT,product_nameTEXT,unit_priceINTEGER)''')#创建Customer表cursor.execute('''CREATETABLEIFNOTEXISTSCustomer(customer_idTEXT,customer_nameTEXT,product_idTEXT)''')#输出Info表的所有内容defshow_info_table():cursor.execute('SELECT*FROMInfo')info_records=cursor.fetchall()forrecordininfo_records:print(f'ProductID:{record[0]},ProductName:{record[1]},UnitPrice:{record[2]}')#添加商品购买信息到Customer表defadd_customer_record(customer_id,customer_name,product_id):cursor.execute('INSERTINTOCustomerVALUES(?,?,?)',(customer_id,customer_name,product_id))mit()print("Customerrecordaddedsuccessfully.")#查询顾客编号的消费金额defshow_customer_total_amount(customer_id):cursor.execute('SELECTcustomer_id,SUM(unit_price)AStotal_amountFROMCustomer''JOINInfoONCduct_id=Iduct_id''WHEREcustomer_id=?GROUPBYcustomer_id',(customer_id,))result=cursor.fetchone()ifresult:print(f'CustomerID:{result[0]},TotalAmount:{result[1]}')else:print("CustomerIDnotfound.")#示例数据：添加一些商品信息cursor.executemany('INSERTINTOInfoVALUES(?,?,?)',[('130207','ProductA',100),('130208','ProductB',200),('130209','ProductC',150)])mit()#输出Info表的所有内容show_info_table()#添加商品购买信息whileTrue:customer_id=input("请输入顾客编号(输入0退出程序):")ifcustomer_id=='0':breakcustomer_name=input("请输入顾客姓名:")product_id=input("请输入购买商品编号:")add_customer_record(customer_id,customer_name,product_id)#查询顾客编号的消费金额customer_id_input=input("请输入顾客编号查询消费金额:")show_customer_total_amount(customer_id_input)#关闭数据库连接conn.close()2.答：importsqlite3#创建数据库文件flmdbconn=sqlite3.connect('flmdb.db')cursor=conn.cursor()#创建“热映电影”表cursor.execute('''CREATETABLEIFNOTEXISTShot_movies(movie_nameTEXT,movie_typeTEXT,movie_regionTEXT)''')#创建“排片”表cursor.execute('''CREATETABLEIFNOTEXISTSschedules(movie_nameTEXT,theaterTEXT,ticket_priceINTEGER)''')#输出“热映电影”表的所有内容defshow_hot_movies():cursor.execute('SELECT*FROMhot_movies')movies=cursor.fetchall()formovieinmovies:print(f'MovieName:{movie[0]},Type:{movie[1]},Region:{movie[2]}')#添加排片信息到“排片”表defadd_schedule(movie_name,theater,ticket_price):cursor.execute('INSERTINTOschedulesVALUES(?,?,?)',(movie_name,theater,ticket_price))mit()print("Scheduleaddedsuccessfully.")#查询电影类型的排片信息defshow_schedule_by_type(movie_type):cursor.execute('SELECTmovie_name,theater,ticket_priceFROMschedulesWHEREmovie_nameIN''(SELECTmovie_nameFROMhot_moviesWHEREmovie_type=?)',(movie_type,))schedules=cursor.fetchall()forscheduleinschedules:print(f'MovieName:{schedule[0]},Theater:{schedule[1]},TicketPrice:{schedule[2]}')#示例数据：添加一些热映电影和排片信息cursor.executemany('INSERTINTOhot_moviesVALUES(?,?,?)',[('MovieA','Action','USA'),('MovieB','Comedy','China'),('MovieC','Drama','UK')])cursor.executemany('INSERTINTOschedulesVALUES(?,?,?)',[('MovieA','Theater1',20),('MovieA','Theater2',25),('MovieB','Theater3',18)])mit()#输出“热映电影”表的所有内容show_hot_movies()#添加排片信息add_schedule('MovieB','Theater4',22)#查询电影类型的排片信息show_schedule_by_type('Action')#关闭数据库连接conn.close()3.答：importsqlite3#创建数据库文件Dormdbconn=sqlite3.connect('Dormdb.db')cursor=conn.cursor()#创建"Dorm"表cursor.execute('''CREATETABLEIFNOTEXISTSDorm(dorm_numberTEXT,phoneTEXT,accommodation_feeINTEGER,bed_countINTEGER)''')#创建"Student"表cursor.execute('''CREATETABLEIFNOTEXISTSStudent(student_idTEXT,student_nameTEXT,dorm_numberTEXT)''')#输出"Dorm"表的所有内容defshow_dorms():cursor.execute('SELECT*FROMDorm')dorm_records=cursor.fetchall()forrecordindorm_records:print(f'DormNumber:{record[0]},Phone:{record[1]},AccommodationFee:{record[2]},BedCount:{record[3]}')#添加学生信息到"Student"表defadd_student(student_id,student_name,dorm_number):cursor.execute('INSERTINTOStudentVALUES(?,?,?)',(student_id,student_name,dorm_number))mit()print("Studentaddedsuccessfully.")#查询学生所住的宿舍信息defshow_student_dorm_info(student_id):cursor.execute('SELECTdorm_number,phone,accommodation_feeFROMDormWHEREdorm_number=(SELECTdorm_numberFROMStudentWHEREstudent_id=?)',(student_id,))result=cursor.fetchone()ifresult:print(f'DormNumber:{result[0]},Phone:{result[1]},AccommodationFee:{result[2]}')else:print("Studentnotfound.")#示例数据：添加一些宿舍信息cursor.executemany('INSERTINTODormVALUES(?,?,?,?)',[('Dorm1','123456',500,4),