Python PostgreSQL – Transaction management using Commit and Rollback
In this article, we are going to see how to transaction management using Commit and Rollback.
In pyscopg, the connection class in psycopg is in charge of processing transactions. When you use a cursor object to issue your first SQL statement to the PostgreSQL database, psycopg generates a new transaction. Psycopg executes all following statements in the same transaction from that point forward. Psycopg will stop the transaction if any statement fails. Commit() and rollback() are two methods of the connection class that may be used to stop a transaction. The commit() function is used to permanently commit all changes to the PostgreSQL database. You may also use the rollback() function to undo any modifications you’ve made.
The following is an example of how to handle a transaction in psycopg, the most basic way to handle it.
The table from the database we are working on looks like this:
Click here to view and download the CSV file.
Create the sales table in PostgreSQL and import the CSV file:
Example 1: Example of a successful transaction
The code starts with importing packages, establishing a connection to the database and we must make sure that connection.autocommit is false because by default it is True, it commits all changes beforehand and we cannot use rollback() to go back to the previous state. a cursor is created with connection.cursor() method. data is queried and fetched. the sales of a particular order_id are calculated. as the SQL statement doesn’t have any error, the transaction is finished successfully.
total sales from the order id CA-2016-152156 is : 993.9000000000001
successfully finished the transaction
PostgreSQL database connection is closed
Example 2: Example of an unsuccessful transaction
The code is similar to the before one except that the wrong table name is given in the SQL statement, as it’s incorrect, all changes are reverted or undone using the rollback() method and the connection is closed.
Error in transaction, reverting all changes using rollback, error is : relation “sales1” does not exist
LINE 1: select sales from sales1 where order_id = ‘CA-2016-1521591’
Please Login to comment...