Release: 1.4.0b1 beta release | Release Date: November 2, 2020

SQLAlchemy 1.4 Documentation

SQLAlchemy 1.4 / 2.0 Tutorial

This page is part of the SQLAlchemy 1.4 / 2.0 Tutorial.

Previous: Working with Database Metadata | Next: Data Manipulation with the ORM

Working with Data

In Working with Transactions and the DBAPI, we learned the basics of how to interact with the Python DBAPI and its transactional state. Then, in Working with Database Metadata, we learned how to represent database tables, columns, and constraints within SQLAlchemy using the MetaData and related objects. In this section we will combine both concepts above to create, select and manipulate data within a relational database. Our interaction with the database is always in terms of a transaction, even if we’ve set our database driver to use autocommit behind the scenes.

The components of this section are as follows:

Core Insert

When using Core, a SQL INSERT statement is generated using the insert() function - this function generates a new instance of Insert which represents an INSERT statement in SQL, that adds new data into a table.

ORM Readers - The way that rows are INSERTed into the database from an ORM perspective makes use of object-centric APIs on the Session object known as the unit of work process, and is fairly different from the Core-only approach described here. The more ORM-focused sections later starting at Inserting Rows with the ORM subsequent to the Expression Language sections introduce this.

The insert() SQL Expression Construct

A simple example of Insert illustrating the target table and the VALUES clause at once:

>>> from sqlalchemy import insert
>>> stmt = insert(user_table).values(name='spongebob', fullname="Spongebob Squarepants")

The above stmt variable is an instance of Insert. Most SQL expressions can be stringified in place as a means to see the general form of what’s being produced:

>>> print(stmt)
INSERT INTO user_account (name, fullname) VALUES (:name, :fullname)

The stringified form is created by producing a Compiled form of the object which includes a database-specific string SQL representation of the statement; we can acquire this object directly using the ClauseElement.compile() method:

>>> compiled = stmt.compile()

Our Insert construct is an example of a “parameterized” construct, illustrated previously at Sending Parameters; to view the name and fullname bound parameters, these are available from the Compiled construct as well:

>>> compiled.params
{'name': 'spongebob', 'fullname': 'Spongebob Squarepants'}

Executing the Statement

Invoking the statement we can INSERT a row into user_table. The INSERT SQL as well as the bundled parameters can be seen in the SQL logging:

>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     conn.commit()
BEGIN (implicit) INSERT INTO user_account (name, fullname) VALUES (?, ?) [...] ('spongebob', 'Spongebob Squarepants') COMMIT

In its simple form above, the INSERT statement does not return any rows, and if only a single row is inserted, it will usually include the ability to return information about column-level default values that were generated during the INSERT of that row, most commonly an integer primary key value. In the above case the first row in a SQLite database will normally return 1 for the first integer primary key value, which we can acquire using the CursorResult.inserted_primary_key accessor:

>>> result.inserted_primary_key
(1,)

Tip

CursorResult.inserted_primary_key returns a tuple because a primary key may contain multiple columns. This is known as a composite primary key. The CursorResult.inserted_primary_key is intended to always contain the complete primary key of the record just inserted, not just a “cursor.lastrowid” kind of value, and is also intended to be populated regardless of whether or not “autoincrement” were used, hence to express a complete primary key it’s a tuple.

INSERT usually generates the “values” clause automatically

The example above made use of the Insert.values() method to explicitly create the VALUES clause of the SQL INSERT statement. This method in fact has some variants that allow for special forms such as multiple rows in one statement and insertion of SQL expressions. However the usual way that Insert is used is such that the VALUES clause is generated automatically from the parameters passed to the Connection.execute() method; below we INSERT two more rows to illustrate this:

>>> with engine.connect() as conn:
...     result = conn.execute(
...         insert(user_table),
...         [
...             {"name": "sandy", "fullname": "Sandy Cheeks"},
...             {"name": "patrick", "fullname": "Patrick Star"}
...         ]
...     )
...     conn.commit()
BEGIN (implicit) INSERT INTO user_account (name, fullname) VALUES (?, ?) [...] (('sandy', 'Sandy Cheeks'), ('patrick', 'Patrick Star')) COMMIT

The execution above features “executemany” form first illustrated at Sending Multiple Parameters, however unlike when using the text() construct, we didn’t have to spell out any SQL. By passing a dictionary or list of dictionaries to the Connection.execute() method in conjunction with the Insert construct, the Connection ensures that the column names which are passed will be expressed in the VALUES clause of the Insert construct automatically.

Deep Alchemy

Hi, welcome to the first edition of Deep Alchemy. The person on the left is known as The Alchemist, and you’ll note they are not a wizard, as the pointy hat is not sticking upwards. The Alchemist comes around to describe things that are generally more advanced and/or tricky and additionally not usually needed, but for whatever reason they feel you should know about this thing that SQLAlchemy can do.

In this edition, towards the goal of having some interesting data in the address_table as well, below is a more advanced example illustrating how the Insert.values() method may be used explicitly while at the same time including for additional VALUES generated from the parameters. A scalar subquery is constructed, making use of the select() construct introduced in the next section, and the parameters used in the subquery are set up using an explicit bound parameter name, established using the bindparam() construct.

This is some slightly deeper alchemy just so that we can add related rows without fetching the primary key identifiers from the user_table operation into the application. Most Alchemists will simply use the ORM which takes care of things like this for us.

>>> from sqlalchemy import select, bindparam
>>> scalar_subquery = (
...     select(user_table.c.id).
...     where(user_table.c.name==bindparam('username')).
...     scalar_subquery()
... )

>>> with engine.connect() as conn:
...     result = conn.execute(
...         insert(address_table).values(user_id=scalar_subquery),
...         [
...             {"username": 'spongebob', "email_address": "spongebob@sqlalchemy.org"},
...             {"username": 'sandy', "email_address": "sandy@sqlalchemy.org"},
...             {"username": 'sandy', "email_address": "sandy@squirrelpower.org"},
...         ]
...     )
...     conn.commit()
BEGIN (implicit) INSERT INTO address (user_id, email_address) VALUES ((SELECT user_account.id FROM user_account WHERE user_account.name = ?), ?) [...] (('spongebob', 'spongebob@sqlalchemy.org'), ('sandy', 'sandy@sqlalchemy.org'), ('sandy', 'sandy@squirrelpower.org')) COMMIT

