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

第十一章数据库操作1.答：import sqlite3#创建数据库文件Businessdbconn = sqlite3. connect (? Businessdb. dbJ) cursor = conn, cursor ()#创建Info表cursor, execute C '' CREATE TABLE IF NOT EXISTS Info ( product_id TEXT, product_name TEXT, unit_price INTEGER )''')#创建Customer表cursor, execute (，J ? CREATE TABLE IF NOT EXISTS Customer ( customer_id TEXT,customer_name TEXT, productid TEXT#输出Info表的所有内容 def show_info_table ():cursor, execute (? SELECT * FROM Info')info_records = cursor, fetchall () for record in inforecords:print (f Product ID: record0, Product Name: recordl, Unit Price: record2 ，)#添加商品购买信息到Customer表def add_customer_record(custonier_id, customer_name, product_id):cursor, executeINSERT INTO Customer VALUES (?, ?, ?)', (customer_id, customer_name, productd)conn, commit ()print (Customer record added successfully. z，)#查询顾客编号的消费金额def show_customer_total_amount(customer_id):cursor, execute(? SELECT customer_id, SUM(unit price) AS total_amount FROM Customer ''JOIN Info ON Customer. product_id = Info. product_id ' 'WHERE customer_id=? GROUP BY customer_id，, (customer_id,)result = cursor. fetchoneOif result:print(f, Customer ID: result0, Total Amount: result1?) else:print (""Customer ID not found. z，)#示例数据：添加一些商品信息cursor. executemany(，INSERT INTO Info VALUES (?, ?, ?)', ('130207', ' Product A', 100),(T30208', 'Product B', 200),('130209', ，Product C', 150) conn, commit ()#输出Info表的所有内容 show_info_table()#添加商品购买信息while True:customer_id = input (请输入顾客编号(输入0退出程序)：)if customer id = ' O':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. closeO2.答：import sqlite3#创建数据库文件flmdbconn = sqlite3. connect flmdb. db?)cursor = conn, cursor ()#创建“热映电影”表cursor, execute C '' CREATE TABLE IF NOT EXISTS hot_movies ( movie_name TEXT,movie_type TEXT, movie_region TEXT )''')#创建“排片”表cursor, executed ''CREATE TABLE IF NOT EXISTS schedules ( moviename TEXT, theater TEXT, ticket_price INTEGER )''')#输出“热映电影”表的所有内容def show_hot_movies():cursor, executeSELECT * FROM hot_movies，)movies = cursor. fetchall () for movie in movies:print (fy Movie Name: movie0, Type: movie1, Region: movie2J)#添加排片信息到“排片”表def addschedule(movie_name, theater, ticket_price):cursor, execute (J INSERT INTO schedules VALUES (?, ?, ?), (movie name, theater, ticket price)conn, commit ()print (Z/Schedule added successfully. zz)#查询电影类型的排片信息def show_schedule_by_type(movie_type):cursor, execute C SELECT movie_name, theater, ticket_price FROM schedules WHERE movie_name IN '(SELECT movie_name FROM hot_movies WHERE movie_type=?)J, (movie type,)schedules = cursor, fetchall()for schedule in schedules:print (fJ Movie Name: schedule0, Theater: scheduled), Ticket Price: schedule 2)#示例数据：添加一些热映电影和排片信息cursor, executemany INSERT INTO hot_movies VALUES (?, ?, ?), ('Movie A', 'Action', J USA，),('MovieB','Comedy', ' China'),(J MovieC','Drama'UK')cursor, executemanyINSERT INTO schedules VALUES (?, ?, ?)' , ('Movie A', J Theaterf, 20),(J MovieA',C Movie B',J Theater 2', 25),'Theater 3' , 18)conn, commit ()# 输出“热映电影”表的所有内容 show_hot_movies()# 添加排片信息add_schedule C Movie B', J Theater 4', 22)# 查询电影类型的排片信息show schedule by type (' Action，)# 关闭数据库连接conn, close ()3.答：import sqlite3# 创建数据库文件Dormdbconn = sqlite3. connect Dormdb. db*)cursor = conn, cursor ()# 创建Dorm表cursor, execute C '' CREATE TABLE IF NOT EXISTS Dorm ( dorm_number TEXT, phone TEXT, accommodation_fee INTEGER, bed_count INTEGER# 创建"Student”表cursor, execute C f' CREATE TABLE IF NOT EXISTS Student ( student_id TEXT, student_name TEXT, dorm_number TEXT#输出Dorm表的所有内容def show_dorms ():cursor.execute(J SELECT * FROM Dorm')dorm_records = cursor, fetchall()for record in dorm_records:print(fJDorm Number: record0, Phone: recordl, Accommodation Fee: record2, Bed Count: record 3，)#添加学生信息到"Student”表def add_student(student_id, student_name, dormnumber):cursor, executeINSERT INTO Student VALUES (?,?,?)', (student_id,student_nanie, dorm_number)conn, commit ()print ("Student added successfully. z，)#查询学生所住的宿舍信息def show_student_dorm_info(student_id):cursor, execute(? SELECT dorm number, phone, accommodation fee FROM Dorm WHERE dormnumber = (SELECT dormnumber FROM Student WHERE student_id = ?)', (student_id,)result = cursor. fetchoneOif result:print (fJDorm Number: result0, Phone: result1, Accommodation Fee: result 2')else:print (""Student not found. )#示例数据：添加一些宿舍信息cursor, executemany (，INSERT INTO Dorm VALUES (?, ?, ?, ?), ('Dorml', '123456', 500, 4),('Donn2','789012',450, 3),('Dorm3,' 345678,600, 5) conn, commit ()#输出Dorm表的所有内容showdorms ()#添加学生信息while True:student_id=input (请输入学号(输入0退出程序)：)if student_id = ' O': breakstudent_name = input (请输入姓名：)dorm number = input (请输入宿舍号：)addstudent(student_id, studentname, dormnumber)#查询学生所住的宿舍信息 while True:student_id_input二input (请输入学号查询学生所住宿舍信息(输入0退出程序)：) if student_id input = ' O':breakshow_student_dorm_info(student_id_input)#关闭数据库连接 conn, close ()4.略