Mike Bayer [Tue, 27 Sep 2022 14:23:28 +0000 (10:23 -0400)]
correct test criteria for mssql
this test was failing on mssql b.c. mssql
reports false for supports_default_metavalue. however
insertmanyvalues is still used in this case, and the
assert SQL is against the default dialect in any case.
Mike Bayer [Mon, 26 Sep 2022 12:54:59 +0000 (08:54 -0400)]
adjust tests for sqlites w/o returning
the sqlite builds on github actions seem to be very
inconsistent about versions and many don't support
RETURNING. ensure any tests that depend on RETURNING present
are marked as such.
Mike Bayer [Thu, 18 Aug 2022 17:56:50 +0000 (13:56 -0400)]
New ORM Query Guide featuring DML support
reviewers: these docs publish periodically at:
https://docs.sqlalchemy.org/en/gerrit/4042/orm/queryguide/index.html
See the "last generated" timestamp near the bottom of the
page to ensure the latest version is up
Change includes some other adjustments:
* small typing fixes for end-user benefit
* removal of a bunch of old examples for patterns that nobody
uses or aren't really what we promote now
* modernization of some examples, including inheritance
Mike Bayer [Sun, 25 Sep 2022 18:56:22 +0000 (14:56 -0400)]
warn for local-only column in remote side
A warning is emitted in ORM configurations when an explicit
:func:`_orm.remote` annotation is applied to columns that are local to the
immediate mapped class, when the referenced class does not include any of
the same table columns. Ideally this would raise an error at some point as
it's not correct from a mapping point of view.
Mike Bayer [Sun, 7 Aug 2022 16:14:19 +0000 (12:14 -0400)]
ORM bulk insert via execute
* ORM Insert now includes "bulk" mode that will run
essentially the same process as session.bulk_insert_mappings;
interprets the given list of values as ORM attributes for
key names
* ORM UPDATE has a similar feature, without RETURNING support,
for session.bulk_update_mappings
* Added support for upserts to do RETURNING ORM objects as well
* ORM UPDATE/DELETE with list of parameters + WHERE criteria
is a not implemented; use connection
* ORM UPDATE/DELETE defaults to "auto" synchronize_session;
use fetch if RETURNING is present, evaluate if not, as
"fetch" is much more efficient (no expired object SELECT problem)
and less error prone if RETURNING is available
UPDATE: howver this is inefficient! please continue to
use evaluate for simple cases, auto can move to fetch
if criteria not evaluable
* "Evaluate" criteria will now not preemptively
unexpire and SELECT attributes that were individually
expired. Instead, if evaluation of the criteria indicates that
the necessary attrs were expired, we expire the object
completely (delete) or expire the SET attrs unconditionally
(update). This keeps the object in the same unloaded state
where it will refresh those attrs on the next pass, for
this generally unusual case. (originally #5664)
* Core change! update/delete rowcount comes from len(rows)
if RETURNING was used. SQLite at least otherwise did not
support this. adjusted test_rowcount accordingly
* ORM DELETE with a list of parameters at all is also a not
implemented as this would imply "bulk", and there is no
bulk_delete_mappings (could be, but we dont have that)
* ORM insert().values() with single or multi-values translates
key names based on ORM attribute names
* ORM returning() implemented for insert, update, delete;
explcit returning clauses now interpret rows in an ORM
context, with support for qualifying loader options as well
* session.bulk_insert_mappings() assigns polymorphic identity
if not set.
* explicit RETURNING + synchronize_session='fetch' is now
supported with UPDATE and DELETE.
* expanded return_defaults() to work with DELETE also.
* added support for composite attributes to be present
in the dictionaries used by bulk_insert_mappings and
bulk_update_mappings, which is also the new ORM bulk
insert/update feature, that will expand the composite
values into their individual mapped attributes the way they'd
be on a mapped instance.
* bulk UPDATE supports "synchronize_session=evaluate", is the
default. this does not apply to session.bulk_update_mappings,
just the new version
* both bulk UPDATE and bulk INSERT, the latter with or without
RETURNING, support *heterogenous* parameter sets.
session.bulk_insert/update_mappings did this, so this feature
is maintained. now cursor result can be both horizontally
and vertically spliced :)
This is now a long story with a lot of options, which in
itself is a problem to be able to document all of this
in some way that makes sense. raising exceptions for
use cases we haven't supported is pretty important here
too, the tradition of letting unsupported things just not work
is likely not a good idea at this point, though there
are still many cases that aren't easily avoidable
Mike Bayer [Mon, 18 Jul 2022 19:08:37 +0000 (15:08 -0400)]
implement batched INSERT..VALUES () () for executemany
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.
Tighten password security by removing `URL.__str__`
For improved security, the :class:`_url.URL` object will now use password
obfuscation by default when ``str(url)`` is called. To stringify a URL with
cleartext password, the :meth:`_url.URL.render_as_string` may be used,
passing the :paramref:`_url.URL.render_as_string.hide_password` parameter
as ``False``. Thanks to our contributors for this pull request.
Mike Bayer [Fri, 23 Sep 2022 19:17:57 +0000 (15:17 -0400)]
remove should_nest behavior for contains_eager()
Fixed regression for 1.4 in :func:`_orm.contains_eager` where the "wrap in
subquery" logic of :func:`_orm.joinedload` would be inadvertently triggered
for use of the :func:`_orm.contains_eager` function with similar statements
(e.g. those that use ``distinct()``, ``limit()`` or ``offset()``). This is
not appropriate for :func:`_orm.contains_eager` which has always had the
contract that the user-defined SQL statement is unmodified with the
exception of adding the appropriate columns.
Also includes an adjustment to the assertion in Label._make_proxy()
which was there to prevent a fixed label name from being anonymized;
if the label is already anonymous, the change should proceed.
This logic was being hit before the contains_eager behavior was
adjusted. With the adjustment, this code is not used.
Mike Bayer [Fri, 23 Sep 2022 17:20:16 +0000 (13:20 -0400)]
update whatsnew/ migration
The migration20 guide had a lot of "whats new" stuff in it,
which is moved to the whatsnew20 document.
Also moved most of the content regarding the reflection rewrite
into the whatsnew20 document and added some performance comparisons
to 1.4.
The sectioning for these two documents, as has happened for some
other documents I'm working on, has been modified to only use the
double-equals operator at the top. This is strictly because my
vscode seems to be able to show me an .rst outline in the side panel
if I do it this way, and otherwise it's blank.
Mike Bayer [Tue, 20 Sep 2022 16:21:14 +0000 (12:21 -0400)]
auto-cast PG range types
Range type handling has been enhanced so that it automatically
renders type casts, so that in-place round trips for statements that don't
provide the database with any context don't require the :func:`_sql.cast`
construct to be explicit for the database to know the desired type.
Mike Bayer [Mon, 19 Sep 2022 13:40:40 +0000 (09:40 -0400)]
break out text() from TextualSelect for col matching
Fixed issue where mixing "*" with additional explicitly-named column
expressions within the columns clause of a :func:`_sql.select` construct
would cause result-column targeting to sometimes consider the label name or
other non-repeated names to be an ambiguous target.
Mike Bayer [Mon, 19 Sep 2022 16:18:47 +0000 (12:18 -0400)]
add raiseload to load_only()
currently this can't be finessed in another way,
at least not easily. The deferred() that it sets up
doesn't seem to be cancellable. in any case, this is more consistent
API with that of defer().
Mike Bayer [Sat, 17 Sep 2022 14:33:55 +0000 (10:33 -0400)]
change verbiage stating exact compliance with RFC-1738
As long as we aren't using urlparse() to parse URLs,
we are not RFC-1738 compliant. As we accept underscores
in the scheme and not dashes or dots, we are not
RFC-1738 compliant, so emulate language like
that of PostgreSQL [1] that we "generally follow" this
scheme but include some exceptions.
Mike Bayer [Sat, 17 Sep 2022 14:18:56 +0000 (10:18 -0400)]
remove obtuse section about "bundled bind parameters"
Just looking for basics on insert in the first pages
of the tutorial I see this weird detour into something that
nobody ever uses and definitely isn't going to make sense
to the people I see complaining about our docs on twitter,
remove this. the tutorial probably needs a big sweep
for wordy obtuse things. the userbase is changing and
we really have a lot of brand-new-to-programming types coming
in.
The :class:`_functions.array_agg` will now set the array dimensions to 1.
Improved :class:`_types.ARRAY` processing to accept ``None`` values as
value of a multi-array.
Mike Bayer [Thu, 15 Sep 2022 12:42:34 +0000 (08:42 -0400)]
catch exception for system_views also
Fixed yet another regression in SQL Server isolation level fetch (see
:ticket:`8231`, :ticket:`8475`), this time with "Microsoft Dynamics CRM
Database via Azure Active Directory", which apparently lacks the
``system_views`` view entirely. Error catching has been extended that under
no circumstances will this method ever fail, provided database connectivity
is present.
Mike Bayer [Tue, 13 Sep 2022 15:00:46 +0000 (11:00 -0400)]
Add type awareness to evaluator
Fixed regression where using ORM update() with synchronize_session='fetch'
would fail due to the use of evaluators that are now used to determine the
in-Python value for expressions in the the SET clause when refreshing
objects; if the evaluators make use of math operators against non-numeric
values such as PostgreSQL JSONB, the non-evaluable condition would fail to
be detected correctly. The evaluator now limits the use of math mutation
operators to numeric types only, with the exception of "+" that continues
to work for strings as well. SQLAlchemy 2.0 may alter this further by
fetching the SET values completely rather than using evaluation.
Mike Bayer [Thu, 8 Sep 2022 17:19:08 +0000 (13:19 -0400)]
additional de-stringify pass for unions
the change in c3cfee5b00a40790c18d took out
a pass for de-stringify that broke some un-tested cases
for Optional with future annotations mode. Adding tests
for this revealed that this was a subset of
a more general case where Union is presented
with ForwardRefs inside of it matching up within the type
map, which wasn't working before either, fixed that as well with
an additional de-stringify for elements within the Union.
Added long-requested case-insensitive string operators
:meth:`_sql.ColumnOperators.icontains`,
:meth:`_sql.ColumnOperators.istartswith`,
:meth:`_sql.ColumnOperators.iendswith`, which produce case-insensitive
LIKE compositions (using ILIKE on PostgreSQL, and the LOWER() function on
all other backends) to complement the existing LIKE composition operators
:meth:`_sql.ColumnOperators.contains`,
:meth:`_sql.ColumnOperators.startswith`, etc. Huge thanks to Matias
Martinez Rebori for their meticulous and complete efforts in implementing
these new methods.
Mike Bayer [Wed, 7 Sep 2022 21:13:23 +0000 (17:13 -0400)]
enable UPDATE..FROM for SQLite
The SQLite dialect now supports UPDATE..FROM syntax, for UPDATE statements
that may refer to additional tables within the WHERE criteria of the
statement without the need to use subqueries. This syntax is invoked
automatically when using the :class:`_dml.Update` construct when more than
one table or other entity or selectable is used.
Ensure that all cython extension are imported by the compied detection logic.
This is required since cython extensions moduels are marked as optional
in the install, so it's possible that only some of them are compiled.
The extensions are enabled only if all of them are correctly compiled
Fixed regression caused by the fix for :ticket:`8231` released in 1.4.40
where connection would fail if the user does not have permission to query
the dm_exec_sessions or dm_pdw_nodes_exec_sessions system view when trying
to determine the current transaction isolation level.
Peter Schutt [Thu, 1 Sep 2022 23:11:40 +0000 (19:11 -0400)]
Detection of PEP 604 union syntax.
### Description
Fixes #8478
Handle `UnionType` as arguments to `Mapped`, e.g., `Mapped[str | None]`:
- adds `utils.typing.is_optional_union()` used to detect if a column should be nullable.
- adds `"UnionType"` to `utils.typing.is_optional()` names.
- uses `get_origin()` in `utils.typing.is_origin_of()` as `UnionType` has no `__origin__` attribute.
- tests with runtime type and postponed annotations and guard the tests running with `compat.py310`.
### Checklist
<!-- go over following points. check them with an `x` if they do apply, (they turn into clickable checkboxes once the PR is submitted, so no need to do everything at once)
-->
This pull request is:
- [ ] A documentation / typographical error fix
- Good to go, no issue or tests are needed
- [x] A short code fix
- please include the issue number, and create an issue if none exists, which
must include a complete example of the issue. one line code fixes without an
issue and demonstration will not be accepted.
- Please include: `Fixes: #<issue number>` in the commit message
- please include tests. one line code fixes without tests will not be accepted.
- [ ] A new feature implementation
- please include the issue number, and create an issue if none exists, which must
include a complete example of how the feature would look.
- Please include: `Fixes: #<issue number>` in the commit message
- please include tests.
Mike Bayer [Wed, 31 Aug 2022 15:07:23 +0000 (11:07 -0400)]
run update_subclass anytime we add new clslevel dispatch
Fixed event listening issue where event listeners added to a superclass
would be lost if a subclass were created which then had its own listeners
associated. The practical example is that of the :class:`.sessionmaker`
class created after events have been associated with the
:class:`_orm.Session` class.
Mike Bayer [Tue, 30 Aug 2022 14:47:24 +0000 (10:47 -0400)]
include TableClause.schema in cache key
Fixed issue where use of the :func:`_sql.table` construct, passing a string
for the :paramref:`_sql.table.schema` parameter, would fail to take the
"schema" string into account when producing a cache key, thus leading to
caching collisions if multiple, same-named :func:`_sql.table` constructs
with different schemas were used.
Mike Bayer [Tue, 30 Aug 2022 14:25:47 +0000 (10:25 -0400)]
implement event for merge/load=False for mutable state setup
Fixed issue in :mod:`sqlalchemy.ext.mutable` extension where collection
links to the parent object would be lost if the object were merged with
:meth:`.Session.merge` while also passing :paramref:`.Session.merge.load`
as False.
The event added here is currently private for expediency, but
is acceptable to become a public event at some point.
Mike Bayer [Tue, 30 Aug 2022 13:50:03 +0000 (09:50 -0400)]
apply consistent ORM mutable notes for all mutable SQL types
in
https://github.com/sqlalchemy/sqlalchemy/discussions/8447
I was surprised that we didnt have any notes about using Mutable
for ARRAY classes, since we have them for HSTORE and JSON.
Add a consistent topic box for these so we have something to
point towards.
Mike Bayer [Mon, 29 Aug 2022 14:43:36 +0000 (10:43 -0400)]
refine ruleset to determine when poly adaption should be used
Fixed regression appearing in the 1.4 series where a joined-inheritance
query placed as a subquery within an enclosing query for that same entity
would fail to render the JOIN correctly for the inner query. The issue
manifested in two different ways prior and subsequent to version 1.4.18
(related issue #6595), in one case rendering JOIN twice, in the other
losing the JOIN entirely. To resolve, the conditions under which
"polymorphic loading" are applied have been scaled back to not be invoked
for simple joined inheritance queries.
Trevor Gross [Wed, 24 Aug 2022 10:32:31 +0000 (06:32 -0400)]
Use cibuildwheel to create wheels
Using cibuildwheel the following wheels are created:
- windows x64 and x84
- macos x64 and arm
- linux x64 and arm on manylinux and mosulinux (for alpine)
- create a pure python wheel (for pypy and other archs)
Mike Bayer [Wed, 24 Aug 2022 17:06:09 +0000 (13:06 -0400)]
fix mock issue in pool test
Due to the change for #5648 in ad14471bc99c2fb2315d3333a95dd,
the mock in test_recycle_pool_no_race needs to implement
an empty handle_error dispatcher.
the exception was in a thread so did not interrupt the
test suite:
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 2059, in _handle_dbapi_exception
for fn in self.dialect.dispatch.handle_error:
TypeError: 'Mock' object is not iterable
Mike Bayer [Tue, 23 Aug 2022 13:28:06 +0000 (09:28 -0400)]
integrate connection.terminate() for supporting dialects
Integrated support for asyncpg's ``terminate()`` method call for cases
where the connection pool is recycling a possibly timed-out connection,
where a connection is being garbage collected that wasn't gracefully
closed, as well as when the connection has been invalidated. This allows
asyncpg to abandon the connection without waiting for a response that may
incur long timeouts.
Mike Bayer [Mon, 22 Aug 2022 17:43:59 +0000 (13:43 -0400)]
reformat functions.rst; document coalsce
this file was all over the place autodocumenting all the
contents of functions.py with no regards to the heading
paragraph which seemed to be introducing the generic functions.
Use specific autoclass/autofunc docs as automodule is generally
unworkable.
Mike Bayer [Sun, 21 Aug 2022 15:58:20 +0000 (11:58 -0400)]
Column._copy() duplicates "user defined" nullable state exactly
To accommodate how mapped_column() works, after many
attempts to get this working it became clear that _copy()
should just transfer "nullable" state exactly as it was,
including the state where .nullable was set but user_defined_nullable
remains at not user set.
additionally, added a similar step to _merge() that was needed
to preserve the nullability behavior when Identity is present.
server / client default objects are not copied within column._copy()
and this should be fixed.
Mike Bayer [Tue, 16 Aug 2022 14:44:48 +0000 (10:44 -0400)]
support create/drop events for all CREATE/DROP
Implemented the DDL event hooks :meth:`.DDLEvents.before_create`,
:meth:`.DDLEvents.after_create`, :meth:`.DDLEvents.before_drop`,
:meth:`.DDLEvents.after_drop` for all :class:`.SchemaItem` objects that
include a distinct CREATE or DROP step, when that step is invoked as a
distinct SQL statement, including for :class:`.ForeignKeyConstraint`,
:class:`.Sequence`, :class:`.Index`, and PostgreSQL's
:class:`_postgresql.ENUM`.
Mike Bayer [Fri, 19 Aug 2022 15:12:41 +0000 (11:12 -0400)]
remove narrative "reconstructor" document
this event hook is not commonly used and this page does not
fit into the current narrative very well. We should possibly
write a new paragraph regarding how instances load
at some point though the best place to put it is not clear.
projects / thirdparty / sqlalchemy / sqlalchemy.git / log
