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

第十一章数据库操作

1.答import sqlite3#创建数据库文件Businessdbconn=sqlite

3.connectBusinessdb.dbJ cursor=conn,cursor#创建Info表cursor,execute CCREATE TABLE IF NOT EXISTS Infoproduct_id TEXT,product_name TEXT,unit_price INTEGER#创建Customer表cursor,execute，JCREATE TABLE IF NOT EXISTS Customercustomer_id TEXT,customer_name TEXT,productid TEXT#输出Info表的所有内容def show_info_table:cursor,executeSELECT*FROM Infoinfo_records=cursor,fetchallfor recordin inforecords:print fProduct ID:{record

[0]},Product Name:{record[l]},Unit Price:{record

[2]}，#添加商品购买信息到Customer表def add_customer_recordcustonier_id,customer_name,product_id:cursor,executeINSERT INTOCustomer VALUES,,,customer_id,customer_name,product」dconn,commit print^Customer recordadded successfully.z，#查询顾客编号的消费金额def show_customer_total_amountcustomer_id:cursor,execute SELECTcustomer_id,SUMunit priceAS total_amount FROMCustomer JOINInfo ONCustomer.product_id=Info.product_idWHERE customer_id=GROUP BYcustomer_id，,customer_id,result=cursor.fetchoneOif result:printf,Customer ID:{result

[0]},Total Amount:{result

[1]}else:print CustomerID not found.z，#示例数据添加一些商品信息cursor.executemany，INSERT INTOInfo 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 customerid==O:breakcustomer_name=input〃请输入顾客姓名〃product_id=input〃请输入购买商品编号〃add customerrecordcustomer id,customer name,product id#查询顾客编号的消费金额customer_id_input二input〃请输入顾客编号查询消费金额〃show_customer_total_amountcustomer_id_input#关闭数据库连接conn.closeO

2.答import sqlite3#创建数据库文件flmdbconn=sqlite

3.connect flmdb.dbcursor=conn,cursor#创建“热映电影”表cursor,execute CCREATE TABLE IF NOT EXISTShot_moviesmovie_nameTEXT,movie_type TEXT,movie_region TEXT#创建“排片”表cursor,executed CREATETABLEIFNOTEXISTSschedulesmoviename TEXT,theater TEXT,ticket_price INTEGER#输出“热映电影”表的所有内容def show_hot_movies:cursor,executeSELECT*FROM hot_movies，movies=cursor.fetchallfor moviein movies:print fyMovie Name:{movie

[0]},Type:{movie

[1]},Region:{movie

[2]}J#添加排片信息到“排片”表def addschedulemovie_name,theater,ticket_price:cursor,execute JINSERT INTOschedules VALUES,,’,movie name,theater,ticketpriceconn,commit printZ/Schedule addedsuccessfully.zz#查询电影类型的排片信息def show_schedule_by_typemovie_type:cursor,execute CSELECTmovie_name,theater,ticket_price FROMschedules WHEREmovie_nameIN SELECTmovie_name FROMhot_movies WHEREmovie_type=J,movie type,schedules=cursor,fetchallfor schedulein schedules:print fJ Movie Name:{schedule

[0]},Theater:{scheduled],Ticket Price:{schedule

[2]#示例数据添加一些热映电影和排片信息cursor,executemany INSERT INTO hot_movies VALUES,,’,[Movie A,Action,J USA，,Movie B,Comedy,China,J MovieC,DramaUK]cursor,executemanyINSERT INTOschedules VALUES,,,[Movie A,J Theaterf,20,JMovieA,J Theater2,25,C Movie B,Theater3,18]conn,commit#输出“热映电影”表的所有内容show_hot_movies#添加排片信息add_schedule CMovieB,J Theater4,22#查询电影类型的排片信息show scheduleby typeAction，#关闭数据库连接conn,close

3.答import sqlite3#创建数据库文件Dormdbconn=sqlite

3.connect Dormdb.db*cursor=conn,cursor#创建〃Dorm〃表cursor,execute CCREATETABLEIFNOTEXISTS Dormdorm_number TEXT,phone TEXT,accommodation_fee INTEGER,bed_count INTEGER#创建Student”表cursor,execute Cf CREATETABLEIFNOTEXISTSStudentstudent_id TEXT,student_name TEXT,dorm_number TEXT#输出〃Dorm〃表的所有内容def show_dorms:cursor.executeJ SELECT*FROM Dormdorm_records=cursor,fetchallfor recordin dorm_records:printfJDorm Number:{record

[0]},Phone:{record[l]},Accommodation Fee:{record

[2]},Bed Count:{record

[3]}，#添加学生信息到Student”表def add_studentstudent_id,student_name,dormnumber:cursor,executeINSERT INTOStudent VALUES,,,student_id,student_nanie,dorm_numberconn,commit print Student addedsuccessfully.z，#查询学生所住的宿舍信息def show_student_dorm_infostudent_id:cursor,execute SELECTdorm number,phone,accommodation feeFROM DormWHERE dormnumber=SELECT dormnumberFROM StudentWHERE student_id=,student_id,result=cursor.fetchoneOif result:print fJDormNumber:{result

[0]},Phone:{result

[1]},Accommodation Fee:{result

[2]}else:printStudentnotfound.〃#示例数据添加一些宿舍信息cursor,executemany，INSERTINTODorm 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〃请输入宿舍号〃addstudentstudent_id,studentname,dormnumber#查询学生所住的宿舍信息while True:student_id_input二input〃请输入学号查询学生所住宿舍信息输入0退出程序〃ifstudent_id input==O:breakshow_student_dorm_infostudent_id_input#关闭数据库连接conn,close

4.略。