How to leverage SQLAlchemy toolkit to connect to PostgreSQL database

Here in this article we will try to understand about SQLAlchemy toolkit for python programming language based applications and leverage it to connect to PostgreSQL database.

Table of Contents

Test Environment

Fedora 39 Server
Docker 27.3.1
Python 3.12.7

What is SQLAlchemy

SQLAlchemy is an open-source SQL toolkit and Object Relational Mapper (ORM) for the Python programming language. It facilitates communication between Python applications and various relational databases.

High Level Architecture

Features

SQLAlchemy Core: This component provides a powerful and flexible SQL expression language, allowing developers to construct SQL queries programmatically using Python objects, abstracting away the differences between different database systems (e.g., PostgreSQL, MySQL, SQLite).
Object Relational Mapper (ORM): The ORM layer maps Python classes and objects to database tables and rows. This allows developers to interact with the database using familiar Python objects, performing operations like creating, reading, updating, and deleting data without writing raw SQL.
Database Agnostic: SQLAlchemy provides a unified interface for interacting with various database systems, making it easier to switch between different databases without significant code changes.
Enterprise-level Persistence Patterns: It incorporates well-known enterprise-level persistence patterns, designed for efficient and high-performing database access within Python applications.

SQLAlchemy is presented as two distinct APIs, one building on top of the other. These APIs are known as Core and ORM.

If you are interested in watching the video. Here is the YouTube video on the same step by step procedure outlined below.

Procedure

Step1: Install SQLAlchemy and Postgres SQL Driver

As a first step let’s create a python virtual environment and activate it. Once the python virtual environment is activated we can update the pip package if required and install the “sqlalchemy” and “psycopg2” packages.

The psycopg2 package is a PostgreSQL database adapter for Python, widely used for connecting Python applications to PostgreSQL databases. It is compliant with the Python DB API 2.0 specification.

admin@fedser:sqlalchemy$ python -m venv env
admin@fedser:sqlalchemy$ source env/bin/activate
(env) admin@fedser:sqlalchemy$ pip install --upgrade pip
(env) admin@fedser:sqlalchemy$ pip install sqlalchemy
(env) admin@fedser:sqlalchemy$ pip install psycopg2

Now let’s list the installed pip packages.

(env) admin@fedser:sqlalchemy$ pip list installed
Package           Version
----------------- -------
greenlet          3.2.4
pip               25.2
psycopg2          2.9.10
SQLAlchemy        2.0.43
typing_extensions 4.15.0

We can also validate the installed SQLAlchemy version by triggering the following command from the python interactive shell.

