python读取数据库数据输出为excel格式_python从数据库查出结果并以指定格式输出

python读取数据库数据输出为excel格式

# author:Admin@123
# cratedate:2022/7/28
import pymysql, xlwt
import time
import pandas as pd

start = time.perf_counter()
#
def read_db(tb_name):
    """
    :param tb_name: 数据库中的表名
    """
    host, user, passwd, db = 'localhost', 'root', 'root', 'db_goods'
    conn = pymysql.connect(user=user, host=host, port=3306, passwd=passwd, db=db, charset='utf8')
    cur = conn.cursor()
    sql = 'select * from %s' % tb_name
    cur.execute(sql)  # 返回受影响的行数
    fields = [field[0] for field in cur.description]  # 获取所有字段名
    print("所有字段名展示:",fields)
    all_data = cur.fetchall()  # 所有数据
    print("所有数据展示:",all_data)
    # print("数据"% tb_name)
    print("执行的SQL语句",sql)#打印执行的SQL语句

#输出为excel格式
def export_excel(table_name):
    """
    :param table_name: 数据库中的表名
    """
    # 连接数据库，查询数据
    host, user, passwd, db = 'localhost', 'root', 'root', 'db_goods'
    conn = pymysql.connect(user=user, host=host, port=3306, passwd=passwd, db=db, charset='utf8')
    cur = conn.cursor()
    sql = 'select * from %s' % table_name
    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" % table_name)

if __name__ == '__main__':
    # export_excel('tab_user')
    read_db('tab_user')
    print("文件输出成功！")
    end = time.perf_counter()
    # print('运行时间 : %s 秒' % (end - start))
    print("运行时间:%0.0f秒" % (end - start))
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