The world’s leading publication for data science, AI, and ML professionals.

Here is the reason why SQLAlchemy is so popular.

A definitive guide to connecting a flask application to a postgresql database directly that will help you understand why SQLAlchemy is the…

A definitive guide to connecting a flask application to a postgresql database directly that will help you understand why SQLAlchemy is the preferred way of connecting python applications to relational databases

Photo by Caspar Camille Rubin on Unsplash
Photo by Caspar Camille Rubin on Unsplash

Introduction

There are two ways you can connect a python application to a relational Database: a low-level approach and a high-level one. The low-level approach involves installing and setting up the relational database management system on your local machine, and writing actual SQL commands to carry out database operations. The alternative approach is to use an object relational mapper (ORM for short). An ORM is a database abstraction layer that sits as an intermediary between you and the database engine. It allows you to define regular Python objects and methods and translates them into low-level SQL database instructions for you. SQLAlchemy is the ORM of choice for working with relational databases in python.

The reason why SQLAlchemy is so popular is because it is very simple to implement, helps you develop your code quicker and doesn’t require knowledge of SQL to get started. This is why almost all the programming tutorials and courses online teach the high-level approach. Most practicing software engineers also seem to prefer to use SQLAlchemy too.

This article is a tutorial on how to connect a simple flask application to a postgresql database without using SQLAlchemy. Yes, you read that correctly, so here me out: once you understand how to work with a database the low-level approach, you will not only have a much greater appreciation for SQLAlchemy, but also actually understand what is happening behind the scenes when you use it!

Our investigation will involve building a web app that takes in user input via a form and stores it in a postgresql database. Let’s get started!

Step 1: Project Setup

Create a project directory, navigate to it in terminal and then create a virtual environment for the project. Once you have activated the virtual environment, install flask and psycopg2. psycopg2 is the most popular PostgreSQL adaptor for Python. It allows you to access a postgresql database on your local machine directly from your python code. Make sure to write all the python packages you installed in the virtual environment to a requirements.txt file.

$ py -3 -m venv venv
$ venv/Scripts/activate
$ pip install flask
$ pip install psycopg2
$ pip freeze > requirements.txt

You can also go ahead and create an empty python script called app.py , a folder called templates and a blank html document called index.html inside the templates folder in your project directory. The structure of your project folder should look as follows:

|- app.py
|- requirements.txt
|- templates
   -index.html
|- env

Step 2: Create a database

You will need to install postgres on your local machine. You can download the right version for your operating system from https://www.postgresql.org/download/.

