From dce11383f83c28f2acc0ed9ee346a56d63e9fcf8 Mon Sep 17 00:00:00 2001 From: Federico Caselli Date: Tue, 10 Jan 2023 21:39:44 +0100 Subject: [PATCH] Improve sql formatting change {opensql} to {printsql} in prints, add missing markers Change-Id: I07b72e6620bb64e329d6b641afa27631e91c4f16 --- doc/build/changelog/migration_08.rst | 4 +- doc/build/changelog/migration_09.rst | 18 +- doc/build/changelog/migration_11.rst | 13 +- doc/build/changelog/migration_12.rst | 8 +- doc/build/changelog/migration_13.rst | 12 +- doc/build/changelog/migration_14.rst | 24 +-- doc/build/changelog/whatsnew_20.rst | 4 +- doc/build/core/connections.rst | 4 +- doc/build/core/constraints.rst | 12 +- doc/build/core/custom_types.rst | 4 +- doc/build/core/ddl.rst | 10 +- doc/build/core/defaults.rst | 2 +- doc/build/core/metadata.rst | 6 +- doc/build/core/operators.rst | 12 +- doc/build/core/reflection.rst | 4 +- doc/build/errors.rst | 8 +- doc/build/faq/ormconfiguration.rst | 8 +- doc/build/faq/sqlexpressions.rst | 8 +- doc/build/orm/cascades.rst | 4 +- doc/build/orm/composites.rst | 12 +- doc/build/orm/dataclasses.rst | 2 +- doc/build/orm/declarative_mixins.rst | 8 +- doc/build/orm/declarative_tables.rst | 10 +- doc/build/orm/extensions/associationproxy.rst | 6 +- doc/build/orm/inheritance.rst | 4 +- doc/build/orm/join_conditions.rst | 8 +- doc/build/orm/large_collections.rst | 26 +-- doc/build/orm/mapped_attributes.rst | 6 +- doc/build/orm/queryguide/api.rst | 10 +- doc/build/orm/queryguide/columns.rst | 44 ++--- doc/build/orm/queryguide/dml.rst | 40 ++-- doc/build/orm/queryguide/inheritance.rst | 49 +++-- doc/build/orm/queryguide/relationships.rst | 24 +-- doc/build/orm/queryguide/select.rst | 86 ++++----- doc/build/orm/quickstart.rst | 24 +-- doc/build/orm/relationship_persistence.rst | 2 +- doc/build/orm/self_referential.rst | 4 +- doc/build/orm/session_state_management.rst | 2 +- doc/build/tutorial/data_insert.rst | 14 +- doc/build/tutorial/data_select.rst | 172 +++++++++--------- doc/build/tutorial/data_update.rst | 26 +-- doc/build/tutorial/dbapi_transactions.rst | 24 +-- doc/build/tutorial/metadata.rst | 4 +- doc/build/tutorial/orm_data_manipulation.rst | 18 +- doc/build/tutorial/orm_related_objects.rst | 22 +-- lib/sqlalchemy/dialects/mssql/base.py | 4 +- lib/sqlalchemy/dialects/mysql/base.py | 8 +- lib/sqlalchemy/dialects/postgresql/base.py | 40 ++-- lib/sqlalchemy/dialects/sqlite/base.py | 18 +- lib/sqlalchemy/ext/hybrid.py | 14 +- lib/sqlalchemy/sql/_elements_constructors.py | 16 +- lib/sqlalchemy/sql/dml.py | 4 +- lib/sqlalchemy/sql/functions.py | 30 +-- lib/sqlalchemy/sql/selectable.py | 14 +- lib/sqlalchemy/sql/sqltypes.py | 2 +- test/base/test_tutorials.py | 4 +- tools/format_docs_code.py | 6 +- 57 files changed, 483 insertions(+), 489 deletions(-) diff --git a/doc/build/changelog/migration_08.rst b/doc/build/changelog/migration_08.rst index 3faecf08f3..0ec39ed3c5 100644 --- a/doc/build/changelog/migration_08.rst +++ b/doc/build/changelog/migration_08.rst @@ -755,7 +755,7 @@ whenever the ``test_table.c.data`` column is rendered in the columns clause of a SELECT statement:: >>> print(select([test_table]).where(test_table.c.data == "HI")) - SELECT lower(test_table.data) AS data + {printsql}SELECT lower(test_table.data) AS data FROM test_table WHERE test_table.data = lower(:data_1) @@ -955,7 +955,7 @@ when features such as :meth:`_schema.MetaData.create_all` and :func:`.cast` is u >>> stmt = select([cast(sometable.c.somechar, String(20, collation="utf8"))]) >>> print(stmt) - SELECT CAST(sometable.somechar AS VARCHAR(20) COLLATE "utf8") AS anon_1 + {printsql}SELECT CAST(sometable.somechar AS VARCHAR(20) COLLATE "utf8") AS anon_1 FROM sometable .. seealso:: diff --git a/doc/build/changelog/migration_09.rst b/doc/build/changelog/migration_09.rst index 37f619c9de..ec2c31242b 100644 --- a/doc/build/changelog/migration_09.rst +++ b/doc/build/changelog/migration_09.rst @@ -655,7 +655,7 @@ signs within the enumerated values:: >>> type = postgresql.ENUM("one", "two", "three's", name="myenum") >>> from sqlalchemy.dialects.postgresql import base >>> print(base.CreateEnumType(type).compile(dialect=postgresql.dialect())) - CREATE TYPE myenum AS ENUM ('one','two','three''s') + {printsql}CREATE TYPE myenum AS ENUM ('one','two','three''s') Existing workarounds which already escape single quote signs will need to be modified, else they will now double-escape. @@ -894,7 +894,7 @@ where it will be used to render an ``INSERT .. SELECT`` construct:: >>> t1 = table("t1", column("a"), column("b")) >>> t2 = table("t2", column("x"), column("y")) >>> print(t1.insert().from_select(["a", "b"], t2.select().where(t2.c.y == 5))) - INSERT INTO t1 (a, b) SELECT t2.x, t2.y + {printsql}INSERT INTO t1 (a, b) SELECT t2.x, t2.y FROM t2 WHERE t2.y = :y_1 @@ -1603,7 +1603,7 @@ on backends that don't feature ``true``/``false`` constant behavior:: >>> from sqlalchemy.dialects import mysql, postgresql >>> print(select([t1]).where(t1.c.x).compile(dialect=mysql.dialect())) - SELECT t.x, t.y FROM t WHERE t.x = 1 + {printsql}SELECT t.x, t.y FROM t WHERE t.x = 1 The :func:`.and_` and :func:`.or_` constructs will now exhibit quasi "short circuit" behavior, that is truncating a rendered expression, when a @@ -1612,32 +1612,32 @@ The :func:`.and_` and :func:`.or_` constructs will now exhibit quasi >>> print( ... select([t1]).where(and_(t1.c.y > 5, false())).compile(dialect=postgresql.dialect()) ... ) - SELECT t.x, t.y FROM t WHERE false + {printsql}SELECT t.x, t.y FROM t WHERE false :func:`.true` can be used as the base to build up an expression:: >>> expr = true() >>> expr = expr & (t1.c.y > 5) >>> print(select([t1]).where(expr)) - SELECT t.x, t.y FROM t WHERE t.y > :y_1 + {printsql}SELECT t.x, t.y FROM t WHERE t.y > :y_1 The boolean constants :func:`.true` and :func:`.false` themselves render as ``0 = 1`` and ``1 = 1`` for a backend with no boolean constants:: >>> print(select([t1]).where(and_(t1.c.y > 5, false())).compile(dialect=mysql.dialect())) - SELECT t.x, t.y FROM t WHERE 0 = 1 + {printsql}SELECT t.x, t.y FROM t WHERE 0 = 1 Interpretation of ``None``, while not particularly valid SQL, is at least now consistent:: >>> print(select([t1.c.x]).where(None)) - SELECT t.x FROM t WHERE NULL + {printsql}SELECT t.x FROM t WHERE NULL >>> print(select([t1.c.x]).where(None).where(None)) - SELECT t.x FROM t WHERE NULL AND NULL + {printsql}SELECT t.x FROM t WHERE NULL AND NULL >>> print(select([t1.c.x]).where(and_(None, None))) - SELECT t.x FROM t WHERE NULL AND NULL + {printsql}SELECT t.x FROM t WHERE NULL AND NULL :ticket:`2804` diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst index 0732900c6d..302b5492d8 100644 --- a/doc/build/changelog/migration_11.rst +++ b/doc/build/changelog/migration_11.rst @@ -1197,8 +1197,8 @@ statement:: ... ), ... ) >>> - >>> print(insert) # note formatting added for clarity - WITH upsert AS + >>> print(insert) # Note: formatting added for clarity + {printsql}WITH upsert AS (UPDATE orders SET amount=:amount, product=:product, quantity=:quantity WHERE orders.region = :region_1 RETURNING orders.region, orders.amount, orders.product, orders.quantity @@ -1265,7 +1265,7 @@ selectable, e.g. lateral correlation:: ... .lateral("book_subq") ... ) >>> print(select([people]).select_from(people.join(subq, true()))) - SELECT people.people_id, people.age, people.name + {printsql}SELECT people.people_id, people.age, people.name FROM people JOIN LATERAL (SELECT books.book_id AS book_id FROM books WHERE books.owner_id = people.people_id) AS book_subq ON true @@ -2058,7 +2058,7 @@ datatypes:: >>> from sqlalchemy import table, column t>>> t = table('x', column('a'), column('b')) >>> print(t.insert().returning(t.c.a, t.c.b)) - INSERT INTO x (a, b) VALUES (:a, :b) RETURNING x.a, x.b + {printsql}INSERT INTO x (a, b) VALUES (:a, :b) RETURNING x.a, x.b The ``str()`` function now calls upon an entirely separate dialect / compiler intended just for plain string printing without a specific dialect set up, @@ -2374,8 +2374,7 @@ passed through the literal quoting system:: >>> from sqlalchemy.types import String >>> t = Table("t", MetaData(), Column("x", String(), server_default="hi ' there")) >>> print(CreateTable(t)) - - CREATE TABLE t ( + {printsql}CREATE TABLE t ( x VARCHAR DEFAULT 'hi '' there' ) @@ -2994,7 +2993,7 @@ name into an alias:: >>> eng = create_engine("mssql+pymssql://mydsn", legacy_schema_aliasing=True) >>> print(account_table.select().compile(eng)) - SELECT account_1.id, account_1.info + {printsql}SELECT account_1.id, account_1.info FROM customer_schema.account AS account_1 However, this aliasing has been shown to be unnecessary and in many cases diff --git a/doc/build/changelog/migration_12.rst b/doc/build/changelog/migration_12.rst index 50b0905fb5..46307c5f9a 100644 --- a/doc/build/changelog/migration_12.rst +++ b/doc/build/changelog/migration_12.rst @@ -189,7 +189,7 @@ are loaded with additional SELECT statements: ... ) >>> query.all() - {opensql}SELECT + {execsql}SELECT employee.id AS employee_id, employee.name AS employee_name, employee.type AS employee_type @@ -578,7 +578,7 @@ query across the two proxies ``A.b_values``, ``AtoB.b_value``: .. sourcecode:: pycon+sql >>> s.query(A).filter(A.b_values.contains("hi")).all() - {opensql}SELECT a.id AS a_id + {execsql}SELECT a.id AS a_id FROM a WHERE EXISTS (SELECT 1 FROM atob @@ -592,7 +592,7 @@ to query across the two proxies ``A.c_values``, ``AtoB.c_value``: .. sourcecode:: pycon+sql >>> s.query(A).filter(A.c_values.any(value="x")).all() - {opensql}SELECT a.id AS a_id + {execsql}SELECT a.id AS a_id FROM a WHERE EXISTS (SELECT 1 FROM atob @@ -1036,7 +1036,7 @@ are named in the documentation now:: ... ) ... ) >>> print(stmt) - SELECT sum(t.value) AS sum_1 + {printsql}SELECT sum(t.value) AS sum_1 FROM t GROUP BY GROUPING SETS((t.x, t.y), (t.z, t.q)) :ticket:`3429` diff --git a/doc/build/changelog/migration_13.rst b/doc/build/changelog/migration_13.rst index e799e71811..2c38511f15 100644 --- a/doc/build/changelog/migration_13.rst +++ b/doc/build/changelog/migration_13.rst @@ -533,7 +533,7 @@ The ``User.values`` association proxy refers to the ``Element.value`` column. Standard column operations are now available, such as ``like``:: >>> print(s.query(User).filter(User.values.like("%foo%"))) - SELECT "user".id AS user_id + {printsql}SELECT "user".id AS user_id FROM "user" WHERE EXISTS (SELECT 1 FROM element @@ -542,7 +542,7 @@ Standard column operations are now available, such as ``like``:: ``equals``:: >>> print(s.query(User).filter(User.values == "foo")) - SELECT "user".id AS user_id + {printsql}SELECT "user".id AS user_id FROM "user" WHERE EXISTS (SELECT 1 FROM element @@ -553,7 +553,7 @@ a test that the related row does not exist at all; this is the same behavior as before:: >>> print(s.query(User).filter(User.values == None)) - SELECT "user".id AS user_id + {printsql}SELECT "user".id AS user_id FROM "user" WHERE (EXISTS (SELECT 1 FROM element @@ -567,7 +567,7 @@ the association proxy used ``.contains`` as a list containment operator only. With a column-oriented comparison, it now behaves like a "like":: >>> print(s.query(User).filter(User.values.contains("foo"))) - SELECT "user".id AS user_id + {printsql}SELECT "user".id AS user_id FROM "user" WHERE EXISTS (SELECT 1 FROM element @@ -1234,7 +1234,7 @@ backend, such as "SELECT CAST(NULL AS INTEGER) WHERE 1!=1" for PostgreSQL, ... ), ... q=[], ... ) - SELECT 1 WHERE 1 IN (SELECT CAST(NULL AS INTEGER) WHERE 1!=1) + {exexsql}SELECT 1 WHERE 1 IN (SELECT CAST(NULL AS INTEGER) WHERE 1!=1) The feature also works for tuple-oriented IN statements, where the "empty IN" expression will be expanded to support the elements given inside the tuple, @@ -1250,7 +1250,7 @@ such as on PostgreSQL:: ... ), ... q=[], ... ) - SELECT 1 WHERE (%(param_1)s, %(param_2)s) + {exexsql}SELECT 1 WHERE (%(param_1)s, %(param_2)s) IN (SELECT CAST(NULL AS INTEGER), CAST(NULL AS VARCHAR) WHERE 1!=1) diff --git a/doc/build/changelog/migration_14.rst b/doc/build/changelog/migration_14.rst index df7080d0aa..d23f8ea1d3 100644 --- a/doc/build/changelog/migration_14.rst +++ b/doc/build/changelog/migration_14.rst @@ -1071,14 +1071,14 @@ an IN expression:: The pre-execution string representation is:: >>> print(stmt) - SELECT a.id, a.data + {printsql}SELECT a.id, a.data FROM a WHERE a.id IN ([POSTCOMPILE_id_1]) To render the values directly, use ``literal_binds`` as was the case previously:: >>> print(stmt.compile(compile_kwargs={"literal_binds": True})) - SELECT a.id, a.data + {printsql}SELECT a.id, a.data FROM a WHERE a.id IN (1, 2, 3) @@ -1086,7 +1086,7 @@ A new flag, "render_postcompile", is added as a helper to allow the current bound value to be rendered as it would be passed to the database:: >>> print(stmt.compile(compile_kwargs={"render_postcompile": True})) - SELECT a.id, a.data + {printsql}SELECT a.id, a.data FROM a WHERE a.id IN (:id_1_1, :id_1_2, :id_1_3) @@ -1260,7 +1260,7 @@ method will not emit a warning unless the linting flag is supplied:: >>> from sqlalchemy.sql import FROM_LINTING >>> print(q.statement.compile(linting=FROM_LINTING)) SAWarning: SELECT statement has a cartesian product between FROM element(s) "addresses" and FROM element "users". Apply join condition(s) between each element to resolve. - SELECT users.id, users.name, users.fullname, users.nickname + {printsql}SELECT users.id, users.name, users.fullname, users.nickname FROM addresses, users JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id WHERE addresses.email_address = :email_address_1 @@ -1538,7 +1538,7 @@ such as :class:`.Subquery` and :class:`_expression.Alias`:: ] >>> print(stmt.subquery().select()) - SELECT anon_1.c1, anon_1.c2, anon_1.c2, anon_1.c2, anon_1.c4 + {printsql}SELECT anon_1.c1, anon_1.c2, anon_1.c2, anon_1.c2, anon_1.c4 FROM (SELECT c1, c2, c3 AS c2, c2, c4) AS anon_1 :class:`_expression.ColumnCollection` also allows access by integer index to support @@ -1576,7 +1576,7 @@ as:: >>> from sqlalchemy import union >>> u = union(s1, s2) >>> print(u) - SELECT "user".id, "user".name, "user".id + {printsql}SELECT "user".id, "user".name, "user".id FROM "user" UNION SELECT c1, c2, c3 @@ -1642,14 +1642,14 @@ reasonable behavior for simple modifications to a single column, most prominently with CAST:: >>> print(select(cast(foo.c.data, String))) - SELECT CAST(foo.data AS VARCHAR) AS data + {printsql}SELECT CAST(foo.data AS VARCHAR) AS data FROM foo For CAST against expressions that don't have a name, the previous logic is used to generate the usual "anonymous" labels:: >>> print(select(cast("hi there," + foo.c.data, String))) - SELECT CAST(:data_1 + foo.data AS VARCHAR) AS anon_1 + {printsql}SELECT CAST(:data_1 + foo.data AS VARCHAR) AS anon_1 FROM foo A :func:`.cast` against a :class:`.Label`, despite having to omit the label @@ -1657,14 +1657,14 @@ expression as these don't render inside of a CAST, will nonetheless make use of the given name:: >>> print(select(cast(("hi there," + foo.c.data).label("hello_data"), String))) - SELECT CAST(:data_1 + foo.data AS VARCHAR) AS hello_data + {printsql}SELECT CAST(:data_1 + foo.data AS VARCHAR) AS hello_data FROM foo And of course as was always the case, :class:`.Label` can be applied to the expression on the outside to apply an "AS " label directly:: >>> print(select(cast(("hi there," + foo.c.data), String).label("hello_data"))) - SELECT CAST(:data_1 + foo.data AS VARCHAR) AS hello_data + {printsql}SELECT CAST(:data_1 + foo.data AS VARCHAR) AS hello_data FROM foo @@ -2291,7 +2291,7 @@ the ``.data`` column attribute, the object is refreshed and this will now include the joinedload operation as well:: >>> a1.data - SELECT a.id AS a_id, a.data AS a_data, b_1.id AS b_1_id, b_1.a_id AS b_1_a_id + {execsql}SELECT a.id AS a_id, a.data AS a_data, b_1.id AS b_1_id, b_1.a_id AS b_1_a_id FROM a LEFT OUTER JOIN b AS b_1 ON a.id = b_1.a_id WHERE a.id = ? @@ -2310,7 +2310,7 @@ an additional query:: >>> a1.data = "new data" >>> session.commit() >>> a1.data - SELECT a.id AS a_id, a.data AS a_data + {execsql}SELECT a.id AS a_id, a.data AS a_data FROM a WHERE a.id = ? (1,) diff --git a/doc/build/changelog/whatsnew_20.rst b/doc/build/changelog/whatsnew_20.rst index d94250d712..adf0971c3c 100644 --- a/doc/build/changelog/whatsnew_20.rst +++ b/doc/build/changelog/whatsnew_20.rst @@ -1831,7 +1831,7 @@ simple ``CREATE SEQUENCE`` DDL, if no additional arguments were specified:: >>> from sqlalchemy import Sequence >>> from sqlalchemy.schema import CreateSequence >>> print(CreateSequence(Sequence("my_seq"))) - CREATE SEQUENCE my_seq + {printsql}CREATE SEQUENCE my_seq However, as :class:`.Sequence` support was added for MS SQL Server, where the default start value is inconveniently set to ``-2**63``, @@ -1842,7 +1842,7 @@ version 1.4 decided to default the DDL to emit a start value of 1, if >>> from sqlalchemy import Sequence >>> from sqlalchemy.schema import CreateSequence >>> print(CreateSequence(Sequence("my_seq"))) - CREATE SEQUENCE my_seq START WITH 1 + {printsql}CREATE SEQUENCE my_seq START WITH 1 This change has introduced other complexities, including that when the :paramref:`.Sequence.min_value` parameter is included, this default of diff --git a/doc/build/core/connections.rst b/doc/build/core/connections.rst index 1b842a4e92..80156b4183 100644 --- a/doc/build/core/connections.rst +++ b/doc/build/core/connections.rst @@ -1475,10 +1475,10 @@ Basic guidelines include: >>> with engine.connect() as conn: ... print(conn.scalar(my_stmt(5, 10))) ... print(conn.scalar(my_stmt(12, 8))) - {opensql}SELECT max(?, ?) AS max_1 + {execsql}SELECT max(?, ?) AS max_1 [generated in 0.00057s] (5, 10){stop} 10 - {opensql}SELECT max(?, ?) AS max_1 + {execsql}SELECT max(?, ?) AS max_1 [cached since 0.002059s ago] (12, 8){stop} 12 diff --git a/doc/build/core/constraints.rst b/doc/build/core/constraints.rst index ff2ae4916c..3b05b8c12d 100644 --- a/doc/build/core/constraints.rst +++ b/doc/build/core/constraints.rst @@ -158,7 +158,7 @@ constraints are created separately: >>> with engine.connect() as conn: ... metadata_obj.create_all(conn, checkfirst=False) - {opensql}CREATE TABLE element ( + {execsql}CREATE TABLE element ( element_id SERIAL NOT NULL, parent_node_id INTEGER, PRIMARY KEY (element_id) @@ -186,7 +186,7 @@ those constraints that are named: >>> with engine.connect() as conn: ... metadata_obj.drop_all(conn, checkfirst=False) - {opensql}ALTER TABLE element DROP CONSTRAINT fk_element_parent_node_id + {execsql}ALTER TABLE element DROP CONSTRAINT fk_element_parent_node_id DROP TABLE node DROP TABLE element {stop} @@ -232,7 +232,7 @@ and not the other one: >>> with engine.connect() as conn: ... metadata_obj.create_all(conn, checkfirst=False) - {opensql}CREATE TABLE element ( + {execsql}CREATE TABLE element ( element_id SERIAL NOT NULL, parent_node_id INTEGER, PRIMARY KEY (element_id) @@ -388,7 +388,7 @@ MySQL. ) mytable.create(engine) - {opensql}CREATE TABLE mytable ( + {execsql}CREATE TABLE mytable ( col1 INTEGER CHECK (col1>5), col2 INTEGER, col3 INTEGER, @@ -875,7 +875,7 @@ INDEX" is issued right after the create statements for the table: Index("myindex", mytable.c.col5, mytable.c.col6, unique=True) mytable.create(engine) - {opensql}CREATE TABLE mytable ( + {execsql}CREATE TABLE mytable ( col1 INTEGER, col2 INTEGER, col3 INTEGER, @@ -914,7 +914,7 @@ The :class:`~sqlalchemy.schema.Index` object also supports its own ``create()`` i = Index("someindex", mytable.c.col5) i.create(engine) - {opensql}CREATE INDEX someindex ON mytable (col5){stop} + {execsql}CREATE INDEX someindex ON mytable (col5){stop} .. _schema_indexes_functional: diff --git a/doc/build/core/custom_types.rst b/doc/build/core/custom_types.rst index 2c8aeeda6f..16c7bbec97 100644 --- a/doc/build/core/custom_types.rst +++ b/doc/build/core/custom_types.rst @@ -687,7 +687,7 @@ datatype. For example:: ... ) >>> engine = create_engine("sqlite://", echo="debug") >>> my_table.create(engine) - INFO sqlalchemy.engine.base.Engine + {execsql}INFO sqlalchemy.engine.base.Engine CREATE TABLE my_table ( id INTEGER, data BLOB @@ -711,7 +711,7 @@ created; we instead get back :class:`.BLOB`:: >>> metadata_two = MetaData() >>> my_reflected_table = Table("my_table", metadata_two, autoload_with=engine) - INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("my_table") + {execsql}INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("my_table") INFO sqlalchemy.engine.base.Engine () DEBUG sqlalchemy.engine.base.Engine Col ('cid', 'name', 'type', 'notnull', 'dflt_value', 'pk') DEBUG sqlalchemy.engine.base.Engine Row (0, 'id', 'INTEGER', 0, None, 0) diff --git a/doc/build/core/ddl.rst b/doc/build/core/ddl.rst index d5d6ded30f..1e323dea2b 100644 --- a/doc/build/core/ddl.rst +++ b/doc/build/core/ddl.rst @@ -125,7 +125,7 @@ first looking within the PostgreSQL catalogs to see if it exists: ) users.create(engine) - {opensql}CREATE TABLE users ( + {execsql}CREATE TABLE users ( user_id SERIAL NOT NULL, user_name VARCHAR(40) NOT NULL, PRIMARY KEY (user_id) @@ -136,7 +136,7 @@ first looking within the PostgreSQL catalogs to see if it exists: {stop} users.drop(engine) - {opensql}SELECT conname FROM pg_constraint WHERE conname='cst_user_name_length' + {execsql}SELECT conname FROM pg_constraint WHERE conname='cst_user_name_length' ALTER TABLE users DROP CONSTRAINT cst_user_name_length DROP TABLE users{stop} @@ -154,7 +154,7 @@ one can use the :class:`.CreateTable` construct: with engine.connect() as conn: conn.execute(CreateTable(mytable)) - {opensql}CREATE TABLE mytable ( + {execsql}CREATE TABLE mytable ( col1 INTEGER, col2 INTEGER, col3 INTEGER, @@ -226,7 +226,7 @@ dialect, for example, neither construct will be included: >>> from sqlalchemy import create_engine >>> sqlite_engine = create_engine("sqlite+pysqlite://", echo=True) >>> meta.create_all(sqlite_engine) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) PRAGMA main.table_info("my_table") [raw sql] () PRAGMA temp.table_info("my_table") @@ -250,7 +250,7 @@ statement emitted for the index: ... "postgresql+psycopg2://scott:tiger@localhost/test", echo=True ... ) >>> meta.create_all(postgresql_engine) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s [generated in 0.00009s] {'name': 'my_table'} diff --git a/doc/build/core/defaults.rst b/doc/build/core/defaults.rst index 848b32b17c..ee9d883ad8 100644 --- a/doc/build/core/defaults.rst +++ b/doc/build/core/defaults.rst @@ -496,7 +496,7 @@ appropriate for the target backend:: >>> my_seq = Sequence("some_sequence", start=1) >>> stmt = select(my_seq.next_value()) >>> print(stmt.compile(dialect=postgresql.dialect())) - SELECT nextval('some_sequence') AS next_value_1 + {printsql}SELECT nextval('some_sequence') AS next_value_1 .. _sequence_metadata: diff --git a/doc/build/core/metadata.rst b/doc/build/core/metadata.rst index f416b6e6df..2503f0b66a 100644 --- a/doc/build/core/metadata.rst +++ b/doc/build/core/metadata.rst @@ -197,7 +197,7 @@ will issue the CREATE statements: ) metadata_obj.create_all(engine) - {opensql}PRAGMA table_info(user){} + {execsql}PRAGMA table_info(user){} CREATE TABLE user( user_id INTEGER NOT NULL PRIMARY KEY, user_name VARCHAR(16) NOT NULL, @@ -241,7 +241,7 @@ default issue the CREATE or DROP regardless of the table being present: Column("employee_dept", Integer, ForeignKey("departments.department_id")), ) employees.create(engine) - {opensql}CREATE TABLE employees( + {execsql}CREATE TABLE employees( employee_id SERIAL NOT NULL PRIMARY KEY, employee_name VARCHAR(60) NOT NULL, employee_dept INTEGER REFERENCES departments(department_id) @@ -253,7 +253,7 @@ default issue the CREATE or DROP regardless of the table being present: .. sourcecode:: python+sql employees.drop(engine) - {opensql}DROP TABLE employees + {execsql}DROP TABLE employees {} To enable the "check first for the table existing" logic, add the diff --git a/doc/build/core/operators.rst b/doc/build/core/operators.rst index 9bf47ec669..84fc554fc6 100644 --- a/doc/build/core/operators.rst +++ b/doc/build/core/operators.rst @@ -193,7 +193,7 @@ at execution time, illustrated below: >>> stmt = select(User.id).where(User.id.in_([1, 2, 3])) >>> result = conn.execute(stmt) - {opensql}SELECT user_account.id + {execsql}SELECT user_account.id FROM user_account WHERE user_account.id IN (?, ?, ?) [...] (1, 2, 3){stop} @@ -209,7 +209,7 @@ in other words, "it just works": >>> stmt = select(User.id).where(User.id.in_([])) >>> result = conn.execute(stmt) - {opensql}SELECT user_account.id + {execsql}SELECT user_account.id FROM user_account WHERE user_account.id IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1) [...] () @@ -254,7 +254,7 @@ To illustrate the parameters rendered: >>> tup = tuple_(User.id, Address.id) >>> stmt = select(User.name).join(Address).where(tup.in_([(1, 1), (2, 2)])) >>> conn.execute(stmt).all() - {opensql}SELECT user_account.name + {execsql}SELECT user_account.name FROM user_account JOIN address ON user_account.id = address.user_id WHERE (user_account.id, address.id) IN (VALUES (?, ?), (?, ?)) [...] (1, 1, 2, 2){stop} @@ -601,7 +601,7 @@ The most common conjunction, "AND", is automatically applied if we make repeated ... .where(user_table.c.name == "squidward") ... .where(address_table.c.user_id == user_table.c.id) ... ) - 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 @@ -613,7 +613,7 @@ The most common conjunction, "AND", is automatically applied if we make repeated ... address_table.c.user_id == user_table.c.id, ... ) ... ) - 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 @@ -629,7 +629,7 @@ The "AND" conjunction, as well as its partner "OR", are both available directly ... ) ... ) ... ) - 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 diff --git a/doc/build/core/reflection.rst b/doc/build/core/reflection.rst index 5a84bc2ffa..1ff517510e 100644 --- a/doc/build/core/reflection.rst +++ b/doc/build/core/reflection.rst @@ -460,7 +460,7 @@ object. We can then, for demonstration purposes, print out the MySQL-specific >>> from sqlalchemy.schema import CreateTable >>> print(CreateTable(my_mysql_table).compile(mysql_engine)) - {opensql}CREATE TABLE my_table ( + {printsql}CREATE TABLE my_table ( id INTEGER(11) NOT NULL AUTO_INCREMENT, data1 VARCHAR(50) CHARACTER SET latin1, data2 MEDIUMINT(4), @@ -501,7 +501,7 @@ We now get a new :class:`_schema.Table` that is generic and uses >>> pg_engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/test", echo=True) >>> my_generic_table.create(pg_engine) - {opensql}CREATE TABLE my_table ( + {execsql}CREATE TABLE my_table ( id SERIAL NOT NULL, data1 VARCHAR(50), data2 INTEGER, diff --git a/doc/build/errors.rst b/doc/build/errors.rst index 87477db382..e7878e06aa 100644 --- a/doc/build/errors.rst +++ b/doc/build/errors.rst @@ -767,7 +767,7 @@ ORM, we get SQL that looks like the following: >>> stmt = select(Employee, Manager).join(Employee.reports_to) >>> print(stmt) - {opensql}SELECT employee.id, employee.manager_id, employee.name, + {printsql}SELECT employee.id, employee.manager_id, employee.name, employee.type, manager_1.id AS id_1, employee_1.id AS id_2, employee_1.manager_id AS manager_id_1, employee_1.name AS name_1, employee_1.type AS type_1 @@ -804,7 +804,7 @@ embedding the join into a new subquery: >>> manager_alias = aliased(Manager, flat=True) >>> stmt = select(Employee, manager_alias).join(Employee.reports_to.of_type(manager_alias)) >>> print(stmt) - {opensql}SELECT employee.id, employee.manager_id, employee.name, + {printsql}SELECT employee.id, employee.manager_id, employee.name, employee.type, manager_1.id AS id_1, employee_1.id AS id_2, employee_1.manager_id AS manager_id_1, employee_1.name AS name_1, employee_1.type AS type_1 @@ -1043,7 +1043,7 @@ not detect the same setting in terms of ``A.bs``: >>> a1.bs = [b1, b2] >>> session.add_all([a1, b1, b2]) >>> session.commit() - {opensql} + {execsql} INSERT INTO a DEFAULT VALUES () INSERT INTO b (a_id) VALUES (?) @@ -1061,7 +1061,7 @@ to NULL, but this is usually not what's desired: >>> session.delete(b1) >>> session.commit() - {opensql} + {execsql} UPDATE b SET a_id=? WHERE b.id = ? (None, 2) DELETE FROM b WHERE b.id = ? diff --git a/doc/build/faq/ormconfiguration.rst b/doc/build/faq/ormconfiguration.rst index 3ff3c93b80..47e9e2a494 100644 --- a/doc/build/faq/ormconfiguration.rst +++ b/doc/build/faq/ormconfiguration.rst @@ -268,11 +268,11 @@ We see two queries emitted like this: .. sourcecode:: pycon+sql >>> session.scalars(select(User).options(subqueryload(User.addresses))).all() - {opensql}-- the "main" query + {execsql}-- the "main" query SELECT users.id AS users_id FROM users {stop} - {opensql}-- the "load" query issued by subqueryload + {execsql}-- the "load" query issued by subqueryload SELECT addresses.id AS addresses_id, addresses.user_id AS addresses_user_id, anon_1.users_id AS anon_1_users_id @@ -289,12 +289,12 @@ the two queries may not see the same results: >>> user = session.scalars( ... select(User).options(subqueryload(User.addresses)).limit(1) ... ).first() - {opensql}-- the "main" query + {execsql}-- the "main" query SELECT users.id AS users_id FROM users LIMIT 1 {stop} - {opensql}-- the "load" query issued by subqueryload + {execsql}-- the "load" query issued by subqueryload SELECT addresses.id AS addresses_id, addresses.user_id AS addresses_user_id, anon_1.users_id AS anon_1_users_id diff --git a/doc/build/faq/sqlexpressions.rst b/doc/build/faq/sqlexpressions.rst index eb3d906d18..73aa65f262 100644 --- a/doc/build/faq/sqlexpressions.rst +++ b/doc/build/faq/sqlexpressions.rst @@ -22,7 +22,7 @@ if we don't use it explicitly):: >>> t = table("my_table", column("x")) >>> statement = select(t) >>> print(str(statement)) - SELECT my_table.x + {printsql}SELECT my_table.x FROM my_table The ``str()`` builtin, or an equivalent, can be invoked on ORM @@ -402,7 +402,7 @@ escaping behavior:: >>> from sqlalchemy.dialects import postgresql >>> t = table("my_table", column("value % one"), column("value % two")) >>> print(t.select().compile(dialect=postgresql.dialect())) - SELECT my_table."value %% one", my_table."value %% two" + {printsql}SELECT my_table."value %% one", my_table."value %% two" FROM my_table When such a dialect is being used, if non-DBAPI statements are desired that @@ -412,7 +412,7 @@ signs is to simply substitute in an empty set of parameters using Python's >>> strstmt = str(t.select().compile(dialect=postgresql.dialect())) >>> print(strstmt % ()) - SELECT my_table."value % one", my_table."value % two" + {printsql}SELECT my_table."value % one", my_table."value % two" FROM my_table The other is to set a different parameter style on the dialect being used; all @@ -424,7 +424,7 @@ percent signs are no longer significant in the compiled form of SQL, and will no longer be escaped:: >>> print(t.select().compile(dialect=postgresql.dialect(paramstyle="named"))) - SELECT my_table."value % one", my_table."value % two" + {printsql}SELECT my_table."value % one", my_table."value % two" FROM my_table diff --git a/doc/build/orm/cascades.rst b/doc/build/orm/cascades.rst index 2c7c0d9cf4..02d68669ee 100644 --- a/doc/build/orm/cascades.rst +++ b/doc/build/orm/cascades.rst @@ -258,7 +258,7 @@ If we mark ``user1`` for deletion, after the flush operation proceeds, >>> sess.delete(user1) >>> sess.commit() - {opensql}DELETE FROM address WHERE address.id = ? + {execsql}DELETE FROM address WHERE address.id = ? ((1,), (2,)) DELETE FROM user WHERE user.id = ? (1,) @@ -281,7 +281,7 @@ deleted, but are instead de-associated: >>> sess.delete(user1) >>> sess.commit() - {opensql}UPDATE address SET user_id=? WHERE address.id = ? + {execsql}UPDATE address SET user_id=? WHERE address.id = ? (None, 1) UPDATE address SET user_id=? WHERE address.id = ? (None, 2) diff --git a/doc/build/orm/composites.rst b/doc/build/orm/composites.rst index 1dd857739e..ab2b551095 100644 --- a/doc/build/orm/composites.rst +++ b/doc/build/orm/composites.rst @@ -69,7 +69,7 @@ The above mapping would correspond to a CREATE TABLE statement as: >>> from sqlalchemy.schema import CreateTable >>> print(CreateTable(Vertex.__table__)) - {opensql}CREATE TABLE vertices ( + {printsql}CREATE TABLE vertices ( id INTEGER NOT NULL, x1 INTEGER NOT NULL, y1 INTEGER NOT NULL, @@ -99,7 +99,7 @@ well as with instances of the ``Vertex`` class, where the ``.start`` and >>> v = Vertex(start=Point(3, 4), end=Point(5, 6)) >>> session.add(v) >>> session.commit() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) INSERT INTO vertices (x1, y1, x2, y2) VALUES (?, ?, ?, ?) [generated in ...] (3, 4, 5, 6) COMMIT @@ -115,7 +115,7 @@ well as with instances of the ``Vertex`` class, where the ``.start`` and >>> stmt = select(Vertex.start, Vertex.end) >>> session.execute(stmt).all() - {opensql}SELECT vertices.x1, vertices.y1, vertices.x2, vertices.y2 + {execsql}SELECT vertices.x1, vertices.y1, vertices.x2, vertices.y2 FROM vertices [...] () {stop}[(Point(x=3, y=4), Point(x=5, y=6))] @@ -129,7 +129,7 @@ well as with instances of the ``Vertex`` class, where the ``.start`` and >>> stmt = select(Vertex).where(Vertex.start == Point(3, 4)).where(Vertex.end < Point(7, 8)) >>> session.scalars(stmt).all() - {opensql}SELECT vertices.id, vertices.x1, vertices.y1, vertices.x2, vertices.y2 + {execsql}SELECT vertices.id, vertices.x1, vertices.y1, vertices.x2, vertices.y2 FROM vertices WHERE vertices.x1 = ? AND vertices.y1 = ? AND vertices.x2 < ? AND vertices.y2 < ? [...] (3, 4, 7, 8) @@ -157,14 +157,14 @@ well as with instances of the ``Vertex`` class, where the ``.start`` and .. sourcecode:: pycon+sql >>> v1 = session.scalars(select(Vertex)).one() - {opensql}SELECT vertices.id, vertices.x1, vertices.y1, vertices.x2, vertices.y2 + {execsql}SELECT vertices.id, vertices.x1, vertices.y1, vertices.x2, vertices.y2 FROM vertices [...] () {stop} >>> v1.end = Point(x=10, y=14) >>> session.commit() - {opensql}UPDATE vertices SET x2=?, y2=? WHERE vertices.id = ? + {execsql}UPDATE vertices SET x2=?, y2=? WHERE vertices.id = ? [...] (10, 14, 1) COMMIT diff --git a/doc/build/orm/dataclasses.rst b/doc/build/orm/dataclasses.rst index 4566d704fe..e98c67e689 100644 --- a/doc/build/orm/dataclasses.rst +++ b/doc/build/orm/dataclasses.rst @@ -262,7 +262,7 @@ parameter for ``created_at`` were passed proceeds as: >>> with Session(e) as session: ... session.add(User()) ... session.commit() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) INSERT INTO user_account (created_at) VALUES (utc_timestamp()) [generated in 0.00010s] () COMMIT diff --git a/doc/build/orm/declarative_mixins.rst b/doc/build/orm/declarative_mixins.rst index 8927e710dc..336882c73a 100644 --- a/doc/build/orm/declarative_mixins.rst +++ b/doc/build/orm/declarative_mixins.rst @@ -97,7 +97,7 @@ example:: >>> from sqlalchemy import select >>> print(select(MyModel).join(MyModel.log_record)) - SELECT mymodel.name, mymodel.id, mymodel.log_record_id + {printsql}SELECT mymodel.name, mymodel.id, mymodel.log_record_id FROM mymodel JOIN logrecord ON logrecord.id = mymodel.log_record_id .. tip:: The examples of :class:`_orm.declared_attr` will attempt to illustrate @@ -366,10 +366,10 @@ to ``Target`` accessed along the ``.target`` attribute:: >>> from sqlalchemy import select >>> print(select(Foo).join(Foo.target)) - SELECT foo.id, foo.target_id + {printsql}SELECT foo.id, foo.target_id FROM foo JOIN target ON target.id = foo.target_id >>> print(select(Bar).join(Bar.target)) - SELECT bar.id, bar.target_id + {printsql}SELECT bar.id, bar.target_id FROM bar JOIN target ON target.id = bar.target_id Special arguments such as :paramref:`_orm.relationship.primaryjoin` may also @@ -440,7 +440,7 @@ it produces the full expression:: >>> from sqlalchemy import select >>> print(select(Something.x_plus_y)) - SELECT something.x + something.y AS anon_1 + {printsql}SELECT something.x + something.y AS anon_1 FROM something .. tip:: The :class:`_orm.declared_attr` decorator causes the decorated callable diff --git a/doc/build/orm/declarative_tables.rst b/doc/build/orm/declarative_tables.rst index 806a6897f2..9442d2f161 100644 --- a/doc/build/orm/declarative_tables.rst +++ b/doc/build/orm/declarative_tables.rst @@ -344,8 +344,7 @@ first on the Microsoft SQL Server backend, illustrating the ``NVARCHAR`` datatyp >>> from sqlalchemy.schema import CreateTable >>> from sqlalchemy.dialects import mssql, postgresql >>> print(CreateTable(SomeClass.__table__).compile(dialect=mssql.dialect())) - - CREATE TABLE some_table ( + {printsql}CREATE TABLE some_table ( id BIGINT NOT NULL IDENTITY, date TIMESTAMP NOT NULL, status NVARCHAR(max) NOT NULL, @@ -355,8 +354,7 @@ first on the Microsoft SQL Server backend, illustrating the ``NVARCHAR`` datatyp Then on the PostgreSQL backend, illustrating ``TIMESTAMP WITH TIME ZONE``:: >>> print(CreateTable(SomeClass.__table__).compile(dialect=postgresql.dialect())) - - CREATE TABLE some_table ( + {printsql}CREATE TABLE some_table ( id BIGSERIAL NOT NULL, date TIMESTAMP WITH TIME ZONE NOT NULL, status VARCHAR NOT NULL, @@ -627,7 +625,7 @@ specific to each attribute:: >>> from sqlalchemy.schema import CreateTable >>> print(CreateTable(SomeClass.__table__)) - CREATE TABLE some_table ( + {printsql}CREATE TABLE some_table ( id INTEGER NOT NULL, name VARCHAR(30) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, @@ -946,7 +944,7 @@ and will see the SQL names generated:: >>> from sqlalchemy import select >>> print(select(User.id, User.name).where(User.name == "x")) - SELECT "user".user_id, "user".user_name + {printsql}SELECT "user".user_id, "user".user_name FROM "user" WHERE "user".user_name = :user_name_1 diff --git a/doc/build/orm/extensions/associationproxy.rst b/doc/build/orm/extensions/associationproxy.rst index 6334cbecdc..5281f59a55 100644 --- a/doc/build/orm/extensions/associationproxy.rst +++ b/doc/build/orm/extensions/associationproxy.rst @@ -573,7 +573,7 @@ standard column operators can be used which will be embedded in the subquery. For example a straight equality operator:: >>> print(session.scalars(select(User).where(User.special_keys == "jek"))) - SELECT "user".id AS user_id, "user".name AS user_name + {printsql}SELECT "user".id AS user_id, "user".name AS user_name FROM "user" WHERE EXISTS (SELECT 1 FROM user_keyword @@ -582,7 +582,7 @@ For example a straight equality operator:: a LIKE operator:: >>> print(session.scalars(select(User).where(User.special_keys.like("%jek")))) - SELECT "user".id AS user_id, "user".name AS user_name + {printsql}SELECT "user".id AS user_id, "user".name AS user_name FROM "user" WHERE EXISTS (SELECT 1 FROM user_keyword @@ -596,7 +596,7 @@ two association proxies linked together, so when using this proxy for generating SQL phrases, we get two levels of EXISTS subqueries:: >>> print(session.scalars(select(User).where(User.keywords.any(Keyword.keyword == "jek")))) - SELECT "user".id AS user_id, "user".name AS user_name + {printsql}SELECT "user".id AS user_id, "user".name AS user_name FROM "user" WHERE EXISTS (SELECT 1 FROM user_keyword diff --git a/doc/build/orm/inheritance.rst b/doc/build/orm/inheritance.rst index 7d7213db71..5d2896ee46 100644 --- a/doc/build/orm/inheritance.rst +++ b/doc/build/orm/inheritance.rst @@ -727,7 +727,7 @@ Upon select, the polymorphic union produces a query like this: .. sourcecode:: python+sql session.scalars(select(Employee)).all() - {opensql} + {execsql} SELECT pjoin.id, pjoin.name, @@ -882,7 +882,7 @@ class and any attributes that are locally declared upon it, such as the >>> stmt = select(Employee).where(Employee.name == "n1") >>> print(stmt) - SELECT pjoin.id, pjoin.name, pjoin.type, pjoin.manager_data, pjoin.engineer_info + {printsql}SELECT pjoin.id, pjoin.name, pjoin.type, pjoin.manager_data, pjoin.engineer_info FROM ( SELECT engineer.id AS id, engineer.name AS name, engineer.engineer_info AS engineer_info, CAST(NULL AS VARCHAR(40)) AS manager_data, 'engineer' AS type diff --git a/doc/build/orm/join_conditions.rst b/doc/build/orm/join_conditions.rst index 8f799732e1..f895654450 100644 --- a/doc/build/orm/join_conditions.rst +++ b/doc/build/orm/join_conditions.rst @@ -740,7 +740,7 @@ directly. A query from ``A`` to ``D`` looks like: sess.scalars(select(A).join(A.d)).all() - {opensql}SELECT a.id AS a_id, a.b_id AS a_b_id + {execsql}SELECT a.id AS a_id, a.b_id AS a_b_id FROM a JOIN ( b AS b_1 JOIN d AS d_1 ON b_1.d_id = d_1.id JOIN c AS c_1 ON c_1.d_id = d_1.id) @@ -845,7 +845,7 @@ With the above mapping, a simple join looks like: sess.scalars(select(A).join(A.b)).all() - {opensql}SELECT a.id AS a_id, a.b_id AS a_b_id + {execsql}SELECT a.id AS a_id, a.b_id AS a_b_id FROM a JOIN (b JOIN d ON d.b_id = b.id JOIN c ON c.id = d.c_id) ON a.b_id = b.id Using the AliasedClass target in Queries @@ -871,7 +871,7 @@ A query using the above ``A.b`` relationship will render a subquery: sess.scalars(select(A).join(A.b)).all() - {opensql}SELECT a.id AS a_id, a.b_id AS a_b_id + {execsql}SELECT a.id AS a_id, a.b_id AS a_b_id FROM a JOIN (SELECT b.id AS id, b.some_b_column AS some_b_column FROM b JOIN d ON d.b_id = b.id JOIN c ON c.id = d.c_id) AS anon_1 ON a.b_id = anon_1.id @@ -887,7 +887,7 @@ so in terms of ``B_viacd_subquery`` rather than ``B`` directly: .order_by(B_viacd_subquery.id) ).all() - {opensql}SELECT a.id AS a_id, a.b_id AS a_b_id + {execsql}SELECT a.id AS a_id, a.b_id AS a_b_id FROM a JOIN (SELECT b.id AS id, b.some_b_column AS some_b_column FROM b JOIN d ON d.b_id = b.id JOIN c ON c.id = d.c_id) AS anon_1 ON a.b_id = anon_1.id WHERE anon_1.some_b_column = ? ORDER BY anon_1.id diff --git a/doc/build/orm/large_collections.rst b/doc/build/orm/large_collections.rst index 4d9f96e9cf..8553fae42e 100644 --- a/doc/build/orm/large_collections.rst +++ b/doc/build/orm/large_collections.rst @@ -149,7 +149,7 @@ source of objects to start, where below we use a Python ``list``:: >>> with Session(engine) as session: ... session.add(new_account) ... session.commit() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) INSERT INTO account (identifier) VALUES (?) [...] ('account_01',) INSERT INTO account_transaction (account_id, description, amount, timestamp) VALUES @@ -185,7 +185,7 @@ methods:: >>> from sqlalchemy import select >>> session = Session(engine, expire_on_commit=False) >>> existing_account = session.scalar(select(Account).filter_by(identifier="account_01")) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT account.id, account.identifier FROM account WHERE account.identifier = ? @@ -198,7 +198,7 @@ methods:: ... ] ... ) >>> session.commit() - {opensql}INSERT INTO account_transaction (account_id, description, amount, timestamp) + {execsql}INSERT INTO account_transaction (account_id, description, amount, timestamp) VALUES (?, ?, ?, CURRENT_TIMESTAMP), (?, ?, ?, CURRENT_TIMESTAMP) RETURNING id, timestamp [...] (1, 'paycheck', 2000.0, 1, 'rent', -800.0) @@ -232,7 +232,7 @@ criteria, indicated in the example mapping by the this criteria would be omitted if the parameter were not configured:: >>> print(existing_account.account_transactions.select()) - {opensql}SELECT account_transaction.id, account_transaction.account_id, account_transaction.description, + {printsql}SELECT account_transaction.id, account_transaction.account_id, account_transaction.description, account_transaction.amount, account_transaction.timestamp FROM account_transaction WHERE :param_1 = account_transaction.account_id ORDER BY account_transaction.timestamp @@ -251,7 +251,7 @@ rows:: ... .where(AccountTransaction.amount < 0) ... .limit(10) ... ).all() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT account_transaction.id, account_transaction.account_id, account_transaction.description, account_transaction.amount, account_transaction.timestamp FROM account_transaction @@ -277,7 +277,7 @@ DELETE of that row:: >>> existing_transaction = account_transactions[0] >>> existing_account.account_transactions.remove(existing_transaction) >>> session.commit() - {opensql}DELETE FROM account_transaction WHERE account_transaction.id = ? + {execsql}DELETE FROM account_transaction WHERE account_transaction.id = ? [...] (3,) COMMIT @@ -319,7 +319,7 @@ related collection:: ... {"description": "transaction 4", "amount": Decimal("-300.00")}, ... ], ... ) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) INSERT INTO account_transaction (account_id, description, amount, timestamp) VALUES (?, ?, ?, CURRENT_TIMESTAMP) [...] [(1, 'transaction 1', 47.5), (1, 'transaction 2', -501.25), (1, 'transaction 3', 1800.0), (1, 'transaction 4', -300.0)] <...> @@ -385,7 +385,7 @@ we could just as easily use existing ``AccountTransaction`` objects as well):: ... {"description": "odd trans 3", "amount": Decimal("45.00")}, ... ], ... ).all() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) INSERT INTO account_transaction (account_id, description, amount, timestamp) VALUES (?, ?, ?, CURRENT_TIMESTAMP), (?, ?, ?, CURRENT_TIMESTAMP), (?, ?, ?, CURRENT_TIMESTAMP) RETURNING id, account_id, description, amount, timestamp @@ -400,7 +400,7 @@ at once with a new ``BankAudit`` object:: >>> session.add(bank_audit) >>> bank_audit.account_transactions.add_all(new_transactions) >>> session.commit() - {opensql}INSERT INTO audit DEFAULT VALUES + {execsql}INSERT INTO audit DEFAULT VALUES [...] () INSERT INTO audit_transaction (audit_id, transaction_id) VALUES (?, ?) [...] [(1, 10), (1, 11), (1, 12)] @@ -438,7 +438,7 @@ adding the amount of ``200`` to them:: ... .values(amount=AccountTransaction.amount + 200) ... .where(AccountTransaction.amount == -800), ... ) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) UPDATE account_transaction SET amount=(account_transaction.amount + ?) WHERE ? = account_transaction.account_id AND account_transaction.amount = ? [...] (200, 1, -800) @@ -452,7 +452,7 @@ DELETE statement that is invoked in the same way:: ... AccountTransaction.amount.between(0, 30) ... ), ... ) - {opensql}DELETE FROM account_transaction WHERE ? = account_transaction.account_id + {execsql}DELETE FROM account_transaction WHERE ? = account_transaction.account_id AND account_transaction.amount BETWEEN ? AND ? RETURNING id [...] (1, 0, 30) <...> @@ -484,7 +484,7 @@ many-to-many ``BankAudit.account_transactions`` collection:: ... description=AccountTransaction.description + " (audited)" ... ) ... ) - {opensql}UPDATE account_transaction SET description=(account_transaction.description || ?) + {execsql}UPDATE account_transaction SET description=(account_transaction.description || ?) FROM audit_transaction WHERE ? = audit_transaction.audit_id AND account_transaction.id = audit_transaction.transaction_id RETURNING id [...] (' (audited)', 1) @@ -509,7 +509,7 @@ produce a :term:`scalar subquery`:: ... .values(description=AccountTransaction.description + " (audited)") ... .where(AccountTransaction.id.in_(subq)) ... ) - {opensql}UPDATE account_transaction SET description=(account_transaction.description || ?) + {execsql}UPDATE account_transaction SET description=(account_transaction.description || ?) WHERE account_transaction.id IN (SELECT account_transaction.id FROM audit_transaction WHERE ? = audit_transaction.audit_id AND account_transaction.id = audit_transaction.transaction_id) diff --git a/doc/build/orm/mapped_attributes.rst b/doc/build/orm/mapped_attributes.rst index 4352655dc2..d95406f4a9 100644 --- a/doc/build/orm/mapped_attributes.rst +++ b/doc/build/orm/mapped_attributes.rst @@ -188,7 +188,7 @@ that is, from the ``EmailAddress`` class directly: address = session.scalars( select(EmailAddress).where(EmailAddress.email == "address@example.com") ).one() - {opensql}SELECT address.email AS address_email, address.id AS address_id + {execsql}SELECT address.email AS address_email, address.id AS address_id FROM address WHERE address.email = ? ('address@example.com',) @@ -196,7 +196,7 @@ that is, from the ``EmailAddress`` class directly: address.email = "otheraddress@example.com" session.commit() - {opensql}UPDATE address SET email=? WHERE address.id = ? + {execsql}UPDATE address SET email=? WHERE address.id = ? ('otheraddress@example.com', 1) COMMIT {stop} @@ -246,7 +246,7 @@ attribute, a SQL function is rendered which produces the same effect: address = session.scalars( select(EmailAddress).where(EmailAddress.email == "address") ).one() - {opensql}SELECT address.email AS address_email, address.id AS address_id + {execsql}SELECT address.email AS address_email, address.id AS address_id FROM address WHERE substr(address.email, ?, length(address.email) - ?) = ? (0, 12, 'address') diff --git a/doc/build/orm/queryguide/api.rst b/doc/build/orm/queryguide/api.rst index 559010cf7d..15301cbd00 100644 --- a/doc/build/orm/queryguide/api.rst +++ b/doc/build/orm/queryguide/api.rst @@ -69,7 +69,7 @@ Example use looks like:: >>> stmt = select(User).execution_options(populate_existing=True) >>> result = session.execute(stmt) - {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 ... @@ -132,7 +132,7 @@ to not invoke the "autoflush" step. It is equivalent to using the >>> stmt = select(User).execution_options(autoflush=False) >>> session.execute(stmt) - {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 ... @@ -209,7 +209,7 @@ ORM objects is illustrated below:: >>> stmt = select(User).execution_options(yield_per=10) >>> for user_obj in session.scalars(stmt): ... print(user_obj) - {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(id=1, name='spongebob', fullname='Spongebob Squarepants') @@ -227,7 +227,7 @@ method of :class:`_engine.Result`:: >>> stmt = select(User).execution_options(stream_results=True, max_row_buffer=10) >>> for user_obj in session.scalars(stmt).yield_per(10): ... print(user_obj) - {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(id=1, name='spongebob', fullname='Spongebob Squarepants') @@ -244,7 +244,7 @@ partitions. The size of each partition defaults to the integer value passed to >>> for partition in session.scalars(stmt).partitions(): ... for user_obj in partition: ... print(user_obj) - {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(id=1, name='spongebob', fullname='Spongebob Squarepants') diff --git a/doc/build/orm/queryguide/columns.rst b/doc/build/orm/queryguide/columns.rst index 29edca345a..a57c9f38bd 100644 --- a/doc/build/orm/queryguide/columns.rst +++ b/doc/build/orm/queryguide/columns.rst @@ -63,7 +63,7 @@ columns ``.title``, ``.summary`` and ``.cover_photo``. Using >>> from sqlalchemy.orm import load_only >>> stmt = select(Book).options(load_only(Book.title, Book.summary)) >>> books = session.scalars(stmt).all() - {opensql}SELECT book.id, book.title, book.summary + {execsql}SELECT book.id, book.title, book.summary FROM book [...] () {stop}>>> for book in books: @@ -87,7 +87,7 @@ order to load the value. Below, accessing ``.cover_photo`` emits a SELECT statement to load its value:: >>> img_data = books[0].cover_photo - {opensql}SELECT book.cover_photo AS book_cover_photo + {execsql}SELECT book.cover_photo AS book_cover_photo FROM book WHERE book.id = ? [...] (1,) @@ -119,7 +119,7 @@ statement, all columns for ``user_account`` are present, whereas only >>> stmt = select(User, Book).join_from(User, Book).options(load_only(Book.title)) >>> print(stmt) - {opensql}SELECT user_account.id, user_account.name, user_account.fullname, + {printsql}SELECT user_account.id, user_account.name, user_account.fullname, book.id AS id_1, book.title FROM user_account JOIN book ON user_account.id = book.owner_id @@ -132,7 +132,7 @@ If we wanted to apply :func:`_orm.load_only` options to both ``User`` and ... .options(load_only(User.name), load_only(Book.title)) ... ) >>> print(stmt) - {opensql}SELECT user_account.id, user_account.name, book.id AS id_1, book.title + {printsql}SELECT user_account.id, user_account.name, book.id AS id_1, book.title FROM user_account JOIN book ON user_account.id = book.owner_id .. _orm_queryguide_load_only_related: @@ -154,7 +154,7 @@ in addition to primary key column:: >>> stmt = select(User).options(selectinload(User.books).load_only(Book.title)) >>> for user in session.scalars(stmt): ... print(f"{user.fullname} {[b.title for b in user.books]}") - {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 [...] () SELECT book.owner_id AS book_owner_id, book.id AS book_id, book.title AS book_title @@ -181,7 +181,7 @@ the SELECT statement emitted for each ``User.books`` collection:: >>> stmt = select(User).options(defaultload(User.books).load_only(Book.title)) >>> for user in session.scalars(stmt): ... print(f"{user.fullname} {[b.title for b in user.books]}") - {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 [...] () SELECT book.id AS book_id, book.title AS book_title @@ -189,7 +189,7 @@ the SELECT statement emitted for each ``User.books`` collection:: WHERE ? = book.owner_id [...] (1,) {stop}Spongebob Squarepants ['100 Years of Krabby Patties', 'Sea Catch 22', 'The Sea Grapes of Wrath'] - {opensql}SELECT book.id AS book_id, book.title AS book_title + {execsql}SELECT book.id AS book_id, book.title AS book_title FROM book WHERE ? = book.owner_id [...] (2,) @@ -209,7 +209,7 @@ unchanged:: >>> from sqlalchemy.orm import defer >>> stmt = select(Book).where(Book.owner_id == 2).options(defer(Book.cover_photo)) >>> books = session.scalars(stmt).all() - {opensql}SELECT book.id, book.owner_id, book.title, book.summary + {execsql}SELECT book.id, book.owner_id, book.title, book.summary FROM book WHERE book.owner_id = ? [...] (2,) @@ -223,7 +223,7 @@ As is the case with :func:`_orm.load_only`, unloaded columns by default will load themselves when accessed using :term:`lazy loading`:: >>> img_data = books[0].cover_photo - {opensql}SELECT book.cover_photo AS book_cover_photo + {execsql}SELECT book.cover_photo AS book_cover_photo FROM book WHERE book.id = ? [...] (4,) @@ -267,7 +267,7 @@ access:: >>> book = session.scalar( ... select(Book).options(defer(Book.cover_photo, raiseload=True)).where(Book.id == 4) ... ) - {opensql}SELECT book.id, book.owner_id, book.title, book.summary + {execsql}SELECT book.id, book.owner_id, book.title, book.summary FROM book WHERE book.id = ? [...] (4,) @@ -285,7 +285,7 @@ to all deferred attributes:: >>> book = session.scalar( ... select(Book).options(load_only(Book.title, raiseload=True)).where(Book.id == 5) ... ) - {opensql}SELECT book.id, book.title + {execsql}SELECT book.id, book.title FROM book WHERE book.id = ? [...] (5,) @@ -344,7 +344,7 @@ Using the above mapping, queries against ``Book`` will automatically not include the ``summary`` and ``cover_photo`` columns:: >>> book = session.scalar(select(Book).where(Book.id == 2)) - {opensql}SELECT book.id, book.owner_id, book.title + {execsql}SELECT book.id, book.owner_id, book.title FROM book WHERE book.id = ? [...] (2,) @@ -354,7 +354,7 @@ on the loaded object are first accessed is that they will :term:`lazy load` their value:: >>> img_data = book.cover_photo - {opensql}SELECT book.cover_photo AS book_cover_photo + {execsql}SELECT book.cover_photo AS book_cover_photo FROM book WHERE book.id = ? [...] (2,) @@ -455,7 +455,7 @@ as deferred:: >>> from sqlalchemy.orm import undefer >>> book = session.scalar(select(Book).where(Book.id == 2).options(undefer(Book.summary))) - {opensql}SELECT book.id, book.owner_id, book.title, book.summary + {execsql}SELECT book.id, book.owner_id, book.title, book.summary FROM book WHERE book.id = ? [...] (2,) @@ -505,12 +505,12 @@ Using the above mapping, accessing either ``summary`` or ``cover_photo`` will load both columns at once using just one SELECT statement:: >>> book = session.scalar(select(Book).where(Book.id == 2)) - {opensql}SELECT book.id, book.owner_id, book.title + {execsql}SELECT book.id, book.owner_id, book.title FROM book WHERE book.id = ? [...] (2,) {stop}>>> img_data, summary = book.cover_photo, book.summary - {opensql}SELECT book.summary AS book_summary, book.cover_photo AS book_cover_photo + {execsql}SELECT book.summary AS book_summary, book.cover_photo AS book_cover_photo FROM book WHERE book.id = ? [...] (2,) @@ -528,7 +528,7 @@ option, passing the string name of the group to be eagerly loaded:: >>> book = session.scalar( ... select(Book).where(Book.id == 2).options(undefer_group("book_attrs")) ... ) - {opensql}SELECT book.id, book.owner_id, book.title, book.summary, book.cover_photo + {execsql}SELECT book.id, book.owner_id, book.title, book.summary, book.cover_photo FROM book WHERE book.id = ? [...] (2,) @@ -547,7 +547,7 @@ columns can be undeferred at once, without using a group name, by indicating a wildcard:: >>> book = session.scalar(select(Book).where(Book.id == 3).options(undefer("*"))) - {opensql}SELECT book.id, book.owner_id, book.title, book.summary, book.cover_photo + {execsql}SELECT book.id, book.owner_id, book.title, book.summary, book.cover_photo FROM book WHERE book.id = ? [...] (3,) @@ -586,7 +586,7 @@ Using the above mapping, the ``.summary`` and ``.cover_photo`` columns are by default not loadable:: >>> book = session.scalar(select(Book).where(Book.id == 2)) - {opensql}SELECT book.id, book.owner_id, book.title + {execsql}SELECT book.id, book.owner_id, book.title FROM book WHERE book.id = ? [...] (2,) @@ -607,7 +607,7 @@ Only by overridding their behavior at query time, typically using ... .options(undefer("*")) ... .execution_options(populate_existing=True) ... ) - {opensql}SELECT book.id, book.owner_id, book.title, book.summary, book.cover_photo + {execsql}SELECT book.id, book.owner_id, book.title, book.summary, book.cover_photo FROM book WHERE book.id = ? [...] (2,) @@ -660,7 +660,7 @@ entries, one for ``User`` and one for ``func.count(Book.id)``:: >>> stmt = select(User, func.count(Book.id)).join_from(User, Book).group_by(Book.owner_id) >>> for user, book_count in session.execute(stmt): ... print(f"Username: {user.name} Number of books: {book_count}") - {opensql}SELECT user_account.id, user_account.name, user_account.fullname, + {execsql}SELECT user_account.id, user_account.name, user_account.fullname, count(book.id) AS count_1 FROM user_account JOIN book ON user_account.id = book.owner_id GROUP BY book.owner_id @@ -729,7 +729,7 @@ to each ``User`` object as it's loaded:: ... ) >>> for user in session.scalars(stmt): ... print(f"Username: {user.name} Number of books: {user.book_count}") - {opensql}SELECT count(book.id) AS count_1, user_account.id, user_account.name, + {execsql}SELECT count(book.id) AS count_1, user_account.id, user_account.name, user_account.fullname FROM user_account JOIN book ON user_account.id = book.owner_id GROUP BY book.owner_id diff --git a/doc/build/orm/queryguide/dml.rst b/doc/build/orm/queryguide/dml.rst index 97aee3fea3..606b7ccefc 100644 --- a/doc/build/orm/queryguide/dml.rst +++ b/doc/build/orm/queryguide/dml.rst @@ -67,7 +67,7 @@ as much as possible for many rows:: ... {"name": "ehkrabs", "fullname": "Eugene H. Krabs"}, ... ], ... ) - {opensql}INSERT INTO user_account (name, fullname) VALUES (?, ?) + {execsql}INSERT INTO user_account (name, fullname) VALUES (?, ?) [...] [('spongebob', 'Spongebob Squarepants'), ('sandy', 'Sandy Cheeks'), ('patrick', 'Patrick Star'), ('squidward', 'Squidward Tentacles'), ('ehkrabs', 'Eugene H. Krabs')] {stop}<...> @@ -126,7 +126,7 @@ iteration of ``User`` objects:: ... {"name": "ehkrabs", "fullname": "Eugene H. Krabs"}, ... ], ... ) - {opensql}INSERT INTO user_account (name, fullname) + {execsql}INSERT INTO user_account (name, fullname) VALUES (?, ?), (?, ?), (?, ?), (?, ?), (?, ?) RETURNING id, name, fullname, species [... (insertmanyvalues)] ('spongebob', 'Spongebob Squarepants', 'sandy', 'Sandy Cheeks', 'patrick', 'Patrick Star', 'squidward', 'Squidward Tentacles', @@ -190,7 +190,7 @@ to each set of keys and batch accordingly into separate INSERT statements:: ... {"name": "ehkrabs", "fullname": "Eugene H. Krabs", "species": "Crab"}, ... ], ... ) - {opensql}INSERT INTO user_account (name, fullname, species) VALUES (?, ?, ?), (?, ?, ?) RETURNING id, name, fullname, species + {execsql}INSERT INTO user_account (name, fullname, species) VALUES (?, ?, ?), (?, ?, ?) RETURNING id, name, fullname, species [... (insertmanyvalues)] ('spongebob', 'Spongebob Squarepants', 'Sea Sponge', 'sandy', 'Sandy Cheeks', 'Squirrel') INSERT INTO user_account (name, species) VALUES (?, ?) RETURNING id, name, fullname, species [...] ('patrick', 'Starfish') @@ -232,7 +232,7 @@ the returned rows include values for all columns inserted:: ... {"name": "ehkrabs", "manager_name": "Eugene H. Krabs"}, ... ], ... ) - {opensql}INSERT INTO employee (name, type) VALUES (?, ?), (?, ?) RETURNING id, name, type + {execsql}INSERT INTO employee (name, type) VALUES (?, ?), (?, ?) RETURNING id, name, type [... (insertmanyvalues)] ('sandy', 'manager', 'ehkrabs', 'manager') INSERT INTO manager (id, manager_name) VALUES (?, ?), (?, ?) RETURNING id, manager_name [... (insertmanyvalues)] (1, 'Sandy Cheeks', 2, 'Eugene H. Krabs') @@ -280,7 +280,7 @@ and then pass the additional records using "bulk" mode:: ... {"message": "log message #4"}, ... ], ... ) - {opensql}INSERT INTO log_record (message, code, timestamp) + {execsql}INSERT INTO log_record (message, code, timestamp) VALUES (?, ?, CURRENT_TIMESTAMP), (?, ?, CURRENT_TIMESTAMP), (?, ?, CURRENT_TIMESTAMP), (?, ?, CURRENT_TIMESTAMP) RETURNING id, message, code, timestamp @@ -359,7 +359,7 @@ and also demonstrates :meth:`_dml.Insert.returning` in this form, is below:: ... ) ... .returning(Address), ... ) - {opensql}INSERT INTO address (user_id, email_address) VALUES + {execsql}INSERT INTO address (user_id, email_address) VALUES ((SELECT user_account.id FROM user_account WHERE user_account.name = ?), ?), ((SELECT user_account.id @@ -498,7 +498,7 @@ as ORM mapped attribute keys, rather than column names: ... index_elements=[User.name], set_=dict(fullname=stmt.excluded.fullname) ... ) >>> session.execute(stmt) - {opensql}INSERT INTO user_account (name, fullname) + {execsql}INSERT INTO user_account (name, fullname) VALUES (?, ?), (?, ?), (?, ?), (?, ?), (?, ?) ON CONFLICT (name) DO UPDATE SET fullname = excluded.fullname [...] ('spongebob', 'Spongebob Squarepants', 'sandy', 'Sandy Cheeks', @@ -521,7 +521,7 @@ example in the previous section:: >>> result = session.scalars( ... stmt.returning(User), execution_options={"populate_existing": True} ... ) - {opensql}INSERT INTO user_account (name, fullname) + {execsql}INSERT INTO user_account (name, fullname) VALUES (?, ?), (?, ?), (?, ?), (?, ?), (?, ?) ON CONFLICT (name) DO UPDATE SET fullname = excluded.fullname RETURNING id, name, fullname, species @@ -605,7 +605,7 @@ appropriate WHERE criteria to match each row by primary key, and using ... {"id": 5, "fullname": "Eugene H. Krabs"}, ... ], ... ) - {opensql}UPDATE user_account SET fullname=? WHERE user_account.id = ? + {execsql}UPDATE user_account SET fullname=? WHERE user_account.id = ? [...] [('Spongebob Squarepants', 1), ('Patrick Star', 3), ('Eugene H. Krabs', 5)] {stop}<...> @@ -671,7 +671,7 @@ Example:: ... }, ... ], ... ) - {opensql}UPDATE employee SET name=? WHERE employee.id = ? + {execsql}UPDATE employee SET name=? WHERE employee.id = ? [...] [('scheeks', 1), ('eugene', 2)] UPDATE manager SET manager_name=? WHERE manager.id = ? [...] [('Sandy Cheeks, President', 1), ('Eugene H. Krabs, VP Marketing', 2)] @@ -753,7 +753,7 @@ field of multiple rows ... .values(fullname="Name starts with S") ... ) >>> session.execute(stmt) - {opensql}UPDATE user_account SET fullname=? WHERE user_account.name IN (?, ?) + {execsql}UPDATE user_account SET fullname=? WHERE user_account.name IN (?, ?) [...] ('Name starts with S', 'squidward', 'sandy') {stop}<...> @@ -763,7 +763,7 @@ For a DELETE, an example of deleting rows based on criteria:: >>> from sqlalchemy import delete >>> stmt = delete(User).where(User.name.in_(["squidward", "sandy"])) >>> session.execute(stmt) - {opensql}DELETE FROM user_account WHERE user_account.name IN (?, ?) + {execsql}DELETE FROM user_account WHERE user_account.name IN (?, ?) [...] ('squidward', 'sandy') {stop}<...> @@ -799,7 +799,7 @@ which is passed as an string ORM execution option, typically by using the ... update(User).where(User.name == "squidward").values(fullname="Squidward Tentacles") ... ) >>> session.execute(stmt, execution_options={"synchronize_session": False}) - {opensql}UPDATE user_account SET fullname=? WHERE user_account.name = ? + {execsql}UPDATE user_account SET fullname=? WHERE user_account.name = ? [...] ('Squidward Tentacles', 'squidward') {stop}<...> @@ -814,7 +814,7 @@ The execution option may also be bundled with the statement itself using the ... .execution_options(synchronize_session=False) ... ) >>> session.execute(stmt) - {opensql}UPDATE user_account SET fullname=? WHERE user_account.name = ? + {execsql}UPDATE user_account SET fullname=? WHERE user_account.name = ? [...] ('Squidward Tentacles', 'squidward') {stop}<...> @@ -893,7 +893,7 @@ and/or columns may be indicated for RETURNING:: ... .returning(User) ... ) >>> result = session.scalars(stmt) - {opensql}UPDATE user_account SET fullname=? WHERE user_account.name = ? + {execsql}UPDATE user_account SET fullname=? WHERE user_account.name = ? RETURNING id, name, fullname, species [...] ('Squidward Tentacles', 'squidward') {stop}>>> print(result.all()) @@ -945,7 +945,7 @@ that are local to the subclass table, as in the example below:: ... .values(manager_name="Sandy Cheeks, President") ... ) >>> session.execute(stmt) - UPDATE manager SET manager_name=? WHERE manager.id = ? + {execsql}UPDATE manager SET manager_name=? WHERE manager.id = ? [...] ('Sandy Cheeks, President', 1) <...> @@ -961,7 +961,7 @@ to locate rows which will work on any SQL backend is so use a subquery:: ... .values(manager_name="Sandy Cheeks, President") ... ) >>> session.execute(stmt) - {opensql}UPDATE manager SET manager_name=? WHERE manager.id = (SELECT employee.id + {execsql}UPDATE manager SET manager_name=? WHERE manager.id = (SELECT employee.id FROM employee WHERE employee.name = ?) RETURNING id [...] ('Sandy Cheeks, President', 'sandy') @@ -977,7 +977,7 @@ tables must be stated explicitly in some way:: ... .values(manager_name="Sandy Cheeks, President") ... ) >>> session.execute(stmt) - {opensql}UPDATE manager SET manager_name=? FROM employee + {execsql}UPDATE manager SET manager_name=? FROM employee WHERE manager.id = employee.id AND employee.name = ? [...] ('Sandy Cheeks, President', 'sandy') {stop}<...> @@ -990,11 +990,11 @@ table individually:: >>> from sqlalchemy import delete >>> session.execute(delete(Manager).where(Manager.id == 1)) - {opensql}DELETE FROM manager WHERE manager.id = ? + {execsql}DELETE FROM manager WHERE manager.id = ? [...] (1,) {stop}<...> >>> session.execute(delete(Employee).where(Employee.id == 1)) - {opensql}DELETE FROM employee WHERE employee.id = ? + {execsql}DELETE FROM employee WHERE employee.id = ? [...] (1,) {stop}<...> diff --git a/doc/build/orm/queryguide/inheritance.rst b/doc/build/orm/queryguide/inheritance.rst index 7905dde2e2..7040128f40 100644 --- a/doc/build/orm/queryguide/inheritance.rst +++ b/doc/build/orm/queryguide/inheritance.rst @@ -41,7 +41,7 @@ objects of type ``Manager``:: >>> from sqlalchemy import select >>> stmt = select(Manager).order_by(Manager.id) >>> managers = session.scalars(stmt).all() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT manager.id, employee.id AS id_1, employee.name, employee.type, employee.company_id, manager.manager_name FROM employee JOIN manager ON employee.id = manager.id ORDER BY manager.id [...] () @@ -72,7 +72,7 @@ and ``Employee``, may be within the result set:: >>> from sqlalchemy import select >>> stmt = select(Employee).order_by(Employee.id) >>> objects = session.scalars(stmt).all() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT employee.id, employee.name, employee.type, employee.company_id FROM employee ORDER BY employee.id [...] () @@ -89,7 +89,7 @@ accessed using :term:`lazy loading`:: >>> mr_krabs = objects[0] >>> print(mr_krabs.manager_name) - {opensql}SELECT manager.manager_name AS manager_manager_name + {execsql}SELECT manager.manager_name AS manager_manager_name FROM manager WHERE ? = manager.id [...] (1,) @@ -144,7 +144,7 @@ load columns local to both the ``Manager`` and ``Engineer`` subclasses:: >>> loader_opt = selectin_polymorphic(Employee, [Manager, Engineer]) >>> stmt = select(Employee).order_by(Employee.id).options(loader_opt) >>> objects = session.scalars(stmt).all() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT employee.id, employee.name, employee.type, employee.company_id FROM employee ORDER BY employee.id [...] () @@ -209,8 +209,8 @@ this collection on all ``Manager`` objects, where the sub-attributes of ... selectinload(Manager.paperwork), ... ) ... ) - {opensql}>>> objects = session.scalars(stmt).all() - BEGIN (implicit) + >>> objects = session.scalars(stmt).all() + {execsql}BEGIN (implicit) SELECT employee.id, employee.name, employee.type, employee.company_id FROM employee ORDER BY employee.id [...] () @@ -254,7 +254,7 @@ we only indicate the additional target subclasses we wish to load:: >>> for company in session.scalars(stmt): ... print(f"company: {company.name}") ... print(f"employees: {company.employees}") - {opensql}SELECT company.id, company.name + {execsql}SELECT company.id, company.name FROM company [...] () SELECT employee.company_id AS employee_company_id, employee.id AS employee_id, @@ -370,7 +370,7 @@ section, to load all columns for ``Manager`` and ``Engineer`` at once:: >>> stmt = select(employee_poly).order_by(employee_poly.id) >>> objects = session.scalars(stmt).all() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT employee.id, employee.name, employee.type, employee.company_id, manager.id AS id_1, manager.manager_name, engineer.id AS id_2, engineer.engineer_info FROM employee @@ -419,7 +419,7 @@ construct to create criteria against both classes at once:: ... .order_by(employee_poly.id) ... ) >>> objects = session.scalars(stmt).all() - {opensql}SELECT employee.id, employee.name, employee.type, employee.company_id, manager.id AS id_1, + {execsql}SELECT employee.id, employee.name, employee.type, employee.company_id, manager.id AS id_1, manager.manager_name, engineer.id AS id_2, engineer.engineer_info FROM employee LEFT OUTER JOIN manager ON employee.id = manager.id @@ -480,7 +480,7 @@ column along with some additional limiting criteria against the ... ) >>> for manager, engineer in session.execute(stmt): ... print(f"{manager} {engineer}") - {opensql}SELECT + {execsql}SELECT employee_1.id, employee_1.name, employee_1.type, employee_1.company_id, manager_1.id AS id_1, manager_1.manager_name, employee_2.id AS id_2, employee_2.name AS name_1, employee_2.type AS type_1, @@ -523,7 +523,7 @@ subquery, producing a more verbose form:: ... .order_by(engineer_employee.name, manager_employee.name) ... ) >>> print(stmt) - {opensql}SELECT anon_1.employee_id, anon_1.employee_name, anon_1.employee_type, + {printsql}SELECT anon_1.employee_id, anon_1.employee_name, anon_1.employee_type, anon_1.employee_company_id, anon_1.manager_id, anon_1.manager_manager_name, anon_2.employee_id AS employee_id_1, anon_2.employee_name AS employee_name_1, anon_2.employee_type AS employee_type_1, anon_2.employee_company_id AS employee_company_id_1, anon_2.engineer_id, anon_2.engineer_engineer_info @@ -613,7 +613,7 @@ automatically assume the use of when the statement is emitted:: print(select(Employee)) - {opensql}SELECT employee.id, employee.name, employee.type, engineer.id AS id_1, + {printsql}SELECT employee.id, employee.name, employee.type, engineer.id AS id_1, engineer.engineer_info, manager.id AS id_2, manager.manager_name FROM employee LEFT OUTER JOIN engineer ON employee.id = engineer.id @@ -630,7 +630,7 @@ entity:: or_(Manager.manager_name == "x", Engineer.engineer_info == "y") ) ) - {opensql}SELECT employee.id, employee.name, employee.type, engineer.id AS id_1, + {printsql}SELECT employee.id, employee.name, employee.type, engineer.id AS id_1, engineer.engineer_info, manager.id AS id_2, manager.manager_name FROM employee LEFT OUTER JOIN engineer ON employee.id = engineer.id @@ -724,7 +724,7 @@ using a :func:`_orm.with_polymorphic` entity as the target:: ... ) >>> for company_name, emp_name in session.execute(stmt): ... print(f"{company_name} {emp_name}") - {opensql}SELECT company.name, employee.name AS name_1 + {execsql}SELECT company.name, employee.name AS name_1 FROM company JOIN (employee LEFT OUTER JOIN engineer ON employee.id = engineer.id) ON company.id = employee.company_id WHERE employee.name = ? OR engineer.engineer_info = ? [...] ('SpongeBob', 'Senior Customer Engagement Engineer') @@ -748,7 +748,7 @@ query could be written strictly in terms of ``Engineer`` targets as follows:: ... ) >>> for company_name, emp_name in session.execute(stmt): ... print(f"{company_name} {emp_name}") - {opensql}SELECT company.name, employee.name AS name_1 + {execsql}SELECT company.name, employee.name AS name_1 FROM company JOIN (employee JOIN engineer ON employee.id = engineer.id) ON company.id = employee.company_id WHERE employee.name = ? OR engineer.engineer_info = ? [...] ('SpongeBob', 'Senior Customer Engagement Engineer') @@ -780,7 +780,7 @@ eagerly load all elements of ``Company.employees`` using the >>> for company in session.scalars(stmt): ... print(f"company: {company.name}") ... print(f"employees: {company.employees}") - {opensql}SELECT company.id, company.name + {execsql}SELECT company.id, company.name FROM company [...] () SELECT employee.company_id AS employee_company_id, employee.id AS employee_id, @@ -842,7 +842,7 @@ As an example, a query for the single-inheritance example mapping of >>> stmt = select(Employee).order_by(Employee.id) >>> for obj in session.scalars(stmt): ... print(f"{obj}") - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT employee.id, employee.name, employee.type FROM employee ORDER BY employee.id [...] () @@ -856,7 +856,7 @@ the ``Engineer`` entity is performed:: >>> stmt = select(Engineer).order_by(Engineer.id) >>> objects = session.scalars(stmt).all() - {opensql}SELECT employee.id, employee.name, employee.type, employee.engineer_info + {execsql}SELECT employee.id, employee.name, employee.type, employee.engineer_info FROM employee WHERE employee.type IN (?) ORDER BY employee.id [...] ('engineer',) @@ -884,13 +884,13 @@ attribute is not present by default, and an additional SELECT is emitted when it's accessed:: >>> mr_krabs = session.scalars(select(Employee).where(Employee.name == "Mr. Krabs")).one() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT employee.id, employee.name, employee.type FROM employee WHERE employee.name = ? [...] ('Mr. Krabs',) {stop}>>> mr_krabs.manager_name - {opensql}SELECT employee.manager_name AS employee_manager_name + {execsql}SELECT employee.manager_name AS employee_manager_name FROM employee WHERE employee.id = ? AND employee.type IN (?) [...] (1, 'manager') @@ -911,7 +911,7 @@ efficient for single-inheritance mappers:: >>> employees = with_polymorphic(Employee, "*") >>> stmt = select(employees).order_by(employees.id) >>> objects = session.scalars(stmt).all() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT employee.id, employee.name, employee.type, employee.manager_name, employee.engineer_info FROM employee ORDER BY employee.id @@ -966,15 +966,10 @@ their columns included in SELECT statements against the ``Employee`` entity automatically:: >>> print(select(Employee)) - {opensql}SELECT employee.id, employee.name, employee.type, + {printsql}SELECT employee.id, employee.name, employee.type, employee.manager_name, employee.engineer_info FROM employee - - - - - Inheritance Loading API ----------------------- diff --git a/doc/build/orm/queryguide/relationships.rst b/doc/build/orm/queryguide/relationships.rst index fff79b3180..593fe995b2 100644 --- a/doc/build/orm/queryguide/relationships.rst +++ b/doc/build/orm/queryguide/relationships.rst @@ -321,7 +321,7 @@ in order to load the related object or objects: .. sourcecode:: pycon+sql >>> spongebob.addresses - {opensql}SELECT + {execsql}SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id @@ -449,7 +449,7 @@ using the :func:`_orm.joinedload` loader option: >>> from sqlalchemy.orm import joinedload >>> stmt = select(User).options(joinedload(User.addresses)).filter_by(name="spongebob") >>> spongebob = session.scalars(stmt).unique().all() - {opensql}SELECT + {execsql}SELECT addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address, addresses_1.user_id AS addresses_1_user_id, @@ -508,7 +508,7 @@ an OUTER JOIN: ... joinedload(User.addresses).joinedload(Address.widgets, innerjoin=True) ... ) >>> results = session.scalars(stmt).unique().all() - {opensql}SELECT + {execsql}SELECT widgets_1.id AS widgets_1_id, widgets_1.name AS widgets_1_name, addresses_1.id AS addresses_1_id, @@ -580,7 +580,7 @@ named in the query: ... .order_by(Address.email_address) ... ) >>> result = session.scalars(stmt).unique().all() - {opensql}SELECT + {execsql}SELECT addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address, addresses_1.user_id AS addresses_1_user_id, @@ -609,7 +609,7 @@ address is to use :meth:`_sql.Select.join`: ... .order_by(Address.email_address) ... ) >>> result = session.scalars(stmt).unique().all() - {opensql} + {execsql} SELECT users.id AS users_id, users.name AS users_name, @@ -638,7 +638,7 @@ are ordering on, the other is used anonymously to load the contents of the ... .order_by(Address.email_address) ... ) >>> result = session.scalars(stmt).unique().all() - {opensql}SELECT + {execsql}SELECT addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address, addresses_1.user_id AS addresses_1_user_id, @@ -673,7 +673,7 @@ to see why :func:`joinedload` does what it does, consider if we were ... .filter(Address.email_address == "someaddress@foo.com") ... ) >>> result = session.scalars(stmt).unique().all() - {opensql}SELECT + {execsql}SELECT addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address, addresses_1.user_id AS addresses_1_user_id, @@ -709,7 +709,7 @@ into :func:`.selectinload`: ... .filter(Address.email_address == "someaddress@foo.com") ... ) >>> result = session.scalars(stmt).all() - {opensql}SELECT + {execsql}SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -765,7 +765,7 @@ order to load related associations: ... .filter(or_(User.name == "spongebob", User.name == "ed")) ... ) >>> result = session.scalars(stmt).all() - {opensql}SELECT + {execsql}SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -799,7 +799,7 @@ value from the parent object is used: >>> from sqlalchemy import selectinload >>> stmt = select(Address).options(selectinload(Address.user)) >>> result = session.scalars(stmt).all() - {opensql}SELECT + {execsql}SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id @@ -882,7 +882,7 @@ the collection members to load them at once: >>> from sqlalchemy.orm import subqueryload >>> stmt = select(User).options(subqueryload(User.addresses)).filter_by(name="spongebob") >>> results = session.scalars(stmt).all() - {opensql}SELECT + {execsql}SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, @@ -1104,7 +1104,7 @@ the specific :func:`_orm.aliased` construct to be passed: # get results normally r = session.scalars(stmt).unique().all() - {opensql}SELECT + {execsql}SELECT users.user_id AS users_user_id, users.user_name AS users_user_name, adalias.address_id AS adalias_address_id, diff --git a/doc/build/orm/queryguide/select.rst b/doc/build/orm/queryguide/select.rst index 55c3ae94ea..579a3f8427 100644 --- a/doc/build/orm/queryguide/select.rst +++ b/doc/build/orm/queryguide/select.rst @@ -31,7 +31,7 @@ the ORM to get rows back, the object is passed to returned:: >>> result = session.execute(stmt) - {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 = ? [...] ('spongebob',){stop} @@ -66,7 +66,7 @@ Below we select from the ``User`` entity, producing a :class:`_sql.Select` that selects from the mapped :class:`_schema.Table` to which ``User`` is mapped:: >>> result = session.execute(select(User).order_by(User.id)) - {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 [...] () @@ -92,7 +92,7 @@ receive ORM entities directly. This is most easily achieved by using the which yields single elements rather than rows is returned:: >>> session.scalars(select(User).order_by(User.id)).all() - {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 [...] () {stop}[User(id=1, name='spongebob', fullname='Spongebob Squarepants'), @@ -122,7 +122,7 @@ refer to them under the names ``User`` and ``Address``:: >>> stmt = select(User, Address).join(User.addresses).order_by(User.id, Address.id) >>> for row in session.execute(stmt): ... print(f"{row.User.name} {row.Address.email_address}") - {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.user_id, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id ORDER BY user_account.id, address.id @@ -146,7 +146,7 @@ parameter to alias them with an explicit name:: ... .order_by(user_cls.id, email_cls.id) ... ) >>> row = session.execute(stmt).first() - {opensql}SELECT user_cls.id, user_cls.name, user_cls.fullname, + {execsql}SELECT user_cls.id, user_cls.name, user_cls.fullname, email.id AS id_1, email.user_id, email.email_address FROM user_account AS user_cls JOIN address AS email ON user_cls.id = email.user_id ORDER BY user_cls.id, email.id @@ -166,7 +166,7 @@ above using this form as well:: ... select(User).join(User.addresses).add_columns(Address).order_by(User.id, Address.id) ... ) >>> print(stmt) - {opensql}SELECT user_account.id, user_account.name, user_account.fullname, + {printsql}SELECT user_account.id, user_account.name, user_account.fullname, address.id AS id_1, address.user_id, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id ORDER BY user_account.id, address.id @@ -186,7 +186,7 @@ as table columns are used:: ... .join(User.addresses) ... .order_by(User.id, Address.id) ... ) - {opensql}SELECT user_account.name, address.email_address + {execsql}SELECT user_account.name, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id ORDER BY user_account.id, address.id [...] (){stop} @@ -220,7 +220,7 @@ allows sets of column expressions to be grouped in result rows:: ... ).join_from(User, Address) >>> for row in session.execute(stmt): ... print(f"{row.user.name} {row.user.fullname} {row.email.email_address}") - {opensql}SELECT user_account.name, user_account.fullname, address.email_address + {execsql}SELECT user_account.name, user_account.fullname, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id [...] (){stop} spongebob Spongebob Squarepants spongebob@sqlalchemy.org @@ -253,7 +253,7 @@ construct against a mapped class:: >>> from sqlalchemy.orm import aliased >>> u1 = aliased(User) >>> print(select(u1).order_by(u1.id)) - {opensql}SELECT user_account_1.id, user_account_1.name, user_account_1.fullname + {printsql}SELECT user_account_1.id, user_account_1.name, user_account_1.fullname FROM user_account AS user_account_1 ORDER BY user_account_1.id As is the case when using :meth:`_schema.Table.alias`, the SQL alias @@ -265,7 +265,7 @@ passed as well:: >>> u1 = aliased(User, name="u1") >>> stmt = select(u1).order_by(u1.id) >>> row = session.execute(stmt).first() - {opensql}SELECT u1.id, u1.name, u1.fullname + {execsql}SELECT u1.id, u1.name, u1.fullname FROM user_account AS u1 ORDER BY u1.id [...] (){stop} >>> print(f"{row.u1.name}") @@ -324,7 +324,7 @@ method:: >>> orm_sql = select(User).from_statement(textual_sql) >>> for user_obj in session.execute(orm_sql).scalars(): ... print(user_obj) - {opensql}SELECT id, name, fullname FROM user_account ORDER BY id + {execsql}SELECT id, name, fullname FROM user_account ORDER BY id [...] (){stop} User(id=1, name='spongebob', fullname='Spongebob Squarepants') User(id=2, name='sandy', fullname='Sandy Cheeks') @@ -341,7 +341,7 @@ construct, in a similar manner as discussed below in :ref:`orm_queryguide_subque >>> stmt = select(orm_subquery) >>> for user_obj in session.execute(stmt).scalars(): ... print(user_obj) - {opensql}SELECT anon_1.id, anon_1.name, anon_1.fullname + {execsql}SELECT anon_1.id, anon_1.name, anon_1.fullname FROM (SELECT id, name, fullname FROM user_account ORDER BY id) AS anon_1 [...] (){stop} User(id=1, name='spongebob', fullname='Spongebob Squarepants') @@ -375,7 +375,7 @@ derived from those entities, such as in the example below:: >>> stmt = select(aliased_user) >>> for user_obj in session.execute(stmt).scalars(): ... print(user_obj) - {opensql} SELECT anon_1.id, anon_1.name, anon_1.fullname + {execsql} 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 WHERE user_account.id < ? ORDER BY user_account.id) AS anon_1 @@ -415,7 +415,7 @@ is used:: >>> stmt = select(User).from_statement(u) >>> for user_obj in session.execute(stmt).scalars(): ... print(user_obj) - {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.id < ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname FROM user_account @@ -441,7 +441,7 @@ and order by criteria based on its exported columns:: >>> stmt = select(user_alias).order_by(user_alias.id) >>> for user_obj in session.execute(stmt).scalars(): ... print(user_obj) - {opensql}SELECT anon_1.id, anon_1.name, anon_1.fullname + {execsql}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 WHERE user_account.id < ? UNION ALL SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname @@ -491,7 +491,7 @@ Where above, the call to :meth:`_sql.Select.join` along ``User.addresses`` will result in SQL approximately equivalent to:: >>> 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 JOIN address ON user_account.id = address.user_id In the above example we refer to ``User.addresses`` as passed to @@ -527,7 +527,7 @@ JOIN elements in the resulting SQL:: >>> stmt = select(User).join(User.orders).join(Order.items) >>> 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 JOIN user_order ON user_account.id = user_order.user_id JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id @@ -551,7 +551,7 @@ on the ``User.addresses`` relationship to our chain of joins:: >>> stmt = select(User).join(User.orders).join(Order.items).join(User.addresses) >>> 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 JOIN user_order ON user_account.id = user_order.user_id JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id @@ -569,7 +569,7 @@ key relationship between two entities:: >>> stmt = select(User).join(Address) >>> 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 JOIN address ON user_account.id = address.user_id In the above calling form, :meth:`_sql.Select.join` is called upon to infer @@ -597,7 +597,7 @@ a SQL expression as the ON clause is as follows:: >>> stmt = select(User).join(Address, User.id == Address.user_id) >>> 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 JOIN address ON user_account.id = address.user_id The expression-based ON clause may also be a :func:`_orm.relationship`-bound @@ -606,7 +606,7 @@ attribute, in the same way it's used in >>> stmt = select(User).join(Address, User.addresses) >>> 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 JOIN address ON user_account.id = address.user_id The above example seems redundant in that it indicates the target of ``Address`` @@ -638,7 +638,7 @@ email addresses: ... User.addresses.and_(Address.email_address == "squirrel@squirrelpower.org") ... ) >>> session.execute(stmt).all() - {opensql}SELECT user_account.fullname + {execsql}SELECT user_account.fullname FROM user_account JOIN address ON user_account.id = address.user_id AND address.email_address = ? [...] ('squirrel@squirrelpower.org',){stop} @@ -676,7 +676,7 @@ against the ``Address`` entity:: ... .where(address_alias_2.email_address == "patrick@gmail.com") ... ) >>> 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 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 @@ -697,7 +697,7 @@ to produce the same SQL statement as the one just illustrated:: ... .join(User.addresses.of_type(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 @@ -711,7 +711,7 @@ construct directly:: >>> user_alias_1 = aliased(User) >>> print(select(user_alias_1.name).join(user_alias_1.addresses)) - {opensql}SELECT user_account_1.name + {printsql}SELECT user_account_1.name FROM user_account AS user_account_1 JOIN address ON user_account_1.id = address.user_id @@ -735,7 +735,7 @@ target of the :meth:`_sql.Select.join` method:: >>> subq = select(Address).where(Address.email_address == "pat999@aol.com").subquery() >>> stmt = select(User).join(subq, User.id == subq.c.user_id) >>> 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 JOIN (SELECT address.id AS id, address.user_id AS user_id, address.email_address AS email_address @@ -755,7 +755,7 @@ so that we can refer to it by name in the result row:: >>> stmt = select(User, address_subq).join(address_subq) >>> for row in session.execute(stmt): ... print(f"{row.User} {row.address}") - {opensql}SELECT user_account.id, user_account.name, user_account.fullname, + {execsql}SELECT user_account.id, user_account.name, user_account.fullname, anon_1.id AS id_1, anon_1.user_id, anon_1.email_address FROM user_account JOIN (SELECT address.id AS id, @@ -782,7 +782,7 @@ of the join:: >>> stmt = select(User, address_subq).join(User.addresses.of_type(address_subq)) >>> for row in session.execute(stmt): ... print(f"{row.User} {row.address}") - {opensql}SELECT user_account.id, user_account.name, user_account.fullname, + {execsql}SELECT user_account.id, user_account.name, user_account.fullname, anon_1.id AS id_1, anon_1.user_id, anon_1.email_address FROM user_account JOIN (SELECT address.id AS id, @@ -825,7 +825,7 @@ subquery once, but in a result-row context can return objects of both >>> stmt = select(user_alias, address_alias).where(user_alias.name == "sandy") >>> for row in session.execute(stmt): ... print(f"{row.user} {row.address}") - {opensql}SELECT anon_1.id, anon_1.name, anon_1.fullname, anon_1.id_1, anon_1.email_address + {execsql}SELECT anon_1.id, anon_1.name, anon_1.fullname, anon_1.id_1, anon_1.email_address FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname, address.id AS id_1, address.email_address AS email_address @@ -847,7 +847,7 @@ the :meth:`_sql.Select.join_from` method may be used:: >>> stmt = select(Address).join_from(User, User.addresses).where(User.name == "sandy") >>> print(stmt) - SELECT address.id, address.user_id, address.email_address + {printsql}SELECT address.id, address.user_id, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id WHERE user_account.name = :name_1 @@ -857,7 +857,7 @@ in the form ``(, )``, or ``(, , >>> stmt = select(Address).join_from(User, Address).where(User.name == "sandy") >>> print(stmt) - SELECT address.id, address.user_id, address.email_address + {printsql}SELECT address.id, address.user_id, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id WHERE user_account.name = :name_1 @@ -868,7 +868,7 @@ be used:: >>> stmt = select(Address).select_from(User).join(Address).where(User.name == "sandy") >>> print(stmt) - SELECT address.id, address.user_id, address.email_address + {printsql}SELECT address.id, address.user_id, address.email_address FROM user_account JOIN address ON user_account.id = address.user_id WHERE user_account.name = :name_1 @@ -885,7 +885,7 @@ be used:: >>> stmt = select(Address).select_from(User).join(Address.user).where(User.name == "sandy") >>> print(stmt) - SELECT address.id, address.user_id, address.email_address + {printsql}SELECT address.id, address.user_id, address.email_address FROM address JOIN user_account ON user_account.id = address.user_id WHERE user_account.name = :name_1 @@ -907,7 +907,7 @@ be used:: ... .where(user_table.c.name == "sandy") ... ) >>> print(stmt) - SELECT address.id, address.user_id, address.email_address + {printsql}SELECT address.id, address.user_id, address.email_address FROM address JOIN user_account ON user_account.id = address.user_id WHERE user_account.name = :name_1 @@ -954,7 +954,7 @@ an optional WHERE criteria to limit the rows matched by the subquery: ... User.addresses.any(Address.email_address == "squirrel@squirrelpower.org") ... ) >>> session.execute(stmt).all() - {opensql}SELECT user_account.fullname + {execsql}SELECT user_account.fullname FROM user_account WHERE EXISTS (SELECT 1 FROM address @@ -971,7 +971,7 @@ for ``User`` entities that have no related ``Address`` rows: >>> stmt = select(User.fullname).where(~User.addresses.any()) >>> session.execute(stmt).all() - {opensql}SELECT user_account.fullname + {execsql}SELECT user_account.fullname FROM user_account WHERE NOT (EXISTS (SELECT 1 FROM address @@ -988,7 +988,7 @@ which belonged to "sandy": >>> stmt = select(Address.email_address).where(Address.user.has(User.name == "sandy")) >>> session.execute(stmt).all() - {opensql}SELECT address.email_address + {execsql}SELECT address.email_address FROM address WHERE EXISTS (SELECT 1 FROM user_account @@ -1018,9 +1018,9 @@ in terms of the target :func:`_orm.relationship`. object given:: >>> user_obj = session.get(User, 1) - SELECT ... + {execsql}SELECT ... >>> print(select(Address).where(Address.user == user_obj)) - {opensql}SELECT address.id, address.user_id, address.email_address + {printsql}SELECT address.id, address.user_id, address.email_address FROM address WHERE :param_1 = address.user_id @@ -1030,7 +1030,7 @@ in terms of the target :func:`_orm.relationship`. be used:: >>> print(select(Address).where(Address.user != user_obj)) - {opensql}SELECT address.id, address.user_id, address.email_address + {printsql}SELECT address.id, address.user_id, address.email_address FROM address WHERE address.user_id != :user_id_1 OR address.user_id IS NULL @@ -1041,9 +1041,9 @@ in terms of the target :func:`_orm.relationship`. primary key equals the value of the foreign key in a related object:: >>> address_obj = session.get(Address, 1) - SELECT ... + {execsql}SELECT ... >>> print(select(User).where(User.addresses.contains(address_obj))) - {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.id = :param_1 @@ -1056,7 +1056,7 @@ in terms of the target :func:`_orm.relationship`. >>> from sqlalchemy.orm import with_parent >>> print(select(Address).where(with_parent(user_obj, User.addresses))) - {opensql}SELECT address.id, address.user_id, address.email_address + {printsql}SELECT address.id, address.user_id, address.email_address FROM address WHERE :param_1 = address.user_id diff --git a/doc/build/orm/quickstart.rst b/doc/build/orm/quickstart.rst index 5528ce631e..a22ad2ffd5 100644 --- a/doc/build/orm/quickstart.rst +++ b/doc/build/orm/quickstart.rst @@ -151,7 +151,7 @@ in our target SQLite database, using a method called :meth:`_schema.MetaData.cre .. sourcecode:: pycon+sql >>> Base.metadata.create_all(engine) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) PRAGMA main.table_...info("user_account") ... PRAGMA main.table_...info("address") @@ -217,7 +217,7 @@ is used: ... session.add_all([spongebob, sandy, patrick]) ... ... session.commit() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) INSERT INTO user_account (name, fullname) VALUES (?, ?), (?, ?), (?, ?) RETURNING id [...] ('spongebob', 'Spongebob Squarepants', 'sandy', 'Sandy Cheeks', 'patrick', 'Patrick Star') INSERT INTO address (email_address, user_id) VALUES (?, ?), (?, ?), (?, ?) RETURNING id @@ -261,7 +261,7 @@ the ORM objects we've selected: >>> for user in session.scalars(stmt): ... print(user) - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) SELECT user_account.id, user_account.name, user_account.fullname FROM user_account WHERE user_account.name IN (?, ?) @@ -294,7 +294,7 @@ construct creates joins using the :meth:`_sql.Select.join` method: ... .where(Address.email_address == "sandy@sqlalchemy.org") ... ) >>> sandy_address = session.scalars(stmt).one() - {opensql}SELECT address.id, address.email_address, address.user_id + {execsql}SELECT address.id, address.email_address, address.user_id FROM address JOIN user_account ON user_account.id = address.user_id WHERE user_account.name = ? AND address.email_address = ? [...] ('sandy', 'sandy@sqlalchemy.org') @@ -324,14 +324,14 @@ address associated with "sandy", and also add a new email address to >>> stmt = select(User).where(User.name == "patrick") >>> patrick = session.scalars(stmt).one() - {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} >>> patrick.addresses.append(Address(email_address="patrickstar@sqlalchemy.org")) - {opensql}SELECT address.id AS address_id, address.email_address AS address_email_address, address.user_id AS address_user_id + {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 [...] (3,){stop} @@ -339,7 +339,7 @@ address associated with "sandy", and also add a new email address to >>> sandy_address.email_address = "sandy_cheeks@sqlalchemy.org" >>> session.commit() - {opensql}UPDATE address SET email_address=? WHERE address.id = ? + {execsql}UPDATE address SET email_address=? WHERE address.id = ? [...] ('sandy_cheeks@sqlalchemy.org', 2) INSERT INTO address (email_address, user_id) VALUES (?, ?) [...] ('patrickstar@sqlalchemy.org', 3) @@ -369,14 +369,14 @@ object by primary key using :meth:`_orm.Session.get`, then work with the object: .. sourcecode:: pycon+sql >>> sandy = session.get(User, 2) - {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 = ? [...] (2,){stop} >>> sandy.addresses.remove(sandy_address) - {opensql}SELECT address.id AS address_id, address.email_address AS address_email_address, address.user_id AS address_user_id + {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 [...] (2,) @@ -393,7 +393,7 @@ committing the transaction, using the .. sourcecode:: pycon+sql >>> session.flush() - {opensql}DELETE FROM address WHERE address.id = ? + {execsql}DELETE FROM address WHERE address.id = ? [...] (2,) Next, we will delete the "patrick" user entirely. For a top-level delete of @@ -406,7 +406,7 @@ options that we configured, in this case, onto the related ``Address`` objects: .. sourcecode:: pycon+sql >>> session.delete(patrick) - {opensql}SELECT user_account.id AS user_account_id, user_account.name AS user_account_name, user_account.fullname AS user_account_fullname + {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 = ? [...] (3,) @@ -428,7 +428,7 @@ To illustrate the rows being deleted, here's the commit: .. sourcecode:: pycon+sql >>> session.commit() - {opensql}DELETE FROM address WHERE address.id = ? + {execsql}DELETE FROM address WHERE address.id = ? [...] (4,) DELETE FROM user_account WHERE user_account.id = ? [...] (3,) diff --git a/doc/build/orm/relationship_persistence.rst b/doc/build/orm/relationship_persistence.rst index 56c49ff2f6..9a5a036c69 100644 --- a/doc/build/orm/relationship_persistence.rst +++ b/doc/build/orm/relationship_persistence.rst @@ -107,7 +107,7 @@ row at a time for the time being): >>> w1.entries = [e1] >>> session.add_all([w1, e1]) >>> session.commit() - {opensql}BEGIN (implicit) + {execsql}BEGIN (implicit) INSERT INTO widget (favorite_entry_id, name) VALUES (?, ?) (None, 'somewidget') INSERT INTO entry (widget_id, name) VALUES (?, ?) diff --git a/doc/build/orm/self_referential.rst b/doc/build/orm/self_referential.rst index bb267c0141..70dfb4be93 100644 --- a/doc/build/orm/self_referential.rst +++ b/doc/build/orm/self_referential.rst @@ -154,7 +154,7 @@ looks like: .join(Node.parent.of_type(nodealias)) .where(nodealias.data == "child2") ).all() - {opensql}SELECT node.id AS node_id, + {execsql}SELECT node.id AS node_id, node.parent_id AS node_parent_id, node.data AS node_data FROM node JOIN node AS node_1 @@ -191,7 +191,7 @@ configured via :paramref:`~.relationships.join_depth`: session.scalars(select(Node)).all() - {opensql}SELECT node_1.id AS node_1_id, + {execsql}SELECT node_1.id AS node_1_id, node_1.parent_id AS node_1_parent_id, node_1.data AS node_1_data, node_2.id AS node_2_id, diff --git a/doc/build/orm/session_state_management.rst b/doc/build/orm/session_state_management.rst index 21f4a3c675..26a51cfb2d 100644 --- a/doc/build/orm/session_state_management.rst +++ b/doc/build/orm/session_state_management.rst @@ -452,7 +452,7 @@ one of these columns and are watching SQL, we'd see this: .. sourcecode:: pycon+sql >>> print(user.name) - {opensql}SELECT user.id AS user_id, user.name AS user_name + {execsql}SELECT user.id AS user_id, user.name AS user_name FROM user WHERE user.id = ? (1,) diff --git a/doc/build/tutorial/data_insert.rst b/doc/build/tutorial/data_insert.rst index 5087434d70..4e85f903cf 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 57a8e72096..98dc7ab03d 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 38624d5f71..a82f070a3f 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')] @@ -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 d40aaf5b8d..d0cd09aa0e 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) [...] () @@ -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)] @@ -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)] @@ -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 7c05497acb..65047fd9c3 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 3c4e55b3ef..ab16c58567 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 a23c3369ba..bd1fae131c 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 diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index d373b3a449..a6a12f4514 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -343,7 +343,7 @@ The :class:`.Sequence` object creates "real" sequences, i.e., >>> from sqlalchemy.schema import CreateSequence >>> from sqlalchemy.dialects import mssql >>> print(CreateSequence(Sequence("my_seq", start=1)).compile(dialect=mssql.dialect())) - CREATE SEQUENCE my_seq START WITH 1 + {printsql}CREATE SEQUENCE my_seq START WITH 1 For integer primary key generation, SQL Server's ``IDENTITY`` construct should generally be preferred vs. sequence. @@ -697,7 +697,7 @@ below:: >>> eng = create_engine("mssql+pymssql://mydsn", legacy_schema_aliasing=True) >>> print(account_table.select().compile(eng)) - SELECT account_1.id, account_1.info + {printsql}SELECT account_1.id, account_1.info FROM customer_schema.account AS account_1 This mode of behavior is now off by default, as it appears to have served diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 1a05344908..50e0ec07ea 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -553,7 +553,7 @@ the generative method :meth:`~.mysql.Insert.on_duplicate_key_update`: ... status='U' ... ) >>> print(on_duplicate_key_stmt) - {opensql}INSERT INTO my_table (id, data) VALUES (%s, %s) + {printsql}INSERT INTO my_table (id, data) VALUES (%s, %s) ON DUPLICATE KEY UPDATE data = VALUES(data), status = %s @@ -584,7 +584,7 @@ as values: ... ) >>> print(on_duplicate_key_stmt) - {opensql}INSERT INTO my_table (id, data) VALUES (%s, %s) + {printsql}INSERT INTO my_table (id, data) VALUES (%s, %s) ON DUPLICATE KEY UPDATE data = %s, updated_at = CURRENT_TIMESTAMP In a manner similar to that of :meth:`.UpdateBase.values`, other parameter @@ -613,7 +613,7 @@ this context is unambiguous: ... ) >>> print(on_duplicate_key_stmt) - {opensql}INSERT INTO my_table (id, data) VALUES (%s, %s) + {printsql}INSERT INTO my_table (id, data) VALUES (%s, %s) ON DUPLICATE KEY UPDATE data = %s, updated_at = CURRENT_TIMESTAMP .. versionchanged:: 1.3 support for parameter-ordered UPDATE clause within @@ -649,7 +649,7 @@ table: ... ) >>> print(do_update_stmt) - {opensql}INSERT INTO my_table (id, data, author) VALUES (%s, %s, %s) + {printsql}INSERT INTO my_table (id, data, author) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE data = %s, author = VALUES(author) When rendered, the "inserted" namespace will produce the expression diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index f3e98caa00..d47a037c44 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -539,7 +539,7 @@ and :meth:`~.postgresql.Insert.on_conflict_do_nothing`: ... index_elements=['id'] ... ) >>> print(do_nothing_stmt) - {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) ON CONFLICT (id) DO NOTHING {stop} @@ -548,7 +548,7 @@ and :meth:`~.postgresql.Insert.on_conflict_do_nothing`: ... set_=dict(data='updated value') ... ) >>> print(do_update_stmt) - {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) ON CONFLICT ON CONSTRAINT pk_my_table DO UPDATE SET data = %(param_1)s .. versionadded:: 1.1 @@ -577,7 +577,7 @@ named constraint or by column inference: ... set_=dict(data='updated value') ... ) >>> print(do_update_stmt) - {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) ON CONFLICT (id) DO UPDATE SET data = %(param_1)s {stop} @@ -586,7 +586,7 @@ named constraint or by column inference: ... set_=dict(data='updated value') ... ) >>> print(do_update_stmt) - {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) ON CONFLICT (id) DO UPDATE SET data = %(param_1)s * When using :paramref:`_postgresql.Insert.on_conflict_do_update.index_elements` to @@ -602,7 +602,7 @@ named constraint or by column inference: ... set_=dict(data=stmt.excluded.data) ... ) >>> print(stmt) - {opensql}INSERT INTO my_table (data, user_email) + {printsql}INSERT INTO my_table (data, user_email) VALUES (%(data)s, %(user_email)s) ON CONFLICT (user_email) WHERE user_email LIKE %(user_email_1)s DO UPDATE SET data = excluded.data @@ -617,7 +617,7 @@ named constraint or by column inference: ... set_=dict(data='updated value') ... ) >>> print(do_update_stmt) - {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) ON CONFLICT ON CONSTRAINT my_table_idx_1 DO UPDATE SET data = %(param_1)s {stop} @@ -626,7 +626,7 @@ named constraint or by column inference: ... set_=dict(data='updated value') ... ) >>> print(do_update_stmt) - {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) ON CONFLICT ON CONSTRAINT my_table_pk DO UPDATE SET data = %(param_1)s {stop} @@ -649,7 +649,7 @@ named constraint or by column inference: ... set_=dict(data='updated value') ... ) >>> print(do_update_stmt) - {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) ON CONFLICT (id) DO UPDATE SET data = %(param_1)s The SET Clause @@ -670,7 +670,7 @@ for UPDATE: ... set_=dict(data='updated value') ... ) >>> print(do_update_stmt) - {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) ON CONFLICT (id) DO UPDATE SET data = %(param_1)s .. warning:: @@ -705,7 +705,7 @@ table: ... set_=dict(data='updated value', author=stmt.excluded.author) ... ) >>> print(do_update_stmt) - {opensql}INSERT INTO my_table (id, data, author) + {printsql}INSERT INTO my_table (id, data, author) VALUES (%(id)s, %(data)s, %(author)s) ON CONFLICT (id) DO UPDATE SET data = %(param_1)s, author = excluded.author @@ -729,7 +729,7 @@ parameter, which will limit those rows which receive an UPDATE: ... where=(my_table.c.status == 2) ... ) >>> print(on_update_stmt) - {opensql}INSERT INTO my_table (id, data, author) + {printsql}INSERT INTO my_table (id, data, author) VALUES (%(id)s, %(data)s, %(author)s) ON CONFLICT (id) DO UPDATE SET data = %(param_1)s, author = excluded.author WHERE my_table.status = %(status_1)s @@ -747,7 +747,7 @@ this is illustrated using the >>> stmt = insert(my_table).values(id='some_id', data='inserted value') >>> stmt = stmt.on_conflict_do_nothing(index_elements=['id']) >>> print(stmt) - {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) ON CONFLICT (id) DO NOTHING If ``DO NOTHING`` is used without specifying any columns or constraint, @@ -759,7 +759,7 @@ constraint violation which occurs: >>> stmt = insert(my_table).values(id='some_id', data='inserted value') >>> stmt = stmt.on_conflict_do_nothing() >>> print(stmt) - {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + {printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) ON CONFLICT DO NOTHING .. _postgresql_match: @@ -1249,7 +1249,7 @@ Examples from PostgreSQL's reference documentation follow below: >>> from sqlalchemy import select, func >>> stmt = select(func.json_each('{"a":"foo", "b":"bar"}').table_valued("key", "value")) >>> print(stmt) - SELECT anon_1.key, anon_1.value + {printsql}SELECT anon_1.key, anon_1.value FROM json_each(:json_each_1) AS anon_1 * ``json_populate_record()``:: @@ -1262,7 +1262,7 @@ Examples from PostgreSQL's reference documentation follow below: ... ).table_valued("a", "b", name="x") ... ) >>> print(stmt) - SELECT x.a, x.b + {printsql}SELECT x.a, x.b FROM json_populate_record(null::myrowtype, :json_populate_record_1) AS x * ``json_to_record()`` - this form uses a PostgreSQL specific form of derived @@ -1279,7 +1279,7 @@ Examples from PostgreSQL's reference documentation follow below: ... ).render_derived(name="x", with_types=True) ... ) >>> print(stmt) - SELECT x.a, x.b, x.d + {printsql}SELECT x.a, x.b, x.d FROM json_to_record(:json_to_record_1) AS x(a INTEGER, b TEXT, d TEXT) * ``WITH ORDINALITY`` - part of the SQL standard, ``WITH ORDINALITY`` adds an @@ -1296,7 +1296,7 @@ Examples from PostgreSQL's reference documentation follow below: ... render_derived() ... ) >>> print(stmt) - SELECT anon_1.value, anon_1.ordinality + {printsql}SELECT anon_1.value, anon_1.ordinality FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) WITH ORDINALITY AS anon_1(value, ordinality) @@ -1333,7 +1333,7 @@ scalar value. PostgreSQL functions such as ``json_array_elements()``, >>> from sqlalchemy import select, func >>> stmt = select(func.unnest(array([1, 2])).column_valued()) >>> print(stmt) - SELECT anon_1 + {printsql}SELECT anon_1 FROM unnest(ARRAY[%(param_1)s, %(param_2)s]) AS anon_1 The function can of course be used against an existing table-bound column @@ -1344,7 +1344,7 @@ scalar value. PostgreSQL functions such as ``json_array_elements()``, >>> t = table("t", column('value', ARRAY(Integer))) >>> stmt = select(func.unnest(t.c.value).column_valued("unnested_value")) >>> print(stmt) - SELECT unnested_value + {printsql}SELECT unnested_value FROM unnest(t.value) AS unnested_value .. seealso:: @@ -1395,7 +1395,7 @@ itself:: >>> a = table( "a", column("id"), column("x"), column("y")) >>> stmt = select(func.row_to_json(a.table_valued())) >>> print(stmt) - SELECT row_to_json(a) AS row_to_json_1 + {printsql}SELECT row_to_json(a) AS row_to_json_1 FROM a .. versionadded:: 1.4.0b2 diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index c2c08b3124..e46443a74b 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -492,7 +492,7 @@ and :meth:`_sqlite.Insert.on_conflict_do_nothing`: ... ) >>> print(do_update_stmt) - {opensql}INSERT INTO my_table (id, data) VALUES (?, ?) + {printsql}INSERT INTO my_table (id, data) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET data = ?{stop} >>> do_nothing_stmt = insert_stmt.on_conflict_do_nothing( @@ -500,7 +500,7 @@ and :meth:`_sqlite.Insert.on_conflict_do_nothing`: ... ) >>> print(do_nothing_stmt) - {opensql}INSERT INTO my_table (id, data) VALUES (?, ?) + {printsql}INSERT INTO my_table (id, data) VALUES (?, ?) ON CONFLICT (id) DO NOTHING .. versionadded:: 1.4 @@ -537,11 +537,10 @@ Both methods supply the "target" of the conflict using column inference: ... ) >>> print(do_update_stmt) - {opensql}INSERT INTO my_table (data, user_email) VALUES (?, ?) + {printsql}INSERT INTO my_table (data, user_email) VALUES (?, ?) ON CONFLICT (user_email) WHERE user_email LIKE '%@gmail.com' DO UPDATE SET data = excluded.data - >>> The SET Clause ^^^^^^^^^^^^^^^ @@ -563,8 +562,7 @@ for UPDATE: ... ) >>> print(do_update_stmt) - - {opensql}INSERT INTO my_table (id, data) VALUES (?, ?) + {printsql}INSERT INTO my_table (id, data) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET data = ? .. warning:: @@ -599,7 +597,7 @@ would have been inserted had the constraint not failed: ... ) >>> print(do_update_stmt) - {opensql}INSERT INTO my_table (id, data, author) VALUES (?, ?, ?) + {printsql}INSERT INTO my_table (id, data, author) VALUES (?, ?, ?) ON CONFLICT (id) DO UPDATE SET data = ?, author = excluded.author Additional WHERE Criteria @@ -623,7 +621,7 @@ parameter, which will limit those rows which receive an UPDATE: ... where=(my_table.c.status == 2) ... ) >>> print(on_update_stmt) - {opensql}INSERT INTO my_table (id, data, author) VALUES (?, ?, ?) + {printsql}INSERT INTO my_table (id, data, author) VALUES (?, ?, ?) ON CONFLICT (id) DO UPDATE SET data = ?, author = excluded.author WHERE my_table.status = ? @@ -640,7 +638,7 @@ using the :meth:`_sqlite.Insert.on_conflict_do_nothing` method: >>> stmt = insert(my_table).values(id='some_id', data='inserted value') >>> stmt = stmt.on_conflict_do_nothing(index_elements=['id']) >>> print(stmt) - {opensql}INSERT INTO my_table (id, data) VALUES (?, ?) ON CONFLICT (id) DO NOTHING + {printsql}INSERT INTO my_table (id, data) VALUES (?, ?) ON CONFLICT (id) DO NOTHING If ``DO NOTHING`` is used without specifying any columns or constraint, @@ -652,7 +650,7 @@ occurs: >>> stmt = insert(my_table).values(id='some_id', data='inserted value') >>> stmt = stmt.on_conflict_do_nothing() >>> print(stmt) - {opensql}INSERT INTO my_table (id, data) VALUES (?, ?) ON CONFLICT DO NOTHING + {printsql}INSERT INTO my_table (id, data) VALUES (?, ?) ON CONFLICT DO NOTHING .. _sqlite_type_reflection: diff --git a/lib/sqlalchemy/ext/hybrid.py b/lib/sqlalchemy/ext/hybrid.py index f27e721c2f..baedc42c4d 100644 --- a/lib/sqlalchemy/ext/hybrid.py +++ b/lib/sqlalchemy/ext/hybrid.py @@ -71,7 +71,7 @@ returns a new SQL expression:: interval."end" - interval.start >>> print(Session().query(Interval).filter(Interval.length > 10)) - SELECT interval.id AS interval_id, interval.start AS interval_start, + {printsql}SELECT interval.id AS interval_id, interval.start AS interval_start, interval."end" AS interval_end FROM interval WHERE interval."end" - interval.start > :param_1 @@ -81,7 +81,7 @@ generally use ``getattr()`` to locate attributes, so can also be used with hybrid attributes:: >>> print(Session().query(Interval).filter_by(length=5)) - SELECT interval.id AS interval_id, interval.start AS interval_start, + {printsql}SELECT interval.id AS interval_id, interval.start AS interval_start, interval."end" AS interval_end FROM interval WHERE interval."end" - interval.start = :param_1 @@ -104,14 +104,14 @@ SQL expression-level boolean behavior:: False >>> print(Session().query(Interval).filter(Interval.contains(15))) - SELECT interval.id AS interval_id, interval.start AS interval_start, + {printsql}SELECT interval.id AS interval_id, interval.start AS interval_start, interval."end" AS interval_end FROM interval WHERE interval.start <= :start_1 AND interval."end" > :end_1 >>> ia = aliased(Interval) >>> print(Session().query(Interval, ia).filter(Interval.intersects(ia))) - SELECT interval.id AS interval_id, interval.start AS interval_start, + {printsql}SELECT interval.id AS interval_id, interval.start AS interval_start, interval."end" AS interval_end, interval_1.id AS interval_1_id, interval_1.start AS interval_1_start, interval_1."end" AS interval_1_end FROM interval, interval AS interval_1 @@ -370,7 +370,7 @@ would use an outer join:: >>> from sqlalchemy import or_ >>> print (Session().query(User, User.balance).outerjoin(User.accounts). ... filter(or_(User.balance < 5000, User.balance == None))) - SELECT "user".id AS user_id, "user".name AS user_name, + {printsql}SELECT "user".id AS user_id, "user".name AS user_name, account.balance AS account_balance FROM "user" LEFT OUTER JOIN account ON "user".id = account.user_id WHERE account.balance < :balance_1 OR account.balance IS NULL @@ -474,7 +474,7 @@ Above, SQL expressions against ``word_insensitive`` will apply the ``LOWER()`` SQL function to both sides:: >>> print(Session().query(SearchWord).filter_by(word_insensitive="Trucks")) - SELECT searchword.id AS searchword_id, searchword.word AS searchword_word + {printsql}SELECT searchword.id AS searchword_id, searchword.word AS searchword_word FROM searchword WHERE lower(searchword.word) = lower(:lower_1) @@ -628,7 +628,7 @@ SQL expression versus SQL expression:: ... filter( ... sw1.word_insensitive > sw2.word_insensitive ... )) - SELECT lower(searchword_1.word) AS lower_1, + {printsql}SELECT lower(searchword_1.word) AS lower_1, lower(searchword_2.word) AS lower_2 FROM searchword AS searchword_1, searchword AS searchword_2 WHERE lower(searchword_1.word) > lower(searchword_2.word) diff --git a/lib/sqlalchemy/sql/_elements_constructors.py b/lib/sqlalchemy/sql/_elements_constructors.py index 1d8818a1ff..6e5a7bc5e0 100644 --- a/lib/sqlalchemy/sql/_elements_constructors.py +++ b/lib/sqlalchemy/sql/_elements_constructors.py @@ -1066,23 +1066,23 @@ def false() -> False_: >>> from sqlalchemy import false >>> print(select(t.c.x).where(false())) - SELECT x FROM t WHERE false + {printsql}SELECT x FROM t WHERE false A backend which does not support true/false constants will render as an expression against 1 or 0:: >>> print(select(t.c.x).where(false())) - SELECT x FROM t WHERE 0 = 1 + {printsql}SELECT x FROM t WHERE 0 = 1 The :func:`.true` and :func:`.false` constants also feature "short circuit" operation within an :func:`.and_` or :func:`.or_` conjunction:: >>> print(select(t.c.x).where(or_(t.c.x > 5, true()))) - SELECT x FROM t WHERE true + {printsql}SELECT x FROM t WHERE true >>> print(select(t.c.x).where(and_(t.c.x > 5, false()))) - SELECT x FROM t WHERE false + {printsql}SELECT x FROM t WHERE false .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature better integrated behavior within conjunctions and on dialects @@ -1483,23 +1483,23 @@ def true() -> True_: >>> from sqlalchemy import true >>> print(select(t.c.x).where(true())) - SELECT x FROM t WHERE true + {printsql}SELECT x FROM t WHERE true A backend which does not support true/false constants will render as an expression against 1 or 0:: >>> print(select(t.c.x).where(true())) - SELECT x FROM t WHERE 1 = 1 + {printsql}SELECT x FROM t WHERE 1 = 1 The :func:`.true` and :func:`.false` constants also feature "short circuit" operation within an :func:`.and_` or :func:`.or_` conjunction:: >>> print(select(t.c.x).where(or_(t.c.x > 5, true()))) - SELECT x FROM t WHERE true + {printsql}SELECT x FROM t WHERE true >>> print(select(t.c.x).where(and_(t.c.x > 5, false()))) - SELECT x FROM t WHERE false + {printsql}SELECT x FROM t WHERE false .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature better integrated behavior within conjunctions and on dialects diff --git a/lib/sqlalchemy/sql/dml.py b/lib/sqlalchemy/sql/dml.py index 355a00c604..1ab7f2cebd 100644 --- a/lib/sqlalchemy/sql/dml.py +++ b/lib/sqlalchemy/sql/dml.py @@ -681,7 +681,7 @@ class UpdateBase( ... .returning(table.c.server_flag, table.c.updated_timestamp) ... ) >>> print(stmt) - UPDATE some_table SET status=:status + {printsql}UPDATE some_table SET status=:status WHERE some_table.data = :data_1 RETURNING some_table.server_flag, some_table.updated_timestamp @@ -702,7 +702,7 @@ class UpdateBase( ... (table.c.first_name + " " + table.c.last_name).label("fullname") ... ) >>> print(stmt) - INSERT INTO some_table (first_name, last_name) + {printsql}INSERT INTO some_table (first_name, last_name) VALUES (:first_name, :last_name) RETURNING some_table.first_name || :first_name_1 || some_table.last_name AS fullname diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py index 446cbcd3c5..902811037e 100644 --- a/lib/sqlalchemy/sql/functions.py +++ b/lib/sqlalchemy/sql/functions.py @@ -194,7 +194,7 @@ class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative): >>> from sqlalchemy import func, select >>> fn = func.jsonb_each("{'k', 'v'}").scalar_table_valued("key") >>> print(select(fn)) - SELECT (jsonb_each(:jsonb_each_1)).key + {printsql}SELECT (jsonb_each(:jsonb_each_1)).key .. versionadded:: 1.4.0b2 @@ -222,11 +222,11 @@ class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative): ... ) >>> print(select(fn)) - SELECT anon_1.value, anon_1.start, anon_1.stop, anon_1.step + {printsql}SELECT anon_1.value, anon_1.start, anon_1.stop, anon_1.step FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1 >>> print(select(fn.c.value, fn.c.stop).where(fn.c.value > 2)) - SELECT anon_1.value, anon_1.stop + {printsql}SELECT anon_1.value, anon_1.stop FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1 WHERE anon_1.value > :value_1 @@ -235,7 +235,7 @@ class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative): >>> fn = func.generate_series(4, 1, -1).table_valued("gen", with_ordinality="ordinality") >>> print(select(fn)) - SELECT anon_1.gen, anon_1.ordinality + {printsql}SELECT anon_1.gen, anon_1.ordinality FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) WITH ORDINALITY AS anon_1 :param \*expr: A series of string column names that will be added to the @@ -301,7 +301,7 @@ class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative): >>> from sqlalchemy import select, func >>> gs = func.generate_series(1, 5, -1).column_valued() >>> print(select(gs)) - SELECT anon_1 + {printsql}SELECT anon_1 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) AS anon_1 This is shorthand for:: @@ -341,7 +341,7 @@ class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative): >>> from sqlalchemy import column, select, func >>> stmt = select(column('x'), column('y')).select_from(func.myfunction()) >>> print(stmt) - SELECT x, y FROM myfunction() + {printsql}SELECT x, y FROM myfunction() The above form is a legacy feature that is now superseded by the fully capable :meth:`_functions.FunctionElement.table_valued` @@ -588,7 +588,7 @@ class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative): >>> from sqlalchemy import func, select, column >>> data_view = func.unnest([1, 2, 3]).alias("data_view") >>> print(select(data_view.column)) - SELECT data_view + {printsql}SELECT data_view FROM unnest(:unnest_1) AS data_view The :meth:`_functions.FunctionElement.column_valued` method provides @@ -596,7 +596,7 @@ class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative): >>> data_view = func.unnest([1, 2, 3]).column_valued("data_view") >>> print(select(data_view)) - SELECT data_view + {printsql}SELECT data_view FROM unnest(:unnest_1) AS data_view .. versionadded:: 1.4.0b2 Added the ``.column`` accessor @@ -772,7 +772,7 @@ class _FunctionGenerator: column-oriented SQL element like any other, and is used in that way:: >>> print(select(func.count(table.c.id))) - SELECT count(sometable.id) FROM sometable + {printsql}SELECT count(sometable.id) FROM sometable Any name can be given to :data:`.func`. If the function name is unknown to SQLAlchemy, it will be rendered exactly as is. For common SQL functions @@ -780,13 +780,13 @@ class _FunctionGenerator: function* which will be compiled appropriately to the target database:: >>> print(func.current_timestamp()) - CURRENT_TIMESTAMP + {printsql}CURRENT_TIMESTAMP To call functions which are present in dot-separated packages, specify them in the same manner:: >>> print(func.stats.yield_curve(5, 10)) - stats.yield_curve(:yield_curve_1, :yield_curve_2) + {printsql}stats.yield_curve(:yield_curve_1, :yield_curve_2) SQLAlchemy can be made aware of the return type of functions to enable type-specific lexical and result-based behavior. For example, to ensure @@ -796,7 +796,7 @@ class _FunctionGenerator: >>> print(func.my_string(u'hi', type_=Unicode) + ' ' + ... func.my_string(u'there', type_=Unicode)) - my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3) + {printsql}my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3) The object returned by a :data:`.func` call is usually an instance of :class:`.Function`. @@ -834,7 +834,7 @@ class _FunctionGenerator: :class:`.Function` - """ + """ # noqa def __init__(self, **opts): self.__names = [] @@ -1250,14 +1250,14 @@ class concat(GenericFunction[str]): E.g.:: >>> print(select(func.concat('a', 'b'))) - SELECT concat(:concat_2, :concat_3) AS concat_1 + {printsql}SELECT concat(:concat_2, :concat_3) AS concat_1 String concatenation in SQLAlchemy is more commonly available using the Python ``+`` operator with string datatypes, which will render a backend-specific concatenation operator, such as :: >>> print(select(literal("a") + "b")) - SELECT :param_1 || :param_2 AS anon_1 + {printsql}SELECT :param_1 || :param_2 AS anon_1 """ diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index 898b524adb..1d283e83c2 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -1032,7 +1032,7 @@ class NamedFromClause(FromClause): >>> a = table("a", column("id"), column("x"), column("y")) >>> stmt = select(func.row_to_json(a.table_valued())) >>> print(stmt) - SELECT row_to_json(a) AS row_to_json_1 + {printsql}SELECT row_to_json(a) AS row_to_json_1 FROM a .. versionadded:: 1.4.0b2 @@ -1061,7 +1061,7 @@ class SelectLabelStyle(Enum): >>> table1 = table("table1", column("columna"), column("columnb")) >>> table2 = table("table2", column("columna"), column("columnc")) >>> print(select(table1, table2).join(table2, true()).set_label_style(LABEL_STYLE_NONE)) - SELECT table1.columna, table1.columnb, table2.columna, table2.columnc + {printsql}SELECT table1.columna, table1.columnb, table2.columna, table2.columnc FROM table1 JOIN table2 ON true Used with the :meth:`_sql.Select.set_label_style` method. @@ -1084,7 +1084,7 @@ class SelectLabelStyle(Enum): >>> table1 = table("table1", column("columna"), column("columnb")) >>> table2 = table("table2", column("columna"), column("columnc")) >>> print(select(table1, table2).join(table2, true()).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)) - SELECT table1.columna AS table1_columna, table1.columnb AS table1_columnb, table2.columna AS table2_columna, table2.columnc AS table2_columnc + {printsql}SELECT table1.columna AS table1_columna, table1.columnb AS table1_columnb, table2.columna AS table2_columna, table2.columnc AS table2_columnc FROM table1 JOIN table2 ON true Used with the :meth:`_sql.GenerativeSelect.set_label_style` method. @@ -1111,7 +1111,7 @@ class SelectLabelStyle(Enum): >>> table1 = table("table1", column("columna"), column("columnb")) >>> table2 = table("table2", column("columna"), column("columnc")) >>> print(select(table1, table2).join(table2, true()).set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)) - SELECT table1.columna, table1.columnb, table2.columna AS columna_1, table2.columnc + {printsql}SELECT table1.columna, table1.columnb, table2.columna AS columna_1, table2.columnc FROM table1 JOIN table2 ON true Used with the :meth:`_sql.GenerativeSelect.set_label_style` method, @@ -1719,7 +1719,7 @@ class TableValuedAlias(LateralFromClause, Alias): >>> from sqlalchemy import select, func >>> fn = func.json_array_elements_text('["one", "two", "three"]').table_valued("value") >>> print(select(fn.c.value)) - SELECT anon_1.value + {printsql}SELECT anon_1.value FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1 .. versionadded:: 1.4.0b2 @@ -1831,7 +1831,7 @@ class TableValuedAlias(LateralFromClause, Alias): table_valued("x", with_ordinality="o").render_derived() ... ) ... ) - SELECT anon_1.x, anon_1.o + {printsql}SELECT anon_1.x, anon_1.o FROM unnest(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s]) WITH ORDINALITY AS anon_1(x, o) The ``with_types`` keyword will render column types inline within @@ -1847,7 +1847,7 @@ class TableValuedAlias(LateralFromClause, Alias): ... .render_derived(with_types=True) ... ) ... ) - SELECT anon_1.a, anon_1.b FROM json_to_recordset(:json_to_recordset_1) + {printsql}SELECT anon_1.a, anon_1.b FROM json_to_recordset(:json_to_recordset_1) AS anon_1(a INTEGER, b VARCHAR) :param name: optional string name that will be applied to the alias diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index bcdbcc8047..bcbc7004c0 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -206,7 +206,7 @@ class String(Concatenable, TypeEngine[str]): >>> from sqlalchemy import cast, select, String >>> print(select(cast('some string', String(collation='utf8')))) - SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1 + {printsql}SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1 .. note:: diff --git a/test/base/test_tutorials.py b/test/base/test_tutorials.py index 9c2fb2d72b..b920f25f0a 100644 --- a/test/base/test_tutorials.py +++ b/test/base/test_tutorials.py @@ -110,7 +110,9 @@ class DocTest(fixtures.TestBase): doctest_enabled = True for line_counter, line in enumerate(file_, 1): - line = re.sub(r"{(?:stop|sql|opensql)}", "", line) + line = re.sub( + r"{(?:stop|sql|opensql|execsql|printsql)}", "", line + ) include = re.match(r"\.\. doctest-include (.+\.rst)", line) if include: diff --git a/tools/format_docs_code.py b/tools/format_docs_code.py index 6ef7a30806..3b11c24a81 100644 --- a/tools/format_docs_code.py +++ b/tools/format_docs_code.py @@ -103,10 +103,12 @@ def _format_block( format_directive = re.compile(r"^\.\.\s*format\s*:\s*(on|off)\s*$") -doctest_code_start = re.compile(r"^(\s+)({(?:opensql|sql|stop)})?>>>\s?(.+)") +doctest_code_start = re.compile( + r"^(\s+)({(?:opensql|execsql|printsql|sql|stop)})?>>>\s?(.+)" +) doctest_code_continue = re.compile(r"^\s+\.\.\.\s?(\s*.*)") -sql_code_start = re.compile(r"^(\s+)({(?:open)?sql})") +sql_code_start = re.compile(r"^(\s+)({(?:open|print|exec)?sql})") sql_code_stop = re.compile(r"^(\s+){stop}") start_code_section = re.compile( -- 2.47.2