How to use PostgreSQL with Django application

Before using PostgreSQL with Django, you need to create a database and database user. Please refer to following for creation of database and user using terminal.

$sudo su - postgres
[sudo] password for USER: 
postgres@inspiron-3542:~$ psql
postgres=#
postgres=# CREATE DATABASE polls;
postgres=# CREATE USER pollsuser WITH PASSWORD 'pollsuser';  
CREATE ROLE
postgres=# ALTER ROLE pollsuser SET client_encoding TO 'utf8';
ALTER ROLE
postgres=# ALTER ROLE polsuser SET default_transaction_isolation TO 'read committed';
ERROR:  role "polsuser" does not exist
postgres=# ALTER ROLE pollsuser SET default_transaction_isolation TO 'read committed';
ALTER ROLE
postgres=# ALTER ROLE pollsuser SET timezone TO 'UTC';
ALTER ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE polls TO pollsuser;
GRANT
postgres=# \q

Now, make following changes in settings.py


DATABASES = {
    'default': {
        # 'ENGINE': 'django.db.backends.sqlite3',
        # 'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'polls',
        'USER': 'pollsuser',
        'PASSWORD': 'pollsuser',
        'HOST': 'localhost',
        'PORT': '',            
    }
}

If we do not create database and user and run makemigrations command, you will get following error.

python3 manage.py makemigrations polls
Traceback (most recent call last):
  File "/home/conquistadorjd/.local/lib/python3.6/site-packages/django/db/backends/base/base.py", line 220, in ensure_connection
    self.connect()
  File "/home/conquistadorjd/.local/lib/python3.6/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "/home/conquistadorjd/.local/lib/python3.6/site-packages/django/db/backends/base/base.py", line 197, in connect
    self.connection = self.get_new_connection(conn_params)
  File "/home/conquistadorjd/.local/lib/python3.6/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "/home/conquistadorjd/.local/lib/python3.6/site-packages/django/db/backends/postgresql/base.py", line 185, in get_new_connection
    connection = Database.connect(**conn_params)
  File "/home/conquistadorjd/.local/lib/python3.6/site-packages/psycopg2/__init__.py", line 126, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL:  database "polls" does not exist

This is because we have not created polls database. Now let us run makemigrations. makemigrations is like version control for database.

$ python3 manage.py makemigrations polls
No changes detected in app 'polls'

It says no changes detected in app because we have not added anything into polls/models.py. Let us add Questions and Choice model in this file.

from django.db import models

class Question(models.Model):
    question_text = models.CharField(max_length=200)
    pub_date = models.DateTimeField('date published')
    def __str__(self):
        return self.question_text


class Choice(models.Model):
    question = models.ForeignKey(Question, on_delete=models.CASCADE)
    choice_text = models.CharField(max_length=200)
    votes = models.IntegerField(default=0)
    def __str__(self):
        return self.choice_text   

After adding models definition, let us rune makemigrations command again


$ python3 manage.py makemigrations polls
Migrations for 'polls':
  polls/migrations/0001_initial.py
    - Create model Question
    - Create model Choice

Please note this just creates models files, tables are not yet created in database, we need to run migrate command to apply this to PostgreSQL database. Let us check if there are any tables in “polls” database

postgres=# \connect polls
You are now connected to database "polls" as user "postgres".
polls=# \dt
Did not find any relations.

As you can see, there are no tables in database “polls”. Let us run migrate command now


$ python3 manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, polls, 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 polls.0001_initial... OK
  Applying sessions.0001_initial... OK

Now let us check if any tables exists in database polls.


polls=# \dt
                    List of relations
 Schema |            Name            | Type  |   Owner   
--------+----------------------------+-------+-----------
 public | auth_group                 | table | pollsuser
 public | auth_group_permissions     | table | pollsuser
 public | auth_permission            | table | pollsuser
 public | auth_user                  | table | pollsuser
 public | auth_user_groups           | table | pollsuser
 public | auth_user_user_permissions | table | pollsuser
 public | django_admin_log           | table | pollsuser
 public | django_content_type        | table | pollsuser
 public | django_migrations          | table | pollsuser
 public | django_session             | table | pollsuser
 public | polls_choice               | table | pollsuser
 public | polls_question             | table | pollsuser
(12 rows)

Now we have established connection with PostgreSQL. Now we need to do following.

  • Accessing these model from admin menu
  • Fetching and displaying data from database
  • Updating database

Accessing model from admin menu

To have these models accessed from admin menu, you simple have to register them for admin. This is very simple and can be done by adding below code into polls/admin.py file.


from django.contrib import admin

from .models import Question, Choice

# Register your models here.
admin.site.register(Question)
admin.site.register(Choice)

Here is the output. You can see both the models are visible from admin login now.

Fetching and displaying data from database

Now let us try to display some data from PostgreSQL tables to one of the view. You can add data directly into database or from admin menu. Its very easy to add data from admin menu, so I will use this method.

Here is how polls/views.py will look like

.
.

from .models import Question, Choice
.
.
def seventh(request):
    context = Question.objects.all()
    return render(request, 'polls/seventh.html' , {'context': context})

Please note that you need to import model names which you want to access from views. Django provide methods to access data from models.

View will look like this

{% extends "polls/base.html" %}
{% block content %}
{% if context %}
<ul>
{% for question in context %}
<li> {{ question}}</li>
{% endfor %}
</ul>
{% else %}
<p>No questions are available.</p>
{% endif %}
{% endblock %}

Here is the output

Updating database

Updating PostgreSQL is not very complex, its very similar to displaying data, you simply need to use different methods.


def detail(request, question_id):
    question = get_object_or_404(Question, pk=question_id)
    return render(request, 'polls/detail.html', {'question': question})

def results(request, question_id):
    question = get_object_or_404(Question, pk=question_id)
    return render(request, 'polls/results.html', {'question': question})

def vote(request, question_id):
    question = get_object_or_404(Question, pk=question_id)
    try:
        selected_choice = question.choice_set.get(pk=request.POST['choice'])
    except (KeyError, Choice.DoesNotExist):
        # Redisplay the question voting form.
        return render(request, 'polls/detail.html', {
            'question': question,
            'error_message': "You didn't select a choice.",
        })
    else:
        selected_choice.votes += 1
        selected_choice.save()
        # Always return an HttpResponseRedirect after successfully dealing
        # with POST data. This prevents data from being posted twice if a
        # user hits the Back button.
        return HttpResponseRedirect(reverse('polls:results', args=(question.id,)))

also added following templates polls/details.html

{% extends "polls/base.html" %}
{% block content %}
<h1>{{ question.question_text }}</h1>
{% if error_message %}<p><strong>{{ error_message }}</strong></p>{% endif %}
<form action="{% url 'polls:vote' question.id %}" method="post">
{% csrf_token %}
{% for choice in question.choice_set.all %}
<inputtype="radio"name="choice"id="choice{{ forloop.counter }}"value="{{ choice.id }}">
<labelfor="choice{{ forloop.counter }}">{{ choice.choice_text }}</label><br>
{% endfor %}
<input type="submit" value="Vote">
</form>
{% endblock %}

polls/results.html

{% extends "polls/base.html" %}
{% block content %}
<h1>{{ question.question_text }}</h1>
<ul>
{% for choice in question.choice_set.all %}
<li>{{ choice.choice_text }} -- {{ choice.votes }} vote{{ choice.votes|pluralize }}</li>
{% endfor %}
</ul>
<ahref="{% url 'polls:detail' question.id %}">Vote again?</a>
{% endblock %}

You can find complete code here

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.