-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathpsql_setup_modified.py
More file actions
59 lines (49 loc) · 1.95 KB
/
psql_setup_modified.py
File metadata and controls
59 lines (49 loc) · 1.95 KB
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
import psycopg2
c = psycopg2.connect("dbname=postgres user=postgres password=admin") #TODO: edit this.
cur = c.cursor()
cur.execute("DROP TABLE IF EXISTS products CASCADE")
cur.execute("DROP TABLE IF EXISTS profiles CASCADE")
cur.execute("DROP TABLE IF EXISTS profiles_previously_viewed CASCADE")
cur.execute("DROP TABLE IF EXISTS sessions CASCADE")
# All product-related tables
cur.execute("""CREATE TABLE products
(id VARCHAR PRIMARY KEY,
name VARCHAR,
brand VARCHAR,
type VARCHAR,
category VARCHAR,
subcategory VARCHAR,
subsubcategory VARCHAR,
targetaudience VARCHAR,
msrp INTEGER,
discount INTEGER,
sellingprice INTEGER,
deal VARCHAR,
description VARCHAR);""")
# All profile-related tables
cur.execute("""CREATE TABLE profiles
(id VARCHAR PRIMARY KEY,
latestactivity TIMESTAMP,
segment VARCHAR);""")
cur.execute("""CREATE TABLE profiles_previously_viewed
(profid VARCHAR,
prodid VARCHAR,
FOREIGN KEY (profid) REFERENCES profiles (id),
FOREIGN KEY (prodid) REFERENCES products (id));""")
# All session-related tables
cur.execute("""CREATE TYPE d_type AS ENUM ('mobile', 'tablet', 'pc', 'other');""")
cur.execute("""CREATE TABLE sessions
(id VARCHAR PRIMARY KEY,
profid VARCHAR,
segment VARCHAR,
sale BOOLEAN,
starttime TIMESTAMP,
endtime TIMESTAMP,
duration INTEGER,
os VARCHAR,
devicefamily VARCHAR,
devicetype d_type,
FOREIGN KEY (profid) REFERENCES profiles (id));""")
c.commit()
cur.close()
c.close()