INSERT…FROM SELECT

The Insert construct can compose an INSERT that gets rows directly from a SELECT using the Insert.from_select() method:

>>> select_stmt = select(user_table.c.id, user_table.c.name + "@aol.com")
>>> insert_stmt = insert(address_table).from_select(
...     ["user_id", "email_address"], select_stmt
... )
>>> print(insert_stmt)
INSERT INTO address (user_id, email_address) SELECT user_account.id, user_account.name || :name_1 AS anon_1 FROM user_account

INSERT…RETURNING

The RETURNING clause for supported backends is used automatically in order to retrieve the last inserted primary key value as well as the values for server defaults. However the RETURNING clause may also be specified explicitly using the Insert.returning() method; in this case, the Result object that’s returned when the statement is executed has rows which can be fetched:

>>> insert_stmt = insert(address_table).returning(address_table.c.id, address_table.c.email_address)
>>> print(insert_stmt)
INSERT INTO address (id, user_id, email_address) VALUES (:id, :user_id, :email_address) RETURNING address.id, address.email_address

It can also be combined with Insert.from_select(), as in the example below that builds upon the example stated in INSERT…FROM SELECT:

>>> select_stmt = select(user_table.c.id, user_table.c.name + "@aol.com")
>>> insert_stmt = insert(address_table).from_select(
...     ["user_id", "email_address"], select_stmt
... )
>>> print(insert_stmt.returning(address_table.c.id, address_table.c.email_address))
INSERT INTO address (user_id, email_address) SELECT user_account.id, user_account.name || :name_1 AS anon_1 FROM user_account RETURNING address.id, address.email_address

Tip

The RETURNING feature is also supported by UPDATE and DELETE statements, which will be introduced later in this tutorial. The RETURNING feature is generally [1]_ only supported for statement executions that use a single set of bound parameters; that is, it wont work with the “executemany” form introduced at Sending Multiple Parameters. Additionally, some dialects such as the Oracle dialect only allow RETURNING to return a single row overall, meaning it won’t work with “INSERT..FROM SELECT” nor will it work with multiple row Update or Delete forms.

1

There is internal support for the psycopg2 dialect to INSERT many rows at once and also support RETURNING, which is leveraged by the SQLAlchemy ORM. However this feature has not been generalized to all dialects and is not yet part of SQLAlchemy’s regular API.

See also

Insert - in the SQL Expression API documentation

Selecting Data

For both Core and ORM, the select() function generates a Select construct which is used for all SELECT queries. Passed to methods like Connection.execute() in Core and Session.execute() in ORM, a SELECT statement is emitted in the current transaction and the result rows available via the returned Result object.

ORM Readers - the content here applies equally well to both Core and ORM use and basic ORM variant use cases are mentioned here. However there are a lot more ORM-specific features available as well; these are documented at ORM Querying Guide.

The select() SQL Expression Construct

The select() construct builds up a statement in the same way as that of insert(), using a generative approach where each method builds more state onto the object. Like the other SQL constructs, it can be stringified in place:

>>> from sqlalchemy import select
>>> stmt = select(user_table).where(user_table.c.name == 'spongebob')
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname FROM user_account WHERE user_account.name = :name_1

Also in the same manner as all other statement-level SQL constructs, to actually run the statement we pass it to an execution method. Since a SELECT statement returns rows we can always iterate the result object to get Row objects back:

>>> with engine.connect() as conn:
...     for row in conn.execute(stmt):
...         print(row)
BEGIN (implicit) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account WHERE user_account.name = ? [...] ('spongebob',)
(1, 'spongebob', 'Spongebob Squarepants')
ROLLBACK

When using the ORM, particularly with a select() construct that’s composed against ORM entities, we will want to execute it using the Session.execute() method on the Session; using this approach, we continue to get Row objects from the result, however these rows are now capable of including complete entities, such as instances of the User class, as column values:

>>> stmt = select(User).where(User.name == 'spongebob')
>>> with Session(engine) as session:
...     for row in session.execute(stmt):
...         print(row)
BEGIN (implicit) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account WHERE user_account.name = ? [...] ('spongebob',)
(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)
ROLLBACK

select() from a Table vs. ORM class

While the SQL generated in these examples looks the same whether we invoke select(user_table) or select(User), in the more general case they do not necessarily render the same thing, as an ORM-mapped class may be mapped to other kinds of “selectables” besides tables. The select() that’s against an ORM entity also indicates that ORM-mapped instances should be returned in a result, which is not the case when SELECTing from a Table object.

The following sections will discuss the SELECT construct in more detail.

Setting the COLUMNS and FROM clause

The select() function accepts positional elements representing any number of Column and/or Table expressions, as well as a wide range of compatible objects, which are resolved into a list of SQL expressions to be SELECTed from that will be returned as columns in the result set. These elements also serve in simpler cases to create the FROM clause, which is inferred from the columns and table-like expressions passed:

>>> print(select(user_table))
SELECT user_account.id, user_account.name, user_account.fullname FROM user_account

To SELECT from individual columns using a Core approach, Column objects are accessed from the Table.c accessor and can be sent directly; the FROM clause will be inferred as the set of all Table and other FromClause objects that are represented by those columns:

>>> print(select(user_table.c.name, user_table.c.fullname))
SELECT user_account.name, user_account.fullname FROM user_account

Selecting ORM Entities and Columns

ORM entities, such our User class as well as the column-mapped attributes upon it such as User.name, also participate in the SQL Expression Language system representing tables and columns. Below illustrates an example of SELECTing from the User entity, which ultimately renders in the same way as if we had used user_table directly:

>>> print(select(User))
SELECT user_account.id, user_account.name, user_account.fullname FROM user_account

