Skip to content

Commit dce1138

Browse files
CaselITCaselIT
committed
Improve sql formatting
change {opensql} to {printsql} in prints, add missing markers Change-Id: I07b72e6620bb64e329d6b641afa27631e91c4f16
1 parent a950402 commit dce1138

57 files changed

Lines changed: 483 additions & 489 deletions

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

‎doc/build/changelog/migration_08.rst‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -755,7 +755,7 @@ whenever the ``test_table.c.data`` column is rendered in the columns
755755
clause of a SELECT statement::
756756

757757
>>> print(select([test_table]).where(test_table.c.data == "HI"))
758-
SELECT lower(test_table.data) AS data
758+
{printsql}SELECT lower(test_table.data) AS data
759759
FROM test_table
760760
WHERE test_table.data = lower(:data_1)
761761

@@ -955,7 +955,7 @@ when features such as :meth:`_schema.MetaData.create_all` and :func:`.cast` is u
955955

956956
>>> stmt = select([cast(sometable.c.somechar, String(20, collation="utf8"))])
957957
>>> print(stmt)
958-
SELECT CAST(sometable.somechar AS VARCHAR(20) COLLATE "utf8") AS anon_1
958+
{printsql}SELECT CAST(sometable.somechar AS VARCHAR(20) COLLATE "utf8") AS anon_1
959959
FROM sometable
960960

961961
.. seealso::

‎doc/build/changelog/migration_09.rst‎

Lines changed: 9 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -655,7 +655,7 @@ signs within the enumerated values::
655655
>>> type = postgresql.ENUM("one", "two", "three's", name="myenum")
656656
>>> from sqlalchemy.dialects.postgresql import base
657657
>>> print(base.CreateEnumType(type).compile(dialect=postgresql.dialect()))
658-
CREATE TYPE myenum AS ENUM ('one','two','three''s')
658+
{printsql}CREATE TYPE myenum AS ENUM ('one','two','three''s')
659659

660660
Existing workarounds which already escape single quote signs will need to be
661661
modified, else they will now double-escape.
@@ -894,7 +894,7 @@ where it will be used to render an ``INSERT .. SELECT`` construct::
894894
>>> t1 = table("t1", column("a"), column("b"))
895895
>>> t2 = table("t2", column("x"), column("y"))
896896
>>> print(t1.insert().from_select(["a", "b"], t2.select().where(t2.c.y == 5)))
897-
INSERT INTO t1 (a, b) SELECT t2.x, t2.y
897+
{printsql}INSERT INTO t1 (a, b) SELECT t2.x, t2.y
898898
FROM t2
899899
WHERE t2.y = :y_1
900900

@@ -1603,7 +1603,7 @@ on backends that don't feature ``true``/``false`` constant behavior::
16031603
>>> from sqlalchemy.dialects import mysql, postgresql
16041604

16051605
>>> print(select([t1]).where(t1.c.x).compile(dialect=mysql.dialect()))
1606-
SELECT t.x, t.y FROM t WHERE t.x = 1
1606+
{printsql}SELECT t.x, t.y FROM t WHERE t.x = 1
16071607

16081608
The :func:`.and_` and :func:`.or_` constructs will now exhibit quasi
16091609
"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
16121612
>>> print(
16131613
... select([t1]).where(and_(t1.c.y > 5, false())).compile(dialect=postgresql.dialect())
16141614
... )
1615-
SELECT t.x, t.y FROM t WHERE false
1615+
{printsql}SELECT t.x, t.y FROM t WHERE false
16161616

16171617
:func:`.true` can be used as the base to build up an expression::
16181618

16191619
>>> expr = true()
16201620
>>> expr = expr & (t1.c.y > 5)
16211621
>>> print(select([t1]).where(expr))
1622-
SELECT t.x, t.y FROM t WHERE t.y > :y_1
1622+
{printsql}SELECT t.x, t.y FROM t WHERE t.y > :y_1
16231623

