python pandas 导出大数据到excel

import pandas as pd
import pymysql
import zipfile

def getDb():
    try:
        conn = pymysql.connect(
            host='xxx',  # 参数host:连接的mysql主机,如果本机是'localhost'或者127.0.0.1
            port=3306,  # mysql端口3306
            user='xxx',  # mysql用户名
            passwd='xxx!@#',  # mysql密码
            db='xxx',  # 数据库名
            charset='utf8mb4'  # 数据库编码
        )
        return conn

    except Exception as e:
        print(e)
    else:
        print('成功连接数据')



def main():
    conn = getDb()
    cur = conn.cursor()
    #导出数据库
    dbname = "res_account"
    exportFile = dbname + ".zip"
    #获取列名
    cur.execute("desc "+dbname)
    fields = cur.fetchall()
    fs = []
    for f in fields:
        fs.append(f[0])

    limit = 10000

    id = 0;
    with zipfile.ZipFile(exportFile, 'w') as zf:
        while True:
            row_count = cur.execute("select * from "+dbname+" where id > %s limit " + str(limit), id)
            if row_count == 0:
                break;

            results = cur.fetchall()

            filename = r"filename" + str(id) + "-" + str(id + limit) + ".xlsx"

            df = pd.DataFrame(results, columns=fs)

            with zf.open(filename, 'w') as buffer:
                with pd.ExcelWriter(buffer) as writer:
                    df.to_excel(writer, index=None)


            id = results[row_count - 1][0]
            print(id)

if __name__ == '__main__':
    main()
此条目发表在pandas, Python分类目录,贴了, 标签。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

看不清?