Python之excel文件从MySQL数据库导入导出
excel文件导入MySQL数据库
import pymysql
import xlrd
import xlwt
def get_conn():
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='db_xlsx', charset='utf8')
return conn
def insert(cur, sql, args):
cur.execute(sql, args)
def read_xlsx_to_mysql(filename):
excel = xlrd.open_workbook(filename) # 打开xlsx文件,返回一个对象
sheet = excel.sheet_by_index(0) # 获取第一个sheet表格
conn = get_conn()
cur = conn.cursor()
sql = 'insert into tb_xlsx values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
print(sheet.nrows)
for row in range(sheet.nrows):
print(row)
args = sheet.row_values(row)
print(args)
print(type(args))
if row == 0:
continue
if args[1] is None or args[1] == '':
continue
insert(cur, sql, args=args)
conn.commit()
cur.close()
conn.close()
if __name__ == '__main__':
read_xlsx_to_mysql('1.xlsx')
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
1.xlsx文件的表头是:
['序号', '合同编号', '义务人', '档案柜号', '柜内编号', '权利人', '放款金额', '放款日期', '他项权利证编号', '抵押物地址', '结清出库日期', '备注', '地区']
1
2
对应的mysql的tb_xlsx的表字段:
CREATE TABLE tb_xlsx(
xuhao VARCHAR(20),
htcode VARCHAR(20),
yiwuren VARCHAR(20),
dagh VARCHAR(20),
gncode VARCHAR(20),
quanliren VARCHAR(20),
fkmoney VARCHAR(20),
fkdata VARCHAR(20),
qitacode VARCHAR(20),
diyaaddr VARCHAR(100),
jqdata VARCHAR(30),
beizhu VARCHAR(30),
zone VARCHAR(30),
PRIMARY KEY(htcode)
)CHARSET=utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MySQL数据库导出Excel文件
import pymysql
import xlwt
def get_conn():
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='db_xlsx', charset='utf8')
return conn
def query_all(cur, sql, args):
cur.execute(sql, args)
return cur.fetchall()
def read_mysql_to_xlsx(filename):
list_table_head = ['序号', '合同编号', '义务人', '档案柜号', '柜内编号', '权利人', '放款金额', '放款日期', '他项权利证编号', '抵押物地址', '结清出库日期', '备注',
'地区']
workbook = xlwt.Workbook()
sheet = workbook.add_sheet('data', cell_overwrite_ok=True)
for i in range(len(list_table_head)):
sheet.write(0, i, list_table_head)
conn = get_conn()
cur = conn.cursor()
sql = 'select * from tb_xlsx'
results = query_all(cur, sql, None)
conn.commit()
cur.close()
conn.close()
row = 1
for result in results:
col = 0
print(type(result))
print(result)
for item in result:
print(item)
sheet.write(row, col, item)
col += 1
row += 1
workbook.save(filename)
if __name__ == '__main__':
read_mysql_to_xlsx('2.xlsx')
---------------------
【转载】
作者:张行之
来源:CSDN
原文:https://blog.csdn.net/qq_33689414/article/details/78310706
版权声明:本文为博主原创文章,转载请附上博文链接!
|
|