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
Added ``if_exists`` and ``if_not_exists`` parameters for all "Create" / "Drop" constructs including :class:`.CreateSequence`, :class:`.DropSequence`, :class:`.CreateIndex`, :class:`.DropIndex`, etc. allowing generic "IF EXISTS" / "IF NOT EXISTS" phrases to be rendered within DDL. Pull request courtesy Jesse Bakker. Fixes: #7354 Closes: #8492 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/8492 Pull-request-sha: d107c6ce553bd430111607815f5b3938ffc4770c Change-Id: I367e57b2d9216f5180bcc44e86ca6f3dc794e5ca
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: #6195 Fixes: #7011 Closes: #7047 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7047 Pull-request-sha: d25d5ea3abe094f282c53c7dd87f5f53a9e85248 Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com> Change-Id: I9908ce0ff7bdc50bd5b27722081767c31c19a950
implement strict typing for schema.py this module has lots of public API, lots of old decisions and very hard to follow construction sequences in many cases, and is also where we get a lot of new feature requests, so strict typing should help keep things clean. among improvements here, fixed the pool .info getters and also figured out how to get ColumnCollection and related to be covariant so that we may set them up as returning Column or ColumnClause without any conflicts. DDL was affected, noting that superclasses of DDLElement (_DDLCompiles, added recently) can now be passed into "ddl_if" callables; reorganized ddl into ExecutableDDLElement as a new name for DDLElement and _DDLCompiles renamed to BaseDDLElement. setting up strict also located an API use case that is completely broken, which is connection.execute(some_default) returns a scalar value. This case has been deprecated and new paths have been set up so that connection.scalar() may be used. This likely wasn't possible in previous versions because scalar() would assume a CursorResult. The scalar() change also impacts Session as we have explicit support (since someone had reported it as a regression) for session.execute(Sequence()) to work. They will get the same deprecation message (which omits the word "Connection", just uses ".execute()" and ".scalar()") and they can then use Session.scalar() as well. Getting this to type correctly while still supporting ORM use cases required some refactoring, and I also set up a keyword only delimeter for Session.execute() and related as execution_options / bind_arguments should always be keyword only, applied these changes to AsyncSession as well. Additionally simpify Table __init__ now that we are Python 3 only, we can have positional plus explicit kwargs finally. Simplify Column.__init__ as well again taking advantage of kw only arguments. Fill in most/all __init__ methods in sqltypes.py as the constructor for types is most of the API. should likely do this for dialect-specific types as well. Apply _InfoType for all info attributes as should have been done originally and update descriptor decorators. Change-Id: I3f9f8ff3f1c8858471ff4545ac83d68c88107527
Change-Id: I8172fdcc3103ff92aa049827728484c8779af6b7
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 major action here is to lift and move future.Connection and future.Engine fully into sqlalchemy.engine.base. This removes lots of engine concepts, including: * autocommit * Connection running without a transaction, autobegin is now present in all cases * most "autorollback" is obsolete * Core-level subtransactions (i.e. MarkerTransaction) * "branched" connections, copies of connections * execution_options() returns self, not a new connection * old argument formats, distill_params(), simplifies calling scheme between engine methods * before/after_execute() events (oriented towards compiled constructs) don't emit for exec_driver_sql(). before/after_cursor_execute() is still included for this * old helper methods superseded by context managers, connection.transaction(), engine.transaction() engine.run_callable() * ancient engine-level reflection methods has_table(), table_names() * sqlalchemy.testing.engines.proxying_engine References: #7257 Change-Id: Ib20ed816642d873b84221378a9ec34480e01e82c
Fixed issue where an engine that had ``implicit_returning`` set to False would fail to function when PostgreSQL's "fast insertmany" feature were used in conjunction with a ``Sequence``, as well as if any kind of "executemany" with "return_defaults()" were used in conjunction with a ``Sequence``. Note that PostgreSQL "fast insertmany" uses "RETURNING" by definition, when the SQL statement is passed to the driver; overall, the ``implicit_returning`` flag is legacy and has no real use in modern SQLAlchemy, and will be deprecated in a separate change. Fixes: #6963 Change-Id: Id8e3dd50a21b9124f338067b0fdb57b8f608dca8
Fixed issue where usage of an explicit :class:`.Sequence` would produce inconsistent "inline" behavior for an :class:`.Insert` construct that includes multiple values phrases; the first seq would be inline but subsequent ones would be "pre-execute", leading to inconsistent sequence ordering. The sequence expressions are now fully inline. Fixes: #6361 Change-Id: Ie16794ec0e19979a7e6c8d1bef5716a9fc199889
Fixes #6287 Change-Id: I7d428ed86cd72cd910bfff9058a52c7fcb7c64ac