pandas读取数据库并存为Excel

最新推荐文章于 2024-09-04 18:01:16 发布

小基基o_O

最新推荐文章于 2024-09-04 18:01:16 发布

阅读量1.1k

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/Yellow_python/article/details/82380768

版权

数据库专栏收录该内容

21 篇文章 1 订阅

订阅专栏

一张表：存csv

import pandas as pd, pymysql
def db_to_csv(db_name, query, file):
    db = pymysql.connect('localhost', 'root', 'yellow', charset='utf8', db=db_name)
    df =pd.read_sql_query(query, db)
    df.to_csv(file, index=False)
db_to_csv('库名', 'SELECT * FROM 表;', '文件名.csv')

多个sheets：存xlsx

import pandas as pd, pymysql
def db_to_xlsx(db_name, queries, file_name):
    db = pymysql.connect('localhost', 'root', 'yellow', charset='utf8', db=db_name)
    writer = pd.ExcelWriter(file_name)
    for sheet_name, query in queries.items():
        df = pd.read_sql_query(query, db)
        df.to_excel(writer, sheet_name=sheet_name, index=False)
    writer.save()
queries = {
    'sheet1': 'SELECT * FROM 表1;',
    'sheet2': 'SELECT * FROM 表2;',
    'sheet3': 'SELECT * FROM 表3;'}
db_to_xlsx('库名', queries, '文件名.xlsx')