diff options
| author | Federico Caselli <cfederico87@gmail.com> | 2023-01-10 21:39:44 +0100 |
|---|---|---|
| committer | Federico Caselli <cfederico87@gmail.com> | 2023-01-11 20:24:29 +0100 |
| commit | dce11383f83c28f2acc0ed9ee346a56d63e9fcf8 (patch) | |
| tree | 3a0196dd35fe8e87a8fadaa2611cc308d6d65914 /doc/build/tutorial | |
| parent | a950402dae2a5b2448f5f4235946b2f767c7485c (diff) | |
| download | sqlalchemy-dce11383f83c28f2acc0ed9ee346a56d63e9fcf8.tar.gz | |
Improve sql formatting
change {opensql} to {printsql} in prints, add missing markers
Change-Id: I07b72e6620bb64e329d6b641afa27631e91c4f16
Diffstat (limited to 'doc/build/tutorial')
| -rw-r--r-- | doc/build/tutorial/data_insert.rst | 14 | ||||
| -rw-r--r-- | doc/build/tutorial/data_select.rst | 172 | ||||
| -rw-r--r-- | doc/build/tutorial/data_update.rst | 26 | ||||
| -rw-r--r-- | doc/build/tutorial/dbapi_transactions.rst | 24 | ||||
| -rw-r--r-- | doc/build/tutorial/metadata.rst | 4 | ||||
| -rw-r--r-- | doc/build/tutorial/orm_data_manipulation.rst | 18 | ||||
| -rw-r--r-- | doc/build/tutorial/orm_related_objects.rst | 22 |
7 files changed, 140 insertions, 140 deletions
diff --git a/doc/build/tutorial/data_insert.rst b/doc/build/tutorial/data_insert.rst index 5087434d7..4e85f903c 100644 --- a/doc/build/tutorial/data_insert.rst +++ b/doc/build/tutorial/data_insert.rst @@ -47,7 +47,7 @@ SQL expressions can be stringified in place as a means to see the general form of what's being produced:: >>> print(stmt) - {opensql}INSERT INTO user_account (name, fullname) VALUES (:name, :fullname) + {printsql}INSERT INTO user_account (name, fullname) VALUES (:name, :fullname) The stringified form is created by producing a :class:`_engine.Compiled` form of the object which includes a database-specific string SQL representation of @@ -76,7 +76,7 @@ SQL logging: >>> with engine.connect() as conn: ... result = conn.execute(stmt) ... conn.commit() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) INSERT INTO user_account (name, fullname) VALUES (?, ?) [...] ('spongebob', 'Spongebob Squarepants') COMMIT @@ -131,7 +131,7 @@ illustrate this: ... ], ... ) ... conn.commit() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) INSERT INTO user_account (name, fullname) VALUES (?, ?) [...] [('sandy', 'Sandy Cheeks'), ('patrick', 'Patrick Star')] COMMIT{stop} @@ -190,7 +190,7 @@ construct automatically. ... ], ... ) ... conn.commit() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) INSERT INTO address (user_id, email_address) VALUES ((SELECT user_account.id FROM user_account WHERE user_account.name = ?), ?) @@ -212,7 +212,7 @@ method:: ... ["user_id", "email_address"], select_stmt ... ) >>> print(insert_stmt) - {opensql}INSERT INTO address (user_id, email_address) + {printsql}INSERT INTO address (user_id, email_address) SELECT user_account.id, user_account.name || :name_1 AS anon_1 FROM user_account @@ -233,7 +233,7 @@ can be fetched:: ... address_table.c.id, address_table.c.email_address ... ) >>> print(insert_stmt) - {opensql}INSERT INTO address (id, user_id, email_address) + {printsql}INSERT INTO address (id, user_id, email_address) VALUES (:id, :user_id, :email_address) RETURNING address.id, address.email_address @@ -246,7 +246,7 @@ as in the example below that builds upon the example stated in ... ["user_id", "email_address"], select_stmt ... ) >>> print(insert_stmt.returning(address_table.c.id, address_table.c.email_address)) - {opensql}INSERT INTO address (user_id, email_address) + {printsql}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 diff --git a/doc/build/tutorial/data_select.rst b/doc/build/tutorial/data_select.rst index 57a8e7209..98dc7ab03 100644 --- a/doc/build/tutorial/data_select.rst +++ b/doc/build/tutorial/data_select.rst @@ -38,7 +38,7 @@ it can be stringified in place:: >>> from sqlalchemy import select >>> stmt = select(user_table).where(user_table.c.name == "spongebob") >>> print(stmt) - {opensql}SELECT user_account.id, user_account.name, user_account.fullname + {printsql}SELECT user_account.id, user_account.name, user_account.fullname FROM user_account WHERE user_account.name = :name_1 @@ -53,13 +53,13 @@ objects back: >>> with engine.connect() as conn: ... for row in conn.execute(stmt): ... print(row) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account WHERE user_account.name = ? [...] ('spongebob',){stop} (1, 'spongebob', 'Spongebob Squarepants') - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} When using the ORM, particularly with a :func:`_sql.select` construct that's composed against ORM entities, we will want to execute it using the @@ -75,13 +75,13 @@ elements within each row: >>> with Session(engine) as session: ... for row in session.execute(stmt): ... print(row) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account WHERE user_account.name = ? [...] ('spongebob',){stop} (User(id=1, name='spongebob', fullname='Spongebob Squarepants'),) - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} .. topic:: select() from a Table vs. ORM class @@ -108,7 +108,7 @@ 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)) - {opensql}SELECT user_account.id, user_account.name, user_account.fullname + {printsql}SELECT user_account.id, user_account.name, user_account.fullname FROM user_account To SELECT from individual columns using a Core approach, @@ -118,7 +118,7 @@ of all :class:`_schema.Table` and other :class:`_sql.FromClause` objects that are represented by those columns:: >>> print(select(user_table.c.name, user_table.c.fullname)) - {opensql}SELECT user_account.name, user_account.fullname + {printsql}SELECT user_account.name, user_account.fullname FROM user_account Alternatively, when using the :attr:`.FromClause.c` collection of any @@ -126,7 +126,7 @@ Alternatively, when using the :attr:`.FromClause.c` collection of any for a :func:`_sql.select` by using a tuple of string names:: >>> print(select(user_table.c["name", "fullname"])) - {opensql}SELECT user_account.name, user_account.fullname + {printsql}SELECT user_account.name, user_account.fullname FROM user_account .. versionadded:: 2.0 Added tuple-accessor capability to the @@ -145,7 +145,7 @@ 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)) - {opensql}SELECT user_account.id, user_account.name, user_account.fullname + {printsql}SELECT user_account.id, user_account.name, user_account.fullname FROM user_account When executing a statement like the above using the ORM :meth:`_orm.Session.execute` @@ -157,7 +157,7 @@ things to fetch, we get back :class:`_engine.Row` objects that have only one ele instances of the ``User`` class:: >>> row = session.execute(select(User)).first() - {opensql}BEGIN... + {execsql}BEGIN... SELECT user_account.id, user_account.name, user_account.fullname FROM user_account [...] (){stop} @@ -176,7 +176,7 @@ that delivers the first "column" of each row at once, in this case, instances of the ``User`` class:: >>> user = session.scalars(select(User)).first() - {opensql}SELECT user_account.id, user_account.name, user_account.fullname + {execsql}SELECT user_account.id, user_account.name, user_account.fullname FROM user_account [...] (){stop} >>> user @@ -190,7 +190,7 @@ the :class:`_schema.Column` or other SQL expression represented by each attribute:: >>> print(select(User.name, User.fullname)) - {opensql}SELECT user_account.name, user_account.fullname + {printsql}SELECT user_account.name, user_account.fullname FROM user_account When we invoke *this* statement using :meth:`_orm.Session.execute`, we now @@ -198,7 +198,7 @@ receive rows that have individual elements per value, each corresponding to a separate column or other SQL expression:: >>> row = session.execute(select(User.name, User.fullname)).first() - {opensql}SELECT user_account.name, user_account.fullname + {execsql}SELECT user_account.name, user_account.fullname FROM user_account [...] (){stop} >>> row @@ -211,7 +211,7 @@ it with full ``Address`` entities in the second element:: >>> session.execute( ... select(User.name, Address).where(User.id == Address.user_id).order_by(Address.id) ... ).all() - {opensql}SELECT user_account.name, address.id, address.email_address, address.user_id + {execsql}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 [...] (){stop} @@ -243,14 +243,14 @@ when referring to arbitrary SQL expressions in a result row by name: >>> with engine.connect() as conn: ... for row in conn.execute(stmt): ... print(f"{row.username}") - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT ? || user_account.name AS username FROM user_account ORDER BY user_account.name [...] ('Username: ',){stop} Username: patrick Username: sandy Username: spongebob - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} .. seealso:: @@ -281,12 +281,12 @@ SELECT statement:: >>> stmt = select(text("'some phrase'"), user_table.c.name).order_by(user_table.c.name) >>> with engine.connect() as conn: ... print(conn.execute(stmt).all()) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT 'some phrase', user_account.name FROM user_account ORDER BY user_account.name [generated in ...] () {stop}[('some phrase', 'patrick'), ('some phrase', 'sandy'), ('some phrase', 'spongebob')] - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} While the :func:`_sql.text` construct can be used in most places to inject literal SQL phrases, more often than not we are actually dealing with textual @@ -306,14 +306,14 @@ towards in subqueries and other expressions:: >>> with engine.connect() as conn: ... for row in conn.execute(stmt): ... print(f"{row.p}, {row.name}") - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT 'some phrase' AS p, user_account.name FROM user_account ORDER BY user_account.name [generated in ...] () {stop}some phrase, patrick some phrase, sandy some phrase, spongebob - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} Note that in both cases, when using :func:`_sql.text` or @@ -344,7 +344,7 @@ We can use expressions like these to generate the WHERE clause by passing the resulting objects to the :meth:`_sql.Select.where` method:: >>> print(select(user_table).where(user_table.c.name == "squidward")) - {opensql}SELECT user_account.id, user_account.name, user_account.fullname + {printsql}SELECT user_account.id, user_account.name, user_account.fullname FROM user_account WHERE user_account.name = :name_1 @@ -357,7 +357,7 @@ method may be invoked any number of times:: ... .where(user_table.c.name == "squidward") ... .where(address_table.c.user_id == user_table.c.id) ... ) - {opensql}SELECT address.email_address + {printsql}SELECT address.email_address FROM address, user_account WHERE user_account.name = :name_1 AND address.user_id = user_account.id @@ -370,7 +370,7 @@ with the same effect:: ... address_table.c.user_id == user_table.c.id, ... ) ... ) - {opensql}SELECT address.email_address + {printsql}SELECT address.email_address FROM address, user_account WHERE user_account.name = :name_1 AND address.user_id = user_account.id @@ -387,7 +387,7 @@ of ORM entities:: ... ) ... ) ... ) - {opensql}SELECT address.email_address + {printsql}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 @@ -398,7 +398,7 @@ 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")) - {opensql}SELECT user_account.id, user_account.name, user_account.fullname + {printsql}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 @@ -423,14 +423,14 @@ in the COLUMNS clause, it puts that :class:`_schema.Table` in the FROM clause as well:: >>> print(select(user_table.c.name)) - {opensql}SELECT user_account.name + {printsql}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)) - {opensql}SELECT user_account.name, address.email_address + {printsql}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 @@ -443,7 +443,7 @@ explicitly:: ... user_table, address_table ... ) ... ) - {opensql}SELECT user_account.name, address.email_address + {printsql}SELECT user_account.name, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id @@ -451,7 +451,7 @@ The other is the the :meth:`_sql.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)) - {opensql}SELECT user_account.name, address.email_address + {printsql}SELECT user_account.name, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id .. sidebar:: The ON Clause is inferred @@ -468,7 +468,7 @@ clause and :meth:`_sql.Select.join` to establish ``address_table`` as the second:: >>> print(select(address_table.c.email_address).select_from(user_table).join(address_table)) - {opensql}SELECT address.email_address + {printsql}SELECT address.email_address FROM user_account JOIN address ON user_account.id = address.user_id Another example where we might want to use :meth:`_sql.Select.select_from` @@ -479,7 +479,7 @@ produce the SQL ``count()`` function:: >>> from sqlalchemy import func >>> print(select(func.count("*")).select_from(user_table)) - {opensql}SELECT count(:count_2) AS count_1 + {printsql}SELECT count(:count_2) AS count_1 FROM user_account .. seealso:: @@ -511,7 +511,7 @@ same SQL Expression mechanics as we saw about in :ref:`tutorial_select_where_cla ... .select_from(user_table) ... .join(address_table, user_table.c.id == address_table.c.user_id) ... ) - {opensql}SELECT address.email_address + {printsql}SELECT address.email_address FROM user_account JOIN address ON user_account.id = address.user_id .. container:: orm-header @@ -532,11 +532,11 @@ accept keyword arguments :paramref:`_sql.Select.join.isouter` and and FULL OUTER JOIN, respectively:: >>> print(select(user_table).join(address_table, isouter=True)) - {opensql}SELECT user_account.id, user_account.name, user_account.fullname + {printsql}SELECT user_account.id, user_account.name, user_account.fullname FROM user_account LEFT OUTER JOIN address ON user_account.id = address.user_id{stop} >>> print(select(user_table).join(address_table, full=True)) - {opensql}SELECT user_account.id, user_account.name, user_account.fullname + {printsql}SELECT user_account.id, user_account.name, user_account.fullname FROM user_account FULL OUTER JOIN address ON user_account.id = address.user_id{stop} There is also a method :meth:`_sql.Select.outerjoin` that is equivalent to @@ -572,7 +572,7 @@ similar objects. The :meth:`_sql.Select.order_by` method accepts one or more of these expressions positionally:: >>> print(select(user_table).order_by(user_table.c.name)) - {opensql}SELECT user_account.id, user_account.name, user_account.fullname + {printsql}SELECT user_account.id, user_account.name, user_account.fullname FROM user_account ORDER BY user_account.name Ascending / descending is available from the :meth:`_sql.ColumnElement.asc` @@ -581,7 +581,7 @@ from ORM-bound attributes as well:: >>> print(select(User).order_by(User.fullname.desc())) - {opensql}SELECT user_account.id, user_account.name, user_account.fullname + {printsql}SELECT user_account.id, user_account.name, user_account.fullname FROM user_account ORDER BY user_account.fullname DESC The above statement will yield rows that are sorted by the @@ -609,7 +609,7 @@ we call upon the ``count()`` name:: >>> from sqlalchemy import func >>> count_fn = func.count(user_table.c.id) >>> print(count_fn) - {opensql}count(user_account.id) + {printsql}count(user_account.id) SQL functions are described in more detail later in this tutorial at :ref:`tutorial_functions`. @@ -638,13 +638,13 @@ than one address: ... .having(func.count(Address.id) > 1) ... ) ... print(result.all()) - {opensql}BEGIN (implicit) + {execsql}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,){stop} [('sandy', 2)] - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} .. _tutorial_order_by_label: @@ -671,7 +671,7 @@ error if no match is found. The unary modifiers ... .order_by("user_id", desc("num_addresses")) ... ) >>> print(stmt) - {opensql}SELECT address.user_id, count(address.id) AS num_addresses + {printsql}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 .. _tutorial_using_aliases: @@ -700,7 +700,7 @@ below for example returns all unique pairs of user names:: ... user_alias_1, user_alias_2, user_alias_1.c.id > user_alias_2.c.id ... ) ... ) - {opensql}SELECT user_account_1.name, user_account_2.name AS name_1 + {printsql}SELECT user_account_1.name, user_account_2.name AS name_1 FROM user_account AS user_account_1 JOIN user_account AS user_account_2 ON user_account_1.id > user_account_2.id @@ -726,7 +726,7 @@ while maintaining ORM functionality. The SELECT below selects from the ... .join_from(User, address_alias_2) ... .where(address_alias_2.email_address == "patrick@gmail.com") ... ) - {opensql}SELECT user_account.id, user_account.name, user_account.fullname + {printsql}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 @@ -776,7 +776,7 @@ Stringifying the subquery by itself without it being embedded inside of another without any enclosing parenthesis:: >>> print(subq) - {opensql}SELECT count(address.id) AS count, address.user_id + {printsql}SELECT count(address.id) AS count, address.user_id FROM address GROUP BY address.user_id @@ -787,7 +787,7 @@ refer to both the ``user_id`` column as well as our custom labeled ``count`` expression:: >>> print(select(subq.c.user_id, subq.c.count)) - {opensql}SELECT anon_1.user_id, anon_1.count + {printsql}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 @@ -800,7 +800,7 @@ the ``user_account`` table:: ... ) >>> print(stmt) - {opensql}SELECT user_account.name, user_account.fullname, anon_1.count + {printsql}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 @@ -834,7 +834,7 @@ table expression syntax:: ... ) >>> print(stmt) - {opensql}WITH anon_1 AS + {printsql}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 @@ -892,7 +892,7 @@ each ``Address`` object ultimately came from a subquery against the >>> with Session(engine) as session: ... for user, address in session.execute(stmt): ... print(f"{user} {address}") - {opensql}BEGIN (implicit) + {execsql}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 @@ -904,7 +904,7 @@ each ``Address`` object ultimately came from a subquery against the 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') - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} Another example follows, which is exactly the same except it makes use of the :class:`_sql.CTE` construct instead: @@ -921,7 +921,7 @@ Another example follows, which is exactly the same except it makes use of the >>> with Session(engine) as session: ... for user, address in session.execute(stmt): ... print(f"{user} {address}") - {opensql}BEGIN (implicit) + {execsql}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 @@ -935,7 +935,7 @@ Another example follows, which is exactly the same except it makes use of the 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') - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} .. seealso:: @@ -970,7 +970,7 @@ renders as an ordinary SELECT statement that is selecting from two tables:: ... .scalar_subquery() ... ) >>> print(subq) - {opensql}(SELECT count(address.id) AS count_1 + {printsql}(SELECT count(address.id) AS count_1 FROM address, user_account WHERE user_account.id = address.user_id) @@ -979,7 +979,7 @@ SQL expression hierarchy, in that it may be used like any other column expression:: >>> print(subq == 5) - {opensql}(SELECT count(address.id) AS count_1 + {printsql}(SELECT count(address.id) AS count_1 FROM address, user_account WHERE user_account.id = address.user_id) = :param_1 @@ -992,7 +992,7 @@ into an enclosing :func:`_sql.select` construct that deals with the >>> stmt = select(user_table.c.name, subq.label("address_count")) >>> print(stmt) - {opensql}SELECT user_account.name, (SELECT count(address.id) AS count_1 + {printsql}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 @@ -1043,7 +1043,7 @@ The statement then can return the data for this column like any other: ... .order_by(user_table.c.id, address_table.c.id) ... ) ... print(result.all()) - {opensql}BEGIN (implicit) + {execsql}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 @@ -1051,7 +1051,7 @@ The statement then can return the data for this column like any other: [...] (){stop} [('spongebob', 'spongebob@sqlalchemy.org', 1), ('sandy', 'sandy@sqlalchemy.org', 2), ('sandy', 'sandy@squirrelpower.org', 2)] - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} .. _tutorial_lateral_correlation: @@ -1095,7 +1095,7 @@ was discussed in the previous section:: ... .order_by(user_table.c.id, subq.c.email_address) ... ) >>> print(stmt) - {opensql}SELECT user_account.name, anon_1.address_count, anon_1.email_address + {printsql}SELECT user_account.name, anon_1.address_count, anon_1.email_address FROM user_account JOIN LATERAL (SELECT count(address.id) AS address_count, address.email_address AS email_address, address.user_id AS user_id @@ -1150,7 +1150,7 @@ that it has fewer methods. The :class:`_sql.CompoundSelect` produced by >>> with engine.connect() as conn: ... result = conn.execute(u) ... print(result.all()) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account WHERE user_account.name = ? @@ -1159,7 +1159,7 @@ that it has fewer methods. The :class:`_sql.CompoundSelect` produced by WHERE user_account.name = ? [generated in ...] ('sandy', 'spongebob') {stop}[(2, 'sandy', 'Sandy Cheeks'), (1, 'spongebob', 'Spongebob Squarepants')] - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} To use a :class:`_sql.CompoundSelect` as a subquery, just like :class:`_sql.Select` it provides a :meth:`_sql.SelectBase.subquery` method which will produce a @@ -1175,7 +1175,7 @@ collection that may be referred towards in an enclosing :func:`_sql.select`:: >>> with engine.connect() as conn: ... result = conn.execute(stmt) ... print(result.all()) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT anon_1.name, address.email_address FROM address JOIN (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname @@ -1189,7 +1189,7 @@ collection that may be referred towards in an enclosing :func:`_sql.select`:: ORDER BY anon_1.name, address.email_address [generated in ...] ('sandy', 'spongebob') {stop}[('sandy', 'sandy@sqlalchemy.org'), ('sandy', 'sandy@squirrelpower.org'), ('spongebob', 'spongebob@sqlalchemy.org')] - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} .. _tutorial_orm_union: @@ -1220,7 +1220,7 @@ criteria can be added after :meth:`_sql.Select.from_statement` is used:: >>> with Session(engine) as session: ... for obj in session.execute(orm_stmt).scalars(): ... print(obj) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account WHERE user_account.name = ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname @@ -1229,7 +1229,7 @@ criteria can be added after :meth:`_sql.Select.from_statement` is used:: [generated in ...] ('sandy', 'spongebob') {stop}User(id=2, name='sandy', fullname='Sandy Cheeks') User(id=1, name='spongebob', fullname='Spongebob Squarepants') - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} To use a UNION or other set-related construct as an entity-related component in in a more flexible manner, the :class:`_sql.CompoundSelect` construct may be @@ -1247,7 +1247,7 @@ by the subquery:: >>> with Session(engine) as session: ... for obj in session.execute(orm_stmt).scalars(): ... print(obj) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT anon_1.id, anon_1.name, anon_1.fullname FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname FROM user_account @@ -1257,7 +1257,7 @@ by the subquery:: [generated in ...] ('sandy', 'spongebob') {stop}User(id=1, name='spongebob', fullname='Spongebob Squarepants') User(id=2, name='sandy', fullname='Sandy Cheeks') - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} .. seealso:: @@ -1288,7 +1288,7 @@ can return ``user_account`` rows that have more than one related row in >>> with engine.connect() as conn: ... result = conn.execute(select(user_table.c.name).where(subq)) ... print(result.all()) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT user_account.name FROM user_account WHERE EXISTS (SELECT count(address.id) AS count_1 @@ -1297,7 +1297,7 @@ can return ``user_account`` rows that have more than one related row in HAVING count(address.id) > ?) [...] (1,){stop} [('sandy',)] - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} 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 @@ -1313,7 +1313,7 @@ clause: >>> with engine.connect() as conn: ... result = conn.execute(select(user_table.c.name).where(~subq)) ... print(result.all()) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT user_account.name FROM user_account WHERE NOT (EXISTS (SELECT address.id @@ -1321,7 +1321,7 @@ clause: WHERE user_account.id = address.user_id)) [...] (){stop} [('patrick',)] - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} .. _tutorial_functions: @@ -1342,7 +1342,7 @@ possibly some arguments. Examples of typical SQL functions include: .. sourcecode:: pycon+sql >>> print(select(func.count()).select_from(user_table)) - SELECT count(*) AS count_1 + {printsql}SELECT count(*) AS count_1 FROM user_account .. @@ -1353,7 +1353,7 @@ possibly some arguments. Examples of typical SQL functions include: .. sourcecode:: pycon+sql >>> print(select(func.lower("A String With Much UPPERCASE"))) - SELECT lower(:lower_2) AS lower_1 + {printsql}SELECT lower(:lower_2) AS lower_1 .. @@ -1367,7 +1367,7 @@ possibly some arguments. Examples of typical SQL functions include: >>> with engine.connect() as conn: ... result = conn.execute(stmt) ... print(result.all()) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT CURRENT_TIMESTAMP AS now_1 [...] () [(datetime.datetime(...),)] @@ -1381,7 +1381,7 @@ accepts. Any name that is accessed from this namespace is automatically considered to be a SQL function that will render in a generic way:: >>> print(select(func.some_crazy_function(user_table.c.name, 17))) - SELECT some_crazy_function(user_account.name, :some_crazy_function_2) AS some_crazy_function_1 + {printsql}SELECT some_crazy_function(user_account.name, :some_crazy_function_2) AS some_crazy_function_1 FROM user_account At the same time, a relatively small set of extremely common SQL functions such @@ -1394,11 +1394,11 @@ the :class:`_functions.now` function:: >>> from sqlalchemy.dialects import postgresql >>> print(select(func.now()).compile(dialect=postgresql.dialect())) - SELECT now() AS now_1 + {printsql}SELECT now() AS now_1 >>> from sqlalchemy.dialects import oracle >>> print(select(func.now()).compile(dialect=oracle.dialect())) - SELECT CURRENT_TIMESTAMP AS now_1 FROM DUAL + {printsql}SELECT CURRENT_TIMESTAMP AS now_1 FROM DUAL Functions Have Return Types ~~~~~~~~~~~~~~~~~~~~~~~~~~~ @@ -1502,7 +1502,7 @@ operator for example will be correctly interpreted as the string concatenation operator based on looking at both sides of the expression:: >>> print(select(func.upper("lowercase") + " suffix")) - SELECT upper(:upper_1) || :upper_2 AS anon_1 + {printsql}SELECT upper(:upper_1) || :upper_2 AS anon_1 Overall, the scenario where the :paramref:`_functions.Function.type_` parameter is likely necessary is: @@ -1579,7 +1579,7 @@ number the email addresses of individual users: >>> with engine.connect() as conn: # doctest:+SKIP ... result = conn.execute(stmt) ... print(result.all()) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT row_number() OVER (PARTITION BY user_account.name) AS anon_1, user_account.name, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id @@ -1605,7 +1605,7 @@ We also may make use of the ``ORDER BY`` clause using :paramref:`_functions.Func >>> with engine.connect() as conn: # doctest:+SKIP ... result = conn.execute(stmt) ... print(result.all()) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT count(*) OVER (ORDER BY user_account.name) AS anon_1, user_account.name, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id @@ -1662,13 +1662,13 @@ using the :meth:`_functions.FunctionElement.filter` method:: >>> with engine.connect() as conn: # doctest:+SKIP ... result = conn.execute(stmt) ... print(result.all()) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_1, count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_2 FROM user_account JOIN address ON user_account.id = address.user_id [...] ('sandy', 'spongebob') {stop}[(2, 1)] - {opensql}ROLLBACK + {execsql}ROLLBACK .. _tutorial_functions_table_valued: @@ -1707,13 +1707,13 @@ modern versions of SQLite:: >>> with engine.connect() as conn: ... result = conn.execute(stmt) ... result.all() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT anon_1.value FROM json_each(?) AS anon_1 WHERE anon_1.value IN (?, ?) [...] ('["one", "two", "three"]', 'two', 'three') {stop}[('two',), ('three',)] - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} Above, we used the ``json_each()`` JSON function supported by SQLite and PostgreSQL to generate a table valued expression with a single column referred @@ -1744,7 +1744,7 @@ to a :class:`_functions.Function` construct:: >>> from sqlalchemy import select, func >>> stmt = select(func.json_array_elements('["one", "two"]').column_valued("x")) >>> print(stmt) - SELECT x + {printsql}SELECT x FROM json_array_elements(:json_array_elements_1) AS x The "column valued" form is also supported by the Oracle dialect, where @@ -1753,7 +1753,7 @@ it is usable for custom SQL functions:: >>> from sqlalchemy.dialects import oracle >>> stmt = select(func.scalar_strings(5).column_valued("s")) >>> print(stmt.compile(dialect=oracle.dialect())) - SELECT s.COLUMN_VALUE + {printsql}SELECT s.COLUMN_VALUE FROM TABLE (scalar_strings(:scalar_strings_1)) s @@ -1781,12 +1781,12 @@ object:: >>> with engine.connect() as conn: ... result = conn.execute(stmt) ... result.all() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT CAST(user_account.id AS VARCHAR) AS id FROM user_account [...] () {stop}[('1',), ('2',), ('3',)] - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} The :func:`.cast` function not only renders the SQL CAST syntax, it also produces a SQLAlchemy column expression that will act as the given datatype on @@ -1823,7 +1823,7 @@ string into one of MySQL's JSON functions: >>> from sqlalchemy.dialects import mysql >>> s = select(type_coerce({"some_key": {"foo": "bar"}}, JSON)["some_key"]) >>> print(s.compile(dialect=mysql.dialect())) - SELECT JSON_EXTRACT(%s, %s) AS anon_1 + {printsql}SELECT JSON_EXTRACT(%s, %s) AS anon_1 Above, MySQL's ``JSON_EXTRACT`` SQL function was invoked because we used :func:`.type_coerce` to indicate that our Python dictionary diff --git a/doc/build/tutorial/data_update.rst b/doc/build/tutorial/data_update.rst index 38624d5f7..a82f070a3 100644 --- a/doc/build/tutorial/data_update.rst +++ b/doc/build/tutorial/data_update.rst @@ -61,7 +61,7 @@ A basic UPDATE looks like:: ... .values(fullname="Patrick the Star") ... ) >>> print(stmt) - {opensql}UPDATE user_account SET fullname=:fullname WHERE user_account.name = :name_1 + {printsql}UPDATE user_account SET fullname=:fullname WHERE user_account.name = :name_1 The :meth:`_sql.Update.values` method controls the contents of the SET elements of the UPDATE statement. This is the same method shared by the :class:`_sql.Insert` @@ -73,7 +73,7 @@ where we can make use of :class:`_schema.Column` expressions:: >>> stmt = update(user_table).values(fullname="Username: " + user_table.c.name) >>> print(stmt) - {opensql}UPDATE user_account SET fullname=(:name_1 || user_account.name) + {printsql}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 :func:`_sql.bindparam` @@ -97,7 +97,7 @@ that literal values would normally go: ... {"oldname": "jim", "newname": "jake"}, ... ], ... ) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) UPDATE user_account SET name=? WHERE user_account.name = ? [...] [('ed', 'jack'), ('mary', 'wendy'), ('jake', 'jim')] <sqlalchemy.engine.cursor.CursorResult object at 0x...> @@ -124,7 +124,7 @@ anywhere a column expression might be placed:: ... ) >>> update_stmt = update(user_table).values(fullname=scalar_subq) >>> print(update_stmt) - {opensql}UPDATE user_account SET fullname=(SELECT address.email_address + {printsql}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) @@ -147,7 +147,7 @@ WHERE clause of the statement:: ... .values(fullname="Pat") ... ) >>> print(update_stmt) - {opensql}UPDATE user_account SET fullname=:fullname FROM address + {printsql}UPDATE user_account SET fullname=:fullname FROM address WHERE user_account.id = address.user_id AND address.email_address = :email_address_1 @@ -168,7 +168,7 @@ order to refer to additional tables:: ... ) >>> from sqlalchemy.dialects import mysql >>> print(update_stmt.compile(dialect=mysql.dialect())) - {opensql}UPDATE user_account, address + {printsql}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 @@ -186,7 +186,7 @@ tuples so that this order may be controlled [2]_:: ... (some_table.c.y, 20), (some_table.c.x, some_table.c.y + 10) ... ) >>> print(update_stmt) - {opensql}UPDATE some_table SET y=:y, x=(some_table.y + :y_1) + {printsql}UPDATE some_table SET y=:y, x=(some_table.y + :y_1) .. [2] While Python dictionaries are @@ -215,7 +215,7 @@ 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) - {opensql}DELETE FROM user_account WHERE user_account.name = :name_1 + {printsql}DELETE FROM user_account WHERE user_account.name = :name_1 .. _tutorial_multi_table_deletes: @@ -234,7 +234,7 @@ syntaxes, such as ``DELETE FROM..USING`` on MySQL:: ... ) >>> from sqlalchemy.dialects import mysql >>> print(delete_stmt.compile(dialect=mysql.dialect())) - {opensql}DELETE FROM user_account USING user_account, address + {printsql}DELETE FROM user_account USING user_account, address WHERE user_account.id = address.user_id AND address.email_address = %s .. _tutorial_update_delete_rowcount: @@ -257,11 +257,11 @@ is available from the :attr:`_engine.CursorResult.rowcount` attribute: ... .where(user_table.c.name == "patrick") ... ) ... print(result.rowcount) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) UPDATE user_account SET fullname=? WHERE user_account.name = ? [...] ('Patrick McStar', 'patrick'){stop} 1 - {opensql}COMMIT{stop} + {execsql}COMMIT{stop} .. tip:: @@ -315,7 +315,7 @@ be iterated:: ... .returning(user_table.c.id, user_table.c.name) ... ) >>> print(update_stmt) - {opensql}UPDATE user_account SET fullname=:fullname + {printsql}UPDATE user_account SET fullname=:fullname WHERE user_account.name = :name_1 RETURNING user_account.id, user_account.name{stop} @@ -325,7 +325,7 @@ be iterated:: ... .returning(user_table.c.id, user_table.c.name) ... ) >>> print(delete_stmt) - {opensql}DELETE FROM user_account + {printsql}DELETE FROM user_account WHERE user_account.name = :name_1 RETURNING user_account.id, user_account.name{stop} diff --git a/doc/build/tutorial/dbapi_transactions.rst b/doc/build/tutorial/dbapi_transactions.rst index d40aaf5b8..d0cd09aa0 100644 --- a/doc/build/tutorial/dbapi_transactions.rst +++ b/doc/build/tutorial/dbapi_transactions.rst @@ -65,11 +65,11 @@ in more detail later: >>> with engine.connect() as conn: ... result = conn.execute(text("select 'hello world'")) ... print(result.all()) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) select 'hello world' [...] () {stop}[('hello world',)] - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} In the above example, the context manager provided for a database connection and also framed the operation inside of a transaction. The default behavior of @@ -110,7 +110,7 @@ where we acquired the :class:`_engine.Connection` object: ... [{"x": 1, "y": 1}, {"x": 2, "y": 4}], ... ) ... conn.commit() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) CREATE TABLE some_table (x int, y int) [...] () <sqlalchemy.engine.cursor.CursorResult object at 0x...> @@ -147,7 +147,7 @@ may be referred towards as **begin once**: ... text("INSERT INTO some_table (x, y) VALUES (:x, :y)"), ... [{"x": 6, "y": 8}, {"x": 9, "y": 10}], ... ) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) INSERT INTO some_table (x, y) VALUES (?, ?) [...] [(6, 8), (9, 10)] <sqlalchemy.engine.cursor.CursorResult object at 0x...> @@ -216,14 +216,14 @@ statement on the table we've created: ... result = conn.execute(text("SELECT x, y FROM some_table")) ... for row in result: ... print(f"x: {row.x} y: {row.y}") - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT x, y FROM some_table [...] () {stop}x: 1 y: 1 x: 2 y: 4 x: 6 y: 8 x: 9 y: 10 - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} Above, the "SELECT" string we executed selected all rows from our table. The object returned is called :class:`_engine.Result` and represents an @@ -319,13 +319,13 @@ construct accepts these using a colon format "``:y``". The actual value for ... result = conn.execute(text("SELECT x, y FROM some_table WHERE y > :y"), {"y": 2}) ... for row in result: ... print(f"x: {row.x} y: {row.y}") - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT x, y FROM some_table WHERE y > ? [...] (2,) {stop}x: 2 y: 4 x: 6 y: 8 x: 9 y: 10 - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} In the logged SQL output, we can see that the bound parameter ``:y`` was @@ -370,7 +370,7 @@ of execution is known as :term:`executemany`: ... [{"x": 11, "y": 12}, {"x": 13, "y": 14}], ... ) ... conn.commit() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) INSERT INTO some_table (x, y) VALUES (?, ?) [...] [(11, 12), (13, 14)] <sqlalchemy.engine.cursor.CursorResult object at 0x...> @@ -439,14 +439,14 @@ a context manager: ... result = session.execute(stmt, {"y": 6}) ... for row in result: ... print(f"x: {row.x} y: {row.y}") - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT x, y FROM some_table WHERE y > ? ORDER BY x, y [...] (6,){stop} x: 6 y: 8 x: 9 y: 10 x: 11 y: 12 x: 13 y: 14 - {opensql}ROLLBACK{stop} + {execsql}ROLLBACK{stop} The example above can be compared to the example in the preceding section in :ref:`tutorial_sending_parameters` - we directly replace the call to @@ -467,7 +467,7 @@ our data: ... [{"x": 9, "y": 11}, {"x": 13, "y": 15}], ... ) ... session.commit() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) UPDATE some_table SET y=? WHERE x=? [...] [(11, 9), (15, 13)] COMMIT{stop} diff --git a/doc/build/tutorial/metadata.rst b/doc/build/tutorial/metadata.rst index 7c05497ac..65047fd9c 100644 --- a/doc/build/tutorial/metadata.rst +++ b/doc/build/tutorial/metadata.rst @@ -213,7 +213,7 @@ sending it the :class:`_engine.Engine` that refers to the target database: .. sourcecode:: pycon+sql >>> metadata_obj.create_all(engine) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) PRAGMA main.table_...info("user_account") ... PRAGMA main.table_...info("address") @@ -598,7 +598,7 @@ using the :paramref:`_schema.Table.autoload_with` parameter: .. sourcecode:: pycon+sql >>> some_table = Table("some_table", metadata_obj, autoload_with=engine) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) PRAGMA main.table_...info("some_table") [raw sql] () SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view') diff --git a/doc/build/tutorial/orm_data_manipulation.rst b/doc/build/tutorial/orm_data_manipulation.rst index 3c4e55b3e..ab16c5856 100644 --- a/doc/build/tutorial/orm_data_manipulation.rst +++ b/doc/build/tutorial/orm_data_manipulation.rst @@ -123,7 +123,7 @@ method: .. sourcecode:: pycon+sql >>> session.flush() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) INSERT INTO user_account (name, fullname) VALUES (?, ?), (?, ?) RETURNING id [...] ('squidward', 'Squidward Tentacles', 'ehkrabs', 'Eugene H. Krabs') @@ -255,7 +255,7 @@ as well as the :meth:`_engine.Result.scalar_one` method): .. sourcecode:: pycon+sql >>> sandy = session.execute(select(User).filter_by(name="sandy")).scalar_one() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account WHERE user_account.name = ? @@ -288,7 +288,7 @@ from this row and we will get our updated value back: .. sourcecode:: pycon+sql >>> sandy_fullname = session.execute(select(User.fullname).where(User.id == 2)).scalar_one() - {opensql}UPDATE user_account SET fullname=? WHERE user_account.id = ? + {execsql}UPDATE user_account SET fullname=? WHERE user_account.id = ? [...] ('Sandy Squirrel', 2) SELECT user_account.fullname FROM user_account @@ -333,7 +333,7 @@ Let's load up ``patrick`` from the database: .. sourcecode:: pycon+sql >>> patrick = session.get(User, 3) - {opensql}SELECT user_account.id AS user_account_id, user_account.name AS user_account_name, + {execsql}SELECT user_account.id AS user_account_id, user_account.name AS user_account_name, user_account.fullname AS user_account_fullname FROM user_account WHERE user_account.id = ? @@ -350,7 +350,7 @@ until the flush proceeds, which as mentioned before occurs if we emit a query: .. sourcecode:: pycon+sql >>> session.execute(select(User).where(User.name == "patrick")).first() - {opensql}SELECT address.id AS address_id, address.email_address AS address_email_address, + {execsql}SELECT address.id AS address_id, address.email_address AS address_email_address, address.user_id AS address_user_id FROM address WHERE ? = address.user_id @@ -460,7 +460,7 @@ a new transaction and refresh ``sandy`` with the current database row: .. sourcecode:: pycon+sql >>> sandy.fullname - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT user_account.id AS user_account_id, user_account.name AS user_account_name, user_account.fullname AS user_account_fullname FROM user_account @@ -487,7 +487,7 @@ and of course the database data is present again as well: .. sourcecode:: pycon+sql >>> session.execute(select(User).where(User.name == "patrick")).scalar_one() is patrick - {opensql}SELECT user_account.id, user_account.name, user_account.fullname + {execsql}SELECT user_account.id, user_account.name, user_account.fullname FROM user_account WHERE user_account.name = ? [...] ('patrick',){stop} @@ -506,7 +506,7 @@ close out the :class:`_orm.Session` when we are done with it: .. sourcecode:: pycon+sql >>> session.close() - {opensql}ROLLBACK + {execsql}ROLLBACK Closing the :class:`_orm.Session`, which is what happens when we use it in a context manager as well, accomplishes the following things: @@ -542,7 +542,7 @@ a context manager as well, accomplishes the following things: >>> session.add(squidward) >>> squidward.name - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT user_account.id AS user_account_id, user_account.name AS user_account_name, user_account.fullname AS user_account_fullname FROM user_account WHERE user_account.id = ? diff --git a/doc/build/tutorial/orm_related_objects.rst b/doc/build/tutorial/orm_related_objects.rst index a23c3369b..bd1fae131 100644 --- a/doc/build/tutorial/orm_related_objects.rst +++ b/doc/build/tutorial/orm_related_objects.rst @@ -196,7 +196,7 @@ newly generated primary key of the ``user_account`` row is applied to the .. sourcecode:: pycon+sql >>> session.commit() - {opensql}INSERT INTO user_account (name, fullname) VALUES (?, ?) + {execsql}INSERT INTO user_account (name, fullname) VALUES (?, ?) [...] ('pkrabs', 'Pearl Krabs') INSERT INTO address (email_address, user_id) VALUES (?, ?), (?, ?) RETURNING id [...] ('pearl.krabs@gmail.com', 6, 'pearl@aol.com', 6) @@ -220,7 +220,7 @@ newly generated primary key for the ``u1`` object: .. sourcecode:: pycon+sql >>> u1.id - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT user_account.id AS user_account_id, user_account.name AS user_account_name, user_account.fullname AS user_account_fullname FROM user_account @@ -236,7 +236,7 @@ we again see a :term:`lazy load` emitted in order to retrieve the objects: .. sourcecode:: pycon+sql >>> u1.addresses - {opensql}SELECT address.id AS address_id, address.email_address AS address_email_address, + {execsql}SELECT address.id AS address_id, address.email_address AS address_email_address, address.user_id AS address_user_id FROM address WHERE ? = address.user_id @@ -305,7 +305,7 @@ argument** to :meth:`_sql.Select.join`, where it serves to indicate both the right side of the join as well as the ON clause at once:: >>> print(select(Address.email_address).select_from(User).join(User.addresses)) - {opensql}SELECT address.email_address + {printsql}SELECT address.email_address FROM user_account JOIN address ON user_account.id = address.user_id The presence of an ORM :func:`_orm.relationship` on a mapping is not used @@ -317,7 +317,7 @@ between the two mapped :class:`_schema.Table` objects, not because of the :func:`_orm.relationship` objects on the ``User`` and ``Address`` classes:: >>> print(select(Address.email_address).join_from(User, Address)) - {opensql}SELECT address.email_address + {printsql}SELECT address.email_address FROM user_account JOIN address ON user_account.id = address.user_id See the section :ref:`orm_queryguide_joins` in the :ref:`queryguide_toplevel` @@ -447,7 +447,7 @@ related ``Address`` objects: ... print( ... f"{row.User.name} ({', '.join(a.email_address for a in row.User.addresses)})" ... ) - {opensql}SELECT user_account.id, user_account.name, user_account.fullname + {execsql}SELECT user_account.id, user_account.name, user_account.fullname FROM user_account ORDER BY user_account.id [...] () SELECT address.user_id AS address_user_id, address.id AS address_id, @@ -492,7 +492,7 @@ as below where we know that all ``Address`` objects have an associated ... ) >>> for row in session.execute(stmt): ... print(f"{row.Address.email_address} {row.Address.user.name}") - {opensql}SELECT address.id, address.email_address, address.user_id, user_account_1.id AS id_1, + {execsql}SELECT address.id, address.email_address, address.user_id, user_account_1.id AS id_1, user_account_1.name, user_account_1.fullname FROM address JOIN user_account AS user_account_1 ON user_account_1.id = address.user_id @@ -563,7 +563,7 @@ example: ... ) >>> for row in session.execute(stmt): ... print(f"{row.Address.email_address} {row.Address.user.name}") - {opensql}SELECT user_account.id, user_account.name, user_account.fullname, + {execsql}SELECT user_account.id, user_account.name, user_account.fullname, address.id AS id_1, address.email_address, address.user_id FROM address JOIN user_account ON user_account.id = address.user_id WHERE user_account.name = ? ORDER BY address.id @@ -584,7 +584,7 @@ SQL query that unnecessarily joins twice:: ... .order_by(Address.id) ... ) >>> print(stmt) # SELECT has a JOIN and LEFT OUTER JOIN unnecessarily - {opensql}SELECT address.id, address.email_address, address.user_id, + {printsql}SELECT address.id, address.email_address, address.user_id, user_account_1.id AS id_1, user_account_1.name, user_account_1.fullname 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 @@ -644,7 +644,7 @@ Using such a mapping, the application is blocked from lazy loading, indicating that a particular query would need to specify a loader strategy:: >>> u1 = session.execute(select(User)).scalars().first() - {opensql}SELECT user_account.id FROM user_account + {execsql}SELECT user_account.id FROM user_account [...] () {stop}>>> u1.addresses Traceback (most recent call last): @@ -660,7 +660,7 @@ instead:: ... .scalars() ... .first() ... ) - {opensql}SELECT user_account.id + {execsql}SELECT user_account.id FROM user_account [...] () SELECT address.user_id AS address_user_id, address.id AS address_id |
