create_table.py

import sqlite3

class CT:
    """
    Create tables
    """
    """
    The trade price≤100 and retail price≤100, quantity≤20
    with regard to calculation, use decimal will be convenient
    the limits are released by input
    """

    def __init__(self):
        self.conn = sqlite3.connect("book_shop.db")
        self.tables = ["admin_t","book_t","customer_t","cart_t","cart_item","order_item","order_t"]

    def create_admin_t(self):
        c = self.conn.cursor()
        c.execute(
            "CREATE TABLE IF NOT EXISTS admin_t"
            "("
            "admin_id INTEGER PRIMARY KEY AUTOINCREMENT, "
            "username TEXT UNIQUE NOT NULL, "
            "pwd TEXT NOT NULL"
            ")"
        )
        self.conn.commit()
        c.close()

    # The cover and description of the book can be uploaded later.
    def create_book_t(self):
        c = self.conn.cursor()
        c.execute(
            "CREATE TABLE IF NOT EXISTS book_t "
            "("
            "isbn TEXT PRIMARY KEY UNIQUE,"
            "book_title TEXT NOT NULL, "
            "book_author TEXT NOT NULL,"
            "pub_date TEXT NOT NULL, "
            "book_des TEXT, "
            "front_cover TEXT,"
            "trade_price decimal(5,2) NOT NULL,"
            "retail_price decimal(5,2) NOT NULL,"
            "s_quantity INTEGER NOT NULL"
            ")"
        )
        c.close()
        self.conn.commit()


    def create_customer_t(self):
        c = self.conn.cursor()
        c.execute(
            "CREATE TABLE IF NOT EXISTS customer_t"
            "("
            "user_id INTEGER PRIMARY KEY AUTOINCREMENT, "
            "username TEXT UNIQUE NOT NULL, "
            "pwd TEXT NOT NULL"
            ")"
        )
        self.conn.commit()
        c.close()


    def create_cart_t(self):
        c = self.conn.cursor()
        c.execute(
            "CREATE TABLE IF NOT EXISTS cart_t"
            "("
            "cart_id INTEGER PRIMARY KEY AUTOINCREMENT, "
            "user_id INTEGER NOT NULL, "
            "FOREIGN KEY(user_id) REFERENCES customer_t(user_id)"
            ")"
        )
        self.conn.commit()
        c.close()

    def create_cart_item(self):
        c=self.conn.cursor()
        c.execute(
            "CREATE TABLE IF NOT EXISTS cart_item"
            "("
            "isbn TEXT NOT NULL,"
            "cart_id INTEGER NOT NULL,"
            "c_quantity INTEGER NOT NULL,"
            "PRIMARY KEY (isbn, cart_id),"
            "FOREIGN KEY (isbn) REFERENCES book_t(isbn),"
            "FOREIGN KEY (cart_id) REFERENCES cart_t(cart_id)"
            ")"
        )
        self.conn.commit()
        c.close()


    def create_order_t(self):
        c = self.conn.cursor()
        c.execute(
            "CREATE TABLE IF NOT EXISTS order_t"
            "("
            "order_id INTEGER PRIMARY KEY AUTOINCREMENT, "
            "user_id INTEGER NOT NULL, "
            "order_time TEXT NOT NULL, "
            "card_no TEXT NOT NULL,"
            "o_quantity INTEGER NOT NULL,"
            "postage INTEGER NOT NULL,"
            "book_cost decimal(7,2) NOT NULL,"
            "order_price decimal(7,2) NOT NULL,"
            "address TEXT NOT NULL,"
            "phone TEXT NOT NULL,"
            "FOREIGN KEY(user_id) REFERENCES customer_t(user_id)"
            ")"
        )
        self.conn.commit()
        c.close()


    def create_order_item(self):
        c=self.conn.cursor()
        c.execute(
            "CREATE TABLE IF NOT EXISTS order_item"
            "("
            "isbn TEXT NOT NULL,"
            "order_id INTEGER NOT NULL,"
            "o_quantity INTEGER NOT NULL,"
            "unit_price decimal(5,2) NOT NULL,"
            "PRIMARY KEY(isbn, order_id),"
            "FOREIGN KEY(isbn) REFERENCES book_t(isbn),"
            "FOREIGN KEY(order_id) REFERENCES order_t(order_id)"
            ")"
        )
        self.conn.commit()
        c.close()



    def close_conn(self):
        self.conn.close()

    def create_tables(self):
        self.create_admin_t()
        self.create_book_t()
        self.create_customer_t()
        self.create_cart_t()
        self.create_cart_item()
        self.create_order_t()
        self.create_order_item()


    def drop_tables(self):
        c = self.conn.cursor()
        for table_name in self.tables:
            c.execute("drop TABLE IF EXISTS {}".format(table_name))
        self.conn.commit()
        c.close()



if __name__ == "__main__":
    ct = CT()
    ct.drop_tables()
    ct.create_tables()
    ct.close_conn()