mssql_script6.py
8.18 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
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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
import pyodbc
hil_sql = """
create table hil_se_comparison_info
(
id bigint identity primary key,
uniq_seq nvarchar(128) not null,
application_id nvarchar(64) not null,
application_version tinyint default 0 not null,
customer_type nvarchar(16) not null,
first_submmison_date nvarchar(16) not null,
property_doc_policy nvarchar(16),
individual_cus_info nvarchar(max) not null,
corporate_cus_info nvarchar(max),
vehicle_info nvarchar(max) not null,
insurance_info nvarchar(max) not null,
bank_info nvarchar(max) not null,
quotationt_info nvarchar(max) not null,
update_time datetime not null,
create_time datetime not null
);
create index hil_se_comparison_info_application_id_index
on hil_se_comparison_info (application_id);
create index hil_se_comparison_info_create_time_index
on hil_se_comparison_info (create_time);
create index hil_se_comparison_info_uniq_seq_index
on hil_se_comparison_info (uniq_seq);
create table hil_se_ocr_result
(
id int identity primary key,
application_id nvarchar(64) not null,
bs_ocr nvarchar(max),
mvi_ocr nvarchar(max),
ic_ocr nvarchar(max),
rp_ocr nvarchar(max),
bc_ocr nvarchar(max),
bl_ocr nvarchar(max),
uci_ocr nvarchar(max),
eep_ocr nvarchar(max),
dl_ocr nvarchar(max),
pp_ocr nvarchar(max),
mvc_ocr nvarchar(max),
vat_ocr nvarchar(max),
update_time datetime not null,
create_time datetime not null
);
create unique index hil_se_ocr_result_application_id_uindex
on hil_se_ocr_result (application_id);
create table hil_ca_compare_result
(
id int identity primary key,
application_id nvarchar(64) not null,
is_finish bit default 0 not null,
compare_count smallint default 0 not null,
failed_count smallint default 0 not null,
reason1_count smallint default 0 not null,
reason2_count smallint default 0 not null,
reason3_count smallint default 0 not null,
reason4_count smallint default 0 not null,
reason5_count smallint default 0 not null,
reason6_count smallint default 0 not null,
reason7_count smallint default 0 not null,
reason8_count smallint default 0 not null,
reason9_count smallint default 0 not null,
reason10_count smallint default 0 not null,
result nvarchar(max),
update_time datetime not null,
create_time datetime not null
);
create unique index hil_ca_compare_result_application_id_uindex
on hil_ca_compare_result (application_id);
create index hil_ca_compare_result_update_time_index
on hil_ca_compare_result (update_time);
create table hil_se_compare_result
(
id int identity primary key,
application_id nvarchar(64) not null,
is_finish bit default 0 not null,
compare_count smallint default 0 not null,
failed_count smallint default 0 not null,
reason1_count smallint default 0 not null,
reason2_count smallint default 0 not null,
reason3_count smallint default 0 not null,
reason4_count smallint default 0 not null,
reason5_count smallint default 0 not null,
reason6_count smallint default 0 not null,
reason7_count smallint default 0 not null,
reason8_count smallint default 0 not null,
reason9_count smallint default 0 not null,
reason10_count smallint default 0 not null,
result nvarchar(max),
update_time datetime not null,
create_time datetime not null
);
create unique index hil_se_compare_result_application_id_uindex
on hil_se_compare_result (application_id);
create index hil_se_compare_result_update_time_index
on hil_se_compare_result (update_time);
"""
afc_sql = """
create table afc_se_comparison_info
(
id bigint identity primary key,
uniq_seq nvarchar(128) not null,
application_id nvarchar(64) not null,
application_version tinyint default 0 not null,
customer_type nvarchar(16) not null,
first_submmison_date nvarchar(16) not null,
property_doc_policy nvarchar(16),
individual_cus_info nvarchar(max) not null,
corporate_cus_info nvarchar(max),
vehicle_info nvarchar(max) not null,
insurance_info nvarchar(max) not null,
bank_info nvarchar(max) not null,
quotationt_info nvarchar(max) not null,
update_time datetime not null,
create_time datetime not null
);
create index afc_se_comparison_info_application_id_index
on afc_se_comparison_info (application_id);
create index afc_se_comparison_info_create_time_index
on afc_se_comparison_info (create_time);
create index afc_se_comparison_info_uniq_seq_index
on afc_se_comparison_info (uniq_seq);
create table afc_se_ocr_result
(
id int identity primary key,
application_id nvarchar(64) not null,
bs_ocr nvarchar(max),
mvi_ocr nvarchar(max),
ic_ocr nvarchar(max),
rp_ocr nvarchar(max),
bc_ocr nvarchar(max),
bl_ocr nvarchar(max),
uci_ocr nvarchar(max),
eep_ocr nvarchar(max),
dl_ocr nvarchar(max),
pp_ocr nvarchar(max),
mvc_ocr nvarchar(max),
vat_ocr nvarchar(max),
update_time datetime not null,
create_time datetime not null
);
create unique index afc_se_ocr_result_application_id_uindex
on afc_se_ocr_result (application_id);
create table afc_ca_compare_result
(
id int identity primary key,
application_id nvarchar(64) not null,
is_finish bit default 0 not null,
compare_count smallint default 0 not null,
failed_count smallint default 0 not null,
reason1_count smallint default 0 not null,
reason2_count smallint default 0 not null,
reason3_count smallint default 0 not null,
reason4_count smallint default 0 not null,
reason5_count smallint default 0 not null,
reason6_count smallint default 0 not null,
reason7_count smallint default 0 not null,
reason8_count smallint default 0 not null,
reason9_count smallint default 0 not null,
reason10_count smallint default 0 not null,
result nvarchar(max),
update_time datetime not null,
create_time datetime not null
);
create unique index afc_ca_compare_result_application_id_uindex
on afc_ca_compare_result (application_id);
create index afc_ca_compare_result_update_time_index
on afc_ca_compare_result (update_time);
create table afc_se_compare_result
(
id int identity primary key,
application_id nvarchar(64) not null,
is_finish bit default 0 not null,
compare_count smallint default 0 not null,
failed_count smallint default 0 not null,
reason1_count smallint default 0 not null,
reason2_count smallint default 0 not null,
reason3_count smallint default 0 not null,
reason4_count smallint default 0 not null,
reason5_count smallint default 0 not null,
reason6_count smallint default 0 not null,
reason7_count smallint default 0 not null,
reason8_count smallint default 0 not null,
reason9_count smallint default 0 not null,
reason10_count smallint default 0 not null,
result nvarchar(max),
update_time datetime not null,
create_time datetime not null
);
create unique index afc_se_compare_result_application_id_uindex
on afc_se_compare_result (application_id);
create index afc_se_compare_result_update_time_index
on afc_se_compare_result (update_time);
"""
hil_cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};', autocommit=True)
hil_cursor = hil_cnxn.cursor()
hil_cursor.execute(hil_sql)
hil_cursor.close()
hil_cnxn.close()
afc_cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};', autocommit=True)
afc_cursor = afc_cnxn.cursor()
afc_cursor.execute(afc_sql)
afc_cursor.close()
afc_cnxn.close()