Sending data from a Flask app to PostgreSQL database.

Managing the flow of data through a website or app is a crucial skill to master if you plan on making any sort of modern web service. With flask, Object Relational Mappers (ORM’s) are employed to allow your app to interact with a relational Database. An Object Relational Mapper is a framework that, in our case, will allow us to interact with a SQL database using python instead of explicit SQL queries. The name of the ORM we are using is SQLAlchemy and can be downloaded as follows:

pip install flask_sqlalchemy
pip install psycopg2-binary #for using postgres

This article assumes that you have some basic knowledge of SQL, and have Flask, PostgreSQL, and pgAdmin installed on your machine.

Directory structure.

In order for our app to function properly, the directory needs to be laid out as follows. Make sure not to change the name or spelling of any files/folders you see below.

# from the terminal in the project folder

$ mkdir templates static
$ touch app.py
$ cd templates
$ touch index.html

$ tree (optional: only works if tree is installed on OSX)

├── app.py
├── static
└── templates
    └── index.html

2 directories, 2 files

In the next sections, we are going to make a basic form that will send a persons name, and their favorite color to a local PostgreSQL database.

Setting up our database.

We will create a database in pgAdmin:

Now, we need to set up app.py to connect our database to our application. We start by creating our app and configuring the database URI, and secret_key.

The SQLALCHEMY_DATABASE_URI is a string describing our database connection. For the puposes of this article that connection is local, and can be described as follows:

engine:[//[user[:password]@][host]/[dbname]]

engine -> postgresql
user -> postgres (see `owner` field in previous screenshot)
password -> password (my db password is the string, `password`)
host -> localhost (because we are running locally on out machine)
dbname -> flasksql (this is the name I gave to the db in the previous step)

After configuring our connection to our local database, we need to create our people table. This will consist of a primary key of integer type, a name column that must be unique, and a color column. Both the color and name columns must be entered by the user.

Now where the landing page would normally be, we will add a button that takes us to the input form. When we hit submit on the form, we add the name of the person and their favorite color to the People class/table, then using db.session we add that entry to the database, and commit the change.

We need to use db.create() to create the database before the app is run:

The HTML form for submitting the data.

To get the information from the user and into the database, we use an HTML form, with inputs that have name attributes corresponding to the column names in the database. We grab this information from the form in the personadd() function with request.form["pname"] and request.form["color"] .

Viewing the data in pgAdmin.

Now that we have the names and colors in the database, we can go to pgAdmin and query the database directly. Every time we add a new entry using the form, we can go to pgAdmin and refresh the database to reflect the results.

Every time you add a new entry, press F5 and the query will update with the new data!

Conclusion.

This article is not meant to illustrate best practices or a production ready solution, it is intended for those who have no idea where to start. We went over some of the basic concepts such as ORM’s and database URI’s, this foundational knowledge should serve you well as you learn more about databases, application infrastructure.

Happy Coding!

The GitHub repo can be found here.

💻 Feel free to check out my website.