diff options
| -rw-r--r-- | doc/build/changelog/unreleased_14/dialect_print.rst | 14 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mssql/base.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mysql/base.py | 100 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mysql/dml.py | 4 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 247 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/dml.py | 4 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/ext.py | 4 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/ddl.py | 4 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/elements.py | 14 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/schema.py | 2 | ||||
| -rw-r--r-- | test/sql/test_compiler.py | 34 |
11 files changed, 289 insertions, 140 deletions
diff --git a/doc/build/changelog/unreleased_14/dialect_print.rst b/doc/build/changelog/unreleased_14/dialect_print.rst new file mode 100644 index 000000000..2de7a4b19 --- /dev/null +++ b/doc/build/changelog/unreleased_14/dialect_print.rst @@ -0,0 +1,14 @@ +.. change:: + :tags: feature, engine + + Dialect-specific constructs such as + :meth:`_postgresql.Insert.on_conflict_do_update` can now stringify in-place + without the need to specify an explicit dialect object. The constructs, + when called upon for ``str()``, ``print()``, etc. now have internal + direction to call upon their appropriate dialect rather than the + "default"dialect which doesn't know how to stringify these. The approach + is also adapted to generic schema-level create/drop such as + :class:`_schema.AddConstraint`, which will adapt its stringify dialect to + one indicated by the element within it, such as the + :class:`_postgresql.ExcludeConstraint` object. + diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index bfe7a00ba..9addbf31f 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -1268,6 +1268,8 @@ class TryCast(sql.elements.Cast): __visit_name__ = "try_cast" + stringify_dialect = "mssql" + def __init__(self, *arg, **kw): """Create a TRY_CAST expression. diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 04175dedf..3ad0e3813 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -454,20 +454,24 @@ values to INSERT versus the values for UPDATE. SQLAlchemy provides ``ON DUPLICATE KEY UPDATE`` support via the MySQL-specific :func:`.mysql.insert()` function, which provides -the generative method :meth:`~.mysql.Insert.on_duplicate_key_update`:: +the generative method :meth:`~.mysql.Insert.on_duplicate_key_update`: - from sqlalchemy.dialects.mysql import insert +.. sourcecode:: pycon+sql - insert_stmt = insert(my_table).values( - id='some_existing_id', - data='inserted value') + >>> from sqlalchemy.dialects.mysql import insert - on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( - data=insert_stmt.inserted.data, - status='U' - ) + >>> insert_stmt = insert(my_table).values( + ... id='some_existing_id', + ... data='inserted value') + + >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( + ... data=insert_stmt.inserted.data, + ... status='U' + ... ) + >>> print(on_duplicate_key_stmt) + {opensql}INSERT INTO my_table (id, data) VALUES (%s, %s) + ON DUPLICATE KEY UPDATE data = VALUES(data), status = %s - conn.execute(on_duplicate_key_stmt) Unlike PostgreSQL's "ON CONFLICT" phrase, the "ON DUPLICATE KEY UPDATE" phrase will always match on any primary key or unique key, and will always @@ -482,33 +486,51 @@ keyword arguments passed to the given column key values (usually the name of the column, unless it specifies :paramref:`_schema.Column.key` ) as keys and literal or SQL expressions -as values:: +as values: - on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( - data="some data", - updated_at=func.current_timestamp(), - ) +.. sourcecode:: pycon+sql + + >>> insert_stmt = insert(my_table).values( + ... id='some_existing_id', + ... data='inserted value') + + >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( + ... data="some data", + ... updated_at=func.current_timestamp(), + ... ) + + >>> print(on_duplicate_key_stmt) + {opensql}INSERT INTO my_table (id, data) VALUES (%s, %s) + ON DUPLICATE KEY UPDATE data = %s, updated_at = CURRENT_TIMESTAMP In a manner similar to that of :meth:`.UpdateBase.values`, other parameter -forms are accepted, including a single dictionary:: +forms are accepted, including a single dictionary: - on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( - {"data": "some data", "updated_at": func.current_timestamp()}, - ) +.. sourcecode:: pycon+sql + + >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( + ... {"data": "some data", "updated_at": func.current_timestamp()}, + ... ) as well as a list of 2-tuples, which will automatically provide a parameter-ordered UPDATE statement in a manner similar to that described at :ref:`updates_order_parameters`. Unlike the :class:`_expression.Update` object, no special flag is needed to specify the intent since the argument form is -this context is unambiguous:: +this context is unambiguous: - on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( - [ - ("data", "some data"), - ("updated_at", func.current_timestamp()), - ], - ) +.. sourcecode:: pycon+sql + + >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( + ... [ + ... ("data", "some data"), + ... ("updated_at", func.current_timestamp()), + ... ] + ... ) + + >>> print(on_duplicate_key_stmt) + {opensql}INSERT INTO my_table (id, data) VALUES (%s, %s) + ON DUPLICATE KEY UPDATE data = %s, updated_at = CURRENT_TIMESTAMP .. versionchanged:: 1.3 support for parameter-ordered UPDATE clause within MySQL ON DUPLICATE KEY UPDATE @@ -528,19 +550,23 @@ In order to refer to the proposed insertion row, the special alias :attr:`~.mysql.Insert.inserted` is available as an attribute on the :class:`.mysql.Insert` object; this object is a :class:`_expression.ColumnCollection` which contains all columns of the target -table:: +table: - from sqlalchemy.dialects.mysql import insert +.. sourcecode:: pycon+sql - stmt = insert(my_table).values( - id='some_id', - data='inserted value', - author='jlh') - do_update_stmt = stmt.on_duplicate_key_update( - data="updated value", - author=stmt.inserted.author - ) - conn.execute(do_update_stmt) + >>> stmt = insert(my_table).values( + ... id='some_id', + ... data='inserted value', + ... author='jlh') + + >>> do_update_stmt = stmt.on_duplicate_key_update( + ... data="updated value", + ... author=stmt.inserted.author + ... ) + + >>> print(do_update_stmt) + {opensql}INSERT INTO my_table (id, data, author) VALUES (%s, %s, %s) + ON DUPLICATE KEY UPDATE data = %s, author = VALUES(author) When rendered, the "inserted" namespace will produce the expression ``VALUES(<columnname>)``. diff --git a/lib/sqlalchemy/dialects/mysql/dml.py b/lib/sqlalchemy/dialects/mysql/dml.py index c19ed6c0b..9f8177c59 100644 --- a/lib/sqlalchemy/dialects/mysql/dml.py +++ b/lib/sqlalchemy/dialects/mysql/dml.py @@ -22,6 +22,8 @@ class Insert(StandardInsert): """ + stringify_dialect = "mysql" + @property def inserted(self): """Provide the "inserted" namespace for an ON DUPLICATE KEY UPDATE statement @@ -120,6 +122,8 @@ class OnDuplicateClause(ClauseElement): _parameter_ordering = None + stringify_dialect = "mysql" + def __init__(self, inserted_alias, update): self.inserted_alias = inserted_alias diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 391361e23..c7467f5ba 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -415,26 +415,29 @@ the indexes. SQLAlchemy provides ``ON CONFLICT`` support via the PostgreSQL-specific :func:`_postgresql.insert()` function, which provides the generative methods :meth:`~.postgresql.Insert.on_conflict_do_update` -and :meth:`~.postgresql.Insert.on_conflict_do_nothing`:: - - from sqlalchemy.dialects.postgresql import insert - - insert_stmt = insert(my_table).values( - id='some_existing_id', - data='inserted value') - - do_nothing_stmt = insert_stmt.on_conflict_do_nothing( - index_elements=['id'] - ) - - conn.execute(do_nothing_stmt) - - do_update_stmt = insert_stmt.on_conflict_do_update( - constraint='pk_my_table', - set_=dict(data='updated value') - ) - - conn.execute(do_update_stmt) +and :meth:`~.postgresql.Insert.on_conflict_do_nothing`: + +.. sourcecode:: pycon+sql + + >>> from sqlalchemy.dialects.postgresql import insert + >>> insert_stmt = insert(my_table).values( + ... id='some_existing_id', + ... data='inserted value') + >>> do_nothing_stmt = insert_stmt.on_conflict_do_nothing( + ... index_elements=['id'] + ... ) + >>> print(do_nothing_stmt) + {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + ON CONFLICT (id) DO NOTHING + {stop} + + >>> do_update_stmt = insert_stmt.on_conflict_do_update( + ... constraint='pk_my_table', + ... set_=dict(data='updated value') + ... ) + >>> print(do_update_stmt) + {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + ON CONFLICT ON CONSTRAINT pk_my_table DO UPDATE SET data = %(param_1)s Both methods supply the "target" of the conflict using either the named constraint or by column inference: @@ -442,45 +445,67 @@ named constraint or by column inference: * The :paramref:`.Insert.on_conflict_do_update.index_elements` argument specifies a sequence containing string column names, :class:`_schema.Column` objects, and/or SQL expression elements, which would identify a unique - index:: - - do_update_stmt = insert_stmt.on_conflict_do_update( - index_elements=['id'], - set_=dict(data='updated value') - ) - - do_update_stmt = insert_stmt.on_conflict_do_update( - index_elements=[my_table.c.id], - set_=dict(data='updated value') - ) + index: + + .. sourcecode:: pycon+sql + + >>> do_update_stmt = insert_stmt.on_conflict_do_update( + ... index_elements=['id'], + ... set_=dict(data='updated value') + ... ) + >>> print(do_update_stmt) + {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + ON CONFLICT (id) DO UPDATE SET data = %(param_1)s + {stop} + + >>> do_update_stmt = insert_stmt.on_conflict_do_update( + ... index_elements=[my_table.c.id], + ... set_=dict(data='updated value') + ... ) + >>> print(do_update_stmt) + {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + ON CONFLICT (id) DO UPDATE SET data = %(param_1)s * When using :paramref:`.Insert.on_conflict_do_update.index_elements` to infer an index, a partial index can be inferred by also specifying the - use the :paramref:`.Insert.on_conflict_do_update.index_where` parameter:: + use the :paramref:`.Insert.on_conflict_do_update.index_where` parameter: - from sqlalchemy.dialects.postgresql import insert + .. sourcecode:: pycon+sql - stmt = insert(my_table).values(user_email='a@b.com', data='inserted data') - stmt = stmt.on_conflict_do_update( - index_elements=[my_table.c.user_email], - index_where=my_table.c.user_email.like('%@gmail.com'), - set_=dict(data=stmt.excluded.data) - ) - conn.execute(stmt) + >>> stmt = insert(my_table).values(user_email='a@b.com', data='inserted data') + >>> stmt = stmt.on_conflict_do_update( + ... index_elements=[my_table.c.user_email], + ... index_where=my_table.c.user_email.like('%@gmail.com'), + ... set_=dict(data=stmt.excluded.data) + ... ) + >>> print(stmt) + {opensql}INSERT INTO my_table (data, user_email) + VALUES (%(data)s, %(user_email)s) ON CONFLICT (user_email) + WHERE user_email LIKE %(user_email_1)s DO UPDATE SET data = excluded.data * The :paramref:`.Insert.on_conflict_do_update.constraint` argument is used to specify an index directly rather than inferring it. This can be - the name of a UNIQUE constraint, a PRIMARY KEY constraint, or an INDEX:: - - do_update_stmt = insert_stmt.on_conflict_do_update( - constraint='my_table_idx_1', - set_=dict(data='updated value') - ) - - do_update_stmt = insert_stmt.on_conflict_do_update( - constraint='my_table_pk', - set_=dict(data='updated value') - ) + the name of a UNIQUE constraint, a PRIMARY KEY constraint, or an INDEX: + + .. sourcecode:: pycon+sql + + >>> do_update_stmt = insert_stmt.on_conflict_do_update( + ... constraint='my_table_idx_1', + ... set_=dict(data='updated value') + ... ) + >>> print(do_update_stmt) + {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + ON CONFLICT ON CONSTRAINT my_table_idx_1 DO UPDATE SET data = %(param_1)s + {stop} + + >>> do_update_stmt = insert_stmt.on_conflict_do_update( + ... constraint='my_table_pk', + ... set_=dict(data='updated value') + ... ) + >>> print(do_update_stmt) + {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + ON CONFLICT ON CONSTRAINT my_table_pk DO UPDATE SET data = %(param_1)s + {stop} * The :paramref:`.Insert.on_conflict_do_update.constraint` argument may also refer to a SQLAlchemy construct representing a constraint, @@ -492,28 +517,35 @@ named constraint or by column inference: construct. This use is especially convenient to refer to the named or unnamed primary key of a :class:`_schema.Table` using the - :attr:`_schema.Table.primary_key` attribute:: + :attr:`_schema.Table.primary_key` attribute: - do_update_stmt = insert_stmt.on_conflict_do_update( - constraint=my_table.primary_key, - set_=dict(data='updated value') - ) + .. sourcecode:: pycon+sql + + >>> do_update_stmt = insert_stmt.on_conflict_do_update( + ... constraint=my_table.primary_key, + ... set_=dict(data='updated value') + ... ) + >>> print(do_update_stmt) + {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + ON CONFLICT (id) DO UPDATE SET data = %(param_1)s ``ON CONFLICT...DO UPDATE`` is used to perform an update of the already existing row, using any combination of new values as well as values from the proposed insertion. These values are specified using the :paramref:`.Insert.on_conflict_do_update.set_` parameter. This parameter accepts a dictionary which consists of direct values -for UPDATE:: +for UPDATE: - from sqlalchemy.dialects.postgresql import insert +.. sourcecode:: pycon+sql - stmt = insert(my_table).values(id='some_id', data='inserted value') - do_update_stmt = stmt.on_conflict_do_update( - index_elements=['id'], - set_=dict(data='updated value') - ) - conn.execute(do_update_stmt) + >>> stmt = insert(my_table).values(id='some_id', data='inserted value') + >>> do_update_stmt = stmt.on_conflict_do_update( + ... index_elements=['id'], + ... set_=dict(data='updated value') + ... ) + >>> print(do_update_stmt) + {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + ON CONFLICT (id) DO UPDATE SET data = %(param_1)s .. warning:: @@ -530,57 +562,70 @@ In order to refer to the proposed insertion row, the special alias the :class:`_postgresql.Insert` object; this object is a :class:`_expression.ColumnCollection` which alias contains all columns of the target -table:: - - from sqlalchemy.dialects.postgresql import insert - - stmt = insert(my_table).values( - id='some_id', - data='inserted value', - author='jlh') - do_update_stmt = stmt.on_conflict_do_update( - index_elements=['id'], - set_=dict(data='updated value', author=stmt.excluded.author) - ) - conn.execute(do_update_stmt) +table: + +.. sourcecode:: pycon+sql + + >>> stmt = insert(my_table).values( + ... id='some_id', + ... data='inserted value', + ... author='jlh' + ... ) + >>> do_update_stmt = stmt.on_conflict_do_update( + ... index_elements=['id'], + ... set_=dict(data='updated value', author=stmt.excluded.author) + ... ) + >>> print(do_update_stmt) + {opensql}INSERT INTO my_table (id, data, author) + VALUES (%(id)s, %(data)s, %(author)s) + ON CONFLICT (id) DO UPDATE SET data = %(param_1)s, author = excluded.author The :meth:`_expression.Insert.on_conflict_do_update` method also accepts a WHERE clause using the :paramref:`.Insert.on_conflict_do_update.where` -parameter, which will limit those rows which receive an UPDATE:: - - from sqlalchemy.dialects.postgresql import insert - - stmt = insert(my_table).values( - id='some_id', - data='inserted value', - author='jlh') - on_update_stmt = stmt.on_conflict_do_update( - index_elements=['id'], - set_=dict(data='updated value', author=stmt.excluded.author) - where=(my_table.c.status == 2) - ) - conn.execute(on_update_stmt) +parameter, which will limit those rows which receive an UPDATE: + +.. sourcecode:: pycon+sql + + >>> stmt = insert(my_table).values( + ... id='some_id', + ... data='inserted value', + ... author='jlh' + ... ) + >>> on_update_stmt = stmt.on_conflict_do_update( + ... index_elements=['id'], + ... set_=dict(data='updated value', author=stmt.excluded.author), + ... where=(my_table.c.status == 2) + ... ) + >>> print(on_update_stmt) + {opensql}INSERT INTO my_table (id, data, author) + VALUES (%(id)s, %(data)s, %(author)s) + ON CONFLICT (id) DO UPDATE SET data = %(param_1)s, author = excluded.author + WHERE my_table.status = %(status_1)s ``ON CONFLICT`` may also be used to skip inserting a row entirely if any conflict with a unique or exclusion constraint occurs; below this is illustrated using the -:meth:`~.postgresql.Insert.on_conflict_do_nothing` method:: +:meth:`~.postgresql.Insert.on_conflict_do_nothing` method: - from sqlalchemy.dialects.postgresql import insert +.. sourcecode:: pycon+sql - stmt = insert(my_table).values(id='some_id', data='inserted value') - stmt = stmt.on_conflict_do_nothing(index_elements=['id']) - conn.execute(stmt) + >>> stmt = insert(my_table).values(id='some_id', data='inserted value') + >>> stmt = stmt.on_conflict_do_nothing(index_elements=['id']) + >>> print(stmt) + {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + ON CONFLICT (id) DO NOTHING If ``DO NOTHING`` is used without specifying any columns or constraint, it has the effect of skipping the INSERT for any unique or exclusion -constraint violation which occurs:: +constraint violation which occurs: - from sqlalchemy.dialects.postgresql import insert +.. sourcecode:: pycon+sql - stmt = insert(my_table).values(id='some_id', data='inserted value') - stmt = stmt.on_conflict_do_nothing() - conn.execute(stmt) + >>> stmt = insert(my_table).values(id='some_id', data='inserted value') + >>> stmt = stmt.on_conflict_do_nothing() + >>> print(stmt) + {opensql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) + ON CONFLICT DO NOTHING .. versionadded:: 1.1 Added support for PostgreSQL ON CONFLICT clauses diff --git a/lib/sqlalchemy/dialects/postgresql/dml.py b/lib/sqlalchemy/dialects/postgresql/dml.py index 70d26a94b..50fd09528 100644 --- a/lib/sqlalchemy/dialects/postgresql/dml.py +++ b/lib/sqlalchemy/dialects/postgresql/dml.py @@ -30,6 +30,8 @@ class Insert(StandardInsert): """ + stringify_dialect = "postgresql" + @util.memoized_property def excluded(self): """Provide the ``excluded`` namespace for an ON CONFLICT statement @@ -149,6 +151,8 @@ insert = public_factory( class OnConflictClause(ClauseElement): + stringify_dialect = "postgresql" + def __init__(self, constraint=None, index_elements=None, index_where=None): if constraint is not None: diff --git a/lib/sqlalchemy/dialects/postgresql/ext.py b/lib/sqlalchemy/dialects/postgresql/ext.py index c139fe94f..78d9a96b6 100644 --- a/lib/sqlalchemy/dialects/postgresql/ext.py +++ b/lib/sqlalchemy/dialects/postgresql/ext.py @@ -52,6 +52,8 @@ class aggregate_order_by(expression.ColumnElement): __visit_name__ = "aggregate_order_by" + stringify_dialect = "postgresql" + def __init__(self, target, *order_by): self.target = coercions.expect(roles.ExpressionElementRole, target) @@ -96,6 +98,8 @@ class ExcludeConstraint(ColumnCollectionConstraint): where = None + create_drop_stringify_dialect = "postgresql" + @elements._document_text_coercion( "where", ":class:`.ExcludeConstraint`", diff --git a/lib/sqlalchemy/sql/ddl.py b/lib/sqlalchemy/sql/ddl.py index 3bd831292..f1012292b 100644 --- a/lib/sqlalchemy/sql/ddl.py +++ b/lib/sqlalchemy/sql/ddl.py @@ -373,6 +373,10 @@ class _CreateDropBase(DDLElement): self.element = element self.bind = bind + @property + def stringify_dialect(self): + return self.element.create_drop_stringify_dialect + def _create_rule_disable(self, compiler): """Allow disable of _create_rule using a callable. diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 03476633a..86611baeb 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -199,6 +199,9 @@ class ClauseElement( """ supports_execution = False + + stringify_dialect = "default" + _from_objects = [] bind = None description = None @@ -435,6 +438,7 @@ class ClauseElement( return self @util.preload_module("sqlalchemy.engine.default") + @util.preload_module("sqlalchemy.engine.url") def compile(self, bind=None, dialect=None, **kw): """Compile this SQL expression. @@ -482,14 +486,20 @@ class ClauseElement( """ - default = util.preloaded.engine_default if not dialect: if bind: dialect = bind.dialect elif self.bind: dialect = self.bind.dialect else: - dialect = default.StrCompileDialect() + if self.stringify_dialect == "default": + default = util.preloaded.engine_default + dialect = default.StrCompileDialect() + else: + url = util.preloaded.engine_url + dialect = url.URL.create( + self.stringify_dialect + ).get_dialect()() return self._compiler(dialect, **kw) diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py index ab635cd56..98ea8648a 100644 --- a/lib/sqlalchemy/sql/schema.py +++ b/lib/sqlalchemy/sql/schema.py @@ -100,6 +100,8 @@ class SchemaItem(SchemaEventTarget, visitors.Visitable): __visit_name__ = "schema_item" + create_drop_stringify_dialect = "default" + def _init_items(self, *args, **kw): """Initialize the list of child items for this SchemaItem.""" for item in args: diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 8e5f4a43a..75ac896e9 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -4268,6 +4268,40 @@ class StringifySpecialTest(fixtures.TestBase): "use some_hint", ) + def test_dialect_specific_sql(self): + my_table = table( + "my_table", column("id"), column("data"), column("user_email") + ) + + from sqlalchemy.dialects.postgresql import insert + + insert_stmt = insert(my_table).values( + id="some_existing_id", data="inserted value" + ) + + do_update_stmt = insert_stmt.on_conflict_do_update( + index_elements=["id"], set_=dict(data="updated value") + ) + eq_ignore_whitespace( + str(do_update_stmt), + "INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) " + "ON CONFLICT (id) DO UPDATE SET data = %(param_1)s", + ) + + def test_dialect_specific_ddl(self): + + from sqlalchemy.dialects.postgresql import ExcludeConstraint + + m = MetaData() + tbl = Table("testtbl", m, Column("room", Integer, primary_key=True)) + cons = ExcludeConstraint(("room", "=")) + tbl.append_constraint(cons) + + eq_ignore_whitespace( + str(schema.AddConstraint(cons)), + "ALTER TABLE testtbl ADD EXCLUDE USING gist " "(room WITH =)", + ) + class KwargPropagationTest(fixtures.TestBase): @classmethod |