[PYTHON] Execute SQL with Django + Postgresql (including PreparedStatement)

Django connects differently between Postgreslq and MySQL

By default, Django supports MySQL, but Postgresql requires a library called psycopg! So this time it will be a Postgresql limited method

Get a connection with psycopg

The procedure for issuing a SQL query with psycopg looks like this

1. Create a connection
2. Execute query from the cursor

If you write this using With, it will look like this (Because the With statement automatically closes the connection at the end of the statement ...)

service.py

dbname = os.environ.get('DATABASE_URL')
with psycopg2.connect(dbname ) as conn:
    with conn.cursor() as curs:
        curs.execute('SELECT * FROM users')

So, it is fetched by fetch.

service.py

dbname = os.environ.get('DATABASE_URL')
data = None
with psycopg2.connect(dbname ) as conn:
    with conn.cursor() as curs:
        curs.execute('SELECT * FROM users')
        data = cur.fetchone() #Get one
        data = cur.fetchall() #all

So, I want to put anything in the SQL condition! So I would like to use variables to make the parameters variable

service.py

dbname = os.environ.get('DATABASE_URL')
WhereValue = ' WHERE gender = \'male\''
data = None
with psycopg2.connect(dbname ) as conn:
    with conn.cursor() as curs:
        curs.execute('SELECT * FROM users' + WhereValue)
        data = cur.fetchone() #Get one
        data = cur.fetchall() #all

Trouble will occur here

service.py

WhereValue = '\'t OR \'same\' = \'same\''

If you do, the WHERE clause will be a complete set and SQL injection will occur.

Therefore, this is the method to execute with PreparedStatement.

How to perform a PreparedStatement in Postgresql

How to specify parameters with% s

By setting the parameter to **% s ** and specifying the list (or tuple) as the second argument You can issue SQL using PreparedStatement

service.py

dbname = os.environ.get('DATABASE_URL')
WhereValue = '\'t OR \'same\' = \'same\''
data = None
with psycopg2.connect(dbname ) as conn:
    with conn.cursor() as curs:
        #Set list type and execute PreparedStatement
        curs.execute('SELECT * FROM users Where gender = %s', (WhereValue,))
        data = cur.fetchone() #Get one
        data = cur.fetchall() #all

Also, it seems that you can use a dictionary type like this. (Rumor)

service.py

#%(Variable name)Match the name with the parameter of s
dbname = os.environ.get('DATABASE_URL')
WhereValue = {'gender_param': ('male',)}
data = None
with psycopg2.connect(dbname ) as conn:
    with conn.cursor() as curs:

        #Set dictionary type and execute PreparedStatement
        curs.execute('SELECT * FROM users Where gender = %(gender_param)s', WhereValue )
        data = cur.fetchone() #Get one
        data = cur.fetchall() #all

Now when you inject SQL into a parameter, it won't run illegally!

reference

Summary of operations often performed with psycopg2 https://qiita.com/hoto17296/items/0ca1569d6fa54c7c4732 I tried throwing a mutable SQL statement from Python code to PostgreSQL https://dev.classmethod.jp/articles/py-psql-query-trial/ How to use python postgreSQL library psycopg2 https://qiita.com/t20190127/items/0fb944c8679795257e01

psycopg2 Documentation ConnectionClass https://www.psycopg.org/docs/connection.html psycopg2 Documentation CursurClass https://www.psycopg.org/docs/cursor.html