Untitled

import sqlite3

# creating db file and connecting with python
conn = sqlite3.connect('ShoppingMall.db')
cursor = conn.cursor()

cursor.execute('drop table if exists CUSTOMERS')
cursor.execute('drop table if exists ORDERS')
conn.commit()

# Practice to create tables

#varchar(n) : allow n characters
sql_command = '''
CREATE TABLE CUSTOMERS (
    ID   varchar(20) not null,
    Name varchar(20) not null,
    Rating VARCHAR(10) not null,
    Reserves int default 0,
    Phone varchar(11) not null,
    Address varchar(50),
    Primary key(ID) ) ; '''

cursor.execute(sql_command)

conn.commit()

sql_command = '''
CREATE TABLE ORDERS (
    OrderNumber int(10) not null,
    ID   varchar(20) not null,
    ProdName varchar(20) not null,
    Quantity int not null,
    UnitPrice int not null,
    OrderDate Date,
    primary key(OrderNumber) ) ; '''
cursor.execute( sql_command )

conn.commit()

# Inserting data

sql_command = '''
INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
VALUES('ri902', 'Rick', 'Gold', '30000', '2178001000', 'Chicago')
'''
cursor.execute(sql_command)

conn.commit()

sql_command = '''
INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
VALUES('da290', 'David', 'Platinum', '23000', '2132001000', 'NewYork')
'''
cursor.execute(sql_command)

sql_command = '''
INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
VALUES('rona324', 'Emily', 'Silver', '70000', '8019004000', 'Washington')
'''
cursor.execute(sql_command)

sql_command = '''
INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
VALUES('imkim', 'Kim', 'Gold', '20000', '2178002400', 'Dallas')
'''
cursor.execute(sql_command)

sql_command = '''
INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
VALUES('er123', 'Eric', 'Silver', '40000', '3138001000', 'Chicago')
'''
cursor.execute(sql_command)

sql_command = '''
INSERT INTO ORDERS
VALUES('2019031801', 'ri902', 'Kimchi', '10', '35', '20190318')
'''
cursor.execute(sql_command)


sql_command = '''
INSERT INTO ORDERS
VALUES('2019030113', 'da290', 'Ramen', '20', '1', '20190301')
'''
cursor.execute(sql_command)


sql_command = '''
INSERT INTO ORDERS
VALUES('2019021901', 'rona324', 'Ramen', '2', '1', '20190219')
'''
cursor.execute(sql_command)


sql_command = '''
INSERT INTO ORDERS
VALUES('2019031724', 'imkim', 'Icecream', '8', '16', '20190317')
'''
cursor.execute(sql_command)


sql_command = '''
INSERT INTO ORDERS
VALUES('2019031907', 'er123', 'Fried chicken', '1', '17', '20190319')
'''
cursor.execute(sql_command)

conn.commit()


sql_command = '''
INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
VALUES('da290', 'David', 'Platinum', '23000', '2132001000', 'NewYork')
'''
cursor.execute(sql_command)

sql_command = '''
INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
VALUES('rona324', 'Emily', 'Silver', '70000', '8019004000', 'Washington')
'''
cursor.execute(sql_command)

sql_command = '''
INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
VALUES('imkim', 'Kim', 'Gold', '20000', '2178002400', 'Dallas')
'''
cursor.execute(sql_command)

sql_command = '''
INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
VALUES('er123', 'Eric', 'Silver', '40000', '3138001000', 'Chicago')
'''
cursor.execute(sql_command)

sql_command = '''
INSERT INTO ORDERS
VALUES('2019031801', 'ri902', 'Kimchi', '10', '35', '20190318')
'''
cursor.execute(sql_command)


sql_command = '''
INSERT INTO ORDERS
VALUES('2019030113', 'da290', 'Ramen', '20', '1', '20190301')
'''
cursor.execute(sql_command)


sql_command = '''
INSERT INTO ORDERS
VALUES('2019021901', 'rona324', 'Ramen', '2', '1', '20190219')
'''
cursor.execute(sql_command)


sql_command = '''
INSERT INTO ORDERS
VALUES('2019031724', 'imkim', 'Icecream', '8', '16', '20190317')
'''
cursor.execute(sql_command)


sql_command = '''
INSERT INTO ORDERS
VALUES('2019031907', 'er123', 'Fried chicken', '1', '17', '20190319')
'''
cursor.execute(sql_command)

conn.commit()

sql_command = '''
INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
VALUES('da290', 'David', 'Platinum', '23000', '2132001000', 'NewYork')
'''
cursor.execute(sql_command)

sql_command = '''
INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
VALUES('rona324', 'Emily', 'Silver', '70000', '8019004000', 'Washington')
'''
cursor.execute(sql_command)

sql_command = '''
INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
VALUES('imkim', 'Kim', 'Gold', '20000', '2178002400', 'Dallas')
'''
cursor.execute(sql_command)

