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"

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"

• python
• pandas
• sqlalchemy
• streamlit
• langchain

• You don't have decimal data. You have a string "[{A: Decimal(1,2)}]". There's no decimal data to read here. Looking at the docs, I don't know if this thing you're using is actually appropriate for querying numeric data in the first place. – user2357112 Commented Feb 2 at 20:10
• Have you tried using pandas .read_sql_query() to execute your SQL statement and create the DataFrame directly? pandas should be able to handle all of the type juggling for you, and you could then just use df.to_csv() to create the file. – Gord Thompson Commented Feb 2 at 20:57
• @user2357112 My bad, I should have mentioned - running eval() on the out variable gave an error "could not recognized Decimal(...) object" – Soumya Commented Feb 3 at 0:04
• @GordThompson while that required some changes to the original structure of the code, it worked! Thank you. Do you mind putting your comment in an answer so I can accept it? – Soumya Commented Feb 3 at 14:49

3 Answers 3

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

In python, you can directly use Pandas dataframe to handle Decimal objects. Also, I tried using json.loads() instead of eval() or literal_eval() as the data in your example seems to be JSON-like.

Next, try passing the data either as bytes or as a file-like object to use streamlit download.

Try this, it worked for me this way.

import pandas as pd
from decimal import Decimal
import csv
import streamlit as st
import json


db_output = '[{"A": "Decimal(1.2)"}]' #using this example


def parse_decimal(obj):
    if "Decimal" in obj: #checking and converting to decimal
        return Decimal(obj.replace("Decimal(", "").replace(")", ""))
    return obj


data = json.loads(db_output, parse_float=parse_decimal) #converting from json to python obj


df = pd.DataFrame(data)

# converting DataFrame to CSV (in-memory)
csv_data = df.to_csv(index=False).encode('utf-8')

# Streamlit download button
st.download_button(
    label="Downloading data as CSV",
    data=csv_data,
    file_name="data_inp.csv",
    mime='text/csv'
)