Shiny for Python: How to Work With MySQL and Postgres Databases
Most of the real-world data is stored in databases, so knowing how to access different database systems from Shiny is a mandatory skill. With the rise of <a href="https://appsilon.com/shiny-for-python-introduction/" target="_blank" rel="noopener">Shiny for Python</a>, databases and their access got a whole new meaning, as Shiny developers are no longer limited to R. That being said, Shiny developers must learn how to connect to databases from Shiny for Python. That's where this article comes in. We'll show you how to connect to two databases - MySQL and Postgres - running live on <a href="https://aws.amazon.com/rds/?trk=2fad3d88-146d-4d95-858e-c860297a48cb&sc_channel=ps&sc_campaign=acquisition&sc_medium=ACQ-P|PS-GO|Brand|Desktop|SU|Database|Solution|EEM|EN|Text|EU&s_kwcid=AL!4422!3!548915587593!e!!g!!amazon%20relational%20database&ef_id=Cj0KCQjwrs2XBhDjARIsAHVymmTZSZ9LYupE8I1JCWtgni-CIiDe8j08vY1syNIKGac-eeIfb7JnNDMaAllaEALw_wcB:G:s&s_kwcid=AL!4422!3!548915587593!e!!g!!amazon%20relational%20database" target="_blank" rel="nofollow noopener">AWS RDS</a>. By the end of the article, you'll have a fully-working Shiny for Python dashboard connected to these two. <blockquote>Interested in connecting R Shiny to the Postgres database? <a href="https://appsilon.com/r-shiny-postgres-database/" target="_blank" rel="noopener">We have you covered</a>.</blockquote> Table of contents: <ul><li><a href="#databases">How to Provision Databases on AWS for Py Shiny</a></li><li><a href="#connection">Establish Database Connections from Python</a></li><li><a href="#shiny">Shiny for Python - How to Connect to Databases</a></li><li><a href="#summary">Summary of Shiny for Python Databases</a></li></ul> <hr /> <h2 id="databases">How to Provision Databases on AWS for Py Shiny</h2> So, why AWS? Put simply, cloud databases are more popular and easier to set up, and AWS offers a generous free tier for MySQL and Postgres. If you decide to provision and run Oracle or SQL Server databases, you'll have to pay up to hundreds of dollars per month, so these won't be covered today. <blockquote>Completely new to SQL and databases? <a href="https://appsilon.com/introduction-to-sql/" target="_blank" rel="noopener">Here are 5 key concepts you must know</a>.</blockquote> We assume you already have an AWS account configured. The first step is to configure a <b>VPC security group</b>. You need to allow traffic from external sources to your database. Go to EC2 Security Groups and create a new one. Pay attention to <i>Inbounded rules</i>. You want to allow all traffic anywhere, as shown below: <img class="size-full wp-image-15121" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b7d2a8dfb1b34228f8e4bc_f108fd98_1-2.webp" alt="Configuring a VPC security group on AWS" width="2840" height="2298" /> Image 1 - Configuring a VPC security group on AWS Once done, go to RDS Management Console - Databases - and click on the "Create database" button. You'll have to create two <b>always-free</b> databases to follow along, one of them being MySQL and the other being Postgres. The configuration process is identical, just make sure to remember the assigned username and password. Also, <b>it's mandatory to allow public access and choose a security group</b>, as shown in the image below: <img class="wp-image-15123 size-full" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b7d2a90e70c8a308d340fc_96ad6d02_2-2.webp" alt="Creating a new database instance in AWS for a Shiny for Python app" width="2840" height="2298" /> Image 2 - Creating a new database instance For reference sake, <code>rds-python</code> is our security group that allows all traffic anywhere. Once both databases are created and provisioned, you'll see something similar in the database list: <img class="size-full wp-image-15125" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b7d2aac3f76fec62eda41d_73914b97_3-2.webp" alt="Database list on AWS" width="3416" height="2030" /> Image 3 - Database list Clicking on any database identifier will show you the connection endpoint and port: <img class="size-full wp-image-15127" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b7d2ab65eef25835e89977_0a7a5d67_4-1.webp" alt="Database connection endpoint and port" width="2998" height="2030" /> Image 4 - Database connection endpoint and port That's all you need to establish a database connection from Python - you have the username and password already. Let's see how in the following section. <h2 id="connection">Establish Database Connections from Python</h2> Python requires you to install a couple of libraries before you can connect to databases. The first two are used for MySQL, and the last one is for Postgres: <pre><code class="language-shell">pip install mysql mysql-connector psycopg2</code></pre> To test the database connection, we decided to create a <code>conns.py</code> file. It will be used to test the connection with both. Let's start with MySQL. Import the required library and use the <code>connect()</code> method to communicate with the database. Once the connection is established, we'll execute a simple query that prints the database version and the current user: <pre><code class="language-python">import mysql.connector <br>conn = mysql.connector.connect( host="<your-host>", user="<your-user>", passwd="<your-password>" ) <br>res = "" cursor = conn.cursor() cursor.execute("SELECT CONCAT(VERSION(), ' | ', CURRENT_USER());") res = cursor.fetchall() print(res[0]) <br>conn.close()</code></pre> Here are the results: <img class="size-full wp-image-15129" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b7d2ac0d6008396df1f764_d96faf7e_5-1.webp" alt="Connecting to a MySQL database for Python for Shiny" width="1884" height="1082" /> Image 5 - Connecting to a MySQL database Everything looks good, so let's continue with Postgres. The connection script looks nearly identical, we're just using a different library: PostgreSQL: <pre><code class="language-python">import psycopg2 <br>conn = psycopg2.connect( user="<your-user>", passwd="<your-password>" host="<your-host>", port="<your-port>", database="<your-database>" ) <br>cursor = conn.cursor() cursor.execute("SELECT datname || ' | ' || datid FROM pg_stat_activity WHERE state = 'active';") res = cursor.fetchall() print(res[0]) <br>conn.close()</code></pre> Let's see if the connection goes through: <img class="size-full wp-image-15131" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b7d2adebc881cc38312a30_a5d4c05e_6-1.webp" alt="Connecting to a Postgres database for Python for Shiny" width="1884" height="1082" /> Image 6 - Connecting to a Postgres database It does, so it's safe to assume we have everything needed to carry on. Up next, you'll learn how to work with Shiny for Python databases. <h2 id="shiny">Shiny for Python - How to Connect to Databases</h2> The dashboard you're about to see will render the <a href="https://gist.github.com/netj/8836201" target="_blank" rel="nofollow noopener">Iris dataset</a>. The link contains the data in CSV format, so transfer it to both databases before proceeding. We've decided to name the target variable <code>iris_species</code> in MySQL and <code>variety</code> in Postgres, just so it's easier to spot the difference. You don't have to do the same. Create an <code>app.py</code> file that will hold the entire Shiny application. Inside the file, we'll import all the required libraries and declare a helper function for connecting to the database - <code>connect_to_db()</code>. This function will accept one parameter - <code>provider</code> that can be either MySQL or Postgres. Feel free to add additional options if you've created another database. <blockquote>Don't have Shiny for Python installed? <a href="https://appsilon.com/shiny-for-python-introduction/" target="_blank" rel="noopener">Here's how to get started</a>.</blockquote> The code for this function will look familiar, as we've already discussed the database connection logic: <pre><code class="language-python">import pandas as pd import psycopg2 import mysql.connector from shiny import App, render, ui, reactive <br> def connect_to_db(provider: str): if provider == "MySQL": conn = mysql.connector.connect( host="<your-host>", user="<your-user>", passwd="<your-password>" ) elif provider == "PostgreSQL": conn = psycopg2.connect( user="<your-user>", passwd="<your-password>" host="<your-host>", port="<your-port>", database="<your-database>" ) else: raise Exception("Invalid DB Provider!") return conn</code></pre> Now onto the Shiny for Python app. The UI will be simple: <ul><li>Inputs: Only one input, allows the user to select a database to connect.</li><li>Outputs: One text output containing database details, and one table output rendering the Iris table.</li></ul> The Server is where things get tricky. We'll have two <b>reactive calculations</b>, one containing the database info and the other containing the Iris dataset. The <code>db_info()</code> reactive function should also feel familiar, as you've seen its logic in the previous section. The <code>data()</code> function establishes a database connection and runs a SQL statement. The result of the statement is returned in a Pandas DataFrame format. As for the rendering, things are simple - we have all the data prepared, so the only thing left to do is to call the corresponding reactive functions. Here's the remainder of the <code>app.py</code> script: <pre><code class="language-python">app_ui = ui.page_fluid( ui.h2("Shiny for Python Database Connections"), ui.input_select(id="select_db", label="Selected Database:", choices=["MySQL", "PostgreSQL"], selected="MySQL"), ui.hr(), ui.output_text(id="out_db_details"), ui.output_table(id="out_table") ) <br> def server(input, output, session): @reactive.Calc def db_info(): if input.select_db() == "MySQL": conn = connect_to_db(provider="MySQL") stmt = "SELECT CONCAT(VERSION(), ' | ', CURRENT_USER());" else: conn = connect_to_db(provider="PostgreSQL") stmt = "SELECT datname || ' | ' || datid FROM pg_stat_activity WHERE state = 'active';" cursor = conn.cursor() cursor.execute(stmt) res = cursor.fetchall() conn.close() return str(res[0]) <br> @reactive.Calc def data(): conn = connect_to_db(provider=input.select_db()) if input.select_db() == "MySQL": stmt = "SELECT * FROM db.iris" else: stmt = "SELECT * FROM iris" df = pd.read_sql(stmt, con=conn) return df <br> @output @render.text def out_db_details(): return f"Current database: {db_info()}" <br> @output @render.table def out_table(): return data() <br> app = App(app_ui, server)</code></pre> The only thing left to do is to run the Shiny for Python app. Open its parent folder in the Terminal and run the following: <pre><code class="language-shell">shiny run --reload app.py</code></pre> It will launch the Shiny app on port 8000. Here's what it looks like on our end: <img class="size-full wp-image-15133" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b2abb573b7c1f9f5f730e5_7.gif" alt="Shiny for Python dashboard demonstrating database connectivity - MySQL and PostgreSQL" width="788" height="728" /> Image 7 - Shiny for Python dashboard demonstrating database connectivity You can select either database from the dropdown menu and the outputs will update accordingly. <hr /> <h2 id="summary">Summary of Shiny for Python Databases</h2> You have to admit - connecting Python to databases is simple and straightforward. Also, provisioning these databases is almost effortless on AWS, assuming you know you have to create a security group. The procedure for connecting other database vendors to Shiny for Python would be near identical. Oracle uses Python's <code>cx-Oracle</code> package, and Microsoft uses <code>pyodbc</code>. We decided to leave them out because you can't run them for free. No one needs an extra cost, and these databases are extremely easy to forget about. <i>Did you have any difficulties connecting Python Shiny apps to remote databases?</i> Please let us know in the comment section below. Also, don't hesitate to continue the discussion on Twitter - <a href="https://twitter.com/appsilon" target="_blank" rel="noopener">@appsilon</a>. We'd love to hear your input. <blockquote>Shiny adoption is a growing in large companies. <a href="https://appsilon.com/how-to-start-a-career-as-an-r-shiny-developer/" target="_blank" rel="noopener">You can make a career out of it - Here's how</a>.</blockquote>
