SQLAlchemy ORM - Database operations in a pythonic way

Imagine performing database operations without ever touching SQL—or having your Python objects seamlessly mirror your database structure. Sounds too good to be true? That’s exactly what SQLAlchemy ORM brings to the table!

In this blog, we’ll dive into the basics of SQLAlchemy ORM, explore its features, and learn how to perform CRUD (Create, Read, Update, Delete) operations in pythonic way.

What is SQLAlchemy ORM?

SQLAlchemy ORM (Object-Relational Mapping) is a sophisticated, object-oriented tool within the SQLAlchemy library. It enables developers to work with relational databases by using Python objects instead of writing raw SQL queries. The ORM connects database tables to Python classes and maps table rows to instances of these classes. This approach integrates relational database operations with Python's object-oriented programming, simplifying CRUD (Create, Read, Update, Delete) operations through an intuitive, object-based syntax.

ORM (Object-Relational Mapping), a programming technique used to bridge the gap between object-oriented programming languages (like Python, Java, or Ruby) and relational databases (like MySQL, PostgreSQL, or SQLite).

How It Works :

Mapping: ORM tools map database tables to programming language classes and columns to class attributes.

Query Translation: The ORM translates object-oriented method calls into SQL queries and executes them

in the database.

Object Representation: Results from the database are represented as objects in the programming language,

simplifying data manipulation

 Advantages of SQLAlchemy ORM :

   Productivity:  Reduces the need to write raw SQL for common operations.

   Maintainability: Keeps database logic encapsulated in Python classes, improving code readability

   Database Independence: Queries are abstracted, making it easier to switch database backends.

   Relationships: Simplifies managing complex database relationships using object-oriented syntax.

In summary, ORM simplifies database interactions by letting developers work with objects instead of writing SQL, enhancing productivity and maintainability.

Installing SQLAlchemy

Now, let’s look into environmental setup required to use SQLAlchemy. 

The easiest way to install SQLAlchemy is by using Python Package Manager, pip.

pip install sqlalchemy

SQLAlchemy can also be installed from conda terminal using the below command

conda install -c anaconda sqlalchemy

To check whether the SQLAlchemy is installed or not and to know the version, execute below commands

import sqlalchemy
print(sqlalchemy._version_)

After successful installation of SQLAlchemy, we can proceed to database operations by connecting SQLAlchemy

SQLAlchemy is designed to operate with a DBAPI implementation built for a particular database. It uses dialect system to communicate with various types of DBAPI implementations and databases.

Here’s a step-by-step guide to connect to an existing database, and performing CRUD operations using SQLAlchemy.

Basic Workflow of SQLAlchemy ORM

   Create a Database Connection: Establish a connection to the database using the Engine.

   Define Classes (Models):  Define Python classes that represent database tables.

   Configure the ORM: Use a Session object to interact with the database.

   Perform CRUD Operations: Use Python objects to create, read, update, and delete rows in the       

                                                     database.

Create a database connection using create_engine () module imported from SQLAlchemy by providing database URL/Connection String parameter formatted as below:

        " dialect+driver://username:password@host:port/database "

Using the above format, we can connect to any database supported by SQLAlchemy ORM.

For Example:

  # mysql

            mysql_db_url = “mysql+pymysql://<username>:<password>@<host>:<port>/<dbname>”

   # postgresql

          postgresql_db_url ="postgresql+psycopg2://<username>:<password>@<host>:<port>/<dbname>"

Here, let’s Set Up the Connection to the PostgreSQL Server using psycopg2 driver.

# Install required libraries and import necessary modules
pip install sqlalchemy, psycopg2
from sqlalchemy import create_engine

# Define database params required in Connection String or URL
# Assign existing database name to dbname key that you want to work
db_params = {
 'dbname': 'training',
 'user': 'postgres',
 'password': '12345'
 'host': 'localhost',  # or your PostgreSQL server host
 'port': '5432'        # default PostgreSQL port
}

#Replace db_params with your server credentials. 

# Connection to the PostgreSQL server
engine = create_engine(f'postgresql+psycopg2://{db_params["user"]}:{db_params["password"]}@{db_params["host"]}:{db_params["port"]}/{db_params["dbname"]}')

Check the connection is successful or not using try-except block to handle exceptions if connection failed

Psycopg2 is a popular PostgreSQL adapter for Python that allows efficient interaction between Python applications and PostgreSQL databases. It supports advanced features like connection pooling, asynchronous queries, and transactions, making it suitable for both simple and complex database operations

The create_engine function in SQLAlchemy establishes a connection to a database by generating an SQLAlchemy Engine object, which serves as the interface for executing SQL queries and database operations. It takes a database URL and configuration options as parameters, enabling flexible interaction with various database backends using SQLAlchemy’s ORM or Core functionalities.

Key Points:

create_engine:

• This is a SQLAlchemy function used to establish a connection with a database.