16241624
The boolean constants :func:`.true` and :func:`.false` themselves render as
16251625
``0 = 1`` and ``1 = 1`` for a backend with no boolean constants::
16261626

16271627
>>> print(select([t1]).where(and_(t1.c.y > 5, false())).compile(dialect=mysql.dialect()))
1628-
SELECT t.x, t.y FROM t WHERE 0 = 1
1628+
{printsql}SELECT t.x, t.y FROM t WHERE 0 = 1
16291629

16301630
Interpretation of ``None``, while not particularly valid SQL, is at least
16311631
now consistent::
16321632

16331633
>>> print(select([t1.c.x]).where(None))
1634-
SELECT t.x FROM t WHERE NULL
1634+
{printsql}SELECT t.x FROM t WHERE NULL
16351635

16361636
>>> print(select([t1.c.x]).where(None).where(None))
1637-
SELECT t.x FROM t WHERE NULL AND NULL
1637+
{printsql}SELECT t.x FROM t WHERE NULL AND NULL
16381638

16391639
>>> print(select([t1.c.x]).where(and_(None, None)))
1640-
SELECT t.x FROM t WHERE NULL AND NULL
1640+
{printsql}SELECT t.x FROM t WHERE NULL AND NULL
16411641

16421642
:ticket:`2804`
16431643

‎doc/build/changelog/migration_11.rst‎

Lines changed: 6 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1197,8 +1197,8 @@ statement::
11971197
... ),
11981198
... )
11991199
>>>
1200-
>>> print(insert) # note formatting added for clarity
1201-
WITH upsert AS
1200+
>>> print(insert) # Note: formatting added for clarity
1201+
{printsql}WITH upsert AS
12021202
(UPDATE orders SET amount=:amount, product=:product, quantity=:quantity
12031203
WHERE orders.region = :region_1
12041204
RETURNING orders.region, orders.amount, orders.product, orders.quantity
@@ -1265,7 +1265,7 @@ selectable, e.g. lateral correlation::
12651265
... .lateral("book_subq")
12661266
... )
12671267
>>> print(select([people]).select_from(people.join(subq, true())))
1268-
SELECT people.people_id, people.age, people.name
1268+
{printsql}SELECT people.people_id, people.age, people.name
12691269
FROM people JOIN LATERAL (SELECT books.book_id AS book_id
12701270
FROM books WHERE books.owner_id = people.people_id)
12711271
AS book_subq ON true
@@ -2058,7 +2058,7 @@ datatypes::
20582058
>>> from sqlalchemy import table, column
20592059
t>>> t = table('x', column('a'), column('b'))
20602060
>>> print(t.insert().returning(t.c.a, t.c.b))
2061-
INSERT INTO x (a, b) VALUES (:a, :b) RETURNING x.a, x.b
2061+
{printsql}INSERT INTO x (a, b) VALUES (:a, :b) RETURNING x.a, x.b
20622062

20632063
The ``str()`` function now calls upon an entirely separate dialect / compiler
20642064
intended just for plain string printing without a specific dialect set up,
@@ -2374,8 +2374,7 @@ passed through the literal quoting system::
23742374
>>> from sqlalchemy.types import String
23752375
>>> t = Table("t", MetaData(), Column("x", String(), server_default="hi ' there"))
23762376
>>> print(CreateTable(t))
2377-
2378-
CREATE TABLE t (
2377+
{printsql}CREATE TABLE t (
23792378
x VARCHAR DEFAULT 'hi '' there'
23802379
)
23812380

@@ -2994,7 +2993,7 @@ name into an alias::
29942993

29952994
>>> eng = create_engine("mssql+pymssql://mydsn", legacy_schema_aliasing=True)
29962995
>>> print(account_table.select().compile(eng))
2997-
SELECT account_1.id, account_1.info
2996+
{printsql}SELECT account_1.id, account_1.info
29982997
FROM customer_schema.account AS account_1
29992998

30002999
However, this aliasing has been shown to be unnecessary and in many cases

‎doc/build/changelog/migration_12.rst‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -189,7 +189,7 @@ are loaded with additional SELECT statements:
189189
... )
190190

