forked from shonholl/team-4-project
-
Notifications
You must be signed in to change notification settings - Fork 0
/
connection.py
82 lines (66 loc) · 2.45 KB
/
connection.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
import boto3
import psycopg2
def conn():
client = boto3.client('redshift', region_name='eu-west-1')
REDSHIFT_USER = "awsuser"
REDSHIFT_CLUSTER = "redshiftcluster-jlqz8zhcuit6"
REDSHIFT_HOST = "redshiftcluster-jlqz8zhcuit6.cc3hslvy2bfm.eu-west-1.redshift.amazonaws.com"
REDSHIFT_DATABASE = "team-4-db"
credentials = client.get_cluster_credentials(
DbUser=REDSHIFT_USER,
DbName=REDSHIFT_DATABASE,
ClusterIdentifier=REDSHIFT_CLUSTER,
DurationSeconds=3600)
connection = psycopg2.connect(
user=credentials['DbUser'],
password=credentials['DbPassword'],
host=REDSHIFT_HOST,
database=REDSHIFT_DATABASE,
port=5439)
return connection
def initdb():
connection = conn()
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS branches (
branch_id INT IDENTITY (1,1) NOT NULL,
branch_name varchar(22) NOT NULL,
UNIQUE (branch_name),
PRIMARY KEY (branch_id)
);
CREATE TABLE IF NOT EXISTS orders (
order_id INT IDENTITY (1,1) NOT NULL,
order_timestamp timestamp NOT NULL,
branch_id int NOT NULL,
FOREIGN KEY (branch_id) REFERENCES branches (branch_id),
PRIMARY KEY (order_id)
);
CREATE TABLE IF NOT EXISTS payments (
payment_id int NOT NULL,
payment_method varchar(10) NOT NULL,
payment_total float NOT NULL,
FOREIGN KEY (payment_id) REFERENCES orders (order_id),
UNIQUE (payment_id),
PRIMARY KEY (payment_id)
);
CREATE TABLE IF NOT EXISTS products (
product_id INT IDENTITY (1,1) NOT NULL,
product_name varchar(100) NOT NULL,
product_size varchar(10),
product_price float NOT NULL,
UNIQUE (product_name, product_size, product_price),
PRIMARY KEY (product_id)
);
CREATE TABLE IF NOT EXISTS baskets (
order_id int NOT NULL,
product_id int NOT NULL,
product_quantity int NOT NULL,
FOREIGN KEY (product_id) REFERENCES products (product_id),
FOREIGN KEY (order_id) REFERENCES orders (order_id),
UNIQUE (order_id, product_id),
PRIMARY KEY (order_id, product_id)
);
""")
connection.commit()
cursor.close()
connection.close()