[Python/Django] Summary of frequently used commands (3) <Operation of PostgreSQL>

background

Here are some frequently used commands when developing with Python/Django.

I would like to take this opportunity to thank you for solving the problem by borrowing the wisdom of our predecessors, and I am very sorry to say that I will summarize it here as my own memo.

environment

(Production environment)

• AWS EC2 (Amazon Linux 2) --Python 3.7.9 * Default on Amazon Linux 2 as of 12/10/2020
• Django 3.1.3 --PostgreSQL 11.5 * Same as above --Nginx 1.12 * Same as above
• Gunicorn
• Putty 0.74

(Development environment)

• Windwos 10 Pro
• Python 3.9.0
• Django 3.1.3
• PostgreSQL 13.1
• Nginx 1.19.5
• Gunicorn
• Putty 0.74

1. PostgreSQL operation

• It is assumed that the installation has already been completed in the local machine environment.

1-1. Checking the version

Terminal

$ psql --version

psql (PostgreSQL) 13.1

1-2. Login

Terminal

$ psql -U postgres

Password for user postgres:                 
psql (13.1)
"help"Get help with.

postgres=#

1-3. Create a database

Terminal

postgres=#create database <database name>;

CREATE DATABASE

1-4. Check the list of databases

Terminal

postgres=# \l
Database list
name|owner|encoding|Collation| Ctype(Conversion operator) |Access rights
---------------+----------+------------------+----------+-------------------+-----------------------
 postgres      | postgres | UTF8             | C        | C                 |
<DB name>| postgres | UTF8             | C        | C                 |
 template0     | postgres | UTF8             | C        | C                 | =c/postgres          +
               |          |                  |          |                   | postgres=CTc/postgres
 template1     | postgres | UTF8             | C        | C                 | =c/postgres          +
               |          |                  |          |                   | postgres=CTc/postgres
(4 lines)

postgres=#  

1-5. Check the data

1-5-1. Enter the Python virtual environment and change to the directory where manage.py exists.

1-5-2. Execute the following to set the environment variables for database user and password.

-(Premise) Project setting file:

settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': '<Database name>',
        'USER': os.environ.get('DB_USER'),
        'PASSWORD': os.environ.get('DB_PASSWORD'),
        'HOST': '',
        'PORT': '',
    }
}

Terminal

(venv_<Project name>) $ set DB_USER=<User name>
(venv_<Project name>) $ set DB_PASSWORD=<Password>

1-5-3. Execute the following to enable database operations.

Terminal

(venv_<Project name>) $ python manage.py dbshell --settings <database name>.settings

<Database name>=#

1-5-4. Displaying the table list

Terminal

<Database name>=# \dt

1-5-5. View all records in a particular table

Terminal

<Database name>=# select *from <table name>;

1-5.6. Other

• Please refer to How to use PostgreSQL.

1-5-7. Log out

Terminal

<Database name>=# \q
Or
postgres=# \q

(Relation)

[Python/Django] Summary of frequently used commands (1) <Creating virtual environment, project, application> [Python/Django] Summary of frequently used commands (2) [Python/Django] Summary of frequently used commands (4) -Part 1- <Production operation: Amazon EC2 (Amazon Linux 2)> [Python/Django] Summary of frequently used commands (4) -Part 2- <Production operation: Amazon EC2 (Amazon Linux 2)>

(Editor's note)

We will strive to create an application while carefully checking what values ​​are stored in each table in the database.