When executing a statement like the above using the ORM Session.execute() method, there is an important difference when we select from a full entity such as User, as opposed to user_table, which is that the entity itself is returned as a single column within each row. That is, when we fetch rows from the above statement, as there is only the User entity in the list of things to fetch, we get back Row objects that have only one column, which contain instances of the User class:

>>> row = session.execute(select(User)).first()
BEGIN... SELECT user_account.id, user_account.name, user_account.fullname FROM user_account [...] ()
>>> row (User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)

The above Row has just one column, representing the User entity:

>>> row[0]
User(id=1, name='spongebob', fullname='Spongebob Squarepants')

Alternatively, we can select individual columns from an ORM entity, by using the class-bound attributes; when these are passed to a construct such as select(), they are resolved into the Column or other SQL expression represented by each attribute:

>>> print(select(User.name, User.fullname))
SELECT user_account.name, user_account.fullname FROM user_account

When we invoke this statement using Session.execute(), we now receive rows that have individual columns per value:

>>> row = session.execute(select(User.name, User.fullname)).first()
SELECT user_account.name, user_account.fullname FROM user_account [...] ()
>>> row ('spongebob', 'Spongebob Squarepants')

The approaches can also be mixed, as below where we SELECT the name attribute of the User entity as the first column, and combine it with full Address entities in the second column:

>>> session.execute(
...     select(User.name, Address).
...     where(User.id==Address.user_id).
...     order_by(Address.id)
... ).all()
SELECT user_account.name, address.id, address.email_address, address.user_id FROM user_account, address WHERE user_account.id = address.user_id ORDER BY address.id [...] ()
[('spongebob', Address(id=1, email_address='spongebob@sqlalchemy.org')), ('sandy', Address(id=2, email_address='sandy@sqlalchemy.org')), ('sandy', Address(id=3, email_address='sandy@squirrelpower.org'))]

Approaches towards selecting ORM entities and columns as well as common methods for converting rows are discussed further at Selecting ORM Entities and Attributes.

Selecting from Labeled SQL Expressions

The ColumnElement.label() method as well as the same-named method available on ORM attributes provides a SQL label of a column or expression, allowing it to have a specific name in a result set. This can be helpful when referring to arbitrary SQL expressions in a result row by name:

>>> from sqlalchemy import func, cast
>>> stmt = (
...     select(
...         ("Username: " + user_table.c.name).label("username"),
...     ).order_by(user_table.c.name)
... )
>>> with engine.connect() as conn:
...     for row in conn.execute(stmt):
...         print(f"{row.username}")
BEGIN (implicit) SELECT ? || user_account.name AS username FROM user_account ORDER BY user_account.name [...] ('Username: ',)
Username: patrick Username: sandy Username: spongebob
ROLLBACK

The WHERE clause

SQLAlchemy allows us to compose SQL expressions, such as name = 'squidward' or user_id > 10, by making use of standard Python operators in conjunction with Column and similar objects. For boolean expressions, most Python operators such as ==, !=, <, >= etc. generate new SQL Expression objects, rather than plain boolean True/False values:

>>> print(user_table.c.name == 'squidward')
user_account.name = :name_1

>>> print(address_table.c.user_id > 10)
address.user_id > :user_id_1

We can use expressions like these to generate the WHERE clause by passing the resulting objects to the Select.where() method:

>>> print(select(user_table).where(user_table.c.name == 'squidward'))
SELECT user_account.id, user_account.name, user_account.fullname FROM user_account WHERE user_account.name = :name_1

To produce multiple expressions joined by AND, the Select.where() method may be invoked any number of times:

>>> print(
...     select(address_table.c.email_address).
...     where(user_table.c.name == 'squidward').
...     where(address_table.c.user_id == user_table.c.id)
... )
SELECT address.email_address FROM address, user_account WHERE user_account.name = :name_1 AND address.user_id = user_account.id

A single call to Select.where() also accepts multiple expressions with the same effect:

>>> print(
...     select(address_table.c.email_address).
...     where(
...          user_table.c.name == 'squidward',
...          address_table.c.user_id == user_table.c.id
...     )
... )
SELECT address.email_address FROM address, user_account WHERE user_account.name = :name_1 AND address.user_id = user_account.id

“AND” and “OR” conjunctions are both available directly using the and_() and or_() functions, illustrated below in terms of ORM entities:

>>> from sqlalchemy import and_, or_
>>> print(
...     select(Address.email_address).
...     where(
...         and_(
...             or_(User.name == 'squidward', User.name == 'sandy'),
...             Address.user_id == User.id
...         )
...     )
... )
SELECT address.email_address FROM address, user_account WHERE (user_account.name = :name_1 OR user_account.name = :name_2) AND address.user_id = user_account.id

For simple “equality” comparisons against a single entity, there’s also a popular method known as Select.filter_by() which accepts keyword arguments that match to column keys or ORM attribute names. It will filter against the leftmost FROM clause or the last entity joined:

>>> print(
...     select(User).filter_by(name='spongebob', fullname='Spongebob Squarepants')
... )
SELECT user_account.id, user_account.name, user_account.fullname FROM user_account WHERE user_account.name = :name_1 AND user_account.fullname = :fullname_1

See also

Operator Reference - descriptions of most SQL operator functions in SQLAlchemy

Explicit FROM clauses and JOINs

As mentioned previously, the FROM clause is usually inferred based on the expressions that we are setting in the columns clause as well as other elements of the Select.

If we set a single column from a particular Table in the COLUMNS clause, it puts that Table in the FROM clause as well:

>>> print(select(user_table.c.name))
SELECT user_account.name FROM user_account

If we were to put columns from two tables, then we get a comma-separated FROM clause:

>>> print(select(user_table.c.name, address_table.c.email_address))
SELECT user_account.name, address.email_address FROM user_account, address

In order to JOIN these two tables together, we typically use one of two methods on Select. The first is the Select.join_from() method, which allows us to indicate the left and right side of the JOIN explicitly:

>>> print(
...     select(user_table.c.name, address_table.c.email_address).
...     join_from(user_table, address_table)
... )
SELECT user_account.name, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id

The other is the the Select.join() method, which indicates only the right side of the JOIN, the left hand-side is inferred:

