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
|
from more_itertools import chunked
import openpyxl
import pprint
from itertools import groupby
from operator import itemgetter
wb = openpyxl.load_workbook('test.xlsx')
sheet = wb['Sheet3']
table_name = sheet['C2'].value
mylist = [[c.value for c in r if c.value is not None] for r in sheet.iter_rows(min_row=4,min_col=3) if r[0].value is not None]
mylist = sorted(mylist, key=itemgetter(0))
create_sql_list = dict()
recreate_sql_list = dict()
alter_sql_list = dict()
for (table_name, table_values) in groupby(mylist, key=itemgetter(0)):
print("table_name:" + table_name)
tstr = "CREATE TABLE " + table_name + "("
table_definition = ""
alter_table_str = ""
version_num = len(list(chunked(r[1:], 8)))-1
alter_table_str_list = [""] * version_num
is_recreate_list = [False] * version_num
drop_column_list = [""] * version_num
for r in table_values:
vlist = list(chunked(r[1:], 8))
if (vlist[-1][0] == "〇"):
column_definition_str = vlist[-1][1] + " " + vlist[-1][2]
if (vlist[-1][3] == "〇"):
column_definition_str += " PRIMARY KEY"
if (vlist[-1][4] == "〇"):
column_definition_str += " AUTOINCREMENT"
if ((vlist[-1][5] != "-") and (vlist[-1][6] != "-")):
column_definition_str += " CHECK("+str(vlist[-1][6])+" < "+vlist[-1][1]+" and "+vlist[-1][1]+" < "+str(vlist[-1][5])+")"
elif (vlist[-1][5] != "-"):
column_definition_str += " CHECK("+vlist[-1][1]+" < "+str(vlist[-1][5])+")"
elif (vlist[-1][6] != "-"):
column_definition_str += " CHECK("+str(vlist[-1][6])+" < "+vlist[-1][1]+")"
if (vlist[-1][7] != "-"):
column_definition_str += " DEFAULT "+str(vlist[-1][7])
table_definition += column_definition_str + ", "
for i,columns in enumerate(vlist[:-1]):
if (columns != vlist[-1]):
if ((columns[0] == "×") and (vlist[-1][0] == "〇")):
alter_table_str_list[i] += "ALTER TABLE ADD COLUMN " + column_definition_str + ";"
elif ((columns[0] == "〇") and (vlist[-1][0] == "〇")):
if (columns[1] != vlist[-1][1]):
alter_table_str_list[i] += "ALTER TABLE RENAME COLUMN " + columns[1] + " TO " + vlist[-1][1] + ";"
if (columns[2:] != vlist[-1][2:]):
is_recreate_list[i] = True
elif ((columns[0] == "〇") and (vlist[-1][0] == "×")):
drop_column_list[i] += vlist[-1][1]+","
is_recreate_list[i] = True
table_definition = table_definition[:-2] + ");"
create_sql_list[table_name] = "CREATE TABLE " + table_name + "(" + table_definition
alter_sql_list[table_name] = alter_table_str_list
print(drop_column_list)
recreate_str_list = [""] * version_num
for i,is_recreate in enumerate(is_recreate_list):
if is_recreate:
collist = "*"
recreate_str_list[i] = "CREATE TABLE " + table_name + "_tmp(" + table_definition
recreate_str_list[i] += "INSERT INTO " + table_name + "_tmp(" + collist + ") SELECT " + collist + " FROM " + table_name + ";"
recreate_str_list[i] += "DROP TABLE " + table_name + ";"
recreate_str_list[i] += "ALTER TABLE " + table_name + "_tmp RENAME TO " + table_name + ";"
recreate_sql_list[table_name] = recreate_str_list
print("----------------\n")
print(create_sql_list)
print("----------------\n")
print(recreate_sql_list)
print("----------------\n")
print(alter_sql_list)
|