From 5f6a00fa45c00de8c132b63098dbdee8e65d915b Mon Sep 17 00:00:00 2001 From: Daniel Black Date: Mon, 13 Sep 2021 23:53:58 +1000 Subject: [PATCH] Docs for INSERT/DELETE RETURNING for MariaDB --- doc/build/orm/persistence_techniques.rst | 16 ++++++++-------- doc/build/orm/versioning.rst | 4 ++-- lib/sqlalchemy/dialects/mysql/base.py | 19 +++++++++++++++++++ 3 files changed, 29 insertions(+), 10 deletions(-) diff --git a/doc/build/orm/persistence_techniques.rst b/doc/build/orm/persistence_techniques.rst index 38f289058b..00895f3954 100644 --- a/doc/build/orm/persistence_techniques.rst +++ b/doc/build/orm/persistence_techniques.rst @@ -36,12 +36,12 @@ from the database. The feature also has conditional support to work in conjunction with primary key columns. A database that supports RETURNING, e.g. PostgreSQL, -Oracle, or SQL Server, or as a special case when using SQLite with the pysqlite -driver and a single auto-increment column, a SQL expression may be assigned -to a primary key column as well. This allows both the SQL expression to -be evaluated, as well as allows any server side triggers that modify the -primary key value on INSERT, to be successfully retrieved by the ORM as -part of the object's primary key:: +Oracle, SQL Server, or MariaDB (10.5+, INSERT only), or as a special case when +using SQLite with the pysqlite driver and a single auto-increment column, a +SQL expression may be assigned to a primary key column as well. This allows +both the SQL expression to be evaluated, as well as allows any server side +triggers that modify the primary key value on INSERT, to be successfully +retrieved by the ORM as part of the object's primary key:: class Foo(Base): @@ -272,8 +272,8 @@ answered are, 1. is this column part of the primary key or not, and 2. does the database support RETURNING or an equivalent, such as "OUTPUT inserted"; these are SQL phrases which return a server-generated value at the same time as the INSERT or UPDATE statement is invoked. Databases that support RETURNING or -equivalent include PostgreSQL, Oracle, and SQL Server. Databases that do not -include SQLite and MySQL. +equivalent include PostgreSQL, Oracle, MariaDB (10.5+, INSERT only) and SQL +Server. Databases that do not include SQLite and MySQL. Case 1: non primary key, RETURNING or equivalent is supported ------------------------------------------------------------- diff --git a/doc/build/orm/versioning.rst b/doc/build/orm/versioning.rst index a141df6a0c..5ad5f8dec1 100644 --- a/doc/build/orm/versioning.rst +++ b/doc/build/orm/versioning.rst @@ -204,8 +204,8 @@ missed version counters:: It is *strongly recommended* that server side version counters only be used when absolutely necessary and only on backends that support :term:`RETURNING`, -e.g. PostgreSQL, Oracle, SQL Server (though SQL Server has -`major caveats `_ when triggers are used), Firebird. +e.g. PostgreSQL, Oracle, MariaDB (10.5+, INSERT only), SQL Server (though SQL +Server has `major caveats `_ when triggers are used), Firebird. .. versionadded:: 0.9.0 diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index c8e204895c..e792a10b0f 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -452,6 +452,25 @@ available. :class:`_mysql.match` +INSERT/DELETE...RETURNING +------------------------- + +The MariaDB dialect supports 10.5+'s ``INSERT..RETURNING`` and +``DELETE..RETURNING`` (10.0+) syntaxes. ``INSERT..RETURNING`` is used by +default for INSERT statements in order to fetch newly generated identifiers. +To specify an explicit ``RETURNING`` clause, use the +:meth:`._UpdateBase.returning` method on a per-statement basis:: + + # INSERT..RETURNING + result = table.insert().returning(table.c.col1, table.c.col2).\ + values(name='foo') + print(result.fetchall()) + + # DELETE..RETURNING + result = table.delete().returning(table.c.col1, table.c.col2).\ + where(table.c.name=='foo') + print(result.fetchall()) + .. _mysql_insert_on_duplicate_key_update: INSERT...ON DUPLICATE KEY UPDATE (Upsert) -- 2.47.3