Fixed an issue in :meth:`_sql.Select.join_from` where the join condition
between the left and right tables specified in the method call could be
incorrectly determined based on an intermediate table already present in
the FROM clause, rather than matching the foreign keys between the
immediate left and right arguments. The join condition is now determined by
matching primary keys between the two tables explicitly passed to
:meth:`_sql.Select.join_from`, ensuring consistent and predictable join
behavior regardless of the order of join operations or other tables present
in the query. The fix is applied to both the Core and ORM implementations
of :meth:`_sql.Select.join_from`.
Fixes: #12931
Change-Id: Id457d441ee8a1bd64a002e57a9dcfd6fc56ff15e
Fixed regression in :meth:`_sql.Select.with_statement_hint` and others
where the generative behavior of the method stopped producing a copy of the
object.
Fixes: #11703
Change-Id: Ia4482f91f76fae9982dc6b075bf5cfec7042ffa6
Added support for slice access with :class:`.ColumnCollection`, e.g.
``table.c[0:5]``, ``subquery.c[:-1]`` etc. Slice access returns a sub
:class:`.ColumnCollection` in the same way as passing a tuple of keys. This
is a natural continuation of the key-tuple access added for 🎫`8285`,
which it appears to be an oversight that this usage was omitted.
Change-Id: I6378642f39501ffbbae4acadf1dc38a43c39d722
References: #8285
References: #9690
Fixed regression where the :func:`_sql.select` construct would not be able
to render if it were given no columns and then used in the context of an
EXISTS, raising an internal exception instead. While an empty "SELECT" is
not typically valid SQL, in the context of EXISTS databases such as
PostgreSQL allow it, and in any case the condition now no longer raises
an internal exception.
For this case, also add an extra whitespace trim step for the unusual
case that there are no columns to render. This is done in such a
way as to not interfere with other test cases that are involving
custom compilation schemes.
Fixes: #9440
Change-Id: If65ba9ce15d371f09b4342ad0669143b7b082a78
Added new syntax to the ``.c`` collection on all :class:`.FromClause`
objects allowing tuples of keys to be passed to ``__getitem__()``, along
with support for ``select()`` handling of ``.c`` collections directly,
allowing the syntax ``select(table.c['a', 'b', 'c'])`` to be possible. The
sub-collection returned is itself a :class:`.ColumnCollection` which is
also directly consumable by :func:`_sql.select` and similar now.
Fixes: #8285
Change-Id: I2236662c477ffc50af079310589e213323c960d1
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.
Change-Id: I5eff7eefe1d6166ee74160b2785c5e6a81fa8b95
"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-933882348Fixes: #7259Closes: #7260
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7260
Pull-request-sha: 2565a5fd4b
Change-Id: I685c8379762b5fb6ab4107ff8f4d8a4de70c0ca6
Fixed regression caused in 1.4.19 due to #6503 and related involving
:meth:`_orm.Query.with_entities` where the new structure used would be
inappropriately transferred to an enclosing :class:`_orm.Query` when making
use of set operations such as :meth:`_orm.Query.union`, causing the JOIN
instructions within to be applied to the outside query as well.
Fixes: #6698
Change-Id: Ia9f294215ebc01330d142a0a3e5be9d02be9380f
Fixed further regressions in the same area as that of 🎫`6052` where
loader options as well as invocations of methods like
:meth:`_orm.Query.join` would fail if the left side of the statement for
which the option/join depends upon were replaced by using the
:meth:`_orm.Query.with_entities` method, or when using 2.0 style queries
when using the :meth:`_sql.Select.with_only_columns` method. A new set of
state has been added to the objects which tracks the "left" entities that
the options / join were made against which is memoized when the lead
entities are changed.
Fixes: #6503Fixes: #6253
Change-Id: I211b2af98b0b20d1263fb15dc513884dcc5de6a4
Fixed regression where :meth:`_orm.Query.filter_by` would not work if the
lead entity were a SQL function or other expression derived from the
primary entity in question, rather than a simple entity or column of that
entity. Additionally, improved the behavior of
:meth:`_sql.Select.filter_by` overall to work with column expressions even
in a non-ORM context.
Fixes: #6414
Change-Id: I316b5bf98293bec1ede08787f6181dd14be85419
Fixed regression caused by 🎫`5395` where tuning back the check for
sequences in :func:`_sql.select` now caused failures when doing 2.0-style
querying with a mapped class that also happens to have an ``__iter__()``
method. Tuned the check some more to accommodate this as well as some other
interesting ``__iter__()`` scenarios.
Fixes: #6300
Change-Id: Idf1983fd764b91a7d5fa8117aee8a3def3cfe5ff
Fixed issue in new :meth:`_sql.Select.join` method where chaining from the
current JOIN wasn't looking at the right state, causing an expression like
"FROM a JOIN b <onclause>, b JOIN c <onclause>" rather than
"FROM a JOIN b <onclause> JOIN c <onclause>".
Added :meth:`_sql.Select.outerjoin_from` method to complement
:meth:`_sql.Select.join_from`.
Fixes: #5858
Change-Id: I1346ebe0963bbd1e4bf868650e3ee1d6d3072f04
This change includes mainly that the bracketed use within
select() is moved to positional, and keyword arguments are
removed from calls to the select() function. it does not
yet fully address other issues such as keyword arguments passed
to the table.select().
Additionally, allows False / None to both be considered
as "disable" for all of select.correlate(), select.correlate_except(),
query.correlate(), which establishes consistency with
passing of ``False`` for the legact select(correlate=False)
argument.
Change-Id: Ie6c6e6abfbd3d75d4c8de504c0cf0159e6999108
Improved the :func:`_sql.tuple_` construct such that it behaves predictably
when used in a columns-clause context. The SQL tuple is not supported as a
"SELECT" columns clause element on most backends; on those that do
(PostgreSQL, not surprisingly), the Python DBAPI does not have a "nested
type" concept so there are still challenges in fetching rows for such an
object. Use of :func:`_sql.tuple_` in a :func:`_sql.select` or
:class:`_orm.Query` will now raise a :class:`_exc.CompileError` at the
point at which the :func:`_sql.tuple_` object is seen as presenting itself
for fetching rows (i.e., if the tuple is in the columns clause of a
subquery, no error is raised). For ORM use,the :class:`_orm.Bundle` object
is an explicit directive that a series of columns should be returned as a
sub-tuple per row and is suggested by the error message. Additionally ,the
tuple will now render with parenthesis in all contexts. Previously, the
parenthesization would not render in a columns context leading to
non-defined behavior.
As part of this change, Tuple receives a dedicated datatype
which appears to allow us the very desirable change of removing
the bindparam._expanding_in_types attribute as well as
ClauseList._tuple_values (which might already have not been
needed due to #4645).
Fixes: #5127
Change-Id: Iecafa0e0aac2f1f37ec8d0e1631d562611c90200
Several weeks of using the future_select() construct
has led to the proposal there be just one select() construct
again which features the new join() method, and otherwise accepts
both the 1.x and 2.x argument styles. This would make
migration simpler and reduce confusion.
However, confusion may be increased by the fact that select().join()
is different Current thinking is we may be better off
with a few hard behavioral changes to old and relatively unknown APIs
rather than trying to play both sides within two extremely similar
but subtly different APIs. At the moment, the .join() thing seems
to be the only behavioral change that occurs without the user
taking any explicit steps. Session.execute() will still
behave the old way as we are adding a future flag.
This change also adds the "future" flag to Session() and
session.execute(), so that interpretation of the incoming statement,
as well as that the new style result is returned, does not
occur for existing applications unless they add the use
of this flag.
The change in general is moving the "removed in 2.0" system
further along where we want the test suite to fully pass
even if the SQLALCHEMY_WARN_20 flag is set.
Get many tests to pass when SQLALCHEMY_WARN_20 is set; this
should be ongoing after this patch merges.
Improve the RemovedIn20 warning; these are all deprecated
"since" 1.4, so ensure that's what the messages read.
Make sure the inforamtion link is on all warnings.
Add deprecation warnings for parameters present and
add warnings to all FromClause.select() types of methods.
Fixes: #5379Fixes: #5284
Change-Id: I765a0b912b3dcd0e995426427d8bb7997cbffd51
References: #5159
Convert Query to do virtually all compile state computation
in the _compile_context() phase, and organize it all
such that a plain select() construct may also be used as the
source of information in order to generate ORM query state.
This makes it such that Query is not needed except for
its additional methods like from_self() which are all to
be deprecated.
The construction of ORM state will occur beyond the
caching boundary when the new execution model is integrated.
future select() gains a working join() and filter_by() method.
as we continue to rebase and merge each commit in the steps,
callcounts continue to bump around. will have to look at
the final result when it's all in.
References: #5159
References: #4705
References: #4639
References: #4871
References: #5010
Change-Id: I19e05b3424b07114cce6c439b05198ac47f7ac10
if the left side is a join, it will attempt to join the right
side to the rightmost side of the left first, and not raise
any exceptions about ambiguous join conditions if successful
even if there are further join targets across the rest of
the left. [ticket:1714]
- generalized the "freetds" / "unicode statements" behavior of MS-SQL/pyodbc
into the base Pyodbc connector, as this seems to apply to Sybase as well.
- generalized the python-sybase "use autocommit for DDL" into the pyodbc
connector. With pyodbc, the "autocommit" flag on connection is used,
as Pyodbc seems to have more database conversation than python-sybase that
can't otherwise be suppressed.
- Some platforms will now interpret certain literal values
as non-bind parameters, rendered literally into the SQL
statement. This to support strict SQL-92 rules that are
enforced by some platforms including MS-SQL and Sybase.
In this model, bind parameters aren't allowed in the
columns clause of a SELECT, nor are certain ambiguous
expressions like "?=?". When this mode is enabled, the base
compiler will render the binds as inline literals, but only across
strings and numeric values. Other types such as dates
will raise an error, unless the dialect subclass defines
a literal rendering function for those. The bind parameter
must have an embedded literal value already or an error
is raised (i.e. won't work with straight bindparam('x')).
Dialects can also expand upon the areas where binds are not
accepted, such as within argument lists of functions
(which don't work on MS-SQL when native SQL binding is used).
defined bind parameter name that directly conflicts with
a column-named bind generated directly from the SET or
VALUES clause of an update/insert generates a compile error.
This reduces call counts and eliminates some cases where
undesirable name conflicts could still occur.
of determining the return type from an expression
as well as the adaptation of the Python operator into
a SQL operator, based on the full left/right/operator
of the given expression. In particular
the date/time/interval system created for Postgresql
EXTRACT in [ticket:1647] has now been generalized into
the type system. The previous behavior which often
occured of an expression "column + literal" forcing
the type of "literal" to be the same as that of "column"
will now usually not occur - the type of
"literal" is first derived from the Python type of the
literal, assuming standard native Python types + date
types, before falling back to that of the known type
on the other side of the expression. Also part
of [ticket:1683].
to be compared to another set, typically with IN against
composite primary keys or similar. Also accepts an
IN with multiple columns. The "scalar select can
have only one column" error message is removed - will
rely upon the database to report problems with
col mismatch.
of statements have more consistent behavior w.r.t.
parenthesizing. Each compound element embedded within
another will now be grouped with parenthesis - previously,
the first compound element in the list would not be grouped,
as SQLite doesn't like a statement to start with
parenthesis. However, Postgresql in particular has
precedence rules regarding INTERSECT, and it is
more consistent for parenthesis to be applied equally
to all sub-elements. So now, the workaround for SQLite
is also what the workaround for PG was previously -
when nesting compound elements, the first one usually needs
".alias().select()" called on it to wrap it inside
of a subquery. [ticket:1665]
before issuing the usual "expr != expr" clause. The
"expr != expr" can be very expensive, and it's preferred
that the user not issue in_() if the list is empty,
instead simply not querying, or modifying the criterion
as appropriate for more complex situations.
[ticket:1628]
"dbo.master", are now rendered in select() labels
with underscores for dots, i.e. "dbo_master_table_column".
This is a "friendly" label that behaves better
in result sets. [ticket:1428]
objects using names that match the keys of columns. These
bind parameters will circumvent the usual route to those
keys showing up in the VALUES or SET clause of the generated
SQL. [ticket:1579]
- deprecated PassiveDefault - use DefaultClause.
- the BINARY and MSBinary types now generate "BINARY" in all
cases. Omitting the "length" parameter will generate
"BINARY" with no length. Use BLOB to generate an unlengthed
binary column.
- the "quoting='quoted'" argument to MSEnum/ENUM is deprecated.
It's best to rely upon the automatic quoting.
- "shortname" attribute on bindparam() is removed.
- fold_equivalents flag on join is deprecated (will remain
until [ticket:1131] is implemented)
- "scalar" flag on select() is removed, use
select.as_scalar().
- 'transactional' flag on sessionmaker() and others is
removed. Use 'autocommit=True' to indicate 'transactional=False'.
- 'polymorphic_fetch' argument on mapper() is removed.
Loading can be controlled using the 'with_polymorphic'
option.
- 'select_table' argument on mapper() is removed. Use
'with_polymorphic=("*", <some selectable>)' for this
functionality.
- 'proxy' argument on synonym() is removed. This flag
did nothing throughout 0.5, as the "proxy generation"
behavior is now automatic.
- Passing a single list of elements to eagerload(),
eagerload_all(), contains_eager(), lazyload(),
defer(), and undefer() instead of multiple positional
-args is deprecated.
- Passing a single list of elements to query.order_by(),
query.group_by(), query.join(), or query.outerjoin()
instead of multiple positional *args is deprecated.
- query.iterate_instances() is removed. Use query.instances().
- Query.query_from_parent() is removed. Use the
sqlalchemy.orm.with_parent() function to produce a
"parent" clause, or alternatively query.with_parent().
- query._from_self() is removed, use query.from_self()
instead.
- the "comparator" argument to composite() is removed.
Use "comparator_factory".
- RelationProperty._get_join() is removed.
- the 'echo_uow' flag on Session is removed. Use
logging on the "sqlalchemy.orm.unitofwork" name.
- session.clear() is removed. use session.expunge_all().
- session.save(), session.update(), session.save_or_update()
are removed. Use session.add() and session.add_all().
- the "objects" flag on session.flush() remains deprecated.
- the "dont_load=True" flag on session.merge() is deprecated
in favor of "load=False".
- passing an InstanceState (internal SQLAlchemy state object) to
attributes.init_collection() or attributes.get_history() is
deprecated. These functions are public API and normally
expect a regular mapped object instance.
- the 'engine' parameter to declarative_base() is removed.
Use the 'bind' keyword argument.
Mike Bayer