Connect to a database

Using Streamlit
1

Hi,

Is it possible to connect to a database and query from it and visualise the data from it.

Thanks & Best Regards

Michael

2 Likes
2

Hi, Yes. It is possible. I was able to do it with SQL Server database.

3

Thanks a lot for your help.
Thanks & Best Regards
Michael

4

Is it possible to share a code snippet… How did you handle refresh when the database is updated with new data? I am very new to streamlit, and will appreciate a little help. Thanks

1 Like
5

I created a class to handle my mySQL queries. It is just standard Python code for connecting to the database.

As for updates, if the database is being updated from an outside source, you will need to find a way to notify Streamlit of the change. I do my updates inside Streamlit - so I don’t have that problem.

import streamlit as st
import mysql.connector
from mysql.connector import errorcode

config = {
‘user’: ‘username’,
‘password’:‘password’,
‘host’: ‘localhost’,
‘database’:‘database’,
‘raise_on_warnings’: True
}

class mySQL:
def init(self, st):
self.st = st
try:
self.cnx = mysql.connector.connect(**config)
self.cursor = self.cnx.cursor()

    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            self.st.text("Something is wrong with your user name or password")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            self.st.text("Database does not exist")
        else:
            self.st.text('Unknown error')
    
def mysql_select(self, sql) :
    try :
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        if not result :
            return False, "Record not found"
        else :
            return True, result
        
    except mysql.connector.Error as err:
        return False, str(err.errno) + " : " + sql
2 Likes
6

thank you so very much.

7

Hi,
Based on this class, how do I execute a query ?
Thanks.

8

@Michael_Schroter,

you might also want to specify the database you want to use and whether it should be a local
one or hosted with a cloud provider(?).

In case of the latter, I highly recommend to check out the blogpost written by @AustinC. Streamlit ❤️ Firestore

@blob123, I guess you create a new mySQL class object of and then call the mysql_select function:

query_to_execute = """select * from table"""
mysql = mySQL(st)
mysql. mysql_select(query_to_execute)

Best regards
Chris

1 Like
9

Many thanks Chris.

I get this error message though:

AttributeError: ‘mySQL’ object has no attribute ‘connector’

10

Not sure why exactly that happend. Did you do the imports?

11

I did.

12

I really don’t manage to run this code.

Does anyone else have an experience connecting to mySQL, potentially with a RDS instance in the backend ?

13

The only thing I can think of is mysql-connector-python is not installed.

14

Thanks for your reply.

mysql-connector-python was well installed…