• It generates an Engine object that serves as the interface to the database.

f'postgresql+psycopg2://...':

• Specifies the database dialect (postgresql) and the driver (psycopg2) for the connection.

• The connection string is formatted as a Python f-string, dynamically incorporating parameters like the user, password, host, port, and database name.

  
  

CREATE Table "students " in the "training" database

Key Points in Table Creation:

from sqlalchemy.orm import declarative_base

Purpose: This import provides the declarative_base function, which is used to create a base class for

defining ORM (Object-Relational Mapping) models in SQLAlchemy.

How It Works:

You call declarative_base () to generate a base class, typically named Base.

This base class is then inherited by all ORM models (Python classes) that represent database tables.

   from sqlalchemy import Column, Integer, String, Float

Purpose: These imports allow you to define the schema for database tables by specifying the columns and

their data types.

How They Work:

Column: Used to define individual columns in a database table. It takes the column’s data type and

additional options like primary_key, autoincrement, nullable, etc.

Integer: Specifies that the column holds integer values.

String: Specifies that the column holds string/text data.

Float: Specifies that the column holds floating-point numbers.

autoincrement=True : This option automatically generates a unique, incrementing value for the column

with each new record. Typically used for primary key columns to eliminate the

need for manual value assignment.

The statement Base.metadata.create_all(engine) is used in SQLAlchemy to create all database tables defined by ORM models. Here's how it works:

Base.metadata:

• This is a container that holds information about all the table schemas defined in your ORM models.

• When you define a model class (e.g., Student) using SQLAlchemy and inherit from Base, the metadata for those tables is automatically added to Base.metadata.

create_all(engine):

• This method generates the SQL statements required to create the tables in the database and executes them using the provided engine.

• The engine is the connection object that specifies the database to which the tables

  
  

You can query the database metadata using SQLAlchemy's inspect module or you can check in pgadmin

INSERT rows in to the "students" table

# Import sessionmaker module from ORM
from sqlalchemy.orm import sessionmaker
# Create a session
Session = sessionmaker(bind=engine)
session = Session()
# Add a new student
new_stu1 = Student(name='John Doe',age=15,grade='Sophomore',gpa=4.2)
session.add(new_stu1)
session.commit()

Key Points:

 from sqlalchemy.orm import sessionmaker

• The sessionmaker factory is used to create session objects, which manage interactions with the database.

• Sessions are used to interact with the database, enabling operations like querying, adding, updating, and deleting records.

  
  

Session = sessionmaker(bind=engine)

• A factory function to configure and create sessions.

• The bind=engine parameter associates the session with the database connection (engine).

  
  

 session = Session()

Creates a new session instance. This session acts as a transactional workspace for interacting with the database

 session.add()

The session.add() method queues the new_stu1 object for insertion into the database.

session.commit()

The session.commit() finalizes the transaction and sends the queued SQL INSERT statement to the database.

Inserting Multiple rows in to the table using add_all()

#inserting multiple rows
new_stu2 = Student(name='Robert Sen',age=14,grade='Freshman',gpa=4.8)
new_stu3 = Student(name='Shawn Kim',age=16,grade='Junior',gpa=6.2)
session.add_all([new_stu2,new_stu3]) #queues all the objects for insertion
session.commit()

Read / Query the Table to retrieve rows

session.query(Student).all(): Retrieves all rows from the students table and maps them to Student objects.

for student in students: Iterates through each Student object in the students list.

print() Statement: Prints the attributes (id, name, age, salary) of each Student object.

Always ensure you reference the individual object (student) inside the loop, not the list (students).

SQLAlchemy ORM maps database rows to Python objects, making it easy to work with the results using object attributes.

Filtering to retrieve specific rows

UPDATE the row if there is only one student with name 'Shawn Kim' using one_or_none() filter option

DELETE a row from the table

Conclusion:

SQLAlchemy ORM is a tool that lets you interact with databases in Python by using objects instead of writing raw SQL. It maps database tables to Python classes, and rows in the table become instances of these classes. This makes it easy to perform CRUD operations (Create, Read, Update, Delete) using Python’s object-oriented syntax. SQLAlchemy ORM is ideal for developers who want a simplified, high-level approach to managing relational data in their applications.

SQLAlchemy ORM offers much more than basic CRUD operations, including managing relationships between tables, optimizing queries with lazy or eager loading, and handling transactions and schema migrations seamlessly. It supports advanced features like custom data types, polymorphic queries, event listeners.

SQLAlchemy ORM may seem complex at first due to the learning curve involved in understanding its abstraction over raw SQL. However, it is built to streamline database interactions, offering a Pythonic approach to managing tables and queries, which becomes easier to use once the fundamentals are understood. Starting with core concepts like defining models, executing simple CRUD operations, and working with relationships allows beginners to gradually build confidence without feeling overwhelmed by its advanced capabilities.