Exporting a Query to Excel From PostgreSQL With Python

The first export from PostgreSQL to Excel usually takes ten minutes and looks fine. The second reveals what the first hid: a timestamp column makes the writer raise an exception, the money column is text, and the order numbers lost their zeros. These are the same three problems every time, and each has a short fix. This post exports one query, step by step, and applies each fix at the point where it belongs. It is a spoke of sending database results to Excel, which explains the thinking behind the choices.

The query and the connection

Install the pieces:

pip install pandas sqlalchemy "psycopg[binary]" xlsxwriter

Use a SQLAlchemy engine to read into pandas. Since pandas 2, passing a plain psycopg connection to read_sql produces a warning that only SQLAlchemy connectables (and sqlite3) are tested, and the engine avoids it:

import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine("postgresql+psycopg://reporter@localhost/app")

sql = text("""
    SELECT o.order_no,
           c.name        AS customer,
           o.amount_eur  AS amount,
           o.placed_at
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
    WHERE o.placed_at >= :start AND o.placed_at < :end
    ORDER BY o.placed_at, o.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
placed_at       datetime64[ns, UTC]
dtype: object

Read the dtypes before writing anything. amount is object because PostgreSQL numeric arrives as Python Decimal, and placed_at is timezone-aware because the column is timestamptz. Both need handling. Query parameters go through params, never through string formatting, which also protects against injection if the dates come from a form. The ORDER BY is deliberate: a spreadsheet inherits the row order, and a result without an order can change between runs.

Fix the types

Excel stores no time zone. A date-time cell is a number of days; nothing in the file says whether it was UTC or local. pandas refuses to write a timezone-aware column and raises a ValueError saying that Excel does not support datetimes with timezones. Convert to the zone your readers live in, then drop the zone information:

df["placed_at"] = df["placed_at"].dt.tz_convert("Europe/Oslo").dt.tz_localize(None)

tz_convert shifts the instants to Oslo local time, and tz_localize(None) removes the zone marker, leaving a naive timestamp that shows the Oslo wall clock time. State the zone in the header or a note, since the file no longer says it. Converting to UTC instead is a good choice when the readers are systems or span several countries, and again the note matters.

Next, the numbers and the identifiers:

df["amount"] = df["amount"].astype(float)
df["order_no"] = df["order_no"].astype(str)

Decimal to float is fine for a report, because Excel stores doubles anyway. If the database holds more precision than a double can represent (more than 15 digits), or the amounts must reconcile to the cent after summing, round in SQL to the precision you need and understand that the workbook will hold floats (Excel number precision and the 15 digit limit). The order number is already a string here, since the column is text; the astype(str) guards against an integer id that would otherwise become a number cell. Apply it to every column you would never add up.

Write the workbook

with pd.ExcelWriter("orders_2024-03.xlsx", engine="xlsxwriter", datetime_format="yyyy-mm-dd hh:mm") 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:A", 14)
    ws.set_column("B:B", 30)
    ws.set_column("C:C", 12, money)
    ws.set_column("D:D", 18)

The datetime_format argument gives date-time cells a readable format, which is the cure for dates showing as numbers (why an exported date shows as a number). Setting index=False keeps pandas from writing its row index as an extra first column. The formatting calls freeze the header row, add filters and set widths and the money format; the details of each are in freezing panes, filters and column widths from code. The file name carries the period, which keeps scheduled runs from overwriting each other.

For the first rows of the March data, the cells would read: A2 text 0007, B2 text Acme AS, C2 number 19.99 shown as 19.99, D2 a date-time shown as 2024-03-01 09:15. Open the file and check that the Amount column has a sum in the status bar when selected, which proves the cells are numbers.

Big results: a server-side cursor

read_sql loads the whole result into memory. For hundreds of thousands of rows that is slow and heavy, and a plain psycopg cursor also fetches everything client-side by default. A named cursor, which psycopg calls a server-side cursor, keeps the result on the server and fetches in batches:

import psycopg
import xlsxwriter

with psycopg.connect("dbname=app user=reporter") as conn:
    with conn.cursor(name="export_orders") as cur:
        cur.execute("SELECT order_no, amount_eur, 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"])
        date_fmt = wb.add_format({"num_format": "yyyy-mm-dd hh:mm"})
        r = 1
        while rows := cur.fetchmany(5000):
            for order_no, amount, placed in rows:
                ws.write_string(r, 0, str(order_no))
                ws.write_number(r, 1, float(amount))
                ws.write_datetime(r, 2, placed.astimezone().replace(tzinfo=None), date_fmt)
                r += 1
        wb.close()

The constant_memory option makes XlsxWriter write each row to disk as soon as it is complete, so memory use stays flat, with the rule that rows must be written in order and not revisited. The loop fetches 5,000 rows at a time. write_string guarantees a text cell, which also defuses a value that starts with an equals sign (sending database results to Excel properly every time covers formula injection). The astimezone() call converts to the machine's local zone; give it an explicit zone object instead if the report must not depend on where the script runs. Remember that a named cursor lives inside a transaction, so keep the with blocks around the whole read.

The worksheet limit still applies: stop or split at 1,048,575 data rows (Excel row and column limits).

Choose between Excel and CSV on the database side

If the reader needs plain data, PostgreSQL can write CSV directly with COPY, with no Python in the loop:

psql "dbname=app" -c "\copy (SELECT order_no, amount_eur, placed_at FROM orders ORDER BY placed_at) TO 'orders.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8')"

\copy runs on the client and writes the file where you run psql. This is the fastest path for large exports, and it is right for programs. For people who will open the file in Excel, an XLSX written as above is safer, because Excel's CSV import re-guesses types (Excel versus CSV for sharing data with other systems).

Connection and permissions

Run the export under a role that can only read the tables the report needs. Keep the password out of the script by reading it from the environment or a .pgpass file, which psycopg and SQLAlchemy both honor. Do not build SQL with f-strings from user input. If the report is scheduled, the script's scheduler account should own the file location and nothing else (emailing a scheduled Excel report from a database).

A read-only role also protects against a bug in the report that would modify data. Set default_transaction_read_only on the role, or open the connection with options="-c default_transaction_read_only=on".

When exporting a PostgreSQL query to Excel with Python is the wrong approach

You only need CSV for a program. Use \copy, which is faster and needs no Python.

The result is enormous. Past a million rows, Excel cannot hold it. Export Parquet or CSV, or deliver an aggregate.

The data must be live. An Excel file is a snapshot. Offer a view in a BI tool or a direct database connection for readers who need current numbers.

The recipients need to refresh the data themselves. A Power Query connection inside Excel can query PostgreSQL on demand (connecting Power Query to a PostgreSQL database).

The writing options are documented in the pandas to_excel reference and the streaming mode in the XlsxWriter page on memory and performance. For checking a small extract, the CSV to JSON converter is handy.