sql_command = '''
INSERT INTO CUSTOMERS(ID, Name, Rating, Reserves, Phone, Address)
VALUES('er123', 'Eric', 'Silver', '40000', '3138001000', 'Chicago')
'''
cursor.execute(sql_command)

sql_command = '''
INSERT INTO ORDERS
VALUES('2019031801', 'ri902', 'Kimchi', '10', '35', '20190318')
'''
cursor.execute(sql_command)


sql_command = '''
INSERT INTO ORDERS
VALUES('2019030113', 'da290', 'Ramen', '20', '1', '20190301')
'''
cursor.execute(sql_command)


sql_command = '''
INSERT INTO ORDERS
VALUES('2019021901', 'rona324', 'Ramen', '2', '1', '20190219')
'''
cursor.execute(sql_command)


sql_command = '''
INSERT INTO ORDERS
VALUES('2019031724', 'imkim', 'Icecream', '8', '16', '20190317')
'''
cursor.execute(sql_command)


sql_command = '''
INSERT INTO ORDERS
VALUES('2019031907', 'er123', 'Fried chicken', '1', '17', '20190319')
'''
cursor.execute(sql_command)

conn.commit()


# Practicing SELECT statement

result = cursor.execute('SELECT * FROM CUSTOMERS') # all data in customers in tuples

for i in result:
    print(i)

result = cursor.execute('SELECT ID, Name FROM CUSTOMERS')

for i in result:
    print(i)

 # Practicing WHERE
result = cursor.execute('''
SELECT Name FROM CUSTOMERS WHERE Address = 'Chicago'
''')

for i in result:
    print(i)

result = cursor.execute('SELECT * FROM ORDERS WHERE QUANTITY*UNITPRICE > 100 AND ORDERDATE >= 20190302')


for i in result:
    print(i)

# Practicing COUNT
result = cursor.execute('''
SELECT COUNT(*) FROM ORDERS WHERE QUANTITY*UNITPRICE > 100
''')
print(result.fetchone()) #fetchone returns one record as a tuple

# PRACTICING GROUP BY
result = cursor.execute('''
SELECT ProdName, sum(QUANTITY*UNITPRICE) FROM ORDERS GROUP BY PRODNAME
''')

for i in result:
    print(i)

# PRACTICING JOIN

result = cursor.execute('''
SELECT CUSTOMERS.NAME, CUSTOMERS.PHONE, ORDERS.*
FROM CUSTOMERS, ORDERS
WHERE ORDERS.ID=CUSTOMERS.ID
''')

for i in result:
    print(i)

#PRACTICING UPDATE

cursor.execute('''
UPDATE ORDERS SET PRODNAME = 'Cake' WHERE PRODNAME = 'Ramen'
''')

result = cursor.execute('''
SELECT * FROM ORDERS
''')

for r in result:
    print(r)

conn.commit()


# Apply and create pragram

def Menu():
    while True:
        print('1 = customers inquiry')
        print('2 = orders inquiry')
        print('3 = search customer')
        print('4 = search order')
        print('0 = exit program')
        cmd = input('>>')
        if cmd.isdigit():
            cmd = int(cmd)
            if cmd in range(0,5):
                return cmd

def printResult(schema, result):
    schema_list = schema.split()
    print('-'*(len(schema)+10))
    print(schema)
    print('-'*(len(schema)+10))

    for rec in result: # result = tuples in tuple
        for j in range(len(rec)): # rec =tuple
            print(rec[j], end=' ') # print element in tuple
            if len(str(rec[j])) < len(schema_list[j]): #if length of rec < length of schema name
                print(' '*(len(schema_list[j]) - len(str(rec[j]))), end=' ') # fill out that difference with empty spaces

        print() # horizonal space (enter-key)
    print('-'*(len(schema)+10))

# shoppingmall management program
CustomerSchema = 'CustomerID CustomerName Rating Reserves PhoneNumber Address'
OrderSchema = 'Order# CustomerName ProductName Quantity UnitPrice OrderDate'

#conn = sqlite3.conn(ShoppingMall.db)
#cursor = conn.cursor()

running = True
while running:
    cmd = Menu()
    if cmd == 0:
        running = False
    elif cmd == 1:
        result = cursor.execute('SELECT * FROM CUSTOMERS')
        printResult(CustomerSchema, result)
    elif cmd == 2:
        result = cursor.execute('SELECT * FROM ORDERS')
        printResult(OrderSchema, result)
    elif cmd == 3:
        print('Put last four digits of phone #:', end=' ')
        phonenum = input()
        result = cursor.execute('SELECT * FROM CUSTOMERS')
        for r in result:
            cphone = r[4]
            cphone = cphone[-4:-1] + cphone[-1] # last four digits
            if phonenum == cphone:
                print(r)
                break
            else:
                print(phonenum + 'No customer')
    elif cmd == 4:
        print('Put a product name:', end=' ')
        pname = input()
        result = cursor.execute('SELECT * FROM ORDERS')
        for r in result:
            name = r[2]
            if pname == name:
                print(r)

cursor.close()
conn.close()