ORM Quick Start

    As the descriptions in this section are intentionally very short, please proceed to the full SQLAlchemy Unified Tutorial for a much more in-depth description of each of the concepts being illustrated here.

    Changed in version 2.0: The ORM Quickstart is updated for the latest -aware features using new constructs including mapped_column(). See the section for migration information.

    Here, we define module-level constructs that will form the structures which we will be querying from the database. This structure, known as a , defines at once both a Python object model, as well as database metadata that describes real SQL tables that exist, or will exist, in a particular database:

    The mapping starts with a base class, which above is called , and is created by making a simple subclass against the class.

    Individual mapped classes are then created by making subclasses of Base. A mapped class typically refers to a single particular database table, the name of which is indicated by using the __tablename__ class-level attribute.

    Next, columns that are part of the table are declared, by adding attributes that include a special typing annotation called Mapped. The name of each attribute corresponds to the column that is to be part of the database table. The datatype of each column is taken first from the Python datatype that’s associated with each annotation; int for INTEGER, str for VARCHAR, etc. Nullability derives from whether or not the Optional[] type modifier is used. More specific typing information may be indicated using SQLAlchemy type objects in the right side mapped_column() directive, such as the datatype used above in the User.name column. The association between Python types and SQL types can be customized using the type annotation map.

    The directive is used for all column-based attributes that require more specific customization. Besides typing information, this directive accepts a wide variety of arguments that indicate specific details about a database column, including server defaults and constraint information, such as membership within the primary key and foreign keys. The mapped_column() directive accepts a superset of arguments that are accepted by the SQLAlchemy class, which is used by SQLAlchemy Core to represent database columns.

    All ORM mapped classes require at least one column be declared as part of the primary key, typically by using the Column.primary_key parameter on those objects that should be part of the key. In the above example, the User.id and Address.id columns are marked as primary key.

    Taken together, the combination of a string table name as well as a list of column declarations is referred towards in SQLAlchemy as table metadata. Setting up table metadata using both Core and ORM approaches is introduced in the at Working with Database Metadata. The above mapping is an example of what’s referred towards as configuration.

    Other variants of Mapped are available, most commonly the construct indicated above. In contrast to the column-based attributes, relationship() denotes a linkage between two ORM classes. In the above example, User.addresses links User to Address, and Address.user links Address to User. The construct is introduced in the SQLAlchemy Unified Tutorial at .

    Finally, the above example classes include a __repr__() method, which is not required but is useful for debugging. Mapped classes can be created with methods such as __repr__() generated automatically, using dataclasses. More on dataclass mapping at Declarative Dataclass Mapping.

    Create an Engine

    The Engine is a factory that can create new database connections for us, which also holds onto connections inside of a for fast reuse. For learning purposes, we normally use a SQLite memory-only database for convenience:

    1. >>> from sqlalchemy import create_engine
    2. >>> engine = create_engine("sqlite://", echo=True)

    The echo=True parameter indicates that SQL emitted by connections will be logged to standard out.

    A full intro to the starts at Establishing Connectivity - the Engine.

    Emit CREATE TABLE DDL

    Using our table metadata and our engine, we can generate our schema at once in our target SQLite database, using a method called MetaData.create_all():

    1. >>> Base.metadata.create_all(engine)
    2. BEGIN (implicit)
    3. PRAGMA main.table_...info("user_account")
    4. ...
    5. PRAGMA main.table_...info("address")
    6. ...
    7. CREATE TABLE user_account (
    8.     id INTEGER NOT NULL,
    9.     name VARCHAR(30) NOT NULL,
    10.     fullname VARCHAR,
    11.     PRIMARY KEY (id)
    12. )
    13. ...
    15.     id INTEGER NOT NULL,
    16.     user_id INTEGER NOT NULL,
    17.     PRIMARY KEY (id),
    18.     FOREIGN KEY(user_id) REFERENCES user_account (id)
    19. )
    20. ...
    21. COMMIT

    A lot just happened from that bit of Python code we wrote. For a complete overview of what’s going on on with Table metadata, proceed in the Tutorial at .

    We are now ready to insert data in the database. We accomplish this by creating instances of User and Address classes, which have an __init__() method already as established automatically by the declarative mapping process. We then pass them to the database using an object called a , which makes use of the Engine to interact with the database. The method is used here to add multiple objects at once, and the Session.commit() method will be used to any pending changes to the database and then commit the current database transaction, which is always in progress whenever the is used:

    Tip

    It’s recommended that the Session be used in context manager style as above, that is, using the Python with: statement. The object represents active database resources so it’s good to make sure it’s closed out when a series of operations are completed. In the next section, we’ll keep a Session opened just for illustration purposes.

    Basics on creating a are at Executing with an ORM Session and more at .

    Then, some varieties of basic persistence operations are introduced at Inserting Rows using the ORM Unit of Work pattern.

    Simple SELECT

    With some rows in the database, here’s the simplest form of emitting a SELECT statement to load some objects. To create SELECT statements, we use the select() function to create a new object, which we then invoke using a Session. The method that is often useful when querying for ORM objects is the method, which will return a ScalarResult object that will iterate through the ORM objects we’ve selected:

    1. >>> from sqlalchemy import select
    3. >>> session = Session(engine)
    5. >>> stmt = select(User).where(User.name.in_(["spongebob", "sandy"]))
    7. >>> for user in session.scalars(stmt):
    8. ...     print(user)
    9. BEGIN (implicit)
    10. SELECT user_account.id, user_account.name, user_account.fullname
    11. FROM user_account
    12. WHERE user_account.name IN (?, ?)
    13. [...] ('spongebob', 'sandy')
    14. User(id=1, name='spongebob', fullname='Spongebob Squarepants')
    15. User(id=2, name='sandy', fullname='Sandy Cheeks')

    The above query also made use of the method to add WHERE criteria, and also used the ColumnOperators.in_() method that’s part of all SQLAlchemy column-like constructs to use the SQL IN operator.

    More detail on how to select objects and individual columns is at .

    SELECT with JOIN

    1. >>> stmt = (
    2. ...     select(Address)
    3. ...     .join(Address.user)
    4. ...     .where(User.name == "sandy")
    5. ...     .where(Address.email_address == "sandy@sqlalchemy.org")
    6. >>> sandy_address = session.scalars(stmt).one()
    8. FROM address JOIN user_account ON user_account.id = address.user_id
    9. WHERE user_account.name = ? AND address.email_address = ?
    10. [...] ('sandy', 'sandy@sqlalchemy.org')
    11. >>> sandy_address
    12. Address(id=2, email_address='sandy@sqlalchemy.org')

    The above query illustrates multiple WHERE criteria which are automatically chained together using AND, as well as how to use SQLAlchemy column-like objects to create “equality” comparisons, which uses the overridden Python method to produce a SQL criteria object.

    Some more background on the concepts above are at The WHERE clause and .

    The object, in conjunction with our ORM-mapped classes User and Address, automatically track changes to the objects as they are made, which result in SQL statements that will be emitted the next time the Session flushes. Below, we change one email address associated with “sandy”, and also add a new email address to “patrick”, after emitting a SELECT to retrieve the row for “patrick”:

    Notice when we accessed patrick.addresses, a SELECT was emitted. This is called a . Background on different ways to access related items using more or less SQL is introduced at Loader Strategies.

    A detailed walkthrough on ORM data manipulation starts at .

    Some Deletes

    All things must come to an end, as is the case for some of our database rows - here’s a quick demonstration of two different forms of deletion, both of which are important based on the specific use case.

    First we will remove one of the Address objects from the “sandy” user. When the next flushes, this will result in the row being deleted. This behavior is something that we configured in our mapping called the delete cascade. We can get a handle to the sandy object by primary key using , then work with the object:

    1. >>> sandy = session.get(User, 2)
    2. BEGIN (implicit)
    3. SELECT user_account.id AS user_account_id, user_account.name AS user_account_name, user_account.fullname AS user_account_fullname
    4. FROM user_account
    5. WHERE user_account.id = ?
    6. [...] (2,)
    7. >>> sandy.addresses.remove(sandy_address)
    8. SELECT address.id AS address_id, address.email_address AS address_email_address, address.user_id AS address_user_id
    9. FROM address
    10. WHERE ? = address.user_id
    11. [...] (2,)

    The last SELECT above was the lazy load operation proceeding so that the sandy.addresses collection could be loaded, so that we could remove the sandy_address member. There are other ways to go about this series of operations that won’t emit as much SQL.

    We can choose to emit the DELETE SQL for what’s set to be changed so far, without committing the transaction, using the method:

    1. >>> session.flush()
    2. DELETE FROM address WHERE address.id = ?
    3. [...] (2,)

    Next, we will delete the “patrick” user entirely. For a top-level delete of an object by itself, we use the Session.delete() method; this method doesn’t actually perform the deletion, but sets up the object to be deleted on the next flush. The operation will also to related objects based on the cascade options that we configured, in this case, onto the related Address objects:

    The Session.delete() method in this particular case emitted two SELECT statements, even though it didn’t emit a DELETE, which might seem surprising. This is because when the method went to inspect the object, it turns out the patrick object was , which happened when we last called upon Session.commit(), and the SQL emitted was to re-load the rows from the new transaction. This expiration is optional, and in normal use we will often be turning it off for situations where it doesn’t apply well.

    To illustrate the rows being deleted, here’s the commit:

    1. >>> session.commit()
    2. DELETE FROM address WHERE address.id = ?
    3. [...] (4,)
    4. DELETE FROM user_account WHERE user_account.id = ?
    5. [...] (3,)

    Learn the above concepts in depth

    For a new user, the above sections were likely a whirlwind tour. There’s a lot of important concepts in each step above that weren’t covered. With a quick overview of what things look like, it’s recommended to work through the SQLAlchemy Unified Tutorial to gain a solid working knowledge of what’s really going on above. Good luck!