191191
>>> query.all()
192-
{opensql}SELECT
192+
{execsql}SELECT
193193
employee.id AS employee_id,
194194
employee.name AS employee_name,
195195
employee.type AS employee_type
@@ -578,7 +578,7 @@ query across the two proxies ``A.b_values``, ``AtoB.b_value``:
578578
.. sourcecode:: pycon+sql
579579

580580
>>> s.query(A).filter(A.b_values.contains("hi")).all()
581-
{opensql}SELECT a.id AS a_id
581+
{execsql}SELECT a.id AS a_id
582582
FROM a
583583
WHERE EXISTS (SELECT 1
584584
FROM atob
@@ -592,7 +592,7 @@ to query across the two proxies ``A.c_values``, ``AtoB.c_value``:
592592
.. sourcecode:: pycon+sql
593593

594594
>>> s.query(A).filter(A.c_values.any(value="x")).all()
595-
{opensql}SELECT a.id AS a_id
595+
{execsql}SELECT a.id AS a_id
596596
FROM a
597597
WHERE EXISTS (SELECT 1
598598
FROM atob
@@ -1036,7 +1036,7 @@ are named in the documentation now::
10361036
... )
10371037
... )
10381038
>>> print(stmt)
1039-
SELECT sum(t.value) AS sum_1
1039+
{printsql}SELECT sum(t.value) AS sum_1
10401040
FROM t GROUP BY GROUPING SETS((t.x, t.y), (t.z, t.q))
10411041

10421042
:ticket:`3429`

‎doc/build/changelog/migration_13.rst‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -533,7 +533,7 @@ The ``User.values`` association proxy refers to the ``Element.value`` column.
533533
Standard column operations are now available, such as ``like``::
534534

