Updating Database Through Streamlit + Postgresql

[Explorer]

I am trying to update a table through streamlit. I am implementing this locally.

Below is the main file:

import streamlit as st

# Initialize connection.
conn = st.connection("postgresql", type="sql")
person = 124
new = 12345558
try:
    conn.query(f"UPDATE user_table SET password = '{new}' WHERE person='{person}'")

    conn.commit()
except Exception as e:
    print(e)
finally:
    st.write("Yessss!!")

Below is secrets.toml file:

[connections.postgresql]
dialect = "postgresql"
host = "localhost"
port = "5432"
database = "main"
username = "postgres"
password = "1234"

Below is the error:

This result object does not return rows. It has been closed automatically.

It is also not updating in the database.

Here are the versions in my environment:

Python: 3.11
streamlit: 1.30.0
sqlalchemy: 2.0.27

[Guna_Sekhar_Venkata]

Hi @Explorer
I think you need to use conn.close() after commuting. Hope it works.
Happy Streamlit-ing

[Explorer]

Unfortunately, this action did not provide the necessary assistance. The connection is closing before the query executes, resulting in the values not being updated in the database.

[Guna_Sekhar_Venkata]

I mean write it after commiting.

[Explorer]

Yes, I had implemented it post commit. The issue persists.

[Guna_Sekhar_Venkata]

Is the same issue?

[Explorer]

yes

[Guna_Sekhar_Venkata]

Hi @Explorer . Remove the finally block and in the except block use st.write(e) instead of print(e). Also, add st.write(‘yes’) after conn.close(). Once try this

[Explorer]

This is the error I am getting now

There must a version issue here.

[Guna_Sekhar_Venkata]

Hi @Explorer . Have u tried with sqlite3 code structure?

[Explorer]

No, I haven’t.

[Explorer]

This issue also comes while performing “INSERT” statements.

[IndigoJay]

@Explorer

sqlalchemy

If you are open to try a different method; Psycopg2 library works well for connecting Streamlit to Postgres. Basic module usage — Psycopg 2.9.9 documentation

As a debugging step for your current code: copy and paste your SQL into the postgres console, and test if the INSERT works there.

[CHerSun]

st.connection is a means to fetch data as easily, as possible. It is not meant currently to run arbitrary queries, at least that’s my understanding from the docs on streamlit site.

If you want to run arbitrary queries - either use low-level library, like psycopg2 (psycopg2-binary) for Postgres, or a higher-level ORM, like sqlalchemy.

[Goyo]

From the docs:

SQLConnection provides the query() convenience method, which can be used to run simple read-only queries with both caching and simple error handling/retries. More complex DB interactions can be performed by using the .session property to receive a regular SQLAlchemy Session.