Lo-Fi Python

May 25, 2020

Integrating MySQL with Flask, pandas and pythonanywhere

Sometimes a spark comes from seemingly nowhere. That's when you reach for your tools and create. After a series of successful experiments, I decided this stack might be my quickest, best shot to get a functional website up and running in Python. I was pleasantly surprised to make rapid progress over the span of a quarantine weekend. Here are the steps to create a MySQL backed website with Flask.

Hosting With pythonanywhere

pythonanywhere is a web hosting service like GoDaddy. If you host your app with them, MySQL is the default database. Postgres integration is available at higher price tiers.

To get your Flask app's database up and running you need to:

  1. Create your database (see the "Databases" tab in pythonanywhere)
  2. Use the mysql terminal to create your tables
  3. Use the mysql.connector API to connect to your table and execute SQL from your Flask app.

Essential MySQL Terminal Commands

Show MySQL Version

1
SELECT VERSION();

List tables in a database

1
SHOW TABLES;

Show All MySQL Variable Values

1
SHOW VARIABLES;

Creating a Table

1
CREATE TABLE Marijuana (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(350), date VARCHAR(350));

Create a Table with a JSON column

1
2
3
4
5
6
CREATE TABLE Marijuana (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `store` varchar(200) NOT NULL,
  `details` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Add New Column and specify column to insert AFTER

1
2
ALTER TABLE Marijuana
ADD COLUMN date VARCHAR(100) AFTER other_column;

Alter Datatype of a Column

1
ALTER TABLE Marijuana MODIFY id INT AUTO_INCREMENT PRIMARY KEY;

Describe a Table

1
DESCRIBE Marijuana;

View All Records in a Table

1
SELECT * from Marijuana;

Using LIKE in MySQL

Select 10 Newest Records

1
SELECT * from Marijuana ORDER BY id DESC LIMIT 10;

"Explaining" A Query

1
EXPLAIN SELECT * from Marijuana;

Using "ANALYZE TABLE" tooptimize themis periodically recommended by MySQL:

1
ANALYZE TABLE Marijuana;
mysql commands in shell

Installing Libraries in PythonAnywhere

You can use pip to install python libraries within the PythonAnywhere bash terminal. Go to the consoles tab and start a new bash terminal. Then to install a library, such as pandas:

python -m pip3.8 install --user pandas

Flask app with mysql.connector API, SQL and pandas

A Flask app making a mysql database connection with pandas:

  1. Creating an error log with logging.
  2. Connecting to a mysql database hosted through Flask and pythonanywhere
  3. Then reading a table to a pandas dataframe
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
import mysql.connector
from flask import Flask
import pandas as pd
from datetime import date
import logging
import sys

app = Flask(__name__)
logging.basicConfig(stream=sys.stderr, level=logging.DEBUG)


@app.route("/")
def hello_world():
    """Call database and return data from df. Then display homepage."""
    try:
        email_df = get_database_table_as_dataframe()
        print(email_df.shape)
        html_page = render_homepage()
        return html_page
    except:
        logging.exception("Failed to connect to database.")


def render_homepage():
    """Note: you should use Flask's render_template to render HTML files.
    However, for example you can make a quick f-string HTML page that works in this code.
    """
    html_page = f"""<html><head><link rel='stylesheet' href="/static/styles/some_file.css"><link rel="shortcut icon" type="image/x-icon" href="static/favicon.ico">
                    <Title>Dispensary Alerts</Title></head>
                    <body><h2></h2>
                    <p>Get alerts for your dope.</p><br>
                    <h6><b>Sign Up</b></h6><br>
                    <div class="form">
                    <form action="/add_signup_to_db" method="post" style="width:420px;text-align:center;display:block;" >
                    <input type="text" name="Signup Form">
                    <input type="submit" value="Submit">
                    </form></div><br><br>
                    <p><b>Current Time:</b>
                    {str(date.today())} </p></body></html>"""
    return html_page


def get_database_table_as_dataframe():
    """Connect to a table named 'Emails'. Returns pandas dataframe."""
    try:
        connection = mysql.connector.connect(
            host="username.mysql.pythonanywhere-services.com",
            db="username$DatabaseName",
            user="username",
            password="password",
        )

        email_df = pd.read_sql(sql="""SELECT * FROM Emails""", con=connection)
        logging.info(email_df.head())
        return email_df
    except:
        logging.exception("Failed to fetch dataframe from DB.")
        return "Oops!"


@app.route("/add_signup_to_db", methods=["GET", "POST"])
def add_signup_to_db(email, date):
    """Pass data as SQL parameters with mysql."""
    try:
        connection = mysql.connector.connect(
            host="username.mysql.pythonanywhere-services.com",
            db="username$DatabaseName",
            user="username",
            password="password",
        )
        cursor = connection.cursor()
        sql = """INSERT INTO Emails (message, date) VALUES (%s, %s) """
        record_tuple = (email, date)
        cursor.execute(sql, record_tuple)
        connection.commit()
    except mysql.connector.Error as error:
        logging.info("Failed to insert into MySQL table {}".format(error))
    except:
        logging.exception("Error inserting records to DB.")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
        return "MySQL connection is closed"

Iterative Development

Below: making my website look less like a "my first HTML" website, experimenting with my app's message name and adding a sign-up form connected to the database.
Screenshot_20200606-132252 (1)

Note: if you see this error when making a request in pythonanywhere:

OSError: Tunnel connection failed: 403 Forbidden

It's likely because you are "whitelisted" on the free plan. Upgrading to the $5/month plan will fix it!

Scoping The Full Stack

I'm really enjoying this web development stack. Here are all of the tools and library choices for this website:

Finding Your Flask Groove

Flask is a little scary at first, but reasonable once you get a grasp of the basic syntax. Using the logging module to establish access, error and server log feeds was a big step to finding my Python traceback fixing groove. It's a work in progress.

Recapping My Python Web Development and Database Experiences

I previously created a website with web2py, another Python web framework like Flask and Django. I think it was a decent choice for me at that point in my Python journey. Back then, I connected a MongoDB back-end to web2py. I randomly picked Mongo out of the DB hat and it worked well enough.

My Python Web Development and Database Tools

App #1 web2py + MongoDB

App #2 Flask + MySQL

Future App? py4web + pyDAL + PostgreSQL

Future App? tornado + streamlit (or) Flask + Dash (+ SQLite)

Of these two diverse Python stacks, I favor MySQL and Flask. But I learned a lot from watching web2py's tutorial videos and it's less intimidating for beginners. And I barely scratched the surface of web2py's "pure Python" pyDAL (Database Abstraction Layer), which seems pretty dope.

web2py's creator has a new framework in progress called py4web. It has the same DAL and inherits many other web2py qualities. Definitely looking forward to exploring the DAL on my first py4web website. I'll likely use it to connect to PostgreSQL or SQLite. Maybe I'll install pyDAL with pip in the meantime.

Final Thoughts

Both of my websites are hosted with pythonanywhere, which gives you a text editor and bash terminal to run your scripts in a shell environment. I'm so pleased with all of these tools. They fit together smoothly and made creating my website a fun experience.

posted at 21:48  ·   ·  flask  mysql  py4web  pyDAL  python