Understanding Psycopg2: Bridging Python and PostgreSQL

What is Psycopg2:

Psycopg2 is a PostgreSQL database adapter for the Python programming language. It allows developers to interact with PostgreSQL database using Python. It provides functionalities to connect to the PostgreSQL database, execute SQL commands, fetch results from executed SQL commands, manage database transactions, and more.

Querying the Database with Psycopg2:

Querying database can be simplified into following steps

1. Establish the connection to the database
2. Create a cursor object (which will be used to query the Database)
3. Execute SQL query with the help of cursor.
4. Retrieve database result, using fetchone(), fetchmany() or fetchall(), depend upon the use case.
5. Finally, close the connection, to free the allocated resources.

import psycopg2

# 1. Make a connection to the database
conn = psycopg2.connect(
    database="database name",
    user="username",
    password="password",
    host="localhost",
    port="5432" # default port of postgresql service
)

# 2. Create a cursor
with conn.cursor() as cur:

    # 3. Execute your SQL command
    cur.execute("SELECT * FROM table_name")

    # 4. Retrieve the result and print it
    rows = cur.fetchall()

    for row in rows:
        print(row)

# 5. Close the connection
conn.close()

Pro tip: We used context manager to create the cursor object
with conn.cursor() as cur: which will automatically close the cursor, once code goes out of the with block.

A little deep dive with retrieving database result:

Once we execute a SQL command with cur.execute() we can retrieve the results with fetchone(), fetchmany(), or fetchall().

• fetchone(): This method retrieves the next row of a query result set and returns a single sequence, or None if no more rows are available.

# Execute your SQL command
cur.execute("SELECT * FROM table_name")

# Fetch the next row
row = cur.fetchone()

# Print the row
print(row)

• fetchmany([size=cursor.arraysize]): This method retrieves the next set of rows of a query result and returns a list. An empty list is returned when no more rows are available. The number of rows to fetch per call is specified by the size parameter. If it is not given, the cursor’s arraysize determines the number of rows to be fetched.

# Execute your SQL command
cur.execute("SELECT * FROM table_name")

# Fetch the next set of rows
rows = cur.fetchmany(5)

# Print the rows
for row in rows:
    print(row)

• fetchall(): This method retrieves all (remaining) rows of a query result and returns them as a list of tuples. An empty list is returned if no more rows are available.

# Execute your SQL command
cur.execute("SELECT * FROM table_name")

# Fetch all results
rows = cur.fetchall()

# Print the results
for row in rows:
    print(row)

These methods provide different ways to retrieve query results depending on the specific needs.

Working with transactions in Psycopg2:

In order to perform any changes to the database such as insert, update, or delete records, we need to use a database transaction.
Theoretically, Transaction is the set of operations, which are atomic in nature, so, either all the operations of the set will be executed successfully or all will be failed.

Executing Transaction involves following steps:

1. Create a database connection
2. Create a database cursor
3. Execute the insert, update or delete command 4.1. Commit the changes 4.2. If something went wrong in the above steps, do a rollback

# 1. Create database connection
conn = psycopg2.connect(...)
try:
    # 2. Create a cursor
    with conn.cursor() as cur:

        # 3. Execute SQL commands (e.g., INSERT INTO table_name)
        cur.execute("INSERT INTO table_name VALUES (%s, %s)", (value1, value2))

    # 4.1. Commit the transaction
    conn.commit()

except Exception as e:
    # 4.2. Roll back in case of error
    conn.rollback()

    print("An error occurred:", e)

And that’s it! Psycopg2 is your trusty bridge between Python and PostgreSQL. So go forth, create magic, and may your code dance elegantly through the data! 🌟✨