· 4 years ago · May 15, 2021, 11:42 AM
1import sqlite3
2import sys
3import os
4
5def getConnection():
6 #create connection to the database
7 print('Creating connection to the Database')
8 conn = ''
9 try:
10 conn = sqlite3.connect('computer_cards.db')
11 except Exception as ex:
12 print(ex)
13 conn = ''
14 return conn
15
16def displayRecords(conn):
17 #cursor data needs to be fetched before you can use it
18 #fetchall returns a list of lists
19 cursor = conn.execute('SELECT * FROM {}'.format(getTableName()))
20 records = cursor.fetchall()
21# records = cursor.fetchmany(5)
22 for record in records:
23 item = ''
24 print(buildRecordString(record))
25
26def displayRecord(rid,conn):
27 table = getTableName()
28 cursor = conn.execute("SELECT * FROM {} WHERE name = '{}'".format(table,rid))
29 record = cursor.fetchone()
30 if(record == None):
31 print('No records found with a name of {}'.format(rid))
32 else:
33 print(buildRecordString(record))
34
35def updateRecord(rid,conn,name,cores,speed,ram,cost):
36 sql = "UPDATE {} SET name = '{}', cores = {}, cpu_speed = {}, ram = {}, cost = {} WHERE name = '{}'".format(getTableName(),name,cores,speed,ram,cost,rid)
37 cursor = conn.execute(sql)
38 print("Rows updated: {}".format(cursor.rowcount));
39
40def leaveRecord(rid):
41 print('A record with the name {} does not exist'.format(rid))
42
43def buildRecordString(record):
44 item = ''
45 for i in range(len(record)):
46 item += str(record[i]) + '\t' + '\t'
47 return item
48
49def closeConnection(conn):
50 print('Closing connection to the Database')
51 try:
52 conn.close()
53 except Exception as ex:
54 print('Error closing connection {}'.format(ex))
55
56
57def getCommand():
58 response = ''
59 while response == '':
60 response = input('Enter command: ').strip().upper()
61 return response
62
63def displayMenu():
64 os.system('cls')
65 print('A > Add Record')
66 print('P > Print Records')
67 print('R > Print Record')
68 print('U > Update Record')
69 print('D > Delete Record')
70 print('M > Menu')
71 print('Q > Quit')
72
73def validateText(text):
74 text = text.strip()
75 if len(text)>0:
76 valid = True
77 else:
78 valid = False
79 return text,valid
80
81def validateInt(text):
82 text = text.strip()
83 try:
84 text = int(text)
85 valid = True
86 except:
87 valid = False
88 return text,valid
89
90def validateFloat(text):
91 text = text.strip()
92 try:
93 text = float(text)
94 valid = True
95 except:
96 valid = False
97 return text,valid
98
99
100def getData():
101 #TODO Need to validation input
102 valid = True
103 name, valid = validateText(input('Enter Computer Name: '))
104 if valid:
105 cores = -1
106 speed = -1
107 ram = -1
108 cost = -1
109 if valid:
110 cores,valid = validateInt(input('Enter number of cores: '))
111 if valid:
112 speed,valid = validateInt(input('Enter CPU speed: '))
113 if valid:
114 ram,valid = validateInt(input('Enter amount of RAM: '))
115 if valid:
116 cost,valid = validateFloat(input('Enter cost: '))
117 return name,cores,speed,ram,cost,valid
118
119def addRecord(conn,name,cores,speed,ram,cost):
120 try:
121 sql = "INSERT INTO computer(name, cores,cpu_speed,ram,cost) VALUES ('{}', {}, {}, {}, {})".format(name, cores,speed,ram,cost)
122 cursor = conn.execute(sql)
123 conn.commit()
124 print("Rows inserted: {}".format(cursor.rowcount));
125 except Exception:
126 print('Add Record Failed')
127 #traceback.print_exc()
128
129def getRecordID():
130 try:
131 cid = input('Enter Computer ID: ').strip()
132 except:
133 cid = 0
134 return cid
135
136def deleteRecord(cid,conn):
137 try:
138 sql = "DELETE FROM computer WHERE name = '{}'".format(cid)
139 cursor = conn.execute(sql)
140 conn.commit()
141 print("Rows deleted: {}".format(cursor.rowcount));
142 except Exception:
143 print('Delete Record Failed')
144
145def checkCursor(cursor):
146 if cursor == '':
147 print('No table cursor established')
148 else:
149 print('Have Cursor - {}'.format(cursor))
150
151def getTableName():
152 return 'computer'
153
154def recordExists(rid,conn):
155 exists = False
156 cursor = conn.execute("SELECT * FROM {} WHERE name = '{}'".format(getTableName(),rid))
157 record = cursor.fetchone()
158 if(record == None):
159 exists = False
160 else:
161 exists = True
162 return exists
163
164def mainLoop(conn):
165 action = ''
166 while action != 'Q':
167 action = getCommand()
168 if action == 'P':
169 displayRecords(conn)
170 elif action == 'R':
171 rid = getRecordID()
172 if rid != '':
173 displayRecord(rid,conn)
174 elif action == 'U':
175 rid = getRecordID()
176 if rid != '':
177 if(recordExists(rid,conn)):
178 name,cores,speed,ram,cost,valid = getData()
179 if valid:
180 updateRecord(rid,conn,name,cores,speed,ram,cost)
181 else:
182 leaveRecord(rid)
183 elif action == 'M':
184 displayMenu()
185 elif action == 'A':
186 name,cores,speed,ram,cost,valid = getData()
187 if valid:
188 addRecord(conn,name,cores,speed,ram,cost)
189 elif action == 'D':
190 rid = getRecordID()
191 if rid != '':
192 deleteRecord(rid,conn)
193
194def leave():
195 print('Exit')
196
197
198#Main Program
199conn = getConnection()
200if conn == '':
201 print('Failed to establish connection to database')
202else:
203 displayMenu()
204 mainLoop(conn)
205 closeConnection(conn)
206leave()
207