[PYTHON] Transactions when operating PostgreSQL with Psycopg2

Here is a summary of the basic usage of Psycopg2 and new lessons learned about transactions.

environment

Psycopg2 : 2.8.4 Heroku : 0.1.4 python : 3.7.4

Connect to DB with Psycopg2 and execute SQL statement

All about psycopg2 is written in the official documentation. https://www.psycopg.org/docs/usage.html#transactions-control

The following is the one who only needs to know at least this. It's easy!

psycopg2.py

#First, create a connection instance connected to the DB with the connect function.
conn = psycopg2.connect(DATABASE_URL)

#Then, use the cursor method of the connection instance to create a cursor instance that can execute the sql statement.
cursor = conn.cursor()

#When doing INSERT or DELETE, execute it, then commit and reflect it in the DB.
cursor.execute("The sql statement you want to execute")
cursor.commit()

#After SELECT, execute and then fetch with fetch one, fetch all or fetch many
cursor.execute("The sql statement you want to execute")
data = cursor.fetchall()
cursor.close() #Don't forget to close this

#Finally disconnect
conn.close()

Be careful

Be sure to close or commit cursor after executing the sql statement. Even if you commit, cursor will be automatically close.

That is, always conn.commit () after executing INSERT, and cursor.close () after fetch after SELECT.

According to the documentation, a transaction is created when the sql statement is executed. This is handled by the connection class. The transaction ends when cursor is closed.

If another sql statement is executed before the transaction that has already been executed close, the sql statement will be executed in the same transaction (no new transaction will be created).

In other words, if you do not end the transaction properly (do not close`` cursor), the sql statement will continue to be executed forever in the same transaction. The scary thing about this is that once an error occurs in a transaction, it will be trapped in the error forever until the transaction ends, making the next sql statement unexecutable.

So every time you execute a sql statement, let cursor`` close.

With statement for you who are likely to forget close

Using the with statement looks like this. An excellent one that even commits without permission when the processing in the with sentence is completed. By the way, there seems to be no problem even if you commit after executing SELECT. In other words, it's a "commit`".

connect.py

import psycopg2

DATABASE_URL = os.environ.get('DATABASE_URL')
SQL = "select *from table name"

with psycopg2.connect(DABASE_URL) as conn:
    with conn.cursor() as curs:
        curs.execute(SQL)
        result = curs.fetchall()
#Search results are stored in a list in result

By the way, note that even if cursor is close with with, connection seems to remain connected. (What's wrong with keeping them connected ...)

For those of you who don't want to use the with statement autocommit mode

You can control the execution of the next sql statement with the set_session method of the connection instance. If you set ʻautocommit mode, it will commit without permission after execution. Thank you. In readonry mode`, INSERT and DELETE cannot be performed.

For more information, see Official Documents

connect.py

import psycopg2

DATABASE_URL = os.environ.get('DATABASE_URL')
SQL = "select *from table name"

#As usual until you create a cursor instance
conn = psycopg2.connect(DATABASE_URL)
cursor = conn.cursor()

#Can be set for the following execute
conn.set_session(readonly=True, autocommit=True)

#Even if you execute it normally, it will be committed without permission after that ~~
self.cursor.execute (sql)

This also stays connected unless conn.close ().