How to Use SQL Databases in Python with SQLAlchemy

Published on June 9, 2022•Updated on November 21, 2023

Introduction

SQLAlchemy is a Python library that allows your applications to interact with databases. It supports several database engines such as MySQL, PostgreSQL, Oracle, Microsoft SQL Server, and SQLite. It comes with an Object Relational Mapper (ORM), which provides an abstract interface to interact with the database engine using Python classes instead of writing SQL statements.

The ORM translates Python classes and function calls to SQL statements. With the ORM, you declare relational database tables using Python classes, and the instances of this class represent a row in the table. This approach makes it easier for developers to interact with relational databases by abstracting away some complexities of SQL. It helps reduce the development time and makes database logic less error-prone.

This article explains the basics of SQLAlchemy, such as performing create, read, update and delete operations, querying filtered data, building relationships, and connecting to database engines such as MySQL and PostgreSQL.

Prerequisites

Deploy a new Ubuntu 20.04 Vultr cloud server.
Update the server.
Create a non-root user with sudo privileges.
Log in to your server as a non-root user.

Set Up the Environment

Install the SQLAlchemy library and create an SQLite database with a table to store students with two columns of data: name and age. This environment and database serve as a test bench for performing operations below.

Install the SQLAlchemy library.

$ pip install sqlalchemy

Set up a new project directory.

$ mkdir ~/sqlalchemy_demo

Switch to the project directory.

$ cd ~/sqlalchemy_demo

Using a text editor, create a new file named models.py.

$ nano models.py

Add the following contents to the file.

from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, String, Integer

Base = declarative_base()

class Student(Base):
    __tablename__ = 'students'

    id = Column(Integer(), auto_increment=True, primary_key=True)
    name = Column(String(50), nullable=False)
    age = Column(Integer(), nullable=False)

    def __repr__(self):
        return f'<Student name="{self.name}" age={self.age}>'

A model is a Python class mapped to a table. The above code imports the required classes, such as the base class, to declare a model named Student with database metadata such as the table name and the columns.

Create a new file named main.py.

$ nano main.py

Add the following contents to the file.

import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

database_dir = os.path.abspath(os.path.dirname(__file__))
database_uri = f'sqlite:///{database_dir}/demo.db'

Session = sessionmaker()

engine = create_engine(database_uri)
session = Session(bind=engine)

An engine in SQLAlchemy establishes a connection with the database, and the session allows you to interact with the database. The above code imports the required classes to create the engine and session object. You declared the connection string as demo.db stored in the project directory, which is an SQLite database.

Open the Python Console.

$ python

Import the necessary modules.

>>> from main import engine
>>> from models import Base

The above commands import the engine object from the main.py file and Base class from the models.py file. The Base class allows initializing and creating the database with all the declared models.

Create the database.

>>> Base.metadata.create_all(engine)

The above command creates the demo.db file in the project directory with a single table named students declared as the Student model.

Exit the Python console.

>>> exit()

Perform Operations

The Object Relational Mapper (ORM) allows you to manipulate the database using the session object defined in the main.py file. This section explains how you can perform create, update, read and delete operations on the Student model.

Open the Python Console.

$ python

Import the necessary modules.

>>> from main import session
>>> from models import Student

Create a Row

Create an instance of the model class.

>>> new_student = Student(name='Student 1', age=18)

Stage and commit changes.

>>> session.add(new_student)
>>> session.commit()

The add() method lets you stage changes, and the commit() method identifies the task and executes an SQL statement to make the required changes. You can add multiple objects using the add() method before committing changes.

Verify changes.

>>> session.query(Student).all()

Output.

[<Student name="Student 1" age=18>]

Read a Row

Fetch the object.

>>> fetched_student = session.query(Student).first()

Read the object contents.

>>> print(fetched_student.name)
>>> print(fetched_student.age)

Output.

Student 1
18

Update a Row

Fetch the object.

>>> fetched_student = session.query(Student).first()

Update the object attributes.

>>> fetched_student.age = 20

Stage and commit changes.

>>> session.add(fetched_student)
>>> session.commit()

Verify changes.

>>> print(fetched_student.name)
>>> print(fetched_student.age)

Output.

Student 1
20

You can stage multiple objects together using the add_all() method. It accepts a Python list containing model instance objects.

Delete a Row

Fetch the object.

>>> fetched_student = session.query(Student).first()

