SQL Alchemy mappings

In the previous topic, you learned the basics of SQLAlchemy. It has a lot of useful features. For example, mapping. It transforms SQL objects into Python ones. There are two main mapping types in SQLAlchemy. One is classical mapping, the other is declarative mapping. We will discuss these types below.

Classical mapping

Classical mapping refers to the configuration of a mapped class that was created with the mapper() function. We need to define a database table and the corresponding Python class separately to link them with mapper(). After that, all changes to the table and class made via SQLAlchemy are saved in your database. Classical mapping is a base mapping system provided by the ORM. Take a look at the snippet below:

from sqlalchemy import Table, MetaData, Column, Integer, String
from sqlalchemy.orm import mapper

metadata = MetaData()

animals = Table('animals', metadata,
                Column('id', Integer, primary_key=True),
                Column('petname', String(30)),
                Column('age', Integer),
                Column('weight', Integer))


class Animals(object):
    def __init__(self, petname, age, weight):
        self.petname = petname
        self.age = age
        self.weight = weight


mapper(Animals, animals)  # < Mapper at 0x7f1e9d72b590; Animals >

First of all, we import Table and MetaData constructions to create a table. We also need to import Column, Integer and String to define table features. Mapper can help us to associate the table with the class. After that, we define the animals variable, in which we create an instance of the Table() class, store the name of our new table, and specify the metadata. Then we need to define columns for data. Each column should have a name and datatype (a string or an integer). For string, it is a good idea to specify the length. Simply stated, an SQLAlchemy string is similar to SQL VARCHAR. Finally, if you need to define a primary key, you can assign True to the primary_key parameter. This parameter means that the data in a specific column is unique and can't be repeated twice.

The next step is to define the corresponding Animals class. It should have the same name as the table we have created. The Animals class specifies the details about our table such as petname, age and weight. These names should also correspond to the columns, otherwise, errors will occur when you set to add information to the database.

Finally, the table metadata is associated with the Animals class via the mapper() function. It means that you can easily modify your database with a Python class and save the changes later. The mapper returns a new mapper object.

Declarative Mapping

Declarative mapping is a concise version of classical mapping. We don't need to specify a class and a table separately, we can do it all in one class. Let's try to write a concise form of the previous snippet:

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

Base = declarative_base()


class Animals(Base):
    __tablename__ = 'animals'

    id = Column(Integer, primary_key=True)
    petname = Column(String(30))
    age = Column(Integer)
    weight = Column(Integer)

Our code has become more readable, hasn't it? This time we import declarative_base, a basic function that constructs a base class for defining a declarative class (the Animals class will be declarative). It is assigned to the Base variable in our example. Inside the Animals class, we define the name of our table and the columns with their parameters. Mind that you can define a name either by using __tablename__ or a hybrid approach described in the Documentation. We don't use the mapper() function here, as both the table and class have already been associated in the declarative class.

After that, we can create an engine to carry on with our table:

from sqlalchemy import create_engine

engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)

This is our SQLite engine. The create_all() function saves our table in SQLite format. As the echo is True, you will see a log:

# 2025-08-11 07:34:29,890 INFO sqlalchemy.engine.Engine BEGIN (implicit)
# 2025-08-11 07:34:29,893 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("animals")
# 2025-08-11 07:34:29,900 INFO sqlalchemy.engine.Engine [raw sql] ()
# 2025-08-11 07:34:29,902 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("animals")
# 2025-08-11 07:34:29,903 INFO sqlalchemy.engine.Engine [raw sql] ()
# 2025-08-11 07:34:29,906 INFO sqlalchemy.engine.Engine 
# CREATE TABLE animals (
# 	id INTEGER NOT NULL, 
# 	petname VARCHAR, 
# 	age INTEGER, 
# 	weight INTEGER, 
# 	PRIMARY KEY (id)
# )
# 
#
# 2025-08-11 07:34:29,908 INFO sqlalchemy.engine.Engine [no key 0.00203s] ()
# 2025-08-11 07:34:29,910 INFO sqlalchemy.engine.Engine COMMIT

Hooray! Our table is ready.

Sessions

The mapper() function and declarative extensions are primary interfaces for ORM. Once our mappings are configured, we can proceed to the primary interface. It is also known as a session. It helps us to communicate with our database and ensures that all operations run smoothly. You can modify your database and save the changes during the session. To start one, you can use the following statement:

from sqlalchemy.orm import sessionmaker

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

The Session class is defined with sessionmaker(), a configurable session factory method that is bound to the engine object. When the session object is set up, we can use the default constructor — session = Session(). After that, it remains connected to the engine until we commit the changes or close the session object.

Suppose, we want to add some information about animals to the table:

animal_1 = Animals(petname='Timmy', age=2, weight=12)
animal_2 = Animals(petname='Tommy', age=3, weight=10)
animal_3 = Animals(petname='Kitty', age=4, weight=14)
session.add(animal_1)
session.add(animal_2)
session.add(animal_3)
session.commit()

We are using the Animals class to store the information about each animal in the corresponding column. To proceed, we need to add entries to the table with session.add(). After that, we can tell the Session that we want to save the changes to the database and commit the transaction. You can use session.commit() for that. Let's have a look at the log and make sure that everything works well:

# 2025-08-11 08:40:59,267 INFO sqlalchemy.engine.Engine BEGIN (implicit)
# 2025-08-11 08:40:59,275 INFO sqlalchemy.engine.Engine INSERT INTO animals (petname, age, weight) ...
# 2025-08-11 08:40:59,279 INFO sqlalchemy.engine.Engine [generated in 0.00447s] ('Timmy', '2', '12')
# 2025-08-11 08:40:59,283 INFO sqlalchemy.engine.Engine INSERT INTO animals (petname, age, weight) VALUES ...
# 2025-08-11 08:40:59,287 INFO sqlalchemy.engine.Engine [cached since 0.01268s ago] ('Timmy', '2', '12')
# ...
# 2025-08-11 08:40:59,313 INFO sqlalchemy.engine.Engine COMMIT

Summary

This topic was just a brief introduction to mappings in SQLAlchemy. So far, you know:

• about two main mapping types: classical and declarative;

• that it is important to initialize a table and a class separately in classical mappings;

• that declarative mapping concisely maps a class;

• how to create sessions, add information to the existing table, and save the changes.

If you want to learn more about mappings, feel free to visit the Official Documentation. But for now, let's practice!