-
Notifications
You must be signed in to change notification settings - Fork 0
/
create_db_tables.py
130 lines (103 loc) · 4.29 KB
/
create_db_tables.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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
import psycopg2 as pg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
import time
import json
with open('settings.json', 'r') as f:
settings = json.load(f)
POSTGRES, POSTGRES_PWD, CONE_USER, CONE_PASSWORD, CONE_DBNAME = settings['POSTGRES'], settings[
'POSTGRES_PWD'], settings['CONE_USER'], settings['CONE_PASSWORD'], settings['CONE_DBNAME'],
def create_user():
global POSTGRES, POSTGRES_PWD, CONE_USER, CONE_PASSWORD
con = pg2.connect(host='localhost', port=5432,
user=POSTGRES, password=POSTGRES_PWD)
cur = con.cursor()
con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
s = """CREATE ROLE {CONE_USER} WITH
LOGIN
SUPERUSER
CREATEDB
CREATEROLE
INHERIT
NOREPLICATION
CONNECTION LIMIT -1
PASSWORD '{CONE_PASSWORD}';
alter user {CONE_USER} CREATEDB;""".format(
CONE_USER=CONE_USER, CONE_PASSWORD=CONE_PASSWORD)
print(s)
cur.execute(s)
con.commit()
def create_db():
global CONE_USER, CONE_DBNAME, CONE_PASSWORD, POSTGRES, POSTGRES_PWD
print(CONE_USER, CONE_DBNAME, CONE_PASSWORD)
con = pg2.connect(host='localhost', port=5432,
user=CONE_USER, password=CONE_PASSWORD, dbname='postgres')
db = con.cursor()
con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
statement = """CREATE DATABASE {CONE_DBNAME}
WITH
OWNER = {CONE_USER}
ENCODING = 'UTF8'
LC_COLLATE = 'English_India.1252'
LC_CTYPE = 'English_India.1252'
TABLESPACE = pg_default
CONNECTION LIMIT = -1;""".format(CONE_DBNAME=CONE_DBNAME, CONE_USER=CONE_USER)
db.execute(statement)
con.commit()
db.close()
con.close()
def create_tables():
global CONE_USER, CONE_PASSWORD, CONE_DBNAME
con = pg2.connect(host='localhost', port=5432,
user=CONE_USER, password=CONE_PASSWORD, dbname=CONE_DBNAME)
cur = con.cursor()
def execute(statement, con, cursor):
cursor.execute(statement)
con.commit()
cone_records = """CREATE TABLE public.cone_records
(
date date DEFAULT date(now()),
time_stamp integer DEFAULT date_part('epoch'::text, now()),
lot_number text COLLATE pg_catalog."default",
yarn_type text COLLATE pg_catalog."default",
customer_name text COLLATE pg_catalog."default",
lot_weight double precision,
yarn_count text COLLATE pg_catalog."default",
sample_number integer,
density double precision,
error_type text COLLATE pg_catalog."default",
laser_raw_output double precision,
outer_diameter double precision,
volume double precision,
weight_raw_output double precision,
mass double precision,
barcode_raw_input double precision,
id serial primary key,
start_lot_height double precision,
end_lot_height double precision,
weight double precision,
spindle_number text COLLATE pg_catalog."default"
)
TABLESPACE pg_default;
GRANT INSERT, SELECT ON TABLE public.cone_records TO {CONE_USER};""".format(CONE_USER=CONE_USER)
execute(cone_records, con, cur)
port = """create table port(id serial primary key, laser_port text, weight_port text);
GRANT INSERT, SELECT ON TABLE public.cone_records TO {CONE_USER};""".format(CONE_USER=CONE_USER)
execute(port, con, cur)
error = """create table error(id serial primary key, min_density double precision, max_density double precision, min_outer_diameter double precision, max_outer_diameter double precision, min_weight double precision, max_weight double precision);
GRANT INSERT, SELECT ON TABLE public.cone_records TO {CONE_USER};""".format(CONE_USER=CONE_USER)
execute(error, con, cur)
param = """create table param(id serial primary key, empty_tube_diameter double precision, calibration double precision);
GRANT INSERT, SELECT ON TABLE public.cone_records TO {CONE_USER};""".format(CONE_USER=CONE_USER)
execute(param, con, cur)
cur.close()
con.close()
def create_uri():
settings['SQLALCHEMY_URI'] = "postgres://{}:{}@localhost:5432/{}".format(
CONE_USER, CONE_PASSWORD, CONE_DBNAME)
with open('settings.json', 'w') as f:
json.dump(settings, f)
if __name__ == '__main__':
create_user()
create_db()
create_tables()
create_uri()