init_db.py
1.77 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
60
61
62
63
64
65
import sys
import os
import csv
from configparser import ConfigParser
from sqlalchemy import create_engine
from sqlalchemy.schema import CreateSchema
from sqlalchemy.orm import sessionmaker
from zope.sqlalchemy import register
import transaction
from ..models import (
Base,
Jalur,
Status,
)
my_registry = dict()
def get_file(filename):
base_dir = os.path.split(__file__)[0]
fullpath = os.path.join(base_dir, 'data', filename)
return open(fullpath)
def append_csv(table, filename, keys):
db_session = my_registry['db_session']
with get_file(filename) as f:
reader = csv.DictReader(f)
filter_ = dict()
for cf in reader:
for key in keys:
filter_[key] = cf[key]
q = db_session.query(table).filter_by(**filter_)
found = q.first()
if found:
continue
row = table()
for fieldname in cf:
val = cf[fieldname]
if not val:
continue
setattr(row, fieldname, val)
db_session.add(row)
def main(argv=sys.argv[1:]):
conf_file = argv[0]
conf = ConfigParser()
conf.read(conf_file)
db_url = conf.get('main', 'db_url')
engine = create_engine(db_url, echo=True)
schema_name = 'im'
with engine.connect() as conn:
if not conn.dialect.has_schema(conn, schema_name):
sql = CreateSchema(schema_name)
conn.execute(sql)
conn.commit()
Base.metadata.create_all(engine)
factory = sessionmaker(bind=engine)
my_registry['db_session'] = db_session = factory()
register(db_session)
with transaction.manager:
append_csv(Jalur, 'jalur.csv', ['id'])
append_csv(Status, 'status.csv', ['id'])