How do I store Decimal() values coming from SQL in CSV file with Python?

Question

I have a simple chatbot that generates SQL queries and uses them on a database. I want to store the output in a .csv file and then download that file.

This is usually possible when I take my output from SQL (a list of dicts), create a pd.Dataframe after evaluating that output, and finally download it as a csv file using st.download_button.

However, when the result is a Decimal() format from SQL, pd.Dataframe fails and both eval and literal_eval would not work on it (I got invalid object error using literal_eval).

I also tried to convert the data using Python's Decimal datatype. from decimal import Decimal But this recognized the output object as string and not a Decimal.

So I did some research and found that writerows would work with Decimal type data, but I am still not able to download the file.

out = db.run_no_throw(query, include_columns=True) 
#This is the output returned by the database. According to the [docs][1], this returns a string with the result.

print(out, type(out)) 
# Prints [{A: Decimal(1,2)}] and str (literal_eval(out), literal_eval(str(out)) and literal_eval('"' + out + '"') all gave an invalid object error here)

This is how I am currently trying to download the output data:

with open(filename, mode='w+', newline='') as file_to_output:
                writer = csv.writer(file_to_output, delimiter=",")
                writer.writerows(out)
                downloaded = st.download_button(
                    label="Download data as CSV",
                    data=file_to_output,
                    file_name="filename.csv"
                )

The above code creates a file locally with the expected data, but it prints 1 line per row in the .csv file, like so -

[

{

A

:

D

e

...

However, on the server, the file I download does not even consist of that data (It is a blank file). So the streamlit download button is not getting the data from the file, even though it says here that it should because it is one of str, bytes, or a file.

What am I missing here? I would really appreciate any help. Thanks!

EDIT - Running eval() on out variable gave the error "Could not recognize Decimal() object"

Answer 1

pandas .read_sql_query() method can be used to directly create a DataFrame from an SQL query. Then the DataFrame can be written to a CSV file using the .to_csv() method.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine("postgresql://scott:[email protected]/test")

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
"""
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')

df.to_csv("products.csv", header=True, index=False)
with open("products.csv", "r") as csv:
    print(csv.read())
"""
item,price
widget,2.9900
gadget,9.9900
"""