(env) admin@fedser:sqlalchemy$ python
Python 3.12.7 (main, Oct  1 2024, 00:00:00) [GCC 13.3.1 20240913 (Red Hat 13.3.1-3)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> 
>>> import sqlalchemy
>>> sqlalchemy.__version__
'2.0.43'

Step2: Setup Postgres Service using docker compose

Ensure that docker service is installed and running.

(env) admin@fedser:sqlalchemy$ sudo systemctl start docker.service 
(env) admin@fedser:sqlalchemy$ sudo systemctl status docker.service

Here is the sample docker compose file which will setup a basic postgres sql database server with “inventorydb” database and user “sa” with password “sa1234” to connect to the database.

Also we are launching an “adminser” service which is basically a PHPAdmin portal for managing the database.

(env) admin@fedser:sqlalchemy$ cat docker-compose.yml 
services:
  db:
    image: postgres:13.22-alpine3.21
    container_name: db
    ports:
      - "5432:5432"
    volumes:
      - pg-data:/var/lib/postgresql/data
    environment:
      POSTGRES_DB: inventorydb
      POSTGRES_USER: sa
      POSTGRES_PASSWORD: sa1234

  adminer:
    image: adminer
    container_name: adminer
    ports:
      - 8080:8080

volumes:
  pg-data:
    driver: local

Let’s instantiate Postgres service along with PHP Admin portal.

(env) admin@fedser:sqlalchemy$ docker compose up -d

Once the services are up and running, you can try to connect to your postgres database from the PHP Admin portal.

URL: http://localhost:8080

Here are the connection details.

System: PostgreSQL
Server: db
Username: sa
Password: sa1234
Database: inventorydb

Step3: Create Engine for DB connection

Here we are going to create an engine object which is responsible for establishing and managing connections to a specific database. It encapsulates the details of the database connection, including the dialect (e.g., PostgreSQL, MySQL, SQLite) and the connection pooling mechanism.

In psycopg2, the isolation level defines how transactions interact with each other in a concurrent environment, ensuring data consistency and preventing various types of anomalies.

SERIALIZABLE: This is the highest isolation level, providing the strongest guarantee of data consistency. It ensures that concurrent transactions produce the same results as if they were executed sequentially, one after another. It prevents dirty reads, non-repeatable reads, phantom reads, and serialization anomalies. This level offers the highest data integrity but can impact performance due to increased contention and potential transaction retries.

(env) admin@fedser:sqlalchemy$ cat sqlalchemydemo.py 
from sqlalchemy import create_engine

engine = create_engine(
    "postgresql+psycopg2://sa:sa1234@localhost:5432/inventorydb",
    isolation_level="SERIALIZABLE",
    echo=True
)

print(engine)

Let’s now execute our script and verify the database engine ojbect that has been created.

(env) admin@fedser:sqlalchemy$ python sqlalchemydemo.py 
Engine(postgresql+psycopg2://sa:***@1234@db:5432/inventorydb)

Step4: Create a DB connection and Execute SQL

Here we are going to create a connection object to connect to our intended database service. Once we have the database connection established we can execute the SQL statements.

Below we are using a textual SQL statement to show “Hello World”. Textual SQL is created with a construct called text().

The text function in SQLAlchemy is used to construct a textual SQL expression. This allows for the execution of raw, conventional SQL queries directly through SQLAlchemy’s engine or connection, bypassing the SQL Expression Language or ORM constructs for that specific query.

(env) admin@fedser:sqlalchemy$ cat sqlalchemydemo.py 
from sqlalchemy import create_engine
from sqlalchemy import text

engine = create_engine(
    "postgresql+psycopg2://sa:sa1234@localhost:5432/inventorydb",
    isolation_level="SERIALIZABLE",
    echo=True
)

print(engine)

with engine.connect() as conn:
    result = conn.execute(text("select 'hello world'"))
    print(result.all())

Now let’s try to execute our script and see it in action. “BEGIG implicit” here means that SQLAlchemy did not actually send any command to the database; it just considers this to be the start of the DBAPI’s implicit transaction.

(env) admin@fedser:sqlalchemy$ python sqlalchemydemo.py 
Engine(postgresql+psycopg2://sa:***@localhost:5432/inventorydb)
2025-10-04 09:54:25,220 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2025-10-04 09:54:25,220 INFO sqlalchemy.engine.Engine [raw sql] {}
2025-10-04 09:54:25,221 INFO sqlalchemy.engine.Engine select current_schema()
2025-10-04 09:54:25,221 INFO sqlalchemy.engine.Engine [raw sql] {}
2025-10-04 09:54:25,221 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2025-10-04 09:54:25,221 INFO sqlalchemy.engine.Engine [raw sql] {}
2025-10-04 09:54:25,222 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-10-04 09:54:25,222 INFO sqlalchemy.engine.Engine select 'hello world'
2025-10-04 09:54:25,222 INFO sqlalchemy.engine.Engine [generated in 0.00012s] {}
[('hello world',)]
2025-10-04 09:54:25,223 INFO sqlalchemy.engine.Engine ROLLBACK

Step5: Update script and commit changes

The context manager creates a database connection and executes the operation in a transaction. The default behavior of the Python DBAPI is that a transaction is always in progress; when the connection is released, a ROLLBACK is emitted to end the transaction. The transaction is not committed automatically; if we want to commit data we need to call Connection.commit() as shown below.

So here we are executing a create and insert statement and then calling the commit() to persist our transaction.

(env) admin@fedser:sqlalchemy$ cat sqlalchemydemo.py 
from sqlalchemy import create_engine
from sqlalchemy import text

engine = create_engine(
    "postgresql+psycopg2://sa:sa1234@localhost:5432/inventorydb",
    isolation_level="SERIALIZABLE",
    echo=True
)

print(engine)

with engine.connect() as conn:
    conn.execute(text("CREATE TABLE coordinates (x int, y int)"))
    conn.execute(
        text("INSERT INTO coordinates (x, y) values (:x, :y)"),
        [{"x": 1, "y": 1}, {"x": 2, "y": 4}]
    )
    conn.commit()

(env) admin@fedser:sqlalchemy$ python sqlalchemydemo.py 
Engine(postgresql+psycopg2://sa:***@localhost:5432/inventorydb)
2025-10-04 09:59:34,422 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2025-10-04 09:59:34,422 INFO sqlalchemy.engine.Engine [raw sql] {}
2025-10-04 09:59:34,423 INFO sqlalchemy.engine.Engine select current_schema()
2025-10-04 09:59:34,423 INFO sqlalchemy.engine.Engine [raw sql] {}
2025-10-04 09:59:34,424 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2025-10-04 09:59:34,424 INFO sqlalchemy.engine.Engine [raw sql] {}
2025-10-04 09:59:34,425 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-10-04 09:59:34,425 INFO sqlalchemy.engine.Engine CREATE TABLE coordinates (x int, y int)
2025-10-04 09:59:34,425 INFO sqlalchemy.engine.Engine [generated in 0.00013s] {}
2025-10-04 09:59:34,427 INFO sqlalchemy.engine.Engine INSERT INTO coordinates (x, y) values (%(x)s, %(y)s)
2025-10-04 09:59:34,427 INFO sqlalchemy.engine.Engine [generated in 0.00010s] [{'x': 1, 'y': 1}, {'x': 2, 'y': 4}]
2025-10-04 09:59:34,427 INFO sqlalchemy.engine.Engine COMMIT

Step6: Update script and read table

Here we are just updating our script to take an input action argument to “create” or “read” database table. Once the updates are completed we can “read” the table by passing the “read” action as shown below.

(env) admin@fedser:sqlalchemy$ cat sqlalchemydemo.py 
from sqlalchemy import create_engine
from sqlalchemy import text
import sys

action = sys.argv[1]

engine = create_engine(
    "postgresql+psycopg2://sa:sa1234@localhost:5432/inventorydb",
    isolation_level="SERIALIZABLE",
    echo=True
)

print(engine)

def create_table():
    with engine.connect() as conn:
        conn.execute(text("CREATE TABLE coordinates (x int, y int)"))
        conn.execute(
            text("INSERT INTO coordinates (x, y) values (:x, :y)"),
            [{"x": 1, "y": 1}, {"x": 2, "y": 4}]
        )
        conn.commit()

def read_table():
    with engine.connect() as conn:
        result = conn.execute(text("SELECT x, y from coordinates"))
        for row in result:
            print("x:", row.x, "y:", row.y)

if action == "create":
    create_table()
else:
    read_table()

(env) admin@fedser:sqlalchemy$ python sqlalchemydemo.py read
Engine(postgresql+psycopg2://sa:***@localhost:5432/inventorydb)
2025-10-04 10:27:25,775 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2025-10-04 10:27:25,775 INFO sqlalchemy.engine.Engine [raw sql] {}
2025-10-04 10:27:25,776 INFO sqlalchemy.engine.Engine select current_schema()
2025-10-04 10:27:25,776 INFO sqlalchemy.engine.Engine [raw sql] {}
2025-10-04 10:27:25,776 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2025-10-04 10:27:25,776 INFO sqlalchemy.engine.Engine [raw sql] {}
2025-10-04 10:27:25,777 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-10-04 10:27:25,777 INFO sqlalchemy.engine.Engine SELECT x, y from coordinates
2025-10-04 10:27:25,777 INFO sqlalchemy.engine.Engine [generated in 0.00012s] {}
x: 1 y: 1
x: 2 y: 4
2025-10-04 10:27:25,778 INFO sqlalchemy.engine.Engine ROLLBACK

We have just seen how easily we can establish a connection to backend database using the SQLAlchemy toolkit and used very basic example to show how we can establish a connection to database and execute some queires but there is much more that the SQL Alchemy provides for the developer productivity.

Hope you enjoyed reading this article. Thank you..

How to leverage SQLAlchemy toolkit to connect to PostgreSQL database