Sending data from a Flask app to PostgreSQL Database

A database is used to store and maintain persistent data that can be retrieved and manipulated efficiently. we usually need a database, an organized collection of data for example in an e-commerce web app where we need to store the data about the users, orders, carts, shipping, etc. In a database, data is stored in such a way that it makes data transactions very efficient.

In this article, we will cover how we can configure a PostgreSQL database with a Flask app and store some data in the database after configuring it. Now before directly moving to the configuration phase here is a short overview of all tools and software we will use.

Python is a prevalent programming language that we will be using in this article. Flask is a lightweight Python web framework that provides valuable tools and features for creating web applications in Python. PostgreSQL or Postgres is a powerful, open-source object-relations database system. We are using psycopg2 which is a PostgreSQL database adapter that allows you to interact with the PostgreSQL database in Python.

 Prerequisites

• A decent understanding of Python and a machine with Python installed
• Understanding of basic concepts of Flask
• Postgres is installed on your local machine

Creating a Database and User

Now we set up PostgreSQL by creating a database with a user, and it is granted all required privileges on the created database.

# create a database named demo
CREATE DATABASE demo;

# created a user demouser with password 12345678
CREATE USER demouser WITH PASSWORD '12345678';

# configured client encoding to utf8
ALTER ROLE demouser SET client_encoding TO 'utf8';
ALTER ROLE demouser SET default_transaction_isolation TO 'read committed';
ALTER ROLE demouser SET timezone TO 'UTC';

# grant all required privileges to demouser over demo db
GRANT ALL PRIVILEGES ON DATABASE demo TO demouser; 

Database creation

Setup a Development Environment

Let’s configure the virtual environment for development, this step can be skipped but it is always recommended to use a dedicated development environment for each project to avoid dependency clash, this can be achieved using a Python virtual environment.

mkdir gfg
# Move to gfg folder
cd gfg

directory screenshot

I created a dedicated folder for the project, you can name it anything you want and cd (change directory) to go into your newly created directory then run the following command that will create a virtual environment for your project.

python -m venv venv

virtual environment creation.

Now to use the virtual environment we need to first activate it, this can be done by executing the activated binary file.

.\venv\Scripts\activate # for Windows
source venv/bin/activate # for Linux

using virtual environment

Installing Dependencies for the Project

As the development environment is configured we can install all the required tools. Following command installs Flask, psycopg2 adapter for Postgres.

 pip install Flask psycopg2-binary

install necessary libraries output

You now have the required packages installed on your virtual environment. Next, you’ll connect to and set up your database.

Setup a Database for Flask

In this step, we are going to connect to the `demo` database that we created earlier and create a user table in which user data will be inserted using a Flask App. 

To create the user’s table in the demo database first we need to connect to the demo database by using the following command.

postgres=# \c demo

using demo database in postgres

Now that we are connected to the demo database Here is how we create a table called users.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
   username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(120) UNIQUE NOT NULL
 );

Creating Table in Postgres

By creating a users table we are done with setting up the database for the Flask App.

Creating a Flask App

We are done with the database setup now we will create a Flask app and connect it to the database we created and insert some user data into it. First, let’s create a Flask app as follows.

create a main.py file in the project directory and add the following code into the file.

Python3

from flask import Flask

app = Flask(__name__)

@app.route('/')
def hello_world():
    return 'Hello, World!'

if __name__ == '__main__':
    app.run()

I have created a starter HelloWorld Flask App with just a single route let’s test this out by running then we will connect it to the database.

Flask app terminal output

Output:

Flask app output

Connecting Flask App to Database

We have got a running starter Flask App with some basic code let’s connect it to the database using the following script.

Python3

# Connect to the database
conn = psycopg2.connect(
    host="localhost",
    database="your_database_name",
    user="your_database_username",
    password="your_database_password"
)

The above script will connect to the Postgres database now we need a post route to create an entry in the database which can be done as follow.

Python3

@app.route('/create', methods=['POST'])
def create():
    # Get the username and email from the request body
    username = request.form.get('username')
    email = request.form.get('email')

    # Insert the data into the database
    cur = conn.cursor()
    cur.execute(
        "INSERT INTO users (username, email) VALUES (%s, %s)", (username, email))
    conn.commit()

    return 'User created successfully!'

Here we are getting the username and email from the request body then using a SQL query string we are inserting the data into the database.

For reference here is the overall code that I used for the demo.

Final Code:

Python3

from flask import Flask, request
import psycopg2

# Create a Flask app
app = Flask(__name__)

# Connect to the database using psycopg2 library and the database credentials
conn = psycopg2.connect(
    host="localhost",
    database="demo",
    user="demouser",
    password="12345678"
)

# Root route


@app.route('/', methods=['GET'])
def hello_world():
    return 'Hello, World!'


@app.route('/create', methods=['POST'])
def create():
    # Get the username and email from the request body
    username = request.form.get('username')
    email = request.form.get('email')

    # Insert the data into the database
    cur = conn.cursor()
    cur.execute(
        "INSERT INTO users (username, email) VALUES (%s, %s)", (username, email))
    conn.commit()

    return 'User created successfully!'


if __name__ == '__main__':
    app.run()

Sending Data from Flask to PostgreSQL

Now let us test the entire script I wrote and if we can insert some data into the database using it, First run the Flask App shown before then make a POST request to /create a route using a tool like Postman.

postman screenshot

The response above looks fine let’s see if there is any entry in the user’s table or not.

Now go to your PostgreSQL shell and Type 

SELECT * FROM users;

postgres output