Fixed issue in the :ref:`engine_insertmanyvalues` feature where using
PostgreSQL's ``ON CONFLICT`` clause with
:paramref:`_dml.Insert.returning.sort_by_parameter_order` enabled would
generate invalid SQL when the insert used an implicit sentinel (server-side
autoincrement primary key). The generated SQL would incorrectly declare a
sentinel counter column in the ``imp_sen`` table alias without providing
corresponding values in the ``VALUES`` clause, leading to a
``ProgrammingError`` indicating column count mismatch. The fix allows batch
execution mode when ``embed_values_counter`` is active, as the embedded
counter provides the ordering capability needed even with upsert behaviors,
rather than unnecessarily downgrading to row-at-a-time execution.
Fixes: #13107
Change-Id: I382472b2cf2991b520344adea5783584e27425d0
this DB is extremely erratic in being able to connect. Add
a brute force connection retrier to all engines everywhere
(which for oracledb we can fortunately use their built-in feature
that also works).
This actually works and I can see it pausing under load, reconnecting,
and succeeding. the problem is that absolutely every engine everywhere
needs this routine otherwise an engine without a retrier in it will
crash. That then necessitates digging into testing_engine(),
making sure testing_engine() is used everywhere an engine that's going
to connect is used, then dealing with the fallout from that.
We also simplify some older workarounds for cx_oracle and
hack into config/provision to make oracledb seem like the primary
DBAPI for most tests.
testing_engine has been completely overhauled, making use of a new
post_configure_testing_engine() hook which moves and refines
the SQLite pool sharing and savepoint logic all into sqlite/provision.py
and also allows for cx_oracle to apply a retry event handler.
Change-Id: I4ea4c523effb878290d28b94d8925eb32fc5ae3b
one particular vector test wont run on oracle 23c free, so
just disable it.
added better skips for the rest of the vector tests and
fixed a deprecation issue.
this will be the first run on the new oracle23 on CI so we'll have to
see how this goes.
Also adjust for mariabdb12 being overly helpful with regards
to stale row updates.
as we are having trouble getting 23c to pass throug transaction
tests, i noted we have an explosion of tests due to the multiple
drivers, so this patch introduces __sparse_driver_backend__
for all tests where we want variety of
database server but there's no need to test every driver.
This should dramatically reduce the size of the test suite run
Change-Id: Ic8d3eb0a60e76b4c54c6bb4a721f90c81ede782b
Fixed issue in "insertmanyvalues" feature where an INSERT..RETURNING
that also made use of a sentinel column to track results would fail to
filter out the additional column when :meth:`.Result.unique` were used
to uniquify the result set.
Fixes: #10802
Change-Id: Ie4f9dab96193099002088c5219cc41a543a00f62
Fixed issue in "insertmanyvalues" feature where a particular call to
``cursor.fetchall()`` were not wrapped in SQLAlchemy's exception wrapper,
which apparently can raise a database exception during fetch when using
pyodbc.
Fixes: #11532
Change-Id: Ic07d3e79dd597e18d87a56b45ddffa25e762beb9
Made a change to the adjustment made in version 2.0.10 for 🎫`9618`,
which added the behavior of reconciling RETURNING rows from a bulk INSERT
to the parameters that were passed to it. This behavior included a
comparison of already-DB-converted bound parameter values against returned
row values that was not always "symmetrical" for SQL column types such as
UUIDs, depending on specifics of how different DBAPIs receive such values
versus how they return them, necessitating the need for additional
"sentinel value resolver" methods on these column types. Unfortunately
this broke third party column types such as UUID/GUID types in libraries
like SQLModel which did not implement this special method, raising an error
"Can't match sentinel values in result set to parameter sets". Rather than
attempt to further explain and document this implementation detail of the
"insertmanyvalues" feature including a public version of the new
method, the approach is intead revised to no longer need this extra
conversion step, and the logic that does the comparison now works on the
pre-converted bound parameter value compared to the post-result-processed
value, which should always be of a matching datatype. In the unusual case
that a custom SQL column type that also happens to be used in a "sentinel"
column for bulk INSERT is not receiving and returning the same value type,
the "Can't match" error will be raised, however the mitigation is
straightforward in that the same Python datatype should be passed as that
returned.
Fixes: #11160
Change-Id: Ica62571e923ad9545eb90502e6732b11875b164a
Fixed issue in :ref:`engine_insertmanyvalues` feature where using a primary
key column with an "inline execute" default generator such as an explicit
:class:`.Sequence` with an explcit schema name, while at the same time
using the
:paramref:`_engine.Connection.execution_options.schema_translate_map`
feature would fail to render the sequence or the parameters properly,
leading to errors.
Fixes: #11157
Change-Id: I35666af46d40996aff35d3d39f48c150d838e6e4
Added new core execution option
paramref:`_engine.Connection.execution_options.preserve_rowcount`
to unconditionally save the ``rowcount`` attribute from the cursor in the
class:`_engine.Result` returned from an execution, regardless of the
statement being executed.
When this option is provided the correct value is also set when
an INSERT makes use of the "insertmanyvalues" mode, that may use
more than one actualy cursor execution.
Fixes: #10974
Change-Id: Icecef6b7539be9f0a1a02b9539864f5f163dcfbc
Fixed an issue regarding the use of the :class:`.Uuid` datatype with the
:paramref:`.Uuid.as_uuid` parameter set to False, when using the pymssql
dialect. ORM-optimized INSERT statements (e.g. the "insertmanyvalues"
feature) would not correctly align primary key UUID values for bulk INSERT
statements, resulting in errors.
This change also adds a small degree of generalization to the
Uuid datatype by adding the native/non-native compilation conditional
to the base compiler.
Patch is originally part of Ib920871102b9b64f2cba9697f5cb72b6263e4ed8
which is implementing native UUID for mariadb in 2.1 only.
Change-Id: I96cbec5c0ece312b345206aa5a5db2ffcf732d41
I updated flake8 in pre-commit a few days ago but forgot to do it in
tox.
this flake seems to be picking inside of f-strings, which black does
not fix, so fix these manually.
Change-Id: I9a641a99e280fbba9d893a6f1f051b5039d5d4eb
fix a handful of warnings that were emitting but not raising,
usually because they were inside an "expect_warnings" block.
modify "expect_warnings" to always use "raise_on_any_unexpected"
behavior; remove this parameter.
Fixed issue in semi-private ``await_only()`` and ``await_fallback()``
concurrency functions where the given awaitable would remain un-awaited if
the function threw a ``GreenletError``, which could cause "was not awaited"
warnings later on if the program continued. In this case, the given
awaitable is now cancelled before the exception is thrown.
Change-Id: I33668c5e8c670454a3d879e559096fb873b57244
Repaired a major shortcoming which was identified in the
:ref:`engine_insertmanyvalues` performance optimization feature first
introduced in the 2.0 series. This was a continuation of the change in
2.0.9 which disabled the SQL Server version of the feature due to a
reliance in the ORM on apparent row ordering that is not guaranteed to take
place. The fix applies new logic to all "insertmanyvalues" operations,
which takes effect when a new parameter
:paramref:`_dml.Insert.returning.sort_by_parameter_order` on the
:meth:`_dml.Insert.returning` or :meth:`_dml.UpdateBase.return_defaults`
methods, that through a combination of alternate SQL forms, direct
correspondence of client side parameters, and in some cases downgrading to
running row-at-a-time, will apply sorting to each batch of returned rows
using correspondence to primary key or other unique values in each row
which can be correlated to the input data.
Performance impact is expected to be minimal as nearly all common primary
key scenarios are suitable for parameter-ordered batching to be
achieved for all backends other than SQLite, while "row-at-a-time"
mode operates with a bare minimum of Python overhead compared to the very
heavyweight approaches used in the 1.x series. For SQLite, there is no
difference in performance when "row-at-a-time" mode is used.
It's anticipated that with an efficient "row-at-a-time" INSERT with
RETURNING batching capability, the "insertmanyvalues" feature can be later
be more easily generalized to third party backends that include RETURNING
support but not necessarily easy ways to guarantee a correspondence
with parameter order.
Fixes: #9618
References: #9603
Change-Id: I1d79353f5f19638f752936ba1c35e4dc235a8b7c
Fixed regression related to the implementation for the new
"insertmanyvalues" feature where an internal ``TypeError`` would occur in
arrangements where a :func:`_sql.insert` would be referred towards inside
of another :func:`_sql.insert` via a CTE; made additional repairs for this
use case for positional dialects such as asyncpg when using
"insertmanyvalues".
at the core here is a change to positional insertmanyvalues
where we now get exactly the positions for the "manyvalues" within
the larger list, allowing non-"manyvalues" on the left and right
sides at the same time, not assuming anything about how RETURNING
renders etc., since CTEs are in the mix also.
Fixes: #9173
Change-Id: I5ff071fbef0d92a2d6046b9c4e609bb008438afd
The :class:`.Sequence` construct restores itself to the DDL behavior it
had prior to the 1.4 series, where creating a :class:`.Sequence` with
no additional arguments will emit a simple ``CREATE SEQUENCE`` instruction
**without** any additional parameters for "start value". For most backends,
this is how things worked previously in any case; **however**, for
MS SQL Server, the default value on this database is
``-2**63``; to prevent this generally impractical default
from taking effect on SQL Server, the :paramref:`.Sequence.start` parameter
should be provided. As usage of :class:`.Sequence` is unusual
for SQL Server which for many years has standardized on ``IDENTITY``,
it is hoped that this change has minimal impact.
Fixes: #7211
Change-Id: I1207ea10c8cb1528a1519a0fb3581d9621c27b31
Fixed bug in new "insertmanyvalues" feature where INSERT that included a
subquery with :func:`_sql.bindparam` inside of it would fail to render
correctly in "insertmanyvalues" format. This affected psycopg2 most
directly as "insertmanyvalues" is used unconditionally with this driver.
Fixes: #8639
Change-Id: I67903fa86afe208899d4f23f940e0727d1be2ce3
the feature is enabled for all built in backends
when RETURNING is used,
except for Oracle that doesn't need it, and on
psycopg2 and mssql+pyodbc it is used for all INSERT statements,
not just those that use RETURNING.
third party dialects would need to opt in to the new feature
by setting use_insertmanyvalues to True.
Also adds dialect-level guards against using returning
with executemany where we dont have an implementation to
suit it. execute single w/ returning still defers to the
server without us checking.
Fixes: #6047Fixes: #7907
Change-Id: I3936d3c00003f02e322f2e43fb949d0e6e568304
Fixed issues that prevented the new usage patterns for using DML with ORM
objects presented at :ref:`orm_dml_returning_objects` from working
correctly with the SQL Server pyodbc dialect.
Here we add a step to look in compile_state._dict_values more thoroughly
for the keys we need to determine "identity insert" or not, and also
add a new compiler variable dml_compile_state so that we can skip the
ORM's compile_state if present.
Fixes: #8210
Change-Id: Idbd76bb3eb075c647dc6c1cb78f7315c821e15f7
As almost every dialect supports RETURNING now, RETURNING
is also made more of a default assumption.
* the default compiler generates a RETURNING clause now
when specified; CompileError is no longer raised.
* The dialect-level implicit_returning parameter now has
no effect. It's not fully clear if there are real world
cases relying on the dialect-level parameter, so we will see
once 2.0 is released. ORM-level RETURNING can be disabled
at the table level, and perhaps "implicit returning" should
become an ORM-level option at some point as that's where
it applies.
* Altered ORM update() / delete() to respect table-level
implicit returning for fetch.
* Since MariaDB doesnt support UPDATE returning, "full_returning"
is now split into insert_returning, update_returning, delete_returning
* Crazy new thing. Dialects that have *both* cursor.lastrowid
*and* returning. so now we can pick between them for SQLite
and mariadb. so, we are trying to keep it on .lastrowid for
simple inserts with an autoincrement column, this helps with
some edge case test scenarios and i bet .lastrowid is faster
anyway. any return_defaults() / multiparams etc then we
use returning
* SQLite decided they dont want to return rows that match in
ON CONFLICT. this is flat out wrong, but for now we need to
work with it.
Fixes: #6195Fixes: #7011Closes: #7047
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7047
Pull-request-sha: d25d5ea3ab
Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com>
Change-Id: I9908ce0ff7bdc50bd5b27722081767c31c19a950
Altered the compilation mechanics of the :class:`.Insert` construct such
that the "autoincrement primary key" column value will be fetched via
``cursor.lastrowid`` or RETURNING even if present in the parameter set or
within the :meth:`.Insert.values` method as a plain bound value, for
single-row INSERT statements on specific backends that are known to
generate autoincrementing values even when explicit NULL is passed. This
restores a behavior that was in the 1.3 series for both the use case of
separate parameter set as well as :meth:`.Insert.values`. In 1.4, the
parameter set behavior unintentionally changed to no longer do this, but
the :meth:`.Insert.values` method would still fetch autoincrement values up
until 1.4.21 where 🎫`6770` changed the behavior yet again again
unintentionally as this use case was never covered.
The behavior is now defined as "working" to suit the case where databases
such as SQLite, MySQL and MariaDB will ignore an explicit NULL primary key
value and nonetheless invoke an autoincrement generator.
Fixes: #7998
Change-Id: I5d4105a14217945f87fbe9a6f2a3c87f6ef20529
The :paramref:`_sa.create_engine.implicit_returning` parameter is
deprecated on the :func:`_sa.create_engine` function only; the parameter
remains available on the :class:`_schema.Table` object. This parameter was
originally intended to enable the "implicit returning" feature of
SQLAlchemy when it was first developed and was not enabled by default.
Under modern use, there's no reason this parameter should be disabled, and
it has been observed to cause confusion as it degrades performance and
makes it more difficult for the ORM to retrieve recently inserted server
defaults. The parameter remains available on :class:`_schema.Table` to
specifically suit database-level edge cases which make RETURNING
infeasible, the sole example currently being SQL Server's limitation that
INSERT RETURNING may not be used on a table that has INSERT triggers on it.
Also removed from the Oracle dialect some logic that would upgrade
an Oracle 8/8i server version to use implicit returning if the
parameter were explictly passed; these versions of Oracle
still support RETURNING so the feature is now enabled for all
Oracle versions.
Fixes: #6962
Change-Id: Ib338e300cd7c8026c3083043f645084a8211aed8
The tuple returned by :attr:`.CursorResult.inserted_primary_key` is now a
:class:`_result.Row` object with a named tuple interface on top of the
existing tuple interface.
Fixes: #3314
Change-Id: I85677ef60d8329648f368bf497f634758f4e087b
continuing with producing a SQLAlchemy 1.4.0b2 that internally
does not emit any of its own 2.0 deprecation warnings,
migrate the *args and **kwargs passed to execute() methods
that now must be a single list or dictionary.
Alembic 1.5 is again waiting on this internal consistency to
be present so that it can pass all tests with no 2.0
deprecation warnings.
Change-Id: If6b792e57c8c5dff205419644ab68e631575a2fa
It's better, the majority of these changes look more readable to me.
also found some docstrings that had formatting / quoting issues.
Change-Id: I582a45fde3a5648b2f36bab96bad56881321899b
As the test suite has widespread use of many patterns
that are deprecated, enable SQLALCHEMY_WARN_20 globally
for the test suite but then break the warnings filter
out into a whole list of all the individual warnings
we are looking for. this way individual changesets
can target a specific class of warning, as many of these
warnings will indivdidually affect dozens of files
and potentially hundreds of lines of code.
Many warnings are also resolved here as this
patch started out that way. From this point
forward there should be changesets that target a
subset of the warnings at a time.
For expediency, updates some migration 2.0 docs
for ORM as well.
Change-Id: I98b8defdf7c37b818b3824d02f7668e3f5f31c94
Make optional sequences render as identity in mssql
Remove unused dialect option sequence_default_column_type
Change-Id: I821eeffcb442f8d1b69186a9b798b15c3d8d6ff3
MariaDB should not run a Sequence if it has optional=True.
Additionally, rework the rules in crud.py to accommodate the
new combination MariaDB brings us, which is a dialect
that supports both cursor.lastrowid, explicit sequences,
*and* no support for returning.
Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com>
Fixes: #5528
Change-Id: I9a8ea69a34983affa95dfd22186e2908fdf0d58c
MySQL dialect's server_version_info tuple is now all numeric. String
tokens like "MariaDB" are no longer present so that numeric comparison
works in all cases. The .is_mariadb flag on the dialect should be
consulted for whether or not mariadb was detected. Additionally removed
structures meant to support extremely old MySQL versions 3.x and 4.x;
the minimum MySQL version supported is now version 5.0.2.
In addition, as the "MariaDB" name goes away from server version,
expand upon the change in I330815ebe572b6a9818377da56621397335fa702
to support the name "mariadb" throughout the dialect and test suite
when mariadb-only mode is used. This changes the "name" field
on the MariaDB dialect to "mariadb", which then implies a change
throughout the testing requirements system as well as all the
dialect-specific DDL argument names such as "mysql_engine" is
now specified as "mariadb_engine", etc. Make use of the
recent additions to test suite URL provisioning so that we can
force MariaDB databases to have a "mariadb-only" dialect which
allows us to test this name change fully.
Update documentation to refer to MySQL / MariaDB explicitly
as well as indicating the "mariadb_" prefix used for options.
It seems likely that MySQL and MariaDB version numbers are going to
start colliding at some point so having the "mariadb" name
be available as a totally separate dialect name should give us
some options in this regard.
Currently also includes a date related fix to a test for
the postgresql dialect that was implicitly assuming a
non-UTC timezone
Fixes: #4189
Change-Id: I00e76d00f62971e1f067bd61915fa6cc1cf64e5e
The psycopg2 dialect now defaults to using the very performant
``execute_values()`` psycopg2 extension for compiled INSERT statements,
and also impements RETURNING support when this extension is used. This
allows INSERT statements that even include an autoincremented SERIAL
or IDENTITY value to run very fast while still being able to return the
newly generated primary key values. The ORM will then integrate this
new feature in a separate change.
Implements RETURNING for insert with executemany
Adds support to return_defaults() mode and inserted_primary_key
to support mutiple INSERTed rows, via return_defauls_rows
and inserted_primary_key_rows accessors.
within default execution context, new cached compiler
getters are used to fetch primary keys from rows
inserted_primary_key now returns a plain tuple. this
is not yet a row-like object however this can be
added.
Adds distinct "values_only" and "batch" modes, as
"values" has a lot of benefits but "batch" breaks
cursor.rowcount
psycopg2 minimum version 2.7 so we can remove the
large number of checks for very old versions of
psycopg2
simplify tests to no longer distinguish between
native and non-native json
Fixes: #5401
Change-Id: Ic08fd3423d4c5d16ca50994460c0c234868bd61c
Added support for "CREATE SEQUENCE" and full :class:`.Sequence` support for
Microsoft SQL Server. This removes the deprecated feature of using
:class:`.Sequence` objects to manipulate IDENTITY characteristics which
should now be performed using ``mssql_identity_start`` and
``mssql_identity_increment`` as documented at :ref:`mssql_identity`. The
change includes a new parameter :paramref:`.Sequence.data_type` to
accommodate SQL Server's choice of datatype, which for that backend
includes INTEGER and BIGINT. The default starting value for SQL Server's
version of :class:`.Sequence` has been set at 1; this default is now
emitted within the CREATE SEQUENCE DDL for all backends.
Fixes: #4235Fixes: #4633
Change-Id: I6aa55c441e8146c2f002e2e201a7f645e667b916
In 9fca5d827d we attempted to deprecate the "inline=True" flag
and add a generative inline() method, however failed to include
any tests and the method was implemented incorrectly such that
it would get overwritten with the boolean flag immediately.
Rename the internal "inline" flag to "_inline" and add test
support both for the method as well as deprecated support
for the flag, including a fixture addition to assert the expected
value of the flag as it generally does not affect the
actual compiled SQL string.
Change-Id: I0450049f17f1f0d91e22d27f1a973a2b6c0e59f7
This builds on cc718cccc0 which moved
RowProxy to Row, allowing Row to be more like a named tuple.
- KeyedTuple in ORM is replaced with Row
- ResultSetMetaData broken out into "simple" and "cursor" versions
for ORM and Core, as well as LegacyCursor version.
- Row now has _mapping attribute that supplies full mapping behavior.
Row and SimpleRow both have named tuple behavior otherwise.
LegacyRow has some mapping features on the tuple which emit
deprecation warnings (e.g. keys(), values(), etc). the biggest
change for mapping->tuple is the behavior of __contains__ which
moves from testing of "key in row" to "value in row".
- ResultProxy breaks into ResultProxy and FutureResult (interim),
the latter has the newer APIs. Made available to dialects
using execution options.
- internal reflection methods and most tests move off of implicit
Row mapping behavior and move to row._mapping, result.mappings()
method using future result
- a new strategy system for cursor handling replaces the various
subclasses of RowProxy
- some execution context adjustments. We will leave EC in but
refined things like get_result_proxy() and out parameter handling.
Dialects for 1.4 will need to adjust from get_result_proxy()
to get_result_cursor_strategy(), if they are using this method
- out parameter handling now accommodated by get_out_parameter_values()
EC method. Oracle changes for this. external dialect for
DB2 for example will also need to adjust for this.
- deprecate case_insensitive flag for engine / result, this
feature is not used
mapping-methods on Row are deprecated, and replaced with
Row._mapping.<meth>, including:
row.keys() -> use row._mapping.keys()
row.items() -> use row._mapping.items()
row.values() -> use row._mapping.values()
key in row -> use key in row._mapping
int in row -> use int < len(row)
Fixes: #4710Fixes: #4878
Change-Id: Ieb9085e9bcff564359095b754da9ae0af55679f0
Revised the formatting for :class:`.StatementError` when stringified. Each
error detail is broken up over multiple newlines instead of spaced out on a
single line. Additionally, the SQL representation now stringifies the SQL
statement rather than using ``repr()``, so that newlines are rendered as is.
Pull request courtesy Nate Clark.
Fixes: #4500Closes: #4501
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4501
Pull-request-sha: 60cc0ee68d
Change-Id: I79d8418b7495e5691c9a56f41e79495c26a967ff
Applied on top of a pure run of black -l 79 in
I7eda77fed3d8e73df84b3651fd6cfcfe858d4dc9, this set of changes
resolves all remaining flake8 conditions for those codes
we have enabled in setup.cfg.
Included are resolutions for all remaining flake8 issues
including shadowed builtins, long lines, import order, unused
imports, duplicate imports, and docstring issues.
Change-Id: I4f72d3ba1380dd601610ff80b8fb06a2aff8b0fe
This is a straight reformat run using black as is, with no edits
applied at all.
The black run will format code consistently, however in
some cases that are prevalent in SQLAlchemy code it produces
too-long lines. The too-long lines will be resolved in the
following commit that will resolve all remaining flake8 issues
including shadowed builtins, long lines, import order, unused
imports, duplicate imports, and docstring issues.
Change-Id: I7eda77fed3d8e73df84b3651fd6cfcfe858d4dc9
Mike Bayer