535535
>>> print(s.query(User).filter(User.values.like("%foo%")))
536-
SELECT "user".id AS user_id
536+
{printsql}SELECT "user".id AS user_id
537537
FROM "user"
538538
WHERE EXISTS (SELECT 1
539539
FROM element
@@ -542,7 +542,7 @@ Standard column operations are now available, such as ``like``::
542542
``equals``::
543543

544544
>>> print(s.query(User).filter(User.values == "foo"))
545-
SELECT "user".id AS user_id
545+
{printsql}SELECT "user".id AS user_id
546546
FROM "user"
547547
WHERE EXISTS (SELECT 1
548548
FROM element
@@ -553,7 +553,7 @@ a test that the related row does not exist at all; this is the same
553553
behavior as before::
554554

555555
>>> print(s.query(User).filter(User.values == None))
556-
SELECT "user".id AS user_id
556+
{printsql}SELECT "user".id AS user_id
557557
FROM "user"
558558
WHERE (EXISTS (SELECT 1
559559
FROM element
@@ -567,7 +567,7 @@ the association proxy used ``.contains`` as a list containment operator only.
567567
With a column-oriented comparison, it now behaves like a "like"::
568568

569569
>>> print(s.query(User).filter(User.values.contains("foo")))
570-
SELECT "user".id AS user_id
570+
{printsql}SELECT "user".id AS user_id
571571
FROM "user"
572572
WHERE EXISTS (SELECT 1
573573
FROM element
@@ -1234,7 +1234,7 @@ backend, such as "SELECT CAST(NULL AS INTEGER) WHERE 1!=1" for PostgreSQL,
12341234
... ),
12351235
... q=[],
12361236
... )
1237-
SELECT 1 WHERE 1 IN (SELECT CAST(NULL AS INTEGER) WHERE 1!=1)
1237+
{exexsql}SELECT 1 WHERE 1 IN (SELECT CAST(NULL AS INTEGER) WHERE 1!=1)
12381238

12391239
The feature also works for tuple-oriented IN statements, where the "empty IN"
12401240
expression will be expanded to support the elements given inside the tuple,
@@ -1250,7 +1250,7 @@ such as on PostgreSQL::
12501250
... ),
12511251
... q=[],
12521252
... )
1253-
SELECT 1 WHERE (%(param_1)s, %(param_2)s)
1253+
{exexsql}SELECT 1 WHERE (%(param_1)s, %(param_2)s)
12541254
IN (SELECT CAST(NULL AS INTEGER), CAST(NULL AS VARCHAR) WHERE 1!=1)
12551255

12561256

‎doc/build/changelog/migration_14.rst‎

Lines changed: 12 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1071,22 +1071,22 @@ an IN expression::
10711071
The pre-execution string representation is::
10721072

10731073
>>> print(stmt)
1074-
SELECT a.id, a.data
1074+
{printsql}SELECT a.id, a.data
10751075
FROM a
10761076
WHERE a.id IN ([POSTCOMPILE_id_1])
10771077

10781078
To render the values directly, use ``literal_binds`` as was the case previously::
10791079

10801080
>>> print(stmt.compile(compile_kwargs={"literal_binds": True}))
1081-
SELECT a.id, a.data
1081+
{printsql}SELECT a.id, a.data
10821082
FROM a
10831083
WHERE a.id IN (1, 2, 3)
10841084

10851085
A new flag, "render_postcompile", is added as a helper to allow the current
10861086
bound value to be rendered as it would be passed to the database::
10871087

10881088
>>> print(stmt.compile(compile_kwargs={"render_postcompile": True}))
1089-
SELECT a.id, a.data
1089+
{printsql}SELECT a.id, a.data
10901090
FROM a
10911091
WHERE a.id IN (:id_1_1, :id_1_2, :id_1_3)
10921092

@@ -1260,7 +1260,7 @@ method will not emit a warning unless the linting flag is supplied::
12601260
>>> from sqlalchemy.sql import FROM_LINTING
12611261
>>> print(q.statement.compile(linting=FROM_LINTING))
12621262
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.
1263-
SELECT users.id, users.name, users.fullname, users.nickname
1263+
{printsql}SELECT users.id, users.name, users.fullname, users.nickname
12641264
FROM addresses, users JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
12651265
WHERE addresses.email_address = :email_address_1
12661266

@@ -1538,7 +1538,7 @@ such as :class:`.Subquery` and :class:`_expression.Alias`::
15381538
]
15391539

15401540
>>> print(stmt.subquery().select())
1541-
SELECT anon_1.c1, anon_1.c2, anon_1.c2, anon_1.c2, anon_1.c4
1541+
{printsql}SELECT anon_1.c1, anon_1.c2, anon_1.c2, anon_1.c2, anon_1.c4
15421542
FROM (SELECT c1, c2, c3 AS c2, c2, c4) AS anon_1
15431543

15441544
:class:`_expression.ColumnCollection` also allows access by integer index to support
@@ -1576,7 +1576,7 @@ as::
15761576
>>> from sqlalchemy import union
15771577
>>> u = union(s1, s2)
15781578
>>> print(u)
1579-
SELECT "user".id, "user".name, "user".id
1579+
{printsql}SELECT "user".id, "user".name, "user".id
15801580
FROM "user" UNION SELECT c1, c2, c3
15811581

15821582

@@ -1642,29 +1642,29 @@ reasonable behavior for simple modifications to a single column, most
16421642
prominently with CAST::
16431643

16441644
>>> print(select(cast(foo.c.data, String)))
1645-
SELECT CAST(foo.data AS VARCHAR) AS data
1645+
{printsql}SELECT CAST(foo.data AS VARCHAR) AS data
16461646
FROM foo
16471647

16481648
For CAST against expressions that don't have a name, the previous logic is used
16491649
to generate the usual "anonymous" labels::
16501650