>>> print(
...     select(user_table.c.name, address_table.c.email_address).
...     join(address_table)
... )
SELECT user_account.name, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id

We also have the option add elements to the FROM clause explicitly, if it is not inferred the way we want from the columns clause. We use the Select.select_from() method to achieve this, as below where we establish user_table as the first element in the FROM clause and Select.join() to establish address_table as the second:

>>> print(
...     select(address_table.c.email_address).
...     select_from(user_table).join(address_table)
... )
SELECT address.email_address FROM user_account JOIN address ON user_account.id = address.user_id

Another example where we might want to use Select.select_from() is if our columns clause doesn’t have enough information to provide for a FROM clause. For example, to SELECT from the common SQL expression count(*), we use a SQLAlchemy element known as sqlalchemy.sql.expression.func to produce the SQL count() function:

>>> from sqlalchemy import func
>>> print (
...     select(func.count('*')).select_from(user_table)
... )
SELECT count(:count_2) AS count_1 FROM user_account

Setting the ON Clause

The previous examples of JOIN illustrated that the Select construct can join between two tables and produce the ON clause automatically. This occurs in those examples because the user_table and address_table Table objects include a single ForeignKeyConstraint definition which is used to form this ON clause.

If the left and right targets of the join do not have such a constraint, or there are multiple constraints in place, we need to specify the ON clause directly. Both Select.join() and Select.join_from() accept an additional argument for the ON clause, which is stated using the same SQL Expression mechanics as we saw about in The WHERE clause:

>>> print(
...     select(address_table.c.email_address).
...     select_from(user_table).
...     join(address_table, user_table.c.id == address_table.c.user_id)
... )
SELECT address.email_address FROM user_account JOIN address ON user_account.id = address.user_id

ORM Tip - there’s another way to generate the ON clause when using ORM entities that make use of the relationship() construct, like the mapping set up in the previous section at Declaring Mapped Classes. This is a whole subject onto itself, which is introduced at length at Using Relationships to Join.

OUTER and FULL join

Both the Select.join() and Select.join_from() methods accept keyword arguments Select.join.isouter and Select.join.full which will render LEFT OUTER JOIN and FULL OUTER JOIN, respectively:

>>> print(
...     select(user_table).join(address_table, isouter=True)
... )
SELECT user_account.id, user_account.name, user_account.fullname FROM user_account LEFT OUTER JOIN address ON user_account.id = address.user_id
>>> print( ... select(user_table).join(address_table, full=True) ... )
SELECT user_account.id, user_account.name, user_account.fullname FROM user_account FULL OUTER JOIN address ON user_account.id = address.user_id

There is also a method Select.outerjoin() that is equivalent to using .join(..., isouter=True).

Tip

SQL also has a “RIGHT OUTER JOIN”. SQLAlchemy doesn’t render this directly; instead, reverse the order of the tables and use “LEFT OUTER JOIN”.

ORDER BY

The ORDER BY clause is constructed in terms of SQL Expression constructs typically based on Column or similar objects. The Select.order_by() method accepts one or more of these expressions positionally:

>>> print(select(user_table).order_by(user_table.c.name))
SELECT user_account.id, user_account.name, user_account.fullname FROM user_account ORDER BY user_account.name

Ascending / descending is available from the ColumnElement.asc() and ColumnElement.desc() modifiers, which are present from ORM-bound attributes as well:

>>> print(select(User).order_by(User.name.asc(), User.fullname.desc()))
SELECT user_account.id, user_account.name, user_account.fullname FROM user_account ORDER BY user_account.name ASC, user_account.fullname DESC

Aggregate functions with GROUP BY / HAVING

In SQL, aggregate functions allow column expressions across multiple rows to be aggregated together to produce a single result. Examples include counting, computing averages, as well as locating the maximum or minimum value in a set of values.

SQLAlchemy provides for SQL functions in an open-ended way using a namespace known as func. This is a special constructor object which will create new instances of Function when given the name of a particular SQL function, which can have any name, as well as zero or more arguments to pass to the function, which are, like in all other cases, SQL Expression constructs. For example, to render the SQL COUNT() function against the user_account.id column, we call upon the name count() name:

>>> from sqlalchemy import func
>>> count_fn = func.count(user_table.c.id)
>>> print(count_fn)
count(user_account.id)

When using aggregate functions in SQL, the GROUP BY clause is essential in that it allows rows to be partitioned into groups where aggregate functions will be applied to each group individually. When requesting non-aggregated columns in the COLUMNS clause of a SELECT statement, SQL requires that these columns all be subject to a GROUP BY clause, either directly or indirectly based on a primary key association. The HAVING clause is then used in a similar manner as the WHERE clause, except that it filters out rows based on aggregated values rather than direct row contents.

SQLAlchemy provides for these two clauses using the Select.group_by() and Select.having() methods. Below we illustrate selecting user name fields as well as count of addresses, for those users that have more than one address:

>>> with engine.connect() as conn:
...     result = conn.execute(
...         select(User.name, func.count(Address.id).label("count")).
...         join(Address).
...         group_by(User.name).
...         having(func.count(Address.id) > 1)
...     )
...     print(result.all())
BEGIN (implicit) SELECT user_account.name, count(address.id) AS count FROM user_account JOIN address ON user_account.id = address.user_id GROUP BY user_account.name HAVING count(address.id) > ? [...] (1,)
[('sandy', 2)]
ROLLBACK

Ordering or Grouping by a Label

An important technique, in particular on some database backends, is the ability to ORDER BY or GROUP BY an expression that is already stated in the columns clause, without re-stating the expression in the ORDER BY or GROUP BY clause and instead using the column name or labeled name from the COLUMNS clause. This form is available by passing the string text of the name to the Select.order_by() or Select.group_by() method. The text passed is not rendered directly; instead, the name given to an expression in the columns clause and rendered as that expression name in context, raising an error if no match is found. The unary modifiers asc() and desc() may also be used in this form:

