Python程序設(shè)計(jì)基礎(chǔ)（第3版）-習(xí)題及答案 【ch11】數(shù)據(jù)庫(kù)操作

第十一章數(shù)據(jù)庫(kù)操作1.答：importsqlite3#創(chuàng)建數(shù)據(jù)庫(kù)文件Businessdbconn=sqlite3.connect('Businessdb.db')cursor=conn.cursor()#創(chuàng)建Info表cursor.execute('''CREATETABLEIFNOTEXISTSInfo(product_idTEXT,product_nameTEXT,unit_priceINTEGER)''')#創(chuàng)建Customer表cursor.execute('''CREATETABLEIFNOTEXISTSCustomer(customer_idTEXT,customer_nameTEXT,product_idTEXT)''')#輸出Info表的所有內(nèi)容defshow_info_table():cursor.execute('SELECT*FROMInfo')info_records=cursor.fetchall()forrecordininfo_records:print(f'ProductID:{record[0]},ProductName:{record[1]},UnitPrice:{record[2]}')#添加商品購(gòu)買信息到Customer表defadd_customer_record(customer_id,customer_name,product_id):cursor.execute('INSERTINTOCustomerVALUES(?,?,?)',(customer_id,customer_name,product_id))mit()print("Customerrecordaddedsuccessfully.")#查詢顧客編號(hào)的消費(fèi)金額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.")#示例數(shù)據(jù)：添加一些商品信息cursor.executemany('INSERTINTOInfoVALUES(?,?,?)',[('130207','ProductA',100),('130208','ProductB',200),('130209','ProductC',150)])mit()#輸出Info表的所有內(nèi)容show_info_table()#添加商品購(gòu)買信息whileTrue:customer_id=input("請(qǐng)輸入顧客編號(hào)(輸入0退出程序):")ifcustomer_id=='0':breakcustomer_name=input("請(qǐng)輸入顧客姓名:")product_id=input("請(qǐng)輸入購(gòu)買商品編號(hào):")add_customer_record(customer_id,customer_name,product_id)#查詢顧客編號(hào)的消費(fèi)金額customer_id_input=input("請(qǐng)輸入顧客編號(hào)查詢消費(fèi)金額:")show_customer_total_amount(customer_id_input)#關(guān)閉數(shù)據(jù)庫(kù)連接conn.close()2.答：importsqlite3#創(chuàng)建數(shù)據(jù)庫(kù)文件flmdbconn=sqlite3.connect('flmdb.db')cursor=conn.cursor()#創(chuàng)建“熱映電影”表cursor.execute('''CREATETABLEIFNOTEXISTShot_movies(movie_nameTEXT,movie_typeTEXT,movie_regionTEXT)''')#創(chuàng)建“排片”表cursor.execute('''CREATETABLEIFNOTEXISTSschedules(movie_nameTEXT,theaterTEXT,ticket_priceINTEGER)''')#輸出“熱映電影”表的所有內(nèi)容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]}')#示例數(shù)據(jù)：添加一些熱映電影和排片信息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()#輸出“熱映電影”表的所有內(nèi)容show_hot_movies()#添加排片信息add_schedule('MovieB','Theater4',22)#查詢電影類型的排片信息show_schedule_by_type('Action')#關(guān)閉數(shù)據(jù)庫(kù)連接conn.close()3.答：importsqlite3#創(chuàng)建數(shù)據(jù)庫(kù)文件Dormdbconn=sqlite3.connect('Dormdb.db')cursor=conn.cursor()#創(chuàng)建"Dorm"表cursor.execute('''CREATETABLEIFNOTEXISTSDorm(dorm_numberTEXT,phoneTEXT,accommodation_feeINTEGER,bed_countINTEGER)''')#創(chuàng)建"Student"表cursor.execute('''CREATETABLEIFNOTEXISTSStudent(student_idTEXT,student_nameTEXT,dorm_numberTEXT)''')#輸出"Dorm"表的所有內(nèi)容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]}')#添加學(xué)生信息到"Student"表defadd_student(student_id,student_name,dorm_number):cursor.execute('INSERTINTOStudentVALUES(?,?,?)',(student_id,student_name,dorm_number))mit()print("Studentaddedsuccessfully.")#查詢學(xué)生所住的宿舍信息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.")#示例數(shù)據(jù)：添加一些宿舍信息cursor.executemany('INSERTINTODormVALUES(?,?,?,?)',[('Dorm1','123456',500,4),