PostgreSQL Python Transaction

Python is a popular programming language that allows you to work with any kind of database. It provides numerous adapters for this purpose. These adapters act as an interface between python and your database. Sometimes you may need to perform PostgreSQL transaction from within your Python app or website. In this article, we will learn how to perform PostgreSQL transaction in Python using psycopg adapter.

PostgreSQL Python Transaction

pscyopg is a python adapter that allows you to easily communicate with your PostgreSQL database from within your python application. It provides connection class to help you connect with your PostgreSQL database and run queries, using cursor object.

Once a connection is established, psycopg will execute all your subsequent queries using the same connection & cursor objects. If there is an error in any of the statements, psycopg will terminate the database connection and you will need to create new connection & cursor objects to be able to run subsequent queries.

The connection class also provides two methods commit() and rollback(). When you issue transactional statements, PostgreSQL will not commit these changes to your database. For this purpose, you need to use the commit() function. On the other hand, if you want to discard the statements that you have run since last commit, issue rollback() function. If you close or delete the connection object before committing the changes, all those uncommitted statements will be lost, resulting in implicit rollback.

Alternatively, you can also set autocommit flag to true, in your connection object. In this case, psycopg will automatically commit each executed statement immediately. It also useful to run non-transactional statements like CREATE queries.

Here is the command to install psycopg in your system.

$ pip install psycopg2-binary

Here is the typical code structure of transactional statements.

import psycopg2

conn = None
try:
    conn = psycopg2.connect(dsn)
    cur = conn.cursor()
    # execute 1st statement
    cur.execute(statement_1)
    # execute 2nd statement
    cur.execute(statement_2)
    # commit the transaction
    conn.commit()
    # close the database communication
    cur.close()
except psycopg2.DatabaseError as error:
    print(error)
finally:
    if conn is not None:
        conn.close()

Initially, we import psycpg module into our code. Next, we create a try..except block to connect to our database, run transactional queries, and commit changes.

In the try block, we use psycopg2.connect() function to connect to our database. For this you need to provide the connection string, consisting of host, port, username, password, database. Once the connection is established, we use cursor() function to get a cursor to run queries. Using execute() function, we run couple of SQL queries. We then use commit() function to commit the changes. Finally, we use close() function to close the database connection. In the except block , we basically catch and print the error message. You don’t need to explicitly call rollback() function here since the connection will be automatically closed, in case of errors and uncommitted changes will be lost.

Here is a simple example that shows how to make connection and execute queries.

import psycopg2

# Connect to an existing database
conn = psycopg2.connect("dbname=test user=postgres")

# Open a cursor to perform database operations
cur = conn.cursor()

# Execute a command: this creates a new table
cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")

# Pass data to fill a query placeholders and let Psycopg perform
# the correct conversion (no more SQL injections!)
cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)",
 (100, "abc'def"))

# Query the database and obtain data as Python objects
cur.execute("SELECT * FROM test;")
cur.fetchone()
(1, 100, "abc'def")

# Make the changes to the database persistent
conn.commit()

# Close communication with the database
cur.close()
conn.close()

Alternatively, you can also use psycopg with ‘with’ statement. Here is the basic syntax to execute SQL query using WITH statement. Replace dsn below with connection details.

with psycopg2.connect(dsn) as conn:
    with conn.cursor() as cur:
        cur.execute(sql_query)

Please note, when you exit the WITH block, the connection will not be closed. You can use the same connection object to execute more queries.

# transaction 1
with conn:
    with conn.cursor() as cur:
        cur.execute(sql_query)

In this article, we have learnt how to connect to PostgreSQL database from python and run SQL queries.

PostgreSQL Python Transaction

PostgreSQL Python Transaction

Related posts:

Leave a Reply Cancel reply