>>> from sqlalchemy import func, desc
>>> stmt = select(
...         Address.user_id,
...         func.count(Address.id).label('num_addresses')).\
...         group_by("user_id").order_by("user_id", desc("num_addresses"))
>>> print(stmt)
SELECT address.user_id, count(address.id) AS num_addresses FROM address GROUP BY address.user_id ORDER BY address.user_id, num_addresses DESC

Using Aliases

Now that we are selecting from multiple tables and using joins, we quickly run into the case where we need to refer to the same table mutiple times in the FROM clause of a statement. We accomplish this using SQL aliases, which are a syntax that supplies an alternative name to a table or subquery from which it can be referred towards in the statement.

In the SQLAlchemy Expression Language, these “names” are instead represented by FromClause objects known as the Alias construct, which is constructed in Core using the FromClause.alias() method. An Alias construct is just like a Table construct in that it also has a namespace of Column objects within the Alias.c collection. The SELECT statement below for example returns all unique pairs of user names:

>>> user_alias_1 = user_table.alias()
>>> user_alias_2 = user_table.alias()
>>> print(
...     select(user_alias_1.c.name, user_alias_2.c.name).
...     join_from(user_alias_1, user_alias_2, user_alias_1.c.id > user_alias_2.c.id)
... )
SELECT user_account_1.name, user_account_2.name FROM user_account AS user_account_1 JOIN user_account AS user_account_2 ON user_account_1.id > user_account_2.id

ORM Entity Aliases

The ORM equivalent of the FromClause.alias() method is the ORM aliased() function, which may be applied to an entity such as User and Address. This produces a Alias object internally that’s against the original mapped Table object, while maintaining ORM functionality. The SELECT below selects from the User entity all objects that include two particular email addresses:

>>> from sqlalchemy.orm import aliased
>>> address_alias_1 = aliased(Address)
>>> address_alias_2 = aliased(Address)
>>> print(
...     select(User).
...     join_from(User, address_alias_1).
...     where(address_alias_1.email_address == 'patrick@aol.com').
...     join_from(User, address_alias_2).
...     where(address_alias_2.email_address == 'patrick@gmail.com')
... )
SELECT user_account.id, user_account.name, user_account.fullname FROM user_account JOIN address AS address_1 ON user_account.id = address_1.user_id JOIN address AS address_2 ON user_account.id = address_2.user_id WHERE address_1.email_address = :email_address_1 AND address_2.email_address = :email_address_2

Tip

As mentioned in Setting the ON Clause, the ORM provides for another way to join using the relationship() construct. The above example using aliases is demonstrated using relationship() at Joining between Aliased targets.

Subqueries and CTEs

A subquery in SQL is a SELECT statement that is rendered within parenthesis and placed within the context of an enclosing statement, typically a SELECT statement but not necessarily.

This section will cover a so-called “non-scalar” subquery, which is typically placed in the FROM clause of an enclosing SELECT. We will also cover the Common Table Expression or CTE, which is used in a similar way as a subquery, but includes additional features.

SQLAlchemy uses the Subquery object to represent a subquery and the CTE to represent a CTE, usually obtained from the Select.subquery() and Select.cte() methods, respectively. Either object can be used as a FROM element inside of a larger select() construct.

We can construct a Subquery that will select an aggregate count of rows from the address table (aggregate functions and GROUP BY were introduced previously at Aggregate functions with GROUP BY / HAVING):

>>> subq = select(
...     func.count(address_table.c.id).label("count"),
...     address_table.c.user_id
... ).group_by(address_table.c.user_id).subquery()

Stringifying the subquery by itself without it being embedded inside of another Select or other statement produces the plain SELECT statement without any enclosing parenthesis:

>>> print(subq)
SELECT count(address.id) AS count, address.user_id FROM address GROUP BY address.user_id

The Subquery object behaves like any other FROM object such as a Table, notably that it includes a Subquery.c namespace of the columns which it selects. We can use this namespace to refer to both the user_id column as well as our custom labeled count expression:

>>> print(select(subq.c.user_id, subq.c.count))
SELECT anon_1.user_id, anon_1.count FROM (SELECT count(address.id) AS count, address.user_id AS user_id FROM address GROUP BY address.user_id) AS anon_1

With a selection of rows contained within the subq object, we can apply the object to a larger Select that will join the data to the user_account table:

>>> stmt = select(
...    user_table.c.name,
...    user_table.c.fullname,
...    subq.c.count
... ).join_from(user_table, subq)

>>> print(stmt)
SELECT user_account.name, user_account.fullname, anon_1.count FROM user_account JOIN (SELECT count(address.id) AS count, address.user_id AS user_id FROM address GROUP BY address.user_id) AS anon_1 ON user_account.id = anon_1.user_id

In order to join from user_account to address, we made use of the Select.join_from() method. As has been illustrated previously, the ON clause of this join was again inferred based on foreign key constraints. Even though a SQL subquery does not itself have any constraints, SQLAlchemy can act upon constraints represented on the columns by determining that the subq.c.user_id column is derived from the address_table.c.user_id column, which does express a foreign key relationship back to the user_table.c.id column which is then used to generate the ON clause.

Common Table Expressions (CTEs)

Usage of the CTE construct in SQLAlchemy is virtually the same as how the Subquery construct is used. By changing the invocation of the Select.subquery() method to use Select.cte() instead, we can use the resulting object as a FROM element in the same way, but the SQL rendered is the very different common table expression syntax:

>>> subq = select(
...     func.count(address_table.c.id).label("count"),
...     address_table.c.user_id
... ).group_by(address_table.c.user_id).cte()

>>> stmt = select(
...    user_table.c.name,
...    user_table.c.fullname,
...    subq.c.count
... ).join_from(user_table, subq)

>>> print(stmt)
WITH anon_1 AS (SELECT count(address.id) AS count, address.user_id AS user_id FROM address GROUP BY address.user_id) SELECT user_account.name, user_account.fullname, anon_1.count FROM user_account JOIN anon_1 ON user_account.id = anon_1.user_id

The CTE construct also features the ability to be used in a “recursive” style, and may in more elaborate cases be composed from the RETURNING clause of an INSERT, UPDATE or DELETE statement. The docstring for CTE includes details on these additional patterns.

