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
|
#!/usr/bin/env python3
# import.py is a part of ProductInventory
# An inventory system designed for makeup and related things
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.
import mysql.connector;
categories = ["Primer/Base", "BB/CC", "Foundation",
"Contour", "Bronzer", "Concealer",
"Setting Spray", "Highlighter", "Blusher",
"Eye Primer", "Brow", "Eyeliner",
"Eye Shadow", "Cream Eye Shadow", "Lipstick",
"Face Powder", "Mascara", "Glitter",
"Glitter Glue", "Eyeshadow Palette",
"Lip Liner", "Highlighter Palette",
"Blush Palette", "Glitter Liner", "Pigment",
"Other", "Tests"];
def truncateTables(toDBCursor):
print("truncating tables");
toDBCursor.execute("SET FOREIGN_KEY_CHECKS = 0;");
toDBCursor.execute("TRUNCATE TABLE category");
toDBCursor.execute("TRUNCATE TABLE product");
toDBCursor.execute("SET FOREIGN_KEY_CHECKS = 1;");
def createCategories(toDBCursor):
for i in categories:
print("creating category: {}".format(i));
# we can assume no one is inejcting sql queries here...
toDBCursor.execute("INSERT INTO category (isactive, name) VALUES(1, \"{}\");".format(i));
def importData(fromDBCursor, toDBCursor):
'''
how this works:
get every category from our todatabase
loop through each product in fromdb
where fromdb category name matches todb category name, add product
after, everything not in one of them is added to the "tests" category
'''
print("importing data");
toDBCursor.execute("SET FOREIGN_KEY_CHECKS = 0;");
toDBCursor.execute("SELECT categoryid, name FROM category;");
fromDBCursor.execute("SELECT * from products;");
todb = toDBCursor.fetchall();
fromdb = fromDBCursor.fetchall();
for t in todb:
print("trying {}".format(t[1]));
for f in fromdb:
if f[3] == t[1]:
print("{} == {}".format(f, t));
toDBCursor.execute("INSERT INTO product "
"(categoryid, comment, brand, color, "
"dateadded, deleted) VALUES "
"({}, \"{}\", \"{}\", \"{}\", FROM_UNIXTIME({}), {});"
.format(t[0], f[6], f[4], f[5], f[1], f[2]));
# in our current database we only have 1 image per product
# so a hack like this will work....
try:
image = f[7].decode().split("\"")[1];
print(image);
print("adding {} to productid {}".format(image, t[0]));
toDBCursor.execute("INSERT INTO productimage "
"VALUES({}, \"{}\", FROM_UNIXTIME({}))".format
(t[0], image, f[1]));
except IndexError:
print("no image");
toDBCursor.execute("SET FOREIGN_KEY_CHECKS = 1;");
def main():
resp = input("Running this script will REMOVE ALL DATABASE ENTRIES in the toDB database, continue? (Y/N): ");
if resp.upper() != 'Y':
exit();
fromDB = mysql.connector.connect(
host="192.168.1.17",
user="inventory",
password="inventorypassword"
);
fromDBCursor = fromDB.cursor();
fromDBCursor.execute("USE inventory;");
toDB = mysql.connector.connect(
host="localhost",
user="inventory",
password="inventorypassword"
);
toDBCursor = toDB.cursor();
toDBCursor.execute("USE ProductInventory;");
truncateTables(toDBCursor);
createCategories(toDBCursor);
importData(fromDBCursor, toDBCursor);
toDB.commit();
if __name__ == "__main__":
main();
|
