Python - export mysql table to excel file

export_db.py

import xlsxwriter
import mysql.connector


def fetch_table_data(table_name):
    # The connect() constructor creates a connection to the MySQL server and returns a MySQLConnection object.
    cnx = mysql.connector.connect(
        host='localhost',
        database='schema',
        user='user',
        password='password'
    )

    cursor = cnx.cursor()
    cursor.execute('select * from ' + table_name)

    header = [row[0] for row in cursor.description]

    rows = cursor.fetchall()

    # Closing connection
    cnx.close()

    return header, rows


def export(table_name):
    # Create an new Excel file and add a worksheet.
    workbook = xlsxwriter.Workbook(table_name + '.xlsx')
    worksheet = workbook.add_worksheet('MENU')

    # Create style for cells
    header_cell_format = workbook.add_format({'bold': True, 'border': True, 'bg_color': 'yellow'})
    body_cell_format = workbook.add_format({'border': True})

    header, rows = fetch_table_data(table_name)

    row_index = 0
    column_index = 0

    for column_name in header:
        worksheet.write(row_index, column_index, column_name, header_cell_format)
        column_index += 1

    row_index += 1
    for row in rows:
        column_index = 0
        for column in row:
            worksheet.write(row_index, column_index, column, body_cell_format)
            column_index += 1
        row_index += 1

    print(str(row_index) + ' rows written successfully to ' + workbook.filename)

    # Closing workbook
    workbook.close()


# Tables to be exported
export('TABLE_1')
export('TABLE_2')

This script works extremely well when I run it locally. Thank you for sharing it.

I'm now trying to run it in a PHP/MySQL web application. It runs without error but does not produce the Excel output. Do you have any ideas on how that might be done??

This script works extremely well when I run it locally. Thank you for sharing it.

I'm now trying to run it in a PHP/MySQL web application. It runs without error but does not produce the Excel output. Do you have any ideas on how that might be done??

Was able to make it work. Thanks again.

Worked flawlessly for me. All I need now is to format a column with timestamp data.
Thank you!