-
Notifications
You must be signed in to change notification settings - Fork 0
/
run_update_db.py
executable file
·89 lines (61 loc) · 2.53 KB
/
run_update_db.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
#! ./venv/bin/python
import json
import os
from mysql.connector import Error, MySQLConnection
from util.db_connector_config import read_db_config
cwd = os.getcwd()
def update_db():
tables = os.listdir('{0}/save-on-data'.format(cwd))
for table in tables:
update_table(table)
def update_table(table_name):
db_config = read_db_config()
data_dir = '{0}/save-on-data'.format(cwd)
valid_name = table_name.replace(',', '')
files = os.listdir('{0}/{1}/'.format(data_dir, table_name))
if not files:
print('{0} directory is empty...'.format(table_name))
return
query = """
DROP TABLE IF EXISTS `{0}`;
CREATE TABLE {0} (brand VARCHAR(40), name VARCHAR(50),
category VARCHAR (200),
current_price VARCHAR(20),
regular_price VARCHAR(20), size VARCHAR(20),
current_unit_price VARCHAR(20),
description VARCHAR(200), sku VARCHAR(20));
INSERT INTO {0} VALUES
""".format(valid_name)
for file in files:
file_name = '{0}/{1}/{2}'.format(data_dir, table_name, file)
with open(file_name) as r:
table_data = json.load(r)
for pe in table_data:
query += '("{0}", "{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "{7}", "{8}"),'.format(
pe['brand'].replace('\'', '').replace('\"', ''),
pe['name'].replace('\'', '').replace('\"', ''),
pe['category'].replace('\'', '').replace('\"', ''),
pe['current_price'].replace('\'', '').replace('\"', ''),
pe['regular_price'].replace('\'', '').replace('\"', ''),
pe['size'].replace('\'', '').replace('\"', ''),
pe['current_unit_price'].replace('\'', '').replace('\"', ''),
pe['description'].replace('\'', '').replace('\"', ''),
pe['sku'].replace('\'', '').replace('\"', ''))
try:
query = query[:-1]
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
results = cursor.execute(query, multi=True)
i = 1
for result in results:
if i % 3 == 0:
print('Inserting into saveon_db.`{0}`'.format(valid_name))
i += 1
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
if __name__ == '__main__':
update_db()