summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorFederico Caselli <cfederico87@gmail.com>2023-01-10 21:39:44 +0100
committerFederico Caselli <cfederico87@gmail.com>2023-01-11 20:24:29 +0100
commitdce11383f83c28f2acc0ed9ee346a56d63e9fcf8 (patch)
tree3a0196dd35fe8e87a8fadaa2611cc308d6d65914
parenta950402dae2a5b2448f5f4235946b2f767c7485c (diff)
downloadsqlalchemy-dce11383f83c28f2acc0ed9ee346a56d63e9fcf8.tar.gz
Improve sql formatting
change {opensql} to {printsql} in prints, add missing markers Change-Id: I07b72e6620bb64e329d6b641afa27631e91c4f16
-rw-r--r--doc/build/changelog/migration_08.rst4
-rw-r--r--doc/build/changelog/migration_09.rst18
-rw-r--r--doc/build/changelog/migration_11.rst13
-rw-r--r--doc/build/changelog/migration_12.rst8
-rw-r--r--doc/build/changelog/migration_13.rst12
-rw-r--r--doc/build/changelog/migration_14.rst24
-rw-r--r--doc/build/changelog/whatsnew_20.rst4
-rw-r--r--doc/build/core/connections.rst4
-rw-r--r--doc/build/core/constraints.rst12
-rw-r--r--doc/build/core/custom_types.rst4
-rw-r--r--doc/build/core/ddl.rst10
-rw-r--r--doc/build/core/defaults.rst2
-rw-r--r--doc/build/core/metadata.rst6
-rw-r--r--doc/build/core/operators.rst12
-rw-r--r--doc/build/core/reflection.rst4
-rw-r--r--doc/build/errors.rst8
-rw-r--r--doc/build/faq/ormconfiguration.rst8
-rw-r--r--doc/build/faq/sqlexpressions.rst8
-rw-r--r--doc/build/orm/cascades.rst4
-rw-r--r--doc/build/orm/composites.rst12
-rw-r--r--doc/build/orm/dataclasses.rst2
-rw-r--r--doc/build/orm/declarative_mixins.rst8
-rw-r--r--doc/build/orm/declarative_tables.rst10
-rw-r--r--doc/build/orm/extensions/associationproxy.rst6
-rw-r--r--doc/build/orm/inheritance.rst4
-rw-r--r--doc/build/orm/join_conditions.rst8
-rw-r--r--doc/build/orm/large_collections.rst26
-rw-r--r--doc/build/orm/mapped_attributes.rst6
-rw-r--r--doc/build/orm/queryguide/api.rst10
-rw-r--r--doc/build/orm/queryguide/columns.rst44
-rw-r--r--doc/build/orm/queryguide/dml.rst40
-rw-r--r--doc/build/orm/queryguide/inheritance.rst49
-rw-r--r--doc/build/orm/queryguide/relationships.rst24
-rw-r--r--doc/build/orm/queryguide/select.rst86
-rw-r--r--doc/build/orm/quickstart.rst24
-rw-r--r--doc/build/orm/relationship_persistence.rst2
-rw-r--r--doc/build/orm/self_referential.rst4
-rw-r--r--doc/build/orm/session_state_management.rst2
-rw-r--r--doc/build/tutorial/data_insert.rst14
-rw-r--r--doc/build/tutorial/data_select.rst172
-rw-r--r--doc/build/tutorial/data_update.rst26
-rw-r--r--doc/build/tutorial/dbapi_transactions.rst24
-rw-r--r--doc/build/tutorial/metadata.rst4
-rw-r--r--doc/build/tutorial/orm_data_manipulation.rst18
-rw-r--r--doc/build/tutorial/orm_related_objects.rst22
-rw-r--r--lib/sqlalchemy/dialects/mssql/base.py4
-rw-r--r--lib/sqlalchemy/dialects/mysql/base.py8
-rw-r--r--lib/sqlalchemy/dialects/postgresql/base.py40
-rw-r--r--lib/sqlalchemy/dialects/sqlite/base.py18
-rw-r--r--lib/sqlalchemy/ext/hybrid.py14
-rw-r--r--lib/sqlalchemy/sql/_elements_constructors.py16
-rw-r--r--lib/sqlalchemy/sql/dml.py4
-rw-r--r--lib/sqlalchemy/sql/functions.py30
-rw-r--r--lib/sqlalchemy/sql/selectable.py14
-rw-r--r--lib/sqlalchemy/sql/sqltypes.py2
-rw-r--r--test/base/test_tutorials.py4
-rw-r--r--tools/format_docs_code.py6
57 files changed, 483 insertions, 489 deletions
diff --git a/doc/build/changelog/migration_08.rst b/doc/build/changelog/migration_08.rst
index 3faecf08f..0ec39ed3c 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 37f619c9d..ec2c31242 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 0732900c6..302b5492d 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 50b0905fb..46307c5f9 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 e799e7181..2c38511f1 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 df7080d0a..d23f8ea1d 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 <name>" 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 d94250d71..adf0971c3 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 1b842a4e9..80156b418 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 ff2ae4916..3b05b8c12 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 2c8aeeda6..16c7bbec9 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 d5d6ded30..1e323dea2 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 848b32b17..ee9d883ad 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 f416b6e6d..2503f0b66 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 9bf47ec66..84fc554fc 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 5a84bc2ff..1ff517510 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 87477db38..e7878e06a 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 3ff3c93b8..47e9e2a49 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 eb3d906d1..73aa65f26 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 2c7c0d9cf..02d68669e 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 1dd857739..ab2b55109 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 4566d704f..e98c67e68 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 8927e710d..336882c73 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 806a6897f..9442d2f16 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 6334cbecd..5281f59a5 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 7d7213db7..5d2896ee4 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 8f799732e..f89565445 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 4d9f96e9c..8553fae42 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 4352655dc..d95406f4a 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 559010cf7..15301cbd0 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 29edca345..a57c9f38b 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 97aee3fea..606b7ccef 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 7905dde2e..7040128f4 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 fff79b318..593fe995b 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 55c3ae94e..579a3f842 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 ``(<join from>, <onclause>)``, or ``(<join from>, <join to>,
>>> 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 5528ce631..a22ad2ffd 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 56c49ff2f..9a5a036c6 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 bb267c014..70dfb4be9 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 21f4a3c67..26a51cfb2 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 5087434d7..4e85f903c 100644
--- a/doc/build/tutorial/data_insert.rst
+++ b/doc/build/tutorial/data_insert.rst
@@ -47,7 +47,7 @@ SQL expressions can be stringified in place as a means to see the general
form of what's being produced::
>>> print(stmt)
- {opensql}INSERT INTO user_account (name, fullname) VALUES (:name, :fullname)
+ {printsql}INSERT INTO user_account (name, fullname) VALUES (:name, :fullname)
The stringified form is created by producing a :class:`_engine.Compiled` form
of the object which includes a database-specific string SQL representation of
@@ -76,7 +76,7 @@ SQL logging:
>>> with engine.connect() as conn:
... result = conn.execute(stmt)
... conn.commit()
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
INSERT INTO user_account (name, fullname) VALUES (?, ?)
[...] ('spongebob', 'Spongebob Squarepants')
COMMIT
@@ -131,7 +131,7 @@ illustrate this:
... ],
... )
... conn.commit()
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
INSERT INTO user_account (name, fullname) VALUES (?, ?)
[...] [('sandy', 'Sandy Cheeks'), ('patrick', 'Patrick Star')]
COMMIT{stop}
@@ -190,7 +190,7 @@ construct automatically.
... ],
... )
... conn.commit()
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
INSERT INTO address (user_id, email_address) VALUES ((SELECT user_account.id
FROM user_account
WHERE user_account.name = ?), ?)
@@ -212,7 +212,7 @@ method::
... ["user_id", "email_address"], select_stmt
... )
>>> print(insert_stmt)
- {opensql}INSERT INTO address (user_id, email_address)
+ {printsql}INSERT INTO address (user_id, email_address)
SELECT user_account.id, user_account.name || :name_1 AS anon_1
FROM user_account
@@ -233,7 +233,7 @@ can be fetched::
... address_table.c.id, address_table.c.email_address
... )
>>> print(insert_stmt)
- {opensql}INSERT INTO address (id, user_id, email_address)
+ {printsql}INSERT INTO address (id, user_id, email_address)
VALUES (:id, :user_id, :email_address)
RETURNING address.id, address.email_address
@@ -246,7 +246,7 @@ as in the example below that builds upon the example stated in
... ["user_id", "email_address"], select_stmt
... )
>>> print(insert_stmt.returning(address_table.c.id, address_table.c.email_address))
- {opensql}INSERT INTO address (user_id, email_address)
+ {printsql}INSERT INTO address (user_id, email_address)
SELECT user_account.id, user_account.name || :name_1 AS anon_1
FROM user_account RETURNING address.id, address.email_address
diff --git a/doc/build/tutorial/data_select.rst b/doc/build/tutorial/data_select.rst
index 57a8e7209..98dc7ab03 100644
--- a/doc/build/tutorial/data_select.rst
+++ b/doc/build/tutorial/data_select.rst
@@ -38,7 +38,7 @@ it can be stringified in place::
>>> from sqlalchemy import select
>>> stmt = select(user_table).where(user_table.c.name == "spongebob")
>>> print(stmt)
- {opensql}SELECT user_account.id, user_account.name, user_account.fullname
+ {printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1
@@ -53,13 +53,13 @@ objects back:
>>> with engine.connect() as conn:
... for row in conn.execute(stmt):
... print(row)
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('spongebob',){stop}
(1, 'spongebob', 'Spongebob Squarepants')
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
When using the ORM, particularly with a :func:`_sql.select` construct that's
composed against ORM entities, we will want to execute it using the
@@ -75,13 +75,13 @@ elements within each row:
>>> with Session(engine) as session:
... for row in session.execute(stmt):
... print(row)
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('spongebob',){stop}
(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
.. topic:: select() from a Table vs. ORM class
@@ -108,7 +108,7 @@ set. These elements also serve in simpler cases to create the FROM clause,
which is inferred from the columns and table-like expressions passed::
>>> print(select(user_table))
- {opensql}SELECT user_account.id, user_account.name, user_account.fullname
+ {printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
To SELECT from individual columns using a Core approach,
@@ -118,7 +118,7 @@ of all :class:`_schema.Table` and other :class:`_sql.FromClause` objects that
are represented by those columns::
>>> print(select(user_table.c.name, user_table.c.fullname))
- {opensql}SELECT user_account.name, user_account.fullname
+ {printsql}SELECT user_account.name, user_account.fullname
FROM user_account
Alternatively, when using the :attr:`.FromClause.c` collection of any
@@ -126,7 +126,7 @@ Alternatively, when using the :attr:`.FromClause.c` collection of any
for a :func:`_sql.select` by using a tuple of string names::
>>> print(select(user_table.c["name", "fullname"]))
- {opensql}SELECT user_account.name, user_account.fullname
+ {printsql}SELECT user_account.name, user_account.fullname
FROM user_account
.. versionadded:: 2.0 Added tuple-accessor capability to the
@@ -145,7 +145,7 @@ example of SELECTing from the ``User`` entity, which ultimately renders
in the same way as if we had used ``user_table`` directly::
>>> print(select(User))
- {opensql}SELECT user_account.id, user_account.name, user_account.fullname
+ {printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
When executing a statement like the above using the ORM :meth:`_orm.Session.execute`
@@ -157,7 +157,7 @@ things to fetch, we get back :class:`_engine.Row` objects that have only one ele
instances of the ``User`` class::
>>> row = session.execute(select(User)).first()
- {opensql}BEGIN...
+ {execsql}BEGIN...
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
[...] (){stop}
@@ -176,7 +176,7 @@ that delivers the first "column" of each row at once, in this case,
instances of the ``User`` class::
>>> user = session.scalars(select(User)).first()
- {opensql}SELECT user_account.id, user_account.name, user_account.fullname
+ {execsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
[...] (){stop}
>>> user
@@ -190,7 +190,7 @@ the :class:`_schema.Column` or other SQL expression represented by each
attribute::
>>> print(select(User.name, User.fullname))
- {opensql}SELECT user_account.name, user_account.fullname
+ {printsql}SELECT user_account.name, user_account.fullname
FROM user_account
When we invoke *this* statement using :meth:`_orm.Session.execute`, we now
@@ -198,7 +198,7 @@ receive rows that have individual elements per value, each corresponding
to a separate column or other SQL expression::
>>> row = session.execute(select(User.name, User.fullname)).first()
- {opensql}SELECT user_account.name, user_account.fullname
+ {execsql}SELECT user_account.name, user_account.fullname
FROM user_account
[...] (){stop}
>>> row
@@ -211,7 +211,7 @@ it with full ``Address`` entities in the second element::
>>> session.execute(
... select(User.name, Address).where(User.id == Address.user_id).order_by(Address.id)
... ).all()
- {opensql}SELECT user_account.name, address.id, address.email_address, address.user_id
+ {execsql}SELECT user_account.name, address.id, address.email_address, address.user_id
FROM user_account, address
WHERE user_account.id = address.user_id ORDER BY address.id
[...] (){stop}
@@ -243,14 +243,14 @@ when referring to arbitrary SQL expressions in a result row by name:
>>> with engine.connect() as conn:
... for row in conn.execute(stmt):
... print(f"{row.username}")
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT ? || user_account.name AS username
FROM user_account ORDER BY user_account.name
[...] ('Username: ',){stop}
Username: patrick
Username: sandy
Username: spongebob
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
.. seealso::
@@ -281,12 +281,12 @@ SELECT statement::
>>> stmt = select(text("'some phrase'"), user_table.c.name).order_by(user_table.c.name)
>>> with engine.connect() as conn:
... print(conn.execute(stmt).all())
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT 'some phrase', user_account.name
FROM user_account ORDER BY user_account.name
[generated in ...] ()
{stop}[('some phrase', 'patrick'), ('some phrase', 'sandy'), ('some phrase', 'spongebob')]
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
While the :func:`_sql.text` construct can be used in most places to inject
literal SQL phrases, more often than not we are actually dealing with textual
@@ -306,14 +306,14 @@ towards in subqueries and other expressions::
>>> with engine.connect() as conn:
... for row in conn.execute(stmt):
... print(f"{row.p}, {row.name}")
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT 'some phrase' AS p, user_account.name
FROM user_account ORDER BY user_account.name
[generated in ...] ()
{stop}some phrase, patrick
some phrase, sandy
some phrase, spongebob
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
Note that in both cases, when using :func:`_sql.text` or
@@ -344,7 +344,7 @@ We can use expressions like these to generate the WHERE clause by passing
the resulting objects to the :meth:`_sql.Select.where` method::
>>> print(select(user_table).where(user_table.c.name == "squidward"))
- {opensql}SELECT user_account.id, user_account.name, user_account.fullname
+ {printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1
@@ -357,7 +357,7 @@ method may be invoked any number of times::
... .where(user_table.c.name == "squidward")
... .where(address_table.c.user_id == user_table.c.id)
... )
- {opensql}SELECT address.email_address
+ {printsql}SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id
@@ -370,7 +370,7 @@ with the same effect::
... address_table.c.user_id == user_table.c.id,
... )
... )
- {opensql}SELECT address.email_address
+ {printsql}SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id
@@ -387,7 +387,7 @@ of ORM entities::
... )
... )
... )
- {opensql}SELECT address.email_address
+ {printsql}SELECT address.email_address
FROM address, user_account
WHERE (user_account.name = :name_1 OR user_account.name = :name_2)
AND address.user_id = user_account.id
@@ -398,7 +398,7 @@ arguments that match to column keys or ORM attribute names. It will filter
against the leftmost FROM clause or the last entity joined::
>>> print(select(User).filter_by(name="spongebob", fullname="Spongebob Squarepants"))
- {opensql}SELECT user_account.id, user_account.name, user_account.fullname
+ {printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1 AND user_account.fullname = :fullname_1
@@ -423,14 +423,14 @@ in the COLUMNS clause, it puts that :class:`_schema.Table` in the FROM
clause as well::
>>> print(select(user_table.c.name))
- {opensql}SELECT user_account.name
+ {printsql}SELECT user_account.name
FROM user_account
If we were to put columns from two tables, then we get a comma-separated FROM
clause::
>>> print(select(user_table.c.name, address_table.c.email_address))
- {opensql}SELECT user_account.name, address.email_address
+ {printsql}SELECT user_account.name, address.email_address
FROM user_account, address
In order to JOIN these two tables together, we typically use one of two methods
@@ -443,7 +443,7 @@ explicitly::
... user_table, address_table
... )
... )
- {opensql}SELECT user_account.name, address.email_address
+ {printsql}SELECT user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
@@ -451,7 +451,7 @@ The other is the the :meth:`_sql.Select.join` method, which indicates only the
right side of the JOIN, the left hand-side is inferred::
>>> print(select(user_table.c.name, address_table.c.email_address).join(address_table))
- {opensql}SELECT user_account.name, address.email_address
+ {printsql}SELECT user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
.. sidebar:: The ON Clause is inferred
@@ -468,7 +468,7 @@ clause and :meth:`_sql.Select.join` to establish ``address_table`` as
the second::
>>> print(select(address_table.c.email_address).select_from(user_table).join(address_table))
- {opensql}SELECT address.email_address
+ {printsql}SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
Another example where we might want to use :meth:`_sql.Select.select_from`
@@ -479,7 +479,7 @@ produce the SQL ``count()`` function::
>>> from sqlalchemy import func
>>> print(select(func.count("*")).select_from(user_table))
- {opensql}SELECT count(:count_2) AS count_1
+ {printsql}SELECT count(:count_2) AS count_1
FROM user_account
.. seealso::
@@ -511,7 +511,7 @@ same SQL Expression mechanics as we saw about in :ref:`tutorial_select_where_cla
... .select_from(user_table)
... .join(address_table, user_table.c.id == address_table.c.user_id)
... )
- {opensql}SELECT address.email_address
+ {printsql}SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
.. container:: orm-header
@@ -532,11 +532,11 @@ accept keyword arguments :paramref:`_sql.Select.join.isouter` and
and FULL OUTER JOIN, respectively::
>>> print(select(user_table).join(address_table, isouter=True))
- {opensql}SELECT user_account.id, user_account.name, user_account.fullname
+ {printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account LEFT OUTER JOIN address ON user_account.id = address.user_id{stop}
>>> print(select(user_table).join(address_table, full=True))
- {opensql}SELECT user_account.id, user_account.name, user_account.fullname
+ {printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account FULL OUTER JOIN address ON user_account.id = address.user_id{stop}
There is also a method :meth:`_sql.Select.outerjoin` that is equivalent to
@@ -572,7 +572,7 @@ similar objects. The :meth:`_sql.Select.order_by` method accepts one or
more of these expressions positionally::
>>> print(select(user_table).order_by(user_table.c.name))
- {opensql}SELECT user_account.id, user_account.name, user_account.fullname
+ {printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.name
Ascending / descending is available from the :meth:`_sql.ColumnElement.asc`
@@ -581,7 +581,7 @@ from ORM-bound attributes as well::
>>> print(select(User).order_by(User.fullname.desc()))
- {opensql}SELECT user_account.id, user_account.name, user_account.fullname
+ {printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.fullname DESC
The above statement will yield rows that are sorted by the
@@ -609,7 +609,7 @@ we call upon the ``count()`` name::
>>> from sqlalchemy import func
>>> count_fn = func.count(user_table.c.id)
>>> print(count_fn)
- {opensql}count(user_account.id)
+ {printsql}count(user_account.id)
SQL functions are described in more detail later in this tutorial at
:ref:`tutorial_functions`.
@@ -638,13 +638,13 @@ than one address:
... .having(func.count(Address.id) > 1)
... )
... print(result.all())
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT user_account.name, count(address.id) AS count
FROM user_account JOIN address ON user_account.id = address.user_id GROUP BY user_account.name
HAVING count(address.id) > ?
[...] (1,){stop}
[('sandy', 2)]
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
.. _tutorial_order_by_label:
@@ -671,7 +671,7 @@ error if no match is found. The unary modifiers
... .order_by("user_id", desc("num_addresses"))
... )
>>> print(stmt)
- {opensql}SELECT address.user_id, count(address.id) AS num_addresses
+ {printsql}SELECT address.user_id, count(address.id) AS num_addresses
FROM address GROUP BY address.user_id ORDER BY address.user_id, num_addresses DESC
.. _tutorial_using_aliases:
@@ -700,7 +700,7 @@ below for example returns all unique pairs of user names::
... user_alias_1, user_alias_2, user_alias_1.c.id > user_alias_2.c.id
... )
... )
- {opensql}SELECT user_account_1.name, user_account_2.name AS name_1
+ {printsql}SELECT user_account_1.name, user_account_2.name AS name_1
FROM user_account AS user_account_1
JOIN user_account AS user_account_2 ON user_account_1.id > user_account_2.id
@@ -726,7 +726,7 @@ while maintaining ORM functionality. The SELECT below selects from the
... .join_from(User, address_alias_2)
... .where(address_alias_2.email_address == "patrick@gmail.com")
... )
- {opensql}SELECT user_account.id, user_account.name, user_account.fullname
+ {printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN address AS address_1 ON user_account.id = address_1.user_id
JOIN address AS address_2 ON user_account.id = address_2.user_id
@@ -776,7 +776,7 @@ Stringifying the subquery by itself without it being embedded inside of another
without any enclosing parenthesis::
>>> print(subq)
- {opensql}SELECT count(address.id) AS count, address.user_id
+ {printsql}SELECT count(address.id) AS count, address.user_id
FROM address GROUP BY address.user_id
@@ -787,7 +787,7 @@ refer to both the ``user_id`` column as well as our custom labeled
``count`` expression::
>>> print(select(subq.c.user_id, subq.c.count))
- {opensql}SELECT anon_1.user_id, anon_1.count
+ {printsql}SELECT anon_1.user_id, anon_1.count
FROM (SELECT count(address.id) AS count, address.user_id AS user_id
FROM address GROUP BY address.user_id) AS anon_1
@@ -800,7 +800,7 @@ the ``user_account`` table::
... )
>>> print(stmt)
- {opensql}SELECT user_account.name, user_account.fullname, anon_1.count
+ {printsql}SELECT user_account.name, user_account.fullname, anon_1.count
FROM user_account JOIN (SELECT count(address.id) AS count, address.user_id AS user_id
FROM address GROUP BY address.user_id) AS anon_1 ON user_account.id = anon_1.user_id
@@ -834,7 +834,7 @@ table expression syntax::
... )
>>> print(stmt)
- {opensql}WITH anon_1 AS
+ {printsql}WITH anon_1 AS
(SELECT count(address.id) AS count, address.user_id AS user_id
FROM address GROUP BY address.user_id)
SELECT user_account.name, user_account.fullname, anon_1.count
@@ -892,7 +892,7 @@ each ``Address`` object ultimately came from a subquery against the
>>> with Session(engine) as session:
... for user, address in session.execute(stmt):
... print(f"{user} {address}")
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.email_address, anon_1.user_id
FROM user_account JOIN
@@ -904,7 +904,7 @@ each ``Address`` object ultimately came from a subquery against the
User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org')
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
Another example follows, which is exactly the same except it makes use of the
:class:`_sql.CTE` construct instead:
@@ -921,7 +921,7 @@ Another example follows, which is exactly the same except it makes use of the
>>> with Session(engine) as session:
... for user, address in session.execute(stmt):
... print(f"{user} {address}")
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
WITH anon_1 AS
(SELECT address.id AS id, address.email_address AS email_address, address.user_id AS user_id
FROM address
@@ -935,7 +935,7 @@ Another example follows, which is exactly the same except it makes use of the
User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org')
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
.. seealso::
@@ -970,7 +970,7 @@ renders as an ordinary SELECT statement that is selecting from two tables::
... .scalar_subquery()
... )
>>> print(subq)
- {opensql}(SELECT count(address.id) AS count_1
+ {printsql}(SELECT count(address.id) AS count_1
FROM address, user_account
WHERE user_account.id = address.user_id)
@@ -979,7 +979,7 @@ SQL expression hierarchy, in that it may be used like any other column
expression::
>>> print(subq == 5)
- {opensql}(SELECT count(address.id) AS count_1
+ {printsql}(SELECT count(address.id) AS count_1
FROM address, user_account
WHERE user_account.id = address.user_id) = :param_1
@@ -992,7 +992,7 @@ into an enclosing :func:`_sql.select` construct that deals with the
>>> stmt = select(user_table.c.name, subq.label("address_count"))
>>> print(stmt)
- {opensql}SELECT user_account.name, (SELECT count(address.id) AS count_1
+ {printsql}SELECT user_account.name, (SELECT count(address.id) AS count_1
FROM address
WHERE user_account.id = address.user_id) AS address_count
FROM user_account
@@ -1043,7 +1043,7 @@ The statement then can return the data for this column like any other:
... .order_by(user_table.c.id, address_table.c.id)
... )
... print(result.all())
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT user_account.name, address.email_address, (SELECT count(address.id) AS count_1
FROM address
WHERE user_account.id = address.user_id) AS address_count
@@ -1051,7 +1051,7 @@ The statement then can return the data for this column like any other:
[...] (){stop}
[('spongebob', 'spongebob@sqlalchemy.org', 1), ('sandy', 'sandy@sqlalchemy.org', 2),
('sandy', 'sandy@squirrelpower.org', 2)]
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
.. _tutorial_lateral_correlation:
@@ -1095,7 +1095,7 @@ was discussed in the previous section::
... .order_by(user_table.c.id, subq.c.email_address)
... )
>>> print(stmt)
- {opensql}SELECT user_account.name, anon_1.address_count, anon_1.email_address
+ {printsql}SELECT user_account.name, anon_1.address_count, anon_1.email_address
FROM user_account
JOIN LATERAL (SELECT count(address.id) AS address_count,
address.email_address AS email_address, address.user_id AS user_id
@@ -1150,7 +1150,7 @@ that it has fewer methods. The :class:`_sql.CompoundSelect` produced by
>>> with engine.connect() as conn:
... result = conn.execute(u)
... print(result.all())
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
@@ -1159,7 +1159,7 @@ that it has fewer methods. The :class:`_sql.CompoundSelect` produced by
WHERE user_account.name = ?
[generated in ...] ('sandy', 'spongebob')
{stop}[(2, 'sandy', 'Sandy Cheeks'), (1, 'spongebob', 'Spongebob Squarepants')]
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
To use a :class:`_sql.CompoundSelect` as a subquery, just like :class:`_sql.Select`
it provides a :meth:`_sql.SelectBase.subquery` method which will produce a
@@ -1175,7 +1175,7 @@ collection that may be referred towards in an enclosing :func:`_sql.select`::
>>> with engine.connect() as conn:
... result = conn.execute(stmt)
... print(result.all())
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT anon_1.name, address.email_address
FROM address JOIN
(SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
@@ -1189,7 +1189,7 @@ collection that may be referred towards in an enclosing :func:`_sql.select`::
ORDER BY anon_1.name, address.email_address
[generated in ...] ('sandy', 'spongebob')
{stop}[('sandy', 'sandy@sqlalchemy.org'), ('sandy', 'sandy@squirrelpower.org'), ('spongebob', 'spongebob@sqlalchemy.org')]
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
.. _tutorial_orm_union:
@@ -1220,7 +1220,7 @@ criteria can be added after :meth:`_sql.Select.from_statement` is used::
>>> with Session(engine) as session:
... for obj in session.execute(orm_stmt).scalars():
... print(obj)
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname
@@ -1229,7 +1229,7 @@ criteria can be added after :meth:`_sql.Select.from_statement` is used::
[generated in ...] ('sandy', 'spongebob')
{stop}User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
To use a UNION or other set-related construct as an entity-related component in
in a more flexible manner, the :class:`_sql.CompoundSelect` construct may be
@@ -1247,7 +1247,7 @@ by the subquery::
>>> with Session(engine) as session:
... for obj in session.execute(orm_stmt).scalars():
... print(obj)
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
@@ -1257,7 +1257,7 @@ by the subquery::
[generated in ...] ('sandy', 'spongebob')
{stop}User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
.. seealso::
@@ -1288,7 +1288,7 @@ can return ``user_account`` rows that have more than one related row in
>>> with engine.connect() as conn:
... result = conn.execute(select(user_table.c.name).where(subq))
... print(result.all())
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT user_account.name
FROM user_account
WHERE EXISTS (SELECT count(address.id) AS count_1
@@ -1297,7 +1297,7 @@ can return ``user_account`` rows that have more than one related row in
HAVING count(address.id) > ?)
[...] (1,){stop}
[('sandy',)]
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
The EXISTS construct is more often than not used as a negation, e.g. NOT EXISTS,
as it provides a SQL-efficient form of locating rows for which a related
@@ -1313,7 +1313,7 @@ clause:
>>> with engine.connect() as conn:
... result = conn.execute(select(user_table.c.name).where(~subq))
... print(result.all())
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT user_account.name
FROM user_account
WHERE NOT (EXISTS (SELECT address.id
@@ -1321,7 +1321,7 @@ clause:
WHERE user_account.id = address.user_id))
[...] (){stop}
[('patrick',)]
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
.. _tutorial_functions:
@@ -1342,7 +1342,7 @@ possibly some arguments. Examples of typical SQL functions include:
.. sourcecode:: pycon+sql
>>> print(select(func.count()).select_from(user_table))
- SELECT count(*) AS count_1
+ {printsql}SELECT count(*) AS count_1
FROM user_account
..
@@ -1353,7 +1353,7 @@ possibly some arguments. Examples of typical SQL functions include:
.. sourcecode:: pycon+sql
>>> print(select(func.lower("A String With Much UPPERCASE")))
- SELECT lower(:lower_2) AS lower_1
+ {printsql}SELECT lower(:lower_2) AS lower_1
..
@@ -1367,7 +1367,7 @@ possibly some arguments. Examples of typical SQL functions include:
>>> with engine.connect() as conn:
... result = conn.execute(stmt)
... print(result.all())
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT CURRENT_TIMESTAMP AS now_1
[...] ()
[(datetime.datetime(...),)]
@@ -1381,7 +1381,7 @@ accepts. Any name that is accessed from this namespace is automatically
considered to be a SQL function that will render in a generic way::
>>> print(select(func.some_crazy_function(user_table.c.name, 17)))
- SELECT some_crazy_function(user_account.name, :some_crazy_function_2) AS some_crazy_function_1
+ {printsql}SELECT some_crazy_function(user_account.name, :some_crazy_function_2) AS some_crazy_function_1
FROM user_account
At the same time, a relatively small set of extremely common SQL functions such
@@ -1394,11 +1394,11 @@ the :class:`_functions.now` function::
>>> from sqlalchemy.dialects import postgresql
>>> print(select(func.now()).compile(dialect=postgresql.dialect()))
- SELECT now() AS now_1
+ {printsql}SELECT now() AS now_1
>>> from sqlalchemy.dialects import oracle
>>> print(select(func.now()).compile(dialect=oracle.dialect()))
- SELECT CURRENT_TIMESTAMP AS now_1 FROM DUAL
+ {printsql}SELECT CURRENT_TIMESTAMP AS now_1 FROM DUAL
Functions Have Return Types
~~~~~~~~~~~~~~~~~~~~~~~~~~~
@@ -1502,7 +1502,7 @@ operator for example will be correctly interpreted as the string concatenation
operator based on looking at both sides of the expression::
>>> print(select(func.upper("lowercase") + " suffix"))
- SELECT upper(:upper_1) || :upper_2 AS anon_1
+ {printsql}SELECT upper(:upper_1) || :upper_2 AS anon_1
Overall, the scenario where the
:paramref:`_functions.Function.type_` parameter is likely necessary is:
@@ -1579,7 +1579,7 @@ number the email addresses of individual users:
>>> with engine.connect() as conn: # doctest:+SKIP
... result = conn.execute(stmt)
... print(result.all())
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT row_number() OVER (PARTITION BY user_account.name) AS anon_1,
user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
@@ -1605,7 +1605,7 @@ We also may make use of the ``ORDER BY`` clause using :paramref:`_functions.Func
>>> with engine.connect() as conn: # doctest:+SKIP
... result = conn.execute(stmt)
... print(result.all())
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT count(*) OVER (ORDER BY user_account.name) AS anon_1,
user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
@@ -1662,13 +1662,13 @@ using the :meth:`_functions.FunctionElement.filter` method::
>>> with engine.connect() as conn: # doctest:+SKIP
... result = conn.execute(stmt)
... print(result.all())
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_1,
count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_2
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ('sandy', 'spongebob')
{stop}[(2, 1)]
- {opensql}ROLLBACK
+ {execsql}ROLLBACK
.. _tutorial_functions_table_valued:
@@ -1707,13 +1707,13 @@ modern versions of SQLite::
>>> with engine.connect() as conn:
... result = conn.execute(stmt)
... result.all()
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT anon_1.value
FROM json_each(?) AS anon_1
WHERE anon_1.value IN (?, ?)
[...] ('["one", "two", "three"]', 'two', 'three')
{stop}[('two',), ('three',)]
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
Above, we used the ``json_each()`` JSON function supported by SQLite and
PostgreSQL to generate a table valued expression with a single column referred
@@ -1744,7 +1744,7 @@ to a :class:`_functions.Function` construct::
>>> from sqlalchemy import select, func
>>> stmt = select(func.json_array_elements('["one", "two"]').column_valued("x"))
>>> print(stmt)
- SELECT x
+ {printsql}SELECT x
FROM json_array_elements(:json_array_elements_1) AS x
The "column valued" form is also supported by the Oracle dialect, where
@@ -1753,7 +1753,7 @@ it is usable for custom SQL functions::
>>> from sqlalchemy.dialects import oracle
>>> stmt = select(func.scalar_strings(5).column_valued("s"))
>>> print(stmt.compile(dialect=oracle.dialect()))
- SELECT s.COLUMN_VALUE
+ {printsql}SELECT s.COLUMN_VALUE
FROM TABLE (scalar_strings(:scalar_strings_1)) s
@@ -1781,12 +1781,12 @@ object::
>>> with engine.connect() as conn:
... result = conn.execute(stmt)
... result.all()
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT CAST(user_account.id AS VARCHAR) AS id
FROM user_account
[...] ()
{stop}[('1',), ('2',), ('3',)]
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
The :func:`.cast` function not only renders the SQL CAST syntax, it also
produces a SQLAlchemy column expression that will act as the given datatype on
@@ -1823,7 +1823,7 @@ string into one of MySQL's JSON functions:
>>> from sqlalchemy.dialects import mysql
>>> s = select(type_coerce({"some_key": {"foo": "bar"}}, JSON)["some_key"])
>>> print(s.compile(dialect=mysql.dialect()))
- SELECT JSON_EXTRACT(%s, %s) AS anon_1
+ {printsql}SELECT JSON_EXTRACT(%s, %s) AS anon_1
Above, MySQL's ``JSON_EXTRACT`` SQL function was invoked
because we used :func:`.type_coerce` to indicate that our Python dictionary
diff --git a/doc/build/tutorial/data_update.rst b/doc/build/tutorial/data_update.rst
index 38624d5f7..a82f070a3 100644
--- a/doc/build/tutorial/data_update.rst
+++ b/doc/build/tutorial/data_update.rst
@@ -61,7 +61,7 @@ A basic UPDATE looks like::
... .values(fullname="Patrick the Star")
... )
>>> print(stmt)
- {opensql}UPDATE user_account SET fullname=:fullname WHERE user_account.name = :name_1
+ {printsql}UPDATE user_account SET fullname=:fullname WHERE user_account.name = :name_1
The :meth:`_sql.Update.values` method controls the contents of the SET elements
of the UPDATE statement. This is the same method shared by the :class:`_sql.Insert`
@@ -73,7 +73,7 @@ where we can make use of :class:`_schema.Column` expressions::
>>> stmt = update(user_table).values(fullname="Username: " + user_table.c.name)
>>> print(stmt)
- {opensql}UPDATE user_account SET fullname=(:name_1 || user_account.name)
+ {printsql}UPDATE user_account SET fullname=(:name_1 || user_account.name)
To support UPDATE in an "executemany" context, where many parameter sets will
be invoked against the same statement, the :func:`_sql.bindparam`
@@ -97,7 +97,7 @@ that literal values would normally go:
... {"oldname": "jim", "newname": "jake"},
... ],
... )
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
UPDATE user_account SET name=? WHERE user_account.name = ?
[...] [('ed', 'jack'), ('mary', 'wendy'), ('jake', 'jim')]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
@@ -124,7 +124,7 @@ anywhere a column expression might be placed::
... )
>>> update_stmt = update(user_table).values(fullname=scalar_subq)
>>> print(update_stmt)
- {opensql}UPDATE user_account SET fullname=(SELECT address.email_address
+ {printsql}UPDATE user_account SET fullname=(SELECT address.email_address
FROM address
WHERE address.user_id = user_account.id ORDER BY address.id
LIMIT :param_1)
@@ -147,7 +147,7 @@ WHERE clause of the statement::
... .values(fullname="Pat")
... )
>>> print(update_stmt)
- {opensql}UPDATE user_account SET fullname=:fullname FROM address
+ {printsql}UPDATE user_account SET fullname=:fullname FROM address
WHERE user_account.id = address.user_id AND address.email_address = :email_address_1
@@ -168,7 +168,7 @@ order to refer to additional tables::
... )
>>> from sqlalchemy.dialects import mysql
>>> print(update_stmt.compile(dialect=mysql.dialect()))
- {opensql}UPDATE user_account, address
+ {printsql}UPDATE user_account, address
SET address.email_address=%s, user_account.fullname=%s
WHERE user_account.id = address.user_id AND address.email_address = %s
@@ -186,7 +186,7 @@ tuples so that this order may be controlled [2]_::
... (some_table.c.y, 20), (some_table.c.x, some_table.c.y + 10)
... )
>>> print(update_stmt)
- {opensql}UPDATE some_table SET y=:y, x=(some_table.y + :y_1)
+ {printsql}UPDATE some_table SET y=:y, x=(some_table.y + :y_1)
.. [2] While Python dictionaries are
@@ -215,7 +215,7 @@ allowing for a RETURNING variant on some database backends.
>>> from sqlalchemy import delete
>>> stmt = delete(user_table).where(user_table.c.name == "patrick")
>>> print(stmt)
- {opensql}DELETE FROM user_account WHERE user_account.name = :name_1
+ {printsql}DELETE FROM user_account WHERE user_account.name = :name_1
.. _tutorial_multi_table_deletes:
@@ -234,7 +234,7 @@ syntaxes, such as ``DELETE FROM..USING`` on MySQL::
... )
>>> from sqlalchemy.dialects import mysql
>>> print(delete_stmt.compile(dialect=mysql.dialect()))
- {opensql}DELETE FROM user_account USING user_account, address
+ {printsql}DELETE FROM user_account USING user_account, address
WHERE user_account.id = address.user_id AND address.email_address = %s
.. _tutorial_update_delete_rowcount:
@@ -257,11 +257,11 @@ is available from the :attr:`_engine.CursorResult.rowcount` attribute:
... .where(user_table.c.name == "patrick")
... )
... print(result.rowcount)
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
UPDATE user_account SET fullname=? WHERE user_account.name = ?
[...] ('Patrick McStar', 'patrick'){stop}
1
- {opensql}COMMIT{stop}
+ {execsql}COMMIT{stop}
.. tip::
@@ -315,7 +315,7 @@ be iterated::
... .returning(user_table.c.id, user_table.c.name)
... )
>>> print(update_stmt)
- {opensql}UPDATE user_account SET fullname=:fullname
+ {printsql}UPDATE user_account SET fullname=:fullname
WHERE user_account.name = :name_1
RETURNING user_account.id, user_account.name{stop}
@@ -325,7 +325,7 @@ be iterated::
... .returning(user_table.c.id, user_table.c.name)
... )
>>> print(delete_stmt)
- {opensql}DELETE FROM user_account
+ {printsql}DELETE FROM user_account
WHERE user_account.name = :name_1
RETURNING user_account.id, user_account.name{stop}
diff --git a/doc/build/tutorial/dbapi_transactions.rst b/doc/build/tutorial/dbapi_transactions.rst
index d40aaf5b8..d0cd09aa0 100644
--- a/doc/build/tutorial/dbapi_transactions.rst
+++ b/doc/build/tutorial/dbapi_transactions.rst
@@ -65,11 +65,11 @@ in more detail later:
>>> with engine.connect() as conn:
... result = conn.execute(text("select 'hello world'"))
... print(result.all())
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
select 'hello world'
[...] ()
{stop}[('hello world',)]
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
In the above example, the context manager provided for a database connection
and also framed the operation inside of a transaction. The default behavior of
@@ -110,7 +110,7 @@ where we acquired the :class:`_engine.Connection` object:
... [{"x": 1, "y": 1}, {"x": 2, "y": 4}],
... )
... conn.commit()
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
CREATE TABLE some_table (x int, y int)
[...] ()
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
@@ -147,7 +147,7 @@ may be referred towards as **begin once**:
... text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
... [{"x": 6, "y": 8}, {"x": 9, "y": 10}],
... )
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
INSERT INTO some_table (x, y) VALUES (?, ?)
[...] [(6, 8), (9, 10)]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
@@ -216,14 +216,14 @@ statement on the table we've created:
... result = conn.execute(text("SELECT x, y FROM some_table"))
... for row in result:
... print(f"x: {row.x} y: {row.y}")
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT x, y FROM some_table
[...] ()
{stop}x: 1 y: 1
x: 2 y: 4
x: 6 y: 8
x: 9 y: 10
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
Above, the "SELECT" string we executed selected all rows from our table.
The object returned is called :class:`_engine.Result` and represents an
@@ -319,13 +319,13 @@ construct accepts these using a colon format "``:y``". The actual value for
... result = conn.execute(text("SELECT x, y FROM some_table WHERE y > :y"), {"y": 2})
... for row in result:
... print(f"x: {row.x} y: {row.y}")
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT x, y FROM some_table WHERE y > ?
[...] (2,)
{stop}x: 2 y: 4
x: 6 y: 8
x: 9 y: 10
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
In the logged SQL output, we can see that the bound parameter ``:y`` was
@@ -370,7 +370,7 @@ of execution is known as :term:`executemany`:
... [{"x": 11, "y": 12}, {"x": 13, "y": 14}],
... )
... conn.commit()
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
INSERT INTO some_table (x, y) VALUES (?, ?)
[...] [(11, 12), (13, 14)]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
@@ -439,14 +439,14 @@ a context manager:
... result = session.execute(stmt, {"y": 6})
... for row in result:
... print(f"x: {row.x} y: {row.y}")
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT x, y FROM some_table WHERE y > ? ORDER BY x, y
[...] (6,){stop}
x: 6 y: 8
x: 9 y: 10
x: 11 y: 12
x: 13 y: 14
- {opensql}ROLLBACK{stop}
+ {execsql}ROLLBACK{stop}
The example above can be compared to the example in the preceding section
in :ref:`tutorial_sending_parameters` - we directly replace the call to
@@ -467,7 +467,7 @@ our data:
... [{"x": 9, "y": 11}, {"x": 13, "y": 15}],
... )
... session.commit()
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
UPDATE some_table SET y=? WHERE x=?
[...] [(11, 9), (15, 13)]
COMMIT{stop}
diff --git a/doc/build/tutorial/metadata.rst b/doc/build/tutorial/metadata.rst
index 7c05497ac..65047fd9c 100644
--- a/doc/build/tutorial/metadata.rst
+++ b/doc/build/tutorial/metadata.rst
@@ -213,7 +213,7 @@ sending it the :class:`_engine.Engine` that refers to the target database:
.. sourcecode:: pycon+sql
>>> metadata_obj.create_all(engine)
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
PRAGMA main.table_...info("user_account")
...
PRAGMA main.table_...info("address")
@@ -598,7 +598,7 @@ using the :paramref:`_schema.Table.autoload_with` parameter:
.. sourcecode:: pycon+sql
>>> some_table = Table("some_table", metadata_obj, autoload_with=engine)
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
PRAGMA main.table_...info("some_table")
[raw sql] ()
SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')
diff --git a/doc/build/tutorial/orm_data_manipulation.rst b/doc/build/tutorial/orm_data_manipulation.rst
index 3c4e55b3e..ab16c5856 100644
--- a/doc/build/tutorial/orm_data_manipulation.rst
+++ b/doc/build/tutorial/orm_data_manipulation.rst
@@ -123,7 +123,7 @@ method:
.. sourcecode:: pycon+sql
>>> session.flush()
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
INSERT INTO user_account (name, fullname) VALUES (?, ?), (?, ?) RETURNING id
[...] ('squidward', 'Squidward Tentacles', 'ehkrabs', 'Eugene H. Krabs')
@@ -255,7 +255,7 @@ as well as the :meth:`_engine.Result.scalar_one` method):
.. sourcecode:: pycon+sql
>>> sandy = session.execute(select(User).filter_by(name="sandy")).scalar_one()
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
@@ -288,7 +288,7 @@ from this row and we will get our updated value back:
.. sourcecode:: pycon+sql
>>> sandy_fullname = session.execute(select(User.fullname).where(User.id == 2)).scalar_one()
- {opensql}UPDATE user_account SET fullname=? WHERE user_account.id = ?
+ {execsql}UPDATE user_account SET fullname=? WHERE user_account.id = ?
[...] ('Sandy Squirrel', 2)
SELECT user_account.fullname
FROM user_account
@@ -333,7 +333,7 @@ Let's load up ``patrick`` from the database:
.. sourcecode:: pycon+sql
>>> patrick = session.get(User, 3)
- {opensql}SELECT user_account.id AS user_account_id, user_account.name AS user_account_name,
+ {execsql}SELECT user_account.id AS user_account_id, user_account.name AS user_account_name,
user_account.fullname AS user_account_fullname
FROM user_account
WHERE user_account.id = ?
@@ -350,7 +350,7 @@ until the flush proceeds, which as mentioned before occurs if we emit a query:
.. sourcecode:: pycon+sql
>>> session.execute(select(User).where(User.name == "patrick")).first()
- {opensql}SELECT address.id AS address_id, address.email_address AS address_email_address,
+ {execsql}SELECT address.id AS address_id, address.email_address AS address_email_address,
address.user_id AS address_user_id
FROM address
WHERE ? = address.user_id
@@ -460,7 +460,7 @@ a new transaction and refresh ``sandy`` with the current database row:
.. sourcecode:: pycon+sql
>>> sandy.fullname
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT user_account.id AS user_account_id, user_account.name AS user_account_name,
user_account.fullname AS user_account_fullname
FROM user_account
@@ -487,7 +487,7 @@ and of course the database data is present again as well:
.. sourcecode:: pycon+sql
>>> session.execute(select(User).where(User.name == "patrick")).scalar_one() is patrick
- {opensql}SELECT user_account.id, user_account.name, user_account.fullname
+ {execsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('patrick',){stop}
@@ -506,7 +506,7 @@ close out the :class:`_orm.Session` when we are done with it:
.. sourcecode:: pycon+sql
>>> session.close()
- {opensql}ROLLBACK
+ {execsql}ROLLBACK
Closing the :class:`_orm.Session`, which is what happens when we use it in
a context manager as well, accomplishes the following things:
@@ -542,7 +542,7 @@ a context manager as well, accomplishes the following things:
>>> session.add(squidward)
>>> squidward.name
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT user_account.id AS user_account_id, user_account.name AS user_account_name, user_account.fullname AS user_account_fullname
FROM user_account
WHERE user_account.id = ?
diff --git a/doc/build/tutorial/orm_related_objects.rst b/doc/build/tutorial/orm_related_objects.rst
index a23c3369b..bd1fae131 100644
--- a/doc/build/tutorial/orm_related_objects.rst
+++ b/doc/build/tutorial/orm_related_objects.rst
@@ -196,7 +196,7 @@ newly generated primary key of the ``user_account`` row is applied to the
.. sourcecode:: pycon+sql
>>> session.commit()
- {opensql}INSERT INTO user_account (name, fullname) VALUES (?, ?)
+ {execsql}INSERT INTO user_account (name, fullname) VALUES (?, ?)
[...] ('pkrabs', 'Pearl Krabs')
INSERT INTO address (email_address, user_id) VALUES (?, ?), (?, ?) RETURNING id
[...] ('pearl.krabs@gmail.com', 6, 'pearl@aol.com', 6)
@@ -220,7 +220,7 @@ newly generated primary key for the ``u1`` object:
.. sourcecode:: pycon+sql
>>> u1.id
- {opensql}BEGIN (implicit)
+ {execsql}BEGIN (implicit)
SELECT user_account.id AS user_account_id, user_account.name AS user_account_name,
user_account.fullname AS user_account_fullname
FROM user_account
@@ -236,7 +236,7 @@ we again see a :term:`lazy load` emitted in order to retrieve the objects:
.. sourcecode:: pycon+sql
>>> u1.addresses
- {opensql}SELECT address.id AS address_id, address.email_address AS address_email_address,
+ {execsql}SELECT address.id AS address_id, address.email_address AS address_email_address,
address.user_id AS address_user_id
FROM address
WHERE ? = address.user_id
@@ -305,7 +305,7 @@ argument** to :meth:`_sql.Select.join`, where it serves to indicate both the
right side of the join as well as the ON clause at once::
>>> print(select(Address.email_address).select_from(User).join(User.addresses))
- {opensql}SELECT address.email_address
+ {printsql}SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
The presence of an ORM :func:`_orm.relationship` on a mapping is not used
@@ -317,7 +317,7 @@ between the two mapped :class:`_schema.Table` objects, not because of the
:func:`_orm.relationship` objects on the ``User`` and ``Address`` classes::
>>> print(select(Address.email_address).join_from(User, Address))
- {opensql}SELECT address.email_address
+ {printsql}SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
See the section :ref:`orm_queryguide_joins` in the :ref:`queryguide_toplevel`
@@ -447,7 +447,7 @@ related ``Address`` objects:
... print(
... f"{row.User.name} ({', '.join(a.email_address for a in row.User.addresses)})"
... )
- {opensql}SELECT user_account.id, user_account.name, user_account.fullname
+ {execsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()
SELECT address.user_id AS address_user_id, address.id AS address_id,
@@ -492,7 +492,7 @@ as below where we know that all ``Address`` objects have an associated
... )
>>> for row in session.execute(stmt):
... print(f"{row.Address.email_address} {row.Address.user.name}")
- {opensql}SELECT address.id, address.email_address, address.user_id, user_account_1.id AS id_1,
+ {execsql}SELECT address.id, address.email_address, address.user_id, user_account_1.id AS id_1,
user_account_1.name, user_account_1.fullname
FROM address
JOIN user_account AS user_account_1 ON user_account_1.id = address.user_id
@@ -563,7 +563,7 @@ example:
... )
>>> for row in session.execute(stmt):
... print(f"{row.Address.email_address} {row.Address.user.name}")
- {opensql}SELECT user_account.id, user_account.name, user_account.fullname,
+ {execsql}SELECT user_account.id, user_account.name, user_account.fullname,
address.id AS id_1, address.email_address, address.user_id
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = ? ORDER BY address.id
@@ -584,7 +584,7 @@ SQL query that unnecessarily joins twice::
... .order_by(Address.id)
... )
>>> print(stmt) # SELECT has a JOIN and LEFT OUTER JOIN unnecessarily
- {opensql}SELECT address.id, address.email_address, address.user_id,
+ {printsql}SELECT address.id, address.email_address, address.user_id,
user_account_1.id AS id_1, user_account_1.name, user_account_1.fullname
FROM address JOIN user_account ON user_account.id = address.user_id
LEFT OUTER JOIN user_account AS user_account_1 ON user_account_1.id = address.user_id
@@ -644,7 +644,7 @@ Using such a mapping, the application is blocked from lazy loading,
indicating that a particular query would need to specify a loader strategy::
>>> u1 = session.execute(select(User)).scalars().first()
- {opensql}SELECT user_account.id FROM user_account
+ {execsql}SELECT user_account.id FROM user_account
[...] ()
{stop}>>> u1.addresses
Traceback (most recent call last):
@@ -660,7 +660,7 @@ instead::
... .scalars()
... .first()
... )
- {opensql}SELECT user_account.id
+ {execsql}SELECT user_account.id
FROM user_account
[...] ()
SELECT address.user_id AS address_user_id, address.id AS address_id
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index d373b3a44..a6a12f451 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 1a0534490..50e0ec07e 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 f3e98caa0..d47a037c4 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 c2c08b312..e46443a74 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 f27e721c2..baedc42c4 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 1d8818a1f..6e5a7bc5e 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 355a00c60..1ab7f2ceb 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 446cbcd3c..902811037 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 898b524ad..1d283e83c 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 bcdbcc804..bcbc7004c 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 9c2fb2d72..b920f25f0 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 6ef7a3080..3b11c24a8 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(