how to connect my streamlit application with MySQL database?-CodePudding

So I am trying to use my (continously updating) database on MySQL with some visualizations which I want to put into my Streamlit app. In other words, I want to use the data from MySQL database in my Streamlit application.

For this purpose I consulted the official streamlit documentation here.

The problem here is that the tutorial tells me to create a file like this: .streamlit/secrets.toml and fill it with the following information (copy-pasting the syntax):

[
mysql
]
host = "localhost"
port = 3306
database = "xxx"
user = "xxx"
password = "xxx"

Everything was going good up until now but when I paste my secret.toml info in the SECRET MANAGEMENT widget (it is prompted when I am creating a new app in Streamlit cloud) it gives me a syntax error.

Invalid format: please enter valid TOML.

Up untill this point I was going by the book(tutorial). Now to go over this I tried using only the variable definitions like following (since I am not aware of the .toml syntax):

db_user = "root"
db_name = "dbname"
db_password = "123abc"

Am I doing this right? Or am I missing something obvious?

With all of that aside, I also need to know how to call dependencies on stream cloud for my app. For example, I need mysql-connector-python module but I don't see any console with which I can do that

NOTE: This is my first time deploying an app on the cloud

CodePudding user response：

[
mysql
]

It should be [mysql] in one line

In your GitHub repo, add requirements.txt file with your dependencies.

streamlit cloud will install those packages for your app.

CodePudding user response：

I want to notify another way we can use a database within a Streamlit App rather than using the conventional method.

We can refer to this Medium.com article here.

It explains a way in which we can use Pandas Library to load a database and it also updates in real-time. By using this knowledge, connecting to a database becomes a "Python" problem, not a "streamlit" problem.

Assuming we are using MySQL

We can, according to the official tutorial for MySQL Database, create a .streamlit/secrets.toml file in which we will store our information(related to our database) as below:

# .streamlit/secrets.toml
[
mysql
]
host = "localhost"
port = 3306
database = "xxx"
user = "xxx"
password = "xxx"

Also install mysql-connector-python for python and import it on your application file. you will also need Pandas and toml Ofcourse:

pip install mysql-connector-python pandas toml

Here is what each of them do: | Library | It's use | | -------- | -------------- | | mysql-connector-python | to connect to our database | | pandas | to read and convert our database table into a Dataframe | |toml| to read details from secrets.toml file |

STEP 1

We read details from secrets.toml

# Reading data
toml_data = toml.load("secrets.toml")
# saving each credential into a variable
HOST_NAME = toml_data['mysql']['host']
DATABASE = toml_data['mysql']['database']
PASSWORD = toml_data['mysql']['password']
USER = toml_data['mysql']['user']
PORT = toml_data['mysql']['port']

STEP 2

Connecting to our Database:

# Using the variables we read from secrets.toml
mydb = connection.connect(host=HOST_NAME, database=DATABASE, user=USER, passwd=PASSWORD, use_pure=True)

STEP 3

Making queries from our database:

query = pd.read_sql('SELECT * FROM mytable;' , mydb)

The query variable is now a display-able table in streamlit or Jupyter notebooks

Likewise, we can make any MySQL query(syntax applied) we want from our database.

This information is based on my own experience.