[psycopg2] SQL Transactions

1. What is SQL Transactions?

A Database Transaction is a unit of interaction in a database management system or a similar system. Here, a similar system means a system in which transactions are clear, mutually independent, consistent, and reliable in success and failure. That is, it refers to a unit of work performed to change the state of the database.

 

In theory, the database system guarentees ACID(atomicity, consistency, isolation, durability) for each transcation.

 

• Atomicity : Transactions should or should not be reflected in the database at all.
• Consistency : The results of a transcation's job processing must always be consistent.
• Isolation : No single transaction can interfere with the operation of another transcation.
• Durability : If a transcation is successfully completed, the results should be reflected permanently.

 

A single transaction requires multiple queries that are read or written within the database. What is important at this time is that some queries from which the database has been performed are not left. For example, it is important to check the deposit from another account if it is withdrawn from one account when making a remittance. Transcations should also not interfere with each other.

 

Postgres can cause problem because multiple users connected to the server simultaneously change the database. All queries that can cause problems when a transcation is executed separately or fails to execute are placed in a transcation block and executed at once. Therefore, if one transaction fails, all transcation fail and information is not updated in the database.

 

2. How to work with transcation?

import psycopg2
conn = psycopg2.connect("dbname=db user=ur")
cursor = conn.cursor()

# Initiate transcation 
# query1 : Creating table 
query1 = """
    CREATE TABLE table(
        id integer PRIMARY KEY, 
        col1 text,
        col2 text,
        col3 text
    );
"""
cursor.execute(query1)

# query2 : Insert file in database table 
with open('file.csv', 'r') as file: 
    next(file) 
    reader = csv.reader(file) 
    for row in reader: 
        cursor.execute("INSERT INTO table VALUES (%s, %s, %s, %s);", 
                       (row[0], row[1], row[2], row[3])) 
                       
# query3 : View table stored in database
query3 = """SELECT * FROM table;"""
cursor.execute(query3) 

# Commit queries in transaction
conn.commit()
conn.close()