From 3988071d0d158081dca0a683a1233959c8203439 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 15 Dec 2025 14:53:48 -0500 Subject: [PATCH] update default driver for oracle, postgresql The default DBAPI driver for the Oracle Database dialect has been changed to ``oracledb`` instead of ``cx_oracle``. The ``cx_oracle`` driver remains fully supported and can be explicitly specified in the connection URL using ``oracle+cx_oracle://``. The default DBAPI driver for the PostgreSQL dialect has been changed to ``psycopg`` (psycopg version 3) instead of ``psycopg2``. The ``psycopg2`` driver remains fully supported and can be explicitly specified in the connection URL using ``postgresql+psycopg2://``. Fixes: #13010 Change-Id: Ie75810f4c3af609d20da63289d2662dfa2385ca2 --- doc/build/changelog/migration_21.rst | 94 ++ doc/build/changelog/unreleased_21/13010.rst | 34 + doc/build/core/operators.rst | 6 +- doc/build/dialects/oracle.rst | 8 + doc/build/dialects/postgresql.rst | 20 +- doc/build/tutorial/data_update.rst | 5 +- lib/sqlalchemy/dialects/oracle/__init__.py | 2 +- lib/sqlalchemy/dialects/oracle/provision.py | 9 - .../dialects/postgresql/__init__.py | 2 +- noxfile.py | 4 +- pyproject.toml | 2 +- setup.cfg | 2 +- test/dialect/postgresql/test_compiler.py | 878 +++++++++--------- test/dialect/postgresql/test_types.py | 353 ++++--- test/engine/test_deprecations.py | 2 +- test/engine/test_parseconnect.py | 6 +- test/engine/test_pool.py | 2 +- test/orm/dml/test_orm_upd_del_basic.py | 47 +- test/orm/dml/test_orm_upd_del_inheritance.py | 20 +- test/orm/test_defaults.py | 8 +- test/orm/test_query.py | 2 +- test/orm/test_relationship_criteria.py | 50 +- test/orm/test_unitofworkv2.py | 90 +- test/sql/test_compiler.py | 2 +- test/sql/test_cte.py | 2 +- test/sql/test_functions.py | 6 +- test/sql/test_insert.py | 147 +-- test/sql/test_operators.py | 86 +- test/sql/test_returning.py | 17 +- test/sql/test_text.py | 3 +- test/sql/test_types.py | 12 +- 31 files changed, 1095 insertions(+), 826 deletions(-) create mode 100644 doc/build/changelog/unreleased_21/13010.rst diff --git a/doc/build/changelog/migration_21.rst b/doc/build/changelog/migration_21.rst index bec10d8c19..78abbd4cee 100644 --- a/doc/build/changelog/migration_21.rst +++ b/doc/build/changelog/migration_21.rst @@ -1187,6 +1187,53 @@ raise an error, directing users to use :class:`_sql.FrameClause` instead. PostgreSQL ========== +.. _change_13010_postgresql: + +Default PostgreSQL driver changed to psycopg (psycopg 3) +--------------------------------------------------------- + +The default DBAPI driver for the PostgreSQL dialect has been changed from +``psycopg2`` to ``psycopg`` (psycopg version 3). When using a connection URL +of the form ``postgresql://user:pass@host/dbname``, SQLAlchemy will now +attempt to use the ``psycopg`` driver by default. + +The ``psycopg`` (version 3) driver is the modernized successor to +``psycopg2``, featuring improved performance when built with C extensions, +better support for modern PostgreSQL features, and native async support via +the ``psycopg_async`` dialect. The performance characteristics of ``psycopg`` +with C extensions are comparable to ``psycopg2``. + +The ``psycopg2`` driver remains fully supported and can be used by explicitly +specifying it in the connection URL. + +Examples to summarize the change are as follows:: + + # omit the driver portion, will use the psycopg dialect + engine = create_engine("postgresql://user:pass@host/dbname") + + # indicate the psycopg driver/dialect explcitly (preferred) + engine = create_engine("postgresql+psycopg://user:pass@host/dbname") + + # use the legacy psycopg2 driver/dialect + engine = create_engine("postgresql+psycopg2://user:pass@host/dbname") + +The ``psycopg`` DBAPI driver itself can be installed either directly +or via the ``sqlalchemy[postgresql]`` extra:: + +.. sourcecode:: txt + + # install psycopg directly + pip install "psycopg[binary]" + + # or use SQLAlchemy's postgresql extra (now installs psycopg) + pip install sqlalchemy[postgresql] + +.. seealso:: + + :ref:`postgresql_psycopg` - Documentation for the psycopg 3 dialect + +:ticket:`13010` + .. _change_10594_postgresql: Changes to Named Type Handling in PostgreSQL @@ -1471,6 +1518,53 @@ required if using the connection string directly with ``pyodbc.connect()``). Oracle Database =============== +.. _change_13010_oracle: + +Default Oracle driver changed to python-oracledb +------------------------------------------------- + +The default DBAPI driver for the Oracle dialect has been changed from +``cx_oracle`` to ``oracledb`` (python-oracledb). When using a connection URL +of the form ``oracle://user:pass@host/dbname``, SQLAlchemy will now attempt +to use the ``oracledb`` driver by default. + +The ``oracledb`` driver is the modernized successor to ``cx_oracle``, +actively maintained by Oracle with improved performance characteristics and +ongoing feature development. The documentation for ``cx_oracle`` has been +largely replaced with references to ``oracledb`` at +https://cx-oracle.readthedocs.io/. + +The ``cx_oracle`` driver remains fully supported and can be used by +explicitly specifying it in the connection URL. + +Examples to summarize the change are as follows:: + + # omit the driver portion, will use the oracledb dialect + engine = create_engine("oracle://user:pass@host/dbname") + + # indicate the oracledb driver/dialect explcitly (preferred) + engine = create_engine("oracle+oracledb://user:pass@host/dbname") + + # use the legacy cx_oracle driver/dialect + engine = create_engine("oracle+cx_oracle://user:pass@host/dbname") + +The ``oracledb`` DBAPI driver itself can be installed either directly +or via the ``sqlalchemy[oracle]`` extra: + +.. sourcecode:: txt + + # install oracledb directly + pip install oracledb + + # or use SQLAlchemy's oracle extra (now installs oracledb) + pip install sqlalchemy[oracle] + +.. seealso:: + + :ref:`oracledb` - Documentation for the python-oracledb dialect + +:ticket:`13010` + .. _change_11633: Native :class:`.BOOLEAN` support for Oracle 23c and above diff --git a/doc/build/changelog/unreleased_21/13010.rst b/doc/build/changelog/unreleased_21/13010.rst new file mode 100644 index 0000000000..c43a810788 --- /dev/null +++ b/doc/build/changelog/unreleased_21/13010.rst @@ -0,0 +1,34 @@ +.. change:: + :tags: usecase, oracle + :tickets: 13010 + + The default DBAPI driver for the Oracle Database dialect has been changed + to ``oracledb`` instead of ``cx_oracle``. The ``cx_oracle`` driver remains + fully supported and can be explicitly specified in the connection URL + using ``oracle+cx_oracle://``. + + The ``oracledb`` driver is a modernized version of ``cx_oracle`` with + better performance characteristics and ongoing active development from + Oracle. + + .. seealso:: + + :ref:`change_13010_oracle` + + +.. change:: + :tags: usecase, postgresql + :tickets: 13010 + + The default DBAPI driver for the PostgreSQL dialect has been changed to + ``psycopg`` (psycopg version 3) instead of ``psycopg2``. The ``psycopg2`` + driver remains fully supported and can be explicitly specified in the + connection URL using ``postgresql+psycopg2://``. + + The ``psycopg`` (version 3) driver includes improvements over ``psycopg2`` + including better performance when using C extensions and native support + for async operations. + + .. seealso:: + + :ref:`change_13010_postgresql` diff --git a/doc/build/core/operators.rst b/doc/build/core/operators.rst index b21953200e..d6493a4d63 100644 --- a/doc/build/core/operators.rst +++ b/doc/build/core/operators.rst @@ -427,7 +427,7 @@ behaviors and results on different databases: >>> from sqlalchemy.dialects import postgresql >>> print(column("x").regexp_match("word").compile(dialect=postgresql.dialect())) - {printsql}x ~ %(x_1)s + {printsql}x ~ %(x_1)s::VARCHAR Or MySQL:: @@ -471,7 +471,7 @@ String Alteration REPLACE equivalent for the backends which support it:: >>> print(column("x").regexp_replace("foo", "bar").compile(dialect=postgresql.dialect())) - {printsql}REGEXP_REPLACE(x, %(x_1)s, %(x_2)s) + {printsql}REGEXP_REPLACE(x, %(x_1)s::VARCHAR, %(x_2)s::VARCHAR) .. @@ -638,7 +638,7 @@ boolean operators. >>> from sqlalchemy.dialects import postgresql >>> print(column("x").bitwise_xor(5).compile(dialect=postgresql.dialect())) - x # %(x_1)s + x # %(x_1)s::INTEGER .. diff --git a/doc/build/dialects/oracle.rst b/doc/build/dialects/oracle.rst index 8601b81158..369e0d1d77 100644 --- a/doc/build/dialects/oracle.rst +++ b/doc/build/dialects/oracle.rst @@ -110,6 +110,10 @@ construction arguments, are as follows: python-oracledb --------------- +.. versionchanged:: 2.1 + ``oracledb`` is now the default Oracle dialect when no specific dialect + is specified in the URL (e.g. ``oracle://...``). + .. automodule:: sqlalchemy.dialects.oracle.oracledb .. _cx_oracle: @@ -117,4 +121,8 @@ python-oracledb cx_Oracle --------- +.. versionchanged:: 2.1 + ``cx_oracle`` is no longer the default Oracle dialect. To explicitly use + ``cx_oracle``, specify ``oracle+cx_oracle://...`` in the URL. + .. automodule:: sqlalchemy.dialects.oracle.cx_oracle diff --git a/doc/build/dialects/postgresql.rst b/doc/build/dialects/postgresql.rst index 8e35a73acd..79a04b3046 100644 --- a/doc/build/dialects/postgresql.rst +++ b/doc/build/dialects/postgresql.rst @@ -644,19 +644,27 @@ PostgreSQL DML Constructs .. autoclass:: sqlalchemy.dialects.postgresql.Insert :members: -.. _postgresql_psycopg2: +.. _postgresql_psycopg: -psycopg2 +psycopg -------- -.. automodule:: sqlalchemy.dialects.postgresql.psycopg2 +.. versionchanged:: 2.1 + ``psycopg`` (psycopg 3) is now the default PostgreSQL dialect when no + specific dialect is specified in the URL (e.g. ``postgresql://...``). -.. _postgresql_psycopg: +.. automodule:: sqlalchemy.dialects.postgresql.psycopg -psycopg +.. _postgresql_psycopg2: + +psycopg2 -------- -.. automodule:: sqlalchemy.dialects.postgresql.psycopg +.. versionchanged:: 2.1 + ``psycopg2`` is no longer the default PostgreSQL dialect. To explicitly + use ``psycopg2``, specify ``postgresql+psycopg2://...`` in the URL. + +.. automodule:: sqlalchemy.dialects.postgresql.psycopg2 .. _postgresql_pg8000: diff --git a/doc/build/tutorial/data_update.rst b/doc/build/tutorial/data_update.rst index 4b444395ea..29f9a216a7 100644 --- a/doc/build/tutorial/data_update.rst +++ b/doc/build/tutorial/data_update.rst @@ -190,7 +190,10 @@ multiple rows at once against the named form of VALUES:: >>> print(update_stmt.compile(dialect=postgresql.dialect())) {printsql}UPDATE user_account SET name=my_values.name - FROM (VALUES (%(param_1)s, %(param_2)s), (%(param_3)s, %(param_4)s), (%(param_5)s, %(param_6)s)) AS my_values (id, name) + FROM (VALUES + (%(param_1)s::INTEGER, %(param_2)s::VARCHAR), + (%(param_3)s::INTEGER, %(param_4)s::VARCHAR), + (%(param_5)s::INTEGER, %(param_6)s::VARCHAR)) AS my_values (id, name) WHERE user_account.id = my_values.id .. _tutorial_parameter_ordered_updates: diff --git a/lib/sqlalchemy/dialects/oracle/__init__.py b/lib/sqlalchemy/dialects/oracle/__init__.py index 12c06a84e7..2b12b0db86 100644 --- a/lib/sqlalchemy/dialects/oracle/__init__.py +++ b/lib/sqlalchemy/dialects/oracle/__init__.py @@ -46,7 +46,7 @@ oracledb_async = type( "oracledb_async", (ModuleType,), {"dialect": oracledb.dialect_async} ) -base.dialect = dialect = cx_oracle.dialect +base.dialect = dialect = oracledb.dialect __all__ = ( "VARCHAR", diff --git a/lib/sqlalchemy/dialects/oracle/provision.py b/lib/sqlalchemy/dialects/oracle/provision.py index 2ae5e93f19..997ca3b589 100644 --- a/lib/sqlalchemy/dialects/oracle/provision.py +++ b/lib/sqlalchemy/dialects/oracle/provision.py @@ -19,7 +19,6 @@ from ...testing.provision import drop_all_schema_objects_pre_tables from ...testing.provision import drop_db from ...testing.provision import follower_url_from_main from ...testing.provision import generate_driver_url -from ...testing.provision import is_preferred_driver from ...testing.provision import log from ...testing.provision import post_configure_engine from ...testing.provision import post_configure_testing_engine @@ -159,14 +158,6 @@ def _purge_recyclebin(eng, schema=None): conn.exec_driver_sql(f'purge {type_} {owner}."{object_name}"') -@is_preferred_driver.for_db("oracle") -def _oracle_is_preferred_driver(cfg, engine): - """establish oracledb as the preferred driver to use for tests, even - though cx_Oracle is still the "default" driver""" - - return engine.dialect.driver == "oracledb" and not engine.dialect.is_async - - def _connect_with_retry(dialect, conn_rec, cargs, cparams): assert dialect.driver == "cx_oracle" diff --git a/lib/sqlalchemy/dialects/postgresql/__init__.py b/lib/sqlalchemy/dialects/postgresql/__init__.py index 677f3b7dd5..852d3bf7be 100644 --- a/lib/sqlalchemy/dialects/postgresql/__init__.py +++ b/lib/sqlalchemy/dialects/postgresql/__init__.py @@ -97,7 +97,7 @@ psycopg_async = type( "psycopg_async", (ModuleType,), {"dialect": psycopg.dialect_async} ) -base.dialect = dialect = psycopg2.dialect +base.dialect = dialect = psycopg.dialect __all__ = ( diff --git a/noxfile.py b/noxfile.py index d320710ff2..e9d0c1fa93 100644 --- a/noxfile.py +++ b/noxfile.py @@ -2,6 +2,7 @@ from __future__ import annotations +import multiprocessing import os from pathlib import Path import sys @@ -255,7 +256,8 @@ def _tests( cmd = ["python", "-m", "pytest"] - cmd.extend(os.environ.get("TOX_WORKERS", "-n4").split()) + default_workers = f"-n{int(multiprocessing.cpu_count() * 0.8)}" + cmd.extend(os.environ.get("TOX_WORKERS", default_workers).split()) if coverage: assert not platform_intensive diff --git a/pyproject.toml b/pyproject.toml index 25c2c13879..9da9ea25c0 100644 --- a/pyproject.toml +++ b/pyproject.toml @@ -57,7 +57,7 @@ mariadb-connector = ["mariadb>=1.0.1,!=1.1.2,!=1.1.5,!=1.1.10"] oracle = ["oracledb>=1.0.1"] oracle-cxoracle = ["cx_oracle>=8"] oracle-oracledb = ["oracledb>=1.0.1"] -postgresql = ["psycopg2>=2.7"] +postgresql = ["psycopg>=3.0.7,!=3.1.15"] postgresql-pg8000 = ["pg8000>=1.29.3"] postgresql-asyncpg = [ "sqlalchemy[asyncio]", diff --git a/setup.cfg b/setup.cfg index 902d381d8a..2700e67a98 100644 --- a/setup.cfg +++ b/setup.cfg @@ -29,7 +29,7 @@ aiosqlite = sqlite+aiosqlite:///:memory: sqlite_file = sqlite:///querytest.db aiosqlite_file = sqlite+aiosqlite:///async_querytest.db pysqlcipher_file = sqlite+pysqlcipher://:test@/querytest.db.enc -postgresql = postgresql+psycopg2://scott:tiger@127.0.0.1:5432/test +postgresql = postgresql+psycopg://scott:tiger@127.0.0.1:5432/test psycopg2 = postgresql+psycopg2://scott:tiger@127.0.0.1:5432/test psycopg = postgresql+psycopg://scott:tiger@127.0.0.1:5432/test psycopg_async = postgresql+psycopg_async://scott:tiger@127.0.0.1:5432/test diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py index 817fb620c5..8de98e78e3 100644 --- a/test/dialect/postgresql/test_compiler.py +++ b/test/dialect/postgresql/test_compiler.py @@ -137,7 +137,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): stmt = t.insert().values().return_defaults() eq_ignore_whitespace( str(stmt.compile(dialect=postgresql.dialect())), - "INSERT INTO t (description) VALUES (lower(%(lower_1)s)) " + "INSERT INTO t (description) VALUES (lower(%(lower_1)s::VARCHAR)) " "RETURNING t.myid, t.name, t.description", ) @@ -156,16 +156,15 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( u, - "UPDATE mytable SET name=%(name)s " - "RETURNING mytable.myid, mytable.name", + "UPDATE mytable SET name=%(name)s::VARCHAR RETURNING mytable.myid," + " mytable.name", dialect=dialect, ) u = update(table1).values(dict(name="foo")).returning(table1) self.assert_compile( u, - "UPDATE mytable SET name=%(name)s " - "RETURNING mytable.myid, mytable.name, " - "mytable.description", + "UPDATE mytable SET name=%(name)s::VARCHAR RETURNING mytable.myid," + " mytable.name, mytable.description", dialect=dialect, ) u = ( @@ -175,8 +174,8 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( u, - "UPDATE mytable SET name=%(name)s " - "RETURNING length(mytable.name) AS length_1", + "UPDATE mytable SET name=%(name)s::VARCHAR RETURNING" + " length(mytable.name) AS length_1", dialect=dialect, ) @@ -196,17 +195,15 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - "(%(name)s) RETURNING mytable.myid, " - "mytable.name", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) RETURNING" + " mytable.myid, mytable.name", dialect=dialect, ) i = insert(table1).values(dict(name="foo")).returning(table1) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - "(%(name)s) RETURNING mytable.myid, " - "mytable.name, mytable.description", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) RETURNING" + " mytable.myid, mytable.name, mytable.description", dialect=dialect, ) i = ( @@ -216,9 +213,8 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - "(%(name)s) RETURNING length(mytable.name) " - "AS length_1", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) RETURNING" + " length(mytable.name) AS length_1", dialect=dialect, ) @@ -246,9 +242,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( stmt, - "INSERT INTO some_table (name, value) " - "VALUES (%(name)s, %(value)s) RETURNING some_table.name, " - "lower(some_table.value) AS value", + "INSERT INTO some_table (name, value) VALUES (%(name)s::VARCHAR," + " %(value)s) RETURNING some_table.name, lower(some_table.value) AS" + " value", ) def test_create_drop_enum(self): @@ -752,8 +748,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): postgresql_nulls_not_distinct=True, ) ), - "CREATE UNIQUE INDEX test_idx1 ON test_tbl " - "(data) NULLS NOT DISTINCT", + ( + "CREATE UNIQUE INDEX test_idx1 ON test_tbl " + "(data) NULLS NOT DISTINCT" + ), ), ( lambda tbl: schema.CreateIndex( @@ -764,8 +762,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): postgresql_nulls_not_distinct=False, ) ), - "CREATE UNIQUE INDEX test_idx2 ON test_tbl " - "(data2) NULLS DISTINCT", + "CREATE UNIQUE INDEX test_idx2 ON test_tbl (data2) NULLS DISTINCT", ), ( lambda tbl: schema.CreateIndex( @@ -788,9 +785,11 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): postgresql_with={"fillfactor": 50}, ) ), - "CREATE INDEX test_idx3_complex ON test_tbl " - "(data3) INCLUDE (data2) NULLS NOT DISTINCT WITH " - "(fillfactor = 50) WHERE data3 > 5", + ( + "CREATE INDEX test_idx3_complex ON test_tbl " + "(data3) INCLUDE (data2) NULLS NOT DISTINCT WITH " + "(fillfactor = 50) WHERE data3 > 5" + ), ), ( lambda tbl: schema.AddConstraint( @@ -800,8 +799,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): postgresql_nulls_not_distinct=True, ) ), - "ALTER TABLE test_tbl ADD CONSTRAINT uq_data1 UNIQUE " - "NULLS NOT DISTINCT (data)", + ( + "ALTER TABLE test_tbl ADD CONSTRAINT uq_data1 UNIQUE " + "NULLS NOT DISTINCT (data)" + ), ), ( lambda tbl: schema.AddConstraint( @@ -811,8 +812,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): postgresql_nulls_not_distinct=False, ) ), - "ALTER TABLE test_tbl ADD CONSTRAINT uq_data2 UNIQUE " - "NULLS DISTINCT (data2)", + ( + "ALTER TABLE test_tbl ADD CONSTRAINT uq_data2 UNIQUE " + "NULLS DISTINCT (data2)" + ), ), ( lambda tbl: schema.AddConstraint( @@ -896,8 +899,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( schema.CreateIndex(idx), - "CREATE INDEX test_idx1 ON testtbl " - "(lower(data) text_pattern_ops)", + "CREATE INDEX test_idx1 ON testtbl (lower(data) text_pattern_ops)", dialect=postgresql.dialect(), ) self.assert_compile( @@ -1011,9 +1013,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( schema.CreateIndex(idx2), - "CREATE INDEX test_idx2 ON testtbl " - "(data) " - "WITH (fillfactor = 50)", + "CREATE INDEX test_idx2 ON testtbl (data) WITH (fillfactor = 50)", ) self.assert_compile( schema.CreateIndex(idx3), @@ -1435,8 +1435,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): eq_(list(cons.columns), []) self.assert_compile( schema.AddConstraint(cons), - "ALTER TABLE testtbl ADD EXCLUDE USING gist " - "(room::TEXT WITH =)", + "ALTER TABLE testtbl ADD EXCLUDE USING gist (room::TEXT WITH =)", ) def test_exclude_constraint_colname_needs_quoting(self): @@ -1521,8 +1520,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): eq_(cons.columns.keys(), ["x"]) self.assert_compile( schema.AddConstraint(cons), - "ALTER TABLE foo ADD EXCLUDE USING gist " - "(int8range(x, y) WITH &&)", + "ALTER TABLE foo ADD EXCLUDE USING gist (int8range(x, y) WITH &&)", dialect=postgresql.dialect(), ) @@ -1544,12 +1542,13 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): def test_substring(self): self.assert_compile( func.substring("abc", 1, 2), - "SUBSTRING(%(substring_1)s FROM %(substring_2)s " - "FOR %(substring_3)s)", + "SUBSTRING(%(substring_1)s::VARCHAR FROM %(substring_2)s::INTEGER" + " FOR %(substring_3)s::INTEGER)", ) self.assert_compile( func.substring("abc", 1), - "SUBSTRING(%(substring_1)s FROM %(substring_2)s)", + "SUBSTRING(%(substring_1)s::VARCHAR FROM" + " %(substring_2)s::INTEGER)", ) def test_for_update(self): @@ -1559,86 +1558,88 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( table1.select().where(table1.c.myid == 7).with_for_update(), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s FOR UPDATE", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR UPDATE", ) self.assert_compile( table1.select() .where(table1.c.myid == 7) .with_for_update(nowait=True), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s FOR UPDATE NOWAIT", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR UPDATE" + " NOWAIT", ) self.assert_compile( table1.select() .where(table1.c.myid == 7) .with_for_update(skip_locked=True), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR UPDATE SKIP LOCKED", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR UPDATE SKIP" + " LOCKED", ) self.assert_compile( table1.select() .where(table1.c.myid == 7) .with_for_update(read=True), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s FOR SHARE", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR SHARE", ) self.assert_compile( table1.select() .where(table1.c.myid == 7) .with_for_update(read=True, nowait=True), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s FOR SHARE NOWAIT", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR SHARE" + " NOWAIT", ) self.assert_compile( table1.select() .where(table1.c.myid == 7) .with_for_update(key_share=True, nowait=True), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR NO KEY UPDATE NOWAIT", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR NO KEY" + " UPDATE NOWAIT", ) self.assert_compile( table1.select() .where(table1.c.myid == 7) .with_for_update(key_share=True, read=True, nowait=True), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR KEY SHARE NOWAIT", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR KEY SHARE" + " NOWAIT", ) self.assert_compile( table1.select() .where(table1.c.myid == 7) .with_for_update(read=True, skip_locked=True), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR SHARE SKIP LOCKED", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR SHARE SKIP" + " LOCKED", ) self.assert_compile( table1.select() .where(table1.c.myid == 7) .with_for_update(of=table1.c.myid), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR UPDATE OF mytable", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR UPDATE OF" + " mytable", ) self.assert_compile( table1.select() .where(table1.c.myid == 7) .with_for_update(read=True, nowait=True, of=table1), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR SHARE OF mytable NOWAIT", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR SHARE OF" + " mytable NOWAIT", ) self.assert_compile( @@ -1647,18 +1648,18 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): .with_for_update( key_share=True, read=True, nowait=True, of=table1 ), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR KEY SHARE OF mytable NOWAIT", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR KEY SHARE" + " OF mytable NOWAIT", ) self.assert_compile( table1.select() .where(table1.c.myid == 7) .with_for_update(read=True, nowait=True, of=table1.c.myid), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR SHARE OF mytable NOWAIT", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR SHARE OF" + " mytable NOWAIT", ) self.assert_compile( @@ -1667,9 +1668,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): .with_for_update( read=True, nowait=True, of=[table1.c.myid, table1.c.name] ), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR SHARE OF mytable NOWAIT", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR SHARE OF" + " mytable NOWAIT", ) self.assert_compile( @@ -1681,9 +1682,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): of=[table1.c.myid, table1.c.name], key_share=True, ), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR KEY SHARE OF mytable SKIP LOCKED", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR KEY SHARE" + " OF mytable SKIP LOCKED", ) self.assert_compile( @@ -1692,9 +1693,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): .with_for_update( skip_locked=True, of=[table1.c.myid, table1.c.name] ), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR UPDATE OF mytable SKIP LOCKED", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR UPDATE OF" + " mytable SKIP LOCKED", ) self.assert_compile( @@ -1703,9 +1704,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): .with_for_update( read=True, skip_locked=True, of=[table1.c.myid, table1.c.name] ), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR SHARE OF mytable SKIP LOCKED", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR SHARE OF" + " mytable SKIP LOCKED", ) self.assert_compile( @@ -1714,9 +1715,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): .with_for_update( key_share=True, nowait=True, of=[table1.c.myid, table1.c.name] ), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR NO KEY UPDATE OF mytable NOWAIT", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR NO KEY" + " UPDATE OF mytable NOWAIT", ) self.assert_compile( @@ -1727,9 +1728,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): skip_locked=True, of=[table1.c.myid, table1.c.name], ), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR NO KEY UPDATE OF mytable SKIP LOCKED", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR NO KEY" + " UPDATE OF mytable SKIP LOCKED", ) self.assert_compile( @@ -1738,63 +1739,62 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): .with_for_update( key_share=True, of=[table1.c.myid, table1.c.name] ), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR NO KEY UPDATE OF mytable", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR NO KEY" + " UPDATE OF mytable", ) self.assert_compile( table1.select() .where(table1.c.myid == 7) .with_for_update(key_share=True), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR NO KEY UPDATE", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR NO KEY" + " UPDATE", ) self.assert_compile( table1.select() .where(table1.c.myid == 7) .with_for_update(read=True, key_share=True), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR KEY SHARE", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR KEY SHARE", ) self.assert_compile( table1.select() .where(table1.c.myid == 7) .with_for_update(read=True, key_share=True, of=table1), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR KEY SHARE OF mytable", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR KEY SHARE" + " OF mytable", ) self.assert_compile( table1.select() .where(table1.c.myid == 7) .with_for_update(read=True, of=table1), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR SHARE OF mytable", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR SHARE OF" + " mytable", ) self.assert_compile( table1.select() .where(table1.c.myid == 7) .with_for_update(read=True, key_share=True, skip_locked=True), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR KEY SHARE SKIP LOCKED", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR KEY SHARE" + " SKIP LOCKED", ) self.assert_compile( table1.select() .where(table1.c.myid == 7) .with_for_update(key_share=True, skip_locked=True), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR NO KEY UPDATE SKIP LOCKED", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR NO KEY" + " UPDATE SKIP LOCKED", ) ta = table1.alias() @@ -1802,9 +1802,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): ta.select() .where(ta.c.myid == 7) .with_for_update(of=[ta.c.myid, ta.c.name]), - "SELECT mytable_1.myid, mytable_1.name, mytable_1.description " - "FROM mytable AS mytable_1 " - "WHERE mytable_1.myid = %(myid_1)s FOR UPDATE OF mytable_1", + "SELECT mytable_1.myid, mytable_1.name, mytable_1.description FROM" + " mytable AS mytable_1 WHERE mytable_1.myid = %(myid_1)s::INTEGER" + " FOR UPDATE OF mytable_1", ) table2 = table("table2", column("mytable_id")) @@ -1813,12 +1813,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): join.select() .where(table2.c.mytable_id == 7) .with_for_update(of=[join]), - "SELECT table2.mytable_id, " - "mytable.myid, mytable.name, mytable.description " - "FROM table2 " - "JOIN mytable ON table2.mytable_id = mytable.myid " - "WHERE table2.mytable_id = %(mytable_id_1)s " - "FOR UPDATE OF mytable, table2", + "SELECT table2.mytable_id, mytable.myid, mytable.name," + " mytable.description FROM table2 JOIN mytable ON" + " table2.mytable_id = mytable.myid WHERE table2.mytable_id =" + " %(mytable_id_1)s::INTEGER FOR UPDATE OF mytable, table2", ) join = table2.join(ta, table2.c.mytable_id == ta.c.myid) @@ -1826,13 +1824,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): join.select() .where(table2.c.mytable_id == 7) .with_for_update(of=[join]), - "SELECT table2.mytable_id, " - "mytable_1.myid, mytable_1.name, mytable_1.description " - "FROM table2 " - "JOIN mytable AS mytable_1 " - "ON table2.mytable_id = mytable_1.myid " - "WHERE table2.mytable_id = %(mytable_id_1)s " - "FOR UPDATE OF mytable_1, table2", + "SELECT table2.mytable_id, mytable_1.myid, mytable_1.name," + " mytable_1.description FROM table2 JOIN mytable AS mytable_1 ON" + " table2.mytable_id = mytable_1.myid WHERE table2.mytable_id =" + " %(mytable_id_1)s::INTEGER FOR UPDATE OF mytable_1, table2", ) # ensure of=text() for of works @@ -1840,9 +1835,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): table1.select() .where(table1.c.myid == 7) .with_for_update(of=text("table1")), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR UPDATE OF table1", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR UPDATE OF" + " table1", ) # ensure literal_column of works @@ -1850,9 +1845,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): table1.select() .where(table1.c.myid == 7) .with_for_update(of=literal_column("table1")), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %(myid_1)s " - "FOR UPDATE OF table1", + "SELECT mytable.myid, mytable.name, mytable.description FROM" + " mytable WHERE mytable.myid = %(myid_1)s::INTEGER FOR UPDATE OF" + " table1", ) # test issue #12417 @@ -1874,10 +1869,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): table1.select() .where(table1.c.myid == 7) .with_for_update(of=table1), - "SELECT testschema.mytable.myid, testschema.mytable.name " - "FROM testschema.mytable " - "WHERE testschema.mytable.myid = %(myid_1)s " - "FOR UPDATE OF mytable", + "SELECT testschema.mytable.myid, testschema.mytable.name FROM" + " testschema.mytable WHERE testschema.mytable.myid =" + " %(myid_1)s::INTEGER FOR UPDATE OF mytable", ) def test_reserved_words(self): @@ -1909,19 +1903,24 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( cast(c, postgresql.ARRAY(Integer)), "CAST(x AS INTEGER[])" ) - self.assert_compile(c[5], "x[%(x_1)s]", checkparams={"x_1": 5}) + self.assert_compile( + c[5], "x[%(x_1)s::INTEGER]", checkparams={"x_1": 5} + ) self.assert_compile( - c[5:7], "x[%(x_1)s:%(x_2)s]", checkparams={"x_2": 7, "x_1": 5} + c[5:7], + "x[%(x_1)s::INTEGER:%(x_2)s::INTEGER]", + checkparams={"x_2": 7, "x_1": 5}, ) self.assert_compile( c[5:7][2:3], - "x[%(x_1)s:%(x_2)s][%(param_1)s:%(param_2)s]", + "x[%(x_1)s::INTEGER:%(x_2)s::INTEGER]" + "[%(param_1)s::INTEGER:%(param_2)s::INTEGER]", checkparams={"x_2": 7, "x_1": 5, "param_1": 2, "param_2": 3}, ) self.assert_compile( c[5:7][3], - "x[%(x_1)s:%(x_2)s][%(param_1)s]", + "x[%(x_1)s::INTEGER:%(x_2)s::INTEGER][%(param_1)s::INTEGER]", checkparams={"x_2": 7, "x_1": 5, "param_1": 3}, ) @@ -1955,67 +1954,67 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( 4 == c.any_(), - "%(param_1)s = ANY (x)", + "%(param_1)s::INTEGER = ANY (x)", checkparams={"param_1": 4}, ) self.assert_compile( 5 == any_(c), - "%(param_1)s = ANY (x)", + "%(param_1)s::INTEGER = ANY (x)", checkparams={"param_1": 5}, ) self.assert_compile( ~(c.any_() == 5), - "NOT (%(param_1)s = ANY (x))", + "NOT (%(param_1)s::INTEGER = ANY (x))", checkparams={"param_1": 5}, ) self.assert_compile( ~(5 == c.any_()), - "NOT (%(param_1)s = ANY (x))", + "NOT (%(param_1)s::INTEGER = ANY (x))", checkparams={"param_1": 5}, ) self.assert_compile( 5 != any_(c), - "%(param_1)s != ANY (x)", + "%(param_1)s::INTEGER != ANY (x)", checkparams={"param_1": 5}, ) self.assert_compile( 6 > all_(c), - "%(param_1)s > ALL (x)", + "%(param_1)s::INTEGER > ALL (x)", checkparams={"param_1": 6}, ) self.assert_compile( 7 < all_(c), - "%(param_1)s < ALL (x)", + "%(param_1)s::INTEGER < ALL (x)", checkparams={"param_1": 7}, ) self.assert_compile( c.all_() == 5, - "%(param_1)s = ALL (x)", + "%(param_1)s::INTEGER = ALL (x)", checkparams={"param_1": 5}, ) self.assert_compile( 5 == c.all_(), - "%(param_1)s = ALL (x)", + "%(param_1)s::INTEGER = ALL (x)", checkparams={"param_1": 5}, ) self.assert_compile( ~(5 == all_(c)), - "NOT (%(param_1)s = ALL (x))", + "NOT (%(param_1)s::INTEGER = ALL (x))", checkparams={"param_1": 5}, ) self.assert_compile( ~(all_(c) == 5), - "NOT (%(param_1)s = ALL (x))", + "NOT (%(param_1)s::INTEGER = ALL (x))", checkparams={"param_1": 5}, ) @@ -2025,56 +2024,52 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): with self._array_any_deprecation(): self.assert_compile( postgresql.Any(4, c), - "%(x_1)s = ANY (x)", + "%(x_1)s::INTEGER = ANY (x)", checkparams={"x_1": 4}, ) with self._array_any_deprecation(): self.assert_compile( - c.any(5), - "%(x_1)s = ANY (x)", - checkparams={"x_1": 5}, + c.any(5), "%(x_1)s::INTEGER = ANY (x)", checkparams={"x_1": 5} ) with self._array_any_deprecation(): self.assert_compile( ~c.any(5), - "NOT (%(x_1)s = ANY (x))", + "NOT (%(x_1)s::INTEGER = ANY (x))", checkparams={"x_1": 5}, ) with self._array_any_deprecation(): self.assert_compile( c.any(5, operator=operators.ne), - "%(x_1)s != ANY (x)", + "%(x_1)s::INTEGER != ANY (x)", checkparams={"x_1": 5}, ) with self._array_any_deprecation(): self.assert_compile( postgresql.All(6, c, operator=operators.gt), - "%(x_1)s > ALL (x)", + "%(x_1)s::INTEGER > ALL (x)", checkparams={"x_1": 6}, ) with self._array_any_deprecation(): self.assert_compile( c.all(7, operator=operators.lt), - "%(x_1)s < ALL (x)", + "%(x_1)s::INTEGER < ALL (x)", checkparams={"x_1": 7}, ) with self._array_any_deprecation(): self.assert_compile( - c.all(5), - "%(x_1)s = ALL (x)", - checkparams={"x_1": 5}, + c.all(5), "%(x_1)s::INTEGER = ALL (x)", checkparams={"x_1": 5} ) with self._array_any_deprecation(): self.assert_compile( ~c.all(5), - "NOT (%(x_1)s = ALL (x))", + "NOT (%(x_1)s::INTEGER = ALL (x))", checkparams={"x_1": 5}, ) @@ -2122,17 +2117,18 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): "CAST(x AS INTEGER[])", ) self.assert_compile( - c[5], "x[%(x_1)s]", checkparams={"x_1": 5 + add_one} + c[5], "x[%(x_1)s::INTEGER]", checkparams={"x_1": 5 + add_one} ) self.assert_compile( c[5:7], - "x[%(x_1)s:%(x_2)s]", + "x[%(x_1)s::INTEGER:%(x_2)s::INTEGER]", checkparams={"x_2": 7 + add_one, "x_1": 5 + add_one}, ) self.assert_compile( c[5:7][2:3], - "x[%(x_1)s:%(x_2)s][%(param_1)s:%(param_2)s]", + "x[%(x_1)s::INTEGER:%(x_2)s::INTEGER]" + "[%(param_1)s::INTEGER:%(param_2)s::INTEGER]", checkparams={ "x_2": 7 + add_one, "x_1": 5 + add_one, @@ -2142,7 +2138,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( c[5:7][3], - "x[%(x_1)s:%(x_2)s][%(param_1)s]", + "x[%(x_1)s::INTEGER:%(x_2)s::INTEGER][%(param_1)s::INTEGER]", checkparams={ "x_2": 7 + add_one, "x_1": 5 + add_one, @@ -2174,8 +2170,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): func.array_dims( postgresql.array([1, 2]) + postgresql.array([3, 4, 5]) ), - "array_dims(ARRAY[%(param_1)s, %(param_2)s] || " - "ARRAY[%(param_3)s, %(param_4)s, %(param_5)s])", + "array_dims(ARRAY[%(param_1)s::INTEGER, %(param_2)s::INTEGER] ||" + " ARRAY[%(param_3)s::INTEGER, %(param_4)s::INTEGER," + " %(param_5)s::INTEGER])", checkparams={ "param_5": 5, "param_4": 4, @@ -2188,8 +2185,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): def test_array_literal_compare(self): self.assert_compile( postgresql.array([1, 2]) == [3, 4, 5], - "ARRAY[%(param_1)s, %(param_2)s] = " - "ARRAY[%(param_3)s, %(param_4)s, %(param_5)s]", + "ARRAY[%(param_1)s::INTEGER, %(param_2)s::INTEGER] =" + " ARRAY[%(param_3)s::INTEGER, %(param_4)s::INTEGER," + " %(param_5)s::INTEGER]", checkparams={ "param_5": 5, "param_4": 4, @@ -2202,8 +2200,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): def test_array_literal_contains(self): self.assert_compile( postgresql.array([1, 2]).contains([3, 4, 5]), - "ARRAY[%(param_1)s, %(param_2)s] @> ARRAY[%(param_3)s, " - "%(param_4)s, %(param_5)s]", + "ARRAY[%(param_1)s::INTEGER, %(param_2)s::INTEGER] @>" + " ARRAY[%(param_3)s::INTEGER, %(param_4)s::INTEGER," + " %(param_5)s::INTEGER]", checkparams={ "param_1": 1, "param_2": 2, @@ -2215,27 +2214,30 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( postgresql.array(["a", "b"]).contains([""]), - "ARRAY[%(param_1)s, %(param_2)s] @> ARRAY[%(param_3)s]", + "ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR] @>" + " ARRAY[%(param_3)s::VARCHAR]", checkparams={"param_1": "a", "param_2": "b", "param_3": ""}, ) self.assert_compile( postgresql.array(["a", "b"]).contains([]), - "ARRAY[%(param_1)s, %(param_2)s] @> ARRAY[]", + "ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR] @> ARRAY[]", checkparams={"param_1": "a", "param_2": "b"}, ) self.assert_compile( postgresql.array(["a", "b"]).contains([0]), - "ARRAY[%(param_1)s, %(param_2)s] @> ARRAY[%(param_3)s]", + "ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR] @>" + " ARRAY[%(param_3)s::INTEGER]", checkparams={"param_1": "a", "param_2": "b", "param_3": 0}, ) def test_array_literal_contained_by(self): self.assert_compile( postgresql.array(["a", "b"]).contained_by(["a", "b", "c"]), - "ARRAY[%(param_1)s, %(param_2)s] <@ ARRAY[%(param_3)s, " - "%(param_4)s, %(param_5)s]", + "ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR] <@" + " ARRAY[%(param_3)s::VARCHAR, %(param_4)s::VARCHAR," + " %(param_5)s::VARCHAR]", checkparams={ "param_1": "a", "param_2": "b", @@ -2247,8 +2249,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( postgresql.array([1, 2]).contained_by([3, 4, 5]), - "ARRAY[%(param_1)s, %(param_2)s] <@ ARRAY[%(param_3)s, " - "%(param_4)s, %(param_5)s]", + "ARRAY[%(param_1)s::INTEGER, %(param_2)s::INTEGER] <@" + " ARRAY[%(param_3)s::INTEGER, %(param_4)s::INTEGER," + " %(param_5)s::INTEGER]", checkparams={ "param_1": 1, "param_2": 2, @@ -2260,19 +2263,21 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( postgresql.array(["a", "b"]).contained_by([""]), - "ARRAY[%(param_1)s, %(param_2)s] <@ ARRAY[%(param_3)s]", + "ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR] <@" + " ARRAY[%(param_3)s::VARCHAR]", checkparams={"param_1": "a", "param_2": "b", "param_3": ""}, ) self.assert_compile( postgresql.array(["a", "b"]).contained_by([]), - "ARRAY[%(param_1)s, %(param_2)s] <@ ARRAY[]", + "ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR] <@ ARRAY[]", checkparams={"param_1": "a", "param_2": "b"}, ) self.assert_compile( postgresql.array(["a", "b"]).contained_by([0]), - "ARRAY[%(param_1)s, %(param_2)s] <@ ARRAY[%(param_3)s]", + "ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR] <@" + " ARRAY[%(param_3)s::INTEGER]", checkparams={"param_1": "a", "param_2": "b", "param_3": 0}, ) @@ -2281,8 +2286,8 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): t = Table("t", m, Column("data", postgresql.ARRAY(Integer))) self.assert_compile( t.insert().values(data=array([1, 2, 3])), - "INSERT INTO t (data) VALUES (ARRAY[%(param_1)s, " - "%(param_2)s, %(param_3)s])", + "INSERT INTO t (data) VALUES (ARRAY[%(param_1)s::INTEGER," + " %(param_2)s::INTEGER, %(param_3)s::INTEGER])", ) def test_update_array(self): @@ -2299,7 +2304,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): t = Table("t", m, Column("data", postgresql.ARRAY(Integer))) self.assert_compile( t.update().values({t.c.data[5]: 1}), - "UPDATE t SET data[%(data_1)s]=%(param_1)s", + "UPDATE t SET data[%(data_1)s::INTEGER]=%(param_1)s::INTEGER", checkparams={"data_1": 5, "param_1": 1}, ) @@ -2310,8 +2315,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): # psycopg2-specific, has a cast self.assert_compile( t.update().values({t.c.data[2:5]: [2, 3, 4]}), - "UPDATE t SET data[%(data_1)s:%(data_2)s]=" - "%(param_1)s::INTEGER[]", + "UPDATE t SET data[%(data_1)s:%(data_2)s]=%(param_1)s::INTEGER[]", checkparams={"param_1": [2, 3, 4], "data_2": 5, "data_1": 2}, dialect=PGDialect_psycopg2(), ) @@ -2363,7 +2367,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): stmt = update(tbl1).values(dict(id=1)) stmt = stmt.with_hint("ONLY", dialect_name="postgresql") - expected = "UPDATE ONLY testtbl1 SET id=%(id)s" + expected = "UPDATE ONLY testtbl1 SET id=%(id)s::INTEGER" self.assert_compile(stmt, expected) stmt = delete(tbl1).with_hint( @@ -2493,11 +2497,11 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): ).select_from(series) self.assert_compile( query, - "SELECT (array_agg(series) FILTER " - "(WHERE series %% %(series_1)s = %(param_1)s))[%(param_2)s] " - "AS anon_1 FROM " - "generate_series(%(generate_series_1)s, %(generate_series_2)s) " - "AS series", + "SELECT (array_agg(series) FILTER (WHERE series %%" + " %(series_1)s::INTEGER =" + " %(param_1)s::INTEGER))[%(param_2)s::INTEGER] AS anon_1 FROM" + " generate_series(%(generate_series_1)s::INTEGER," + " %(generate_series_2)s::INTEGER) AS series", ) def test_delete_extra_froms(self): @@ -2925,9 +2929,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( update, - "UPDATE weather SET (temp_lo, temp_hi, prcp)=(weather.temp_lo + " - "%(temp_lo_1)s, weather.temp_lo + %(temp_lo_2)s, DEFAULT) " - "WHERE weather.city = %(city_1)s AND weather.date = %(date_1)s", + "UPDATE weather SET (temp_lo, temp_hi, prcp)=(weather.temp_lo +" + " %(temp_lo_1)s::INTEGER, weather.temp_lo +" + " %(temp_lo_2)s::INTEGER, DEFAULT) WHERE weather.city =" + " %(city_1)s::VARCHAR AND weather.date = %(date_1)s::VARCHAR", { "city_1": "San Francisco", "date_1": "2003-07-03", @@ -2942,11 +2947,13 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): tuple_( self.accounts.c.contact_first_name, self.accounts.c.contact_last_name, - ): select( - self.salesmen.c.first_name, self.salesmen.c.last_name + ): ( + select( + self.salesmen.c.first_name, self.salesmen.c.last_name + ) + .where(self.salesmen.c.id == self.accounts.c.sales_id) + .scalar_subquery() ) - .where(self.salesmen.c.id == self.accounts.c.sales_id) - .scalar_subquery() } ) @@ -2962,8 +2969,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.employees.update() .values( { - self.employees.c.sales_count: self.employees.c.sales_count - + 1 + self.employees.c.sales_count: ( + self.employees.c.sales_count + 1 + ) } ) .where( @@ -2976,10 +2984,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( update, - "UPDATE employees SET sales_count=(employees.sales_count " - "+ %(sales_count_1)s) WHERE employees.id = (SELECT " - "accounts.sales_person FROM accounts WHERE " - "accounts.name = %(name_1)s)", + "UPDATE employees SET sales_count=(employees.sales_count +" + " %(sales_count_1)s::INTEGER) WHERE employees.id = (SELECT" + " accounts.sales_person FROM accounts WHERE accounts.name =" + " %(name_1)s::VARCHAR)", {"sales_count_1": 1, "name_1": "Acme Corporation"}, ) @@ -3006,14 +3014,16 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): summary.c.sum_y, summary.c.avg_x, summary.c.avg_y, - ): select( - func.sum(data.c.x), - func.sum(data.c.y), - func.avg(data.c.x), - func.avg(data.c.y), + ): ( + select( + func.sum(data.c.x), + func.sum(data.c.y), + func.avg(data.c.x), + func.avg(data.c.y), + ) + .where(data.c.group_id == summary.c.group_id) + .scalar_subquery() ) - .where(data.c.group_id == summary.c.group_id) - .scalar_subquery() } ) self.assert_compile( @@ -3039,11 +3049,11 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): @testing.combinations( ( lambda col: col["foo"] + " ", - "x[%(x_1)s] || %(param_1)s", + "x[%(x_1)s::TEXT] || %(param_1)s::VARCHAR", ), ( lambda col: col["foo"] + " " + col["bar"], - "x[%(x_1)s] || %(param_1)s || x[%(x_2)s]", + "x[%(x_1)s::TEXT] || %(param_1)s::VARCHAR || x[%(x_2)s::TEXT]", ), argnames="expr, expected", ) @@ -3071,9 +3081,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( stmt, ( - "SELECT data.x[%(x_1)s] AS anon_1 FROM data" + "SELECT data.x[%(x_1)s::TEXT] AS anon_1 FROM data" if pgversion.pg14 - else "SELECT data.x -> %(x_1)s AS anon_1 FROM data" + else "SELECT data.x -> %(x_1)s::TEXT AS anon_1 FROM data" ), dialect=dialect, ) @@ -3083,9 +3093,12 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( stmt, ( - "UPDATE data SET x[%(x_1)s]=(data.x[%(x_2)s])" + "UPDATE data SET x[%(x_1)s::TEXT]=(data.x[%(x_2)s::TEXT])" if pgversion.pg14 - else "UPDATE data SET x -> %(x_1)s=(data.x -> %(x_2)s)" + else ( + "UPDATE data SET x -> %(x_1)s::TEXT=(data.x ->" + " %(x_2)s::TEXT)" + ) ), dialect=dialect, ) @@ -3099,15 +3112,14 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): # Test SELECT with JSON indexing stmt = select(data.c.x["key"]) self.assert_compile( - stmt, - "SELECT data.x -> %(x_1)s AS anon_1 FROM data", + stmt, "SELECT data.x -> %(x_1)s::TEXT AS anon_1 FROM data" ) # Test UPDATE with JSON indexing stmt = update(data).values({data.c.x["new_key"]: data.c.x["old_key"]}) self.assert_compile( stmt, - "UPDATE data SET x -> %(x_1)s=(data.x -> %(x_2)s)", + "UPDATE data SET x -> %(x_1)s::TEXT=(data.x -> %(x_2)s::TEXT)", ) @testing.variation("pgversion", ["pg14", "pg13"]) @@ -3125,9 +3137,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( stmt, ( - "SELECT data.h[%(h_1)s] AS anon_1 FROM data" + "SELECT data.h[%(h_1)s::VARCHAR] AS anon_1 FROM data" if pgversion.pg14 - else "SELECT data.h -> %(h_1)s AS anon_1 FROM data" + else "SELECT data.h -> %(h_1)s::VARCHAR AS anon_1 FROM data" ), dialect=dialect, ) @@ -3137,9 +3149,13 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( stmt, ( - "UPDATE data SET h[%(h_1)s]=(data.h[%(h_2)s])" + "UPDATE data SET" + " h[%(h_1)s::VARCHAR]=(data.h[%(h_2)s::VARCHAR])" if pgversion.pg14 - else "UPDATE data SET h -> %(h_1)s=(data.h -> %(h_2)s)" + else ( + "UPDATE data SET h -> %(h_1)s::VARCHAR=(data.h ->" + " %(h_2)s::VARCHAR)" + ) ), dialect=dialect, ) @@ -3155,9 +3171,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): stmt = select(func.jsonb_array_elements(data.c.x, type_=JSONB)["key"]) self.assert_compile( stmt, - "SELECT " - "(jsonb_array_elements(data.x))[%(jsonb_array_elements_1)s] " - "AS anon_1 FROM data", + "SELECT" + " (jsonb_array_elements(data.x))[%(jsonb_array_elements_1)s::TEXT]" + " AS anon_1 FROM data", ) # Test with nested function calls @@ -3166,8 +3182,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( stmt, - "SELECT (jsonb_array_elements(data.x[%(x_1)s]))" - "[%(jsonb_array_elements_1)s] AS anon_1 FROM data", + "SELECT" + " (jsonb_array_elements(data.x[%(x_1)s::TEXT]))" + "[%(jsonb_array_elements_1)s::TEXT]" + " AS anon_1 FROM data", ) def test_range_custom_object_hook(self): @@ -3226,30 +3244,34 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): dialect = postgresql.dialect() self.assert_compile( sql.column("foo").ilike("bar", escape="\\"), - "foo ILIKE %(foo_1)s ESCAPE '\\\\'", + "foo ILIKE %(foo_1)s::VARCHAR ESCAPE '\\\\'", ) self.assert_compile( sql.column("foo").ilike("bar", escape=""), - "foo ILIKE %(foo_1)s ESCAPE ''", + "foo ILIKE %(foo_1)s::VARCHAR ESCAPE ''", dialect=dialect, ) self.assert_compile( sql.column("foo").notilike("bar", escape="\\"), - "foo NOT ILIKE %(foo_1)s ESCAPE '\\\\'", + "foo NOT ILIKE %(foo_1)s::VARCHAR ESCAPE '\\\\'", ) self.assert_compile( sql.column("foo").notilike("bar", escape=""), - "foo NOT ILIKE %(foo_1)s ESCAPE ''", + "foo NOT ILIKE %(foo_1)s::VARCHAR ESCAPE ''", dialect=dialect, ) @testing.combinations( (lambda t: t.c.a**t.c.b, "power(t.a, t.b)", {}), - (lambda t: t.c.a**3, "power(t.a, %(pow_1)s)", {"pow_1": 3}), - (lambda t: func.pow(t.c.a, 3), "power(t.a, %(pow_1)s)", {"pow_1": 3}), + (lambda t: t.c.a**3, "power(t.a, %(pow_1)s::INTEGER)", {"pow_1": 3}), + ( + lambda t: func.pow(t.c.a, 3), + "power(t.a, %(pow_1)s::INTEGER)", + {"pow_1": 3}, + ), (lambda t: func.power(t.c.a, t.c.b), "power(t.a, t.b)", {}), ) def test_simple_compile(self, fn, string, params): @@ -3327,7 +3349,7 @@ class InsertOnConflictTest( lambda users, stmt: stmt.on_conflict_do_nothing( index_elements=[users.c.id], index_where=users.c.name == "hi" ), - "ON CONFLICT (id) WHERE name = %(name_1)s DO NOTHING", + "ON CONFLICT (id) WHERE name = %(name_1)s::VARCHAR DO NOTHING", ), ( lambda users, stmt: stmt.on_conflict_do_nothing( @@ -3341,8 +3363,10 @@ class InsertOnConflictTest( set_={users.c.name: "there"}, where=users.c.name == "hi", ), - "ON CONFLICT (id) DO UPDATE SET name = %(param_1)s " - "WHERE users.name = %(name_1)s", + ( + "ON CONFLICT (id) DO UPDATE SET name = %(param_1)s::VARCHAR " + "WHERE users.name = %(name_1)s::VARCHAR" + ), ), ( lambda users, stmt: stmt.on_conflict_do_update( @@ -3350,8 +3374,10 @@ class InsertOnConflictTest( set_={users.c.name: "there"}, where=text("name = 'hi'"), ), - "ON CONFLICT (id) DO UPDATE SET name = %(param_1)s " - "WHERE name = 'hi'", + ( + "ON CONFLICT (id) DO UPDATE SET name = %(param_1)s::VARCHAR " + "WHERE name = 'hi'" + ), ), ( lambda users, stmt: stmt.on_conflict_do_update( @@ -3375,8 +3401,8 @@ class InsertOnConflictTest( testing.resolve_lambda( case, stmt=stmt, users=self.tables.users ), - f"INSERT INTO users (id, name) VALUES (%(id)s, %(name)s) " - f"{expected}", + "INSERT INTO users (id, name) VALUES (%(id)s::INTEGER," + f" %(name)s::VARCHAR) {expected}", ) @fixtures.CacheKeySuite.run_suite_tests @@ -3496,9 +3522,9 @@ class InsertOnConflictTest( stmt.on_conflict_do_update( constraint=users.primary_key, set_=stmt.excluded ), - "INSERT INTO users (id, name) VALUES (%(id)s, %(name)s) ON " - "CONFLICT (id) DO UPDATE " - "SET id = excluded.id, name = excluded.name", + "INSERT INTO users (id, name) VALUES (%(id)s::INTEGER," + " %(name)s::VARCHAR) ON CONFLICT (id) DO UPDATE SET id =" + " excluded.id, name = excluded.name", ) else: users_w_key = self.tables.users_w_key @@ -3510,10 +3536,10 @@ class InsertOnConflictTest( stmt.on_conflict_do_update( constraint=users_w_key.primary_key, set_=stmt.excluded ), - "INSERT INTO users_w_key (id, name) " - "VALUES (%(id)s, %(name_keyed)s) ON " - "CONFLICT (id) DO UPDATE " - "SET id = excluded.id, name = excluded.name", + "INSERT INTO users_w_key (id, name) VALUES" + " (%(id)s::INTEGER, %(name_keyed)s::VARCHAR) ON CONFLICT" + " (id) DO UPDATE SET id = excluded.id, name =" + " excluded.name", ) else: self.assert_compile( @@ -3524,10 +3550,10 @@ class InsertOnConflictTest( "name_keyed": stmt.excluded.name_keyed, }, ), - "INSERT INTO users_w_key (id, name) " - "VALUES (%(id)s, %(name_keyed)s) ON " - "CONFLICT (id) DO UPDATE " - "SET id = excluded.id, name = excluded.name", + "INSERT INTO users_w_key (id, name) VALUES" + " (%(id)s::INTEGER, %(name_keyed)s::VARCHAR) ON CONFLICT" + " (id) DO UPDATE SET id = excluded.id, name =" + " excluded.name", ) def test_dont_consume_set_collection(self): @@ -3547,15 +3573,16 @@ class InsertOnConflictTest( ) self.assert_compile( stmt, - "INSERT INTO users (name) VALUES (%(name_m0)s), (%(name_m1)s) " - "ON CONFLICT (name) DO UPDATE SET name = excluded.name", + "INSERT INTO users (name) VALUES (%(name_m0)s::VARCHAR)," + " (%(name_m1)s::VARCHAR) ON CONFLICT (name) DO UPDATE SET name =" + " excluded.name", ) stmt = stmt.returning(users) self.assert_compile( stmt, - "INSERT INTO users (name) VALUES (%(name_m0)s), (%(name_m1)s) " - "ON CONFLICT (name) DO UPDATE SET name = excluded.name " - "RETURNING users.id, users.name", + "INSERT INTO users (name) VALUES (%(name_m0)s::VARCHAR)," + " (%(name_m1)s::VARCHAR) ON CONFLICT (name) DO UPDATE SET name =" + " excluded.name RETURNING users.id, users.name", ) def test_on_conflict_do_no_call_twice(self): @@ -3608,8 +3635,8 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - "(%(name)s) ON CONFLICT DO NOTHING", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) ON CONFLICT" + " DO NOTHING", ) def test_do_nothing_index_elements_target(self): @@ -3620,8 +3647,8 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - "(%(name)s) ON CONFLICT (myid) DO NOTHING", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) ON CONFLICT" + " (myid) DO NOTHING", ) def test_do_update_set_clause_none(self): @@ -3632,10 +3659,9 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (myid, name) VALUES " - "(%(myid)s, %(name)s) ON CONFLICT (myid) " - "DO UPDATE SET name = %(param_1)s, " - "description = %(param_2)s", + "INSERT INTO mytable (myid, name) VALUES (%(myid)s::INTEGER," + " %(name)s::VARCHAR) ON CONFLICT (myid) DO UPDATE SET name =" + " %(param_1)s::VARCHAR, description = %(param_2)s::VARCHAR", { "myid": 1, "name": "foo", @@ -3657,10 +3683,9 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (myid, name) VALUES " - "(%(myid)s, %(name)s) ON CONFLICT (myid) " - "DO UPDATE SET name = %(param_1)s, " - "description = %(param_2)s", + "INSERT INTO mytable (myid, name) VALUES (%(myid)s::INTEGER," + " %(name)s::VARCHAR) ON CONFLICT (myid) DO UPDATE SET name =" + " %(param_1)s::VARCHAR, description = %(param_2)s::VARCHAR", { "myid": 1, "name": "foo", @@ -3679,10 +3704,9 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (myid, name) VALUES " - "(%(myid)s, %(name)s) ON CONFLICT (myid) " - "DO UPDATE SET name = %(param_1)s, " - "description = NULL", + "INSERT INTO mytable (myid, name) VALUES (%(myid)s::INTEGER," + " %(name)s::VARCHAR) ON CONFLICT (myid) DO UPDATE SET name =" + " %(param_1)s::VARCHAR, description = NULL", {"myid": 1, "name": "foo", "param_1": "I'm a name"}, ) @@ -3699,10 +3723,9 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (myid, name) VALUES " - "(%(myid)s, %(name)s) ON CONFLICT (myid) " - "DO UPDATE SET name = excluded.name, " - "description = excluded.description", + "INSERT INTO mytable (myid, name) VALUES (%(myid)s::INTEGER," + " %(name)s::VARCHAR) ON CONFLICT (myid) DO UPDATE SET name =" + " excluded.name, description = excluded.description", ) def test_do_update_str_index_elements_target_two(self): @@ -3712,9 +3735,8 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - "(%(name)s) ON CONFLICT (myid) " - "DO UPDATE SET name = excluded.name", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) ON CONFLICT" + " (myid) DO UPDATE SET name = excluded.name", ) def test_do_update_col_index_elements_target(self): @@ -3725,9 +3747,8 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - "(%(name)s) ON CONFLICT (myid) " - "DO UPDATE SET name = excluded.name", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) ON CONFLICT" + " (myid) DO UPDATE SET name = excluded.name", ) def test_do_update_unnamed_pk_constraint_target(self): @@ -3738,9 +3759,9 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (myid, name) VALUES " - "(%(myid)s, %(name)s) ON CONFLICT (myid) " - "DO UPDATE SET name = excluded.name", + "INSERT INTO mytable (myid, name) VALUES (%(myid)s::INTEGER," + " %(name)s::VARCHAR) ON CONFLICT (myid) DO UPDATE SET name =" + " excluded.name", ) def test_do_update_pk_constraint_index_elements_target(self): @@ -3751,9 +3772,9 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (myid, name) VALUES " - "(%(myid)s, %(name)s) ON CONFLICT (myid) " - "DO UPDATE SET name = excluded.name", + "INSERT INTO mytable (myid, name) VALUES (%(myid)s::INTEGER," + " %(name)s::VARCHAR) ON CONFLICT (myid) DO UPDATE SET name =" + " excluded.name", ) def test_do_update_named_unique_constraint_target(self): @@ -3763,9 +3784,8 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - "(%(name)s) ON CONFLICT ON CONSTRAINT uq_name " - "DO UPDATE SET myid = excluded.myid", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) ON CONFLICT" + " ON CONSTRAINT uq_name DO UPDATE SET myid = excluded.myid", ) def test_do_update_string_constraint_target(self): @@ -3775,9 +3795,8 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - "(%(name)s) ON CONFLICT ON CONSTRAINT uq_name " - "DO UPDATE SET myid = excluded.myid", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) ON CONFLICT" + " ON CONSTRAINT uq_name DO UPDATE SET myid = excluded.myid", ) def test_do_nothing_quoted_string_constraint_target(self): @@ -3786,9 +3805,8 @@ class InsertOnConflictTest( i = i.on_conflict_do_nothing(constraint="Some Constraint Name") self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - '(%(name)s) ON CONFLICT ON CONSTRAINT "Some Constraint Name" ' - "DO NOTHING", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) ON CONFLICT" + ' ON CONSTRAINT "Some Constraint Name" DO NOTHING', ) def test_do_nothing_super_long_name_constraint_target(self): @@ -3811,10 +3829,10 @@ class InsertOnConflictTest( i = i.on_conflict_do_nothing(constraint=uq) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES (%(name)s) ON CONFLICT " - "ON CONSTRAINT " - "some_table_name_thats_really_really_some_column_name_th_f7ab " - "DO NOTHING", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) ON CONFLICT" + " ON CONSTRAINT" + " some_table_name_thats_really_really_some_column_name_th_f7ab DO" + " NOTHING", ) def test_do_nothing_quoted_named_constraint_target(self): @@ -3826,9 +3844,8 @@ class InsertOnConflictTest( i = i.on_conflict_do_nothing(constraint=unique_constr) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - '(%(name)s) ON CONFLICT ON CONSTRAINT "Some Constraint Name" ' - "DO NOTHING", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) ON CONFLICT" + ' ON CONSTRAINT "Some Constraint Name" DO NOTHING', ) def test_do_update_index_elements_where_target(self): @@ -3842,10 +3859,9 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - "(%(name)s) ON CONFLICT (name) " - "WHERE name > %(name_1)s " - "DO UPDATE SET name = excluded.name", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) ON CONFLICT" + " (name) WHERE name > %(name_1)s::VARCHAR DO UPDATE SET name =" + " excluded.name", ) def test_do_update_index_elements_where_target_multivalues(self): @@ -3861,11 +3877,10 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (name) " - "VALUES (%(name_m0)s), (%(name_m1)s), (%(name_m2)s) " - "ON CONFLICT (name) " - "WHERE name > %(name_1)s " - "DO UPDATE SET name = excluded.name", + "INSERT INTO mytable (name) VALUES (%(name_m0)s::VARCHAR)," + " (%(name_m1)s::VARCHAR), (%(name_m2)s::VARCHAR) ON CONFLICT" + " (name) WHERE name > %(name_1)s::VARCHAR DO UPDATE SET name =" + " excluded.name", checkparams={ "name_1": "m", "name_m0": "foo", @@ -3886,10 +3901,9 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - "(%(name)s) ON CONFLICT (name) " - "WHERE name > %(name_1)s " - "DO UPDATE SET name = excluded.name", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) ON CONFLICT" + " (name) WHERE name > %(name_1)s::VARCHAR DO UPDATE SET name =" + " excluded.name", ) def test_do_update_unnamed_exclude_constraint_target(self): @@ -3899,10 +3913,9 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - "(%(name)s) ON CONFLICT (name, description) " - "WHERE description != %(description_1)s " - "DO UPDATE SET name = excluded.name", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) ON CONFLICT" + " (name, description) WHERE description !=" + " %(description_1)s::VARCHAR DO UPDATE SET name = excluded.name", ) def test_do_update_unnamed_exclude_constraint_string_target(self): @@ -3913,10 +3926,9 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - "(%(name)s) ON CONFLICT (name, description) " - "WHERE description != 'foo' " - "DO UPDATE SET name = excluded.name", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) ON CONFLICT" + " (name, description) WHERE description != 'foo' DO UPDATE SET" + " name = excluded.name", ) def test_do_update_add_whereclause(self): @@ -3931,12 +3943,11 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - "(%(name)s) ON CONFLICT (name, description) " - "WHERE description != %(description_1)s " - "DO UPDATE SET name = excluded.name " - "WHERE mytable.name != %(name_1)s " - "AND mytable.description != %(description_2)s", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) ON CONFLICT" + " (name, description) WHERE description !=" + " %(description_1)s::VARCHAR DO UPDATE SET name = excluded.name" + " WHERE mytable.name != %(name_1)s::VARCHAR AND" + " mytable.description != %(description_2)s::VARCHAR", ) def test_do_update_str_index_where(self): @@ -3951,12 +3962,10 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - "(%(name)s) ON CONFLICT (name, description) " - "WHERE description != 'foo' " - "DO UPDATE SET name = excluded.name " - "WHERE mytable.name != %(name_1)s " - "AND mytable.description != %(description_1)s", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) ON CONFLICT" + " (name, description) WHERE description != 'foo' DO UPDATE SET" + " name = excluded.name WHERE mytable.name != %(name_1)s::VARCHAR" + " AND mytable.description != %(description_1)s::VARCHAR", ) def test_do_update_add_whereclause_references_excluded(self): @@ -3968,11 +3977,10 @@ class InsertOnConflictTest( ) self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - "(%(name)s) ON CONFLICT (name, description) " - "WHERE description != %(description_1)s " - "DO UPDATE SET name = excluded.name " - "WHERE mytable.name != excluded.name", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) ON CONFLICT" + " (name, description) WHERE description !=" + " %(description_1)s::VARCHAR DO UPDATE SET name = excluded.name" + " WHERE mytable.name != excluded.name", ) def test_do_update_additional_colnames(self): @@ -3987,11 +3995,10 @@ class InsertOnConflictTest( ): self.assert_compile( i, - "INSERT INTO mytable (name) VALUES " - "(%(name)s) ON CONFLICT (name, description) " - "WHERE description != %(description_1)s " - "DO UPDATE SET name = %(param_1)s, " - "unknown = %(param_2)s", + "INSERT INTO mytable (name) VALUES (%(name)s::VARCHAR) ON" + " CONFLICT (name, description) WHERE description !=" + " %(description_1)s::VARCHAR DO UPDATE SET name =" + " %(param_1)s::VARCHAR, unknown = %(param_2)s", checkparams={ "name": "bar", "description_1": "foo", @@ -4016,14 +4023,11 @@ class InsertOnConflictTest( self.assert_compile( stmt, - "WITH i_upsert AS " - "(INSERT INTO mytable (name) VALUES (%(param_1)s) " - "ON CONFLICT (name, description) " - "WHERE description != %(description_1)s " - "DO UPDATE SET name = excluded.name " - "WHERE mytable.name != excluded.name RETURNING 1) " - "SELECT i_upsert.1 " - "FROM i_upsert", + "WITH i_upsert AS (INSERT INTO mytable (name) VALUES" + " (%(param_1)s::VARCHAR) ON CONFLICT (name, description) WHERE" + " description != %(description_1)s::VARCHAR DO UPDATE SET name =" + " excluded.name WHERE mytable.name != excluded.name RETURNING 1)" + " SELECT i_upsert.1 FROM i_upsert", ) def test_combined_with_cte(self): @@ -4043,10 +4047,9 @@ class InsertOnConflictTest( self.assert_compile( update_stmt, - "WITH deletions AS (DELETE FROM t WHERE t.c1 < %(c1_1)s) " - "INSERT INTO t (c1, c2) VALUES (%(c1_m0)s, %(c2_m0)s) " - "ON CONFLICT (c1) DO UPDATE SET c1 = excluded.c1, " - "c2 = excluded.c2", + "WITH deletions AS (DELETE FROM t WHERE t.c1 < %(c1_1)s::INTEGER)" + " INSERT INTO t (c1, c2) VALUES (%(c1_m0)s, %(c2_m0)s) ON CONFLICT" + " (c1) DO UPDATE SET c1 = excluded.c1, c2 = excluded.c2", checkparams={"c1_m0": 1, "c2_m0": 2, "c1_1": 1}, ) @@ -4384,8 +4387,9 @@ class FullTextSearchTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( s, - "SELECT mytable.id FROM mytable WHERE " - "to_tsquery(%(to_tsquery_1)s) <-> to_tsquery(%(to_tsquery_2)s)", + "SELECT mytable.id FROM mytable WHERE" + " to_tsquery(%(to_tsquery_1)s::VARCHAR) <->" + " to_tsquery(%(to_tsquery_2)s::VARCHAR)", {"to_tsquery_1": "fat", "to_tsquery_2": "rat"}, ) @@ -4397,9 +4401,11 @@ class FullTextSearchTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( s, - "SELECT mytable.id FROM mytable WHERE " - "to_tsquery(%(to_tsquery_1)s, %(to_tsquery_2)s) <-> " - "to_tsquery(%(to_tsquery_3)s, %(to_tsquery_4)s)", + "SELECT mytable.id FROM mytable WHERE" + " to_tsquery(%(to_tsquery_1)s::REGCONFIG," + " %(to_tsquery_2)s::VARCHAR) <->" + " to_tsquery(%(to_tsquery_3)s::REGCONFIG," + " %(to_tsquery_4)s::VARCHAR)", { "to_tsquery_1": "english", "to_tsquery_2": "fat", @@ -4414,9 +4420,8 @@ class FullTextSearchTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( s, - "SELECT mytable.id " - "FROM mytable " - "WHERE mytable.title @@ plainto_tsquery(%(title_1)s)", + "SELECT mytable.id FROM mytable WHERE mytable.title @@" + " plainto_tsquery(%(title_1)s::VARCHAR)", ) def test_match_regconfig(self): @@ -4427,10 +4432,8 @@ class FullTextSearchTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( s, - "SELECT mytable.id " - "FROM mytable " - "WHERE mytable.title @@ " - "plainto_tsquery('english', %(title_1)s)", + "SELECT mytable.id FROM mytable WHERE mytable.title @@" + " plainto_tsquery('english', %(title_1)s::VARCHAR)", ) def test_match_tsvector(self): @@ -4439,10 +4442,8 @@ class FullTextSearchTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( s, - "SELECT mytable.id " - "FROM mytable " - "WHERE to_tsvector(mytable.title) " - "@@ plainto_tsquery(%(to_tsvector_1)s)", + "SELECT mytable.id FROM mytable WHERE to_tsvector(mytable.title)" + " @@ plainto_tsquery(%(to_tsvector_1)s::VARCHAR)", ) def test_match_tsvectorconfig(self): @@ -4453,10 +4454,9 @@ class FullTextSearchTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( s, - "SELECT mytable.id " - "FROM mytable " - "WHERE to_tsvector(%(to_tsvector_1)s, mytable.title) @@ " - "plainto_tsquery(%(to_tsvector_2)s)", + "SELECT mytable.id FROM mytable WHERE" + " to_tsvector(%(to_tsvector_1)s::REGCONFIG, mytable.title) @@" + " plainto_tsquery(%(to_tsvector_2)s::VARCHAR)", ) def test_match_tsvectorconfig_regconfig(self): @@ -4467,10 +4467,9 @@ class FullTextSearchTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( s, - "SELECT mytable.id " - "FROM mytable " - "WHERE to_tsvector(%(to_tsvector_1)s, mytable.title) @@ " - """plainto_tsquery('english', %(to_tsvector_2)s)""", + "SELECT mytable.id FROM mytable WHERE" + " to_tsvector(%(to_tsvector_1)s::REGCONFIG, mytable.title) @@" + " plainto_tsquery('english', %(to_tsvector_2)s::VARCHAR)", ) @testing.combinations( @@ -4553,7 +4552,7 @@ class FullTextSearchTest(fixtures.TestBase, AssertsCompiledSQL): to_ts_func(regconfig, "python") ) expected = ( - f"to_tsvector(matchtable.title) @@ " + "to_tsvector(matchtable.title) @@ " f"{fn_name}($1::REGCONFIG, $2::VARCHAR)" ) @@ -4613,28 +4612,28 @@ class FullTextSearchTest(fixtures.TestBase, AssertsCompiledSQL): if regconfig is None and not include_options: self.assert_compile( stmt, - f"SELECT ts_headline($1::VARCHAR, " + "SELECT ts_headline($1::VARCHAR, " f"{tsquery_str}) AS ts_headline_1", dialect="postgresql+asyncpg", ) elif regconfig is None and include_options: self.assert_compile( stmt, - f"SELECT ts_headline($1::VARCHAR, " + "SELECT ts_headline($1::VARCHAR, " f"{tsquery_str}, $4::VARCHAR) AS ts_headline_1", dialect="postgresql+asyncpg", ) elif regconfig is not None and not include_options: self.assert_compile( stmt, - f"SELECT ts_headline($1::REGCONFIG, $2::VARCHAR, " + "SELECT ts_headline($1::REGCONFIG, $2::VARCHAR, " f"{tsquery_str}) AS ts_headline_1", dialect="postgresql+asyncpg", ) else: self.assert_compile( stmt, - f"SELECT ts_headline($1::REGCONFIG, $2::VARCHAR, " + "SELECT ts_headline($1::REGCONFIG, $2::VARCHAR, " f"{tsquery_str}, $5::VARCHAR) " "AS ts_headline_1", dialect="postgresql+asyncpg", @@ -4652,7 +4651,7 @@ class RegexpTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_regexp_match(self): self.assert_compile( self.table.c.myid.regexp_match("pattern"), - "mytable.myid ~ %(myid_1)s", + "mytable.myid ~ %(myid_1)s::VARCHAR", checkparams={"myid_1": "pattern"}, ) @@ -4666,28 +4665,28 @@ class RegexpTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_regexp_match_str(self): self.assert_compile( literal("string").regexp_match(self.table.c.name), - "%(param_1)s ~ mytable.name", + "%(param_1)s::VARCHAR ~ mytable.name", checkparams={"param_1": "string"}, ) def test_regexp_match_flags(self): self.assert_compile( self.table.c.myid.regexp_match("pattern", flags="ig"), - "mytable.myid ~ CONCAT('(?', 'ig', ')', %(myid_1)s)", + "mytable.myid ~ CONCAT('(?', 'ig', ')', %(myid_1)s::VARCHAR)", checkparams={"myid_1": "pattern"}, ) def test_regexp_match_flags_ignorecase(self): self.assert_compile( self.table.c.myid.regexp_match("pattern", flags="i"), - "mytable.myid ~* %(myid_1)s", + "mytable.myid ~* %(myid_1)s::VARCHAR", checkparams={"myid_1": "pattern"}, ) def test_not_regexp_match(self): self.assert_compile( ~self.table.c.myid.regexp_match("pattern"), - "mytable.myid !~ %(myid_1)s", + "mytable.myid !~ %(myid_1)s::VARCHAR", checkparams={"myid_1": "pattern"}, ) @@ -4701,49 +4700,51 @@ class RegexpTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_not_regexp_match_str(self): self.assert_compile( ~literal("string").regexp_match(self.table.c.name), - "%(param_1)s !~ mytable.name", + "%(param_1)s::VARCHAR !~ mytable.name", checkparams={"param_1": "string"}, ) def test_not_regexp_match_flags(self): self.assert_compile( ~self.table.c.myid.regexp_match("pattern", flags="ig"), - "mytable.myid !~ CONCAT('(?', 'ig', ')', %(myid_1)s)", + "mytable.myid !~ CONCAT('(?', 'ig', ')', %(myid_1)s::VARCHAR)", checkparams={"myid_1": "pattern"}, ) def test_not_regexp_match_flags_ignorecase(self): self.assert_compile( ~self.table.c.myid.regexp_match("pattern", flags="i"), - "mytable.myid !~* %(myid_1)s", + "mytable.myid !~* %(myid_1)s::VARCHAR", checkparams={"myid_1": "pattern"}, ) def test_regexp_replace(self): self.assert_compile( self.table.c.myid.regexp_replace("pattern", "replacement"), - "REGEXP_REPLACE(mytable.myid, %(myid_1)s, %(myid_2)s)", + "REGEXP_REPLACE(mytable.myid, %(myid_1)s::VARCHAR," + " %(myid_2)s::VARCHAR)", checkparams={"myid_1": "pattern", "myid_2": "replacement"}, ) def test_regexp_replace_column(self): self.assert_compile( self.table.c.myid.regexp_replace("pattern", self.table.c.name), - "REGEXP_REPLACE(mytable.myid, %(myid_1)s, mytable.name)", + "REGEXP_REPLACE(mytable.myid, %(myid_1)s::VARCHAR, mytable.name)", checkparams={"myid_1": "pattern"}, ) def test_regexp_replace_column2(self): self.assert_compile( self.table.c.myid.regexp_replace(self.table.c.name, "replacement"), - "REGEXP_REPLACE(mytable.myid, mytable.name, %(myid_1)s)", + "REGEXP_REPLACE(mytable.myid, mytable.name, %(myid_1)s::VARCHAR)", checkparams={"myid_1": "replacement"}, ) def test_regexp_replace_string(self): self.assert_compile( literal("string").regexp_replace("pattern", self.table.c.name), - "REGEXP_REPLACE(%(param_1)s, %(param_2)s, mytable.name)", + "REGEXP_REPLACE(%(param_1)s::VARCHAR, %(param_2)s::VARCHAR," + " mytable.name)", checkparams={"param_2": "pattern", "param_1": "string"}, ) @@ -4752,11 +4753,9 @@ class RegexpTest(fixtures.TestBase, testing.AssertsCompiledSQL): self.table.c.myid.regexp_replace( "pattern", "replacement", flags="ig" ), - "REGEXP_REPLACE(mytable.myid, %(myid_1)s, %(myid_2)s, 'ig')", - checkparams={ - "myid_1": "pattern", - "myid_2": "replacement", - }, + "REGEXP_REPLACE(mytable.myid, %(myid_1)s::VARCHAR," + " %(myid_2)s::VARCHAR, 'ig')", + checkparams={"myid_1": "pattern", "myid_2": "replacement"}, ) def test_regexp_replace_flags_safestring(self): @@ -4764,11 +4763,9 @@ class RegexpTest(fixtures.TestBase, testing.AssertsCompiledSQL): self.table.c.myid.regexp_replace( "pattern", "replacement", flags="i'g" ), - "REGEXP_REPLACE(mytable.myid, %(myid_1)s, %(myid_2)s, 'i''g')", - checkparams={ - "myid_1": "pattern", - "myid_2": "replacement", - }, + "REGEXP_REPLACE(mytable.myid, %(myid_1)s::VARCHAR," + " %(myid_2)s::VARCHAR, 'i''g')", + checkparams={"myid_1": "pattern", "myid_2": "replacement"}, ) @testing.combinations( @@ -4776,46 +4773,64 @@ class RegexpTest(fixtures.TestBase, testing.AssertsCompiledSQL): 5, 10, {}, - "OFFSET (%(param_1)s) ROWS FETCH FIRST (%(param_2)s) ROWS ONLY", + ( + "OFFSET (%(param_1)s::INTEGER) ROWS FETCH FIRST" + " (%(param_2)s::INTEGER) ROWS ONLY" + ), {"param_1": 10, "param_2": 5}, ), - (None, 10, {}, "LIMIT ALL OFFSET %(param_1)s", {"param_1": 10}), + ( + None, + 10, + {}, + "LIMIT ALL OFFSET %(param_1)s::INTEGER", + {"param_1": 10}, + ), ( 5, None, {}, - "FETCH FIRST (%(param_1)s) ROWS ONLY", + "FETCH FIRST (%(param_1)s::INTEGER) ROWS ONLY", {"param_1": 5}, ), ( 0, 0, {}, - "OFFSET (%(param_1)s) ROWS FETCH FIRST (%(param_2)s) ROWS ONLY", + ( + "OFFSET (%(param_1)s::INTEGER) ROWS FETCH FIRST" + " (%(param_2)s::INTEGER) ROWS ONLY" + ), {"param_1": 0, "param_2": 0}, ), ( 5, 10, {"percent": True}, - "OFFSET (%(param_1)s) ROWS FETCH FIRST " - "(%(param_2)s) PERCENT ROWS ONLY", + ( + "OFFSET (%(param_1)s::INTEGER) ROWS FETCH FIRST " + "(%(param_2)s::INTEGER) PERCENT ROWS ONLY" + ), {"param_1": 10, "param_2": 5}, ), ( 5, 10, {"percent": True, "with_ties": True}, - "OFFSET (%(param_1)s) ROWS FETCH FIRST (%(param_2)s)" - " PERCENT ROWS WITH TIES", + ( + "OFFSET (%(param_1)s::INTEGER) ROWS FETCH FIRST" + " (%(param_2)s::INTEGER) PERCENT ROWS WITH TIES" + ), {"param_1": 10, "param_2": 5}, ), ( 5, 10, {"with_ties": True}, - "OFFSET (%(param_1)s) ROWS FETCH FIRST " - "(%(param_2)s) ROWS WITH TIES", + ( + "OFFSET (%(param_1)s::INTEGER) ROWS FETCH FIRST " + "(%(param_2)s::INTEGER) ROWS WITH TIES" + ), {"param_1": 10, "param_2": 5}, ), ( @@ -4836,7 +4851,10 @@ class RegexpTest(fixtures.TestBase, testing.AssertsCompiledSQL): bindparam("Q", 3), bindparam("Y", 7), {}, - "OFFSET (%(Y)s) ROWS FETCH FIRST (%(Q)s) ROWS ONLY", + ( + "OFFSET (%(Y)s::INTEGER) ROWS FETCH FIRST (%(Q)s::INTEGER)" + " ROWS ONLY" + ), {"Q": 3, "Y": 7}, ), ( diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py index e01c777428..d374a72e17 100644 --- a/test/dialect/postgresql/test_types.py +++ b/test/dialect/postgresql/test_types.py @@ -871,9 +871,11 @@ class NamedTypeTest( go, [ ( - "CREATE TABLE foo (\tbar " - "VARCHAR(5), \tCONSTRAINT myenum CHECK " - "(bar IN ('one', 'two', 'three')))", + ( + "CREATE TABLE foo (\tbar " + "VARCHAR(5), \tCONSTRAINT myenum CHECK " + "(bar IN ('one', 'two', 'three')))" + ), {}, ) ], @@ -906,9 +908,11 @@ class NamedTypeTest( go, [ ( - "CREATE TABLE foo (\tbar " - "VARCHAR(1), \tCONSTRAINT myenum CHECK " - "(bar IN ('B', 'Ü')))", + ( + "CREATE TABLE foo (\tbar " + "VARCHAR(1), \tCONSTRAINT myenum CHECK " + "(bar IN ('B', 'Ü')))" + ), {}, ) ], @@ -1237,9 +1241,11 @@ class NamedTypeTest( go, [ ( - "CREATE TABLE foo (bar " - "VARCHAR(5), CONSTRAINT myenum CHECK " - "(bar IN ('one', 'two', 'three')))", + ( + "CREATE TABLE foo (bar " + "VARCHAR(5), CONSTRAINT myenum CHECK " + "(bar IN ('one', 'two', 'three')))" + ), {}, ) ], @@ -1993,8 +1999,9 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): self.assert_compile( obj, - "ARRAY[%(param_1)s, %(param_2)s] || " - "ARRAY[%(param_3)s, %(param_4)s, %(param_5)s]", + "ARRAY[%(param_1)s::INTEGER, %(param_2)s::INTEGER] ||" + " ARRAY[%(param_3)s::INTEGER, %(param_4)s::INTEGER," + " %(param_5)s::INTEGER]", params={ "param_1": 1, "param_2": 2, @@ -2005,8 +2012,9 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): ) self.assert_compile( obj[1], - "(ARRAY[%(param_1)s, %(param_2)s] || ARRAY[%(param_3)s, " - "%(param_4)s, %(param_5)s])[%(param_6)s]", + "(ARRAY[%(param_1)s::INTEGER, %(param_2)s::INTEGER] ||" + " ARRAY[%(param_3)s::INTEGER, %(param_4)s::INTEGER," + " %(param_5)s::INTEGER])[%(param_6)s::INTEGER]", params={ "param_1": 1, "param_2": 2, @@ -2023,18 +2031,21 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): self.assert_compile( obj, - "ARRAY[ARRAY[%(param_1)s, %(param_2)s], " - "ARRAY[%(param_3)s, %(param_4)s]]", + "ARRAY[ARRAY[%(param_1)s::INTEGER, %(param_2)s::INTEGER]," + " ARRAY[%(param_3)s::INTEGER, %(param_4)s::INTEGER]]", ) self.assert_compile( obj[1], - "(ARRAY[ARRAY[%(param_1)s, %(param_2)s], " - "ARRAY[%(param_3)s, %(param_4)s]])[%(param_5)s]", + "(ARRAY[ARRAY[%(param_1)s::INTEGER, %(param_2)s::INTEGER]," + " ARRAY[%(param_3)s::INTEGER," + " %(param_4)s::INTEGER]])[%(param_5)s::INTEGER]", ) self.assert_compile( obj[1][0], - "(ARRAY[ARRAY[%(param_1)s, %(param_2)s], " - "ARRAY[%(param_3)s, %(param_4)s]])[%(param_5)s][%(param_6)s]", + "(ARRAY[ARRAY[%(param_1)s::INTEGER, %(param_2)s::INTEGER]," + " ARRAY[%(param_3)s::INTEGER," + " %(param_4)s::INTEGER]])[%(param_5)s::INTEGER]" + "[%(param_6)s::INTEGER]", ) def test_array_type_render_str(self): @@ -2127,7 +2138,7 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): col = column("x", postgresql.ARRAY(Integer)) self.assert_compile( select(col[3]), - "SELECT x[%(x_1)s] AS anon_1", + "SELECT x[%(x_1)s::INTEGER] AS anon_1", checkparams={"x_1": 3}, ) @@ -2137,7 +2148,7 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): with _array_any_deprecation(): self.assert_compile( select(col.any(7, operator=operators.lt)), - "SELECT %(x_1)s < ANY (x) AS anon_1", + "SELECT %(x_1)s::INTEGER < ANY (x) AS anon_1", checkparams={"x_1": 7}, ) @@ -2147,7 +2158,7 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): with _array_any_deprecation(): self.assert_compile( select(col.all(7, operator=operators.lt)), - "SELECT %(x_1)s < ALL (x) AS anon_1", + "SELECT %(x_1)s::INTEGER < ALL (x) AS anon_1", checkparams={"x_1": 7}, ) @@ -2155,8 +2166,8 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): col = column("x", postgresql.ARRAY(Integer)) self.assert_compile( select(col.contains(array([4, 5, 6]))), - "SELECT x @> ARRAY[%(param_1)s, %(param_2)s, %(param_3)s] " - "AS anon_1", + "SELECT x @> ARRAY[%(param_1)s::INTEGER, %(param_2)s::INTEGER," + " %(param_3)s::INTEGER] AS anon_1", checkparams={"param_1": 4, "param_3": 6, "param_2": 5}, ) @@ -2173,8 +2184,8 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): col = column("x", postgresql.ARRAY(Integer)) self.assert_compile( select(col.contained_by(array([4, 5, 6]))), - "SELECT x <@ ARRAY[%(param_1)s, %(param_2)s, %(param_3)s] " - "AS anon_1", + "SELECT x <@ ARRAY[%(param_1)s::INTEGER, %(param_2)s::INTEGER," + " %(param_3)s::INTEGER] AS anon_1", checkparams={"param_1": 4, "param_3": 6, "param_2": 5}, ) @@ -2182,8 +2193,8 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): col = column("x", postgresql.ARRAY(Integer)) self.assert_compile( select(col.overlap(array([4, 5, 6]))), - "SELECT x && ARRAY[%(param_1)s, %(param_2)s, %(param_3)s] " - "AS anon_1", + "SELECT x && ARRAY[%(param_1)s::INTEGER, %(param_2)s::INTEGER," + " %(param_3)s::INTEGER] AS anon_1", checkparams={"param_1": 4, "param_3": 6, "param_2": 5}, ) @@ -2191,8 +2202,8 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): col = column("x", postgresql.ARRAY(Integer)) self.assert_compile( select(col.overlap(any_(array([4, 5, 6])))), - "SELECT x && ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s]) " - "AS anon_1", + "SELECT x && ANY (ARRAY[%(param_1)s::INTEGER," + " %(param_2)s::INTEGER, %(param_3)s::INTEGER]) AS anon_1", checkparams={"param_1": 4, "param_3": 6, "param_2": 5}, ) @@ -2200,8 +2211,8 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): col = column("x", postgresql.ARRAY(Integer)) self.assert_compile( select(col.contains(any_(array([4, 5, 6])))), - "SELECT x @> ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s]) " - "AS anon_1", + "SELECT x @> ANY (ARRAY[%(param_1)s::INTEGER," + " %(param_2)s::INTEGER, %(param_3)s::INTEGER]) AS anon_1", checkparams={"param_1": 4, "param_3": 6, "param_2": 5}, ) @@ -2209,7 +2220,7 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): col = column("x", postgresql.ARRAY(Integer)) self.assert_compile( select(col[5:10]), - "SELECT x[%(x_1)s:%(x_2)s] AS anon_1", + "SELECT x[%(x_1)s::INTEGER:%(x_2)s::INTEGER] AS anon_1", checkparams={"x_2": 10, "x_1": 5}, ) @@ -2217,7 +2228,7 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): col = column("x", postgresql.ARRAY(Integer, dimensions=2)) self.assert_compile( select(col[3][5]), - "SELECT x[%(x_1)s][%(param_1)s] AS anon_1", + "SELECT x[%(x_1)s::INTEGER][%(param_1)s::INTEGER] AS anon_1", checkparams={"x_1": 3, "param_1": 5}, ) @@ -2227,7 +2238,8 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): self.assert_compile( select(col + literal), - "SELECT x || ARRAY[%(param_1)s, %(param_2)s] AS anon_1", + "SELECT x || ARRAY[%(param_1)s::INTEGER, %(param_2)s::INTEGER] AS" + " anon_1", checkparams={"param_1": 4, "param_2": 5}, ) @@ -2283,9 +2295,12 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): ) self.assert_compile( stmt, - "SELECT (array_cat(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s], " - "ARRAY[%(param_4)s, %(param_5)s, %(param_6)s]))" - "[%(param_7)s:%(param_8)s] AS anon_1", + "SELECT (array_cat(ARRAY[%(param_1)s::INTEGER," + " %(param_2)s::INTEGER, %(param_3)s::INTEGER]," + " ARRAY[%(param_4)s::INTEGER, %(param_5)s::INTEGER," + " %(param_6)s::INTEGER]))" + "[%(param_7)s::INTEGER:%(param_8)s::INTEGER]" + " AS anon_1", ) self.assert_compile( @@ -2294,8 +2309,10 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): array([4, 5, 6]), type_=postgresql.ARRAY(Integer), )[3], - "(array_cat(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s], " - "ARRAY[%(param_4)s, %(param_5)s, %(param_6)s]))[%(array_cat_1)s]", + "(array_cat(ARRAY[%(param_1)s::INTEGER, %(param_2)s::INTEGER," + " %(param_3)s::INTEGER], ARRAY[%(param_4)s::INTEGER," + " %(param_5)s::INTEGER," + " %(param_6)s::INTEGER]))[%(array_cat_1)s::INTEGER]", ) def test_array_agg_generic(self): @@ -3751,13 +3768,13 @@ class TimestampTest( text("select :parameter").bindparams( parameter=datetime.timedelta(days=2) ), - ("select make_interval(secs=>172800.0)"), + "select make_interval(secs=>172800.0)", ), ( text("select :parameter").bindparams( parameter=datetime.timedelta(days=730, seconds=2323213392), ), - ("select make_interval(secs=>2386285392.0)"), + "select make_interval(secs=>2386285392.0)", ), ) def test_interval_literal_processor_compiled(self, type_, expected): @@ -4087,8 +4104,8 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): stmt = select(self.test_table).where(whereclause) self.assert_compile( stmt, - "SELECT test_table.id, test_table.hash FROM test_table " - "WHERE %s" % expected, + "SELECT test_table.id, test_table.hash FROM test_table WHERE %s" + % expected, ) def test_bind_serialize_default(self): @@ -4187,25 +4204,27 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): def test_where_has_key(self): self._test_where( self.hashcol.has_key("foo"), - "test_table.hash ? %(hash_1)s", + "test_table.hash ? %(hash_1)s::VARCHAR", ) def test_where_has_all(self): self._test_where( self.hashcol.has_all(postgresql.array(["1", "2"])), - "test_table.hash ?& ARRAY[%(param_1)s, %(param_2)s]", + "test_table.hash ?& ARRAY[%(param_1)s::VARCHAR," + " %(param_2)s::VARCHAR]", ) def test_where_has_any(self): self._test_where( self.hashcol.has_any(postgresql.array(["1", "2"])), - "test_table.hash ?| ARRAY[%(param_1)s, %(param_2)s]", + "test_table.hash ?| ARRAY[%(param_1)s::VARCHAR," + " %(param_2)s::VARCHAR]", ) def test_where_defined(self): self._test_where( self.hashcol.defined("foo"), - "defined(test_table.hash, %(defined_1)s)", + "defined(test_table.hash, %(defined_1)s::VARCHAR)", ) def test_where_contains(self): @@ -4223,73 +4242,76 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): def test_where_has_key_any(self): self._test_where( self.hashcol.has_key(any_(array(["foo"]))), - "test_table.hash ? ANY (ARRAY[%(param_1)s])", + "test_table.hash ? ANY (ARRAY[%(param_1)s::VARCHAR])", ) def test_where_has_all_any(self): self._test_where( self.hashcol.has_all(any_(postgresql.array(["1", "2"]))), - "test_table.hash ?& ANY (ARRAY[%(param_1)s, %(param_2)s])", + "test_table.hash ?& ANY (ARRAY[%(param_1)s::VARCHAR," + " %(param_2)s::VARCHAR])", ) def test_where_has_any_any(self): self._test_where( self.hashcol.has_any(any_(postgresql.array(["1", "2"]))), - "test_table.hash ?| ANY (ARRAY[%(param_1)s, %(param_2)s])", + "test_table.hash ?| ANY (ARRAY[%(param_1)s::VARCHAR," + " %(param_2)s::VARCHAR])", ) def test_where_contains_any(self): self._test_where( self.hashcol.contains(any_(array(["foo"]))), - "test_table.hash @> ANY (ARRAY[%(param_1)s])", + "test_table.hash @> ANY (ARRAY[%(param_1)s::VARCHAR])", ) def test_where_contained_by_any(self): self._test_where( self.hashcol.contained_by(any_(array(["foo"]))), - "test_table.hash <@ ANY (ARRAY[%(param_1)s])", + "test_table.hash <@ ANY (ARRAY[%(param_1)s::VARCHAR])", ) def test_where_getitem(self): self._test_where( self.hashcol["bar"] == None, # noqa - "test_table.hash[%(hash_1)s] IS NULL", + "test_table.hash[%(hash_1)s::VARCHAR] IS NULL", ) def test_where_getitem_any(self): self._test_where( self.hashcol["bar"] == any_(array(["foo"])), # noqa - "test_table.hash[%(hash_1)s] = ANY (ARRAY[%(param_1)s])", + "test_table.hash[%(hash_1)s::VARCHAR] = ANY" + " (ARRAY[%(param_1)s::VARCHAR])", ) # Test combinations that don't use subscript operator @testing.combinations( ( lambda self: self.hashcol.delete("foo"), - "delete(test_table.hash, %(delete_2)s) AS delete_1", + "delete(test_table.hash, %(delete_2)s::VARCHAR) AS delete_1", True, ), ( lambda self: self.hashcol.delete(postgresql.array(["foo", "bar"])), ( - "delete(test_table.hash, ARRAY[%(param_1)s, %(param_2)s]) " - "AS delete_1" + "delete(test_table.hash, ARRAY[%(param_1)s::VARCHAR," + " %(param_2)s::VARCHAR]) AS delete_1" ), True, ), ( lambda self: self.hashcol.delete(hstore("1", "2")), ( - "delete(test_table.hash, hstore(%(hstore_1)s, %(hstore_2)s)) " - "AS delete_1" + "delete(test_table.hash, hstore(%(hstore_1)s::VARCHAR," + " %(hstore_2)s::VARCHAR)) AS delete_1" ), True, ), ( lambda self: self.hashcol.slice(postgresql.array(["1", "2"])), ( - "slice(test_table.hash, ARRAY[%(param_1)s, %(param_2)s]) " - "AS slice_1" + "slice(test_table.hash, ARRAY[%(param_1)s::VARCHAR," + " %(param_2)s::VARCHAR]) AS slice_1" ), True, ), @@ -4299,13 +4321,16 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): ), ( "test_table.hash || hstore(CAST(test_table.id AS TEXT), " - "%(hstore_1)s) AS anon_1" + "%(hstore_1)s::VARCHAR) AS anon_1" ), True, ), ( lambda self: hstore("foo", "bar") + self.hashcol, - "hstore(%(hstore_1)s, %(hstore_2)s) || test_table.hash AS anon_1", + ( + "hstore(%(hstore_1)s::VARCHAR, %(hstore_2)s::VARCHAR) ||" + " test_table.hash AS anon_1" + ), True, ), ( @@ -4342,12 +4367,15 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): @testing.combinations( ( lambda self: self.hashcol["foo"], - "test_table.hash[%(hash_1)s] AS anon_1", + "test_table.hash[%(hash_1)s::VARCHAR] AS anon_1", True, ), ( lambda self: hstore("foo", "3")["foo"], - "(hstore(%(hstore_1)s, %(hstore_2)s))[%(hstore_3)s] AS anon_1", + ( + "(hstore(%(hstore_1)s::VARCHAR," + " %(hstore_2)s::VARCHAR))[%(hstore_3)s::VARCHAR] AS anon_1" + ), False, ), ( @@ -4355,27 +4383,32 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): postgresql.array(["1", "2"]), postgresql.array(["3", None]) )["1"], ( - "(hstore(ARRAY[%(param_1)s, %(param_2)s], " - "ARRAY[%(param_3)s, NULL]))[%(hstore_1)s] AS anon_1" + "(hstore(ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR]," + " ARRAY[%(param_3)s::VARCHAR, NULL]))[%(hstore_1)s::VARCHAR]" + " AS anon_1" ), False, ), ( lambda self: hstore(postgresql.array(["1", "2", "3", None]))["3"], ( - "(hstore(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, NULL]))" - "[%(hstore_1)s] AS anon_1" + "(hstore(ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR," + " %(param_3)s::VARCHAR, NULL]))[%(hstore_1)s::VARCHAR] AS" + " anon_1" ), False, ), ( lambda self: (self.hashcol + self.hashcol)["foo"], - "(test_table.hash || test_table.hash)[%(param_1)s] AS anon_1", + ( + "(test_table.hash || test_table.hash)[%(param_1)s::VARCHAR] AS" + " anon_1" + ), True, ), ( lambda self: self.hashcol["foo"] != None, # noqa - "test_table.hash[%(hash_1)s] IS NOT NULL AS anon_1", + "test_table.hash[%(hash_1)s::VARCHAR] IS NOT NULL AS anon_1", True, ), ) @@ -4785,7 +4818,7 @@ class _RangeTypeCompilation( def test_data_str(self, fn, op): self._test_clause( fn(self.col, self._data_str()), - f"data_table.range {op} %(range_1)s", + f"data_table.range {op} %(range_1)s::VARCHAR", ( self.col.type if op in self._not_compare_op @@ -4809,7 +4842,7 @@ class _RangeTypeCompilation( def test_data_str_any(self, fn, op): self._test_clause( fn(self.col, any_(array([self._data_str()]))), - f"data_table.range {op} ANY (ARRAY[%(param_1)s])", + f"data_table.range {op} ANY (ARRAY[%(param_1)s::VARCHAR])", ( self.col.type if op in self._not_compare_op @@ -5048,8 +5081,7 @@ class _RangeComparisonFixtures(_RangeTests): eq_( py_contains, pg_contains, - f"{r1}.contains({r2}): got {py_contains}," - f" expected {pg_contains}", + f"{r1}.contains({r2}): got {py_contains}, expected {pg_contains}", ) r2_in_r1 = r2 in r1 eq_( @@ -5815,7 +5847,7 @@ class _MultiRangeTypeCompilation(AssertsCompiledSQL, fixtures.TestBase): def test_where_equal(self): self._test_clause( self.col == self._data_str(), - "data_table.multirange = %(multirange_1)s", + "data_table.multirange = %(multirange_1)s::VARCHAR", sqltypes.BOOLEANTYPE, ) @@ -5829,7 +5861,7 @@ class _MultiRangeTypeCompilation(AssertsCompiledSQL, fixtures.TestBase): def test_where_not_equal(self): self._test_clause( self.col != self._data_str(), - "data_table.multirange != %(multirange_1)s", + "data_table.multirange != %(multirange_1)s::VARCHAR", sqltypes.BOOLEANTYPE, ) @@ -5857,42 +5889,42 @@ class _MultiRangeTypeCompilation(AssertsCompiledSQL, fixtures.TestBase): def test_where_less_than(self): self._test_clause( self.col < self._data_str(), - "data_table.multirange < %(multirange_1)s", + "data_table.multirange < %(multirange_1)s::VARCHAR", sqltypes.BOOLEANTYPE, ) def test_where_greater_than(self): self._test_clause( self.col > self._data_str(), - "data_table.multirange > %(multirange_1)s", + "data_table.multirange > %(multirange_1)s::VARCHAR", sqltypes.BOOLEANTYPE, ) def test_where_less_than_or_equal(self): self._test_clause( self.col <= self._data_str(), - "data_table.multirange <= %(multirange_1)s", + "data_table.multirange <= %(multirange_1)s::VARCHAR", sqltypes.BOOLEANTYPE, ) def test_where_greater_than_or_equal(self): self._test_clause( self.col >= self._data_str(), - "data_table.multirange >= %(multirange_1)s", + "data_table.multirange >= %(multirange_1)s::VARCHAR", sqltypes.BOOLEANTYPE, ) def test_contains(self): self._test_clause( self.col.contains(self._data_str()), - "data_table.multirange @> %(multirange_1)s", + "data_table.multirange @> %(multirange_1)s::VARCHAR", sqltypes.BOOLEANTYPE, ) def test_contained_by(self): self._test_clause( self.col.contained_by(self._data_str()), - "data_table.multirange <@ %(multirange_1)s", + "data_table.multirange <@ %(multirange_1)s::VARCHAR", sqltypes.BOOLEANTYPE, ) @@ -5906,52 +5938,52 @@ class _MultiRangeTypeCompilation(AssertsCompiledSQL, fixtures.TestBase): def test_overlaps(self): self._test_clause( self.col.overlaps(self._data_str()), - "data_table.multirange && %(multirange_1)s", + "data_table.multirange && %(multirange_1)s::VARCHAR", sqltypes.BOOLEANTYPE, ) def test_strictly_left_of(self): self._test_clause( self.col << self._data_str(), - "data_table.multirange << %(multirange_1)s", + "data_table.multirange << %(multirange_1)s::VARCHAR", sqltypes.BOOLEANTYPE, ) self._test_clause( self.col.strictly_left_of(self._data_str()), - "data_table.multirange << %(multirange_1)s", + "data_table.multirange << %(multirange_1)s::VARCHAR", sqltypes.BOOLEANTYPE, ) def test_strictly_right_of(self): self._test_clause( self.col >> self._data_str(), - "data_table.multirange >> %(multirange_1)s", + "data_table.multirange >> %(multirange_1)s::VARCHAR", sqltypes.BOOLEANTYPE, ) self._test_clause( self.col.strictly_right_of(self._data_str()), - "data_table.multirange >> %(multirange_1)s", + "data_table.multirange >> %(multirange_1)s::VARCHAR", sqltypes.BOOLEANTYPE, ) def test_not_extend_right_of(self): self._test_clause( self.col.not_extend_right_of(self._data_str()), - "data_table.multirange &< %(multirange_1)s", + "data_table.multirange &< %(multirange_1)s::VARCHAR", sqltypes.BOOLEANTYPE, ) def test_not_extend_left_of(self): self._test_clause( self.col.not_extend_left_of(self._data_str()), - "data_table.multirange &> %(multirange_1)s", + "data_table.multirange &> %(multirange_1)s::VARCHAR", sqltypes.BOOLEANTYPE, ) def test_adjacent_to(self): self._test_clause( self.col.adjacent_to(self._data_str()), - "data_table.multirange -|- %(multirange_1)s", + "data_table.multirange -|- %(multirange_1)s::VARCHAR", sqltypes.BOOLEANTYPE, ) @@ -6372,12 +6404,14 @@ class JSONTest(AssertsCompiledSQL, fixtures.TestBase): ), ( lambda self: self.jsoncol["bar"].astext == None, # noqa - "(test_table.test_column ->> %(test_column_1)s) IS NULL", + "(test_table.test_column ->> %(test_column_1)s::TEXT) IS NULL", ), ( lambda self: self.jsoncol["bar"].astext.cast(Integer) == 5, - "CAST((test_table.test_column ->> %(test_column_1)s) AS INTEGER) " - "= %(param_1)s", + ( + "CAST((test_table.test_column ->> %(test_column_1)s::TEXT) AS" + " INTEGER) = %(param_1)s::INTEGER" + ), ), ( lambda self: self.jsoncol[("foo", 1)].astext == None, # noqa @@ -6385,23 +6419,31 @@ class JSONTest(AssertsCompiledSQL, fixtures.TestBase): ), ( lambda self: self.jsoncol["bar"].astext == self.any_, - "(test_table.test_column ->> %(test_column_1)s) = " - "ANY (ARRAY[%(param_1)s])", + ( + "(test_table.test_column ->> %(test_column_1)s::TEXT) = " + "ANY (ARRAY[%(param_1)s::INTEGER])" + ), ), ( lambda self: self.jsoncol["bar"].astext != self.any_, - "(test_table.test_column ->> %(test_column_1)s) != " - "ANY (ARRAY[%(param_1)s])", + ( + "(test_table.test_column ->> %(test_column_1)s::TEXT) != " + "ANY (ARRAY[%(param_1)s::INTEGER])" + ), ), ( lambda self: self.jsoncol[("foo", 1)] == self.any_, - "(test_table.test_column #> %(test_column_1)s) = " - "ANY (ARRAY[%(param_1)s])", + ( + "(test_table.test_column #> %(test_column_1)s) = " + "ANY (ARRAY[%(param_1)s::INTEGER])" + ), ), ( lambda self: self.jsoncol[("foo", 1)] != self.any_, - "(test_table.test_column #> %(test_column_1)s) != " - "ANY (ARRAY[%(param_1)s])", + ( + "(test_table.test_column #> %(test_column_1)s) != " + "ANY (ARRAY[%(param_1)s::INTEGER])" + ), ), id_="as", ) @@ -6419,34 +6461,46 @@ class JSONTest(AssertsCompiledSQL, fixtures.TestBase): @testing.combinations( ( lambda self: self.jsoncol["bar"] == None, # noqa - "(test_table.test_column -> %(test_column_1)s) IS NULL", + "(test_table.test_column -> %(test_column_1)s::TEXT) IS NULL", ), ( lambda self: self.jsoncol["bar"] != None, # noqa - "(test_table.test_column -> %(test_column_1)s) IS NOT NULL", + "(test_table.test_column -> %(test_column_1)s::TEXT) IS NOT NULL", ), ( lambda self: self.jsoncol["bar"].cast(Integer) == 5, - "CAST((test_table.test_column -> %(test_column_1)s) AS INTEGER) " - "= %(param_1)s", + ( + "CAST((test_table.test_column -> %(test_column_1)s::TEXT) AS" + " INTEGER) = %(param_1)s::INTEGER" + ), ), ( lambda self: self.jsoncol["bar"] == 42, - "(test_table.test_column -> %(test_column_1)s) = %(param_1)s", + ( + "(test_table.test_column -> %(test_column_1)s::TEXT) =" + " %(param_1)s::INTEGER" + ), ), ( lambda self: self.jsoncol["bar"] != 42, - "(test_table.test_column -> %(test_column_1)s) != %(param_1)s", + ( + "(test_table.test_column -> %(test_column_1)s::TEXT) !=" + " %(param_1)s::INTEGER" + ), ), ( lambda self: self.jsoncol["bar"] == self.any_, - "(test_table.test_column -> %(test_column_1)s) = " - "ANY (ARRAY[%(param_1)s])", + ( + "(test_table.test_column -> %(test_column_1)s::TEXT) = " + "ANY (ARRAY[%(param_1)s::INTEGER])" + ), ), ( lambda self: self.jsoncol["bar"] != self.any_, - "(test_table.test_column -> %(test_column_1)s) != " - "ANY (ARRAY[%(param_1)s])", + ( + "(test_table.test_column -> %(test_column_1)s::TEXT) != " + "ANY (ARRAY[%(param_1)s::INTEGER])" + ), ), id_="as", ) @@ -6483,7 +6537,7 @@ class JSONTest(AssertsCompiledSQL, fixtures.TestBase): @testing.combinations( ( lambda self: self.jsoncol["foo"], - "test_table.test_column -> %(test_column_1)s AS anon_1", + "test_table.test_column -> %(test_column_1)s::TEXT AS anon_1", True, ) ) @@ -6776,11 +6830,11 @@ class JSONBTest(JSONTest): @testing.combinations( ( lambda self: self.jsoncol.has_key("data"), - "test_table.test_column ? %(test_column_1)s", + "test_table.test_column ? %(test_column_1)s::VARCHAR", ), ( lambda self: self.jsoncol.has_key(self.any_), - "test_table.test_column ? ANY (ARRAY[%(param_1)s])", + "test_table.test_column ? ANY (ARRAY[%(param_1)s::INTEGER])", ), ( lambda self: self.jsoncol.has_all( @@ -6790,17 +6844,20 @@ class JSONBTest(JSONTest): ), ( lambda self: self.jsoncol.has_all(self.any_), - "test_table.test_column ?& ANY (ARRAY[%(param_1)s])", + "test_table.test_column ?& ANY (ARRAY[%(param_1)s::INTEGER])", ), ( lambda self: self.jsoncol.has_any( postgresql.array(["name", "data"]) ), - "test_table.test_column ?| ARRAY[%(param_1)s, %(param_2)s]", + ( + "test_table.test_column ?| ARRAY[%(param_1)s::VARCHAR," + " %(param_2)s::VARCHAR]" + ), ), ( lambda self: self.jsoncol.has_any(self.any_), - "test_table.test_column ?| ANY (ARRAY[%(param_1)s])", + "test_table.test_column ?| ANY (ARRAY[%(param_1)s::INTEGER])", ), ( lambda self: self.jsoncol.contains({"k1": "r1v1"}), @@ -6808,7 +6865,7 @@ class JSONBTest(JSONTest): ), ( lambda self: self.jsoncol.contains(self.any_), - "test_table.test_column @> ANY (ARRAY[%(param_1)s])", + "test_table.test_column @> ANY (ARRAY[%(param_1)s::INTEGER])", ), ( lambda self: self.jsoncol.contained_by({"foo": "1", "bar": None}), @@ -6816,33 +6873,37 @@ class JSONBTest(JSONTest): ), ( lambda self: self.jsoncol.contained_by(self.any_), - "test_table.test_column <@ ANY (ARRAY[%(param_1)s])", + "test_table.test_column <@ ANY (ARRAY[%(param_1)s::INTEGER])", ), ( lambda self: self.jsoncol.delete_path(["a", "b"]), - "test_table.test_column #- CAST(ARRAY[%(param_1)s, " - "%(param_2)s] AS TEXT[])", + ( + "test_table.test_column #- CAST(ARRAY[%(param_1)s::VARCHAR, " + "%(param_2)s::VARCHAR] AS TEXT[])" + ), ), ( lambda self: self.jsoncol.delete_path(array(["a", "b"])), - "test_table.test_column #- CAST(ARRAY[%(param_1)s, " - "%(param_2)s] AS TEXT[])", + ( + "test_table.test_column #- CAST(ARRAY[%(param_1)s::VARCHAR, " + "%(param_2)s::VARCHAR] AS TEXT[])" + ), ), ( lambda self: self.jsoncol.path_exists("$.k1"), - "test_table.test_column @? %(test_column_1)s", + "test_table.test_column @? %(test_column_1)s::VARCHAR", ), ( lambda self: self.jsoncol.path_exists(self.any_), - "test_table.test_column @? ANY (ARRAY[%(param_1)s])", + "test_table.test_column @? ANY (ARRAY[%(param_1)s::INTEGER])", ), ( lambda self: self.jsoncol.path_match("$.k1[0] > 2"), - "test_table.test_column @@ %(test_column_1)s", + "test_table.test_column @@ %(test_column_1)s::VARCHAR", ), ( lambda self: self.jsoncol.path_match(self.any_), - "test_table.test_column @@ ANY (ARRAY[%(param_1)s])", + "test_table.test_column @@ ANY (ARRAY[%(param_1)s::INTEGER])", ), id_="as", ) @@ -6854,34 +6915,46 @@ class JSONBTest(JSONTest): @testing.combinations( ( lambda self: self.jsoncol["bar"] == None, # noqa - "test_table.test_column[%(test_column_1)s] IS NULL", + "test_table.test_column[%(test_column_1)s::TEXT] IS NULL", ), ( lambda self: self.jsoncol["bar"] != None, # noqa - "test_table.test_column[%(test_column_1)s] IS NOT NULL", + "test_table.test_column[%(test_column_1)s::TEXT] IS NOT NULL", ), ( lambda self: self.jsoncol["bar"].cast(Integer) == 5, - "CAST(test_table.test_column[%(test_column_1)s] AS INTEGER) " - "= %(param_1)s", + ( + "CAST(test_table.test_column[%(test_column_1)s::TEXT] AS" + " INTEGER) = %(param_1)s::INTEGER" + ), ), ( lambda self: self.jsoncol["bar"] == 42, - "test_table.test_column[%(test_column_1)s] = %(param_1)s", + ( + "test_table.test_column[%(test_column_1)s::TEXT] =" + " %(param_1)s::INTEGER" + ), ), ( lambda self: self.jsoncol["bar"] != 42, - "test_table.test_column[%(test_column_1)s] != %(param_1)s", + ( + "test_table.test_column[%(test_column_1)s::TEXT] !=" + " %(param_1)s::INTEGER" + ), ), ( lambda self: self.jsoncol["bar"] == self.any_, - "test_table.test_column[%(test_column_1)s] = " - "ANY (ARRAY[%(param_1)s])", + ( + "test_table.test_column[%(test_column_1)s::TEXT] = " + "ANY (ARRAY[%(param_1)s::INTEGER])" + ), ), ( lambda self: self.jsoncol["bar"] != self.any_, - "test_table.test_column[%(test_column_1)s] != " - "ANY (ARRAY[%(param_1)s])", + ( + "test_table.test_column[%(test_column_1)s::TEXT] != " + "ANY (ARRAY[%(param_1)s::INTEGER])" + ), ), id_="as", ) @@ -6899,7 +6972,7 @@ class JSONBTest(JSONTest): @testing.combinations( ( lambda self: self.jsoncol["foo"], - "test_table.test_column[%(test_column_1)s] AS anon_1", + "test_table.test_column[%(test_column_1)s::TEXT] AS anon_1", True, ) ) diff --git a/test/engine/test_deprecations.py b/test/engine/test_deprecations.py index a09669a713..6185f8c534 100644 --- a/test/engine/test_deprecations.py +++ b/test/engine/test_deprecations.py @@ -312,7 +312,7 @@ class ResetEventTest(fixtures.TestBase): dbapi = Mock() return dbapi, create_engine( - "postgresql://", + "mysql://", module=dbapi, creator=lambda: dbapi.connect("foo.db"), _initialize=False, diff --git a/test/engine/test_parseconnect.py b/test/engine/test_parseconnect.py index 00cdfc9bf5..5118687485 100644 --- a/test/engine/test_parseconnect.py +++ b/test/engine/test_parseconnect.py @@ -882,7 +882,7 @@ class CreateEngineTest(fixtures.TestBase): sp = SecurePassword("secured_password") u = url.URL.create( - "postgresql", username="x", password=sp, host="localhost" + "mysql+pymysql", username="x", password=sp, host="localhost" ) if not creator: dbapi = MockDBAPI( @@ -912,8 +912,8 @@ class CreateEngineTest(fixtures.TestBase): ("mariadb://", "mysqldb"), ("mssql://", "pyodbc"), ("mysql://", "mysqldb"), - ("oracle://", "cx_oracle"), - ("postgresql://", "psycopg2"), + ("oracle://", "oracledb"), + ("postgresql://", "psycopg"), ("sqlite://", "pysqlite"), ]: try: diff --git a/test/engine/test_pool.py b/test/engine/test_pool.py index e3437ed3b9..6759390f50 100644 --- a/test/engine/test_pool.py +++ b/test/engine/test_pool.py @@ -2038,7 +2038,7 @@ class ResetOnReturnTest(PoolTestBase): dbapi = Mock() return dbapi, create_engine( - "postgresql://", + "mysql://", module=dbapi, creator=lambda: dbapi.connect("foo.db"), _initialize=False, diff --git a/test/orm/dml/test_orm_upd_del_basic.py b/test/orm/dml/test_orm_upd_del_basic.py index 09b499eda0..34c0465cf7 100644 --- a/test/orm/dml/test_orm_upd_del_basic.py +++ b/test/orm/dml/test_orm_upd_del_basic.py @@ -672,9 +672,9 @@ class UpdateDeleteTest(fixtures.MappedTest): if testing.db.dialect.update_returning: asserter.assert_( CompiledSQL( - "UPDATE users SET age_int=(users.age_int + %(age_int_1)s) " - "WHERE users.name IS NOT NULL " - "RETURNING users.id", + "UPDATE users SET age_int=(users.age_int +" + " %(age_int_1)s::INTEGER) WHERE users.name IS NOT NULL" + " RETURNING users.id", [{"age_int_1": 10}], dialect="postgresql", ), @@ -682,8 +682,7 @@ class UpdateDeleteTest(fixtures.MappedTest): else: asserter.assert_( CompiledSQL( - "SELECT users.id FROM users " - "WHERE users.name IS NOT NULL" + "SELECT users.id FROM users WHERE users.name IS NOT NULL" ), CompiledSQL( "UPDATE users SET age_int=(users.age_int + :age_int_1) " @@ -1230,8 +1229,9 @@ class UpdateDeleteTest(fixtures.MappedTest): if implicit_returning and testing.db.dialect.update_returning: asserter.assert_( CompiledSQL( - "UPDATE users SET age_int=(users.age_int - %(age_int_1)s) " - "WHERE users.age_int > %(age_int_2)s RETURNING users.id", + "UPDATE users SET age_int=(users.age_int -" + " %(age_int_1)s::INTEGER) WHERE users.age_int >" + " %(age_int_2)s::INTEGER RETURNING users.id", [{"age_int_1": 10, "age_int_2": 29}], dialect="postgresql", ), @@ -1276,8 +1276,9 @@ class UpdateDeleteTest(fixtures.MappedTest): if testing.db.dialect.update_returning: asserter.assert_( CompiledSQL( - "UPDATE users SET age_int=(users.age_int - %(age_int_1)s) " - "WHERE users.age_int > %(age_int_2)s RETURNING users.id", + "UPDATE users SET age_int=(users.age_int -" + " %(age_int_1)s::INTEGER) WHERE users.age_int >" + " %(age_int_2)s::INTEGER RETURNING users.id", [{"age_int_1": 10, "age_int_2": 29}], dialect="postgresql", ), @@ -1322,8 +1323,9 @@ class UpdateDeleteTest(fixtures.MappedTest): asserter.assert_( CompiledSQL( - "UPDATE users SET age_int=(users.age_int - %(age_int_1)s) " - "WHERE users.age_int > %(age_int_2)s RETURNING users.id", + "UPDATE users SET age_int=(users.age_int -" + " %(age_int_1)s::INTEGER) WHERE users.age_int >" + " %(age_int_2)s::INTEGER RETURNING users.id", [{"age_int_1": 10, "age_int_2": 29}], dialect="postgresql", ), @@ -1364,10 +1366,11 @@ class UpdateDeleteTest(fixtures.MappedTest): asserter.assert_( CompiledSQL( - "UPDATE users SET age_int=(users.age_int - %(age_int_1)s) " + "UPDATE users SET " + "age_int=(users.age_int - %(age_int_1)s::INTEGER) " "FROM addresses " "WHERE users.id = addresses.user_id AND " - "users.age_int > %(age_int_2)s " + "users.age_int > %(age_int_2)s::INTEGER " "RETURNING users.id, addresses.email_address, " "char_length(users.name) AS char_length_1", [{"age_int_1": 10, "age_int_2": 29}], @@ -1497,8 +1500,8 @@ class UpdateDeleteTest(fixtures.MappedTest): if implicit_returning and testing.db.dialect.delete_returning: asserter.assert_( CompiledSQL( - "DELETE FROM users WHERE users.age_int > %(age_int_1)s " - "RETURNING users.id", + "DELETE FROM users WHERE users.age_int >" + " %(age_int_1)s::INTEGER RETURNING users.id", [{"age_int_1": 29}], dialect="postgresql", ), @@ -1542,8 +1545,8 @@ class UpdateDeleteTest(fixtures.MappedTest): if testing.db.dialect.delete_returning: asserter.assert_( CompiledSQL( - "DELETE FROM users WHERE users.age_int > %(age_int_1)s " - "RETURNING users.id", + "DELETE FROM users WHERE users.age_int >" + " %(age_int_1)s::INTEGER RETURNING users.id", [{"age_int_1": 29}], dialect="postgresql", ), @@ -2009,10 +2012,8 @@ class UpdateDeleteTest(fixtures.MappedTest): cols = [ c.key for c in ( - ( - bulk_ud.result.context - ).compiled.compile_state.statement._values - ) + bulk_ud.result.context + ).compiled.compile_state.statement._values ] m1(cols) @@ -2066,9 +2067,7 @@ class UpdateDeleteTest(fixtures.MappedTest): result = session.execute(stmt) cols = [ c.key - for c in ( - (result.context).compiled.compile_state.statement._values - ) + for c in (result.context).compiled.compile_state.statement._values ] eq_(["age_int", "name"], cols) diff --git a/test/orm/dml/test_orm_upd_del_inheritance.py b/test/orm/dml/test_orm_upd_del_inheritance.py index ffce962be5..3c8c95d4f8 100644 --- a/test/orm/dml/test_orm_upd_del_inheritance.py +++ b/test/orm/dml/test_orm_upd_del_inheritance.py @@ -201,9 +201,10 @@ class InheritTest(fixtures.DeclarativeMappedTest): elif synchronize_session in ("fetch", "fetch_w_hint"): asserter.assert_( CompiledSQL( - "UPDATE engineer SET engineer_name=%(engineer_name)s " - "FROM person WHERE engineer.id = person.id " - "AND person.name = %(name_1)s RETURNING engineer.id", + "UPDATE engineer SET" + " engineer_name=%(engineer_name)s::VARCHAR FROM person" + " WHERE engineer.id = person.id AND person.name =" + " %(name_1)s::VARCHAR RETURNING engineer.id", [{"engineer_name": "e5", "name_1": "e2"}], dialect="postgresql", ), @@ -211,9 +212,10 @@ class InheritTest(fixtures.DeclarativeMappedTest): else: asserter.assert_( CompiledSQL( - "UPDATE engineer SET engineer_name=%(engineer_name)s " - "FROM person WHERE engineer.id = person.id " - "AND person.name = %(name_1)s", + "UPDATE engineer SET" + " engineer_name=%(engineer_name)s::VARCHAR FROM person" + " WHERE engineer.id = person.id AND person.name =" + " %(name_1)s::VARCHAR", [{"engineer_name": "e5", "name_1": "e2"}], dialect="postgresql", ), @@ -299,7 +301,8 @@ class InheritTest(fixtures.DeclarativeMappedTest): asserter.assert_( CompiledSQL( "DELETE FROM engineer USING person WHERE " - "engineer.id = person.id AND person.name = %(name_1)s " + "engineer.id = person.id " + "AND person.name = %(name_1)s::VARCHAR " "RETURNING engineer.id", [{"name_1": "e2"}], dialect="postgresql", @@ -309,7 +312,8 @@ class InheritTest(fixtures.DeclarativeMappedTest): asserter.assert_( CompiledSQL( "DELETE FROM engineer USING person WHERE " - "engineer.id = person.id AND person.name = %(name_1)s", + "engineer.id = person.id " + "AND person.name = %(name_1)s::VARCHAR", [{"name_1": "e2"}], dialect="postgresql", ), diff --git a/test/orm/test_defaults.py b/test/orm/test_defaults.py index c720e09152..1513f3281e 100644 --- a/test/orm/test_defaults.py +++ b/test/orm/test_defaults.py @@ -366,15 +366,15 @@ class ComputedDefaultsOnUpdateTest(fixtures.MappedTest): if eager and testing.db.dialect.update_returning: asserter.assert_( CompiledSQL( - "UPDATE test SET foo=%(foo)s " - "WHERE test.id = %(test_id)s " + "UPDATE test SET foo=%(foo)s::INTEGER " + "WHERE test.id = %(test_id)s::INTEGER " "RETURNING test.bar", [{"foo": 5, "test_id": 1}], dialect="postgresql", ), CompiledSQL( - "UPDATE test SET foo=%(foo)s " - "WHERE test.id = %(test_id)s " + "UPDATE test SET foo=%(foo)s::INTEGER " + "WHERE test.id = %(test_id)s::INTEGER " "RETURNING test.bar", [{"foo": 6, "test_id": 2}], dialect="postgresql", diff --git a/test/orm/test_query.py b/test/orm/test_query.py index e39a70c47c..6edfd15580 100644 --- a/test/orm/test_query.py +++ b/test/orm/test_query.py @@ -5243,7 +5243,7 @@ class DistinctOnTest( "addresses.email_address AS addresses_email_address " "FROM users JOIN addresses ON users.id = addresses.user_id " "ORDER BY addresses.email_address DESC " - "LIMIT %(param_1)s) AS anon_1 " + "LIMIT %(param_1)s::INTEGER) AS anon_1 " "LEFT OUTER JOIN addresses AS addresses_1 " "ON anon_1.users_id = addresses_1.user_id " "ORDER BY anon_1.addresses_email_address DESC, addresses_1.id", diff --git a/test/orm/test_relationship_criteria.py b/test/orm/test_relationship_criteria.py index 90c3243eac..e22a4c8ca2 100644 --- a/test/orm/test_relationship_criteria.py +++ b/test/orm/test_relationship_criteria.py @@ -771,14 +771,13 @@ class LoaderCriteriaTest(_Fixtures, testing.AssertsCompiledSQL): if update_is_orm: self.assert_compile( stmt, - "WITH pd AS (SELECT orders.id AS id, " - "orders.user_id AS user_id, " - "orders.address_id AS address_id, " - "orders.description AS description, orders.isopen AS isopen " - "FROM orders WHERE orders.description != %(description_1)s) " - "UPDATE orders SET description=%(description)s " - "FROM pd WHERE orders.id = pd.id " - "AND orders.description != %(description_2)s", + "WITH pd AS (SELECT orders.id AS id, orders.user_id AS" + " user_id, orders.address_id AS address_id, orders.description" + " AS description, orders.isopen AS isopen FROM orders WHERE" + " orders.description != %(description_1)s::VARCHAR) UPDATE" + " orders SET description=%(description)s::VARCHAR FROM pd" + " WHERE orders.id = pd.id AND orders.description !=" + " %(description_2)s::VARCHAR", dialect="postgresql", checkparams={ "description": "newname", @@ -791,13 +790,12 @@ class LoaderCriteriaTest(_Fixtures, testing.AssertsCompiledSQL): # inside the SELECT self.assert_compile( stmt, - "WITH pd AS (SELECT orders.id AS id, " - "orders.user_id AS user_id, " - "orders.address_id AS address_id, " - "orders.description AS description, orders.isopen AS isopen " - "FROM orders WHERE orders.description != %(description_1)s) " - "UPDATE orders SET description=%(description)s " - "FROM pd WHERE orders.id = pd.id", + "WITH pd AS (SELECT orders.id AS id, orders.user_id AS" + " user_id, orders.address_id AS address_id, orders.description" + " AS description, orders.isopen AS isopen FROM orders WHERE" + " orders.description != %(description_1)s::VARCHAR) UPDATE" + " orders SET description=%(description)s::VARCHAR FROM pd" + " WHERE orders.id = pd.id", dialect="postgresql", checkparams={ "description": "newname", @@ -832,12 +830,12 @@ class LoaderCriteriaTest(_Fixtures, testing.AssertsCompiledSQL): if delete_is_orm: self.assert_compile( stmt, - "WITH pd AS (SELECT orders.id AS id, orders.user_id AS " - "user_id, orders.address_id AS address_id, " - "orders.description AS description, orders.isopen AS isopen " - "FROM orders WHERE orders.description != %(description_1)s) " - "DELETE FROM orders USING pd WHERE orders.id = pd.id " - "AND orders.description != %(description_2)s", + "WITH pd AS (SELECT orders.id AS id, orders.user_id AS" + " user_id, orders.address_id AS address_id, orders.description" + " AS description, orders.isopen AS isopen FROM orders WHERE" + " orders.description != %(description_1)s::VARCHAR) DELETE" + " FROM orders USING pd WHERE orders.id = pd.id AND" + " orders.description != %(description_2)s::VARCHAR", dialect="postgresql", checkparams={"description_1": "name", "description_2": "name"}, ) @@ -846,11 +844,11 @@ class LoaderCriteriaTest(_Fixtures, testing.AssertsCompiledSQL): # inside the SELECT self.assert_compile( stmt, - "WITH pd AS (SELECT orders.id AS id, orders.user_id AS " - "user_id, orders.address_id AS address_id, " - "orders.description AS description, orders.isopen AS isopen " - "FROM orders WHERE orders.description != %(description_1)s) " - "DELETE FROM orders USING pd WHERE orders.id = pd.id", + "WITH pd AS (SELECT orders.id AS id, orders.user_id AS" + " user_id, orders.address_id AS address_id, orders.description" + " AS description, orders.isopen AS isopen FROM orders WHERE" + " orders.description != %(description_1)s::VARCHAR) DELETE" + " FROM orders USING pd WHERE orders.id = pd.id", dialect="postgresql", checkparams={"description_1": "name"}, ) diff --git a/test/orm/test_unitofworkv2.py b/test/orm/test_unitofworkv2.py index 9f6610f08c..6cc081f9fd 100644 --- a/test/orm/test_unitofworkv2.py +++ b/test/orm/test_unitofworkv2.py @@ -2112,7 +2112,7 @@ class BatchInsertsTest(fixtures.MappedTest, testing.AssertsExecutionResults): testing.db.dialect.insert_executemany_returning, [ CompiledSQL( - f"INSERT INTO t (data) VALUES (:data) " + "INSERT INTO t (data) VALUES (:data) " f"RETURNING t.id{tdef_col}", [{"data": "t1"}, {"data": "t2"}], ), @@ -2127,7 +2127,7 @@ class BatchInsertsTest(fixtures.MappedTest, testing.AssertsExecutionResults): ], ), CompiledSQL( - f"INSERT INTO t (id, data) " + "INSERT INTO t (id, data) " f"VALUES (:id, :data){tdef_returning}", [ {"data": "t3", "id": 3}, @@ -2136,12 +2136,12 @@ class BatchInsertsTest(fixtures.MappedTest, testing.AssertsExecutionResults): ], ), CompiledSQL( - f"INSERT INTO t (id, data) " + "INSERT INTO t (id, data) " f"VALUES (:id, lower(:lower_1)){tdef_returning}", {"lower_1": "t6", "id": 6}, ), CompiledSQL( - f"INSERT INTO t (id, data) " + "INSERT INTO t (id, data) " f"VALUES (:id, :data){tdef_returning}", [{"data": "t7", "id": 7}, {"data": "t8", "id": 8}], ), @@ -2153,7 +2153,7 @@ class BatchInsertsTest(fixtures.MappedTest, testing.AssertsExecutionResults): ], ), CompiledSQL( - f"INSERT INTO t (id, data) " + "INSERT INTO t (id, data) " f"VALUES (:id, :data){tdef_returning}", {"data": "t11", "id": 11}, ), @@ -2467,14 +2467,14 @@ class EagerDefaultsTest(fixtures.MappedTest): s.flush, CompiledSQL( "INSERT INTO test (id, foo) " - "VALUES (%(id)s, 2 + 5) " + "VALUES (%(id)s::INTEGER, 2 + 5) " "RETURNING test.foo", [{"id": 1}], dialect="postgresql", ), CompiledSQL( "INSERT INTO test (id, foo) " - "VALUES (%(id)s, 5 + 5) " + "VALUES (%(id)s::INTEGER, 5 + 5) " "RETURNING test.foo", [{"id": 2}], dialect="postgresql", @@ -2626,13 +2626,11 @@ class EagerDefaultsTest(fixtures.MappedTest): ], ), CompiledSQL( - "SELECT test3.foo " - "FROM test3 WHERE test3.id = :pk_1", + "SELECT test3.foo FROM test3 WHERE test3.id = :pk_1", [{"pk_1": 1}], ), CompiledSQL( - "SELECT test3.foo " - "FROM test3 WHERE test3.id = :pk_1", + "SELECT test3.foo FROM test3 WHERE test3.id = :pk_1", [{"pk_1": 2}], ), ], @@ -2667,28 +2665,30 @@ class EagerDefaultsTest(fixtures.MappedTest): testing.db.dialect.update_returning, [ CompiledSQL( - "UPDATE test2 SET foo=%(foo)s " - "WHERE test2.id = %(test2_id)s " + "UPDATE test2 SET foo=%(foo)s::INTEGER " + "WHERE test2.id = %(test2_id)s::INTEGER " "RETURNING test2.bar", [{"foo": 5, "test2_id": 1}], dialect="postgresql", ), CompiledSQL( - "UPDATE test2 SET foo=%(foo)s, bar=%(bar)s " - "WHERE test2.id = %(test2_id)s", + "UPDATE test2 SET foo=%(foo)s::INTEGER," + " bar=%(bar)s::INTEGER WHERE test2.id =" + " %(test2_id)s::INTEGER", [{"foo": 6, "bar": 10, "test2_id": 2}], dialect="postgresql", ), CompiledSQL( - "UPDATE test2 SET foo=%(foo)s " - "WHERE test2.id = %(test2_id)s " + "UPDATE test2 SET foo=%(foo)s::INTEGER " + "WHERE test2.id = %(test2_id)s::INTEGER " "RETURNING test2.bar", [{"foo": 7, "test2_id": 3}], dialect="postgresql", ), CompiledSQL( - "UPDATE test2 SET foo=%(foo)s, bar=%(bar)s " - "WHERE test2.id = %(test2_id)s", + "UPDATE test2 SET foo=%(foo)s::INTEGER," + " bar=%(bar)s::INTEGER WHERE test2.id =" + " %(test2_id)s::INTEGER", [{"foo": 8, "bar": 12, "test2_id": 4}], dialect="postgresql", ), @@ -2717,13 +2717,11 @@ class EagerDefaultsTest(fixtures.MappedTest): enable_returning=False, ), CompiledSQL( - "SELECT test2.bar FROM test2 " - "WHERE test2.id = :pk_1", + "SELECT test2.bar FROM test2 WHERE test2.id = :pk_1", [{"pk_1": 1}], ), CompiledSQL( - "SELECT test2.bar FROM test2 " - "WHERE test2.id = :pk_1", + "SELECT test2.bar FROM test2 WHERE test2.id = :pk_1", [{"pk_1": 3}], ), ], @@ -2766,26 +2764,30 @@ class EagerDefaultsTest(fixtures.MappedTest): testing.db.dialect.update_returning, [ CompiledSQL( - "UPDATE test4 SET foo=%(foo)s, bar=5 + 3 " - "WHERE test4.id = %(test4_id)s RETURNING test4.bar", + "UPDATE test4 SET foo=%(foo)s::INTEGER, bar=5 + 3" + " WHERE test4.id = %(test4_id)s::INTEGER RETURNING" + " test4.bar", [{"foo": 5, "test4_id": 1}], dialect="postgresql", ), CompiledSQL( - "UPDATE test4 SET foo=%(foo)s, bar=%(bar)s " - "WHERE test4.id = %(test4_id)s", + "UPDATE test4 SET foo=%(foo)s::INTEGER," + " bar=%(bar)s::INTEGER WHERE test4.id =" + " %(test4_id)s::INTEGER", [{"foo": 6, "bar": 10, "test4_id": 2}], dialect="postgresql", ), CompiledSQL( - "UPDATE test4 SET foo=%(foo)s, bar=5 + 3 WHERE " - "test4.id = %(test4_id)s RETURNING test4.bar", + "UPDATE test4 SET foo=%(foo)s::INTEGER, bar=5 + 3" + " WHERE test4.id = %(test4_id)s::INTEGER RETURNING" + " test4.bar", [{"foo": 7, "test4_id": 3}], dialect="postgresql", ), CompiledSQL( - "UPDATE test4 SET foo=%(foo)s, bar=%(bar)s WHERE " - "test4.id = %(test4_id)s", + "UPDATE test4 SET foo=%(foo)s::INTEGER," + " bar=%(bar)s::INTEGER WHERE test4.id =" + " %(test4_id)s::INTEGER", [{"foo": 8, "bar": 12, "test4_id": 4}], dialect="postgresql", ), @@ -2816,14 +2818,12 @@ class EagerDefaultsTest(fixtures.MappedTest): enable_returning=False, ), CompiledSQL( - "SELECT test4.bar FROM test4 " - "WHERE test4.id = :pk_1", + "SELECT test4.bar FROM test4 WHERE test4.id = :pk_1", [{"pk_1": 1}], enable_returning=False, ), CompiledSQL( - "SELECT test4.bar FROM test4 " - "WHERE test4.id = :pk_1", + "SELECT test4.bar FROM test4 WHERE test4.id = :pk_1", [{"pk_1": 3}], enable_returning=False, ), @@ -2866,28 +2866,29 @@ class EagerDefaultsTest(fixtures.MappedTest): testing.db, s.flush, CompiledSQL( - "UPDATE test2 SET foo=%(foo)s, bar=1 + 1 " - "WHERE test2.id = %(test2_id)s " + "UPDATE test2 SET foo=%(foo)s::INTEGER, bar=1 + 1 " + "WHERE test2.id = %(test2_id)s::INTEGER " "RETURNING test2.bar", [{"foo": 5, "test2_id": 1}], dialect="postgresql", ), CompiledSQL( - "UPDATE test2 SET foo=%(foo)s, bar=%(bar)s " - "WHERE test2.id = %(test2_id)s", + "UPDATE test2 SET foo=%(foo)s::INTEGER," + " bar=%(bar)s::INTEGER WHERE test2.id =" + " %(test2_id)s::INTEGER", [{"foo": 6, "bar": 10, "test2_id": 2}], dialect="postgresql", ), CompiledSQL( - "UPDATE test2 SET foo=%(foo)s " - "WHERE test2.id = %(test2_id)s " + "UPDATE test2 SET foo=%(foo)s::INTEGER " + "WHERE test2.id = %(test2_id)s::INTEGER " "RETURNING test2.bar", [{"foo": 7, "test2_id": 3}], dialect="postgresql", ), CompiledSQL( - "UPDATE test2 SET foo=%(foo)s, bar=5 + 7 " - "WHERE test2.id = %(test2_id)s RETURNING test2.bar", + "UPDATE test2 SET foo=%(foo)s::INTEGER, bar=5 + 7 WHERE" + " test2.id = %(test2_id)s::INTEGER RETURNING test2.bar", [{"foo": 8, "test2_id": 4}], dialect="postgresql", ), @@ -3010,7 +3011,8 @@ class EagerDefaultsTest(fixtures.MappedTest): testing.db, s.commit, CompiledSQL( - "UPDATE test2 SET bar=%(bar)s WHERE test2.id = %(test2_id)s", + "UPDATE test2 SET bar=%(bar)s::INTEGER WHERE test2.id =" + " %(test2_id)s::INTEGER", [{"bar": 5, "test2_id": 1}, {"bar": 10, "test2_id": 2}], dialect="postgresql", ), diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 377f05b24f..3004e4801d 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -2961,7 +2961,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): check_results( postgresql.dialect(), ["NUMERIC", "NUMERIC(12, 9)", "DATE", "TEXT", "VARCHAR(20)"], - "%(param_1)s", + "%(param_1)s::VARCHAR", ) # then the Oracle engine diff --git a/test/sql/test_cte.py b/test/sql/test_cte.py index 92b83b7fe3..f3f13e9134 100644 --- a/test/sql/test_cte.py +++ b/test/sql/test_cte.py @@ -2933,7 +2933,7 @@ class NestingCTETest(fixtures.TestBase, AssertsCompiledSQL): qry, "WITH delete_cte AS " "(WITH generator AS " - "(SELECT %(param_1)s AS id) " + "(SELECT %(param_1)s::INTEGER AS id) " "DELETE FROM table_1 USING generator " "WHERE table_1.id = generator.id RETURNING table_1.id, " "table_1.price) SELECT delete_cte.id, delete_cte.price " diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py index 6766271d8a..7d0bf5c241 100644 --- a/test/sql/test_functions.py +++ b/test/sql/test_functions.py @@ -87,6 +87,8 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): def test_compile(self): for dialect in all_dialects(): bindtemplate = BIND_TEMPLATES[dialect.paramstyle] + if dialect.driver == "psycopg": + bindtemplate += "::VARCHAR" self.assert_compile( func.current_timestamp(), "CURRENT_TIMESTAMP", dialect=dialect ) @@ -785,8 +787,8 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): num = column("q") self.assert_compile( func.array_agg(num).filter(num % 2 == 0)[1], - "(array_agg(q) FILTER (WHERE q %% %(q_1)s = " - "%(param_1)s))[%(param_2)s]", + "(array_agg(q) FILTER (WHERE q %% %(q_1)s::INTEGER = " + "%(param_1)s::INTEGER))[%(param_2)s::INTEGER]", dialect="postgresql", ) diff --git a/test/sql/test_insert.py b/test/sql/test_insert.py index c4f15657a6..6724d57a10 100644 --- a/test/sql/test_insert.py +++ b/test/sql/test_insert.py @@ -446,9 +446,9 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): ) self.assert_compile( ins, - "INSERT INTO myothertable (otherid, othername) " - "SELECT mytable.myid, mytable.name FROM mytable " - "WHERE mytable.name = %(name_1)s RETURNING myothertable.otherid", + "INSERT INTO myothertable (otherid, othername) SELECT" + " mytable.myid, mytable.name FROM mytable WHERE mytable.name =" + " %(name_1)s::VARCHAR RETURNING myothertable.otherid", checkparams={"name_1": "foo"}, dialect="postgresql", ) @@ -516,9 +516,10 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): self.assert_compile( stmt, - "INSERT INTO t (id, data) VALUES (nextval('id_seq'), " - "%(data_m0)s), (nextval('id_seq'), %(data_m1)s), " - "(nextval('id_seq'), %(data_m2)s)", + "INSERT INTO t (id, data) VALUES (nextval('id_seq')," + " %(data_m0)s::VARCHAR), (nextval('id_seq')," + " %(data_m1)s::VARCHAR), (nextval('id_seq')," + " %(data_m2)s::VARCHAR)", dialect=postgresql.dialect(), ) @@ -543,9 +544,10 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): self.assert_compile( stmt, - "INSERT INTO t (counter, data) VALUES (nextval('counter_seq'), " - "%(data_m0)s), (nextval('counter_seq'), %(data_m1)s), " - "(nextval('counter_seq'), %(data_m2)s)", + "INSERT INTO t (counter, data) VALUES (nextval('counter_seq')," + " %(data_m0)s::VARCHAR), (nextval('counter_seq')," + " %(data_m1)s::VARCHAR), (nextval('counter_seq')," + " %(data_m2)s::VARCHAR)", dialect=postgresql.dialect(), ) @@ -604,10 +606,12 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): if paramstyle.pg: self.assert_compile( stmt, - "INSERT INTO t (x, y, z) VALUES " - "(%(x_m0)s, sum(%(sum_1)s, %(sum_2)s), %(z_m0)s), " - "(sum(%(sum_3)s, %(sum_4)s), %(y_m1)s, %(z_m1)s), " - "(sum(%(sum_5)s, %(sum_6)s), %(y_m2)s, foo(%(foo_1)s))", + "INSERT INTO t (x, y, z) VALUES (%(x_m0)s::INTEGER," + " sum(%(sum_1)s::INTEGER, %(sum_2)s::INTEGER)," + " %(z_m0)s::INTEGER), (sum(%(sum_3)s::INTEGER," + " %(sum_4)s::INTEGER), %(y_m1)s::INTEGER, %(z_m1)s::INTEGER)," + " (sum(%(sum_5)s::INTEGER, %(sum_6)s::INTEGER)," + " %(y_m2)s::INTEGER, foo(%(foo_1)s::INTEGER))", checkparams={ "x_m0": 1, "sum_1": 1, @@ -1088,7 +1092,7 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): ): self.assert_compile( t.insert(), - "INSERT INTO t (x) VALUES (%(x)s)", + "INSERT INTO t (x) VALUES (%(x)s::INTEGER)", params={"x": 5}, dialect=d, ) @@ -1108,7 +1112,7 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): ): self.assert_compile( t.insert(), - "INSERT INTO t (x) VALUES (%(x)s)", + "INSERT INTO t (x) VALUES (%(x)s::INTEGER)", params={"x": 5}, dialect=d, ) @@ -1152,7 +1156,7 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): ): self.assert_compile( t.insert(), - "INSERT INTO t (q) VALUES (%(q)s)", + "INSERT INTO t (q) VALUES (%(q)s::INTEGER)", params={"q": 5}, dialect=d, ) @@ -1172,7 +1176,7 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): ): self.assert_compile( t.insert(), - "INSERT INTO t (q) VALUES (%(q)s)", + "INSERT INTO t (q) VALUES (%(q)s::INTEGER)", params={"q": 5}, dialect=d, ) @@ -1342,9 +1346,9 @@ class InsertImplicitReturningTest( ) self.assert_compile( ins, - "INSERT INTO myothertable (otherid, othername) " - "SELECT mytable.myid, mytable.name FROM mytable " - "WHERE mytable.name = %(name_1)s", + "INSERT INTO myothertable (otherid, othername) SELECT" + " mytable.myid, mytable.name FROM mytable WHERE mytable.name =" + " %(name_1)s::VARCHAR", checkparams={"name_1": "foo"}, ) @@ -1360,9 +1364,9 @@ class InsertImplicitReturningTest( ) self.assert_compile( ins, - "INSERT INTO myothertable (otherid, othername) " - "SELECT mytable.myid, mytable.name FROM mytable " - "WHERE mytable.name = %(name_1)s", + "INSERT INTO myothertable (otherid, othername) SELECT" + " mytable.myid, mytable.name FROM mytable WHERE mytable.name =" + " %(name_1)s::VARCHAR", checkparams={"name_1": "foo"}, ) @@ -1391,10 +1395,14 @@ class InsertImplicitReturningTest( stmt = t.insert().values(x=None, q=5) if insert_null_still_autoincrements: expected = ( - "INSERT INTO t (x, q) VALUES (%(x)s, %(q)s) RETURNING t.x" + "INSERT INTO t (x, q) VALUES (%(x)s::INTEGER," + " %(q)s::INTEGER) RETURNING t.x" ) else: - expected = "INSERT INTO t (x, q) VALUES (%(x)s, %(q)s)" + expected = ( + "INSERT INTO t (x, q) VALUES (%(x)s::INTEGER," + " %(q)s::INTEGER)" + ) params = None elif paramtype == "params": # for params, compiler doesnt have the value available to look @@ -1402,17 +1410,23 @@ class InsertImplicitReturningTest( stmt = t.insert() if insert_null_still_autoincrements: expected = ( - "INSERT INTO t (x, q) VALUES (%(x)s, %(q)s) RETURNING t.x" + "INSERT INTO t (x, q) VALUES (%(x)s::INTEGER," + " %(q)s::INTEGER) RETURNING t.x" ) else: - expected = "INSERT INTO t (x, q) VALUES (%(x)s, %(q)s)" + expected = ( + "INSERT INTO t (x, q) VALUES (%(x)s::INTEGER," + " %(q)s::INTEGER)" + ) params = {"x": None, "q": 5} elif paramtype == "nothing": # no params, we assume full INSERT. this kind of compilation # doesn't actually happen during execution since there are always # parameters or values stmt = t.insert() - expected = "INSERT INTO t (x, q) VALUES (%(x)s, %(q)s)" + expected = ( + "INSERT INTO t (x, q) VALUES (%(x)s::INTEGER, %(q)s::INTEGER)" + ) params = None self.assert_compile(stmt, expected, params=params, dialect=dialect) @@ -1423,9 +1437,8 @@ class InsertImplicitReturningTest( ) self.assert_compile( ins, - "INSERT INTO myothertable (othername) " - "VALUES (%(othername_m0)s), " - "(%(othername_m1)s)", + "INSERT INTO myothertable (othername) VALUES" + " (%(othername_m0)s::VARCHAR), (%(othername_m1)s::VARCHAR)", checkparams={"othername_m1": "bar", "othername_m0": "foo"}, ) @@ -1450,9 +1463,8 @@ class InsertImplicitReturningTest( ) self.assert_compile( ins, - "INSERT INTO myothertable (othername) " - "VALUES (%(othername_m0)s), " - "(%(othername_m1)s)", + "INSERT INTO myothertable (othername) VALUES" + " (%(othername_m0)s::VARCHAR), (%(othername_m1)s::VARCHAR)", checkparams={"othername_m1": "bar", "othername_m0": "foo"}, ) @@ -1460,8 +1472,8 @@ class InsertImplicitReturningTest( ins = self.tables.myothertable.insert().values([{"othername": "foo"}]) self.assert_compile( ins, - "INSERT INTO myothertable (othername) " - "VALUES (%(othername_m0)s)", + "INSERT INTO myothertable (othername) VALUES" + " (%(othername_m0)s::VARCHAR)", checkparams={"othername_m0": "foo"}, ) @@ -1469,8 +1481,8 @@ class InsertImplicitReturningTest( ins = self.tables.myothertable.insert().values({"othername": "foo"}) self.assert_compile( ins, - "INSERT INTO myothertable (othername) " - "VALUES (%(othername)s) RETURNING myothertable.otherid", + "INSERT INTO myothertable (othername) VALUES" + " (%(othername)s::VARCHAR) RETURNING myothertable.otherid", checkparams={"othername": "foo"}, ) @@ -1755,9 +1767,10 @@ class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): self.assert_compile( stmt, - "INSERT INTO mytable (myid, name) VALUES (%(myid_m0)s, " - "%(name_m0)s), (%(myid_m1)s, %(name_m1)s), (%(myid_m2)s, " - "%(name_m2)s)", + "INSERT INTO mytable (myid, name) VALUES (%(myid_m0)s::INTEGER," + " %(name_m0)s::VARCHAR), (%(myid_m1)s::INTEGER," + " %(name_m1)s::VARCHAR), (%(myid_m2)s::INTEGER," + " %(name_m2)s::VARCHAR)", checkparams={ "myid_m0": 1, "name_m0": "d1", @@ -1797,10 +1810,10 @@ class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): self.assert_compile( table.insert().values(values), - "INSERT INTO sometable (id, data, foo) VALUES " - "(%(id_m0)s, %(data_m0)s, foobar()), " - "(%(id_m1)s, %(data_m1)s, %(foo_m1)s), " - "(%(id_m2)s, %(data_m2)s, foobar())", + "INSERT INTO sometable (id, data, foo) VALUES (%(id_m0)s::INTEGER," + " %(data_m0)s::VARCHAR, foobar()), (%(id_m1)s::INTEGER," + " %(data_m1)s::VARCHAR, %(foo_m1)s::INTEGER), (%(id_m2)s::INTEGER," + " %(data_m2)s::VARCHAR, foobar())", checkparams=checkparams, dialect=postgresql.dialect(), ) @@ -1871,10 +1884,10 @@ class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): self.assert_compile( stmt, - "INSERT INTO sometable (id, data, foo) VALUES " - "(%(id_m0)s, %(data_m0)s, %(foo)s), " - "(%(id_m1)s, %(data_m1)s, %(foo_m1)s), " - "(%(id_m2)s, %(data_m2)s, %(foo_m2)s)", + "INSERT INTO sometable (id, data, foo) VALUES (%(id_m0)s::INTEGER," + " %(data_m0)s::VARCHAR, %(foo)s::INTEGER), (%(id_m1)s::INTEGER," + " %(data_m1)s::VARCHAR, %(foo_m1)s::INTEGER), (%(id_m2)s::INTEGER," + " %(data_m2)s::VARCHAR, %(foo_m2)s::INTEGER)", checkparams=checkparams, dialect=postgresql.dialect(), ) @@ -1929,8 +1942,8 @@ class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): self.assert_compile( stmt, - "INSERT INTO sometable (id, data) VALUES " - "(foobar(), %(data)s) RETURNING sometable.id", + "INSERT INTO sometable (id, data) VALUES (foobar()," + " %(data)s::VARCHAR) RETURNING sometable.id", checkparams={"data": "foo"}, params={"data": "foo"}, dialect=returning_dialect, @@ -1959,8 +1972,8 @@ class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): self.assert_compile( stmt, - "INSERT INTO sometable (id, data) VALUES " - "(foobar(), %(data)s) RETURNING sometable.id", + "INSERT INTO sometable (id, data) VALUES (foobar()," + " %(data)s::VARCHAR) RETURNING sometable.id", checkparams={"data": "foo"}, params={"data": "foo"}, dialect=returning_dialect, @@ -2017,10 +2030,10 @@ class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): self.assert_compile( stmt, - "INSERT INTO sometable (id, data, foo) VALUES " - "(%(id_m0)s, %(data_m0)s, %(foo)s), " - "(%(id_m1)s, %(data_m1)s, %(foo_m1)s), " - "(%(id_m2)s, %(data_m2)s, %(foo_m2)s)", + "INSERT INTO sometable (id, data, foo) VALUES (%(id_m0)s::INTEGER," + " %(data_m0)s::VARCHAR, %(foo)s::INTEGER), (%(id_m1)s::INTEGER," + " %(data_m1)s::VARCHAR, %(foo_m1)s::INTEGER), (%(id_m2)s::INTEGER," + " %(data_m2)s::VARCHAR, %(foo_m2)s::INTEGER)", checkparams=checkparams, dialect=postgresql.dialect(), ) @@ -2058,12 +2071,12 @@ class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): self.assert_compile( table.insert().values(values), - "INSERT INTO sometable (id, data, foo) VALUES " - "(%(id_m0)s, %(data_m0)s, foob()), " - "(%(id_m1)s, %(data_m1)s, foob()), " - "(%(id_m2)s, %(data_m2)s, bar()), " - "(%(id_m3)s, %(data_m3)s, %(foo_m3)s), " - "(%(id_m4)s, %(data_m4)s, foob())", + "INSERT INTO sometable (id, data, foo) VALUES (%(id_m0)s::INTEGER," + " %(data_m0)s::VARCHAR, foob()), (%(id_m1)s::INTEGER," + " %(data_m1)s::VARCHAR, foob()), (%(id_m2)s::INTEGER," + " %(data_m2)s::VARCHAR, bar()), (%(id_m3)s::INTEGER," + " %(data_m3)s::VARCHAR, %(foo_m3)s::INTEGER), (%(id_m4)s::INTEGER," + " %(data_m4)s::VARCHAR, foob())", checkparams=checkparams, dialect=postgresql.dialect(), ) @@ -2095,10 +2108,10 @@ class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): self.assert_compile( table.insert().values(values), - "INSERT INTO sometable (id, data) VALUES " - "(%(id_m0)s, %(data_m0)s), " - "(%(id_m1)s, %(data_m1)s), " - "(%(id_m2)s, %(data_m2)s)", + "INSERT INTO sometable (id, data) VALUES (%(id_m0)s::INTEGER," + " %(data_m0)s::VARCHAR), (%(id_m1)s::INTEGER," + " %(data_m1)s::VARCHAR), (%(id_m2)s::INTEGER," + " %(data_m2)s::VARCHAR)", checkparams=checkparams, dialect=postgresql.dialect(), ) diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py index 908c6018e4..168e5fce6a 100644 --- a/test/sql/test_operators.py +++ b/test/sql/test_operators.py @@ -436,8 +436,10 @@ class MultiElementExprTest(fixtures.TestBase, testing.AssertsCompiledSQL): @testing.combinations( ( lambda p, q: (1 - p) * (2 - q) + 10 * (3 - p) * (4 - q), - "(:p_1 - t.p) * (:q_1 - t.q) + " - ":param_1 * (:p_2 - t.p) * (:q_2 - t.q)", + ( + "(:p_1 - t.p) * (:q_1 - t.q) + " + ":param_1 * (:p_2 - t.p) * (:q_2 - t.q)" + ), ), ( lambda p, q: (1 - p) * (2 - q) * (3 - p) * (4 - q), @@ -450,10 +452,12 @@ class MultiElementExprTest(fixtures.TestBase, testing.AssertsCompiledSQL): * (q + (p - 3) + (q - 5) + (p - 9)) * (4 + q + 9) ), - "(:p_1 + t.p + :param_1) * " - "t.p * (t.q - :q_1) * (t.p + :p_2) * " - "(t.q + (t.p - :p_3) + (t.q - :q_2) + (t.p - :p_4)) * " - "(:q_3 + t.q + :param_2)", + ( + "(:p_1 + t.p + :param_1) * " + "t.p * (t.q - :q_1) * (t.p + :p_2) * " + "(t.q + (t.p - :p_3) + (t.q - :q_2) + (t.p - :p_4)) * " + "(:q_3 + t.q + :param_2)" + ), ), ( lambda p, q: (1 // p) - (2 // q) - (3 // p) - (4 // q), @@ -465,8 +469,10 @@ class MultiElementExprTest(fixtures.TestBase, testing.AssertsCompiledSQL): ), ( lambda p, q: (1 + p) * 3 * (2 + q) * 4 * (3 + p) - (4 + q), - "(:p_1 + t.p) * :param_1 * (:q_1 + t.q) * " - ":param_2 * (:p_2 + t.p) - (:q_2 + t.q)", + ( + "(:p_1 + t.p) * :param_1 * (:q_1 + t.q) * " + ":param_2 * (:p_2 + t.p) - (:q_2 + t.q)" + ), ), argnames="expr, expected", ) @@ -509,8 +515,10 @@ class MultiElementExprTest(fixtures.TestBase, testing.AssertsCompiledSQL): f"SELECT NOT (t.q{opstring}t.p{opstring}{exprs}) " "AS anon_1 FROM t" if not reverse - else f"SELECT NOT ({exprs}{opstring}t.q{opstring}t.p) " - "AS anon_1 FROM t" + else ( + f"SELECT NOT ({exprs}{opstring}t.q{opstring}t.p) " + "AS anon_1 FROM t" + ) ), ) else: @@ -520,8 +528,10 @@ class MultiElementExprTest(fixtures.TestBase, testing.AssertsCompiledSQL): f"SELECT t.q{opstring}t.p{opstring}{exprs} " "AS anon_1 FROM t" if not reverse - else f"SELECT {exprs}{opstring}t.q{opstring}t.p " - "AS anon_1 FROM t" + else ( + f"SELECT {exprs}{opstring}t.q{opstring}t.p " + "AS anon_1 FROM t" + ) ), ) @@ -2216,7 +2226,7 @@ class IsDistinctFromTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_is_distinct_from_postgresql(self): self.assert_compile( self.table1.c.myid.is_distinct_from(1), - "mytable.myid IS DISTINCT FROM %(myid_1)s", + "mytable.myid IS DISTINCT FROM %(myid_1)s::INTEGER", dialect=postgresql.dialect(), ) @@ -2229,7 +2239,7 @@ class IsDistinctFromTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_not_is_distinct_from_postgresql(self): self.assert_compile( ~self.table1.c.myid.is_distinct_from(1), - "mytable.myid IS NOT DISTINCT FROM %(myid_1)s", + "mytable.myid IS NOT DISTINCT FROM %(myid_1)s::INTEGER", dialect=postgresql.dialect(), ) @@ -2249,7 +2259,7 @@ class IsDistinctFromTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_is_not_distinct_from_postgresql(self): self.assert_compile( self.table1.c.myid.is_not_distinct_from(1), - "mytable.myid IS NOT DISTINCT FROM %(myid_1)s", + "mytable.myid IS NOT DISTINCT FROM %(myid_1)s::INTEGER", dialect=postgresql.dialect(), ) @@ -2262,7 +2272,7 @@ class IsDistinctFromTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_not_is_not_distinct_from_postgresql(self): self.assert_compile( ~self.table1.c.myid.is_not_distinct_from(1), - "mytable.myid IS DISTINCT FROM %(myid_1)s", + "mytable.myid IS DISTINCT FROM %(myid_1)s::INTEGER", dialect=postgresql.dialect(), ) @@ -2425,8 +2435,7 @@ class InTest(fixtures.TestBase, testing.AssertsCompiledSQL): self.table1.c.myid.in_( text("SELECT myothertable.otherid FROM myothertable") ), - "mytable.myid IN (SELECT myothertable.otherid " - "FROM myothertable)", + "mytable.myid IN (SELECT myothertable.otherid FROM myothertable)", ) def test_in_24(self): @@ -3210,14 +3219,14 @@ class LikeTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_like_7(self): self.assert_compile( self.table1.c.myid.ilike("somstr", escape="\\"), - "mytable.myid ILIKE %(myid_1)s ESCAPE '\\\\'", + "mytable.myid ILIKE %(myid_1)s::VARCHAR ESCAPE '\\\\'", dialect=postgresql.dialect(), ) def test_like_8(self): self.assert_compile( ~self.table1.c.myid.ilike("somstr", escape="\\"), - "mytable.myid NOT ILIKE %(myid_1)s ESCAPE '\\\\'", + "mytable.myid NOT ILIKE %(myid_1)s::VARCHAR ESCAPE '\\\\'", dialect=postgresql.dialect(), ) @@ -3230,7 +3239,7 @@ class LikeTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_like_10(self): self.assert_compile( self.table1.c.name.ilike("%something%"), - "mytable.name ILIKE %(name_1)s", + "mytable.name ILIKE %(name_1)s::VARCHAR", dialect=postgresql.dialect(), ) @@ -3243,7 +3252,7 @@ class LikeTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_like_12(self): self.assert_compile( ~self.table1.c.name.ilike("%something%"), - "mytable.name NOT ILIKE %(name_1)s", + "mytable.name NOT ILIKE %(name_1)s::VARCHAR", dialect=postgresql.dialect(), ) @@ -3319,7 +3328,7 @@ class MatchTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_match_4(self): self.assert_compile( self.table1.c.myid.match("somstr"), - "mytable.myid @@ plainto_tsquery(%(myid_1)s)", + "mytable.myid @@ plainto_tsquery(%(myid_1)s::VARCHAR)", dialect=postgresql.dialect(), ) @@ -3338,7 +3347,7 @@ class MatchTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_boolean_inversion_postgresql(self): self.assert_compile( ~self.table1.c.myid.match("somstr"), - "NOT mytable.myid @@ plainto_tsquery(%(myid_1)s)", + "NOT mytable.myid @@ plainto_tsquery(%(myid_1)s::VARCHAR)", dialect=postgresql.dialect(), ) @@ -3509,8 +3518,7 @@ class RegexpTestStrCompiler(fixtures.TestBase, testing.AssertsCompiledSQL): self.table.c.myid.match("foo"), ~self.table.c.myid.regexp_match("xx"), ), - "mytable.myid MATCH :myid_1 AND " - "mytable.myid :myid_2", + "mytable.myid MATCH :myid_1 AND mytable.myid :myid_2", ) self.assert_compile( and_( @@ -3573,7 +3581,7 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_contains_pg(self): self.assert_compile( column("x").contains("y"), - "x LIKE '%%' || %(x_1)s || '%%'", + "x LIKE '%%' || %(x_1)s::VARCHAR || '%%'", checkparams={"x_1": "y"}, dialect="postgresql", ) @@ -3713,7 +3721,7 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): """ self.assert_compile( column("x").icontains("y"), - "x ILIKE '%%' || %(x_1)s || '%%'", + "x ILIKE '%%' || %(x_1)s::VARCHAR || '%%'", checkparams={"x_1": "y"}, dialect="postgresql", ) @@ -3764,7 +3772,7 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): """ self.assert_compile( ~column("x").icontains("y"), - "x NOT ILIKE '%%' || %(x_1)s || '%%'", + "x NOT ILIKE '%%' || %(x_1)s::VARCHAR || '%%'", checkparams={"x_1": "y"}, dialect="postgresql", ) @@ -4091,7 +4099,7 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): """ self.assert_compile( column("x").istartswith("y"), - "x ILIKE %(x_1)s || '%%'", + "x ILIKE %(x_1)s::VARCHAR || '%%'", checkparams={"x_1": "y"}, dialect="postgresql", ) @@ -4112,7 +4120,7 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): """ self.assert_compile( ~column("x").istartswith("y"), - "x NOT ILIKE %(x_1)s || '%%'", + "x NOT ILIKE %(x_1)s::VARCHAR || '%%'", checkparams={"x_1": "y"}, dialect="postgresql", ) @@ -4317,7 +4325,7 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_not_endswith_pg(self): self.assert_compile( ~column("x").endswith("y"), - "x NOT LIKE '%%' || %(x_1)s", + "x NOT LIKE '%%' || %(x_1)s::VARCHAR", checkparams={"x_1": "y"}, dialect="postgresql", ) @@ -4396,7 +4404,7 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): """ self.assert_compile( column("x").iendswith("y"), - "x ILIKE '%%' || %(x_1)s", + "x ILIKE '%%' || %(x_1)s::VARCHAR", checkparams={"x_1": "y"}, dialect="postgresql", ) @@ -4417,7 +4425,7 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): """ self.assert_compile( ~column("x").iendswith("y"), - "x NOT ILIKE '%%' || %(x_1)s", + "x NOT ILIKE '%%' || %(x_1)s::VARCHAR", checkparams={"x_1": "y"}, dialect="postgresql", ) @@ -4860,8 +4868,7 @@ class InSelectableTest(fixtures.TestBase, testing.AssertsCompiledSQL): self.assert_compile( column("q").in_(stmt.select()), - "q IN (SELECT anon_1.x FROM " - "(SELECT t.x AS x FROM t) AS anon_1)", + "q IN (SELECT anon_1.x FROM (SELECT t.x AS x FROM t) AS anon_1)", ) def test_in_subquery_alias_implicit(self): @@ -5136,8 +5143,9 @@ class AnyAllTest(fixtures.TestBase, testing.AssertsCompiledSQL): self.assert_compile( 5 == fn(t.c.arrval[5:6] + postgresql.array([3, 4])), - f"%(param_1)s = {op} (tab1.arrval[%(arrval_1)s:%(arrval_2)s] || " - "ARRAY[%(param_2)s, %(param_3)s])", + f"%(param_1)s::INTEGER = {op}" + " (tab1.arrval[%(arrval_1)s::INTEGER:%(arrval_2)s::INTEGER] ||" + " ARRAY[%(param_2)s::INTEGER, %(param_3)s::INTEGER])", checkparams={ "arrval_2": 6, "param_1": 5, @@ -5262,7 +5270,7 @@ class DeprecatedAnyAllTest(fixtures.TestBase, testing.AssertsCompiledSQL): with self._array_any_deprecation(): expr = fn(t.c.arrval, bindparam("param")) - expected = f"%(param)s = {op} (tab1.arrval)" + expected = f"%(param)s::INTEGER = {op} (tab1.arrval)" is_(expr.left.type._type_affinity, Integer) self.assert_compile(expr, expected, dialect="postgresql") diff --git a/test/sql/test_returning.py b/test/sql/test_returning.py index eaea657eac..e3f3478d43 100644 --- a/test/sql/test_returning.py +++ b/test/sql/test_returning.py @@ -52,10 +52,18 @@ class ReturnCombinationTests(fixtures.TestBase, AssertsCompiledSQL): @testing.combinations( ( insert, - "INSERT INTO foo (id, q, x, y) " - "VALUES (%(id)s, %(q)s, %(x)s, %(y)s)", + ( + "INSERT INTO foo (id, q, x, y) VALUES (%(id)s::INTEGER," + " %(q)s::INTEGER, %(x)s::INTEGER, %(y)s::INTEGER)" + ), + ), + ( + update, + ( + "UPDATE foo SET id=%(id)s::INTEGER, q=%(q)s::INTEGER," + " x=%(x)s::INTEGER, y=%(y)s::INTEGER" + ), ), - (update, "UPDATE foo SET id=%(id)s, q=%(q)s, x=%(x)s, y=%(y)s"), (delete, "DELETE FROM foo"), argnames="dml_fn, sql_frag", id_="na", @@ -223,8 +231,7 @@ class ReturnCombinationTests(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( stmt, - "UPDATE t SET x=%(x)s, y=%(y)s, z=%(z)s " - "RETURNING t.x, t.y, t.z", + "UPDATE t SET x=%(x)s, y=%(y)s, z=%(z)s RETURNING t.x, t.y, t.z", ) cte = stmt.cte("c") diff --git a/test/sql/test_text.py b/test/sql/test_text.py index 3cd13ab00f..19c6b96f78 100644 --- a/test/sql/test_text.py +++ b/test/sql/test_text.py @@ -403,7 +403,8 @@ class BindParamTest(fixtures.TestBase, AssertsCompiledSQL): text( "select * from foo where lala=:bar and hoho=:whee" ).bindparams(bar=4, whee=7), - "select * from foo where lala=%(bar)s and hoho=%(whee)s", + "select * from foo where lala=%(bar)s::INTEGER " + "and hoho=%(whee)s::INTEGER", checkparams={"bar": 4, "whee": 7}, dialect="postgresql", ) diff --git a/test/sql/test_types.py b/test/sql/test_types.py index fc0f14817c..709d586d2e 100644 --- a/test/sql/test_types.py +++ b/test/sql/test_types.py @@ -1032,12 +1032,14 @@ class TypeDecoratorSpecialCasesTest(AssertsCompiledSQL, fixtures.TestBase): self.assert_compile( 7 < column("q", ArrayDec).any_(), - "%(param_1)s < ANY (q)", + "%(param_1)s::INTEGER < ANY (q)", dialect="postgresql", ) self.assert_compile( - column("q", ArrayDec)[5], "q[%(q_1)s]", dialect="postgresql" + column("q", ArrayDec)[5], + "q[%(q_1)s::INTEGER]", + dialect="postgresql", ) def test_typedec_of_json_ops(self): @@ -1047,12 +1049,14 @@ class TypeDecoratorSpecialCasesTest(AssertsCompiledSQL, fixtures.TestBase): cache_ok = True self.assert_compile( - column("q", JsonDec)["q"], "q -> %(q_1)s", dialect="postgresql" + column("q", JsonDec)["q"], + "q -> %(q_1)s::TEXT", + dialect="postgresql", ) self.assert_compile( column("q", JsonDec)["q"].as_integer(), - "CAST(q ->> %(q_1)s AS INTEGER)", + "CAST(q ->> %(q_1)s::TEXT AS INTEGER)", dialect="postgresql", ) -- 2.47.3