-
Notifications
You must be signed in to change notification settings - Fork 1
/
load_orderings.py
41 lines (30 loc) · 968 Bytes
/
load_orderings.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
import db
import os
import pymysql
# To load a csv file that contains the orderings table, just execute this file with:
# $ python load_orderings.py
# The idea is that the source of truth is a google sheet that somebody mantains. It can have any number of columns, but
# only the first and second column will be inserted by this script as (number, ordering).
# The google sheet was created by hand by a chunk of code in LegoCatalog.sql, section "Ordering Table"
cxn = db.connect()
cursor = cxn.cursor(pymysql.cursors.DictCursor)
sql = "DROP TABLE IF EXISTS ordering"
print(sql)
cursor.execute(sql)
sql = """
create table ordering (
number VARCHAR(32) PRIMARY KEY,
ordering VARCHAR(64)
)
"""
print(sql)
cursor.execute(sql)
sql = """
LOAD DATA INFILE '{}/data/Csv/Lego - Ordering.csv' INTO TABLE ordering
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'
(number, ordering)
"""
sql = sql.format(os.getcwd())
print(sql)
cursor.execute(sql)
cxn.close()