Skip to content

Commit 6774dcd

Browse files
dominictdominict
committed
Added SQLite demo and cleaned up
added files for SQLite vaccine management program and cleaned up the extra files in the repo
1 parent 71183de commit 6774dcd

6 files changed

Lines changed: 152 additions & 0 deletions

File tree

4 DB in Python/DBconnectgrades.py renamed to 4 DB in Python/MySQL Demo/DBconnectgrades.py

File renamed without changes.

4 DB in Python/Sample Student Data.csv renamed to 4 DB in Python/MySQL Demo/Sample Student Data.csv

File renamed without changes.

4 DB in Python/Sample Student Data.xlsx renamed to 4 DB in Python/MySQL Demo/Sample Student Data.xlsx

File renamed without changes.

4 DB in Python/StudentsDBdump.sql renamed to 4 DB in Python/MySQL Demo/StudentsDBdump.sql

File renamed without changes.

4 DB in Python/pymodoro.db renamed to 4 DB in Python/SQLite Demo/myinventory.db

8 KB
Binary file not shown.

‎4 DB in Python/SQLite Demo/vinventory.py‎

Lines changed: 152 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,152 @@
1+
''' This is a simple vaccine management system using SQLite.
2+
It demonstrates the capabilities of SQLite. It is not intended for production environments.
3+
For example, the changemade variable does not update once the program is started.
4+
So, if someone left the application open for a long time and made changes later, the data would not be accurate.
5+
'''
6+
import sqlite3
7+
#importing Error this way let's us refer to it by this name instead of sqlite3.Error
8+
from sqlite3 import Error
9+
import datetime
10+
database_file_path = "myinventory.db"
11+
12+
def create_connection(db_file):
13+
""" create a database connection to the SQLite database
14+
specified by the db_file
15+
:param db_file: database file
16+
:return: Connection object or None
17+
"""
18+
try:
19+
connection = sqlite3.connect(db_file)
20+
return connection
21+
except Error as e:
22+
print(e)
23+
return None
24+
25+
def insert_data():
26+
name = input("Enter the name of the item: ")
27+
ndc = input("Enter the national drug code of the item: ")
28+
location = input ("Enter the item inventory location: ")
29+
availability = input("Enter number of doses left: ")
30+
arrivaldate = input("Enter arrival date: ")
31+
expirationdate = input("Enter expiration date: ")
32+
changemade = str(now.year) +"/"+str(now.month) +"/"+str(now.day)
33+
try:
34+
sqlresult = conn.execute("INSERT INTO vaccines (name,ndc,location,availability,arrivaldate,expirationdate,changemade)\
35+
values("+"'"+ str(name) +"'" + ",'"+ str(ndc) +"', '"+ str(location) +"','"+ str (availability)+"','"+str(arrivaldate)+"','"+ str (expirationdate)+"','"+str(changemade)+"')")
36+
result = conn.commit() #this actually runs the SQL and inserts the data into the database
37+
if result == None:
38+
print("*** Data saved to database. ***")
39+
except Error as e:
40+
print ("*** Insert error: ",e)
41+
pass
42+
43+
def view_data():
44+
try:
45+
cursor = conn.execute ("SELECT id,name, ndc,location,availability,arrivaldate, expirationdate,changemade FROM vaccines" )
46+
alldata = []
47+
alldata.append(["ID","Name","NDC","Location","Availability","Arrival Date","Expiration Date","Last Update"])
48+
for row in cursor:
49+
thisrow=[]
50+
for x in range(8):
51+
thisrow.append(row[x])
52+
alldata.append(thisrow)
53+
return alldata
54+
except Error as e:
55+
print (e)
56+
pass
57+
58+
def update_data():
59+
for row in view_data():
60+
thisrow = " --> "
61+
for item in row:
62+
thisrow += str(item) + " "
63+
print (thisrow)
64+
update_ID = input("Enter the ID of the data record to edit: ")
65+
print('''
66+
1 = edit name
67+
2 = edit ndc
68+
3 = edit location
69+
4 = edit availability
70+
5 = edit arrivaldate
71+
6 = edit expirationdate''')
72+
73+
feature = input("Enter which feature of the data do you want to edit: ")
74+
update_value = input ("Editing "+feature+ ": enter the new value: ")
75+
76+
if(feature == "1"):
77+
sql = "UPDATE vaccines set name = ? where id = ?"
78+
elif (feature == "2"):
79+
sql = "UPDATE vaccines set ndc = ? where id = ?"
80+
elif (feature == "3"):
81+
sql = "UPDATE vaccines set location = ? where id = ?"
82+
elif (feature == "4"):
83+
sql = "UPDATE vaccines set availability = ? where id = ?"
84+
elif (feature == "5"):
85+
sql = "UPDATE vaccines set arrivaldate = ? where id = ?"
86+
elif (feature == "6"):
87+
sql = "UPDATE vaccines set expirationdate = ? where id = ?"
88+
89+
try:
90+
#if we call the connection execute method it invisibly creates a cursor for us
91+
conn.execute(sql, (update_value,update_ID))
92+
#update the change made date log
93+
sql = "UPDATE vaccines set changemade = ? where id = ?"
94+
changemade = str(now.year) +"/"+str(now.month) +"/"+str(now.day)
95+
conn.execute(sql, (changemade,update_ID))
96+
97+
except Error as e:
98+
print(e)
99+
pass
100+
101+
def delete_data():
102+
id_ = input("Enter the ID for the data record to delete:")
103+
cursor = conn.cursor() #This sets a spot in the database connection (cursor) for targeted retrieval
104+
cursor.execute("select name from vaccines where ID = "+id_) #create an object referencing the data
105+
delete_item = cursor.fetchall() # get the data
106+
confirm = input("Are you sure you want to delete " + id_ + " " + str(delete_item[0]) + "? (Enter 'y' to confirm.)")
107+
if confirm.lower() == "y":
108+
try:
109+
delete_sql = "DELETE FROM vaccines WHERE id = ?"
110+
conn.execute(delete_sql,id_)
111+
result = conn.commit() #capture the result of the commit and use it to check the result
112+
if result == None:
113+
print (id_ + " " + str(delete_item[0]) + " deleted.")
114+
else:
115+
print ("Deletion failed during SQL execution.")
116+
except Error as e:
117+
print (e)
118+
pass
119+
else:
120+
print("Deletion aborted.")
121+
122+
conn = create_connection(database_file_path)
123+
now = datetime.datetime.now()
124+
125+
if conn:
126+
print ("Connected to database: ",conn)
127+
else:
128+
print("Error connecting to database.")
129+
130+
while True:
131+
print("Welcome to the Vaccine Management System!")
132+
print("1 to view the data")
133+
print("2 to insert a new data record")
134+
print("3 to update a data record")
135+
print("4 to delete a data record")
136+
print("X to exit")
137+
name = input ("Choose an operation to perform: ")
138+
if (name =="1"):
139+
for row in view_data():
140+
thisrow = " --> "
141+
for item in row:
142+
thisrow += str(item) + " "
143+
print (thisrow)
144+
elif(name == "2"):
145+
insert_data()
146+
elif(name == "3"):
147+
update_data()
148+
elif(name == "4"):
149+
delete_data()
150+
elif(name == "X"):
151+
conn.close()
152+
break

0 commit comments

Comments
 (0)