Connect Django Project to PostgreSQL

1 - Install django, psycopg2

pip install django

pip install psycopg2

After installing these requirements, you need to connect Django project to the new database.

2 - Create Database and Insert Data

In postgres in query tools run this command:

DROP DATABASE IF EXISTS "Ashoori_97149068";
CREATE DATABASE "Ashoori_97149068"
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'English_United States.1252'
    LC_CTYPE = 'English_United States.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

and then with opening "Query tools" of Ashoori_97149068's database run all the create and insert query in +create_and_data.sql".

3 - Set username and password in setting.py

Into the config > setting.py and to the DATABASE section, set the database config like this format:

you need to change the < > and < > of this section to your own information

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'Ashoori_97149068',
        'OPTIONS': {
        'options': '-c search_path=public'
    },
        'USER': '<
   
    >'
   ,
        'PASSWORD': '<
   
    >'
   ,
        'HOST': 'localhost',
        'PORT': '',
    }
}

After doing above steps Django is connected to the database.

4- Adding requirement tables for log in to the Django-admin:

by running migrate command django automatically create its requirements table into database:

python manage.py migrate

Now the Django’s requirement tables are created and we should create a superuser to can Log in to the Django admin:

python manage.py createsuperuser

and fill the requirement with fake info. and then log in with that info.

Explain Queries

All Queries Are in all > admin.py

1 - SELECT all Student that their mark >= 10:

Participated.objects.raw("""
                            SELECT stu_id
                            FROM participated 
                            WHERE mark >= 10
                            """
                            )

2 - SELECT all Student that their mark < 10:

Participated.objects.raw("""
                            SELECT stu_id
                            FROM participated 
                            WHERE mark < 10
                            """
                            )

3 - SELECT all Student that their balance > 70: We should get balance from Students table so we should join participated and students to get access balance:

Participated.objects.raw("""
                            SELECT stu_id
                            FROM participated 
                            natural join students
                            WHERE balance > 70
                            """
                            )

4 - UPDATE Mark in Participated table:

with connection.cursor() as c:
            c.execute("""
            UPDATE participated
            SET mark = 9.99
            WHERE mark IS NOT NULL 
                AND mark < 10
            """)

5 - UPDATE Mark in Participated table:

with connection.cursor() as c:
            c.execute("""
            UPDATE participated
            SET mark = mark + 1
            WHERE mark BETWEEN 10 AND 19
            """)

6 - SELECT username of Students table:

def sql_username(self, obj):
        p = Students.objects.raw(f"""
        SELECT *
        FROM students
        Where stu_id = '{obj.stu_id}'
        limit 1
        """)[0]
        return f"{p.username}"

In admin.py also you can see more similar example.