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;
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
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
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
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
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
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 );
By creating a users table we are done with setting up the database for the Flask App.
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