Skip to content

Commit eb87290

Browse files
yidao620cyidao620c
committed
excel to mysql
1 parent 474833b commit eb87290

File tree

1 file changed

+79
-34
lines changed

1 file changed

+79
-34
lines changed

‎basic/myutils/excel/excel_mysql.py

Lines changed: 79 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -25,9 +25,9 @@
2525
address VARCHAR(200) COMMENT '详细地址',
2626
postcode VARCHAR(10) COMMENT '邮编',
2727
tel VARCHAR(50) COMMENT '联系电话',
28-
contact VARCHAR(10) COMMENT '联系人',
28+
contact VARCHAR(60) COMMENT '联系人',
2929
fax VARCHAR(30) COMMENT '传真',
30-
mobile VARCHAR(16) COMMENT '手机号',
30+
mobile VARCHAR(80) COMMENT '手机号',
3131
created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
3232
updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
3333
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT '企业表';
@@ -67,7 +67,7 @@
6767
logging.basicConfig(level=logging.INFO,
6868
format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
6969
datefmt='%Y-%m-%d %H:%M:%S',
70-
handlers=[logging.FileHandler('excel.log', 'a', 'utf-8')])
70+
handlers=[logging.FileHandler('d:/logs/excel.log', 'a', 'utf-8')])
7171
_log = logging.getLogger('app.' + __name__)
7272

7373

@@ -118,8 +118,37 @@ def parse_sheet(wb, sheet_name, column_num, log_msg):
118118
return result_list
119119

120120

121-
def handle_wrong_line(wrong_line):
122-
pass
121+
def handle_wrong_line(conn_, cursor_, wrong_line, ty=1):
122+
# 处理企业资料创建时的错误
123+
if ty == 1:
124+
id1 = wrong_line[0][0]
125+
many_data = wrong_line[0][1]
126+
next1 = wrong_line[1][0]
127+
next2 = wrong_line[1][1]
128+
next3 = wrong_line[1][2]
129+
many_data = many_data.replace('"', '')
130+
many_data = many_data.replace('_x000D_', '')
131+
many_list = [s.split() for s in many_data.split('\n') if s]
132+
many_list[0].insert(0, str(id1))
133+
many_list[-1][-1] = "{}{}".format(many_list[-1][-1], next1)
134+
many_list[-1].append(next2)
135+
many_list[-1].append(next3)
136+
# 扩充至指定的长度
137+
for many_item in many_list:
138+
if len(many_item) < 6:
139+
for i in range(6 - len(many_item)):
140+
many_item.append(None)
141+
for i, v in enumerate([tuple(li) for li in many_list]):
142+
try:
143+
cursor_.execute(sql_insert_enterprise, v)
144+
except:
145+
conn_.rollback()
146+
logging.exception('handle_wrong_line哈企业资料Exception,line={}'.format(i))
147+
continue
148+
if i % 50 == 0:
149+
conn_.commit()
150+
conn_.commit()
151+
123152

124153
def xlsx_to_table(xlsx_name):
125154
conn_ = _connect()
@@ -135,49 +164,65 @@ def xlsx_to_table(xlsx_name):
135164
list3 = parse_sheet(wb, 'region', 5, 'region表解析end')
136165
data3 = [(v[0], v[1], v[2], v[3], v[4]) for v in list3[1:]]
137166
_log.info('Excel文件解析end')
138-
_log.info('---------------------------分割线--------------------------------')
167+
_log.info('---------------------------分割线-----------------------------')
139168
_log.info('数据库更新start')
140169
cursor = conn_.cursor()
141-
try:
142-
_log.info('插入企业资料start')
143-
for i, d1 in enumerate(data1):
144-
if len(d1[1]) > 300 and not d1[2]:
145-
_log.error('这一行有问题')
146-
handle_wrong_line(d1)
147-
continue
170+
_log.info('插入企业资料start')
171+
wrong1 = []
172+
find_large_name = False
173+
for i, d1 in enumerate(data1):
174+
if find_large_name:
175+
wrong1.append(d1)
176+
handle_wrong_line(conn_, cursor, wrong1)
177+
wrong1.clear()
178+
find_large_name = False
179+
try:
148180
cursor.execute(sql_insert_enterprise, d1)
149-
if i % 50 == 0:
150-
conn_.commit()
151-
conn_.commit()
152-
_log.info('插入企业资料end')
181+
except:
182+
conn_.rollback()
183+
if len(str(d1[1])) > 600:
184+
logging.exception('-------插入企业资料Exception,line={}--------'.format(i))
185+
wrong1.append(d1)
186+
find_large_name = True
187+
continue
188+
if i % 50 == 0:
189+
conn_.commit()
190+
conn_.commit()
191+
_log.info('插入企业资料end')
153192

154-
_log.info('更新企业联系信息start')
155-
for i, d2 in enumerate(data2):
193+
_log.info('更新企业联系信息start')
194+
for i, d2 in enumerate(data2):
195+
try:
156196
cursor.execute(sql_update_enterprise, d2)
157-
if i % 50 == 0:
158-
conn_.commit()
159-
conn_.commit()
160-
_log.info('插入企业资料表end')
197+
except:
198+
conn_.rollback()
199+
logging.exception('-------更新企业联系信息Exception,line={}-------'.format(i))
200+
handle_wrong_line(conn_, cursor,d2, ty=2)
201+
if i % 50 == 0:
202+
conn_.commit()
203+
conn_.commit()
204+
_log.info('插入企业资料表end')
161205

162-
_log.info('插入区域信息start')
163-
for i, d3 in enumerate(data3):
206+
_log.info('插入区域信息start')
207+
for i, d3 in enumerate(data3):
208+
try:
164209
cursor.execute(sql_insert_region, d3)
165-
if i % 50 == 0:
166-
conn_.commit()
167-
conn_.commit()
168-
_log.info('插入区域信息end')
169-
170-
except:
171-
logging.exception('Got exception on db handler')
172-
raise
210+
except:
211+
conn_.rollback()
212+
logging.exception('-------插入区域信息Exception,line={}-------'.format(i))
213+
handle_wrong_line(conn_, cursor, d3, ty=3)
214+
if i % 50 == 0:
215+
conn_.commit()
216+
conn_.commit()
217+
_log.info('插入区域信息end')
173218

174219
_log.info('数据库更新end')
175220
cursor.close()
176221
conn_.close()
177222

178223

179224
if __name__ == '__main__':
180-
excel = r'D:\download\20150505\gdc.xlsx'
225+
excel = r'D:\download\20150505\gdc2.xlsx'
181226
_init_table()
182227
conn = _connect()
183228
xlsx_to_table(excel)

0 commit comments

Comments
 (0)