Convert Mysql database tables to Excel

sql_to_xlsx.py

# -*- coding: utf-8 -*-
import logging
import os

import mysql.connector
import pandas as pd

logging.basicConfig(level=logging.DEBUG)


def query():
    output_dir = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'out')
    os.makedirs(output_dir, exist_ok=True)
    xlsx_filepath = os.path.join(output_dir, 'foo.xlsx')
    writer = pd.ExcelWriter(xlsx_filepath)

    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        passwd="1",
        database="test"
    )

    mycursor = mydb.cursor()

    mycursor.execute('show tables')
    tables = mycursor.fetchall()  # list

    for table in tables:
        table = table[0]
        logging.info('table {}'.format(table))

        mycursor.execute("SELECT * FROM " + table)

        columns = [desc[0] for desc in mycursor.description]
        myresult = mycursor.fetchall()  # list
        logging.info(myresult)

        df = pd.DataFrame(myresult, columns=columns)

        df.to_excel(writer, sheet_name=table)

    writer.save()


if __name__ == '__main__':
    query()