In both cases, the subquery and CTE were named at the SQL level using an “anonymous” name. In the Python code, we don’t need to provide these names at all. The object identity of the Subquery or CTE instances serves as the syntactical identity of the object when rendered. A name that will be rendered in the SQL can be provided by passing it as the first argument of the Select.subquery() or Select.cte() methods.

See also

Select.subquery() - further detail on subqueries

Select.cte() - examples for CTE including how to use RECURSIVE as well as DML-oriented CTEs

ORM Entity Subqueries/CTEs

In the ORM, the aliased() construct may be used to associate an ORM entity, such as our User or Address class, with any FromClause concept that represents a source of rows. The preceding section ORM Entity Aliases illustrates using aliased() to associate the mapped class with an Alias of its mapped Table. Here we illustrate aliased() doing the same thing against both a Subquery as well as a CTE generated against a Select construct, that ultimately derives from that same mapped Table.

Below is an example of applying aliased() to the Subquery construct, so that ORM entities can be extracted from its rows. The result shows a series of User and Address objects, where the data for each Address object ultimately came from a subquery against the address table rather than that table directly:

>>> subq = select(Address).where(~Address.email_address.like('%@aol.com')).subquery()
>>> address_subq = aliased(Address, subq)
>>> stmt = select(User, address_subq).join_from(User, address_subq).order_by(User.id, address_subq.id)
>>> with Session(engine) as session:
...     for user, address in session.execute(stmt):
...         print(f"{user} {address}")
BEGIN (implicit) SELECT user_account.id, user_account.name, user_account.fullname, anon_1.id AS id_1, anon_1.email_address, anon_1.user_id FROM user_account JOIN (SELECT address.id AS id, address.email_address AS email_address, address.user_id AS user_id FROM address WHERE address.email_address NOT LIKE ?) AS anon_1 ON user_account.id = anon_1.user_id ORDER BY user_account.id, anon_1.id [...] ('%@aol.com',)
User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org') User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org') User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org')
ROLLBACK

Another example follows, which is exactly the same except it makes use of the CTE construct instead:

>>> cte = select(Address).where(~Address.email_address.like('%@aol.com')).cte()
>>> address_cte = aliased(Address, cte)
>>> stmt = select(User, address_cte).join_from(User, address_cte).order_by(User.id, address_cte.id)
>>> with Session(engine) as session:
...     for user, address in session.execute(stmt):
...         print(f"{user} {address}")
BEGIN (implicit) WITH anon_1 AS (SELECT address.id AS id, address.email_address AS email_address, address.user_id AS user_id FROM address WHERE address.email_address NOT LIKE ?) SELECT user_account.id, user_account.name, user_account.fullname, anon_1.id AS id_1, anon_1.email_address, anon_1.user_id FROM user_account JOIN anon_1 ON user_account.id = anon_1.user_id ORDER BY user_account.id, anon_1.id [...] ('%@aol.com',)
User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org') User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org') User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org')
ROLLBACK

Scalar and Correlated Subqueries

A scalar subquery is a subquery that returns exactly zero or one row and exactly one column. The subquery is then used in the COLUMNS or WHERE clause of an enclosing SELECT statement and is different than a regular subquery in that it is not used in the FROM clause. A correlated subquery is a scalar subquery that refers to a table in the enclosing SELECT statement.

SQLAlchemy represents the scalar subquery using the ScalarSelect construct, which is part of the ColumnElement expression hierarchy, in contrast to the regular subquery which is represented by the Subquery construct, which is in the FromClause hierarchy.

Scalar subqueries are often, but not necessarily, used with aggregate functions, introduced previously at Aggregate functions with GROUP BY / HAVING. A scalar subquery is indicated explicitly by making use of the Select.scalar_subquery() method as below. It’s default string form when stringified by itself renders as an ordinary SELECT statement that is selecting from two tables:

>>> subq = select(func.count(address_table.c.id)).\
...             where(user_table.c.id == address_table.c.user_id).\
...             scalar_subquery()
>>> print(subq)
(SELECT count(address.id) AS count_1 FROM address, user_account WHERE user_account.id = address.user_id)

The above subq object now falls within the ColumnElement SQL expression hierarchy, in that it may be used like any other column expression:

>>> print(subq == 5)
(SELECT count(address.id) AS count_1 FROM address, user_account WHERE user_account.id = address.user_id) = :param_1

Although the scalar subquery by itself renders both user_account and address in its FROM clause when stringified by itself, when embedding it into an enclosing select() construct that deals with the user_account table, the user_account table is automatically correlated, meaning it does not render in the FROM clause of the subquery:

>>> stmt = select(user_table.c.name, subq.label("address_count"))
>>> print(stmt)
SELECT user_account.name, (SELECT count(address.id) AS count_1 FROM address WHERE user_account.id = address.user_id) AS address_count FROM user_account

Simple correlated subqueries will usually do the right thing that’s desired. However, in the case where the correlation is ambiguous, SQLAlchemy will let us know that more clarity is needed:

>>> stmt = select(
...     user_table.c.name,
...     address_table.c.email_address,
...     subq.label("address_count")
... ).\
... join_from(user_table, address_table).\
... order_by(user_table.c.id, address_table.c.id)
>>> print(stmt)
Traceback (most recent call last):
...
InvalidRequestError: Select statement '<... Select object at ...>' returned
no FROM clauses due to auto-correlation; specify correlate(<tables>) to
control correlation manually.

To specify that the user_table is the one we seek to correlate we specify this using the ScalarSelect.correlate() or ScalarSelect.correlate_except() methods:

>>> subq = select(func.count(address_table.c.id)).\
...             where(user_table.c.id == address_table.c.user_id).\
...             scalar_subquery().correlate(user_table)

The statement then can return the data for this column like any other:

