cMYqmy6P

1#!C:\Python36\python.exe
2print('Content-type: text/html\n\n')
3
4import cgi
5import cgitb; cgitb.enable()
6import sqlite3
7
8mydb = 'wood.db'
9conn = sqlite3.connect(mydb)
10cursor = conn.cursor()
11
12print ('<p><a href="WoodConstruction.html">Return to main page</a></p>')
13
14# Drop tables from DB if they exist
15print('Drop tables if they exist<br />')
16cursor.execute('''DROP TABLE IF EXISTS Supplier''')
17cursor.execute('''DROP TABLE IF EXISTS Import''')
18cursor.execute('''DROP TABLE IF EXISTS Transport''')
19cursor.execute('''DROP TABLE IF EXISTS Vehicle''')
20cursor.execute('''DROP TABLE IF EXISTS Warehouse''')
21cursor.execute('''DROP TABLE IF EXISTS Export''')
22cursor.execute('''DROP TABLE IF EXISTS Buyer''')
23# Create tables
24#******************************************************
25# THIS IS THE SECTION YOU WILL HAVE TO CHANGE
26#
27# Add the SQL to create the Student table here
28
29print('Create Supplier table<br />')
30cursor.execute('''CREATE TABLE Supplier (supplier_id 	    INTEGER PRIMARY KEY AUTOINCREMENT,
31										company_name 		TEXT NOT NULL,
32										address  			TEXT NOT NULL,
33										phone 				TEXT DEFAULT "9999 999 999",
34										);''')
35
36
37
38
39#
40#******************************************************
41
42print('Create Import table<br />')
43cursor.execute('''CREATE TABLE Import  (import_id 			INTEGER PRIMARY KEY AUTOINCREMENT,
44                                        supplier_id         INTEGER NOT NULL,
45										price 	     		TEXT NOT NULL,
46										weight 		    	TEXT NOT NULL,
47										FOREIGN KEY(supplier_id) REFERENCES Supplier(supplier_id)
48										)''')
49
50print('Create Transportation table<br />')
51cursor.execute('''CREATE TABLE Transporation (transport_id 			INTEGER PRIMARY KEY AUTOINCREMENT,
52										      import_id 			INTEGER NOT NULL,
53                                              vehicle_id	        INTEGER NOT NULL,
54										      warehouse_id		    INTEGER NOT NULL,
55									    FOREIGN KEY(import_id)      REFERENCES Import(import_id)
56										FOREIGN KEY(vehicle_id)     REFERENCES Vehicle(vehicle_id)
57										FOREIGN KEY(warehouse_id)   REFERENCES Warehouse(warehouse_id)
58
59											)''')
60
61print('Create Vehicle table<br />')
62cursor.execute('''CREATE TABLE Vehicle  (vehicle_id 		INTEGER PRIMARY KEY AUTOINCREMENT,
63										model 			    TEXT NOT NULL,
64										load_capacity_ton 		INTEGER NOT NULL,
65										)''')
66
67print('Create Warehouse table<br />')
68cursor.execute('''CREATE TABLE Warehouse  (warehouse_id 			INTEGER PRIMARY KEY AUTOINCREMENT,
69                                        address         TEXT NOT NULL,
70										)''')
71
72print('Create Export table<br />')
73cursor.execute('''CREATE TABLE Export  (export_id 			INTEGER PRIMARY KEY AUTOINCREMENT,
74                                        warehouse_id        INTEGER NOT NULL,
75										buyer_id            INTEGER NOT NULL,
76										price               TEXT NOT NULL,
77										import_id           INTEGER NOT NULL,
78										weight              TEXT NOT NULL,
79                                        FOREIGN KEY(warehouse_id)     REFERENCES Warehouse(warehouse_id)
80                                        FOREIGN KEY(buyer_id)         REFERENCES Buyer(buyer_id)
81										FOREIGN KEY(import_id, weight)         REFERENCES Import(import_id, weight)
82										)''')
83
84print('Create Buyer table<br />')
85cursor.execute('''CREATE TABLE Buyer    (buyer_id 	    INTEGER PRIMARY KEY AUTOINCREMENT,
86										company_name 		TEXT NOT NULL,
87										address  			TEXT NOT NULL,
88										phone 				TEXT DEFAULT "9999 999 999",
89										)''')
90
91conn.commit()
92cursor.close()
93print ('New database ', mydb, ' created')