{ "cells": [ { "cell_type": "markdown", "id": "94df0cd4", "metadata": {}, "source": [ "(sqlatutorial:orm-related-objects)=\n", "\n", "# Working with Related Objects\n", "\n", "In this section, we will cover one more essential ORM concept, which is\n", "how the ORM interacts with mapped classes that refer to other objects. In the\n", "section {ref}`sqlatutorial:declaring-mapped-classes`, the mapped class examples\n", "made use of a construct called {func}`~sqlalchemy.orm.relationship`. This construct\n", "defines a linkage between two different mapped classes, or from a mapped class\n", "to itself, the latter of which is called a **self-referential** relationship.\n", "\n", "To describe the basic idea of {func}`~sqlalchemy.orm.relationship`, first we'll review the mapping in short form,\n", "omitting the {class}`~sqlalchemy.schema.Column` mappings and other directives:\n", "\n", "```python\n", "from sqlalchemy.orm import relationship\n", "class User(Base):\n", " __tablename__ = 'user_account'\n", "\n", " # Column mappings\n", "\n", " addresses = relationship(\"Address\", back_populates=\"user\")\n", "\n", "\n", "class Address(Base):\n", " __tablename__ = 'address'\n", "\n", " # Column mappings\n", "\n", " user = relationship(\"User\", back_populates=\"addresses\")\n", "```\n", "\n", "Above, the `User` class now has an attribute `User.addresses` and the\n", "`Address` class has an attribute `Address.user`. The\n", "{func}`~sqlalchemy.orm.relationship` construct will be used to inspect the table\n", "relationships between the {class}`~sqlalchemy.schema.Table` objects that are mapped to the\n", "`User` and `Address` classes. As the {class}`~sqlalchemy.schema.Table` object\n", "representing the\n", "`address` table has a {class}`~sqlalchemy.schema.ForeignKeyConstraint` which refers to\n", "the `user_account` table, the {func}`~sqlalchemy.orm.relationship` can determine\n", "unambiguously that there is a {term}`one to many` relationship from\n", "`User.addresses` to `User`; one particular row in the `user_account`\n", "table may be referred towards by many rows in the `address` table.\n", "\n", "All one-to-many relationships naturally correspond to a {term}`many to one`\n", "relationship in the other direction, in this case the one noted by\n", "`Address.user`. The {paramref}`~sqlalchemy.orm.relationship.back_populates` parameter,\n", "seen above configured on both {func}`~sqlalchemy.orm.relationship` objects referring to\n", "the other name, establishes that each of these two {func}`~sqlalchemy.orm.relationship`\n", "constructs should be considered to be complimentary to each other; we will see\n", "how this plays out in the next section." ] }, { "cell_type": "code", "execution_count": 1, "id": "18a43dd3", "metadata": { "tags": [ "hide-output" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,777 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,778 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"user_account\")\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,779 INFO sqlalchemy.engine.Engine [raw sql] ()\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,780 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"user_account\")\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,780 INFO sqlalchemy.engine.Engine [raw sql] ()\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,781 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"address\")\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,782 INFO sqlalchemy.engine.Engine [raw sql] ()\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,782 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"address\")\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,783 INFO sqlalchemy.engine.Engine [raw sql] ()\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,784 INFO sqlalchemy.engine.Engine \n", "CREATE TABLE user_account (\n", "\tid INTEGER NOT NULL, \n", "\tname VARCHAR(30), \n", "\tfullname VARCHAR, \n", "\tPRIMARY KEY (id)\n", ")\n", "\n", "\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,785 INFO sqlalchemy.engine.Engine [no key 0.00069s] ()\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,786 INFO sqlalchemy.engine.Engine \n", "CREATE TABLE address (\n", "\tid INTEGER NOT NULL, \n", "\temail_address VARCHAR NOT NULL, \n", "\tuser_id INTEGER, \n", "\tPRIMARY KEY (id), \n", "\tFOREIGN KEY(user_id) REFERENCES user_account (id)\n", ")\n", "\n", "\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,786 INFO sqlalchemy.engine.Engine [no key 0.00046s] ()\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,787 INFO sqlalchemy.engine.Engine COMMIT\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,795 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,796 INFO sqlalchemy.engine.Engine INSERT INTO user_account (id, name, fullname) VALUES (?, ?, ?)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,797 INFO sqlalchemy.engine.Engine [generated in 0.00062s] ((1, 'spongebob', 'Spongebob Squarepants'), (2, 'sandy', 'Sandy Cheeks'), (3, 'patrick', 'Patrick Star'))\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,798 INFO sqlalchemy.engine.Engine COMMIT\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,799 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,800 INFO sqlalchemy.engine.Engine INSERT INTO address (email_address, user_id) VALUES (?, ?)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,800 INFO sqlalchemy.engine.Engine [generated in 0.00057s] (('spongebob@sqlalchemy.org', 1), ('sandy@sqlalchemy.org', 2), ('sandy@squirrelpower.org', 2))\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,802 INFO sqlalchemy.engine.Engine COMMIT\n" ] } ], "source": [ "from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey, select, insert, update, delete\n", "from sqlalchemy.orm import aliased, declarative_base, relationship, Session\n", "\n", "Base = declarative_base()\n", "\n", "class User(Base):\n", " __tablename__ = 'user_account'\n", "\n", " id = Column(Integer, primary_key=True)\n", " name = Column(String(30))\n", " fullname = Column(String)\n", "\n", " addresses = relationship(\"Address\", back_populates=\"user\")\n", "\n", " def __repr__(self):\n", " return f\"User({self.name!r}, {self.fullname!r})\"\n", "\n", "class Address(Base):\n", " __tablename__ = 'address'\n", "\n", " id = Column(Integer, primary_key=True)\n", " email_address = Column(String, nullable=False)\n", " user_id = Column(Integer, ForeignKey('user_account.id'))\n", "\n", " user = relationship(\"User\", back_populates=\"addresses\")\n", "\n", " def __repr__(self):\n", " return f\"Address({self.email_address!r})\"\n", "\n", "engine = create_engine(\"sqlite+pysqlite:///:memory:\", echo=True, future=True)\n", "Base.metadata.create_all(engine)\n", "\n", "with engine.begin() as conn:\n", " conn.execute(\n", " insert(User),\n", " [\n", " {\"id\": 1, \"name\": \"spongebob\", \"fullname\": \"Spongebob Squarepants\"},\n", " {\"id\": 2, \"name\": \"sandy\", \"fullname\": \"Sandy Cheeks\"},\n", " {\"id\": 3, \"name\": \"patrick\", \"fullname\": \"Patrick Star\"}\n", " ]\n", " )\n", "with engine.begin() as conn:\n", " conn.execute(\n", " insert(Address),\n", " [\n", " {\"user_id\": 1, \"email_address\": \"spongebob@sqlalchemy.org\"},\n", " {\"user_id\": 2, \"email_address\": \"sandy@sqlalchemy.org\"},\n", " {\"user_id\": 2, \"email_address\": \"sandy@squirrelpower.org\"},\n", " ]\n", " )\n", "\n", "\n", "session = Session(engine)" ] }, { "cell_type": "markdown", "id": "d8e0c02a", "metadata": {}, "source": [ "## Persisting and Loading Relationships\n", "\n", "We can start by illustrating what {func}`~sqlalchemy.orm.relationship` does to instances\n", "of objects. If we make a new `User` object, we can note that there is a\n", "Python list when we access the `.addresses` element:" ] }, { "cell_type": "code", "execution_count": 2, "id": "3bb558d3", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[]" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "u1 = User(name='pkrabs', fullname='Pearl Krabs')\n", "u1.addresses" ] }, { "cell_type": "markdown", "id": "60ad6268", "metadata": {}, "source": [ "This object is a SQLAlchemy-specific version of Python `list` which\n", "has the ability to track and respond to changes made to it. The collection\n", "also appeared automatically when we accessed the attribute, even though we never assigned it to the object.\n", "This is similar to the behavior noted at {ref}`sqlatutorial:inserting-orm` where\n", "it was observed that column-based attributes to which we don't explicitly\n", "assign a value also display as `None` automatically, rather than raising\n", "an `AttributeError` as would be Python's usual behavior.\n", "\n", "As the `u1` object is still {term}`transient` and the `list` that we got\n", "from `u1.addresses` has not been mutated (i.e. appended or extended), it's\n", "not actually associated with the object yet, but as we make changes to it,\n", "it will become part of the state of the `User` object.\n", "\n", "The collection is specific to the `Address` class which is the only type\n", "of Python object that may be persisted within it. Using the `list.append()`\n", "method we may add an `Address` object:" ] }, { "cell_type": "code", "execution_count": 3, "id": "bd87cdbf", "metadata": {}, "outputs": [], "source": [ "a1 = Address(email_address=\"pearl.krabs@gmail.com\")\n", "u1.addresses.append(a1)" ] }, { "cell_type": "markdown", "id": "94c63fd6", "metadata": {}, "source": [ "At this point, the `u1.addresses` collection as expected contains the\n", "new `Address` object:" ] }, { "cell_type": "code", "execution_count": 4, "id": "2f940886", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[Address('pearl.krabs@gmail.com')]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "u1.addresses" ] }, { "cell_type": "markdown", "id": "69bf0db0", "metadata": {}, "source": [ "As we associated the `Address` object with the `User.addresses` collection\n", "of the `u1` instance, another behavior also occurred, which is that the\n", "`User.addresses` relationship synchronized itself with the `Address.user`\n", "relationship, such that we can navigate not only from the `User` object\n", "to the `Address` object, we can also navigate from the `Address` object\n", "back to the \"parent\" `User` object:" ] }, { "cell_type": "code", "execution_count": 5, "id": "eeca7744", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "User('pkrabs', 'Pearl Krabs')" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "a1.user" ] }, { "cell_type": "markdown", "id": "f3de923e", "metadata": {}, "source": [ "This synchronization occurred as a result of our use of the\n", "{paramref}`~sqlalchemy.orm.relationship.back_populates` parameter between the two\n", "{func}`~sqlalchemy.orm.relationship` objects. This parameter names another\n", "{func}`~sqlalchemy.orm.relationship` for which complementary attribute assignment / list\n", "mutation should occur. It will work equally well in the other\n", "direction, which is that if we create another `Address` object and assign\n", "to its `Address.user` attribute, that `Address` becomes part of the\n", "`User.addresses` collection on that `User` object:" ] }, { "cell_type": "code", "execution_count": 6, "id": "62133b34", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[Address('pearl.krabs@gmail.com'), Address('pearl@aol.com')]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "a2 = Address(email_address=\"pearl@aol.com\", user=u1)\n", "u1.addresses" ] }, { "cell_type": "markdown", "id": "ec59cba1", "metadata": {}, "source": [ "We actually made use of the `user` parameter as a keyword argument in the\n", "`Address` constructor, which is accepted just like any other mapped attribute\n", "that was declared on the `Address` class. It is equivalent to assignment\n", "of the `Address.user` attribute after the fact:" ] }, { "cell_type": "code", "execution_count": 7, "id": "e3e1e51d", "metadata": {}, "outputs": [], "source": [ "# equivalent effect as a2 = Address(user=u1)\n", "a2.user = u1" ] }, { "cell_type": "markdown", "id": "850855e5", "metadata": {}, "source": [ "### Cascading Objects into the Session\n", "\n", "We now have a `User` and two `Address` objects that are associated in a\n", "bidirectional structure\n", "in memory, but as noted previously in {ref}`sqlatutorial:inserting-orm` ,\n", "these objects are said to be in the {term}`transient` state until they\n", "are associated with a {class}`~sqlalchemy.orm.Session` object.\n", "\n", "We make use of the {class}`~sqlalchemy.orm.Session` that's still ongoing, and note that\n", "when we apply the {meth}`~sqlalchemy.orm.Session.add` method to the lead `User` object,\n", "the related `Address` object also gets added to that same {class}`~sqlalchemy.orm.Session`:" ] }, { "cell_type": "code", "execution_count": 8, "id": "3209e5af", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(True, True, True)" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "session.add(u1)\n", "u1 in session, a1 in session, a2 in session" ] }, { "cell_type": "markdown", "id": "03f9f01c", "metadata": {}, "source": [ "The above behavior, where the {class}`~sqlalchemy.orm.Session` received a `User` object,\n", "and followed along the `User.addresses` relationship to locate a related\n", "`Address` object, is known as the **save-update cascade** and is discussed\n", "in detail in the ORM reference documentation at {ref}`unitofwork_cascades`.\n", "\n", "The three objects are now in the {term}`pending` state; this means they are\n", "ready to be the subject of an INSERT operation but this has not yet proceeded;\n", "all three objects have no primary key assigned yet, and in addition, the `a1`\n", "and `a2` objects have an attribute called `user_id` which refers to the\n", "{class}`~sqlalchemy.schema.Column` that has a {class}`~sqlalchemy.schema.ForeignKeyConstraint`\n", "referring to the `user_account.id` column; these are also `None` as the\n", "objects are not yet associated with a real database row:" ] }, { "cell_type": "code", "execution_count": 9, "id": "d54703fe", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "None\n", "None\n" ] } ], "source": [ "print(u1.id)\n", "print(a1.user_id)" ] }, { "cell_type": "markdown", "id": "ff553a25", "metadata": {}, "source": [ "It's at this stage that we can see the very great utility that the unit of\n", "work process provides; recall in the section {ref}`sqlatutorial:core-insert-values-clause`,\n", "rows were inserted into the `user_account` and\n", "`address` tables using some elaborate syntaxes in order to automatically\n", "associate the `address.user_id` columns with those of the `user_account`\n", "rows. Additionally, it was necessary that we emit INSERT for `user_account`\n", "rows first, before those of `address`, since rows in `address` are\n", "**dependent** on their parent row in `user_account` for a value in their\n", "`user_id` column.\n", "\n", "When using the {class}`~sqlalchemy.orm.Session`, all this tedium is handled for us and\n", "even the most die-hard SQL purist can benefit from automation of INSERT,\n", "UPDATE and DELETE statements. When we {meth}`~sqlalchemy.orm.Session.commit` the\n", "transaction all steps invoke in the correct order, and furthermore the\n", "newly generated primary key of the `user_account` row is applied to the\n", "`address.user_id` column appropriately:" ] }, { "cell_type": "code", "execution_count": 10, "id": "29d3b871", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,868 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,870 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (?, ?)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,870 INFO sqlalchemy.engine.Engine [generated in 0.00064s] ('pkrabs', 'Pearl Krabs')\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,872 INFO sqlalchemy.engine.Engine INSERT INTO address (email_address, user_id) VALUES (?, ?)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,873 INFO sqlalchemy.engine.Engine [generated in 0.00074s] ('pearl.krabs@gmail.com', 4)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,874 INFO sqlalchemy.engine.Engine INSERT INTO address (email_address, user_id) VALUES (?, ?)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,874 INFO sqlalchemy.engine.Engine [cached since 0.002145s ago] ('pearl@aol.com', 4)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,875 INFO sqlalchemy.engine.Engine COMMIT\n" ] } ], "source": [ "session.commit()" ] }, { "cell_type": "markdown", "id": "144843b9", "metadata": {}, "source": [ "(sqlatutorial:loading-relationships)=\n", "\n", "## Loading Relationships\n", "\n", "In the last step, we called {meth}`~sqlalchemy.orm.Session.commit` which emitted a COMMIT\n", "for the transaction, and then per\n", "{paramref}`~sqlalchemy.orm.Session.commit.expire_on_commit` expired all objects so that\n", "they refresh for the next transaction.\n", "\n", "When we next access an attribute on these objects, we'll see the SELECT\n", "emitted for the primary attributes of the row, such as when we view the\n", "newly generated primary key for the `u1` object:" ] }, { "cell_type": "code", "execution_count": 11, "id": "fe391782", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,885 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,888 INFO sqlalchemy.engine.Engine SELECT user_account.id AS user_account_id, user_account.name AS user_account_name, user_account.fullname AS user_account_fullname \n", "FROM user_account \n", "WHERE user_account.id = ?\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,888 INFO sqlalchemy.engine.Engine [generated in 0.00054s] (4,)\n" ] }, { "data": { "text/plain": [ "4" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "u1.id" ] }, { "cell_type": "markdown", "id": "59908e31", "metadata": {}, "source": [ "The `u1` `User` object now has a persistent collection `User.addresses`\n", "that we may also access. As this collection consists of an additional set\n", "of rows from the `address` table, when we access this collection as well\n", "we again see a {term}`lazy load` emitted in order to retrieve the objects:" ] }, { "cell_type": "code", "execution_count": 12, "id": "a1bf7819", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,899 INFO sqlalchemy.engine.Engine SELECT address.id AS address_id, address.email_address AS address_email_address, address.user_id AS address_user_id \n", "FROM address \n", "WHERE ? = address.user_id\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,900 INFO sqlalchemy.engine.Engine [generated in 0.00079s] (4,)\n" ] }, { "data": { "text/plain": [ "[Address('pearl.krabs@gmail.com'), Address('pearl@aol.com')]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "u1.addresses" ] }, { "cell_type": "markdown", "id": "877cea38", "metadata": {}, "source": [ "Collections and related attributes in the SQLAlchemy ORM are persistent in\n", "memory; once the collection or attribute is populated, SQL is no longer emitted\n", "until that collection or attribute is {term}`expired`. We may access\n", "`u1.addresses` again as well as add or remove items and this will not\n", "incur any new SQL calls:" ] }, { "cell_type": "code", "execution_count": 13, "id": "5b6516fa", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[Address('pearl.krabs@gmail.com'), Address('pearl@aol.com')]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "u1.addresses" ] }, { "cell_type": "markdown", "id": "cd859edc", "metadata": {}, "source": [ "While the loading emitted by lazy loading can quickly become expensive if\n", "we don't take explicit steps to optimize it, the network of lazy loading\n", "at least is fairly well optimized to not perform redundant work; as the\n", "`u1.addresses` collection was refreshed, per the {term}`identity map`\n", "these are in fact the same\n", "`Address` instances as the `a1` and `a2` objects we've been dealing with\n", "already, so we're done loading all attributes in this particular object\n", "graph:" ] }, { "cell_type": "code", "execution_count": 14, "id": "203949b3", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Address('pearl.krabs@gmail.com')" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "a1" ] }, { "cell_type": "code", "execution_count": 15, "id": "2bbfc0df", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Address('pearl@aol.com')" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "a2" ] }, { "cell_type": "markdown", "id": "c1433700", "metadata": {}, "source": [ "The issue of how relationships load, or not, is an entire subject onto\n", "itself. Some additional introduction to these concepts is later in this\n", "section at {ref}`sqlatutorial:orm-loader-strategies`.\n", "\n", "(sqlatutorial:select-relationships)=\n", "\n", "## Using Relationships in Queries\n", "\n", "The previous section introduced the behavior of the {func}`~sqlalchemy.orm.relationship`\n", "construct when working with **instances of a mapped class**, above, the\n", "`u1`, `a1` and `a2` instances of the `User` and `Address` classes.\n", "In this section, we introduce the behavior of {func}`~sqlalchemy.orm.relationship` as it\n", "applies to **class level behavior of a mapped class**, where it serves in\n", "several ways to help automate the construction of SQL queries.\n", "\n", "(sqlatutorial:joining-relationships)=\n", "\n", "### Using Relationships to Join\n", "\n", "The sections {ref}`sqlatutorial:select-join` and\n", "{ref}`sqlatutorial:select-join-onclause` introduced the usage of the\n", "{meth}`~sqlalchemy.sql.expression.Select.join` and {meth}`~sqlalchemy.sql.expression.Select.join_from` methods to compose\n", "SQL JOIN clauses. In order to describe how to join between tables, these\n", "methods either **infer** the ON clause based on the presence of a single\n", "unambiguous {class}`~sqlalchemy.schema.ForeignKeyConstraint` object within the table\n", "metadata structure that links the two tables, or otherwise we may provide an\n", "explicit SQL Expression construct that indicates a specific ON clause.\n", "\n", "When using ORM entities, an additional mechanism is available to help us set up\n", "the ON clause of a join, which is to make use of the {func}`~sqlalchemy.orm.relationship`\n", "objects that we set up in our user mapping, as was demonstrated at\n", "{ref}`sqlatutorial:declaring-mapped-classes`. The class-bound attribute\n", "corresponding to the {func}`~sqlalchemy.orm.relationship` may be passed as the **single\n", "argument** to {meth}`~sqlalchemy.sql.expression.Select.join`, where it serves to indicate both the\n", "right side of the join as well as the ON clause at once:" ] }, { "cell_type": "code", "execution_count": 16, "id": "1ecee79b", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "SELECT address.email_address \n", "FROM user_account JOIN address ON user_account.id = address.user_id\n" ] } ], "source": [ "print(\n", " select(Address.email_address).\n", " select_from(User).\n", " join(User.addresses)\n", ")" ] }, { "cell_type": "markdown", "id": "04e88f20", "metadata": {}, "source": [ "The presence of an ORM {func}`~sqlalchemy.orm.relationship` on a mapping is not used\n", "by {meth}`~sqlalchemy.sql.expression.Select.join` or {meth}`~sqlalchemy.sql.expression.Select.join_from` if we don't\n", "specify it; it is **not used for ON clause\n", "inference**. This means, if we join from `User` to `Address` without an\n", "ON clause, it works because of the {class}`~sqlalchemy.schema.ForeignKeyConstraint`\n", "between the two mapped {class}`~sqlalchemy.schema.Table` objects, not because of the\n", "{func}`~sqlalchemy.orm.relationship` objects on the `User` and `Address` classes:" ] }, { "cell_type": "code", "execution_count": 17, "id": "c08800bb", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "SELECT address.email_address \n", "FROM user_account JOIN address ON user_account.id = address.user_id\n" ] } ], "source": [ "print(\n", " select(Address.email_address).\n", " join_from(User, Address)\n", ")" ] }, { "cell_type": "markdown", "id": "317ba01e", "metadata": {}, "source": [ "(sqlatutorial:joining-relationships-aliased)=\n", "\n", "### Joining between Aliased targets\n", "\n", "In the section {ref}`sqlatutorial:orm-entity-aliases` we introduced the\n", "{func}`~sqlalchemy.orm.aliased` construct, which is used to apply a SQL alias to an\n", "ORM entity. When using a {func}`~sqlalchemy.orm.relationship` to help construct SQL JOIN, the\n", "use case where the target of the join is to be an {func}`~sqlalchemy.orm.aliased` is suited\n", "by making use of the {meth}`~sqlalchemy.orm.PropComparator.of_type` modifier.\n", "To demonstrate we will construct the same join illustrated at {ref}`sqlatutorial:orm-entity-aliases` using the {func}`~sqlalchemy.orm.relationship` attributes to join instead:\n", "\n", "```python\n", "print(\n", " select(User).\n", " join(User.addresses.of_type(address_alias_1)).\n", " where(address_alias_1.email_address == 'patrick@aol.com').\n", " join(User.addresses.of_type(address_alias_2)).\n", " where(address_alias_2.email_address == 'patrick@gmail.com')\n", " )\n", "```\n", "\n", "To make use of a {func}`~sqlalchemy.orm.relationship` to construct a join **from** an aliased entity,\n", "the attribute is available from the {func}`~sqlalchemy.orm.aliased` construct directly:" ] }, { "cell_type": "code", "execution_count": 18, "id": "ae7368bd", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "SELECT user_account_1.name \n", "FROM user_account AS user_account_1 JOIN address ON user_account_1.id = address.user_id\n" ] } ], "source": [ "user_alias_1 = aliased(User)\n", "print(\n", " select(user_alias_1.name).\n", " join(user_alias_1.addresses)\n", ")" ] }, { "cell_type": "markdown", "id": "bff27030", "metadata": {}, "source": [ "(sqlatutorial:joining-relationships-augmented)=\n", "\n", "### Augmenting the ON Criteria\n", "\n", "The ON clause generated by the {func}`~sqlalchemy.orm.relationship` construct may\n", "also be augmented with additional criteria. This is useful both for\n", "quick ways to limit the scope of a particular join over a relationship path,\n", "and also for use cases like configuring loader strategies, introduced below\n", "at {ref}`sqlatutorial:orm-loader-strategies`. The {meth}`~sqlalchemy.orm.PropComparator.and_`\n", "method accepts a series of SQL expressions positionally that will be joined\n", "to the ON clause of the JOIN via AND. For example if we wanted to\n", "JOIN from `User` to `Address` but also limit the ON criteria to only certain\n", "email addresses:" ] }, { "cell_type": "code", "execution_count": 19, "id": "7852e7f5", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,948 INFO sqlalchemy.engine.Engine SELECT user_account.fullname \n", "FROM user_account JOIN address ON user_account.id = address.user_id AND address.email_address = ?\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,949 INFO sqlalchemy.engine.Engine [generated in 0.00077s] ('pearl.krabs@gmail.com',)\n" ] }, { "data": { "text/plain": [ "[('Pearl Krabs',)]" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "stmt = (\n", " select(User.fullname).\n", " join(User.addresses.and_(Address.email_address == 'pearl.krabs@gmail.com'))\n", ")\n", "session.execute(stmt).all()" ] }, { "cell_type": "markdown", "id": "430a49a0", "metadata": {}, "source": [ "(sqlatutorial:relationship-exists)=\n", "\n", "### EXISTS forms: has() / any()\n", "\n", "In the section {ref}`sqlatutorial:exists`, we introduced the {class}`~sqlalchemy.sql.expression.Exists`\n", "object that provides for the SQL EXISTS keyword in conjunction with a\n", "scalar subquery. The {func}`~sqlalchemy.orm.relationship` construct provides for some\n", "helper methods that may be used to generate some common EXISTS styles\n", "of queries in terms of the relationship.\n", "\n", "For a one-to-many relationship such as `User.addresses`, an EXISTS against\n", "the `address` table that correlates back to the `user_account` table\n", "can be produced using {meth}`~sqlalchemy.orm.PropComparator.any`. This method accepts\n", "an optional WHERE criteria to limit the rows matched by the subquery:" ] }, { "cell_type": "code", "execution_count": 20, "id": "7b3bcc6e", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,959 INFO sqlalchemy.engine.Engine SELECT user_account.fullname \n", "FROM user_account \n", "WHERE EXISTS (SELECT 1 \n", "FROM address \n", "WHERE user_account.id = address.user_id AND address.email_address = ?)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,959 INFO sqlalchemy.engine.Engine [generated in 0.00081s] ('pearl.krabs@gmail.com',)\n" ] }, { "data": { "text/plain": [ "[('Pearl Krabs',)]" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "stmt = (\n", " select(User.fullname).\n", " where(User.addresses.any(Address.email_address == 'pearl.krabs@gmail.com'))\n", ")\n", "session.execute(stmt).all()" ] }, { "cell_type": "markdown", "id": "2aff077d", "metadata": {}, "source": [ "As EXISTS tends to be more efficient for negative lookups, a common query\n", "is to locate entities where there are no related entities present. This\n", "is succinct using a phrase such as `~User.addresses.any()`, to select\n", "for `User` entities that have no related `Address` rows:" ] }, { "cell_type": "code", "execution_count": 21, "id": "e57efc22", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,969 INFO sqlalchemy.engine.Engine SELECT user_account.fullname \n", "FROM user_account \n", "WHERE NOT (EXISTS (SELECT 1 \n", "FROM address \n", "WHERE user_account.id = address.user_id))\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,969 INFO sqlalchemy.engine.Engine [generated in 0.00075s] ()\n" ] }, { "data": { "text/plain": [ "[('Patrick Star',)]" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "stmt = (\n", " select(User.fullname).\n", " where(~User.addresses.any())\n", ")\n", "session.execute(stmt).all()" ] }, { "cell_type": "markdown", "id": "f015e106", "metadata": {}, "source": [ "The {meth}`~sqlalchemy.orm.PropComparator.has` method works in mostly the same way as\n", "{meth}`~sqlalchemy.orm.PropComparator.any`, except that it's used for many-to-one\n", "relationships, such as if we wanted to locate all `Address` objects\n", "which belonged to \"pearl\":" ] }, { "cell_type": "code", "execution_count": 22, "id": "c4ec5f49", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,980 INFO sqlalchemy.engine.Engine SELECT address.email_address \n", "FROM address \n", "WHERE EXISTS (SELECT 1 \n", "FROM user_account \n", "WHERE user_account.id = address.user_id AND user_account.name = ?)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:53,980 INFO sqlalchemy.engine.Engine [generated in 0.00057s] ('pkrabs',)\n" ] }, { "data": { "text/plain": [ "[('pearl.krabs@gmail.com',), ('pearl@aol.com',)]" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "stmt = (\n", " select(Address.email_address).\n", " where(Address.user.has(User.name==\"pkrabs\"))\n", ")\n", "session.execute(stmt).all()" ] }, { "cell_type": "markdown", "id": "14e5a1a9", "metadata": {}, "source": [ "(sqlatutorial:relationship-operators)=\n", "\n", "### Common Relationship Operators\n", "\n", "There are some additional varieties of SQL generation helpers that come with\n", "{func}`~sqlalchemy.orm.relationship`, including:\n", "\n", "- **many to one equals comparison** - a specific object instance can be\n", " compared to many-to-one relationship, to select rows where the\n", " foreign key of the target entity matches the primary key value of the\n", " object given:" ] }, { "cell_type": "code", "execution_count": 23, "id": "3b33970b", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "SELECT address.id, address.email_address, address.user_id \n", "FROM address \n", "WHERE :param_1 = address.user_id\n" ] } ], "source": [ "print(select(Address).where(Address.user == u1))" ] }, { "cell_type": "markdown", "id": "b2a3bf8d", "metadata": {}, "source": [ "- **many to one not equals comparison** - the not equals operator may also\n", " be used:" ] }, { "cell_type": "code", "execution_count": 24, "id": "a076cf02", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "SELECT address.id, address.email_address, address.user_id \n", "FROM address \n", "WHERE address.user_id != :user_id_1 OR address.user_id IS NULL\n" ] } ], "source": [ "print(select(Address).where(Address.user != u1))" ] }, { "cell_type": "markdown", "id": "03156be1", "metadata": {}, "source": [ "- **object is contained in a one-to-many collection** - this is essentially\n", " the one-to-many version of the \"equals\" comparison, select rows where the\n", " primary key equals the value of the foreign key in a related object:" ] }, { "cell_type": "code", "execution_count": 25, "id": "5e374751", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "SELECT user_account.id, user_account.name, user_account.fullname \n", "FROM user_account \n", "WHERE user_account.id = :param_1\n" ] } ], "source": [ "print(select(User).where(User.addresses.contains(a1)))" ] }, { "cell_type": "markdown", "id": "bc680a93", "metadata": {}, "source": [ "- **An object has a particular parent from a one-to-many perspective** - the\n", " {func}`~sqlalchemy.orm.with_parent` function produces a comparison that returns rows\n", " which are referred towards by a given parent, this is essentially the\n", " same as using the `==` operator with the many-to-one side:" ] }, { "cell_type": "code", "execution_count": 26, "id": "bc39a70b", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "SELECT address.id, address.email_address, address.user_id \n", "FROM address \n", "WHERE :param_1 = address.user_id\n" ] } ], "source": [ "from sqlalchemy.orm import with_parent\n", "print(select(Address).where(with_parent(u1, User.addresses)))" ] }, { "cell_type": "markdown", "id": "c19fd039", "metadata": {}, "source": [ "(sqlatutorial:orm-loader-strategies)=\n", "\n", "## Loader Strategies\n", "\n", "In the section {ref}`sqlatutorial:loading-relationships` we introduced the concept\n", "that when we work with instances of mapped objects, accessing the attributes\n", "that are mapped using {func}`~sqlalchemy.orm.relationship` in the default case will emit\n", "a {term}`lazy load` when the collection is not populated in order to load\n", "the objects that should be present in this collection.\n", "\n", "Lazy loading is one of the most famous ORM patterns, and is also the one that\n", "is most controversial. When several dozen ORM objects in memory each refer to\n", "a handful of unloaded attributes, routine manipulation of these objects can\n", "spin off many additional queries that can add up (otherwise known as the\n", "{term}`N plus one problem`), and to make matters worse they are emitted\n", "implicitly. These implicit queries may not be noticed, may cause errors\n", "when they are attempted after there's no longer a database tranasction\n", "available, or when using alternative concurrency patterns such as {ref}`asyncio `, they actually won't work at all.\n", "\n", "At the same time, lazy loading is a vastly popular and useful pattern when it\n", "is compatible with the concurrency approach in use and isn't otherwise causing\n", "problems. For these reasons, SQLAlchemy's ORM places a lot of emphasis on\n", "being able to control and optimize this loading behavior.\n", "\n", "Above all, the first step in using ORM lazy loading effectively is to **test\n", "the application, turn on SQL echoing, and watch the SQL that is emitted**. If\n", "there seem to be lots of redundant SELECT statements that look very much like\n", "they could be rolled into one much more efficiently, if there are loads\n", "occurring inappropriately for objects that have been {term}`detached` from\n", "their {class}`~sqlalchemy.orm.Session`, that's when to look into using **loader\n", "strategies**.\n", "\n", "Loader strategies are represented as objects that may be associated with a\n", "SELECT statement using the {meth}`~sqlalchemy.sql.expression.Select.options` method, e.g.:\n", "\n", "```python\n", "for user_obj in session.execute(\n", " select(User).options(selectinload(User.addresses))\n", ").scalars():\n", " user_obj.addresses # access addresses collection already loaded\n", "```\n", "\n", "They may be also configured as defaults for a {func}`~sqlalchemy.orm.relationship` using\n", "the {paramref}`~sqlalchemy.orm.relationship.lazy` option, e.g.:\n", "\n", "```python\n", "from sqlalchemy.orm import relationship\n", "class User(Base):\n", " __tablename__ = 'user_account'\n", "\n", " addresses = relationship(\"Address\", back_populates=\"user\", lazy=\"selectin\")\n", "```\n", "\n", "Each loader strategy object adds some kind of information to the statement that\n", "will be used later by the {class}`~sqlalchemy.orm.Session` when it is deciding how various\n", "attributes should be loaded and/or behave when they are accessed.\n", "\n", "The sections below will introduce a few of the most prominently used\n", "loader strategies.\n", "\n", ":::{seealso}\n", "Two sections in {ref}`loading_toplevel`:\n", "\n", "- {ref}`relationship_lazy_option` - details on configuring the strategy\n", " on {func}`~sqlalchemy.orm.relationship`\n", "- {ref}`relationship_loader_options` - details on using query-time\n", " loader strategies\n", ":::\n", "\n", "### Selectin Load\n", "\n", "The most useful loader in modern SQLAlchemy is the\n", "{func}`~sqlalchemy.orm.selectinload` loader option. This option solves the most common\n", "form of the \"N plus one\" problem which is that of a set of objects that refer\n", "to related collections. {func}`~sqlalchemy.orm.selectinload` will ensure that a particular\n", "collection for a full series of objects are loaded up front using a single\n", "query. It does this using a SELECT form that in most cases can be emitted\n", "against the related table alone, without the introduction of JOINs or\n", "subqueries, and only queries for those parent objects for which the\n", "collection isn't already loaded. Below we illustrate {func}`~sqlalchemy.orm.selectinload`\n", "by loading all of the `User` objects and all of their related `Address`\n", "objects; while we invoke {meth}`~sqlalchemy.orm.Session.execute` only once, given a\n", "{func}`~sqlalchemy.sql.expression.select` construct, when the database is accessed, there are\n", "in fact two SELECT statements emitted, the second one being to fetch the\n", "related `Address` objects:" ] }, { "cell_type": "code", "execution_count": 27, "id": "a66404f5", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:54,016 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname \n", "FROM user_account ORDER BY user_account.id\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:54,017 INFO sqlalchemy.engine.Engine [generated in 0.00062s] ()\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:54,021 INFO sqlalchemy.engine.Engine SELECT address.user_id AS address_user_id, address.id AS address_id, address.email_address AS address_email_address \n", "FROM address \n", "WHERE address.user_id IN (?, ?, ?, ?)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:54,021 INFO sqlalchemy.engine.Engine [generated in 0.00073s] (1, 2, 3, 4)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "spongebob (spongebob@sqlalchemy.org)\n", "sandy (sandy@sqlalchemy.org, sandy@squirrelpower.org)\n", "patrick ()\n", "pkrabs (pearl.krabs@gmail.com, pearl@aol.com)\n" ] } ], "source": [ "from sqlalchemy.orm import selectinload\n", "stmt = (\n", " select(User).options(selectinload(User.addresses)).order_by(User.id)\n", ")\n", "for row in session.execute(stmt):\n", " print(f\"{row.User.name} ({', '.join(a.email_address for a in row.User.addresses)})\")" ] }, { "cell_type": "markdown", "id": "8643b53d", "metadata": {}, "source": [ ":::{seealso}\n", "{ref}`selectin_eager_loading` - in {ref}`loading_toplevel`\n", ":::\n", "\n", "### Joined Load\n", "\n", "The {func}`~sqlalchemy.orm.joinedload` eager load strategy is the oldest eager loader in\n", "SQLAlchemy, which augments the SELECT statement that's being passed to the\n", "database with a JOIN (which may be an outer or an inner join depending on options),\n", "which can then load in related objects.\n", "\n", "The {func}`~sqlalchemy.orm.joinedload` strategy is best suited towards loading\n", "related many-to-one objects, as this only requires that additional columns\n", "are added to a primary entity row that would be fetched in any case.\n", "For greater effiency, it also accepts an option {paramref}`~sqlalchemy.orm.joinedload.innerjoin`\n", "so that an inner join instead of an outer join may be used for a case such\n", "as below where we know that all `Address` objects have an associated\n", "`User`:" ] }, { "cell_type": "code", "execution_count": 28, "id": "61d76df8", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:54,032 INFO sqlalchemy.engine.Engine SELECT address.id, address.email_address, address.user_id, user_account_1.id AS id_1, user_account_1.name, user_account_1.fullname \n", "FROM address JOIN user_account AS user_account_1 ON user_account_1.id = address.user_id ORDER BY address.id\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:54,032 INFO sqlalchemy.engine.Engine [generated in 0.00074s] ()\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "spongebob@sqlalchemy.org spongebob\n", "sandy@sqlalchemy.org sandy\n", "sandy@squirrelpower.org sandy\n", "pearl.krabs@gmail.com pkrabs\n", "pearl@aol.com pkrabs\n" ] } ], "source": [ "from sqlalchemy.orm import joinedload\n", "stmt = (\n", " select(Address).options(joinedload(Address.user, innerjoin=True)).order_by(Address.id)\n", ")\n", "for row in session.execute(stmt):\n", " print(f\"{row.Address.email_address} {row.Address.user.name}\")" ] }, { "cell_type": "markdown", "id": "6d6a4f52", "metadata": {}, "source": [ "{func}`~sqlalchemy.orm.joinedload` also works for collections, meaning one-to-many relationships,\n", "however it has the effect\n", "of multiplying out primary rows per related item in a recursive way\n", "that grows the amount of data sent for a result set by orders of magnitude for\n", "nested collections and/or larger collections, so its use vs. another option\n", "such as {func}`~sqlalchemy.orm.selectinload` should be evaluated on a per-case basis.\n", "\n", "It's important to note that the WHERE and ORDER BY criteria of the enclosing\n", "{class}`~sqlalchemy.sql.expression.Select` statement **do not target the table rendered by\n", "joinedload()**. Above, it can be seen in the SQL that an **anonymous alias**\n", "is applied to the `user_account` table such that is not directly addressable\n", "in the query. This concept is discussed in more detail in the section\n", "{ref}`zen_of_eager_loading`.\n", "\n", "The ON clause rendered by {func}`~sqlalchemy.orm.joinedload` may be affected directly by\n", "using the {meth}`~sqlalchemy.orm.PropComparator.and_` method described previously at\n", "{ref}`sqlatutorial:joining-relationships-augmented`; examples of this technique\n", "with loader strategies are further below at {ref}`sqlatutorial:loader-strategy-augmented`.\n", "However, more generally, \"joined eager loading\" may be applied to a\n", "{class}`~sqlalchemy.sql.expression.Select` that uses {meth}`~sqlalchemy.sql.expression.Select.join` using the approach\n", "described in the next section,\n", "{ref}`sqlatutorial:orm-loader-strategies-contains-eager`.\n", "\n", ":::{tip}\n", "It's important to note that many-to-one eager loads are often not necessary,\n", "as the \"N plus one\" problem is much less prevalent in the common case. When\n", "many objects all refer to the same related object, such as many `Address`\n", "objects that each refer to the same `User`, SQL will be emitted only once\n", "for that `User` object using normal lazy loading. The lazy load routine\n", "will look up the related object by primary key in the current\n", "{class}`~sqlalchemy.orm.Session` without emitting any SQL when possible.\n", ":::\n", "\n", ":::{seealso}\n", "{ref}`joined_eager_loading` - in {ref}`loading_toplevel`\n", ":::\n", "\n", "(sqlatutorial:orm-loader-strategies-contains-eager)=\n", "\n", "### Explicit Join + Eager load\n", "\n", "If we were to load `Address` rows while joining to the `user_account` table\n", "using a method such as {meth}`~sqlalchemy.sql.expression.Select.join` to render the JOIN, we could\n", "also leverage that JOIN in order to eagerly load the contents of the\n", "`Address.user` attribute on each `Address` object returned. This is\n", "essentially that we are using \"joined eager loading\" but rendering the JOIN\n", "ourselves. This common use case is acheived by using the\n", "{func}`~sqlalchemy.orm.contains_eager` option. This option is very similar to\n", "{func}`~sqlalchemy.orm.joinedload`, except that it assumes we have set up the JOIN\n", "ourselves, and it instead only indicates that additional columns in the COLUMNS\n", "clause should be loaded into related attributes on each returned object, for\n", "example:" ] }, { "cell_type": "code", "execution_count": 29, "id": "a427a036", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:54,043 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname, address.id AS id_1, address.email_address, address.user_id \n", "FROM address JOIN user_account ON user_account.id = address.user_id \n", "WHERE user_account.name = ? ORDER BY address.id\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:54,044 INFO sqlalchemy.engine.Engine [generated in 0.00074s] ('pkrabs',)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "pearl.krabs@gmail.com pkrabs\n", "pearl@aol.com pkrabs\n" ] } ], "source": [ "from sqlalchemy.orm import contains_eager\n", "stmt = (\n", " select(Address).\n", " join(Address.user).\n", " where(User.name == 'pkrabs').\n", " options(contains_eager(Address.user)).order_by(Address.id)\n", ")\n", "for row in session.execute(stmt):\n", " print(f\"{row.Address.email_address} {row.Address.user.name}\")" ] }, { "cell_type": "markdown", "id": "97c6b2cd", "metadata": {}, "source": [ "Above, we both filtered the rows on `user_account.name` and also loaded\n", "rows from `user_account` into the `Address.user` attribute of the returned\n", "rows. If we had applied {func}`~sqlalchemy.orm.joinedload` separately, we would get a\n", "SQL query that unnecessarily joins twice:" ] }, { "cell_type": "code", "execution_count": 30, "id": "71c2081f", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "SELECT address.id, address.email_address, address.user_id, user_account_1.id AS id_1, user_account_1.name, user_account_1.fullname \n", "FROM address JOIN user_account ON user_account.id = address.user_id LEFT OUTER JOIN user_account AS user_account_1 ON user_account_1.id = address.user_id \n", "WHERE user_account.name = :name_1 ORDER BY address.id\n" ] } ], "source": [ "stmt = (\n", " select(Address).\n", " join(Address.user).\n", " where(User.name == 'pkrabs').\n", " options(joinedload(Address.user)).order_by(Address.id)\n", ")\n", "print(stmt) # SELECT has a JOIN and LEFT OUTER JOIN unnecessarily" ] }, { "cell_type": "markdown", "id": "afd18589", "metadata": {}, "source": [ ":::{seealso}\n", "Two sections in {ref}`loading_toplevel`:\n", "\n", "- {ref}`zen_of_eager_loading` - describes the above problem in detail\n", "- {ref}`contains_eager` - using {func}`~sqlalchemy.orm.contains_eager`\n", ":::\n", "\n", "(sqlatutorial:loader-strategy-augmented)=\n", "\n", "### Augmenting Loader Strategy Paths\n", "\n", "In {ref}`sqlatutorial:joining-relationships-augmented` we illustrated how to add\n", "arbitrary criteria to a JOIN rendered with {func}`~sqlalchemy.orm.relationship` to also\n", "include additional criteria in the ON clause. The {meth}`~sqlalchemy.orm.PropComparator.and_`\n", "method is in fact generally available for most loader options. For example,\n", "if we wanted to re-load the names of users and their email addresses, but omitting\n", "the email addresses with the `sqlalchemy.org` domain, we can apply\n", "{meth}`~sqlalchemy.orm.PropComparator.and_` to the argument passed to\n", "{func}`~sqlalchemy.orm.selectinload` to limit this criteria:" ] }, { "cell_type": "code", "execution_count": 31, "id": "bae2ac69", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:54,060 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname \n", "FROM user_account ORDER BY user_account.id\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:54,062 INFO sqlalchemy.engine.Engine [generated in 0.00058s] ()\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:54,094 INFO sqlalchemy.engine.Engine SELECT address.user_id AS address_user_id, address.id AS address_id, address.email_address AS address_email_address \n", "FROM address \n", "WHERE address.user_id IN (?, ?, ?, ?) AND (address.email_address NOT LIKE '%' || ?)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:54,094 INFO sqlalchemy.engine.Engine [generated in 0.00064s] (1, 2, 3, 4, 'sqlalchemy.org')\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "spongebob ()\n", "sandy (sandy@squirrelpower.org)\n", "patrick ()\n", "pkrabs (pearl.krabs@gmail.com, pearl@aol.com)\n" ] } ], "source": [ "from sqlalchemy.orm import selectinload\n", "stmt = (\n", " select(User).\n", " options(\n", " selectinload(\n", " User.addresses.and_(\n", " ~Address.email_address.endswith(\"sqlalchemy.org\")\n", " )\n", " )\n", " ).\n", " order_by(User.id).\n", " execution_options(populate_existing=True)\n", ")\n", "for row in session.execute(stmt):\n", " print(f\"{row.User.name} ({', '.join(a.email_address for a in row.User.addresses)})\")" ] }, { "cell_type": "markdown", "id": "5917f574", "metadata": {}, "source": [ "A very important thing to note above is that a special option is added with\n", "`.execution_options(populate_existing=True)`. This option which takes\n", "effect when rows are being fetched indicates that the loader option we are\n", "using should **replace** the existing contents of collections on the objects,\n", "if they are already loaded. As we are working with a single\n", "{class}`~sqlalchemy.orm.Session` repeatedly, the objects we see being loaded above are the\n", "same Python instances as those that were first persisted at the start of the\n", "ORM section of this tutorial.\n", "\n", ":::{seealso}\n", "{ref}`loader_option_criteria` - in {ref}`loading_toplevel`\n", "\n", "{ref}`orm_queryguide_populate_existing` - in {ref}`queryguide_toplevel`\n", ":::\n", "\n", "### Raiseload\n", "\n", "One additional loader strategy worth mentioning is {func}`~sqlalchemy.orm.raiseload`.\n", "This option is used to completely block an application from having the\n", "{term}`N plus one` problem at all by causing what would normally be a lazy\n", "load to raise an error instead. It has two variants that are controlled via\n", "the {paramref}`~sqlalchemy.orm.raiseload.sql_only` option to block either lazy loads\n", "that require SQL, versus all \"load\" operations including those which\n", "only need to consult the current {class}`~sqlalchemy.orm.Session`.\n", "\n", "One way to use {func}`~sqlalchemy.orm.raiseload` is to configure it on\n", "{func}`~sqlalchemy.orm.relationship` itself, by setting {paramref}`~sqlalchemy.orm.relationship.lazy`\n", "to the value `\"raise_on_sql\"`, so that for a particular mapping, a certain\n", "relationship will never try to emit SQL:\n", "\n", "```python\n", "class User(Base):\n", " __tablename__ = 'user_account'\n", "\n", " # Column mappings\n", "\n", " addresses = relationship(\"Address\", back_populates=\"user\", lazy=\"raise_on_sql\")\n", "\n", "\n", "class Address(Base):\n", " __tablename__ = 'address'\n", "\n", " # Column mappings\n", "\n", " user = relationship(\"User\", back_populates=\"addresses\", lazy=\"raise_on_sql\")\n", "```\n", "\n", "Using such a mapping, the application is blocked from lazy loading,\n", "indicating that a particular query would need to specify a loader strategy:\n", "\n", "```python\n", ">>> u1 = s.execute(select(User)).scalars().first()\n", ">>> u1.addresses\n", "sqlalchemy.exc.InvalidRequestError: 'User.addresses' is not available due to lazy='raise_on_sql'\n", "```\n", "\n", "The exception would indicate that this collection should be loaded up front\n", "instead:\n", "\n", "```python\n", ">>> u1 = s.execute(select(User).options(selectinload(User.addresses))).scalars().first()\n", "```\n", "\n", "The `lazy=\"raise_on_sql\"` option tries to be smart about many-to-one\n", "relationships as well; above, if the `Address.user` attribute of an\n", "`Address` object were not loaded, but that `User` object were locally\n", "present in the same {class}`~sqlalchemy.orm.Session`, the \"raiseload\" strategy would not\n", "raise an error.\n", "\n", ":::{seealso}\n", "{ref}`prevent_lazy_with_raiseload` - in {ref}`loading_toplevel`\n", ":::" ] } ], "metadata": { "jupytext": { "text_representation": { "extension": ".md", "format_name": "myst" } }, "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.6" }, "source_map": [ 16, 70, 126, 134, 137, 156, 159, 164, 166, 175, 177, 188, 191, 198, 201, 215, 218, 233, 236, 255, 257, 272, 274, 281, 283, 291, 293, 304, 308, 310, 348, 354, 364, 369, 395, 401, 417, 423, 440, 446, 453, 459, 466, 472, 486, 488, 493, 495, 501, 503, 510, 513, 601, 608, 629, 636, 691, 701, 708, 716, 738, 754 ] }, "nbformat": 4, "nbformat_minor": 5 }