Connecting Flask to a MySQL Database
See my last article for the repository setup
Using SQLAlchemy (Python SQL Toolkit), I’m going to show how we can connect our Flask application to a MySQL database. In this article I’m not going to go over setting up MySQL on your computer, but there are a lot of great resources online to guide you through the process. This video helped me to get started (I am using macOS). Using pipenv we will need to first install the MySQL client: pipenv install mysqlclient.
Once the client is installed, we will want to create a database in MySQL that we can connect our application to. After you have logged into MySQL, we are going to create a new database.
For now we can go back to our code and configure app.py to connect our application to the database. At the root of the repository, create a .env file to house your MySQL credentials (also, you will want to add this to your .gitignore file if using version control). We will use python-dotenv and the load_dotenv() function to access these environment variables in the app.py file.
There are a few lines we need to add to app.py next. I’m going to use SQLAlchemy to connect to the database. First import flask-sqlalchemy, then make a db variable that is assigned to an instance of SQLAlchemy.
So there is a little bit more to unpack here. You’ll notice at the top we are importing os. This will help make it easier to access the variables in our .env file. Another package we are importing is dotenv, which gives us the load_dotenv() function. We will also assign our credentials to variables for our MySQL username, password, and database name.
We can interpolate those credential variables into this SQLAlchemy database URI, that will connect our application to the MySQL database. We are going to turn off the track modifications option as well for this walkthrough as we won’t be using the SQLAlchemy event system.
For this application I am going to create a User table, that is going to have columns for a users email & password. Eventually I’m going to implement Bcrypt to encrypt the password, but for now it’ll just be stored as a string. The user model will look like this:
This allows us to give the table a name of ‘users’. The email of a user will be a string, that must also be unique. The return value of the __repr__ function will be what is displayed if we pass an instance of the User class to the function.
If we run the command flask shell from the terminal, that will start up an interactive shell where we can connect to the database, create our users table, then create users and query them.
From the app.py file we are importing the database & user model, then creating the user table by using db.create_all(). With a database session we can add and commit new users to the MySQL database.