Once installation has completed, run the SQL Shell for postgres (psql) and enter into the database server. If you installed postgres with its default settings, you will be able to leave all the fields blank (just press enter each time so that the selector moves to the next field) and only have to enter the password you chose. You will know that you are in the database server when the SQL Shell terminal gets prepended with the database name (which by default should be the default postgresql database,postgres=#).

Screenshot of SQL Shell (psql) (Author's own).
Screenshot of SQL Shell (psql) (Author’s own).

You can use the default "postgres" database as your project’s database if you want, but I prefer to create my own. To create a database on the server, run the command shown below in the SQL shell. This command instructs the server to create a database named python_app. Make sure to not forget the semicolon at the end of the command otherwhise the command wont execute.

postgres=# CREATE DATABASE python_app;

The server will respond with a confirmation message that it created a database. To view all the databases on the server run the command shown below. You should see python_app listed as one of the databases stored on the server.

postgres=#l

That is all that we will be doing in the SQL Shell. The rest of the database operations (creating tables and adding entries, etc) will be carried out in our python application.

Step 3: Connect to the database from your python application and create a users table

In your app.py file, import the psycopg2 module. You also want to import the Error object from psycopg2 so that you can handle any database errors directly in your python code.

The psycopg2 module exposes a connect() method that takes in as arguments key-value pairs of the parameters you use to log into a postgres database server in the SQL Shell: host, database, port, user, password. The output of the connect() method is a connection object which allows you to create a connection to a postgres database. Once a connection has been established you need some way of telling the database what SQL commands to execute. That’s where cursor objects comes in. The connection object exposes a cursor() method that can be used to create a cursor object whose job is to execute any SQL commands you want the database to carry out.

The workflow for connecting to a postgres database in python code is as follows:

  1. Create a connection object
  2. Use the connection object to create a cursor object
  3. Use the cursor object to execute SQL commands
  4. Close the cursor object and database connection

Since this workflow involves connections to external systems, a good practice would be to is to implement this workflow in a try-catch-finally block so that you can handle any exceptions that arise either due to connectivity issues or bugs in your code.

Executing SQL commands on the database is a two-step process: you first pass a query string to the cursor object via its execute() method. You then have to commit the SQL command to the database using the connection object’s commit() method to get the change to reflect on the database. You can think of cursor.execute() as git add x and connection.commit() as git push.

I find it easier to first define the query string as a variable and then pass it into the execute method as an argument, but you can just go ahead and write the full query as an argument if that’s what you prefer.

The code for creating a users table in the database is shown below. To tell the databse to create a table you use the command keywords "CREATE TABLE" followed by the name of the table, and then in paranthesis the names of the columns that make up the table. Each column has keywords that define its expected data type and constraints.

If your code runs successfully, the python_app database on the postgres server should now contain a users table. To confirm this, you can open up the SQL shell and access the python_app database and then run the dt command to view all the tables in that database. If you want to see all the columns you defined and their contraints you can run the d users command. Also, if for some reason you want to delete the table from the database you can run the DROP TABLE users; command. Lastly, if you find yourself in the default postgres database you can easily switch to the python_app database by running the `c python_app“command.

Screenshot of SQL Shell confirming that the users tabe has been created (Author's own).
Screenshot of SQL Shell confirming that the users tabe has been created (Author’s own).

Step 4: Create a simple flask application that renders a registration web form

Begin by instantiating a flask application object. Next, define a route and view function that will handle requests for ‘/’, the url for the webform. This view function should render the form when it recieves a GET request and process the form when it recieves a POST request when the user submits the form. The processing part has been left blank for now as we will define it in the next step.

your app.py file code should look as follows:

Your index.html file should contain the following code:

Running the command $ python app.py in terminal opens up the flask development server in debug mode. Accessing the url [http://127.0.0.1:5000/](http://127.0.0.1:5000/) in your browser should bring up the registration form.

Screenshot of the registration form (Author's own)
Screenshot of the registration form (Author’s own)

We now have a form that renders in the browser and allows a user to submit input to a server, but nothing happens after the user presses the submit button. Let’s change that.

Step 5: Store the user input in the database

First define a function called "add_new_user" that takes in a tuple of the user data (name, email, hashed password) and writes it to the database.

Then modify the view function so that when a "POST" request is recieved it calls the "add_new_user" function so that the data can be saved to the database.

When you fill out the registration form and press submit, that information should be written to the postgres database on your local machine! To confirm this, open up the SQL shell and access the python_app database. To view the data in a given database table you need to run a query. Since we want to view all the data stored in the users table, we will run SELECT * FROM users;.

You should see the details you entered in the form captured as a new entry in the users table.

Screenshot of new entry in the database (Author's own)
Screenshot of new entry in the database (Author’s own)

Full Code

For your convinience the full app.py code is shown below:

Comparison with SQLAlchemy

This is what the same application would look like using Sqlalchemy:

Conclusion

And there you have it. A simple flask application that connects directly with a postgres database to store user information. As you can see, it is quite a tedious process you have to go through to write to a database using this method. Although very doable, it isnt really very pythonic.

SQLAlchemy allows you to continue writing code in the manner that you are used to even when working with databases. You define a class which represents a particular entity type and directly maps to a table in the database. All the class attributes you define are mapped to the columns in the table, and then any instance of the class is stored in the database as a new entry in the table. Using SQLAlchemy results in simpler, cleaner code that is quicker to write up. Now you know why SQLAlchemy is the defacto way of working with databases in python!


Related Articles