16511651
>>> print(select(cast("hi there," + foo.c.data, String)))
1652-
SELECT CAST(:data_1 + foo.data AS VARCHAR) AS anon_1
1652+
{printsql}SELECT CAST(:data_1 + foo.data AS VARCHAR) AS anon_1
16531653
FROM foo
16541654

16551655
A :func:`.cast` against a :class:`.Label`, despite having to omit the label
16561656
expression as these don't render inside of a CAST, will nonetheless make use of
16571657
the given name::
16581658

16591659
>>> print(select(cast(("hi there," + foo.c.data).label("hello_data"), String)))
1660-
SELECT CAST(:data_1 + foo.data AS VARCHAR) AS hello_data
1660+
{printsql}SELECT CAST(:data_1 + foo.data AS VARCHAR) AS hello_data
16611661
FROM foo
16621662

16631663
And of course as was always the case, :class:`.Label` can be applied to the
16641664
expression on the outside to apply an "AS <name>" label directly::
16651665

16661666
>>> print(select(cast(("hi there," + foo.c.data), String).label("hello_data")))
1667-
SELECT CAST(:data_1 + foo.data AS VARCHAR) AS hello_data
1667+
{printsql}SELECT CAST(:data_1 + foo.data AS VARCHAR) AS hello_data
16681668
FROM foo
16691669

16701670

@@ -2291,7 +2291,7 @@ the ``.data`` column attribute, the object is refreshed and this will now
22912291
include the joinedload operation as well::
22922292

22932293
>>> a1.data
2294-
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
2294+
{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
22952295
FROM a LEFT OUTER JOIN b AS b_1 ON a.id = b_1.a_id
22962296
WHERE a.id = ?
22972297

@@ -2310,7 +2310,7 @@ an additional query::
23102310
>>> a1.data = "new data"
23112311
>>> session.commit()
23122312
>>> a1.data
2313-
SELECT a.id AS a_id, a.data AS a_data
2313+
{execsql}SELECT a.id AS a_id, a.data AS a_data
23142314
FROM a
23152315
WHERE a.id = ?
23162316
(1,)

‎doc/build/changelog/whatsnew_20.rst‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1831,7 +1831,7 @@ simple ``CREATE SEQUENCE`` DDL, if no additional arguments were specified::
18311831
>>> from sqlalchemy import Sequence
18321832
>>> from sqlalchemy.schema import CreateSequence
18331833
>>> print(CreateSequence(Sequence("my_seq")))
1834-
CREATE SEQUENCE my_seq
1834+
{printsql}CREATE SEQUENCE my_seq
18351835

18361836
However, as :class:`.Sequence` support was added for MS SQL Server, where the
18371837
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
18421842
>>> from sqlalchemy import Sequence
18431843
>>> from sqlalchemy.schema import CreateSequence
18441844
>>> print(CreateSequence(Sequence("my_seq")))
1845-
CREATE SEQUENCE my_seq START WITH 1
1845+
{printsql}CREATE SEQUENCE my_seq START WITH 1
18461846

18471847
This change has introduced other complexities, including that when
18481848
the :paramref:`.Sequence.min_value` parameter is included, this default of

‎doc/build/core/connections.rst‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1475,10 +1475,10 @@ Basic guidelines include:
14751475
>>> with engine.connect() as conn:
14761476
... print(conn.scalar(my_stmt(5, 10)))
14771477
... print(conn.scalar(my_stmt(12, 8)))
1478-
{opensql}SELECT max(?, ?) AS max_1
1478+
{execsql}SELECT max(?, ?) AS max_1
14791479
[generated in 0.00057s] (5, 10){stop}
14801480
10
1481-
{opensql}SELECT max(?, ?) AS max_1
1481+
{execsql}SELECT max(?, ?) AS max_1
14821482
[cached since 0.002059s ago] (12, 8){stop}
14831483
12
14841484

0 commit comments

Comments
 (0)