Stage and commit changes.

>>> session.delete(fetched_student)
>>> session.commit()

Verify changes.

>>> session.query(Student).all()

Expected Output.

[]

Query Filtered Data

The query method allows you to filter rows from the database. It takes the model class as an input to select the table. This section explains the basic queries using single and multiple conditional statements. Refer to the SQLAlchemy ORM query documentation to learn more about the query() method.

Populate the database.

>>> from random import randrange
>>> students = [Student(name=f'Student {x}', age=randrange(20, 35)) for x in range(1, 21)]
>>> session.add_all(students)
>>> session.commit()

The above statements import the rangrange function to randomize the age of a student object. The loop encapsulated inside the list creates 20 separate student objects named Student, followed by the loop index.

The filter() method allows you the match the rows in the selected table by comparing it against the conditional arguments. You can pass the conditional arguments as parameters to this method like an if statement. It supports all Python conditional operators such as ==, !=, >, <, =>, =< and so on.

Query data using a single condition.

>> session.query(Student).filter(Student.id < 7).all()

The above statement queries the database to fetch all the student objects with ID less than 7.

Output.

<Student name="Student 1" age=34>, <Student name="Student 2" age=25>, <Student name="Student 3" age=32>, <Student name="Student 4" age=25>, <Student name="Student 5" age=21>, <Student name="Student 6" age=33>]

Query data using multiple conditions.

>> session.query(Student).filter(Student.id > 5, Student.id < 15).all()

The above statement queries the database to fetch all the student objects with IDs more than 5 and less than 15.

Output.

[<Student name="Student 6" age=33>, <Student name="Student 7" age=27>, <Student name="Student 8" age=33>, <Student name="Student 9" age=28>, <Student name="Student 10" age=33>, <Student name="Student 11" age=30>, <Student name="Student 12" age=23>, <Student name="Student 13" age=22>, <Student name="Student 14" age=33>]

By default, the query returns a list of objects. If you want to query the database for a single object, use the first() method.

Query a single object.

>>> session.query(Student).filter(Student.id==1).first()

Output.

<Student name="Student 1" age=34>

The previous section explained the steps to delete an object. Knowing the basics of querying filtered data, you can also delete objects using the delete() method on query objects. You can use the session.query(Student).filter(Student.id == 1).delete() statement to delete a student object with ID set to 1. This eliminates the steps to stage and commit the changes.

Build SQL Relationships

SQL relationships allow data in different tables to interlink with each other. The rows in different tables use a foreign key, which is a column in one table that refers to the primary key in another table. The table with the foreign key is the child table, and the linked table is the parent table.

Types of SQL relationships:

One to Many - row in the parent table linked to multiple rows in the child table.
Many to One - multiple rows in the parent table linked to a single row in the child table.
One to One - row in the parent table linked to a single row in the child table.
Many to Many - multiple rows in the parent table linked to multiple rows in the child table.

The ORM provides a relationship() construct available in the sqlalchemy.orm library. This construct defines a linkage between two models and enables you to fetch the linked objects easily. Visit the Relationship API documentation to find out all features.

One to Many

This relationship occurs when you place a foreign key column in the child table referring to the parent table's primary key column. Refer to the following code block to establish a one-to-many relationship.

from sqlalchemy.orm import declarative_base, relationship
from sqlalchemy import Column, String, Integer, ForeignKey

Base = declarative_base()

class Parent(Base):
    __tablename__ = 'parent'

    id = Column(Integer(), auto_increment=True, primary_key=True)
    name = Column(String(50), nullable=False)

    children = relationship("Child", back_populates="parent")

class Child(Base):
    __tablename__ = 'child'

    id = Column(Integer(), auto_increment=True, primary_key=True)
    name = Column(String(50), nullable=False)
    
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship("Parent", back_populates="children")

The above code block declares two models: Parent and Child. The child model contains a column that refers to the parent model's primary key column. Both models have a relationship() column.

Many to One

This relationship occurs when you place a foreign key column in the parent table referring to the child table's primary key column. Refer to the following code block to establish a one-to-many relationship.

from sqlalchemy.orm import declarative_base, relationship
from sqlalchemy import Column, String, Integer, ForeignKey

Base = declarative_base()

