SQLAlchemy Essentials: A Beginner’s Roadmap

Get started with SQLAlchemy, a powerful Python ORM and SQL toolkit for managing databases, offering flexibility and much more.

Nikhil Yadav

Published: December 4, 2024

Key takeaways

SQLAlchemy combines an ORM and SQL toolkit, allowing developers to work with both Python objects and raw SQL.
Key components include the Engine for database connections, Session for transactions, and Declarative Base for defining models.
SQLAlchemy’s flexibility and support for multiple databases make it ideal for scalable applications, offering full control over query performance.

Introduction

Whenever one needs to interact with the databases in Python, more often than not, SQLAlchemy is what is used. This is a more flexible framework which is also popular particularly for wrapping the relational databases into Python objects. Not only is SQLAlchemy an ORM tool, but it’s also a powerful SQL tool that provides full control to the developer regarding how he/she wants to interact with databases.

This guide will introduce you to SQLAlchemy especially if you are a total beginner in the tool and will also cover some of their most frequent applications.

What is SQLAlchemy?

The Object-Relational Mapping (ORM) module and Python SQL toolkit SQLAlchemy enable developers to avoid usage of plain SQL queries and rather work with python objects for databases. SQLAlchemy offers two essential parts: SQLAlchemy offers two essential parts:

SQL Expression Language: A nicely designed SQL toolkit complemented with the ability to provide direct SQL manipulation in a Pythonic way.

For example the Object-Relational Mapper, or ORM as it stands for, is an edited Pythons tool which allows you to work with the database using only Python objects and Python classes mapped to database tables.

Why Choose SQLAlchemy?

Out of the numerous Python database packages, SQLAlchemy has some benefits which have made it to feature among the best.

Flexibility: It can work at the level of concrete queries in SQL and work at the abstract level of ORM. You can write both ‘low-level’ raw SQL as well as ‘higher-level’ ORM offered by your framework.

Database Agnostic: Some of the supported databases are PostgreSQL, MySQL, SQLite, Oracle, and more more databases are supported by SQLAlchemy. This allows for movement between databases as required, and could be adapted to work in a like manner for document management systems as well.

Declarative Syntax: One of the major strength of SQLAlchemy’s ORM is the declarative syntax that helps in defining models and relationship.

Performance: SQLAlchemy exposes a lot of technical credit over the query performance, unlike certain other ORMs, so the developer can perform the optimizations by their own.

Installing SQLAlchemy

To get started with SQLAlchemy, you can install it via pip:

pip install sqlalchemy

1	pip install sqlalchemy

If you also need a database driver (e.g., psycopg2 for PostgreSQL or pymysql for MySQL), you can install it alongside SQLAlchemy.

Core Concepts of SQLAlchemy

Before getting into the code, there are some important basic concepts of SQLAlchemy that needs to be understood.

Engine: This is the starting point of every application that is built on SQLAlchemy. The engine is a connector between your application and the database while having the connection pool in its disposal.

Session: The session facilitates the communication that results in managing the database. With it you may control the transactions and add, modify or delete things from the database.

Declarative Base: Your whole ORM models are based on this class. It provides the way to relate Python classes with the Relational Database.

Model: The term used to refer to a Python class, which represents a database table is known as Model. To correlate the rows in the table to the concept above, it should be noted that each instance of the class corresponds to a row in the table.

Getting Started with SQLAlchemy ORM

Let’s now go over a simple example that shows you how to utilize the ORM features of SQLAlchemy.

from sqlalchemy import create_engine

# Replace the connection string with your database URL
engine = create_engine('sqlite:///example.db')

from sqlalchemy import create_engine

# Replace the connection string with your database URL

engine = create_engine('sqlite:///example.db')

Defining Models

By building Python classes that derive from the declarative_base() class, you define your database tables.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class User(Base):
   __tablename__ = 'users'
id = Column(Integer, primary_key=True)   
name = Column(String)   age = Column(Integer)

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String

Base = declarative_base()

class User(Base):

__tablename__ = 'users'

id = Column(Integer, primary_key=True)

name = Column(String) age = Column(Integer)

In this instance, a table named users and a User model are defined in tandem. A class property is used to represent each column in the table.

Creating the Tables

After defining your models, you can make the relevant database tables:

Base.metadata.create_all(engine)

1	Base.metadata.create_all(engine)

This will produce the SQL required to establish the user’s table.

Working with Sessions

Using the Session object, database interactions are possible. You can add, query, and update records in the following ways:

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

# Adding a new user
new_user = User(name="John Doe", age=30)
session.add(new_user)
session.commit()

# Querying users
users = session.query(User).all()
for user in users:
   print(user.name, user.age)

# Updating a user
user_to_update = session.query(User).filter_by(name="John Doe").first()
user_to_update.age = 31
session.commit()

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)

session = Session()

# Adding a new user

new_user = User(name="John Doe", age=30)

session.add(new_user)

session.commit()

# Querying users

users = session.query(User).all()

for user in users:

print(user.name, user.age)

# Updating a user

user_to_update = session.query(User).filter_by(name="John Doe").first()

user_to_update.age = 31

session.commit()

Handling Relationships

Define relationships between models with SQLAlchemy, one of its great features. A one-to-many relationship between a User and a Post model, for instance, can be defined as follows:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Post(Base):
   __tablename__ = 'posts'

   id = Column(Integer, primary_key=True)
   title = Column(String)
   content = Column(String)
   user_id = Column(Integer, ForeignKey('users.id'))

   user = relationship("User", back_populates="posts")

User.posts = relationship("Post", order_by=Post.id, back_populates="user")

from sqlalchemy import ForeignKey

from sqlalchemy.orm import relationship

class Post(Base):

__tablename__ = 'posts'

id = Column(Integer, primary_key=True)

title = Column(String)

content = Column(String)

user_id = Column(Integer, ForeignKey('users.id'))

user = relationship("User", back_populates="posts")

User.posts = relationship("Post", order_by=Post.id, back_populates="user")

By doing this, a relationship is established between the user and the post, enabling easy access to the user’s postings and vice versa.

SQLAlchemy vs. Other ORMs

SQLite can also be compared to other ORM like SQLAlchemy, Django, ORM and Peewee. Here’s how it stacks up:Here’s how it stacks up:

Flexibility: SQLAlchemy is more versatile when compared to Django’s ORM because it allows you to write your own SQL query as opposed to limiting you to only pre-defined queries.

Learning Curve: That is why SQLAlchemy is considered to be less easy to learn although it is more powerful than other solutions; it is flexible and is based on both ORM and SQL Expression Language.

Community: It should also be noted that the SQLALchemy has both a large and active community and the project has existed for many years so it is very stable and well documented.

Conclusion

Starting from simple operations with the ORM layer up to complex database work-flow SQLAlchemy is a sophisticated and flexible toolset. Because of the ability to use it as an ORM and SQL tool, it is a favorite among developers who need full control of their database and wishing to work in the object-oriented paradigm of Python.

SQLAlchemy can scale up if you’re using PostgreSQL for a huge application, or you’re using SQLite for a miniature application. If you learn about the fundamental concepts and how they can be harnessed and expanded to take advantage of Python’s more advanced features, it may be possible to write more structured, mangesible and effective database code in Python.
To learn more about SQLAlchemy and its capabilities, check out their official website here.
For additional insightful articles and information, please reach out to us.