Setting The Psycopg2 Isolation Level In Python For Postgresql Transactions

Introduction

The Psycopg 2 adapter is used with PostgreSQL to support the Python programming language. With Psycopg2, developers and DBAs have the ability to set appropriate transaction isolation levels which control the time duration for read locks and other isolation specifications. Learn how with this tutorial that explains a fast way to set Psycopg2 isolation level Python PostgreSQL.

If you already know how to accomplish the steps outlined in this lesson, feel free to go straight to Just the Code.

Prerequisites

• Download and install the latest PostgreSQL binary package for your OS.

• Download and install Python 3 on the same server you installed PostgreSQL.

NOTE: Check the PIP3 version number by entering pip3 -V at the command line. Then confirm the Python 3 version by inputting the python3 -V command.

• Use PIP3 to download and install the library for the Python psycopg2 adapter like this:

Set proper isolation levels for the psycopg2 adapter

At the time you create a new PostgreSQL database, it’s important to modify the isolation level for the object of the database connection. You’ll need to set the object’s integer value for the set_isolation_level.

NOTE: Abide by the Atomicity, Consistency, Isolation, Durability (ACID) standard for setting isolation levels for transaction making.

Settings for integers and their corresponding levels for isolation transactions.

READ UNCOMMITTED = 0 Integer

Can read uncommitted changes or data dirty reads but not in PG locks. Level 0 is the isolation that is the lowest, so it presents a high risk.

READ COMMITTED = 1 Integer

Dirty reads are not possible with this isolation setting. However, phantom reads, nonrepeatable reads, and serialization anomalies are all possible. In separate areas of a transaction, two values that differ can be read after the release of read locks. Write locks are preserved.

REPEATABLE READ = 2 Integer

Phantom reads are allowed and serialization anomalies are possible, but dirty reads are not possible. Write locks and read locks are maintained.

SERIALIZABLE = 3 Integer

At the maximum level of 3, dirty reads, nonrepeatable reads, phantom reads, and serialization anomalies are not possible. Statements for read, write, and ranges are locked.

DEFAULT = 4

If no settings are defined, 4 is the Python Psycopg 2 default.

NOTE: The server where PostgreSQL runs will have a default setting of either READ UNCOMMITTED or 0 for the set_isolation_level. It’s not determined by Python or the Psycog 2 adapter.

The list of Psycopg 2 extensions have slightly different values for some isolation levels

• The Python database module 2.0 API specifications differ somewhat, but the explanations are the same. In other words, READ COMMITTED means the same as ISOLATION_LEVEL_READ_COMMITTED.

Import the extensions library for Psycopg2 isolation level Python PostgreSQL

• You have the option of inside IDLE, doing an importation of the Pyscopg 2 extensions library or like this within your script:

Review the extension library’s isolation levels and relevant integers for Psycopg 2

After you complete your extension importation, check the isolation levels.

Help is available for Python extensions

• To be sure of the isolation level, print out the specifics regarding the extensions library for Python using the integral function Help():

View and print the isolation level for the connection of PostgreSQL

• Determine the actual value of the integer for the attribute of the isolation level instance connection for setting Psycopg2 isolation level Python PostgreSQL:

Modify the PostgreSQL connection isolation level setting

Pass the value of the integer to change the PostgreSQL isolation setting:

• Here’s an example using some code to obtain the integer’s value for the isolation level READ_COMMITTED. It also shows code for printing the isolation level is printed and then setting it:

Conclusion

In this tutorial, you learned how to set the isolation levels for Psycopg2 isolation level Python PostgreSQL. Although the PostgreSQL has four isolation levels, Python has five. The definition of the isolation levels are the same by name, yet their integers differ in some cases. It’s important to realize this so that you’ll be able to properly set isolation levels when using PostgreSQL and the pyscopg2 adapter. Use these tips to code accurately using PostgreSQL and Pyscopg 2 adapter today.

Just the Code

Here’s the code for setting the Psycopg2 isolation level Python PostgreSQL.