class Parent(Base):
    __tablename__ = 'parent'

    id = Column(Integer(), auto_increment=True, primary_key=True)
    name = Column(String(50), nullable=False)

    child_id = Column(Integer(), ForeignKey('child.id'))
    child =  relationship('Child', back_populates="parents")

class Child(Base):
    __tablename__ = 'child'

    id = Column(Integer(), auto_increment=True, primary_key=True)
    name = Column(String(50), nullable=False)
    
    parents = relationship("Parent", back_populates="child", uselist=False)

The above code block declares two models: Parent and Child. The child model contains a column that refers to the parent model's primary key column. Both models have a relationship() column. The relationship() column in the Child model has an extra parameter to set uselist to False, preventing the ORM from returning a list of objects upon query execution.

One to One

from sqlalchemy.orm import declarative_base, relationship
from sqlalchemy import Column, String, Integer, ForeignKey

Base = declarative_base()

class Parent(Base):
    __tablename__ = 'parent'

    id = Column(Integer(), auto_increment=True, primary_key=True)
    name = Column(String(50), nullable=False)

    child = relationship("Child", back_populates="parent", uselist=False)

class Child(Base):
    __tablename__ = 'child'

    id = Column(Integer(), auto_increment=True, primary_key=True)
    name = Column(String(50), nullable=False)
    
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship("Parent", back_populates="child")

The above code block declares two models: Parent and Child. The child model contains a foreign key column which refers to the parent model. Both the models contain a relationship() column. The relationship() column in the Parent model has an extra parameter to set uselist to False, preventing the ORM from returning a list of objects upon query execution.

Many to Many

This relationship occurs when you create a new table for storing parent-child entity association. The association table contains foreign keys referring to the primary keys of the parent and child table. Refer to the following code block to establish a many-to-many relationship.

from sqlalchemy.orm import declarative_base, relationship
from sqlalchemy import Column, String, Integer, ForeignKey

Base = declarative_base()

class Association(Base):
    __tablename__ = 'association'

    parent_id = Column(ForeignKey('parent.id'), primary_key=True)
    child_id = Column(ForeignKey('child.id'), primary_key=True)

    child = relationship("Child", back_populates="parents")
    parent = relationship("Parent", back_populates="children")

class Parent(Base):
    __tablename__ = 'parent'

    id = Column(Integer(), auto_increment=True, primary_key=True)
    name = Column(String(50), nullable=False)

    children = relationship("Association", back_populates="parent")

class Child(Base):
    __tablename__ = 'child'

    id = Column(Integer(), auto_increment=True, primary_key=True)
    name = Column(String(50), nullable=False)

    parents = relationship("Association", back_populates="child")

The above code block declares three models: Association, Parent, and Child. The association model contains two foreign key columns which refer to parent and child models. The Parent and Child models contain a relationship() column.

Connect to Other Database Engines

This article explains the usage of SQLAlchemy using SQLite. However, the SQLAlchemy library supports several other database engines such as MySQL, PostgreSQL, Oracle, and Microsoft SQL Server.

You can connect to other database engines by changing the connection string used in the create_engine() function.

The create_engine() function returns an Engine object based on the database URI. This URI follows the RFC-1738 style guide and includes username, password, hostname, and database name.

Format for creating a connection string.

dialect+driver://user:pass@host:port/database

The dialect refers to the name of the database engine such as sqlite, mysql, postgresql, oracle or mssql. The driver refers to the DBAPI used to connect to the database. It uses the most widely known driver available for that dialect by default. The rest of the variables refer to the login credentials and database name.

Example connection string for a MySQL database.

mysql://user:pass@host:port/database

Example connection string for a PostgreSQL database.

postgresql://user:pass@host:port/database

See the list of all supported dialects and database drivers by SQLAlchemy.

More Information

You learned the basics of SQLAlchemy, creating, reading, updating, and deleting model objects, querying filtered data, building SQL relationships, and connecting to MySQL or PostgreSQL database engine. These basics are a good starting point for integrating SQLAlchemy in your Python application. For more information about SQLAlchemy, visit the official SQLAlchemy documentation.

How to Use SQL Databases in Python with SQLAlchemy

Introduction

Prerequisites

Set Up the Environment

Perform Operations

Create a Row

Read a Row

Update a Row

Delete a Row

Query Filtered Data

Build SQL Relationships

One to Many

Many to One

One to One

Many to Many

Connect to Other Database Engines

More Information

Products

Features

Marketplace

Resources

Company