introductory
Before New Year's Eve that year, the epidemic began to break out, which had a serious impact on the lives and work of people across the country. However, thanks to the strength of the country, the vaccine was quickly developed by the technicians, and people were vaccinated to protect themselves against the new crown virus. This time, we design a "vaccination database" to store the information about people's vaccination and realize the basic operation such as "add, delete, change, and check" as well as other extended functions through the statistics of the vaccination of the new crown vaccine.
Domestic new crown vaccine personnel after vaccination, if not through the data statistics to the database, the amount of data, data query, data verification, data changes and other work will be very inconvenient, through the programming language, the design of the vaccination database to store vaccination-related data, can make the staff and vaccination personnel workload is greatly reduced, thus reducing the workload, saving time, reducing more financial losses, and It is a very efficient, precise, fast and convenient way to realize better data management.
I. Database information
There are four tables in this database design, namely, "vaccination personnel (person)", "manufacturer (manufacturer)", "vaccine information (vaccine)", "vaccination information (object_info)".
- Data items in the "Vaccination of persons" form: name, identity card number, date of birth, sex, home address, contact telephone number, adverse reactions.
- Data items in the Manufacturer table: Manufacturer name, Manufacturer number, Manufacturer address, Contact person, Contact person phone number.
- Data items in the Vaccine Information table: vaccine name, vaccine number, vaccine species, vaccine manufacturer's number, date of manufacture, expiration date.
- Data items in the table "Information on Vaccination": vaccination number, vaccination number, vaccine type, number of doses, name of the vaccinator, ID number of the vaccinator, time of vaccination, unit of vaccination.
major phase
- Determine the topic: 'Course Design - Vaccination Database'.
- Requirements analysis: "Database Data Dictionary", programming language: python + mysql
II. Key Code Display
1. (Main function)
if __name__ == '__main__':
# Global variable initialization
gol._init()
# # Background information on the subject
# menu.course_background()
#
# # Print group member information
# menu.team_info()
# Connection database information
db.connect_to_db(config.db_host, config.db_user, config.db_password, config.db_name)
# Flags for jumping out of the database
flag = True
while (flag):
menu.main_menu()
cin = input("Please enter the operation to be performed:")
choice = int(cin) if () else config.DEFAULT_INPUT
if choice == 0:
print("You have opted out. Thank you for your experience.")
break
elif choice == config.OP_TYPE_ADD:
# ---------------------- added ----------------------------
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:
# ---------------------- Find ----------------------------
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("The operation option was entered incorrectly, please re-enter.")
db.close_db_link()
2. mgr_update.py (update)
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():
# Updated vaccination staffing table
menu.update_menu_1()
cin_update = util.get_user_input(4)
if cin_update == 0:
return
# Updated with ID numbers
cert_no = input("Please enter ID number:")
table = config.table_dict[config.TYPE_1]
table_inject_info = config.table_dict[config.TYPE_2]
if cin_update == 1:
# Update names
new_name = input("Please enter a 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 the phone
new_phone = input("Please enter the revised contact number for the vaccinator:")
sql1 = "UPDATE %s SET phone='%s' WHERE cert_no='%s';" % (table, new_phone, cert_no)
db.execute_sql(sql1)
elif cin_update == 3:
# Update residential address
new_address = input("Please enter the revised home address:")
sql1 = "UPDATE %s SET address='%s' WHERE cert_no='%s';" % (table, new_address, cert_no)
db.execute_sql(sql1)
3. mgr_add.py (add)
def add_data():
# Add operations
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("Table column information:", columns)
field_list = []
data_list = []
for i in columns:
col_name = i[0] # Column name
col_type = i[1] # Column type
col_index_type = i[4] # Column index type
col_desc = i[8] # Column values
# Skip if entry is not required, e.g. id, person_id, etc.
if(col_name in config.SKIP_FIELD_SET):
continue
util.print_log("Currently listed.", i)
# Input values
if col_type == config.FIELD_TYPE_DATETIME:
# Date checking
a = input("Please enter [%s], e.g. 2022-05-02 : " % col_desc)
input_data_invalid = True
while(input_data_invalid):
if(util.is_valid_date(a) == True):
break
a = input("You have entered a date that is not legal, please re-enter it, e.g. 2022-05-02 : ")
else:
a = input("Please enter [%s]: " % col_desc)
# Determine if unique keywords are duplicates
if col_index_type == 'UNI':
sql = '''
SELECT 1 FROM %s
WHERE %s = %s
''' % (table, col_name, a)
util.print_log("Weight check sql: ", sql)
cursor = gol.get_value('cursor')
exist_in_table = 1
while exist_in_table == 1:
dup_result = (sql)
if dup_result == 0:
break
# Re-enter if repeated
a = input("Your input duplicates the information in the table, please re-enter [%s]" % col_name)
field_list.append(col_name)
data_list.append(a)
# Input complete, assemble data
field_str = ",".join(field_list)
data_tuple = tuple(data_list)
# Splice the data
sql = '''
INSERT INTO %s(%s) VALUES %s;
''' % (table, field_str, data_tuple)
util.print_log(sql)
cursor = gol.get_value('cursor')
(sql)
# Submission of data
conn = gol.get_value('conn')
()
util.print_log("%s Table inserted successfully %s" % (table, data_tuple))
return 0
4. mgr_delete.py (delete)
def do_delete():
# Delete operations
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]
# Inoculation 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)
5. mgr_selector.py (find)
def do_select():
# Execute queries
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 manufacturing companies -----------------------
do_select_manufacturer()
def do_select_vaccine():
# Find Vaccine Information
menu.select_menu_3()
cin = util.get_user_input(5)
if cin == 0:
return
# Getting search data
cin2 = input("Please enter search data:")
table = config.table_dict[config.TYPE_3]
query_value = cin2
# Collection of results
result = []
# Vaccine number
if cin == 1:
result = db.query_by_table_field(table, 'vaccine_no', query_value)
# Manufacturer's number
if cin == 2:
result = db.query_by_table_field(table, 'company_no', query_value)
# Date of production
if cin == 3:
sql = '''
SELECT * FROM %s WHERE %s >= '%s'
''' % (table, 'production_at', query_value)
cursor = gol.get_value('cursor')
(sql)
result = ()
# expiration date
if cin == 4:
sql = '''
SELECT * FROM %s WHERE %s <= '%s'
''' % (table, 'expired_at', query_value)
cursor = gol.get_value('cursor')
(sql)
result = ()
show_select_vaccine_result(result)
III. Database data dictionary
1. Vaccination personnel (person)
| field name | Field title | Field type | default (setting) | Remarks on fields |
|---|---|---|---|---|
| id | ID | int | not have | ID |
| name | name and surname | varchar(32) | not have | name and surname |
| cert_no | I.D. number | varchar(32) | not have | I.D. number |
| birthday | birthdays | datetime | not have | birthdays |
| gender | distinguishing between the sexes | vtinyint(1) | not have | distinguishing between the sexes |
| address | home address | varchar(128) | not have | home address |
| phone | telephones | varchar(16) | not have | telephones |
| adverse_effect | adverse reaction | varchar(64) | not have | adverse reaction |
2、Producer (manufacturer)
| field name | Field title | Field type | default (setting) | Remarks on fields |
|---|---|---|---|---|
| id | ID | int | not have | ID |
| name | Manufacturer's name | varchar(255) | not have | Manufacturer's name |
| company_no | Manufacturer's number | varchar(32) | not have | Manufacturer's number |
| address | Manufacturer's address | varchar(255) | not have | Manufacturer's address |
| contact_person | associates | varchar(255) | not have | associates |
| contact_phone | contact person | int | not have | contact person |
3. Vaccine information (vaccine)
| field name | Field title | Field type | default (setting) | Remarks on fields |
|---|---|---|---|---|
| id | ID | int | not have | ID |
| name | Vaccine name | varchar(64) | not have | ID |
| vaccine_no | Vaccine Number | varchar(64) | not have | Vaccine name |
| vaccine_type | Vaccine varieties | datetime(32) | not have | Vaccine varieties |
| company_no | Vaccine Manufacturer Number | vtinyint(32) | not have | Vaccine Manufacturer Number |
| production_at | date of manufacture | datetime | not have | date of manufacture |
| expired_at | expiration date (of document) | datetime | not have | expiration date (of document) |
4. Inoculation information (object_info)
| field name | Field title | Field type | default (setting) | Remarks on fields |
|---|---|---|---|---|
| id | ID | int | not have | ID |
| inject_no | Vaccination number | varchar(32) | not have | Vaccination number |
| vaccine_no | Vaccination number | varchar(32) | not have | Vaccination number |
| vaccine_type | Vaccine varieties | varchar(32) | not have | Vaccine varieties |
| inject_does | inoculation dose | varchar(16) | not have | inoculation dose |
| person_name | Name of Inoculator | varchar(32) | not have | Name of Inoculator |
| person_cert_no | Inoculator's ID number | varchar(32) | not have | Inoculator's ID number |
| inject_at | timing of inoculation | datetime | not have | timing of inoculation |
| inject_company | vaccination unit | varchar(128) | not have | vaccination unit |
IV. Operational effects
1. Design background
2. Group information
3、Main interface
4、New operation
Result: It can be seen that the newly added "Zhang San" information has been successfully inserted.
5. Delete operation
Result: It can be seen that the newly added "Zhang San" information has been successfully deleted.
6、Query operation
7、Modify the operation
Result: The home address of Deng Yanghua has been modified.
Above is Python implementation of vaccination management database step by step details, more information about Python vaccination management database please pay attention to my other related articles!