Mike Bayer [Mon, 30 Mar 2020 15:04:24 +0000 (11:04 -0400)]
Remove ORDER BY pk from subqueryload, selectinload
Modified the queries used by subqueryload and selectinload to no longer
ORDER BY the primary key of the parent entity; this ordering was there to
allow the rows as they come in to be copied into lists directly with a
minimal level of Python-side collation. However, these ORDER BY clauses
can negatively impact the performance of the query as in many scenarios
these columns are derived from a subquery or are otherwise not actual
primary key columns such that SQL planners cannot make use of indexes. The
Python-side collation uses the native itertools.group_by() to collate the
incoming rows, and has been modified to allow multiple
row-groups-per-parent to be assembled together using list.extend(), which
should still allow for relatively fast Python-side performance. There will
still be an ORDER BY present for a relationship that includes an explicit
order_by parameter, however this is the only ORDER BY that will be added to
the query for both kinds of loading.
Mike Bayer [Sun, 29 Mar 2020 18:24:39 +0000 (14:24 -0400)]
Add a third labeling mode for SELECT statements
Enhanced the disambiguating labels feature of the
:func:`~.sql.expression.select` construct such that when a select statement
is used in a subquery, repeated column names from different tables are now
automatically labeled with a unique label name, without the need to use the
full "apply_labels()" feature that conbines tablename plus column name.
The disambigated labels are available as plain string keys in the .c
collection of the subquery, and most importantly the feature allows an ORM
:func:`.orm.aliased` construct against the combination of an entity and an
arbitrary subquery to work correctly, targeting the correct columns despite
same-named columns in the source tables, without the need for an "apply
labels" warning.
The existing labeling style is now called
LABEL_STYLE_TABLENAME_PLUS_COL. This labeling style will remain used
throughout the ORM as has been the case for over a decade, however,
the new disambiguation scheme could theoretically replace this scheme
entirely. The new scheme would dramatically alter how SQL looks
when rendered from the ORM to be more succinct but arguably harder
to read.
The tablename_columnname scheme used by Join.c is unaffected here,
as that's still hardcoded to that scheme.
Federico Caselli [Tue, 24 Mar 2020 21:55:46 +0000 (22:55 +0100)]
String compiler can now literal compile datetime objects
Add ability to literal compile a :class:`DateTime`, :class:`Date`
or :class:"Time" when using the string dialect for debugging purposes.
This change does not impact real dialect implementation that retain
their current behavior.
Mike Bayer [Sun, 29 Mar 2020 16:06:29 +0000 (12:06 -0400)]
Repair queries from #5134 to ORDER BY outside the subquery
ORDER BY has to be on the outermost query to guarantee ordering
as these tests were failing for SQL Server. Also add
new tests that don't use from_self() as this is also going to
be removed in 2.0.
Also propose some from_self() alternatives. References #5221
Federico Caselli [Sat, 28 Mar 2020 10:04:44 +0000 (11:04 +0100)]
Fix typo in resultproxy.c and test compatibility with python 3.5
- Fix typo in resultproxy.c that would error on windows.
- add -Wundef to C flags when linux is detected so that undefined
symbols emit a warning
- a few adjustments for tests to succeed on python 3.5
- note minimum version still documented here as 3.4 but this should
move to at least 3.5 if not 3.6 for SQLAlchemy 1.4
Mike Bayer [Thu, 26 Mar 2020 21:26:57 +0000 (17:26 -0400)]
introduce "autobegin" concept for Connection
because engine.connect() and engine.begin() should feature
identical internal behavior, with the sole exception that
one rolls back and the end and the other commits at the end,
while also supporting execution options like transaction isolation level
at the connection level, include that engine.connect() will
return a connection that uses autobegin in the same way as the
session will. This is solely to support the "begin" event
noting that a transaction is begun which is tracked on the
connection. Behavior and design should be very similar to that
of the ORM session and "Transaction" should no longer be a very
explicit object.
Mike Bayer [Wed, 25 Mar 2020 15:34:19 +0000 (11:34 -0400)]
Correct ambiguous func / class links
:func:`.sql.expression.select`, :func:`.sql.expression.insert`
and :class:`.sql.expression.Insert` were hitting many ambiguous
symbol errors, due to future.select, as well as the PG/MySQL
variants of Insert.
Mike Bayer [Mon, 23 Mar 2020 18:52:05 +0000 (14:52 -0400)]
Convert schema_translate to a post compile
Revised the :paramref:`.Connection.execution_options.schema_translate_map`
feature such that the processing of the SQL statement to receive a specific
schema name occurs within the execution phase of the statement, rather than
at the compile phase. This is to support the statement being efficiently
cached. Previously, the current schema being rendered into the statement
for a particular run would be considered as part of the cache key itself,
meaning that for a run against hundreds of schemas, there would be hundreds
of cache keys, rendering the cache much less performant. The new behavior
is that the rendering is done in a similar manner as the "post compile"
rendering added in 1.4 as part of :ticket:`4645`, :ticket:`4808`.
Gord Thompson [Fri, 20 Dec 2019 00:58:52 +0000 (19:58 -0500)]
Implement SQL VALUES in core.
Added a core :class:`Values` object that enables a VALUES construct
to be used in the FROM clause of an SQL statement for databases that
support it (mainly PostgreSQL and SQL Server).
Mike Bayer [Tue, 24 Mar 2020 16:51:53 +0000 (12:51 -0400)]
Reword changelog for issue #5207
SQLAlchemy can remain using setuptools even when pep-517 is
the standard installation process. We are omitting the file
for now because it leads to new pip installation issues that need
to be worked out in the greater pip community before SQLAlchemy
opts into this system.
Mike Bayer [Sun, 2 Feb 2020 16:39:37 +0000 (11:39 -0500)]
Remove deprecated elements from selectable.py; remove lockmode
Removed autocommit and legacy "for update" / "lockmode" elements
from selectable.py / query.py. lockmode was removed from
selectable in 693938dd6fb2f3ee3e031aed4c62355ac97f3ceb
however was not removed from the ORM.
Also removes the ignore_nonexistent_tables option on
join().
Gord Thompson [Thu, 12 Mar 2020 18:54:37 +0000 (12:54 -0600)]
Implement autocommit isolation level for cx_oracle
As part of this change Oracle also gets the concept of a
default isolation level, however since Oracle does not provide a
fixed method of knowing what the isolation level would be without a
server side transaction actually in progress, for now we hardcode
just to "READ COMMITTED".
Enhanced the test suite for isolation level testing in the dialect
test suite and added features to requirements so that the supported
isolation levels can be reported generically for dialects.
Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com> Fixes: #5200
Change-Id: I2c4d49da9ff80ccc228c21e196ec9a961de53478
Mike Bayer [Sat, 21 Mar 2020 21:26:24 +0000 (17:26 -0400)]
Test instance for matching class hierarchy on get_from_identity
Fixed issue where a lazyload that uses session-local "get" against a target
many-to-one relationship where an object with the correct primary key is
present, however it's an instance of a sibling class, does not correctly
return None as is the case when the lazy loader actually emits a load for
that row.
Federico Caselli [Sat, 14 Mar 2020 13:02:44 +0000 (14:02 +0100)]
Deprecate plain string in execute and introduce `exec_driver_sql`
Execution of literal sql string is deprecated in the
:meth:`.Connection.execute` and a warning is raised when used stating
that it will be coerced to :func:`.text` in a future release.
To execute a raw sql string the new connection method
:meth:`.Connection.exec_driver_sql` was added, that will retain the previous
behavior, passing the string to the DBAPI driver unchanged.
Usage of scalar or tuple positional parameters in :meth:`.Connection.execute`
is also deprecated.
Mike Bayer [Sat, 21 Mar 2020 14:16:47 +0000 (10:16 -0400)]
Remove pyproject.toml from distribution
SQLAlchemy does not want to opt-in to pep-517 at this time
as this would require a custom build backend interface
which we have not built yet, and the standard is not
widely adopted at this time in any case. Per
[1] [2], the presence of this file indicates a positive opt-in
to pep-517, so it must be omitted from source distributions.
Mike Bayer [Fri, 20 Mar 2020 20:40:47 +0000 (16:40 -0400)]
Clarify Alembic vs. Migrate
Migrate is fully legacy at this point so continue to mention
it to provide clarity, but ensure it isn't suggested
as a real alternative. It's unclear if Migrate will be able
to support SQLAlchemy 2.0.
Mike Bayer [Fri, 20 Mar 2020 19:21:10 +0000 (15:21 -0400)]
Emphasize context managers when working with Core
Prep the main documentation for the changes coming up in 1.4
by first removing references to engine.execute() outside
of the "connectionless" section, and using context managers
in all cases. For features that have always been confusing
and are going away, add a note that this feature will
be going away.
Mike Bayer [Fri, 20 Mar 2020 15:17:47 +0000 (11:17 -0400)]
Correct misleading guidance on multiprocessing
Link the connections intro to the dedicated section
on multiprocessing rather than stating that a separate
engine per process is needed, since this is inaccurate
and vague.
mike bayer [Wed, 18 Mar 2020 23:05:20 +0000 (19:05 -0400)]
Don't include PG INCLUDE columns as regular index columns
Fixed issue where a "covering" index, e.g. those which have an INCLUDE
clause, would be reflected including all the columns in INCLUDE clause as
regular columns. A warning is now emitted if these additional columns are
detected indicating that they are currently ignored. Note that full
support for "covering" indexes is part of :ticket:`4458`. Pull request
courtesy Marat Sharafutdinov.
Mike Bayer [Fri, 13 Mar 2020 22:37:25 +0000 (18:37 -0400)]
import the module as passed before we look in sys.modules
in I2e291eba4297867fc0ddb5d875b9f7af34751d01, the importer
is importing tokens[0:-1] and then the final token as the name;
this doesn't always work for the two example modules of "xml.dom"
and "wsgiref.simple_server". import the complete path we will
pull from sys.modules directly.
Federico Caselli [Sat, 14 Mar 2020 12:57:42 +0000 (13:57 +0100)]
Support inspection of computed column
Added support for reflection of "computed" columns, which are now returned
as part of the structure returned by :meth:`.Inspector.get_columns`.
When reflecting full :class:`.Table` objects, computed columns will
be represented using the :class:`.Computed` construct.
Also improve the documentation in :meth:`Inspector.get_columns`, correctly
listing all the returned keys.
jonathan vanasco [Sat, 14 Mar 2020 12:27:43 +0000 (08:27 -0400)]
updated doc string to show `NOT (EXISTS` instead of `NOT EXISTS`, as …
…that is what SqlAlchemy now renders.
SqlAlchemy currently renders `NOT (EXISTS` not `NOT EXISTS`. This fixes the example SQL.
### Description
The change is a docstring only.
### Checklist
This pull request is:
- [x] 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.
2. add some exclusion rules to allow the sqlite_file target to work;
add to tox.
3. add reap dbs target for SQLite, repair SQLite drop_db routine
which also wasn't doing the right thing for memory databases
etc.
4. Fix logging in provision files, as the main provision logger
is the one that's enabled by reap_dbs and maybe others, have all
the provision files use the provision logger.
Mike Bayer [Fri, 13 Mar 2020 03:11:18 +0000 (23:11 -0400)]
Include schema in all_tab_comments query
Fixed regression / incorrect fix caused by fix for :ticket:`5146` where the
Oracle dialect reads from the "all_tab_comments" view to get table comments
but fails to accommodate for the current owner of the table being
requested, causing it to read the wrong comment if multiple tables of the
same name exist in multiple schemas.
Mike Bayer [Thu, 12 Mar 2020 23:44:37 +0000 (19:44 -0400)]
Dont raise on pytest deprecation warnings
py.test 5.4.0 emits deprecation warnings for pytest.Class.
make sure we don't raise for these, and log the code that will
be used for 5.4.0 when we bump requirements.
Mike Bayer [Wed, 11 Mar 2020 14:41:12 +0000 (10:41 -0400)]
Repair broken call to sys.exc_info()
Fixed regression in 1.3.14 due to :ticket:`4849` where a sys.exc_info()
call failed to be invoked correctly when a flush error would occur. Test
coverage has been added for this exception case.
Mike Bayer [Tue, 10 Mar 2020 22:48:42 +0000 (18:48 -0400)]
Reword implicit left join error; ensure deterministic FROM for columns
Adjusted the error message emitted by :meth:`.Query.join` when a left hand
side can't be located that the :meth:`.Query.select_from` method is the
best way to resolve the issue. Also, within the 1.3 series, used a
deterministic ordering when determining the FROM clause from a given column
entity passed to :class:`.Query` so that the same expression is determined
each time.
Mike Bayer [Fri, 6 Mar 2020 21:04:46 +0000 (16:04 -0500)]
Rework select(), CompoundSelect() in terms of CompileState
Continuation of I408e0b8be91fddd77cf279da97f55020871f75a9
- add an options() method to the base Generative construct.
this will be where ORM options can go
- Change Null, False_, True_ to be singletons, so that
we aren't instantiating them and having to use isinstance.
The previous issue with this was that they would produce dupe
labels in SELECT statements. Apply the duplicate column
logic, newly added in 1.4, to these objects as well as to
non-apply-labels SELECT statements in general as a means of
improving this.
- create a revised system for generating ClauseList compilation
constructs that simplfies up front creation to not actually
use ClauseList; a simple tuple is rendered by the compiler
using the same constrcution rules as what are used for
ClauseList but without creating the actual object. Apply
to Select, CompoundSelect, revise Update, Delete
- Select, CompoundSelect get an initial CompileState
implementation. All methods used only within compilation
are moved here
- refine update/insert/delete compile state to not require
an outside boolean
- refine and simplify Select._copy_internals
- rework bind(), which is going away, to not use some
of the internal traversal stuff
- remove "autocommit", "for_update" parameters from Select,
references #4643
- remove "autocommit" parameter from TextClause ,
references #4643
- add deprecation warnings for statement.execute(),
engine.execute(), statement.scalar(), engine.scalar(). Fixes: #5193
Change-Id: I04ca0152b046fd42c5054ba10f37e43fc6e5a57b
Gord Thompson [Mon, 9 Mar 2020 21:50:53 +0000 (17:50 -0400)]
Reflect comments from any table accessible by the current user
Fixed a reflection bug where table comments could only be retrieved for
tables actually owned by the user but not for tables visible to the user
but owned by someone else. Pull request courtesy Dave Hirschfeld.
Simplified module pre-loading strategy and made it linter friendly
Introduced a modules registry to register modules that should be lazily loaded
in the package init. This ensures that they are in the system module cache,
avoiding potential thread safety issues as when importing them directly
in the function that uses them. The module registry is used to obtain
these modules directly, ensuring that the all the lazily loaded modules
are resolved at the proper time
This replaces dependency_for decorator and the dependencies decorator logic,
removing the need to pass the resolved modules as arguments of the
decodated functions and removes possible errors caused by linters.
Mike Bayer [Sun, 23 Feb 2020 18:37:18 +0000 (13:37 -0500)]
Decouple compiler state from DML objects; make cacheable
Targeting select / insert / update / delete, the goal
is to minimize overhead of construction and generative methods
so that only the raw arguments passed are handled. An interim
stage that converts the raw state into more compiler-ready state
is added, which is analogous to the ORM QueryContext which will
also be rolled in to be a similar concept, as is currently
being prototyped in I19e05b3424b07114cce6c439b05198ac47f7ac10.
the ORM update/delete BulkUD concept is also going to be rolled
onto this idea. So while the compiler-ready state object,
here called DMLState, looks a little thin, it's the
base of a bigger pattern that will allow for ORM functionality
to embed itself directly into the compiler, execution
context, and result set objects.
This change targets the DML objects, primarily focused on the
values() method which is the most complex process. The
work done by values() is minimized as much as possible
while still being able to create a cache key. Additional
computation is then offloaded to a new object ValuesState
that is handled by the compiler.
Architecturally, a big change here is that insert.values()
and update.values() will generate BindParameter objects for
the values now, which are then carefully received by crud.py
so that they generate the expected names. This is so that
the values() portion of these constructs is cacheable.
for the "multi-values" version of Insert, this is all skipped
and the plan right now is that a multi-values insert is
not worth caching (can always be revisited).
Using the
coercions system in values() also gets us nicer validation
for free, we can remove the NotAClauseElement thing from
schema, and we also now require scalar_subquery() is called
for an insert/update that uses a SELECT as a column value,
1.x deprecation path is added.
The traversal system is then applied to the DML objects
including tests so that they have traversal, cloning, and
cache key support. cloning is not a use case for DML however
having it present allows better validation of the structure
within the tests.
Special per-dialect DML is explicitly not cacheable at the moment,
more as a proof of concept that third party DML constructs can
exist as gracefully not-cacheable rather than producing an
incomplete cache key.
A few selected performance improvements have been added as well,
simplifying the immutabledict.union() method and adding
a new SQLCompiler function that can generate delimeter-separated
clauses like WHERE and ORDER BY without having to build
a ClauseList object at all. The use of ClauseList will
be removed from Select in an upcoming commit. Overall,
ClaustList is unnecessary for internal use and only adds
overhead to statement construction and will likely be removed
as much as possible except for explcit use of conjunctions like
and_() and or_().
Mike Bayer [Wed, 4 Mar 2020 22:44:40 +0000 (17:44 -0500)]
Render VALUES within composed MySQL on duplicate key expressions
Fixed issue in MySQL :meth:`.mysql.Insert.on_duplicate_key_update` construct
where using a SQL function or other composed expression for a column argument
would not properly render the ``VALUES`` keyword surrounding the column
itself.
Mike Bayer [Wed, 4 Mar 2020 00:15:55 +0000 (19:15 -0500)]
Run handle_error for any exceptions raised in execute_context()
Observing a SQLite connection/cursor being hung on
test_resultset -> PositionalTextTest -> test_dupe_col_obj.
this uses connectionless execution and the result object
fails to be constructed. When that happens, there is no path
for the cursor or connection to be closed / released.
Recent changes with the exception assertions in #4849 seem to
be causing a cycle to last a little longer than usual which
is exposing this issue for one particular test on SQLite.
As we want to get rid of weakref cleanup, evaluate
why we dont have handle_dbapi_exception for this whole
block, as after_cursor_execute can raise, result construction
can raise, autocommit can raise, close can raise, there
does not seem to be a reason these things should be outside
of the block that gets cleaned up.
Mike Bayer [Tue, 3 Mar 2020 22:22:30 +0000 (17:22 -0500)]
Restore crud flags if visiting_cte is set
Fixed bug where a CTE of an INSERT/UPDATE/DELETE that also uses RETURNING
could then not be SELECTed from directly, as the internal state of the
compiler would try to treat the outer SELECT as a DELETE statement itself
and access nonexistent state.
Remove the deprecated loader options ``joinedload_all``, ``subqueryload_all``,
``lazyload_all``, ``selectinload_all``. The normal version with method chaining
should be used in their place.
Mike Bayer [Tue, 3 Mar 2020 21:03:39 +0000 (16:03 -0500)]
Don't import provision.py unconditionally
Removed the imports for provision.py from each dialect
and instead added a call in the central provision.py to
a new dialect level method load_provisioning(). The
provisioning registry works in the same way, so an existing
dialect that is using the provision.py system right now
by importing it as part of the package will still continue to
function. However, to avoid pulling in the testing package when
the dialect is used in a non-testing context, the new hook may be
used. Also removed a module-level dependency
of the testing framework on the orm package.
Revised an internal change to the test system added as a result of
:ticket:`5085` where a testing-related module per dialect would be loaded
unconditionally upon making use of that dialect, pulling in SQLAlchemy's
testing framework as well as the ORM into the module import space. This
would only impact initial startup time and memory to a modest extent,
however it's best that these additional modules aren't reverse-dependent on
straight Core usage.
Mike Bayer [Sat, 29 Feb 2020 19:40:45 +0000 (14:40 -0500)]
Ensure all nested exception throws have a cause
Applied an explicit "cause" to most if not all internally raised exceptions
that are raised from within an internal exception catch, to avoid
misleading stacktraces that suggest an error within the handling of an
exception. While it would be preferable to suppress the internally caught
exception in the way that the ``__suppress_context__`` attribute would,
there does not as yet seem to be a way to do this without suppressing an
enclosing user constructed context, so for now it exposes the internally
caught exception as the cause so that full information about the context
of the error is maintained.
projects / thirdparty / sqlalchemy / sqlalchemy.git / log
