Storing Decimal Values from SQL in a CSV File with Python

To store Decimal() values from SQL in a CSV file with Python, use pandas.read_sql_query() with the coerce_float=False parameter to preserve Decimal types, then export with df.to_csv(). This avoids the common pitfalls of manual string parsing or direct csv.writer usage with Decimal objects.

The Full Answer

When you retrieve data from a SQL database, numeric columns defined as DECIMAL or NUMERIC types are often returned as Python Decimal objects. These objects represent fixed-point numbers with exact precision, which is important for financial or scientific data. However, writing them directly to a CSV file can be tricky because the csv module and pandas may not handle Decimal objects gracefully by default.

The core problem, as described in the Stack Overflow question, is that when you have a list of dictionaries containing Decimal values (like [{A: Decimal('1.2')}]), standard approaches like eval() or ast.literal_eval() fail because they cannot parse the Decimal constructor call from a string representation. Additionally, using csv.writer.writerows() on such a list may produce unexpected output, such as each character of the string representation being written as a separate cell.

Solution 1: Use pandas.read_sql_query() with coerce_float=False (Recommended)

The accepted solution, provided by Gord Thompson on Stack Overflow, leverages pandas and SQLAlchemy to create a DataFrame directly from the SQL query. This method preserves the Decimal type and allows for a clean export to CSV.

Step-by-step implementation:

1. Set up your database connection using SQLAlchemy. This example uses PostgreSQL, but the same approach works with any database supported by SQLAlchemy (MySQL, SQLite, SQL Server, etc.).

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine("postgresql://scott:tiger@192.168.0.199/test")

Replace the connection string with your own credentials and database URL. The format is typically dialect://username:password@host:port/database.

2. Write your SQL query. The example below uses a simple UNION ALL to produce sample data with Decimal columns.

sql = """\
SELECT 'widget' AS item, CAST(2.99 AS Decimal(18, 4)) AS price
UNION ALL
SELECT 'gadget' AS item, CAST(9.99 AS Decimal(18, 4)) AS price
"""

3. Execute the query and create a DataFrame using pd.read_sql_query(). The critical parameter is coerce_float=False. By default, pandas attempts to convert Decimal values to float, which can lose precision. Setting this to False keeps the values as Decimal objects.

df = pd.read_sql_query(sql, engine, coerce_float=False)
print(df)
"""
     item   price
0  widget  2.9900
1  gadget  9.9900
"""
print(repr(df.loc[0, "price"]))  # Decimal('2.9900')

4. Export the DataFrame to CSV using the .to_csv() method. This handles Decimal objects correctly, converting them to their string representation.

df.to_csv("products.csv", header=True, index=False)

5. Verify the output by reading the file back.

with open("products.csv", "r") as csv:
    print(csv.read())
"""
item,price
widget,2.9900
gadget,9.9900
"""

The CSV file contains the exact decimal values as strings, preserving precision. The index=False parameter ensures that the DataFrame index is not written as an extra column.

When to use this solution: This is the preferred approach when you have control over the database query and can use pandas. It is clean, reliable, and handles Decimal types natively.

Solution 2: Convert Decimal to String Before Writing (Alternative)

If you cannot use pandas or need to work with an existing list of dictionaries (as in the original question), you must convert Decimal objects to strings before writing to CSV. This approach requires manual handling of the data structure.

Step-by-step implementation:

1. Retrieve your data from the database. Assume you have a list of dictionaries like:

out = [{'A': Decimal('1.2')}, {'A': Decimal('3.4')}]

2. Convert Decimal values to strings by iterating over each dictionary and checking the type of each value.

from decimal import Decimal

converted_out = []
for row in out:
    converted_row = {}
    for key, value in row.items():
        if isinstance(value, Decimal):
            converted_row[key] = str(value)
        else:
            converted_row[key] = value
    converted_out.append(converted_row)

3. Write to CSV using csv.DictWriter or csv.writer. Using DictWriter is cleaner when you have dictionaries.

import csv

filename = "output.csv"
with open(filename, mode='w', newline='') as file_to_output:
    if converted_out:
        fieldnames = converted_out[0].keys()
        writer = csv.DictWriter(file_to_output, fieldnames=fieldnames)
        writer.writeheader()
        writer.writerows(converted_out)

When to use this solution: Use this when you already have the data as a list of dictionaries and cannot change the query method. It gives you full control over the conversion process.

Handling the Streamlit download_button Issue

The original question also involved a Streamlit st.download_button that was not working correctly. The problem was that the file object was being closed before the button could read it, or the file was opened in the wrong mode. Here is a corrected approach for Streamlit:

import streamlit as st
import csv
from decimal import Decimal

# Assume out is your list of dicts with Decimal values
converted_out = []
for row in out:
    converted_row = {}
    for key, value in row.items():
        if isinstance(value, Decimal):
            converted_row[key] = str(value)
        else:
            converted_row[key] = value
    converted_out.append(converted_row)

# Convert to CSV string in memory
import io
output = io.StringIO()
if converted_out:
    fieldnames = converted_out[0].keys()
    writer = csv.DictWriter(output, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(converted_out)
    csv_data = output.getvalue()
else:
    csv_data = ""

st.download_button(
    label="Download data as CSV",
    data=csv_data,
    file_name="filename.csv",
    mime="text/csv"
)

Using io.StringIO avoids writing to disk and ensures the data is available as a string for the download button. The mime parameter helps browsers handle the file correctly.