2023019058_.py.zip

import csv import sqlite3 str_xuehao = "2023019082" db_file = str_xuehao + "_s1.db" tf_pairs = { "meal_dishes_detail": "C:\\Users\\星际超狗史迪仔\\Desktop\\pythonProject1\\data\\meal_dishes_detail.csv", "meal_order_detail": "data\\meal_order_detail.csv", "meal_order_info": "data\\meal_order_info.csv", } db_conn = None def ImportData(): for key in tf_pairs: with open(tf_pairs[key], "r", encoding="utf-8") as csvfile: reader = csv.reader(csvfile) cur = db_conn.cursor() count = -1 for row in reader: count += 1 if count == 0: continue sql = "insert into " + key + " values" + str(tuple(row)) try: cur.execute(sql) except Exception as e: print(e) try: db_conn.commit() except Exception as e: print(e) def CalcCount(): count = 0 cur = db_conn.cursor() sql = "select info_id from meal_order_info" for row in cur.execute(sql): if row[0][-1] == str_xuehao[-1]: count += 1 print(count) sql = "insert into data_analysis values('Task01', " + str(count) + ")" try: cur.execute(sql) db_conn.commit() except Exception as e: print(e) def OrderAnalysis(): osum = 0 cur = db_conn.cursor() sql = "select info_id from meal_order_info" for row in cur.execute(sql): if row[0][-1] == str_xuehao[-1]: sql = "select amounts from meal_order_detail where order_id='" + row[0] + "'" for r2 in cur.execute(sql): osum += int(r2[0]) print(osum) sql = "insert into data_analysis values('Task02', " + str(osum) + ")" try: cur.execute(sql) db_conn.commit() except Exception as e: print(e) def DeepAnalysis(): ocsum = 0 cur = db_conn.cursor() sql = "select info_id from meal_order_info" for row in cur.execute(sql): if row[0][-1] == str_xuehao[-1]: sql = "select dishes_id, amounts from meal_order_detail where order_id='" + row[0] + "'" for r2 in cur.execute(sql): sql = "select cost from meal_dishes_detail where id='" + r2[0] + "'" for r3 in cur.execute(sql): ocsum += int(r2[1]) * float(r3[0]) print(ocsum) sql = "insert into data_analysis values('Task03', " + str(ocsum) + ")" try: cur.execute(sql) db_conn.commit() except Exception as e: print(e) if __name__ == "__main__": db_conn = sqlite3.connect(db_file) ImportData() CalcCount() OrderAnalysis() DeepAnalysis() db_conn.close()