Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

June 3, 2018
Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

“Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL” covers the entire process of connecting our Flask app to the database we created and configured in the last tutorial. Screenshots of every step along the way are included for reference. This tutorial assumes that your Flask app and database are configured similar to the previous tutorials. If this is not the case, please refer to those tutorials before attempting this one. Flask-SQLAlchemy is a toolkit for Python web applications and an Object Relational Mapper that allows web developers to harness all of the features of MySQL in their web applications. SQLAlchemy is fast, efficient, and high-performing, allowing access to databases and enterprise-level persistence patterns.

1. Before diving in, we have to launch VSCode and open our Flask app. Go ahead and launch VSCode and open the Flask project. The result should look like this:

Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

2. Next, let’s open the VSCode integrated terminal by selecting “View” on the application ribbon, then selecting “Integrated Terminal” from the dropdown.

Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

3. The terminal should open. If it is open, it will look like this:

Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

4. We will have to install SQLAlchemy and PyMySQL before we can use them, but first let’s activate our virtual environment so the packages we install will be easier to manage. Enter the following command into the terminal and press Enter on your keyboard:

source activate flask-env
Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

5. The prompt in your integrated terminal should like like the prompt in this photo:

Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

6. Now that our virtual environment is activated, we can install the packages that we will need to connect our Flask app to our database. In your terminal, type the following command and press Enter on your keyboard:

pip install flask-sqlalchemy

Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

7. You should receive a success message like the one shown in the following image:

Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

8. Next, we have to install PyMySQL. PyMySQL will be our database connector and we can install it by typing the following command into the integrated terminal and pressing Enter on the keyboard:

pip install pymysql

Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

9. Again, you should receive a success message like the one in this photo:

Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

10. We are now finished installing the packages necessary to connect our Flask app to our database. The next step is to import SQLAlchemy into our Flask app so that we can use it. If your main.py file is not currently open in VSCode’s editor, click on it to open it and add the following code to the top of your main.py file:

from flask_sqlalchemy import SQLAlchemy

Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

11. Now that SQLAlchemy is imported into our app, we can start our app by typing the following into the integrated terminal and pressing Enter:

python main.py

Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

12. The output of the previous command in the terminal should look like this:

Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

13. Next we will configure our Flask app’s connection to the database that we created in the previous tutorial. This configuration is called the Database URI and includes all of the necessary information in URL format to connect to our database. Insert the following code into the top of your main.py file, replacing the text in curly braces with your actual information:

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://{DB_USERNAME}:{DB_PASSWORD}@localhost:3306/{DB_NAME}'

Save your project. Your Flask app should now look similar to this:

Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

14. We will want SQLAlchemy debugging and status output so we will add the following to our Flask app to enable it:

app.config[‘SQLALCHEMY_ECHO’] = True

Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

15. To actually use SQLAlchemy with our Flask app, we have to initialize SQLAlchemy as our db object by passing our app into it. Add the following code to your Flask app to accomplish this:

db = SQLAlchemy(app)

Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

16. Since we will eventually be using forms with our Flask app, let’s create a secret key to thwart Cross-Site Request Forgery attacks. Add the following code to your flask app, using a good, secret password:

app.secret_key = ‘shhhh...iAmASecret!’

Connect a Flask App to a MySQL Database with SQLAlchemy and PyMySQL

17. Congratulations! Our configurations are now complete and our Flask application is connected to our database.

Continue to Part 6