琉璃小屋-用python从数据库导出数据到excel文件

安装xwlt

pip install xlwt
Python语言中，写入Excel文件的扩展工具。相应的有xlrd扩展包，专门用于excel读取

编写py文件

import pymysql, xlwt
select * from xxx where xxx into outfile 'xxx.xls'
host = 'IP地址'
user = '数据库用户名'
password = '数据库密码'
db = '库名'

def export_excel(user):
# 连接数据库，查询数据
conn = pymysql.connect(user=user, host=host, port=8306, passwd=password, db=db, charset='utf8')
cur = conn.cursor()
sql = 'select * from %s' % cr_black_list
cur.execute(sql) # 返回受影响的行数

fields = [field[0] for field in cur.description]  # 获取所有字段名
all_data = cur.fetchall()  # 所有数据

# 写入excel
book = xlwt.Workbook()
sheet = book.add_sheet('sheet1')

for col, field in enumerate(fields):
    sheet.write(0, col, field)

row = 1
for data in all_data:
    for col, field in enumerate(data):
        sheet.write(row, col, field)
    row += 1
book.save("%s.xls" % user)

if name == 'main':
export_excel('user')