Catalog
One 、 Demand analysis
1、 Design background
2、 Design purpose
3、 database information
4、 Main stage
Two 、 Key code display
1、app.py（ The main function ）
2、mgr_update.py（ to update ）
3、mgr_add.py( add to )
4、mgr_delete.py( Delete )
5、mgr_selector.py( lookup )
3、 ... and 、 Database data dictionary
1、 Vaccination personnel （person）
2、 The manufacturer （manufacturer）
3、 Vaccine information （vaccine）
4、 Vaccination information （inject_info）
Four 、 Running effect
1、 Design background
2、 Group information
3、 main interface
4、 New operation
5、 Delete operation
6、 Query operation
7、 Modify the operating
2019 By the end of year , COVID-19 began to break out , It has had a serious impact on the life and work of the people all over the country . But with the strength of the country , Covid-19 vaccine was also quickly developed by technicians , People can resist the harm of covid-19 by vaccinating against covid-19 . This time, through the data statistics of covid-19 vaccination , To design “ Vaccination database ”, Store information about people vaccinating , And implement “ Additions and deletions ” And other basic operations and other extended functions .
After covid-19 vaccination in China , If the data is not counted into the database , Large amount of data , Data query , Data verification , Data change and other work will be very inconvenient , Through programming language , Design vaccination database , To store vaccination related data , It can greatly reduce the workload of staff and vaccinators , Thus reducing the workload , Save time , Reduce more financial losses , Achieve better data management , Is a very efficient 、 accurate 、 quick 、 A convenient way .
There are four tables in this database design . Respectively 《 Vaccination personnel （person）》、《 The manufacturer （manufacturer）》、《 Vaccine information （vaccine）》、《 Vaccination information （inject_info）》 Four tables .
- 《 Vaccination personnel 》 Data items of the table ： full name , ID number , Birthday , Gender , Home address , contact number , Adverse reactions .
- 《 The manufacturer 》 Data items of the table ： Name of manufacturer , Manufacturer number , Address of manufacturer , Contacts , Contact number .
- 《 Vaccine information 》 Data items of the table ： Vaccine name , Vaccine number , Vaccine varieties , Vaccine manufacturer number , Date of manufacture , Expiration time .
- 《 Vaccination information 》 Data items of the table ： Vaccination number , Vaccination number , Vaccine varieties , Inoculation times , Name of vaccinator , Vaccination ID number , Vaccination time , Vaccination unit .
- Determine the topic ：《 curriculum design - Vaccination database 》.
- Demand analysis ：《 Database data dictionary 》, The programming language is ：python + mysql
if __name__ == '__main__':
# Global variable initialization
gol._init()
# # Background information of the subject
# menu.course_background()
#
# # Print group member information
# menu.team_info()
# Connect to database information
db.connect_to_db(config.db_host, config.db_user, config.db_password, config.db_name)
# Jump out of the database logo
flag = True
while (flag):
menu.main_menu()
cin = input(" Please enter the action to perform ：")
choice = int(cin) if cin.isdigit() else config.DEFAULT_INPUT
if choice == 0:
print(" You have chosen to exit , Thank you for your experience ")
break
elif choice == config.OP_TYPE_ADD:
# ---------------------- add to ----------------------------
mgr_add.add_data()
print(config.END_OP_TEXT)
input()
elif choice == config.OP_TYPE_DELETE:
# ---------------------- Delete ----------------------------
mgr_delete.do_delete()
print(config.END_OP_TEXT)
input()
elif choice == config.OP_TYPE_SELECT:
# ---------------------- lookup ----------------------------
mgr_selector.do_select()
print(config.END_OP_TEXT)
input()
elif choice == config.OP_TYPE_UPDATE:
# ---------------------- modify ----------------------------
mgr_update.do_update()
print(config.END_OP_TEXT)
input()
else:
print(" Operation option input error , Please re-enter ")
db.close_db_link()def do_update():
menu.update_menu_total()
cin = util.get_user_input(5)
if cin == 0:
return
if cin == config.TYPE_1:
do_update_person()
if cin == config.TYPE_2:
do_update_inject_info()
if cin == config.TYPE_3:
do_update_vaccine()
if cin == config.TYPE_4:
do_update_manufacturer()
def do_update_person():
# Update vaccination personnel table
menu.update_menu_1()
cin_update = util.get_user_input(4)
if cin_update == 0:
return
# Update according to ID number
cert_no = input(" Please input ID card number ：")
table = config.table_dict[config.TYPE_1]
table_inject_info = config.table_dict[config.TYPE_2]
if cin_update == 1:
# Update name
new_name = input(" Please enter the modified name ：")
sql1 = "UPDATE %s SET name='%s' WHERE cert_no='%s';" % (table, new_name, cert_no)
sql2 = "UPDATE %s SET person_name='%s' WHERE person_cert_no='%s';" % (table_inject_info, new_name, cert_no)
db.execute_sql(sql1)
db.execute_sql(sql2)
elif cin_update == 2:
# Update phone
new_phone = input(" Please enter the contact number of the vaccinator after modification ：")
sql1 = "UPDATE %s SET phone='%s' WHERE cert_no='%s';" % (table, new_phone, cert_no)
db.execute_sql(sql1)
elif cin_update == 3:
# Update address
new_address = input(" Please enter the modified home address ：")
sql1 = "UPDATE %s SET address='%s' WHERE cert_no='%s';" % (table, new_address, cert_no)
db.execute_sql(sql1)def add_data():
# Add operation
add_menu_1()
# Get user input
cin = util.get_user_input(5)
if cin == 0:
return
# Get data table columns
table = config.table_dict[cin]
columns = get_table_col(table)
util.print_log(" Column information of the table : ", columns)
field_list = []
data_list = []
for i in columns:
col_name = i[0] # Name
col_type = i[1] # Column type
col_index_type = i[4] # Column index type
col_desc = i[8] # The column value
# If it is unnecessary to enter, skip , such as id,person_id etc.
if(col_name in config.SKIP_FIELD_SET):
continue
util.print_log(" Current column information ", i)
# The input values
if col_type == config.FIELD_TYPE_DATETIME:
# Date check
a = input(" Please enter 【%s】, for example 2022-05-02 : " % col_desc)
input_data_invalid = True
while(input_data_invalid):
if(util.is_valid_date(a) == True):
break
a = input(" The date you entered is illegal , Please re-enter , for example 2022-05-02 : ")
else:
a = input(" Please enter 【%s】: " % col_desc)
# Determine whether the unique keyword is repeated
if col_index_type == 'UNI':
sql = '''
SELECT 1 FROM %s
WHERE %s = %s
''' % (table, col_name, a)
util.print_log(" Duplicate check sql: ", sql)
cursor = gol.get_value('cursor')
exist_in_table = 1
while exist_in_table == 1:
dup_result = cursor.execute(sql)
if dup_result == 0:
break
# Reenter if repeated
a = input(" Your input is repeated with the information in the table , Please re-enter 【%s】" % col_name)
field_list.append(col_name)
data_list.append(a)
# Input complete , Assembly data
field_str = ",".join(field_list)
data_tuple = tuple(data_list)
# Splicing data
sql = '''
INSERT INTO %s(%s) VALUES %s;
''' % (table, field_str, data_tuple)
util.print_log(sql)
cursor = gol.get_value('cursor')
cursor.execute(sql)
# Submit data
conn = gol.get_value('conn')
conn.commit()
util.print_log("%s Table successfully inserted data %s" % (table, data_tuple))
return 0def do_delete():
# Delete operation
menu.delete_menu_total()
# Get input
cin = util.get_user_input(5)
if cin == 0:
return
if cin == config.TYPE_1:
do_delete_person()
if cin == config.TYPE_2:
do_delete_inject_info()
if cin == config.TYPE_3:
do_delete_vaccine()
if cin == config.TYPE_4:
do_delete_manufacturer()
def do_delete_vaccine():
# Delete vaccine information
# Print menu
menu.delete_menu_3()
# Get user input
cin = util.get_user_input(2)
if cin == 0:
return
delete_value = input(" Please enter specific information ：")
# Get related tables
table = config.table_dict[config.TYPE_3]
table2 = config.table_dict[config.TYPE_2]
# Vaccination number
if cin == 1:
sql1 = '''
DELETE FROM %s WHERE vaccine_no='%s'
''' % (table, delete_value)
sql2 = '''
DELETE FROM %s WHERE vaccine_no='%s'
''' % (table2, delete_value)
db.execute_sql(sql1)
db.execute_sql(sql2)def do_select():
# Execute the query
menu.select_menu_total()
# Get input
cin = util.get_user_input(5)
if cin == 0:
return
if cin == config.TYPE_1:
# ------------------ Find information about vaccinators ----------------
do_select_person()
elif cin == config.TYPE_2:
# ------------------ Find vaccination information -----------------------
do_select_inject_info()
elif cin == config.TYPE_3:
# ------------------ Find vaccine information -----------------------
do_select_vaccine()
elif cin == config.TYPE_4:
# ------------------ Find information about the manufacturer -----------------------
do_select_manufacturer()
def do_select_vaccine():
# Find vaccine information
menu.select_menu_3()
cin = util.get_user_input(5)
if cin == 0:
return
# Get search data
cin2 = input(" Please enter the search data ：")
table = config.table_dict[config.TYPE_3]
query_value = cin2
# result set
result = []
# Vaccine number
if cin == 1:
result = db.query_by_table_field(table, 'vaccine_no', query_value)
# Manufacturer number
if cin == 2:
result = db.query_by_table_field(table, 'company_no', query_value)
# Date of manufacture
if cin == 3:
sql = '''
SELECT * FROM %s WHERE %s >= '%s'
''' % (table, 'production_at', query_value)
cursor = gol.get_value('cursor')
cursor.execute(sql)
result = cursor.fetchall()
# Expiration time
if cin == 4:
sql = '''
SELECT * FROM %s WHERE %s <= '%s'
''' % (table, 'expired_at', query_value)
cursor = gol.get_value('cursor')
cursor.execute(sql)
result = cursor.fetchall()
show_select_vaccine_result(result)
result ： It can be seen that the new “ Zhang San ” The information was successfully inserted .
result ： It can be seen that the new “ Zhang San ” Information deleted successfully .
result ： Deng Yanghua's home address has been modified .
Rookie actual combat , Continuous learning !
