Pandas read sql creating invalid dataframe

🎈 Using Streamlit
,
1

Summary

I am using cx oracle to read a view and create a df from it. I see it visually as a table. However, something seems to e wrong with the type of dataframe because I cannot merge it or even print it as a dataframe within the code later. Even functions like df.head doesnot work.

Steps to reproduce

    df_excel=pd.read_excel('filename.xlsx',   index_col=0)
    conn = cx_Oracle.connect("", "", "prod")
    sql_string = f"SELECT a,b,c FROM view d where date = to_date('{ValDatestr}')"
    df = pd.read_sql(sql_string, conn)
    finaldf = pd.merge(df,df_excel, on="columnkey")


    print(st.table(df)) --- for testing
    dfb=st.table(df)--- for testing
    print(TD_df.types) --- for testing
    print(dfb.head(2)) --- for testing

If applicable, please provide the steps we should take to reproduce the error or specified behavior.

Expected behavior:
should give result of following:

  1. print(dfb.head(2))
  2. finaldf = pd.merge(df,df_excel, on="columnkey")

Explain what you expect to happen when you run the code above.

Actual behavior:

  1. StreamlitAPIException: head() is not a valid Streamlit command.
  2. [Pandas key error : _get_label_or_level_values raise KeyError(key)]
    Explain the undesired behavior or error you see when you run the code above.
    If you’re seeing an error message, share the full contents of the error message here.

Debug info

  • Streamlit version: (get it with $ streamlit version) – v1.27.2
  • Python version: (get it with $ python --version)–v3.8
  • Using Conda? PipEnv? PyEnv? Pex?–pyenv
  • OS version:
  • Browser version:

Requirements file

Using Conda? PipEnv? PyEnv? Pex? Share the contents of your requirements file here.
Not sure what a requirements file is? Check out this doc and add a requirements file to your app.

Links

  • Link to your GitHub repo:
  • Link to your deployed app:

Additional information

If needed, add any other context about the problem here.

2

Hi @prwz,

Thanks for posting!

If you have a healthy connection to the db, then try verifying the df by printing out df as such:

df_excel=pd.read_excel('filename.xlsx',   index_col=0)
conn = cx_Oracle.connect("", "", "prod")
sql_string = f"SELECT a,b,c FROM view d where date = to_date('{ValDatestr}')"
df = pd.read_sql(sql_string, conn)
print(type(df)) # this should print out <class 'pandas.core.frame.DataFrame'> if not, then you're not dealing with a pandas df 
finaldf = pd.merge(df,df_excel, on="columnkey")

st.table(df) --- for testing
st.table(df)--- for testing
print(TD_df.types) --- for testing
print(df.head(2)) --- for testing

Please feel free to share the link to your repo if this doesn’t resolve the issue.