Exporting MySQL Data to Excel With a Python Script

MySQL gives you three easy ways to get rows out of a database, and none writes a good Excel file. Workbench exports a result grid, SELECT ... INTO OUTFILE writes delimited text on the server, and mysqldump writes SQL. A script closes the gap in under thirty lines, and it handles the MySQL-specific oddities, such as zero dates and tinyint flags, that a generic tutorial skips. This post builds that script. It is a spoke of sending database results to Excel.

The built-in options

Workbench's result export. In MySQL Workbench, run the query and use the export button above the result grid. Recent versions offer several formats, including CSV, JSON, HTML and Excel; the exact list depends on the version. It is fine for a quick one-off. It cannot be scheduled, it formats nothing, and what you get depends on the choices made in a dialog nobody records.

**SELECT ... INTO OUTFILE.** The statement writes a file on the server:

SELECT order_no, customer, amount, placed_at
FROM orders
WHERE placed_at >= '2024-03-01' AND placed_at < '2024-04-01'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

The file lands on the database server, and only inside the directory named by the secure_file_priv setting. It will not overwrite an existing file, it adds no header row, and it writes NULL as \N. Managed MySQL services usually disallow it. It is useful for a bulk dump for a program that you control, and not for a file a person will open.

**mysqldump --tab and the mysql client.** mysql -B -e "..." prints tab-separated output with escapes. It suits shell pipelines.

For a workbook, a script is the dependable route.

The script

Install the packages:

pip install pandas sqlalchemy pymysql xlsxwriter

Connect with the character set stated, because a default of latin1 or three-byte utf8 damages some text on the way:

import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine("mysql+pymysql://reporter@localhost/app?charset=utf8mb4")

sql = text("""
    SELECT order_no, customer, amount, is_gift, shipped_on, placed_at
    FROM orders
    WHERE placed_at >= :start AND placed_at < :end
    ORDER BY placed_at, order_no
""")
df = pd.read_sql(sql, engine, params={"start": "2024-03-01", "end": "2024-04-01"})
print(df.dtypes)

order_no              object
customer              object
amount                object
is_gift                int64
shipped_on            object
placed_at     datetime64[ns]
dtype: object

Two columns deserve attention. amount is object, since DECIMAL becomes a Python Decimal. And shipped_on is object, because it contains a value pandas could not turn into a datetime: a zero date.

Fix the MySQL specifics

Zero dates. MySQL's default sql_mode in 8.x blocks 0000-00-00 in new rows, but older data often contains it. PyMySQL returns such a value as None or, depending on settings, raises an error when parsing. If the column ends up as object with strange values, convert and blank them:

df["shipped_on"] = pd.to_datetime(df["shipped_on"], errors="coerce")

errors="coerce" turns unparseable values, including zero dates, into NaT, which pandas writes as an empty cell. Excel cannot represent year 0, so an empty cell is the honest output. You can also avoid the problem in SQL with NULLIF(shipped_on, '0000-00-00'), which works where the setting allows comparing to a zero date, or with a CASE expression.

Decimals and flags.

df["amount"] = df["amount"].astype(float)
df["is_gift"] = df["is_gift"].map({0: "No", 1: "Yes"})
df["order_no"] = df["order_no"].astype(str)

tinyint(1) is MySQL's boolean, and arrives as the integers 0 and 1. A reader wants words, so map them. If a value other than 0 and 1 can occur, the map yields NaN, which is a useful alarm: count the blanks after mapping and investigate if there are any. Use str for identifiers.

Time zones. MySQL DATETIME has no zone, and TIMESTAMP is stored in UTC and converted to the session time zone on read. Check SELECT @@session.time_zone so you know what the values mean, and state it in the workbook. The pandas column is naive in both cases, so it writes without conversion.

Character set. With charset=utf8mb4 in the connection URL, names such as "Zoë" and "東京" arrive intact. If you see question marks or mojibake, check the column's collation and the connection's character_set_results.

Write it

with pd.ExcelWriter("orders_2024-03.xlsx", engine="xlsxwriter", datetime_format="yyyy-mm-dd hh:mm", date_format="yyyy-mm-dd") as writer:
    df.to_excel(writer, sheet_name="Orders", index=False)
    ws = writer.sheets["Orders"]
    money = writer.book.add_format({"num_format": "#,##0.00"})
    ws.freeze_panes(1, 0)
    ws.autofilter(0, 0, len(df), len(df.columns) - 1)
    ws.set_column("A:B", 24)
    ws.set_column("C:C", 12, money)
    ws.set_column("D:D", 8)
    ws.set_column("E:F", 18)

The settings come from writing a DataFrame to Excel with formatting. Open the result and check four things: the amount column sums, the dates are dates, the gift column reads Yes or No, and any name with an accent displays correctly.

Large tables and streaming

A big result should not be read into memory in one piece. Use an unbuffered, server-side cursor:

import pymysql, xlsxwriter

conn = pymysql.connect(host="localhost", user="reporter", database="app",
                       charset="utf8mb4", cursorclass=pymysql.cursors.SSCursor)
with conn.cursor() as cur:
    cur.execute("SELECT order_no, amount, placed_at FROM orders ORDER BY placed_at")
    wb = xlsxwriter.Workbook("orders_big.xlsx", {"constant_memory": True})
    ws = wb.add_worksheet("Orders")
    ws.write_row(0, 0, ["Order", "Amount", "Placed"])
    fmt = wb.add_format({"num_format": "yyyy-mm-dd hh:mm"})
    row = 1
    while batch := cur.fetchmany(5000):
        for order_no, amount, placed in batch:
            ws.write_string(row, 0, str(order_no))
            ws.write_number(row, 1, float(amount))
            if placed:
                ws.write_datetime(row, 2, placed, fmt)
            row += 1
    wb.close()
conn.close()

SSCursor streams rows from the server instead of buffering the whole result in the client. The connection must be read to the end or closed before it is used for anything else, which the with block and close() take care of. Keep the long-running read short: a streaming query holds resources on the server, so run it against a replica if the table is large and busy. The sheet limit applies; write a guard that stops or splits before row 1,048,576 (Excel row and column limits).

Security and permissions

Use a read-only MySQL account, GRANT SELECT on the tables in the report, with the password in an environment variable or an option file rather than in the script. Do not build SQL from strings that include user input; bind parameters as above. Be careful with text columns that hold customer-entered data: a value starting with =, +, - or @ can run as a formula when someone opens the file, and the fix is to write text as text (sending database results to Excel properly every time). The write_string call in the streaming example does that; pandas with the default XlsxWriter settings may not, so pass strings_to_formulas: False in the engine options when the data is untrusted.

Scheduling and checks

A script that runs unattended should check itself. Confirm that the row count is above a floor, that a total is within a tolerance of the last run, and that the file exists and is not empty before delivering it. Run it from cron or a task scheduler, and let it exit with a non-zero code on failure so the scheduler alerts someone (emailing a scheduled Excel report from a database).

When a Python script is more than a MySQL export needs

A one-off for yourself. Use Workbench's export, then check types in Excel. Writing a script for a single file costs more than it saves.

The consumer is a system. Use SELECT ... INTO OUTFILE or mysql -B for a documented delimited file, or Parquet.

The hosting forbids file output and you have no Python. Use the provider's export tools or a BI tool with a MySQL connector.

The readers can connect themselves. A Power Query connection from Excel pulls fresh data on demand, which beats a file that goes stale (Power Query for repeatable Excel cleanups).

The statements are documented in the MySQL manual on SELECT ... INTO, and the driver in the PyMySQL documentation. To check a small extract as JSON, use the CSV to JSON converter.