Flask and SQLAlchemy


Now that we have a database ready to use, we need to make our database accessible to our web application framework Flask. To do so, we will be using SQLAlchemy which will not only connect to the database, but will also manage the python objects to make manipulating and using the the data easier.


Getting the Project Files

Previously we had set up a basic Flask directory structure. We are going to move those files to back them up, and get the completed project files.

First, lets change our directory name so we can put the new files there:
mv /var/www/app/app /var/www/app/app.bak

Now lets go to our app directory and use git to download the files we need:
cd /var/www/app
git clone https://github.com/tannercrook/Flask-LAMP
And then rename the directory we download: mv Flask-LAMP app

You can now enter the directory and find our Flask structure!



Build the Python Container

As we did with our first Flask structure, we need to build the virtual environment for Flask and install all of the dependencies. We can use the requirements.txt to install all of the dependencies.

First, let’s make sure we are in the correct location. Running pwd should return /var/www/app/app.

Now, we can create our container for Python:
python3 -m venv venv

Next, we will activate the container, and install the dependencies:
source venv/bin/activate
pip3 install -r requirements.txt

If you get an error installing the requirements, try installing wheel first.

pip3 install wheel



Now you should have your container and the proper packages!



Connect to Your Database

The final step is to connect our app to our database. The database should be running on the same server, so all we need to do is supply it with the proper username and password. Note that all paths will be relative to you being in /var/www/app/app

The first file we will edit is models/Connection.py:
nano models/Connection.py

In the file, you will see a line (line 6) that looks like the following:
engine = create_engine("postgresql+psycopg2://catalog_app:schoolrocks@localhost/course_catalog", implicit_returning=True)

You will need to edit the username and password to match your database credentials. In the case above, catalog_app is my username and schoolrocks is my password. So edit those two values to match your credentials. If your database name is different, you can edit where it says course_catalog.

The final file we will edit is app.py:
nano app.py

The line we edit here will be similar:
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://catalog_app:schoolrocks@localhost/course_catalog'

You will similar edit the same values to adjust the username and password for the database.

After saving, Flask should now be able to talk to your database.


SQLAlchemy is a package that will deeply connect our database and Python. We use sqlacodegen to generate Python objects (classes) for our various database objects so we can skip writing boilerplate code and custom classes to perform database actions. Take a look at the models/models.py file to see the generated objects. You can see how this is used in the various views/*.py files. You can see how sqlacodegen is used here: https://db.tannercrook.com/python-objects-from-database-for-sqlalchemy/


Seeing the Results

Well now everything is set. All we have left to do now is restart apache, and check it out!

sudo service apache2 restart

Now you can navigate to your server IP and view the website.



Run With It

This is just a very small glimpse into the whole process. I hope that from here, you will begin to explore on your own and discover software of your own.

Your app.bak directory will provide you with a blank slate for Flask that will allow you to repurpose it into something of your own creation. You can then use the example laid out to connect to your own database, and implement it.


I hope that you can take it, and run with it.

“Know ye not that they which run in a race run all, but one receiveth the prize? So run, that ye may obtain.” – 1 Corinthians 9:24



Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>