>>> with engine.connect() as conn:
...     result = conn.execute(
...         select(
...             user_table.c.name,
...             address_table.c.email_address,
...             subq.label("address_count")
...         ).
...         join_from(user_table, address_table).
...         order_by(user_table.c.id, address_table.c.id)
...     )
...     print(result.all())
BEGIN (implicit) SELECT user_account.name, address.email_address, (SELECT count(address.id) AS count_1 FROM address WHERE user_account.id = address.user_id) AS address_count FROM user_account JOIN address ON user_account.id = address.user_id ORDER BY user_account.id, address.id [...] ()
[('spongebob', 'spongebob@sqlalchemy.org', 1), ('sandy', 'sandy@sqlalchemy.org', 2), ('sandy', 'sandy@squirrelpower.org', 2)]
ROLLBACK

EXISTS subqueries

The SQL EXISTS keyword is an operator that is used with scalar subqueries to return a boolean true or false depending on if the SELECT statement would return a row. SQLAlchemy includes a variant of the ScalarSelect object called Exists, which will generate an EXISTS subquery and is most conveniently generated using the SelectBase.exists() method. Below we produce an EXISTS so that we can return user_account rows that have more than one related row in address:

>>> subq = (
...     select(func.count(address_table.c.id)).
...     where(user_table.c.id == address_table.c.user_id).
...     group_by(address_table.c.user_id).
...     having(func.count(address_table.c.id) > 1)
... ).exists()
>>> with engine.connect() as conn:
...     result = conn.execute(
...         select(user_table.c.name).where(subq)
...     )
...     print(result.all())
BEGIN (implicit) SELECT user_account.name FROM user_account WHERE EXISTS (SELECT count(address.id) AS count_1 FROM address WHERE user_account.id = address.user_id GROUP BY address.user_id HAVING count(address.id) > ?) [...] (1,)
[('sandy',)]
ROLLBACK

The EXISTS construct is more often than not used as a negation, e.g. NOT EXISTS, as it provides a SQL-efficient form of locating rows for which a related table has no rows. Below we select user names that have no email addresses; note the binary negation operator (~) used inside the second WHERE clause:

>>> subq = (
...     select(address_table.c.id).
...     where(user_table.c.id == address_table.c.user_id)
... ).exists()
>>> with engine.connect() as conn:
...     result = conn.execute(
...         select(user_table.c.name).where(~subq)
...     )
...     print(result.all())
BEGIN (implicit) SELECT user_account.name FROM user_account WHERE NOT (EXISTS (SELECT address.id FROM address WHERE user_account.id = address.user_id)) [...] ()
[('patrick',)]
ROLLBACK

Core UPDATE and DELETE

So far we’ve covered Insert, so that we can get some data into our database, and then spent a lot of time on Select which handles the broad range of usage patterns used for retrieving data from the database. In this section we will cover the Update and Delete constructs, which are used to modify existing rows as well as delete existing rows. This section will cover these constructs from a Core-centric perspective.

ORM Readers - As was the case mentioned at Core Insert, the Update and Delete operations when used with the ORM are usually invoked internally from the Session object as part of the unit of work process.

However, unlike Insert, the Update and Delete constructs can also be used directly with the ORM, using a pattern known as “ORM-enabled update and delete”; for this reason, familiarity with these constructs is useful for ORM use. Both styles of use are discussed in the sections Updating ORM Objects and Deleting ORM Objects.

The update() SQL Expression Construct

The update() function generates a new instance of Update which represents an UPDATE statement in SQL, that will update existing data in a table.

Like the insert() construct, there is a “traditional” form of update(), which emits UPDATE against a single table at a time and does not return any rows. However some backends support an UPDATE statement that may modify multiple tables at once, and the UPDATE statement also supports RETURNING such that columns contained in matched rows may be returned in the result set.

A basic UPDATE looks like:

>>> from sqlalchemy import update
>>> stmt = (
...     update(user_table).where(user_table.c.name == 'patrick').
...     values(fullname='Patrick the Star')
... )
>>> print(stmt)
UPDATE user_account SET fullname=:fullname WHERE user_account.name = :name_1

The Update.values() method controls the contents of the SET elements of the UPDATE statement. This is the same method shared by the Insert construct. Parameters can normally be passed using the column names as keyword arguments.

UPDATE supports all the major SQL forms of UPDATE, including updates against expressions, where we can make use of Column expressions:

>>> stmt = (
...     update(user_table).
...     values(fullname="Username: " + user_table.c.name)
... )
>>> print(stmt)
UPDATE user_account SET fullname=(:name_1 || user_account.name)

To support UPDATE in an “executemany” context, where many parameter sets will be invoked against the same statement, the bindparam() construct may be used to set up bound parameters; these replace the places that literal values would normally go:

>>> from sqlalchemy import bindparam
>>> stmt = (
...   update(user_table).
...   where(user_table.c.name == bindparam('oldname')).
...   values(name=bindparam('newname'))
... )
>>> with engine.begin() as conn:
...   conn.execute(
...       stmt,
...       [
...          {'oldname':'jack', 'newname':'ed'},
...          {'oldname':'wendy', 'newname':'mary'},
...          {'oldname':'jim', 'newname':'jake'},
...       ]
...   )
BEGIN (implicit) UPDATE user_account SET name=? WHERE user_account.name = ? [...] (('ed', 'jack'), ('mary', 'wendy'), ('jake', 'jim')) <sqlalchemy.engine.cursor.CursorResult object at 0x...> COMMIT

Other techniques which may be applied to UPDATE include:

Correlated Updates

An UPDATE statement can make use of rows in other tables by using a correlated subquery. A subuqery may be used anywhere a column expression might be placed:

>>> scalar_subq = (
...   select(address_table.c.email_address).
...   where(address_table.c.user_id == user_table.c.id).
...   order_by(address_table.c.id).
...   limit(1).
...   scalar_subquery()
... )
>>> update_stmt = update(user_table).values(fullname=scalar_subq)
>>> print(update_stmt)
UPDATE user_account SET fullname=(SELECT address.email_address FROM address WHERE address.user_id = user_account.id ORDER BY address.id LIMIT :param_1)

UPDATE..FROM

Some databases such as PostgreSQL and MySQL support a syntax “UPDATE FROM” where additional tables may be stated directly in a special FROM clause. This syntax will be generated implicitly when additional tables are located in the WHERE clause of the statement:

