Blog Details

PostgreSQL and Python form an excellent partnership for developers. PostgreSQL, an influential open-source database system, becomes a sturdy base for handling data-intensive applications when combined with Python. This guide will delve into the seamless integration of PostgreSQL and Python, covering optimal connection methods and demonstrating how to execute CRUD operations (Create, Read, Update, Delete) efficiently within PostgreSQL using Python.

Psycopg2

Psycopg2, a PostgreSQL adapter for Python, implements the Python Database API Specification v2.0, acting as a bridge between Python applications and PostgreSQL databases. It leverages the libpq library, the official PostgreSQL C interface, to facilitate efficient communication. Psycopg2 provides a robust set of features, including transaction management, and support for PostgreSQL-specific data types. Its implementation of the Python DB API ensures seamless integration, enabling developers to execute SQL queries and transactions with precision in Python applications.

Environment setup 

Setup docker container

We are using the official Ubuntu 22.04 docker image to run the container

docker run -it --name pg_python_container ubuntu:22.04

Update the apt package manager

apt-get update

Install Required packages for PostgreSQL

apt-get install wget lsb-release gnupg2 python3-pip

Install PostgreSQL

Please refer to this link for official PostgreSQL Debian packages: HERE 

psycopg2 vs psycopg2-binary

pip3 install psycopg2

When we run this command psycopg2 and its dependencies will be installed, which may include building and linking against the PostgreSQL C library (libpq). It may require the presence of PostgreSQL development headers and libraries on your system. If you encounter issues during installation related to missing PostgreSQL dependencies, you might need to install them separately.

pip3 install psycopg2-binary

This command installs a pre-compiled binary distribution of psycopg2, including its dependencies. It does not require PostgreSQL development headers and libraries, as everything needed for the installation is bundled in the binary distribution. so this is a faster way to install psycopg2

Template of Python program to initiate connection with PostgreSQL via psycopg2

This Python code connects to a PostgreSQL database using psycopg2, executes a SQL query through a cursor, commits changes, and then closes the database connection. Adjust credentials and queries as needed.

Cursor

A cursor enables the execution of SQL queries and facilitates the traversal of result sets. It acts as a pointer to a specific location within the result set, allowing fetching of rows and execution of database commands.

# Import psycopg2 package
import psycopg2

# Replace these with your database credentials
dbname = "your_database_name"
user = "your_username"
password = "your_password"
host = "your_host"
port = "your_port"

# Establish a connection to the PostgreSQL database
conn = psycopg2.connect(dbname=dbname, user=user, password=password, host=host, port=port)

# Create a cursor object to execute SQL queries
cursor = conn.cursor()

# Executing a query
cursor.execute(query)

# Commit the changes inside the database and close the connection
conn.commit()
conn.close()

Insert data into the table

We have defined sample data and inserted it into a table using a loop. It leverages an SQL INSERT query with placeholders to dynamically insert data into the specified database table.

# Sample data to be inserted
sample_data = [
    ("John Doe", 25),
    ("Jane Smith", 30),
    ("Bob Johnson", 22)
]
# Loop through the sample data and insert it into the table
for data in sample_data:
    insert_query = "INSERT INTO example_table (name, age) VALUES (%s, %s);"
    cursor.execute(insert_query, data)

Select data from the table

We have executed a SELECT query on the “example_table,” fetches all rows from the result set, and prints the selected data, using for loop

# Select all data from the example_table
select_query = "SELECT * FROM example_table;"
cursor.execute(select_query)

# Fetch e all rows from the result set
rows = cursor.fetchall()

# Display the selected data
for row in rows:
    print(row)

Fetchone

Used to fetch/select only first row

select_query = "SELECT * FROM example_table;" 
cursor.execute(select_query) 
row = cursor.fetchone() 
print(row)

Update data inside a table

In this example, we update a row in the “example_table” by modifying the age for the specified name. It utilizes an SQL UPDATE query with placeholders and executes the update operation.

# Data for updating a row
update_data = (26, "John Doe") 

# Update the age for the specified name in the example_table
update_query = "UPDATE example_table SET age = %s WHERE name = %s;"
cursor.execute(update_query, update_data)

Delete a row

We are deleting a row in the “example_table” based on the specified name. The delete_data tuple, created with a trailing comma, is used to pass a single-element tuple to the SQL DELETE query executed by cursor.execute().

# Data for deleting a row
delete_data = ("John Doe",) 

# Delete the row for the specified name in the example_table
delete_query = "DELETE FROM example_table WHERE name = %s;"
cursor.execute(delete_query, delete_data)

All CRUD operations in one example 

A simple example of establishing a connection to a PostgreSQL database is creating a table ‘students’, inserting records, updating one, deleting another, and prints all records, and handling potential errors and transactions.

import psycopg2

# Replace these with your database credentials
dbname = "postgres"
user = "postgres"
password = "secure_password"
host = "localhost"
port = "5432"

# Establish a connection to the PostgreSQL database
conn = psycopg2.connect(dbname=dbname, user=user, password=password, host=host, port=port)

# Create a cursor object to execute SQL queries
cursor = conn.cursor()

try:
  # Begin the transaction. It ensures that a sequence of SQL commands is treated as a single transaction
  conn.autocommit = False

  # Create the 'students' table
  create_table_query = """
    CREATE TABLE IF NOT EXISTS students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age INT );"""
  cursor.execute(create_table_query)

  # Insert the records
  insert_data = [
    ("John Doe", 25),
    ("Jane Smith", 30),
    ("Bob Johnson", 22),
    ("Alice Brown", 28),
    ("Charlie Davis", 21)]
  for data in insert_data:
    insert_query = "INSERT INTO students (name, age) VALUES (%s, %s);"
    cursor.execute(insert_query, data)

  # Update the record
  update_query = "UPDATE students SET age = %s WHERE name = %s;"
  update_data = (26, "John Doe")
  cursor.execute(update_query, update_data)

  # Delete the record
  delete_query = "DELETE FROM students WHERE name = %s;"
  cursor.execute(delete_query, ("Charlie Davis",))

  # Commit the transaction
  conn.commit()

  # Select all records
  select_all_query = "SELECT * FROM students;"
  cursor.execute(select_all_query)

  # Fetch all the rows
  all_records = cursor.fetchall()
  print("All Records:")

  # Print all the data to the terminal
  for record in all_records:
    print(record)

except Exception as e:
  # Rollback the transaction in case of an error
  conn.rollback()
  print("Error:", e)

finally:
  # Close the connection
  conn.close()

sqlalchemy

SQLAlchemy is a Python library facilitating interaction with relational databases. It offers an Object-Relational Mapping (ORM) system and a SQL expression language, providing a powerful and flexible toolkit for developers to work with databases using Python classes and SQL queries. In upcoming blogs, we will go over how we can use this library to interact with PostgreSQL

In conclusion, PostgreSQL’s seamless integration with Python, exemplified through the psycopg2 library, empowers developers in building robust applications. This guide has explored the intricacies of connecting, querying, and managing transactions, showcasing the synergy between Python and PostgreSQL for efficient and scalable data-driven solutions.