How to integrate Python Django with PostgreSQL 12 Database
Test Environment
Fedora 31 OS with Python installed
Django is a python web framework which can be used to develop web application. In this article we are going to see how we can use the PostgreSQL Database in place of SQLLite database which is the default database setting in Django web project.
So, here we are going to divide this article into two high level tasks that we will be carrying out.
- Install and Configure the PostgreSQL Database
- Setup Default Django project and configure the PostgreSQL Database
Lets get started.
Install and Configure the PostgreSQL Database
Step1: Install the Repository RPM
In this step we are setting up the PostgreSQL package repository in Fedora 31
[admin@feddesk ~]$ sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/F-31-x86_64/pgdg-fedora-repo-latest.noarch.rpm
...
Installed:
pgdg-fedora-repo-42.0-6.noarchStep2: Install the Client package
[admin@feddesk ~]$ dnf install postgresql12
...
Installed:
postgresql12-12.1-2PGDG.f31.x86_64 postgresql12-libs-12.1-2PGDG.f31.x86_64Step3: Install the Server package
[admin@feddesk ~]$ sudo dnf install postgresql12-server
...
Installed:
postgresql12-server-12.1-2PGDG.f31.x86_64Step4: Initialize the database
[admin@feddesk ~]$ sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database ... OKStep5: Enable and Start PostgreSQL on startup
[admin@feddesk ~]$ sudo systemctl enable postgresql-12.service
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-12.service → /usr/lib/systemd/system/postgresql-12.service.
[admin@feddesk ~]$ sudo systemctl start postgresql-12.service
[admin@feddesk ~]$ sudo systemctl status postgresql-12.service
● postgresql-12.service - PostgreSQL 12 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)
Active: active (running) since Sat 2020-02-01 08:16:31 IST; 11s ago
...
Feb 01 08:16:31 feddesk.stack.com systemd[1]: Starting PostgreSQL 12 database server...
Feb 01 08:16:31 feddesk.stack.com postmaster[3100]: 2020-02-01 08:16:31.763 IST [3100] LOG: starting PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.2.>
Feb 01 08:16:31 feddesk.stack.com postmaster[3100]: 2020-02-01 08:16:31.766 IST [3100] LOG: listening on IPv6 address "::1", port 5432
Feb 01 08:16:31 feddesk.stack.com postmaster[3100]: 2020-02-01 08:16:31.767 IST [3100] LOG: listening on IPv4 address "127.0.0.1", port 5432
Feb 01 08:16:31 feddesk.stack.com postmaster[3100]: 2020-02-01 08:16:31.768 IST [3100] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Feb 01 08:16:31 feddesk.stack.com postmaster[3100]: 2020-02-01 08:16:31.769 IST [3100] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
Feb 01 08:16:31 feddesk.stack.com postmaster[3100]: 2020-02-01 08:16:31.786 IST [3100] LOG: redirecting log output to logging collector process
Feb 01 08:16:31 feddesk.stack.com postmaster[3100]: 2020-02-01 08:16:31.786 IST [3100] HINT: Future log output will appear in directory "log".
Feb 01 08:16:31 feddesk.stack.com systemd[1]: Started PostgreSQL 12 database server.Step6: Initialize the password for ‘postgres’ user
postgres is the superuser which has all the privilages and this is user with which the PostgreSQL run by default.
[root@feddesk ~]# passwd postgresStep7: Switch to user ‘postgres’
[root@feddesk ~]# su - postgresStep8: Create the Database
[postgres@feddesk ~]$ createdb mydbStep9: Access the Database
[postgres@feddesk ~]$ psql mydb
psql (12.1)
Type "help" for help.
mydb=#Step10: Check the Database version
mydb=# select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.2.1 20190827 (Red Hat 9.2.1-1), 64-bit
(1 row)Step11: Create a table
CREATE TABLE students (
name varchar(80),
age int
);Step12: Insert data into table
INSERT INTO students VALUES ('Amber Duo', 46);
mydb=# insert into students values ('Amber', 23);
INSERT 0 1
mydb=# insert into students values ('Mike', 40);
INSERT 0 1
mydb=# insert into students values ('Alaka', 18);
INSERT 0 1Step13: Retrieve the data from table
mydb=# select * from students;
name | age
-------+-----
Amber | 23
Mike | 40
Alaka | 18
(3 rows)As we are going to connect with user and password from Django project we need to edit the below two configuration files to allow connection from remote as shown below.
First, edit the below config file to listen on all interface IP addresses.
Edit postgresql.conf to listen on all interfaces.
[postgres@feddesk data]$ grep -i listen postgresql.conf
listen_addresses = '*'
#listen_addresses = 'localhost' # what IP address(es) to listen on;Second, comment peer and ident method connection and add the below md5 connection from anywhere.
Comment peer and ident method connection and add the below md5 connection from anywhere.
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
#local all all peer
# IPv4 local connections:
#host all all 127.0.0.1/32 ident
# IPv6 local connections:
#host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication all peer
#host replication all 127.0.0.1/32 ident
#host replication all ::1/128 ident
# Accept from anywhere
host all all 0.0.0.0/0 md5
host all all ::1/128 md5Once the necessary configuraiton changes are made we need to restart the PostgreSQL DB service.
Restart database service.
[admin@feddesk ~]$ sudo systemctl restart postgresql-12.serviceStep14: Create a non priviliages user
[postgres@feddesk ~]$ createuser djangouser -U postgres -h localhostStep15: Alter the password for user djangouser
[postgres@feddesk ~]$ psql -c "alter user djangouser with password 'strongpassword'" -U postgres -h localhostStep16: Grant all privilages to user djangouser on mydb
[postgres@feddesk ~]$ psql mydb -U postgres -h localhost
Password for user postgres:
psql (12.1)
Type "help" for help.
mydb=# grant all privileges on database mydb to djangouser;
GRANT
mydb=# grant all privileges on all tables in schema public to djangouser;
GRANTStep17: Connect using djangouser and extract table details
[postgres@feddesk ~]$ psql -U djangouser -h localhost -d mydb
mydb=> select * from students;
name | age
-------+-----
Amber | 23
Mike | 40
Alaka | 18
(3 rows)So, with the above all steps we are able to setup our PostgreSQL database, create tables, create users, insert data into tables and retrieve table details.
Setup Default Django project and configure the PostgreSQL Database
Step18: Install Psycopg database adapter for python
For python applications to connect and use the PostgreSQL database we need to install the below library which will help in establishing connection with our database.
[admin@feddesk ~]$ python -m pip install psycopg2-binary --user
Collecting psycopg2-binary
Using cached https://files.pythonhosted.org/packages/37/69/c964165696fddf611f518b31c20774cdb635a49d6a0bd5bc3a1deec549b3/psycopg2_binary-2.8.4-cp37-cp37m-manylinux1_x86_64.whl
Installing collected packages: psycopg2-binary
Successfully installed psycopg2-binary-2.8.4Step19: Edit the django project database setting as below and comment the sqllite database
Here i am assuming we have already setup your default Django project. If not please go through the Django Getting Started documentation and setup your First Django project.
Ref – https://docs.djangoproject.com/en/3.0/intro/tutorial01/
Once the Django project is setup we need to edit the settings.py file of the project as shown below. As you can see we commented the SQLLite DB setting
and using the PostgreSQL DB setting now.
Edit the django project database setting
#DATABASES = {
# 'default': {
# 'ENGINE': 'django.db.backends.sqlite3',
# 'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
# }
#}
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql',
'NAME': 'mydb',
'USER': 'djangouser',
'PASSWORD': 'django@1234',
'HOST': '127.0.0.1',
'PORT': '5432',
}
}Step20: Apply the Database migrations to the new PostgreSQL Database
Now, let us apply all the database migration which are pending as shown below.
[admin@feddesk mysite]$ python manage.py migrate
Operations to perform:
Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
Applying contenttypes.0001_initial... OK
Applying auth.0001_initial... OK
Applying admin.0001_initial... OK
Applying admin.0002_logentry_remove_auto_add... OK
Applying admin.0003_logentry_add_action_flag_choices... OK
Applying contenttypes.0002_remove_content_type_name... OK
Applying auth.0002_alter_permission_name_max_length... OK
Applying auth.0003_alter_user_email_max_length... OK
Applying auth.0004_alter_user_username_opts... OK
Applying auth.0005_alter_user_last_login_null... OK
Applying auth.0006_require_contenttypes_0002... OK
Applying auth.0007_alter_validators_add_error_messages... OK
Applying auth.0008_alter_user_username_max_length... OK
Applying auth.0009_alter_user_last_name_max_length... OK
Applying auth.0010_alter_group_name_max_length... OK
Applying auth.0011_update_proxy_permissions... OK
Applying sessions.0001_initial... OKStep21: Validate the database migration
[admin@feddesk mysite]$ psql -U djangouser -h localhost -d mydb
Password for user djangouser:
psql (12.1)
Type "help" for help.
mydb=> dt;
List of relations
Schema | Name | Type | Owner
--------+----------------------------+-------+------------
public | auth_group | table | djangouser
public | auth_group_permissions | table | djangouser
public | auth_permission | table | djangouser
public | auth_user | table | djangouser
public | auth_user_groups | table | djangouser
public | auth_user_user_permissions | table | djangouser
public | django_admin_log | table | djangouser
public | django_content_type | table | djangouser
public | django_migrations | table | djangouser
public | django_session | table | djangouserStep22: Restart the django lightweight web server
[admin@feddesk mysite]$ python manage.py runserver 0:8000
Watching for file changes with StatReloader
Performing system checks...
System check identified no issues (0 silenced).
February 02, 2020 - 02:18:55
Django version 3.0.2, using settings 'mysite.settings'
Starting development server at http://0:8000/
Quit the server with CONTROL-C.As you can see from above all the database migrations have been applied and are currently available in the mydb database with owner djangouser.
Hope you enjoyed reading this article. Thank you..
Leave a Reply
You must be logged in to post a comment.