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

《Python程序设计基础（第3版）——习题及答案 【ch11】数据库操作.docx》由会员分享，可在线阅读，更多相关《Python程序设计基础（第3版）——习题及答案 【ch11】数据库操作.docx（6页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第十一章数据库操作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 (

2、 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_r

3、ecord(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(un

4、it 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.

5、 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=inp

6、ut (请输入购买商品编号：)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,

7、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，)movi

8、es = 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 adde

9、d 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 M

10、ovie 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,DramaUK)cursor, executemanyINSERT INTO schedules VALUES (?, ?, ?) , (Movie A, J Theaterf, 20)

11、,(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

12、, 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 ():c

13、ursor.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 VALU

14、ES (?,?,?), (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 = ?)

15、, (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)

16、 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.略