Daniel Black [Tue, 28 Sep 2021 18:20:06 +0000 (14:20 -0400)]
Generalize RETURNING and suppor for MariaDB / SQLite
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.
Mike Bayer [Fri, 11 Feb 2022 09:54:45 +0000 (04:54 -0500)]
add backend agnostic UUID datatype
Added new backend-agnostic :class:`_types.Uuid` datatype generalized from
the PostgreSQL dialects to now be a core type, as well as migrated
:class:`_types.UUID` from the PostgreSQL dialect. Thanks to Trevor Gross
for the help on this.
also includes:
* corrects some missing behaviors in the suite literal fixtures
test where row round trips weren't being correctly asserted.
* fixes some of the ISO literal date rendering added in 952383f9ee0 for #5052 to truncate datetime strings for date/time
datatypes in the same way that drivers typically do for bound
parameters; this was not working fully and wasn't caught by the
broken test fixture
Patrick Gerken [Tue, 31 May 2022 19:42:35 +0000 (21:42 +0200)]
Update declarative_styles.rst: Update code example to attrs TNG usage. (#8072)
* Update declarative_styles.rst
Update docs to new style usage of attrs.
This is the default since December 2021.
While the old style still works, the newer one looks much nicer and is likely to be dominant pretty quickly. Imho.
Mike Bayer [Thu, 26 May 2022 18:35:03 +0000 (14:35 -0400)]
establish sessionmaker and async_sessionmaker as generic
This is so that custom Session and AsyncSession classes
can be typed for these factories. Added appropriate
typevars to `__call__()`, `__enter__()` and other methods
so that a custom Session or AsyncSession subclass is carried
through.
Mike Bayer [Mon, 30 May 2022 16:29:58 +0000 (12:29 -0400)]
Support handle_error for pre_ping
The :meth:`.DialectEvents.handle_error` event is now moved to the
:class:`.DialectEvents` suite from the :class:`.EngineEvents` suite, and
now participates in the connection pool "pre ping" event for those dialects
that make use of disconnect codes in order to detect if the database is
live. This allows end-user code to alter the state of "pre ping". Note that
this does not include dialects which contain a native "ping" method such as
that of psycopg2 or most MySQL dialects.
Mike Bayer [Tue, 31 May 2022 14:48:16 +0000 (10:48 -0400)]
raise informative error when selectable can't be extended
An informative error is raised for the use case where
:meth:`.Insert.from_select` is being passed a "compound select" object such
as a UNION, yet the INSERT statement needs to append additional columns to
support Python-side or explicit SQL defaults from the table metadata. In
this case a subquery of the compound object should be passed.
Mike Bayer [Fri, 27 May 2022 20:07:01 +0000 (16:07 -0400)]
remove "deannotate" from column_property expression
Fixed issue where using a :func:`_orm.column_property` construct containing
a subquery against an already-mapped column attribute would not correctly
apply ORM-compilation behaviors to the subquery, including that the "IN"
expression added for a single-table inherits expression would fail to be
included.
This fix involves a few tweaks in the ORM adaptation logic,
including a missing "parententity" adaptation on the mapper
side. The specific mechanics here have a lot of moving parts
so we will continue to add tests to assert these cases. In
particular a more complete test for issue #2316 is added
that was relying upon the deannotate happening here.
Mike Bayer [Sun, 29 May 2022 16:07:46 +0000 (12:07 -0400)]
move bindparam quote application from compiler to default
in 296c84313ab29bf9599634f3 for #5653 we generalized Oracle's
parameter escaping feature into the compiler, so that it could also
work for PostgreSQL. The compiler used quoted names within parameter
dictionaries, which then led to the complexity that all functions
which interpreted keys from the compiled_params dict had to
also quote the param names to use the dictionary. This
extra complexity was not added to the ORM peristence.py however,
which led to the versioning id feature being broken as well as
other areas where persistence.py relies on naming schemes present
in context.compiled_params. It also was not added to the
"processors" lookup which led to #8053, that added this escaping
to that part of the compiler.
To both solve the whole problem as well as simplify the compiler
quite a bit, move the actual application of the escaped names
to be as late as possible, when default.py builds the final list
of parameters. This is more similar to how it worked previously
where OracleExecutionContext would be late-applying these
escaped names. This re-establishes context.compiled_params as
deterministically named regardless of dialect in use and moves
out the complexity of the quoted param names to be only at the
cursor.execute stage.
Fixed bug, likely a regression from 1.3, where usage of column names that
require bound parameter escaping, more concretely when using Oracle with
column names that require quoting such as those that start with an
underscore, or in less common cases with some PostgreSQL drivers when using
column names that contain percent signs, would cause the ORM versioning
feature to not work correctly if the versioning column itself had such a
name, as the ORM assumes certain bound parameter naming conventions that
were being interfered with via the quotes. This issue is related to
:ticket:`8053` and essentially revises the approach towards fixing this,
revising the original issue :ticket:`5653` that created the initial
implementation for generalized bound-parameter name quoting.
Mike Bayer [Wed, 25 May 2022 12:47:29 +0000 (08:47 -0400)]
apply bindparam escape name to processors dictionary
Fixed SQL compiler issue where the "bind processing" function for a bound
parameter would not be correctly applied to a bound value if the bound
parameter's name were "escaped". Concretely, this applies, among other
cases, to Oracle when a :class:`.Column` has a name that itself requires
quoting, such that the quoting-required name is then used for the bound
parameters generated within DML statements, and the datatype in use
requires bind processing, such as the :class:`.Enum` datatype.
Andrew Brookins [Sun, 22 May 2022 20:24:15 +0000 (16:24 -0400)]
Add a note on using server_onupdate=FetchedValue()
Add a note on using `server_onupdate=FetchedValue()` when using SQL expressions with `onupdate`.
My team encountered an issue with using a SQL expression with `onupdate`.
Despite the dialect (PG) supporting `RETURNING`, we needed to mark the column with
`server_onupdate=FetchedValue()` in order to get the column used with `onupdate`
to appear in the `RETURNING` clause of `UPDATE` statements.
This was not clear from the documentation, so I want to make it crystal clear for other
folks defining similar columns.
Mike Bayer [Wed, 18 May 2022 20:06:29 +0000 (16:06 -0400)]
use plainto_tsquery for PG match
The :meth:`.Operators.match` operator now uses ``plainto_tsquery()`` for
PostgreSQL full text search, rather than ``to_tsquery()``. The rationale
for this change is to provide better cross-compatibility with match on
other database backends. Full support for all PostgreSQL full text
functions remains available through the use of :data:`.func` in
conjunction with :meth:`.Operators.bool_op` (an improved version of
:meth:`.Operators.op` for boolean operators).
Additional doc updates here apply to 1.4 so will backport these
out to a separate commit.
Mike Bayer [Fri, 20 May 2022 19:56:54 +0000 (15:56 -0400)]
render select froms first
The FROM clauses that are established on a :func:`_sql.select` construct
when using the :meth:`_sql.Select.select_from` method will now render first
in the FROM clause of the rendered SELECT, which serves to maintain the
ordering of clauses as was passed to the :meth:`_sql.Select.select_from`
method itself without being affected by the presence of those clauses also
being mentioned in other parts of the query. If other elements of the
:class:`_sql.Select` also generate FROM clauses, such as the columns clause
or WHERE clause, these will render after the clauses delivered by
:meth:`_sql.Select.select_from` assuming they were not explictly passed to
:meth:`_sql.Select.select_from` also. This improvement is useful in those
cases where a particular database generates a desirable query plan based on
a particular ordering of FROM clauses and allows full control over the
ordering of FROM clauses.
Mike Bayer [Sun, 22 May 2022 14:32:02 +0000 (10:32 -0400)]
disable psycopg-c from dbapimain builds
I don't know what this means.
ERROR:
Command errored out with exit status 1:
command: /home/classic/tmp/foo/.venv/bin/python /home/classic/tmp/foo/.venv/lib/python3.10/site-packages/pip/_vendor/pep517/in_process/_in_process.py get_requires_for_build_wheel /tmp/tmp1x3hi09h
cwd: /tmp/pip-install-yo6y28dm/psycopg-c_18eb2a0429dc438493454ed1051296ad
Complete output (14 lines):
error: Multiple top-level packages discovered in a flat-layout: ['psycopg', 'psycopg_c', 'psycopg_pool'].
To avoid accidental inclusion of unwanted files or directories,
setuptools will not proceed with this build.
If you are trying to create a single distribution with multiple packages
on purpose, you should not rely on automatic discovery.
Instead, consider the following options:
1. set up custom discovery (`find` directive with `include` or `exclude`)
2. use a `src-layout`
3. explicitly set `py_modules` or `packages` with a list of names
To find more information, look for "package discovery" on setuptools docs.
Mike Bayer [Fri, 18 Feb 2022 15:05:12 +0000 (10:05 -0500)]
implement dataclass_transforms
Implement a new means of creating a mapped dataclass where
instead of applying the `@dataclass` decorator distinctly,
the declarative process itself can create the dataclass.
MapperProperty and MappedColumn objects themselves take
the place of the dataclasses.Field object when constructing
the class.
The overall approach is made possible at the typing level
using pep-681 dataclass transforms [1].
This new approach should be able to completely supersede the
previous "dataclasses" approach of embedding metadata into
Field() objects, which remains a mutually exclusive declarative
setup style (mixing them introduces new issues that are not worth
solving).
valievkarim [Wed, 18 May 2022 20:24:41 +0000 (16:24 -0400)]
Include new MySQL error code 4031 for MySQL disconnect check
Added disconnect code for MySQL error 4031, introduced in MySQL >= 8.0.24,
indicating connection idle timeout exceeded. In particular this repairs an
issue where pre-ping could not reconnect on a timed-out connection. Pull
request courtesy valievkarim.
Mike Bayer [Mon, 16 May 2022 14:57:51 +0000 (10:57 -0400)]
fix most sphinx warnings (1.4)
still can't figure out the warnings with some of the older
changelog files.
this cherry-picks the sphinx fixes from 1.4 and additionally
fixes a small number of new issues in the 2.0 docs. However,
2.0 has many more errors to fix, primarily from the removal
of the legacy tutorials left behind a lot of labels that need
to be re-linked to the new tutorial.
Mike Bayer [Fri, 13 May 2022 20:08:34 +0000 (16:08 -0400)]
render col name in on conflict set clause, not given key
Fixed bug where the PostgreSQL :meth:`_postgresql.Insert.on_conflict`
method and the SQLite :meth:`_sqlite.Insert.on_conflict` method would both
fail to correctly accommodate a column with a separate ".key" when
specifying the column using its key name in the dictionary passed to
``set_``, as well as if the :attr:`_sqlite.Insert.excluded` or
:attr:`_postgresql.Insert.excluded` collection were used as the dictionary
directly.
Mike Bayer [Fri, 13 May 2022 19:43:53 +0000 (15:43 -0400)]
raise for same param name in expanding + non expanding
An informative error is raised if two individual :class:`.BindParameter`
objects share the same name, yet one is used within an "expanding" context
(typically an IN expression) and the other is not; mixing the same name in
these two different styles of usage is not supported and typically the
``expanding=True`` parameter should be set on the parameters that are to
receive list values outside of IN expressions (where ``expanding`` is set
by default).
Mike Bayer [Sat, 14 May 2022 14:25:53 +0000 (10:25 -0400)]
adjust log stacklevel for py3.11.0b1; enable greenlet
Fixed issue where support for logging "stacklevel" implemented in
:ticket:`7612` required adjustment to work with recently released Python
3.11.0b1, also repairs the unit tests which tested this feature.
Install greenlet from a py311 compat patch.
re: the stacklevel thing, this is going to be very inconvenient
if we have to keep hardcoding numbers everywhere for every
new python version
Mike Bayer [Mon, 9 May 2022 14:27:51 +0000 (10:27 -0400)]
dont use the label convention for memoized entities
Fixed issue where ORM results would apply incorrect key names to the
returned :class:`.Row` objects in the case where the set of columns to be
selected were changed, such as when using
:meth:`.Select.with_only_columns`.
Mike Bayer [Mon, 9 May 2022 02:49:33 +0000 (22:49 -0400)]
explicitly fetch inserted pk for values(pkcol=None)
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 :ticket:`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.
khashashin [Tue, 3 May 2022 20:00:59 +0000 (22:00 +0200)]
docs(types) Fix missing import from sqlalchemy (#7978)
* docs(types) Fix missing import from sqlalchemy
The sample code is missing the import of Enum from sqlalchemy, which might confuse the reader, since we are using another enum type from Python itself here. So it makes sense to clarify that here.
Mike Bayer [Tue, 3 May 2022 12:58:27 +0000 (08:58 -0400)]
bypass pyodbc default server version / set charset
Further adjustments to the MySQL PyODBC dialect to allow for complete
connectivity, which was previously still not working despite fixes in
:ticket:`7871`.
Mike Bayer [Sun, 1 May 2022 16:28:36 +0000 (12:28 -0400)]
use bindparam_type in BinaryElementImpl._post_coercion if available
Fixed an issue where using :func:`.bindparam` with no explicit data or type
given could be coerced into the incorrect type when used in expressions
such as when using :meth:`.ARRAY.comparator.any` and
:meth:`.ARRAY.comparator.all`.
Mike Bayer [Wed, 27 Apr 2022 19:43:02 +0000 (15:43 -0400)]
inline mypy config; files ignoring type errors for the moment
to simplify pyproject.toml change the remaining files
that aren't going to be typed on this first pass
(unless of course someone wants to type some of these)
to include # mypy: ignore-errors. for the moment, only a handful
of ORM modules are to have more type checking implemented.
It's important that ignore-errors is used and
not "# type: ignore", as in the latter case, mypy doesn't even
read the existing types in the file, which makes it impossible to
type any files that refer to those modules at all.
to simplify ongoing typing work use inline mypy config
for remaining files that are "done" for now, indicating the
level of type checking they currently have.
Mike Bayer [Wed, 20 Apr 2022 01:06:41 +0000 (21:06 -0400)]
pep484 ORM / SQL result support
after some experimentation it seems mypy is more amenable
to the generic types being fully integrated rather than
having separate spin-off types. so key structures
like Result, Row, Select become generic. For DML
Insert, Update, Delete, these are spun into type-specific
subclasses ReturningInsert, ReturningUpdate, ReturningDelete,
which is fine since the "row-ness" of these constructs
doesn't happen until returning() is called in any case.
a Tuple based model is then integrated so that these
objects can carry along information about their return
types. Overloads at the .execute() level carry through
the Tuple from the invoked object to the result.
To suit the issue of AliasedClass generating attributes
that are dynamic, experimented with a custom subclass
AsAliased, but then just settled on having aliased()
lie to the type checker and return `Type[_O]`, essentially.
will need some type-related accessors for with_polymorphic()
also.
Additionally, identified an issue in Update when used
"mysql style" against a join(), it basically doesn't work
if asked to UPDATE two tables on the same column name.
added an error message to the specific condition where
it happens with a very non-specific error message that we
hit a thing we can't do right now, suggest multi-table
update as a possible cause.
Stephen Finucane [Wed, 27 Apr 2022 12:56:17 +0000 (08:56 -0400)]
Add git-blame-ignore-revs file
<!-- Provide a general summary of your proposed changes in the Title field above -->
### Description
This is helpful when using git-blame on files that 'black' heavily modified back in 2019'ish. While git doesn't specify this name, there seems to be a general convention around using this. See Django [1] and Twisted [2], for example.
### 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
- [ ] 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.
- [x] None of the above
- This is infra-related
Mike Bayer [Tue, 26 Apr 2022 19:32:30 +0000 (15:32 -0400)]
changelog edits for 1.4.36
for 2.0, we split 7953 into two separate changelog files,
one with the 2.0 fix to the logic itself, and the other under
1.4 indicating the warning being emitted.
we also add into 1.4 the file for the C extension change
made for 7875 so it renders into the 1.4 documentation.
Mike Bayer [Tue, 26 Apr 2022 19:02:37 +0000 (15:02 -0400)]
repair fetch_setting call in mysql pyodbc dialect
Fixed a regression in the untested MySQL PyODBC dialect caused by the fix
for :ticket:`7518` in version 1.4.32 where an argument was being propagated
incorrectly upon first connect, leading to a ``TypeError``.
Mike Bayer [Sun, 24 Apr 2022 20:19:16 +0000 (16:19 -0400)]
forwards port test cases and changelog for #7958 to 2.0
in 6f02d5edd88fe2475629438b0730181a2b00c5fe some cleanup
to ForeignKey repaired the use case of ForeignKey objects
referring to table name alone, by adding more robust
column resolution logic. This change also fixes an issue
where the "referred column" naming convention key uses the
resolved referred column earlier than usual when a
ForeignKey is setting up its constraint.
change message for 1.4:
Fixed bug where :class:`.ForeignKeyConstraint` naming conventions using the
``referred_column_0`` naming convention key would not work if the foreign
key constraint were set up as a :class:`.ForeignKey` object rather than an
explicit :class:`.ForeignKeyConstraint` object. As this change makes use of
a backport of some fixes from version 2.0, an additional little-known
feature that has likely been broken for many years is also fixed which is
that a :class:`.ForeignKey` object may refer to a referred table by name of
the table alone without using a column name, if the name of the referent
column is the same as that of the referred column.
The ``referred_column_0`` naming convention key was not previously not
tested with the :class:`.ForeignKey` object, only
:class:`.ForeignKeyConstraint`, and this bug reveals that the feature has
never worked correctly unless :class:`.ForeignKeyConstraint` is used for
all FK constraints. This bug traces back to the original introduction of
the feature introduced for :ticket:`3989`.
Mike Bayer [Fri, 22 Apr 2022 14:57:00 +0000 (10:57 -0400)]
properly type array element in any() / all()
Fixed bug in :class:`.ARRAY` datatype in combination with :class:`.Enum` on
PostgreSQL where using the ``.any()`` method to render SQL ANY(), given
members of the Python enumeration as arguments, would produce a type
adaptation failure on all drivers.
Mike Bayer [Thu, 21 Apr 2022 17:27:16 +0000 (13:27 -0400)]
fix result.columns() method
Fixed issue in :meth:`.Result.columns` method where calling upon
:meth:`.Result.columns` with a single index could in some cases,
particularly ORM result object cases, cause the :class:`.Result` to yield
scalar objects rather than :class:`.Row` objects, as though the
:meth:`.Result.scalars` method had been called. In SQLAlchemy 1.4, this
scenario emits a warning that the behavior will change in SQLAlchemy 2.0.
Alex Grönholm [Mon, 18 Apr 2022 17:07:19 +0000 (13:07 -0400)]
Implement UUID.python_type
Implemented :attr:`_postgresql.UUID.python_type` attribute for the
:class:`_postgresql.UUID` type object. The attribute will return either
``str`` or ``uuid.UUID`` based on the :paramref:`_postgresql.UUID.as_uuid`
parameter setting. Previously, this attribute was unimplemented. Pull
request courtesy Alex Grönholm.
projects / thirdparty / sqlalchemy / sqlalchemy.git / log
