Which library’s syntax should I follow when I establish connection using streamlit 1.22? I mean I used syntax of sql.connector and I got the following error. Should I learn sql alchemy to use st.experimental_connection?
Thanks for your question, to allow the community to help troubleshoot this error, can you share the code to your app that reproduces this error.
Best regards,
Chanin
Hai @dataprofessor . Here it is…
import pandas as pd
import streamlit as st
# Establish a connection to the MySQL database
conn = st.experimental_connection('mysql', type = 'sql')
cursor = conn.cursor()
def get_dataframe(table_name):
query = f"SELECT * FROM {table_name}"
cursor.execute(query)
data = cursor.fetchall()
df = pd.DataFrame(data, columns=[i[0] for i in cursor.description])
return df
agg_trans_df = agg_user_df = map_trans_df = map_user_df = \
top_trans_dist_df = top_trans_pin_df = top_user_dist_df = top_user_pin_df = None
table_names = [
'agg_trans', 'agg_user', 'map_trans',
'map_user', 'top_trans_dist', 'top_trans_pin',
'top_user_dist', 'top_user_pin'
]
for table_name in table_names:
var_name = f"{table_name}_df"
globals()[var_name] = get_dataframe(table_name)
cursor.close()
conn.close()
While the above thing is the code I used… I have created secrets.toml as mentioned in the documentation for myql connection.
[connections.mysql]
dialect = "mysql"
host = "localhost"
port = 3306
database = "phonepe_pulse"
user = "root"
password = "xxxx"
Also this happened when I tried streamliit app on local host.
BTW, thanks for the instant reply @dataprofessor
Thanks for sharing the code. It seems that you’re reading in several tables iteratively in the app.
Typically when I encounter a problem, I’d like to simplify the problem, for example, by trying out a single element from a collection of elements.
Therefore, in this particular app, could you try reading in a single table from the database to see if that works.
Best regards,
Chanin
1 Like
I tried it just now in a new file @dataprofessor … But I got the same error… I am dropping the code snippet below. Kindly check it out.
import pandas as pd
import streamlit as st
# Establish a connection to the MySQL database
conn = st.experimental_connection('mysql', type = 'sql')
cursor = conn.cursor()
def get_dataframe(table_name):
query = f"SELECT * FROM {table_name}"
cursor.execute(query)
data = cursor.fetchall()
df = pd.DataFrame(data, columns=[i[0] for i in cursor.description])
return df
df = get_dataframe('agg_trans')
df
cursor.close()
conn.close()
I even tried removing the cursor.close() and conn.close() at the end as i have seen it somewhere that it could be the prob. But of no use. It gave me out the exact same error.
As you can see below, the problem lies exactly in the line 6 which is st.experimental_connection()
Thanks for the update. Could you try querying the table as described in the Docs (Connect Streamlit to MySQL - Streamlit Docs) by using conn.query()
df = conn.query('SELECT * from mytable;', ttl=600)
Hope this helps!
Best regards,
Chanin
I tried this as well @dataprofessor but was in vain. I even tried the same in notebook file(.ipynb)… It gave more in detail error in the name of CacheKeyNotFoundError . Look below
import streamlit as st
conn = st.experimental_connection('mysql', type = 'sql')
df = conn.query("SELECT * FROM agg_trans;", ttl = 600)
df
I can’t make this any simpler 
…The error is as follows,
At the end of fourth image you can see that it is ultimately the same error that I got before.
It seems your database name is mysql, could you try renaming your database to something else (perhaps my_db).
Also in your requirements.txt file, do you have the following dependencies specified:
SQLAlchemy
mysqlclient
Can you see if you can reproduce the example described in the release blog of st.experimental_connection (Introducing st.experimental_connection!).
Best regards,
Chanin
I have tried changing it @dataprofessor but was in vain again.
Regarding this, I am working with local server haven’t yet made it remote…
I have tried this as well but of no use… As you can see i spent more than enough time on this and I think I can’t spend more time on this now… So, I am leaving it here and moving on. If anybody could help, I would be pleased as it would help in future projects a lot…
Also this is a commendable step going forward by streamlit team. I would like to applaud the team for the work they put into this thing and looking forward for the shift from st.experimental_connection to st.connection
Sorry to hear that the error still persists, let me ask internally and get back to you again.
Best regards,
Chanin
1 Like
Hi @Nirmal_Kumar,
So sorry for the slow response. I was able to get an error using your setup, though a slightly different one. I switched user to username in secrets.toml, and was able to connect to a local mysql server using that syntax. Here is my setup:
[connections.mysql]
dialect = "mysql"
host = "localhost"
port = 3306
database = "mysql"
username = "root"
password = "password"
import streamlit as st
conn = st.experimental_connection("mysql", type="sql")
if st.button("Create table"):
with conn.session as s:
st.markdown(f"Note that `s` is a `{type(s)}`")
s.execute("CREATE TABLE IF NOT EXISTS pet_owners (person TEXT, pet TEXT);")
s.execute("DELETE FROM pet_owners;")
pet_owners = {"jerry": "fish", "barbara": "cat", "alex": "puppy"}
for k in pet_owners:
s.execute(
"INSERT INTO pet_owners (person, pet) VALUES (:owner, :pet);",
params=dict(owner=k, pet=pet_owners[k]),
)
s.commit()
df = conn.query("select * from pet_owners", ttl=600)
df
2 Likes
One helpful debugging tip might be to double check that the secrets are actually set up the way you think they are. Don’t deploy the app like this, but you can do something like:
st.write(st.secrets["connections"]["mysql"])
# OR even just
st.write(st.secrets)
That can be helpful to make sure you’ve put your secrets file in the right place, etc.
3 Likes
Thank you so much for both the solution as well as the tip @blackary. Sorry from my side for taking too long to respond. You guys are so cool and awesome…
1 Like