q24UxxrA

1import sqlite3
2from sqlite3 import Error
3
4
5def create_connection(db_file):
6    """ create a database connection to the SQLite database
7        specified by db_file
8    :param db_file: database file
9    :return: Connection object or None
10    """
11    conn = None
12    try:
13        conn = sqlite3.connect(db_file)
14        return conn
15    except Error as e:
16        print(e)
17
18    return conn
19
20
21def create_table(conn, create_table_sql):
22    """ create a table from the create_table_sql statement
23    :param conn: Connection object
24    :param create_table_sql: a CREATE TABLE statement
25    :return:
26    """
27    try:
28        c = conn.cursor()
29        c.execute(create_table_sql)
30    except Error as e:
31        print(e)
32
33
34def main():
35    database = r"C:\Users\Admin\Downloads\Assessment 3 - Instructions, Database and Template-20200512\PaalLarssen.sqlite"
36
37    sql_create_AuthorBooks_table = """ CREATE TABLE IF NOT EXISTS AuthorBooks (
38                                        AuthorID NUMERIC,
39                                        BookID NUMERIC,
40                                        FOREIGN KEY (AuthorID)
41                                            REFERENCES Authors (AuthorID),
42                                        FOREIGN KEY Books (BookID)
43                                            REFERENCES Books (BookID)
44                                        
45                                ); """
46
47    sql_create_Authors_table = """ CREATE TABLE IF NOT EXISTS Authors (
48                                    AuthorID NUMERIC PRIMARY KEY,
49                                    Name TEXT
50                            ); """
51
52    sql_create_Publishers_table = """ CREATE TABLE IF NOT EXISTS Publishers (
53                                      PublisherID NUMERIC PRIMARY KEY,
54                                      Name TEXT,
55                                      City TEXT,
56                                      Country TEXT  
57                                ); """
58
59    sql_create_Books_table = """ CREATE TABLE IF NOT EXISTS Books (
60                                 BookID NUMERIC PRIMARY KEY,
61                                 Title TEXT,
62                                 ISBN NUMERIC,
63                                 ISBN13 NUMERIC,
64                                 PublisherID NUMERIC,
65                                 Year_Published NUMERIC,
66                                 Price NUMERIC,
67                                 FOREIGN KEY (PublisherID)
68                                     REFERENCES Publishers (PublisherID)
69                                        ); """
70
71    sql_create_Orders_table = """ CREATE TABLE IF NOT EXISTS Orders (
72                                  OrderID NUMERIC PRIMARY KEY,
73                                  OrderDate NUMERIC,
74                                  CustID NUMERIC,
75                                  TotalAmount NUMERIC,
76                                  FOREIGN KEY CustID
77                                      REFERENCES Customers (CustID)
78                                            ); """
79
80    sql_create_OrderItems_table = """ CREATE TABLE IF NOT EXISTS OrderItems (
81                                      OrderLineID NUMERIC PRIMARY KEY,
82                                      OrderID NUMERIC,
83                                      BookID NUMERIC,
84                                      UnitPrice NUMERIC,
85                                      Quantity NUMERIC,
86                                      FOREIGN KEY OrderID
87                                          REFERENCES Orders (OrderID),
88                                      FOREIGN KEY BookID
89                                          REFERENCES Books (BookID)
90                                ); """
91
92    sql_create_Customers_table = """ CREATE TABLE IF NOT EXISTS Customers (
93                                     CustID NUMERIC PRIMARY KEY,
94                                     FirstName TEXT,
95                                     LastName TEXT,
96                                     Address TEXT,
97                                     City TEXT,
98                                     Country TEXT,
99                                     Phone NUMERIC
100                                            ); """
101
102    # create a database connection
103    conn = create_connection(database)
104
105    # create tables
106    if conn is not None:
107        create_table(conn, sql_create_AuthorBooks_table)
108
109        # create tasks table
110        create_table(conn, sql_create_Authors_table)
111
112        # create tasks table
113        create_table(conn, sql_create_Publishers_table)
114
115        # create tasks table
116        create_table(conn, sql_create_Books_table)
117
118        # create tasks table
119        create_table(conn, sql_create_Orders_table)
120
121        # create tasks table
122        create_table(conn, sql_create_OrderItems_table)
123
124        # create tasks table
125        create_table(conn, sql_create_Customers_table)
126    else:
127        print("Error! cannot create the database connection.")
128
129
130if __name__ == '__main__':
131    main()