· 6 years ago · Mar 24, 2019, 08:02 AM
1import sqlite3
2
3# creating db file and connecting with python
4conn = sqlite3.connect('ShoppingMall.db')
5cursor = conn.cursor()
6
7cursor.execute('drop table if exists CUSTOMERS')
8cursor.execute('drop table if exists ORDERS')
9conn.commit()
10
11# Practice to create tables
12
13#varchar(n) : allow n characters
14sql_command = '''
15CREATE TABLE CUSTOMERS (
16 ID varchar(20) not null,
17 Name varchar(20) not null,
18 Rating VARCHAR(10) not null,
19 Reserves int default 0,
20 Phone varchar(11) not null,
21 Address varchar(50),
22 Primary key(ID) ) ; '''
23
24cursor.execute(sql_command)
25
26conn.commit()
27
28sql_command = '''
29CREATE TABLE ORDERS (
30 OrderNumber int(10) not null,
31 ID varchar(20) not null,
32 ProdName varchar(20) not null,
33 Quantity int not null,
34 UnitPrice int not null,
35 OrderDate Date,
36 primary key(OrderNumber) ) ; '''
37cursor.execute( sql_command )
38
39conn.commit()
40
41# Inserting data
42
43sql_command = '''
44INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
45VALUES('ri902', 'Rick', 'Gold', '30000', '2178001000', 'Chicago')
46'''
47cursor.execute(sql_command)
48
49conn.commit()
50
51sql_command = '''
52INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
53VALUES('da290', 'David', 'Platinum', '23000', '2132001000', 'NewYork')
54'''
55cursor.execute(sql_command)
56
57sql_command = '''
58INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
59VALUES('rona324', 'Emily', 'Silver', '70000', '8019004000', 'Washington')
60'''
61cursor.execute(sql_command)
62
63sql_command = '''
64INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
65VALUES('imkim', 'Kim', 'Gold', '20000', '2178002400', 'Dallas')
66'''
67cursor.execute(sql_command)
68
69sql_command = '''
70INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
71VALUES('er123', 'Eric', 'Silver', '40000', '3138001000', 'Chicago')
72'''
73cursor.execute(sql_command)
74
75sql_command = '''
76INSERT INTO ORDERS
77VALUES('2019031801', 'ri902', 'Kimchi', '10', '35', '20190318')
78'''
79cursor.execute(sql_command)
80
81
82sql_command = '''
83INSERT INTO ORDERS
84VALUES('2019030113', 'da290', 'Ramen', '20', '1', '20190301')
85'''
86cursor.execute(sql_command)
87
88
89sql_command = '''
90INSERT INTO ORDERS
91VALUES('2019021901', 'rona324', 'Ramen', '2', '1', '20190219')
92'''
93cursor.execute(sql_command)
94
95
96sql_command = '''
97INSERT INTO ORDERS
98VALUES('2019031724', 'imkim', 'Icecream', '8', '16', '20190317')
99'''
100cursor.execute(sql_command)
101
102
103sql_command = '''
104INSERT INTO ORDERS
105VALUES('2019031907', 'er123', 'Fried chicken', '1', '17', '20190319')
106'''
107cursor.execute(sql_command)
108
109conn.commit()
110
111
112sql_command = '''
113INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
114VALUES('da290', 'David', 'Platinum', '23000', '2132001000', 'NewYork')
115'''
116cursor.execute(sql_command)
117
118sql_command = '''
119INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
120VALUES('rona324', 'Emily', 'Silver', '70000', '8019004000', 'Washington')
121'''
122cursor.execute(sql_command)
123
124sql_command = '''
125INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
126VALUES('imkim', 'Kim', 'Gold', '20000', '2178002400', 'Dallas')
127'''
128cursor.execute(sql_command)
129
130sql_command = '''
131INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
132VALUES('er123', 'Eric', 'Silver', '40000', '3138001000', 'Chicago')
133'''
134cursor.execute(sql_command)
135
136sql_command = '''
137INSERT INTO ORDERS
138VALUES('2019031801', 'ri902', 'Kimchi', '10', '35', '20190318')
139'''
140cursor.execute(sql_command)
141
142
143sql_command = '''
144INSERT INTO ORDERS
145VALUES('2019030113', 'da290', 'Ramen', '20', '1', '20190301')
146'''
147cursor.execute(sql_command)
148
149
150sql_command = '''
151INSERT INTO ORDERS
152VALUES('2019021901', 'rona324', 'Ramen', '2', '1', '20190219')
153'''
154cursor.execute(sql_command)
155
156
157sql_command = '''
158INSERT INTO ORDERS
159VALUES('2019031724', 'imkim', 'Icecream', '8', '16', '20190317')
160'''
161cursor.execute(sql_command)
162
163
164sql_command = '''
165INSERT INTO ORDERS
166VALUES('2019031907', 'er123', 'Fried chicken', '1', '17', '20190319')
167'''
168cursor.execute(sql_command)
169
170conn.commit()
171
172sql_command = '''
173INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
174VALUES('da290', 'David', 'Platinum', '23000', '2132001000', 'NewYork')
175'''
176cursor.execute(sql_command)
177
178sql_command = '''
179INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
180VALUES('rona324', 'Emily', 'Silver', '70000', '8019004000', 'Washington')
181'''
182cursor.execute(sql_command)
183
184sql_command = '''
185INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
186VALUES('imkim', 'Kim', 'Gold', '20000', '2178002400', 'Dallas')
187'''
188cursor.execute(sql_command)
189
190sql_command = '''
191INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
192VALUES('er123', 'Eric', 'Silver', '40000', '3138001000', 'Chicago')
193'''
194cursor.execute(sql_command)
195
196sql_command = '''
197INSERT INTO ORDERS
198VALUES('2019031801', 'ri902', 'Kimchi', '10', '35', '20190318')
199'''
200cursor.execute(sql_command)
201
202
203sql_command = '''
204INSERT INTO ORDERS
205VALUES('2019030113', 'da290', 'Ramen', '20', '1', '20190301')
206'''
207cursor.execute(sql_command)
208
209
210sql_command = '''
211INSERT INTO ORDERS
212VALUES('2019021901', 'rona324', 'Ramen', '2', '1', '20190219')
213'''
214cursor.execute(sql_command)
215
216
217sql_command = '''
218INSERT INTO ORDERS
219VALUES('2019031724', 'imkim', 'Icecream', '8', '16', '20190317')
220'''
221cursor.execute(sql_command)
222
223
224sql_command = '''
225INSERT INTO ORDERS
226VALUES('2019031907', 'er123', 'Fried chicken', '1', '17', '20190319')
227'''
228cursor.execute(sql_command)
229
230conn.commit()
231
232
233
234# Practicing SELECT statement
235
236result = cursor.execute('SELECT * FROM CUSTOMERS') # all data in customers in tuples
237
238for i in result:
239 print(i)
240
241result = cursor.execute('SELECT ID, Name FROM CUSTOMERS')
242
243for i in result:
244 print(i)
245
246 # Practicing WHERE
247result = cursor.execute('''
248SELECT Name FROM CUSTOMERS WHERE Address = 'Chicago'
249''')
250
251for i in result:
252 print(i)
253
254result = cursor.execute('SELECT * FROM ORDERS WHERE QUANTITY*UNITPRICE > 100 AND ORDERDATE >= 20190302')
255
256
257for i in result:
258 print(i)
259
260# Practicing COUNT
261result = cursor.execute('''
262SELECT COUNT(*) FROM ORDERS WHERE QUANTITY*UNITPRICE > 100
263''')
264print(result.fetchone()) #fetchone returns one record as a tuple
265
266# PRACTICING GROUP BY
267result = cursor.execute('''
268SELECT ProdName, sum(QUANTITY*UNITPRICE) FROM ORDERS GROUP BY PRODNAME
269''')
270
271for i in result:
272 print(i)
273
274# PRACTICING JOIN
275
276result = cursor.execute('''
277SELECT CUSTOMERS.NAME, CUSTOMERS.PHONE, ORDERS.*
278FROM CUSTOMERS, ORDERS
279WHERE ORDERS.ID=CUSTOMERS.ID
280''')
281
282for i in result:
283 print(i)
284
285#PRACTICING UPDATE
286
287cursor.execute('''
288UPDATE ORDERS SET PRODNAME = 'Cake' WHERE PRODNAME = 'Ramen'
289''')
290
291result = cursor.execute('''
292SELECT * FROM ORDERS
293''')
294
295for r in result:
296 print(r)
297
298conn.commit()
299
300
301
302# Apply and create pragram
303
304def Menu():
305 while True:
306 print('1 = customers inquiry')
307 print('2 = orders inquiry')
308 print('3 = search customer')
309 print('4 = search order')
310 print('0 = exit program')
311 cmd = input('>>')
312 if cmd.isdigit():
313 cmd = int(cmd)
314 if cmd in range(0,5):
315 return cmd
316
317def printResult(schema, result):
318 schema_list = schema.split()
319 print('-'*(len(schema)+10))
320 print(schema)
321 print('-'*(len(schema)+10))
322
323 for rec in result: # result = tuples in tuple
324 for j in range(len(rec)): # rec =tuple
325 print(rec[j], end=' ') # print element in tuple
326 if len(str(rec[j])) < len(schema_list[j]): #if length of rec < length of schema name
327 print(' '*(len(schema_list[j]) - len(str(rec[j]))), end=' ') # fill out that difference with empty spaces
328
329 print() # horizonal space (enter-key)
330 print('-'*(len(schema)+10))
331
332# shoppingmall management program
333CustomerSchema = 'CustomerID CustomerName Rating Reserves PhoneNumber Address'
334OrderSchema = 'Order# CustomerName ProductName Quantity UnitPrice OrderDate'
335
336#conn = sqlite3.conn(ShoppingMall.db)
337#cursor = conn.cursor()
338
339running = True
340while running:
341 cmd = Menu()
342 if cmd == 0:
343 running = False
344 elif cmd == 1:
345 result = cursor.execute('SELECT * FROM CUSTOMERS')
346 printResult(CustomerSchema, result)
347 elif cmd == 2:
348 result = cursor.execute('SELECT * FROM ORDERS')
349 printResult(OrderSchema, result)
350 elif cmd == 3:
351 print('Put last four digits of phone #:', end=' ')
352 phonenum = input()
353 result = cursor.execute('SELECT * FROM CUSTOMERS')
354 for r in result:
355 cphone = r[4]
356 cphone = cphone[-4:-1] + cphone[-1] # last four digits
357 if phonenum == cphone:
358 print(r)
359 break
360 else:
361 print(phonenum + 'No customer')
362 elif cmd == 4:
363 print('Put a product name:', end=' ')
364 pname = input()
365 result = cursor.execute('SELECT * FROM ORDERS')
366 for r in result:
367 name = r[2]
368 if pname == name:
369 print(r)
370
371cursor.close()
372conn.close()