>>> update_stmt = (
...    update(user_table).
...    where(user_table.c.id == address_table.c.user_id).
...    where(address_table.c.email_address == 'patrick@aol.com').
...    values(fullname='Pat')
...  )
>>> print(update_stmt)
UPDATE user_account SET fullname=:fullname FROM address WHERE user_account.id = address.user_id AND address.email_address = :email_address_1

There is also a MySQL specific syntax that can UPDATE multiple tables. This requires we refer to Table objects in the VALUES clause in order to refer to additional tables:

>>> update_stmt = (
...    update(user_table).
...    where(user_table.c.id == address_table.c.user_id).
...    where(address_table.c.email_address == 'patrick@aol.com').
...    values(
...        {
...            user_table.c.fullname: "Pat",
...            address_table.c.email_address: "pat@aol.com"
...        }
...    )
...  )
>>> from sqlalchemy.dialects import mysql
>>> print(update_stmt.compile(dialect=mysql.dialect()))
UPDATE user_account, address SET address.email_address=%s, user_account.fullname=%s WHERE user_account.id = address.user_id AND address.email_address = %s

Parameter Ordered Updates

Another MySQL-only behavior is that the order of parameters in the SET clause of an UPDATE actually impacts the evaluation of each expression. For this use case, the Update.ordered_values() method accepts a sequence of tuples so that this order may be controlled [1]_:

>>> update_stmt = (
...     update(some_table).
...     ordered_values(
...         (some_table.c.y, 20),
...         (some_table.c.x, some_table.c.y + 10)
...     )
... )
>>> print(update_stmt)
UPDATE some_table SET y=:y, x=(some_table.y + :y_1)
1

While Python dictionaries are guaranteed to be insert ordered as of Python 3.7, the Update.ordered_values() method stilll provides an additional measure of clarity of intent when it is essential that the SET clause of a MySQL UPDATE statement proceed in a specific way.

The delete() SQL Expression Construct

The delete() function generates a new instance of Delete which represents an DELETE statement in SQL, that will delete rows from a table.

The delete() statement from an API perspective is very similar to that of the update() construct, traditionally returning no rows but allowing for a RETURNING variant on some database backends.

>>> from sqlalchemy import delete
>>> stmt = delete(user_table).where(user_table.c.name == 'patrick')
>>> print(stmt)
DELETE FROM user_account WHERE user_account.name = :name_1

Multiple Table Deletes

Like Update, Delete supports the use of correlated subqueries in the WHERE clause as well as backend-specific multiple table syntaxes, such as DELETE FROM..USING on MySQL:

>>> delete_stmt = (
...    delete(user_table).
...    where(user_table.c.id == address_table.c.user_id).
...    where(address_table.c.email_address == 'patrick@aol.com')
...  )
>>> from sqlalchemy.dialects import mysql
>>> print(delete_stmt.compile(dialect=mysql.dialect()))
DELETE FROM user_account USING user_account, address WHERE user_account.id = address.user_id AND address.email_address = %s

Getting Affected Row Count from UPDATE, DELETE

Both Update and Delete support the ability to return the number of rows matched after the statement proceeds, for statements that are invoked using Core Connection, i.e. Connection.execute(). Per the caveats mentioned below, this value is available from the CursorResult.rowcount attribute:

>>> with engine.begin() as conn:
...     result = conn.execute(
...         update(user_table).
...         values(fullname="Patrick McStar").
...         where(user_table.c.name == 'patrick')
...     )
...     print(result.rowcount)
BEGIN (implicit) UPDATE user_account SET fullname=? WHERE user_account.name = ? [...] ('Patrick McStar', 'patrick')
1
COMMIT

Tip

The CursorResult class is a subclass of Result which contains additional attributes that are specific to the DBAPI cursor object. An instance of this subclass is returned when a statement is invoked via the Connection.execute() method. When using the ORM, the Session.execute() method returns an object of this type for all INSERT, UPDATE, and DELETE statements.

Facts about CursorResult.rowcount:

  • The value returned is the number of rows matched by the WHERE clause of the statement. It does not matter if the row were actually modified or not.

  • CursorResult.rowcount is not necessarily available for an UPDATE or DELETE statement that uses RETURNING.

  • For an executemany execution, CursorResult.rowcount may not be available either, which depends highly on the DBAPI module in use as well as configured options. The attribute CursorResult.supports_sane_multi_rowcount indicates if this value will be available for the current backend in use.

  • Some drivers, particularly third party dialects for non-relational databases, may not support CursorResult.rowcount at all. The CursorResult.supports_sane_rowcount will indicate this.

  • “rowcount” is used by the ORM unit of work process to validate that an UPDATE or DELETE statement matched the expected number of rows, and is also essential for the ORM versioning feature documented at Configuring a Version Counter.

Using RETURNING with UPDATE, DELETE

Like the Insert construct, Update and Delete also support the RETURNING clause which is added by using the Update.returning() and Delete.returning() methods. When these methods are used on a backend that supports RETURNING, selected columns from all rows that match the WHERE criteria of the statement will be returned in the Result object as rows that can be iterated:

>>> update_stmt = (
...     update(user_table).where(user_table.c.name == 'patrick').
...     values(fullname='Patrick the Star').
...     returning(user_table.c.id, user_table.c.name)
... )
>>> print(update_stmt)
UPDATE user_account SET fullname=:fullname WHERE user_account.name = :name_1 RETURNING user_account.id, user_account.name
>>> delete_stmt = ( ... delete(user_table).where(user_table.c.name == 'patrick'). ... returning(user_table.c.id, user_table.c.name) ... ) >>> print(delete_stmt)
DELETE FROM user_account WHERE user_account.name = :name_1 RETURNING user_account.id, user_account.name

Further Reading for UPDATE, DELETE

See also

API documentation for UPDATE / DELETE:

ORM-enabled UPDATE and DELETE:

SQLAlchemy 1.4 / 2.0 Tutorial

Next Tutorial Section: Data Manipulation with the ORM

Previous: Working with Database Metadata Next: Data Manipulation with the ORM