Skip to content

Commit 78201d7

Browse files
yidao620cyidao620c
committed
add excel to mysql
1 parent 76a4014 commit 78201d7

File tree

4 files changed

+221
-1
lines changed

4 files changed

+221
-1
lines changed

‎basic/mycore/logmsg.py

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -13,8 +13,11 @@
1313
import logging
1414
import logging.handlers as handlers
1515
import logging.config as config
16-
__author__ = 'Xiong Neng'
1716

17+
__author__ = 'Xiong Neng'
18+
logging.basicConfig(level=logging.INFO,
19+
format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
20+
datefmt='%Y-%m-%d %H:%M:%S')
1821
# 模块基本用_，类级别用__
1922
_log = logging.getLogger('app.' + __name__)
2023

@@ -23,6 +26,7 @@ class FilterFunc(logging.Filter):
2326
def __init__(self, name):
2427
super().__init__()
2528
self.funcname = name
29+
2630
def filter(self, record):
2731
if record.funcName == self.funcname: return False
2832

‎basic/mydatabase/mysql_db.py

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
1+
#!/usr/bin/env python
2+
# -*- encoding: utf-8 -*-
3+
"""
4+
Topic: 利用MySQL Connector/Python 操作mysql数据库
5+
6+
网址：http://dev.mysql.com/doc/connector-python/en/index.html
7+
8+
"""
9+
import mysql.connector
10+
from mysql.connector import errorcode
11+
12+
13+
def _connect():
14+
config = {
15+
'user': 'root',
16+
'password': 'mysql',
17+
'host': '192.168.203.95',
18+
'database': 'hangxin',
19+
'raise_on_warnings': True,
20+
}
21+
cnx = None
22+
try:
23+
cnx = mysql.connector.connect(**config)
24+
except mysql.connector.Error as err:
25+
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
26+
print("Something is wrong with your user name or password")
27+
elif err.errno == errorcode.ER_BAD_DB_ERROR:
28+
print("Database does not exist")
29+
else:
30+
print(err)
31+
if cnx:
32+
cnx.close()
33+
return cnx
34+
35+
36+
def _insert():
37+
pass
38+
39+
40+

‎basic/myutils/excel/excel_mysql.py

