Data from SQL table will not be passed over to pandas dataframe

Question

I have a Python project, where I want to read out data from a SQL table, pass this data to a pandas dataframe and afterwards analyse and plot the data.

The problem I have is, that some data will not be passed to the panday dataframe and I do no know why.

I have no clue how to solve this issue.

Here is my code:

# Get transaction data grouped by category
sql_query_trans_cat = pd.read_sql_query ('''
                               SELECT
                               category_in_out_id,
                               SUM (amount)
                               FROM Transactions
                               GROUP BY category_in_out_id
                               ''', conn)

print(f"------------->> DEBUG Output of sql_query_trans_cat <<---------------------\n{sql_query_trans_cat}")

# Create a dataframe for all the transactions to be stored
df_trans_all = pd.DataFrame(sql_query_trans_all, columns = ['id', 'hash', 'valutadate', 'amount', 'transaction_text_id',
                                        'account_id', 'asset_class_id', 'category_in_out_id', 'currency_id', 'int_or_ext_id', 'remarks'])

# Create a dataframe for the transactions grouped  by category
df_trans_cat = pd.DataFrame(sql_query_trans_cat, columns = ['amount', 'category_in_out_id'])

print(f"------------->> DEBUG Output of df_trans_cat <<---------------------\n{df_trans_cat}")
print(f"------------->> DEBUG Output of df_trans_cat Data Types <<---------------------\n{df_trans_cat.dtypes}")

Please see also this screenshot for more information:

enter image description here

I have checked if the datatype is correct and yes, the datatype in the pandas column is a float64 also i have defined the data in the SQL table as a float.

Answer 1

Thanks to ajsp for his help:

You are trying to put sql_query_trans_cat into a data frame, when it's already a data frame. pd.read_sql_query returns an actual data frame. Just work with that, or create a copy.

"