summaryrefslogtreecommitdiff
path: root/lib/sqlalchemy/sql
Commit message (Collapse)AuthorAgeFilesLines
...
* | Add Non linear CTE supportEric Masseran2021-11-161-29/+155
|/ | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | "Compound select" methods like :meth:`_sql.Select.union`, :meth:`_sql.Select.intersect_all` etc. now accept ``*other`` as an argument rather than ``other`` to allow for multiple additional SELECTs to be compounded with the parent statement at once. In particular, the change as applied to :meth:`_sql.CTE.union` and :meth:`_sql.CTE.union_all` now allow for a so-called "non-linear CTE" to be created with the :class:`_sql.CTE` construct, whereas previously there was no way to have more than two CTE sub-elements in a UNION together while still correctly calling upon the CTE in recursive fashion. Pull request courtesy Eric Masseran. Allow: ```sql WITH RECURSIVE nodes(x) AS ( SELECT 59 UNION SELECT aa FROM edge JOIN nodes ON bb=x UNION SELECT bb FROM edge JOIN nodes ON aa=x ) SELECT x FROM nodes; ``` Based on @zzzeek suggestion: https://github.com/sqlalchemy/sqlalchemy/pull/7133#issuecomment-933882348 Fixes: #7259 Closes: #7260 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7260 Pull-request-sha: 2565a5fd4b1940e92125e53aeaa731cc682f49bb Change-Id: I685c8379762b5fb6ab4107ff8f4d8a4de70c0ca6
* Merge "removals: all unicode encoding / decoding" into mainmike bayer2021-11-112-282/+19
|\
| * removals: all unicode encoding / decodingMike Bayer2021-11-102-282/+19
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Removed here includes: * convert_unicode parameters * encoding create_engine() parameter * description encoding support * "non-unicode fallback" modes under Python 2 * String symbols regarding Python 2 non-unicode fallbacks * any concept of DBAPIs that don't accept unicode statements, unicode bound parameters, or that return bytes for strings anywhere except an explicit Binary / BLOB type * unicode processors in Python / C Risk factors: * Whether all DBAPIs do in fact return Unicode objects for all entries in cursor.description now * There was logic for mysql-connector trying to determine description encoding. A quick test shows Unicode coming back but it's not clear if there are still edge cases where they return bytes. if so, these are bugs in that driver, and at most we would only work around it in the mysql-connector DBAPI itself (but we won't do that either). * It seems like Oracle 8 was not expecting unicode bound parameters. I'm assuming this was all Python 2 stuff and does not apply for modern cx_Oracle under Python 3. * third party dialects relying upon built in unicode encoding/decoding but it's hard to imagine any non-SQLAlchemy database driver not dealing exclusively in Python unicode strings in Python 3 Change-Id: I97d762ef6d4dd836487b714d57d8136d0310f28a References: #7257
* | Merge "set within_columns_clause=False for all sub-elements of select()" ↵mike bayer2021-11-091-0/+3
|\ \ | | | | | | | | | into main
| * | set within_columns_clause=False for all sub-elements of select()Mike Bayer2021-11-091-0/+3
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Fixed issue where using the feature of using a string label for ordering or grouping described at :ref:`tutorial_order_by_label` would fail to function correctly if used on a :class:`.CTE` construct, when the CTE were embedded inside of an enclosing :class:`_sql.Select` statement that itself was set up as a scalar subquery. Fixes: #7269 Change-Id: Ied6048a1c9a622374a418230c8cfedafa8d3f87e
* | | Merge "change the POSTCOMPILE/ SCHEMA symbols to not conflict w mssql ↵mike bayer2021-11-091-6/+6
|\ \ \ | |_|/ |/| | | | | quoting" into main
| * | change the POSTCOMPILE/ SCHEMA symbols to not conflict w mssql quotingMike Bayer2021-11-091-6/+6
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Adjusted the compiler's generation of "post compile" symbols including those used for "expanding IN" as well as for the "schema translate map" to not be based directly on plain bracketed strings with underscores, as this conflicts directly with SQL Server's quoting format of also using brackets, which produces false matches when the compiler replaces "post compile" and "schema translate" symbols. The issue created easy to reproduce examples both with the :meth:`.Inspector.get_schema_names` method when used in conjunction with the :paramref:`_engine.Connection.execution_options.schema_translate_map` feature, as well in the unlikely case that a symbol overlapping with the internal name "POSTCOMPILE" would be used with a feature like "expanding in". Fixes: #7300 Change-Id: I6255c850b140522a4aba95085216d0bca18ce230
* | | Merge "Fixes: #7295" into mainmike bayer2021-11-091-8/+8
|\ \ \ | |_|/ |/| |
| * | Fixes: #7295jonathan vanasco2021-11-061-8/+8
| | | | | | | | | | | | | | | | | | Fixed issue in ``Table``` object where: param:`implicit_returning` was not compatible with: param:`extend_existing`. Change-Id: I16f4ab585d82f5691a3fed9eba04b84730a8a59e
* | | revert mis-commit from aa026c302c6b188a7e28508f9ecb603809b9e03fMike Bayer2021-11-091-2/+0
| | | | | | | | | | | | | | | | | | | | | A scratch line from #7269 was inadvertently committed here. this needs to be in its own commit w/ tests. Change-Id: I62796e18b05bbbd3b6225e9f27e1e63ab98cf24c
* | | De-emphasize notion of "default driver" (DBAPI)Gord Thompson2021-11-091-1/+1
| |/ |/| | | | | | | | | | | | | | | | | | | Fixes: #6960 Even though a default driver still exists for each dialect, remove most usages of `dialect://` to encourage users to explicitly specify `dialect+driver://` Change-Id: I0ad42167582df509138fca64996bbb53e379b1af
* | Merge "fully implement future engine and remove legacy" into mainmike bayer2021-11-0710-150/+107
|\ \
| * | fully implement future engine and remove legacyMike Bayer2021-11-0710-150/+107
| |/ | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 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
* | use tuple expansion if type._is_tuple, test for Sequence if no typeMike Bayer2021-11-052-7/+19
|/ | | | | | | | | | | | | | | | | | | Fixed regression where the row objects returned for ORM queries, which are now the normal :class:`_sql.Row` objects, would not be interpreted by the :meth:`_sql.ColumnOperators.in_` operator as tuple values to be broken out into individual bound parameters, and would instead pass them as single values to the driver leading to failures. The change to the "expanding IN" system now accommodates for the expression already being of type :class:`.TupleType` and treats values accordingly if so. In the uncommon case of using "tuple-in" with an untyped statement such as a textual statement with no typing information, a tuple value is detected for values that implement ``collections.abc.Sequence``, but that are not ``str`` or ``bytes``, as always when testing for ``Sequence``. Added :class:`.TupleType` to the top level ``sqlalchemy`` import namespace. Fixes: #7292 Change-Id: I8286387e3b3c3752b3bd4ae3560d4f31172acc22
* use ExpressionElementRole for case targets in case()Mike Bayer2021-11-031-22/+12
| | | | | | | | | | | | | | Fixed regression where the :func:`_sql.text` construct would no longer be accepted as a target case in the "whens" list within a :func:`_sql.case` construct. The regression appears related to an attempt to guard against some forms of literal values that were considered to be ambiguous when passed here; however, there's no reason the target cases shouldn't be interpreted as open-ended SQL expressions just like anywhere else, and a literal string or tuple will be converted to a bound parameter as would be the case elsewhere. Fixes: #7287 Change-Id: I75478adfa115f3292cb1362cc5b2fdf152b0ed6f
* add missing info from groupby documentationFederico Caselli2021-11-031-0/+2
| | | | | Change-Id: Icfaf242353c23a579fe79f9d72500a08d90fcb77 Signed-off-by: Federico Caselli <cfederico87@gmail.com>
* First round of removal of python 2Federico Caselli2021-11-015-65/+10
| | | | | References: #4600 Change-Id: I61e35bc93fe95610ae75b31c18a3282558cd4ffe
* Revise "literal parameters" FAQ sectionMike Bayer2021-11-011-1/+23
| | | | | | | | | | based on feedback in #7271, the emphasis on TypeDecorator as a solution to this problem is not very practical. illustrate a series of quick recipes that are useful for debugging purposes to print out a repr() or simple stringify of a parameter without the need to construct custom dialects or types. Change-Id: I788ce1b5ea01d88dd0a22d03d06f35aabff5e5c8
* Merge "2.0 removals: LegacyRow, connectionless execution, close_with_result" ↵mike bayer2021-10-313-53/+2
|\ | | | | | | into main
| * 2.0 removals: LegacyRow, connectionless execution, close_with_resultMike Bayer2021-10-313-53/+2
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | in order to remove LegacyRow / LegacyResult, we have to also lose close_with_result, which connectionless execution relies upon. also includes a new profiles.txt file that's all against py310, as that's what CI is on now. some result counts changed by one function call which was enough to fail the low-count result tests. Replaces Connectable as the common interface between Connection and Engine with EngineEventsTarget. Engine is no longer Connectable. Connection and MockConnection still are. References: #7257 Change-Id: Iad5eba0313836d347e65490349a22b061356896a
* | Remove deprecated dialects and driversFederico Caselli2021-10-311-1/+1
|/ | | | | Fixes: #7258 Change-Id: I3577f665eca04f2632b69bcb090f0a4ec9271db9
* clarify order_by(None) for Core; improve wordingMike Bayer2021-10-291-1/+8
| | | | | | | the order_by(None) convention was documented for orm.Query but not Core select. Change-Id: I0c1ad76c3eefba1cb54b1649cfd09169c17e2bba
* Improve array support on pg8000Federico Caselli2021-10-271-1/+2
| | | | | | References: #6023 Change-Id: I0f6cbc34b3c0bfc0b8c86b3ebe4531e23039b6c0
* add full docs/notes for all TypeEngine/TypeDecorator interactionsMike Bayer2021-10-231-62/+207
| | | | | | | | | | | Added notes to all TypeEngine methods as to which should be overridden by TypeDecorator implementations. Made sure docstrings are present for all TypeDecorator methods as some were missing, and clarified which should be overridden, which should not, as well as what phase each method is called within. Change-Id: Ibd6e7fb0933ad3ca0927d8785d9087e3eddac90e References: #7230
* Merge "Add missing deprecation to ``MetaData.bind`` argument" into mainmike bayer2021-10-201-0/+6
|\
| * Add missing deprecation to ``MetaData.bind`` argumentFederico Caselli2021-10-181-0/+6
| | | | | | | | | | Fixes: #7194 Change-Id: I9dacbb562aa55d9c408a43f8e57050db31dc8bfc
* | remove _resolve_label and related attributesMike Bayer2021-10-202-20/+7
| | | | | | | | | | | | | | | | | | | | | | | | | | these seem to date back to 0.9 and revs like #3148 but none of it seems to be affecting things now, try removing it all and seeing what fails. we've noted that _resolve_label does not appear to be needed, however allow_label_resolve remains relevant both for non-column elements like text() as well as that it is used explicitly by the joined eager loader. Change-Id: Ic8a5d8001ef2a4133360f51a92a6f7b0cc389095
* | process bulk_update_tuples before cache key or compilationMike Bayer2021-10-193-42/+39
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Fixed regression where the use of a :class:`_orm.hybrid_property` attribute or a mapped :func:`_orm.composite` attribute as a key passed to the :meth:`_dml.Update.values` method for an ORM-enabled :class:`_dml.Update` statement, as well as when using it via the legacy :meth:`_orm.Query.update` method, would be processed for incoming ORM/hybrid/composite values within the compilation stage of the UPDATE statement, which meant that in those cases where caching occurred, subsequent invocations of the same statement would no longer receive the correct values. This would include not only hybrids that use the :meth:`_orm.hybrid_property.update_expression` method, but any use of a plain hybrid attribute as well. For composites, the issue instead caused a non-repeatable cache key to be generated, which would break caching and could fill up the statement cache with repeated statements. The :class:`_dml.Update` construct now handles the processing of key/value pairs passed to :meth:`_dml.Update.values` and :meth:`_dml.Update.ordered_values` up front when the construct is first generated, before the cache key has been generated so that the key/value pairs are processed each time, and so that the cache key is generated against the individual column/value pairs that will ultimately be used in the statement. Fixes: #7209 Change-Id: I08f248d1d60ea9690b014c21439b775d951fb9e5
* | use coercions for label element, ensure propagate_attrsMike Bayer2021-10-181-3/+15
|/ | | | | | | | | | Fixed bug where the ORM "plugin", necessary for features such as :func:`_orm.with_loader_criteria` to work correctly, would not be applied to a :func:`_sql.select` which queried from an ORM column expression if it made use of the :meth:`_sql.ColumnElement.label` modifier. Fixes: #7205 Change-Id: I72b84442e14df8b5ece33916f3c51ca3f358864b
* Add missing note: bind arg will be required in 2.0 (#7195)Sergey Golitsynskiy2021-10-161-0/+6
| | | Applies to Index.create() and Index.drop()
* support bind expressions w/ expanding IN; apply to psycopg2Mike Bayer2021-10-151-30/+76
| | | | | | | | | | | | | | | | | | | | | | | Fixed issue where "expanding IN" would fail to function correctly with datatypes that use the :meth:`_types.TypeEngine.bind_expression` method, where the method would need to be applied to each element of the IN expression rather than the overall IN expression itself. Fixed issue where IN expressions against a series of array elements, as can be done with PostgreSQL, would fail to function correctly due to multiple issues within the "expanding IN" feature of SQLAlchemy Core that was standardized in version 1.4. The psycopg2 dialect now makes use of the :meth:`_types.TypeEngine.bind_expression` method with :class:`_types.ARRAY` to portably apply the correct casts to elements. The asyncpg dialect was not affected by this issue as it applies bind-level casts at the driver level rather than at the compiler level. as part of this commit the "bind translate" feature has been simplified and also applies to the names in the POSTCOMPILE tag to accommodate for brackets. Fixes: #7177 Change-Id: I08c703adb0a9bd6f5aeee5de3ff6f03cccdccdc5
* Merge "Pickling fixes for ORM / Core" into mainmike bayer2021-10-131-0/+3
|\
| * Pickling fixes for ORM / CoreMike Bayer2021-10-131-0/+3
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Fixed regression where ORM loaded objects could not be pickled in cases where loader options making use of ``"*"`` were used in certain combinations, such as combining the :func:`_orm.joinedload` loader strategy with ``raiseload('*')`` of sub-elements. Fixes: #7134 Fixed issue where SQL queries using the :meth:`_functions.FunctionElement.within_group` construct could not be pickled, typically when using the ``sqlalchemy.ext.serializer`` extension but also for general generic pickling. Fixes: #6520 Change-Id: Ib73fd49c875e6da9898493c190f610e68b88ec72
* | Fix recursive CTE to support nestingEric Masseran2021-10-122-38/+72
|/ | | | | | | | | | | | | | | | | | | | | | | | | | Repaired issue in new :paramref:`_sql.HasCTE.cte.nesting` parameter introduced with :ticket:`4123` where a recursive :class:`_sql.CTE` using :paramref:`_sql.HasCTE.cte.recursive` in typical conjunction with UNION would not compile correctly. Additionally makes some adjustments so that the :class:`_sql.CTE` construct creates a correct cache key. Pull request courtesy Eric Masseran. Fixes: #4123 > This has not been caught by the tests because the nesting recursive queries there did not union against itself, eg there was only the i root clause... - Now tests are real recursive queries - Add tests on aliased nested CTEs (recursive or not) - Adapt the `_restates` attribute to use it as a reference - Add some docs around to explain some variables usage Closes: #7133 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7133 Pull-request-sha: 2633f34f7f5336a4a85bd3f71d07bca33ce27a2c Change-Id: I15512c94e1bc1f52afc619d82057ca647d274e92
* fixes for usage of the null() and similar constantsMike Bayer2021-10-084-29/+98
| | | | | | | | | | | | | | | | | | | | | | | | Adjusted the "column disambiguation" logic that's new in 1.4, where the same expression repeated gets an "extra anonymous" label, so that the logic more aggressively deduplicates those labels when the repeated element is the same Python expression object each time, as occurs in cases like when using "singleton" values like :func:`_sql.null`. This is based on the observation that at least some databases (e.g. MySQL, but not SQLite) will raise an error if the same label is repeated inside of a subquery. Related to :ticket:`7153`, fixed an issue where result column lookups would fail for "adapted" SELECT statements that selected for "constant" value expressions most typically the NULL expression, as would occur in such places as joined eager loading in conjunction with limit/offset. This was overall a regression due to issue :ticket:`6259` which removed all "adaption" for constants like NULL, "true", and "false", but this broke the case where the same adaption logic were used to match the constant to a labeled expression referring to the constant in a subquery. Fixes: #7153 Fixes: #7154 Change-Id: I43823343721b9e70524ea3f5e8f39dd543a3e92b
* Merge "Warn when trying to execute a query object with a session."mike bayer2021-10-041-1/+22
|\
| * Warn when trying to execute a query object with a session.Federico Caselli2021-10-041-1/+22
| | | | | | | | | | | | | | | | Passing a :class:`.Query` object to :meth:`_orm.Session.execute` is not the intended use of this object, and will now raise a deprecation warning. Fixes: #6284 Change-Id: I30a406d5a72335f9405f10f0a2030a32ccda41b9
* | repair any_() / all_() "implicit flip" behavior for NoneMike Bayer2021-10-044-59/+107
|/ | | | | | | | | | | | | | Fixed an inconsistency in the any_() / all_() functions / methods where the special behavior these functions have of "flipping" the expression such that the "ANY" / "ALL" expression is always on the right side would not function if the comparison were against the None value, that is, "column.any_() == None" should produce the same SQL expression as "null() == column.any_()". Added more docs to clarify this as well, plus mentions that any_() / all_() generally supersede the ARRAY version "any()" / "all()". Fixes: #7140 Change-Id: Ia5d55414ba40eb3fbda3598931fdd24c9b4a4411
* Merge "Account for `schema` in `table()` `fullname` attribute."mike bayer2021-10-011-1/+5
|\
| * Account for `schema` in `table()` `fullname` attribute.Federico Caselli2021-10-011-1/+5
| | | | | | | | | | Fixes: #7061 Change-Id: I715da89591c03d40d77734473bd42b34b9c4e1dc
* | Merge "Fixes: #4504"mike bayer2021-10-011-0/+30
|\ \ | |/ |/|
| * Fixes: #4504jonathan vanasco2021-09-291-0/+30
| | | | | | | | | | | | Docstring for EXTRACT Change-Id: Ie529ceb3551adedf0df873c22e65f8615cd1c9ef
* | Merge "warn or deprecate for auto-aliasing in joins"mike bayer2021-09-281-1/+4
|\ \
| * | warn or deprecate for auto-aliasing in joinsMike Bayer2021-09-281-1/+4
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | An extra layer of warning messages has been added to the functionality of :meth:`_orm.Query.join` and the ORM version of :meth:`_sql.Select.join`, where a few places where "automatic aliasing" continues to occur will now be called out as a pattern to avoid, mostly specific to the area of joined table inheritance where classes that share common base tables are being joined together without using explicit aliases. One case emits a legacy warning for a pattern that's not recommended, the other case is fully deprecated. The automatic aliasing within ORM join() which occurs for overlapping mapped tables does not work consistently with all APIs such as ``contains_eager()``, and rather than continue to try to make these use cases work everywhere, replacing with a more user-explicit pattern is clearer, less prone to bugs and simplifies SQLAlchemy's internals further. The warnings include links to the errors.rst page where each pattern is demonstrated along with the recommended pattern to fix. * Improved the exception message generated when configuring a mapping with joined table inheritance where the two tables either have no foreign key relationships set up, or where they have multiple foreign key relationships set up. The message is now ORM specific and includes context that the :paramref:`_orm.Mapper.inherit_condition` parameter may be needed particularly for the ambiguous foreign keys case. * Add explicit support in the _expect_warnings() assertion for nested _expect_warnings calls * generalize the NoCache fixture, which we also need to catch warnings during compilation consistently * generalize the __str__() method for the HasCode mixin so all warnings and errors include the code link in their string Fixes: #6974 Change-Id: I84ed79ba2112c39eaab7973b6d6f46de7fa80842
* | | Fixes: #5151jonathan vanasco2021-09-271-1/+6
| |/ |/| | | | | | | | | document `listen=True` on `before_create` Change-Id: I0804ee073044d879eb0decafeb1b358741d69f02
* | Fix typo introduced in #3086/I44c1a021a3e7ab7d66fea2d79a36cb2195a1969djonathan vanasco2021-09-271-1/+1
|/ | | | | | Thank you @lelit Change-Id: I98e8e0fca25d6de6c7fa6c8c0ee429e80b14c102
* Fixes: #3086jonathan vanasco2021-09-231-0/+27
| | | | | | show that `server_defaults` can accept contextually valid SQLAlchemy expressions or constructs Change-Id: I44c1a021a3e7ab7d66fea2d79a36cb2195a1969d
* Merge "coerce for multivalues keys"mike bayer2021-09-222-7/+26
|\
| * coerce for multivalues keysMike Bayer2021-09-212-7/+26
| | | | | | | | | | | | | | | | | | Fixed issue where using ORM column expressions as keys in the list of dictionaries passed to :meth:`_sql.Insert.values` for "multi-valued insert" would not be processed correctly into the correct column expressions. Fixes: #7060 Change-Id: I1c4c286c33ea6eeaafba617996828f5c88ff0a1c
* | Merge "implement _all_selected_columns for functionelement"mike bayer2021-09-221-1/+12
|\ \
View Lorry Controller Status