Lines changed: 176 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,176 @@
1+
#!/usr/bin/env python
2+
# -*- encoding: utf-8 -*-
3+
"""
4+
Topic: 将航信excel表数据转换为MySQL中的数据
5+
Desc :
6+
"""
7+
import sys
8+
import copy
9+
from openpyxl import Workbook
10+
from openpyxl import load_workbook
11+
12+
import logging
13+
import datetime
14+
import mysql.connector
15+
from mysql.connector import errorcode
16+
17+
sql_create1 = """
18+
CREATE TABLE t_enterprise (
19+
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
20+
name VARCHAR(300) NOT NULL COMMENT '企业名称',
21+
tax_code VARCHAR(60) NOT NULL COMMENT '税号',
22+
region_id BIGINT COMMENT '区域ID',
23+
customer_type INTEGER COMMENT '客户类型',
24+
enterprise_type INTEGER COMMENT '企业类型',
25+
address VARCHAR(200) COMMENT '详细地址',
26+
postcode VARCHAR(10) COMMENT '邮编',
27+
tel VARCHAR(30) COMMENT '联系电话',
28+
contact VARCHAR(10) COMMENT '联系人',
29+
fax VARCHAR(30) COMMENT '传真',
30+
mobile VARCHAR(30) COMMENT '手机号',
31+
created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
32+
updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
33+
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT '企业表';
34+
"""
35+
sql_create2 = """
36+
CREATE TABLE t_region (
37+
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
38+
region_code VARCHAR(16) COMMENT '邮编',
39+
regian_name VARCHAR(20) COMMENT '区域名',
40+
note VARCHAR(120) COMMENT '备注',
41+
parent_id BIGINT COMMENT '父级ID',
42+
created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
43+
updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
44+
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT '区域表';
45+
"""
46+
sql_insert_enterprise = """
47+
INSERT INTO t_enterprise
48+
(id,name,tax_code,region_id,customer_type,enterprise_type)
49+
VALUES (%s, %s, %s, %s, %s, %s);
50+
"""
51+
sql_update_enterprise = """
52+
UPDATE t_enterprise
53+
SET
54+
address=%s,
55+
postcode=%s,
56+
tel=%s,
57+
contact=%s,
58+
fax=%s,
59+
mobile=%s
60+
WHERE id=%s
61+
"""
62+
sql_insert_region = """
63+
INSERT INTO t_region
64+
(id,region_code,regian_name,note,parent_id)
65+
VALUES (%s, %s, %s, %s, %s);
66+
"""
67+
logging.basicConfig(level=logging.INFO,
68+
format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
69+
datefmt='%Y-%m-%d %H:%M:%S')
70+
_log = logging.getLogger('app.' + __name__)
71+
72+
73+
def _log_info(msgpre):
74+
_log.info('{}---{}'.format(msgpre, datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
75+
76+
77+
def _connect():
78+
config = {
79+
'user': 'root',
80+
'password': 'mysql',
81+
'host': '192.168.203.95',
82+
'database': 'hangxin',
83+
'raise_on_warnings': True,
84+
}
85+
cnx = None
86+
try:
87+
cnx = mysql.connector.connect(**config)
88+
except mysql.connector.Error as err:
89+
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
90+
print("Something is wrong with your user name or password")
91+
elif err.errno == errorcode.ER_BAD_DB_ERROR:
92+
print("Database does not exist")
93+
else:
94+
print(err)
95+
if cnx:
96+
cnx.close()
97+
return cnx
98+
99+
100+
def _init_table():
101+
conn_ = _connect()
102+
cursor = conn_.cursor()
103+
cursor.execute(sql_create1)
104+
cursor.execute(sql_create2)
105+
cursor.close()
106+
conn_.commit()
107+
conn_.close()
108+
109+
110+
def parse_sheet(wb, sheet_name, column_num, log_msg):
111+
ws = wb[sheet_name] # ws is now an IterableWorksheet
112+
result_list = []
113+
for row in ws.rows:
114+
row_data = []
115+
for i, cell in enumerate(row):
116+
if i >= column_num:
117+
break
118+
row_data.append(cell.value)
119+
result_list.append(row_data[:])
120+
_log_info(log_msg)
121+
return result_list
122+
123+
124+
def xlsx_to_table(xlsx_name):
125+
conn_ = _connect()
126+
_log_info('Excel文件解析start')
127+
wb = load_workbook(xlsx_name, read_only=True)
128+
# 先收集企业资料表
129+
list1 = parse_sheet(wb, 'customer', 6, 'customer表解析end')
130+
data1 = [(v[0], v[1], v[2], v[3], v[4], v[5]) for v in list1[1:]]
131+
# 收集地址和联系人表
132+
list2 = parse_sheet(wb, 'addr', 10, 'addr表解析end')
133+
data2 = [(v[2], v[4], v[5], v[6], v[7], v[8], v[0]) for v in list2[1:]]
134+
# 收集区域表
135+
list3 = parse_sheet(wb, 'region', 5, 'region表解析end')
136+
data3 = [(v[0], v[1], v[2], v[3], v[4]) for v in list3[1:]]
137+
_log_info('Excel文件解析end')
138+
139+
cursor = conn_.cursor()
140+
141+
_log_info('插入企业资料start')
142+
for i, d1 in enumerate(data1):
143+
cursor.execute(sql_insert_enterprise, d1)
144+
if i % 50 == 0:
145+
conn_.commit()
146+
conn_.commit()
147+
_log_info('插入企业资料end')
148+
149+
_log_info('更新企业联系信息start')
150+
for i, d2 in enumerate(data2):
151+
cursor.execute(sql_update_enterprise, d2)
152+
if i % 50 == 0:
153+
conn_.commit()
154+
conn_.commit()
155+
_log_info('插入企业资料表end')
156+
157+
_log_info('插入区域信息start')
158+
for i, d3 in enumerate(data3):
159+
cursor.execute(sql_insert_region, d3)
160+
if i % 50 == 0:
161+
conn_.commit()
162+
conn_.commit()
163+
_log_info('插入区域信息end')
164+
165+
_log_info('数据库更新end')
166+
167+
cursor.close()
168+
conn_.close()
169+
170+
171+
if __name__ == '__main__':
172+
excel = r'D:\download\20150505\gdc.xlsx'
173+
_init_table()
174+
conn = _connect()
175+
xlsx_to_table(excel)
176+
pass

‎basic/myutils/excel/generate_javabean.py renamed to ‎basic/myutils/generate_javabean.py

File renamed without changes.

0 commit comments

Comments
 (0)