Cannot obtain tables from an sqlite3 database

[kostasgalexiou]

Hi,

I am trying my app locally, with the aim of updating later on the deployed version of it.

I am using st.connection for connecting to a local sqlite3 database, and then trying to extract all tables name from the database. However, the only thing that I am getting is the list ['name']. Below you have the corresponding code:

command = "SELECT name FROM sqlite_master WHERE type='table';"

tables_sql = list(conn.query(command))
print(tables_sql)

My databases is supposed to contain 2 tables: almond and peach.

Libraries versions:

SQLAlchemy==1.4.0
mysqlclient==2.2.0
numpy==1.26.1
pandas==2.1.2
streamlit==1.28.0

Thanks in advance,
Kostas

[Shawn_Pereira]

Hi @kostasgalexiou,

I don’t use SQLAlchemy and st.connection, so you can have the plain vanilla code from me as under:

import sqlite3
import pandas as pd

application_path = "D:/Users..."  # your application path
dbf_name = "nocrf.db"             # your db name

dbcnn = sqlite3.connect(application_path + dbf_name)
dbcmd = "SELECT tbl_name FROM sqlite_master WHERE type='table' "
tables = pd.read_sql_query(dbcmd, dbcnn)
tables = tables['tbl_name'].tolist()
tables.sort()    # if you need to...

st.info(f"tables: {tables}")

dbcnn.close()

Cheers

[kostasgalexiou]

Hi @Shawn_Pereira ,

Thanks for your reply. I was using sqlite3 before and it was working fine. However, I moved to SQLAlchemy and st.connection because I want to use them for connecting to the local DB from the deployed version of my application. I don’t know if this can be done with sqlite3.

Best
Kostas