sqlalchemy-tickets — trac tickets and updates forwarded to this list

[Sqlalchemy-tickets] Issue #4351: like() in association_proxy (zzzeek/sqlalchemy)

[Abdeali K. <iss...@bi...>]

New issue 4351: like() in association_proxy
https://bitbucket.org/zzzeek/sqlalchemy/issues/4351/like-in-association_proxy

Abdeali Kothari:

This is an issue I'm creating for the situation described in: https://stackoverflow.com/questions/36017947/filter-with-like-on-sqlalchemy-association-proxy

I am creating the issue here to understand if there is an interest in adding this functionality to the core library.

If there is interest in adding like() filters to association proxies i would be interested in contributing and making a pull-request for it

The example here is:

```
class Institution(Base):

    entity_id = Column(
        String(8, u'SQL_Latin1_General_CP1_CI_AS'),
        ForeignKey(Entity.entity_id),
        primary_key=True)
    entity = relationship(Entity, uselist=False)
    name = association_proxy('entity', 'entity_proper_name')

```

and the outputs:
```
query = s.query(Institution).filter(Institution.name=='Correct Name')
# works fine

query = s.query(Institution).filter(Institution.name.like('%Correct N%')
# AttributeError: 'AssociationProxy' object has no attribute 'like'
```

A possible workaround is:
```
from sqlalchemy.ext.associationproxy import AssociationProxy

def like_filter(attr, *args, **kwargs):
    filter_attr = attr
    if isinstance(filter_attr, AssociationProxy):  # If assoc_proxy get remote_attr as like() doesnt work
        filter_attr = filter_attr.remote_attr
    return filter_attr.like(*args, **kwargs)
```

[Sqlalchemy-tickets] Issue #4350: synonym + single inheritance + mixins (zzzeek/sqlalchemy)

[Michael B. <iss...@bi...>]

New issue 4350: synonym + single inheritance + mixins
https://bitbucket.org/zzzeek/sqlalchemy/issues/4350/synonym-single-inheritance-mixins

Michael Bayer:

this shouldn't seem to need @cascading though it at least works when we do that, but synonyms are inheritable.  if the superclass has the synonym it should be getting mapped or at least warning there's a problem:

```
#!python


from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr


Base = declarative_base()


class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    type = Column(String)
    __mapper_options__ = {
        'polymorphic_on': type
    }
    data = Column(String)


class Mixin:
    @declared_attr
    def data_syn(cls):
        return synonym("data")

    @declared_attr.cascading
    def data_syn_cascading(cls):
        return synonym("data")


class B(Mixin, A):
    __mapper_options__ = {
        'polymorphic_identity': "B"
    }


class C(B):
    __mapper_options__ = {
        'polymorphic_identity': "C"
    }

s = Session()

# yes
print(s.query(B.data).filter(B.data == 'foo'))

# yes
print(s.query(B.data_syn_cascading).filter(B.data_syn_cascading == 'foo'))

# yes
print(s.query(B.data_syn).filter(B.data_syn == 'foo'))


# yes
print(s.query(C.data).filter(C.data == 'foo'))

# yes
print(s.query(C.data_syn_cascading).filter(C.data_syn_cascading == 'foo'))

# no, there's an unmapped C.data_syn property sitting there blocking it
print(s.query(C.data_syn).filter(C.data_syn == 'foo'))
```

[Sqlalchemy-tickets] Issue #4349: "Dynamic" relationship seems to ignore (at least) a composite secondary (zzzeek/sqlalchemy)

[Ilja E. <iss...@bi...>]

New issue 4349: "Dynamic" relationship seems to ignore (at least) a composite secondary
https://bitbucket.org/zzzeek/sqlalchemy/issues/4349/dynamic-relationship-seems-to-ignore-at

Ilja Everilä:

It would seem that a relationship configured with `lazy="dynamic"` ignores a [composite "secondary"](https://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#composite-secondary-joins), resulting in an incorrect query that performs a CROSS JOIN instead of the explicitly defined join. Given this artificial setup:


```
#!python

from sqlalchemy import create_engine, Integer, Column, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine("sqlite:///", echo=True)
Base = declarative_base()
Base.metadata.bind = engine
Session = sessionmaker()
session = Session()

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    ds_dyn = relationship(
        'D',
        secondary="join(B, C)",
        primaryjoin="A.id == B.a_id",
        secondaryjoin="C.d_id == D.id",
        lazy="dynamic")
    ds_sel = relationship(
        'D',
        secondary="join(B, C)",
        primaryjoin="A.id == B.a_id",
        secondaryjoin="C.d_id == D.id")
    ds_join = relationship(
        'D',
        secondary="join(B, C)",
        primaryjoin="A.id == B.a_id",
        secondaryjoin="C.d_id == D.id",
        lazy="joined")

class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey(A.id))

class D(Base):
    __tablename__ = 'd'
    id = Column(Integer, primary_key=True)

class C(Base):
    __tablename__ = 'c'
    id = Column(Integer, primary_key=True)
    b_id = Column(ForeignKey(B.id))
    d_id = Column(ForeignKey(D.id))

Base.metadata.create_all()

```

and a dummy instance of `A`:


```
#!python

a = A()
session.add(a)
session.commit()

```

accessing the 3 relationships produce following queries:


```
#!python

In [2]: a.ds_sel
2018-10-17 12:34:20,820 INFO sqlalchemy.engine.base.Engine SELECT d.id AS d_id 
FROM d, b JOIN c ON b.id = c.b_id 
WHERE ? = b.a_id AND c.d_id = d.id
2018-10-17 12:34:20,820 INFO sqlalchemy.engine.base.Engine (1,)
Out[2]: []

```


```
#!python

In [3]: a.ds_join
2018-10-17 12:34:44,376 INFO sqlalchemy.engine.base.Engine SELECT d.id AS d_id 
FROM d, b JOIN c ON b.id = c.b_id 
WHERE ? = b.a_id AND c.d_id = d.id
2018-10-17 12:34:44,376 INFO sqlalchemy.engine.base.Engine (1,)
Out[3]: []

```


```
#!python

In [4]: a.ds_dyn.all()
2018-10-17 12:35:14,804 INFO sqlalchemy.engine.base.Engine SELECT d.id AS d_id 
FROM d, b, c 
WHERE ? = b.a_id AND c.d_id = d.id
2018-10-17 12:35:14,804 INFO sqlalchemy.engine.base.Engine (1,)
Out[4]: []

```

I've tried going through documentation [1](https://docs.sqlalchemy.org/en/latest/orm/relationship_api.html#sqlalchemy.orm.relationship.params.lazy), [2](https://docs.sqlalchemy.org/en/latest/orm/collections.html#dynamic-relationship) looking for if this is a known limitation or such, but without success.

On the other hand "dynamic" is omitted in the footnote about [composite "secondary"](https://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#composite-secondary-joins):

> New in version 0.9.2: Support is improved for allowing a `join()` construct to be used directly as the target of the `secondary` argument, including support for joins, eager joins and lazy loading, as well as support within declarative to specify complex conditions such as joins involving class names as targets.

Does this mean that dynamic loading does not support this?

[Sqlalchemy-tickets] Issue #4348: Table name 'function' not quoted in MySQL which is now a syntax error in version 8 (zzzeek/sqlalchemy)

[Alex H. <iss...@bi...>]

New issue 4348: Table name 'function' not quoted in MySQL which is now a syntax error in version 8
https://bitbucket.org/zzzeek/sqlalchemy/issues/4348/table-name-function-not-quoted-in-mysql

Alex Hall:

I have a table named 'function', and when SQLAlchemy creates it, it sends the SQL `CREATE TABLE function (...)`. This was valid in MySQL 5.6, but in the new MySQL 8 it's a syntax error because `function` is a reserved keyword. Quoting the table name with backticks works, so SQLAlchemy should probably do that.

While waiting for a fix, how can I force SQLAlchemy to always quote identifiers? I don't want to change the table name.

I'm using SQLAlchemy==1.2.12, mysql-connector==2.1.6, and MySQL versions as follows:


```
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+-----------------------+
| Variable_name           | Value                 |
+-------------------------+-----------------------+
| innodb_version          | 8.0.12                |
| protocol_version        | 10                    |
| slave_type_conversions  |                       |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2 |
| version                 | 8.0.12                |
| version_comment         | Homebrew              |
| version_compile_machine | x86_64                |
| version_compile_os      | osx10.13              |
| version_compile_zlib    | 1.2.11                |
+-------------------------+-----------------------+
```

Code to create the table:


```
#!python

class Base(object):
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()


Base = declarative_base(cls=Base)

class Function(Base):
    ... # column definitions

Base.metadata.create_all(engine)

```

Full traceback:

```
#!python

Traceback (most recent call last):
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/cursor.py", line 559, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/connection.py", line 494, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/connection.py", line 396, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'function (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	file TEXT, 
	name TEXT, 
	html' at line 1

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/runpy.py", line 170, in _run_module_as_main
    "__main__", mod_spec)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "/Users/alexhall/Desktop/python/treetrace/birdseye/__main__.py", line 5, in <module>
    from birdseye.server import main
  File "/Users/alexhall/Desktop/python/treetrace/birdseye/server.py", line 37, in <module>
    db = Database()
  File "/Users/alexhall/Desktop/python/treetrace/birdseye/db.py", line 169, in __init__
    Base.metadata.create_all(engine)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 4005, in create_all
    tables=tables)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1940, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1549, in _run_visitor
    **kwargs).traverse_single(element)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 757, in visit_metadata
    _is_metadata_operation=True)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 791, in visit_table
    include_foreign_key_constraints=include_foreign_key_constraints
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1009, in _execute_ddl
    compiled
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/cursor.py", line 559, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/connection.py", line 494, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/connection.py", line 396, in _handle_result
    raise errors.get_exception(packet)
sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'function (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	file TEXT, 
	name TEXT, 
	html' at line 1 [SQL: '\nCREATE TABLE function (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tfile TEXT, \n\tname TEXT, \n\thtml_body LONGTEXT, \n\tlineno INTEGER, \n\tdata LONGTEXT, \n\thash VARCHAR(64), \n\tbody_hash VARCHAR(64), \n\tPRIMARY KEY (id), \n\tCONSTRAINT everything_unique UNIQUE (hash)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)
```

[Sqlalchemy-tickets] Issue #4347: performance issue when fetching mapped columns individually in conjunction with he entity (zzzeek/sqlalchemy)

[Michael B. <iss...@bi...>]

New issue 4347: performance issue when fetching mapped columns individually in conjunction with he entity
https://bitbucket.org/zzzeek/sqlalchemy/issues/4347/performance-issue-when-fetching-mapped

Michael Bayer:

this is due to AnnotatedColumn resulting in an expensive comparison when we are doing the dictionary lookup in result row:

```
#!python

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base


import cProfile
from io import StringIO
import pstats
import contextlib


@contextlib.contextmanager
def profiled(name):
    pr = cProfile.Profile()
    pr.enable()
    yield
    pr.disable()
    ps = pstats.Stats(pr).sort_stats('cumulative')
    print("Test %s total calls: %d" % (name, ps.total_calls))


Base = declarative_base()


class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    data = Column(String)

e = create_engine("sqlite://")
Base.metadata.create_all(e)

s = Session(e)

s.add_all([A(data='asdf') for i in range(1)])
s.commit()


def no_bundle(s):
    for i in range(100):
        s.query(
            A
        ).select_from(A).all()


def no_entity_wo_annotations(s):
    for i in range(100):
        s.query(
            A.__table__.c.data
        ).select_from(A).all()


def no_entity_w_annotations(s):
    for i in range(100):
        s.query(
            A.data
        ).select_from(A).all()


def no_bundle_wo_annotations(s):
    for i in range(100):
        s.query(
            A.__table__.c.data, A
        ).select_from(A).all()


def no_bundle_w_annotations(s):
    for i in range(100):
        s.query(
            A.data, A
        ).select_from(A).all()


def bundle_wo_annotation(s):
    for i in range(100):
        s.query(
            Bundle("ASdf", A.__table__.c.data), A
        ).select_from(A).all()


def bundle_w_annotation(s):
    for i in range(100):
        s.query(
            Bundle("ASdf", A.data), A
        ).select_from(A).all()

with profiled("no bundle"):
    no_bundle(Session(e))

with profiled("no entity wo annotations"):
    no_entity_wo_annotations(Session(e))

with profiled("no entity w annotations"):
    no_entity_w_annotations(Session(e))

with profiled("no bundle wo annotations"):
    no_bundle_wo_annotations(Session(e))

with profiled("no bundle w annotations"):
    no_bundle_w_annotations(Session(e))

with profiled("bundle but using Core column"):
    bundle_wo_annotation(Session(e))

with profiled("bundle against annotated A.data"):
    bundle_w_annotation(Session(e))
```

result:


```
#!python

Test no bundle total calls: 35822
Test no entity wo annotations total calls: 28078
Test no entity w annotations total calls: 28785
Test no bundle wo annotations total calls: 40673
Test no bundle w annotations total calls: 49073
Test bundle but using Core column total calls: 44471
Test bundle against annotated A.data total calls: 54267


```

something weird is going on.  when we have the plain column, plus the full entity, the plain column is getting pulled into the resultproxy key lookup, then when the ORM looks in the key lookup by column, the annotated column is expensive inside the dictionary due to `__eq__` occurring.  This actually goes all the way into default_comparator and is very expensive.

not really sure how to fix this.

[Sqlalchemy-tickets] Issue #4346: update multiple rows fails when using unique constraint on multiple fields (zzzeek/sqlalchemy)

[Marcel B. <iss...@bi...>]

New issue 4346: update multiple rows fails when using unique constraint on multiple fields
https://bitbucket.org/zzzeek/sqlalchemy/issues/4346/update-multiple-rows-fails-when-using

Marcel Blöcher:

When using a unique constraint on multiple fields, e.g.,
__table_args__ = (UniqueConstraint('position', "whatever", name='_whatever_position_uc'),)
updating multiple rows within a session does not work.

Any idea what is going wrong? Seems to be that the query is not executed at once. So it looks like the query issues integrity checks after each single row update.

Tested with driver sqlite in memory, and also mysql (PyMySQL 0.9.2) 

SQLAlchemy 1.2.12
Python 3.6.6
OS 

minimal working example:

```
#!python

from sqlalchemy import create_engine, Column, Integer, UniqueConstraint
from sqlalchemy.orm import sessionmaker

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

engine = create_engine('sqlite:///:memory:')
SessionM = sessionmaker(bind=engine)
session = SessionM()

class Entry(Base):
    __tablename__ = 'entry'
    
    id = Column(Integer, primary_key=True)
    whatever = Column(Integer, nullable=False)
    position = Column(Integer, nullable=False)

    __table_args__ = (UniqueConstraint('position', 'whatever', name='_whatever_position_uc'),)
    
Base.metadata.create_all(engine)

session.add(Entry(whatever=1, position=1))
session.add(Entry(whatever=1, position=2))
session.add(Entry(whatever=1, position=3))
session.add(Entry(whatever=1, position=4))

session.commit()

# no increase the position of some of the entries by 1
to_update = session.query(Entry).filter(Entry.position >= 2).all()
for entry in to_update:
    entry.position += 1
    
session.commit()


```

Error

```
#!bash

/***/env/bin/python /***/bug-sql.py
Traceback (most recent call last):
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1170, in _execute_context
    context)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 506, in do_executemany
    cursor.executemany(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: entry.position, entry.whatever

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/***/bug-sql.py", line 34, in <module>
    session.commit()
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 943, in commit
    self.transaction.commit()
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 467, in commit
    self._prepare_impl()
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 447, in _prepare_impl
    self.session.flush()
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2254, in flush
    self._flush(objects)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2380, in _flush
    transaction.rollback(_capture_exception=True)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 249, in reraise
    raise value
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2344, in _flush
    flush_context.execute()
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 391, in execute
    rec.execute(self)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 556, in execute
    uow
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 177, in save_obj
    mapper, table, update)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 768, in _emit_update_statements
    execute(statement, multiparams)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1170, in _execute_context
    context)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 506, in do_executemany
    cursor.executemany(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: entry.position, entry.whatever [SQL: 'UPDATE entry SET position=? WHERE entry.id = ?'] [parameters: ((3, 2), (4, 3), (5, 4))] (Background on this error at: http://sqlalche.me/e/gkpj)
```

[Sqlalchemy-tickets] Issue #4345: No Query.correlate_except() method (zzzeek/sqlalchemy)

[Marat S. <iss...@bi...>]

New issue 4345: No Query.correlate_except() method
https://bitbucket.org/zzzeek/sqlalchemy/issues/4345/no-querycorrelate_except-method

Marat Sharafutdinov:

There is [Query.correlate()](https://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.correlate) method but there is no Query.correlate_except() method.

[Sqlalchemy-tickets] Issue #4344: Automap (reflection) fails on MySQL Chinook database (zzzeek/sqlalchemy)

[Thorsten K. <iss...@bi...>]

New issue 4344: Automap (reflection) fails on MySQL Chinook database
https://bitbucket.org/zzzeek/sqlalchemy/issues/4344/automap-reflection-fails-on-mysql-chinook

Thorsten Kampe:

* all software at their latest versions
* Database is MySQL 8.0.12 on Linux
* Client OS is Windows 10

Trying to automap the MySQL Chinook database results in an error (other RDBMSs work):
```
sqlalchemy.exc.NoReferencedColumnError: Could not initialize target column for ForeignKey
'Track.trackid' on table 'PlaylistTrack': table 'Track' has no column named 'trackid'
```
Python code:
```
import sqlalchemy as sa
from sqlalchemy.ext import automap

dbtype    = 'mysql'
host      = 'HOST'
password  = 'PASSWORD'
db        = 'Chinook'
sqlite_db = 'Chinook_Sqlite.sqlite'

conn_params = {'mssql':      f'mssql+pymssql://sa:{password}@{host}/{db}',
               'mysql':      f'mysql+pymysql://root:{password}@{host}/{db}',
               'oracle':     f'oracle://chinook:{password}@{host}/xe',
               'postgresql': f'postgres://postgres:{password}@{host}/{db}',
               'sqlite':     f'sqlite+pysqlite:///{sqlite_db}'}

Base   = automap.automap_base()
engine = sa.create_engine(conn_params[dbtype])
Base.prepare(engine, reflect = True)
```

The full stack trace is
```
Traceback (most recent call last):
  File ".\TEST.py", line 18, in <module>
    Base.prepare(engine, reflect = True)
  File "F:\PortableApps\Python\lib\site-packages\sqlalchemy\ext\automap.py", line 769, in prepare
    lcl_m2m, rem_m2m, m2m_const = _is_many_to_many(cls, table)
  File "F:\PortableApps\Python\lib\site-packages\sqlalchemy\ext\automap.py", line 875, in _is_many_to_many
    fk_constraints[0].elements[0].column.table,
  File "F:\PortableApps\Python\lib\site-packages\sqlalchemy\util\langhelpers.py", line 767, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "F:\PortableApps\Python\lib\site-packages\sqlalchemy\sql\schema.py", line 1903, in column
    tablekey, colname)
sqlalchemy.exc.NoReferencedColumnError: Could not initialize target column for ForeignKey 'Track.trackid' on table 'PlaylistTrack': table 'Track' has no column named 'trackid'
```

Code for the foreign key in question (from "Chinook_MySql.sql"):
```
CREATE TABLE `Track`
(
    `TrackId` INT NOT NULL,
-- [...]
    CONSTRAINT `PK_Track` PRIMARY KEY  (`TrackId`)
);

ALTER TABLE `PlaylistTrack` ADD CONSTRAINT `FK_PlaylistTrackTrackId`
    FOREIGN KEY (`TrackId`) REFERENCES `Track` (`TrackId`) ON DELETE NO ACTION ON UPDATE NO ACTION;
```

[Sqlalchemy-tickets] Issue #4343: Deleting middle of many-to-many-to-one relationship via query fails (zzzeek/sqlalchemy)

[John T. W. I. <iss...@bi...>]

New issue 4343: Deleting middle of many-to-many-to-one relationship via query fails
https://bitbucket.org/zzzeek/sqlalchemy/issues/4343/deleting-middle-of-many-to-many-to-one

John Thorvald Wodder II:

The following code declares three classes joined by two many-to-one
relationships, creates & adds an object of each class, and then deletes the
"middle" object:

    import sqlalchemy as S
    from   sqlalchemy.ext.declarative import declarative_base
    from   sqlalchemy.orm             import backref, relationship, sessionmaker

    Base = declarative_base()

    class Project(Base):
        __tablename__ = 'projects'

        id = S.Column(S.Integer, primary_key=True, nullable=False)
        name = S.Column(S.Unicode(2048), nullable=False)

        def __repr__(self):
            return 'Project(name={!r})'.format(self.name)


    class Release(Base):
        __tablename__ = 'releases'

        id = S.Column(S.Integer, primary_key=True, nullable=False)
        version = S.Column(S.Unicode(2048), nullable=False)
        project_id = S.Column(S.Integer, S.ForeignKey('projects.id'), nullable=False)
        project = relationship('Project', backref=backref('releases', cascade='all, delete, delete-orphan'))

        def __repr__(self):
            return 'Release(project={!r}, version={!r})'\
                    .format(self.project, self.version)


    class Asset(Base):
        __tablename__ = 'assets'

        id = S.Column(S.Integer, primary_key=True, nullable=False)
        filename = S.Column(S.Unicode(2048), nullable=False)
        release_id = S.Column(S.Integer, S.ForeignKey('releases.id'), nullable=False)
        release = relationship('Release', backref=backref('assets', cascade='all, delete, delete-orphan'))

        def __repr__(self):
            return 'Asset(release={!r}, filename={!r})'\
                    .format(self.release, self.filename)


    engine = S.create_engine('sqlite:///:memory:')
    Base.metadata.create_all(engine)
    session = sessionmaker(bind=engine)()
    session.execute("PRAGMA foreign_keys=ON")

    p = Project(name='FooBar')
    session.add(p)
    r = Release(project=p, version='1.0')
    session.add(r)
    a = Asset(release=r, filename='FooBar-1.0.tar.gz')
    session.add(a)

    print(session.query(Project).all())
    print(session.query(Release).all())
    print(session.query(Asset).all())

    #session.delete(r)  # Good
    session.query(Release).filter(Release.project == p).delete()  # Bad

    print()
    print(session.query(Project).all())
    print(session.query(Release).all())
    print(session.query(Asset).all())

If the deletion is performed with `session.delete(r)`, then the code works as
intended, with all of `r`'s assets (`a`) being deleted along with it.  However,
if `r` is instead deleted by query (the line marked "`# Bad`"), then we get
this error:

    Traceback (most recent call last):
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
        context)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
        cursor.execute(statement, parameters)
    sqlite3.IntegrityError: FOREIGN KEY constraint failed

    The above exception was the direct cause of the following exception:

    Traceback (most recent call last):
      File "delcache3.py", line 62, in <module>
        session.query(Release).filter(Release.project == p).delete()
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3353, in delete
        delete_op.exec_()
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1329, in exec_
        self._do_exec()
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1521, in _do_exec
        self._execute_stmt(delete_stmt)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1336, in _execute_stmt
        mapper=self.mapper)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1176, in execute
        bind, close_with_result=True).execute(clause, params or {})
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
        return meth(self, multiparams, params)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
        return connection._execute_clauseelement(self, multiparams, params)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
        compiled_sql, distilled_params
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
        context)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
        exc_info
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
        reraise(type(exception), exception, tb=exc_tb, cause=cause)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
        raise value.with_traceback(tb)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
        context)
      File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
        cursor.execute(statement, parameters)
    sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) FOREIGN KEY constraint failed [SQL: 'DELETE FROM releases WHERE ? = releases.project_id'] [parameters: (1,)] (Background on this error at: http://sqlalche.me/e/gkpj)

If the `session.execute("PRAGMA foreign_keys=ON")` line is commented out, then
the delete query completes without error, but the script's output then shows
that `a` is not deleted, and adding the line
`print(session.query(Asset).first().release_id)` shows that `a.release_id` is
still pointing to a `Release` that no longer exists.

If we instead use PostgreSQL rather than SQLite, we get a slightly more
informative error:

    Traceback (most recent call last):
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
        context)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
        cursor.execute(statement, parameters)
    psycopg2.IntegrityError: update or delete on table "releases" violates foreign key constraint "assets_release_id_fkey" on table "assets"
    DETAIL:  Key (id)=(1) is still referenced from table "assets".


    The above exception was the direct cause of the following exception:

    Traceback (most recent call last):
      File "delcache3.py", line 70, in <module>
        session.query(Release).filter(Release.project == p).delete()
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 3353, in delete
        delete_op.exec_()
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 1329, in exec_
        self._do_exec()
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 1521, in _do_exec
        self._execute_stmt(delete_stmt)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 1336, in _execute_stmt
        mapper=self.mapper)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 1176, in execute
        bind, close_with_result=True).execute(clause, params or {})
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 948, in execute
        return meth(self, multiparams, params)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
        return connection._execute_clauseelement(self, multiparams, params)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
        compiled_sql, distilled_params
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
        context)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
        exc_info
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
        reraise(type(exception), exception, tb=exc_tb, cause=cause)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
        raise value.with_traceback(tb)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
        context)
      File "/home/jwodder/tmp/delcache/venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
        cursor.execute(statement, parameters)
    sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) update or delete on table "releases" violates foreign key constraint "assets_release_id_fkey" on table "assets"
    DETAIL:  Key (id)=(1) is still referenced from table "assets".
     [SQL: 'DELETE FROM releases WHERE %(param_1)s = releases.project_id'] [parameters: {'param_1': 1}] (Background on this error at: http://sqlalche.me/e/gkpj)

This problem was observed on the following platforms:

- SQLAlchemy version: 1.2.12  
  Python version: 3.5.2  
  OS: Ubuntu Xenial 16.04.5  
  Database: Python's built-in SQLite3 (`sqlite3.version = '2.6.0'`, `sqlite3.sqlite_version = '3.11.0'`)

- SQLAlchemy version: 1.2.12  
  Python version: 3.7.0  
  OS: Mac OS X 10.13.6  
  Database: Python's built-in SQLite3 (`sqlite3.version = '2.6.0'`, `sqlite3.sqlite_version = '3.24.0'`)

- SQLAlchemy version: 1.2.12  
  Python version: 3.5.2  
  OS: Ubuntu Xenial 16.04.5  
  Database: PostgreSQL 9.6.10-1.pgdg90+1  
  Driver: psycopg2 (installed via `psycopg2-binary` v2.7.5)

[Sqlalchemy-tickets] Issue #4342: Unable to use cascading deletion in SQLite database (zzzeek/sqlalchemy)

[wuhuanyan <iss...@bi...>]

New issue 4342: Unable to use cascading deletion in SQLite database
https://bitbucket.org/zzzeek/sqlalchemy/issues/4342/unable-to-use-cascading-deletion-in-sqlite

wuhuanyan:

I use SQLite database to test cascading deletion.

It is found that the MySQL database can be cascaded and deleted normally.

However, SQLite database cannot be cascaded and deleted.



Is the Interface class or InterfaceSource class definition incorrect?

Sqlalchemy:1.2.12

The test code is as follows:

```
#!python

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint
from sqlalchemy.orm import relationship, backref, Session
import sqlalchemy as sqla

Base = declarative_base()


class Interface(Base):
    __tablename__ = 't_interface'
    id = Column(Integer, primary_key=True)
    interface_name = Column(String(100), unique=False, nullable=True)
    interfacesource = relationship("InterfaceSource", cascade="all, delete-orphan", passive_deletes=True)


class InterfaceSource(Base):
    __tablename__ = 't_interface_source'
    id = Column(Integer, primary_key=True)
    interface_id = Column(Integer, ForeignKey('t_interface.id', ondelete='CASCADE'))
    interface = relationship("Interface")
    table_name = Column(String(100), unique=False, nullable=True)


def get_local_db():
    ip_address = 'localhost'
    username = 'root'
    password = 'Mysql123'
    schema = 'datatools'
    port = '3306'
    uri = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(username, password, ip_address, port, schema)
    engine = sqla.create_engine(uri)
    Base.metadata.create_all(engine)
    return engine


def get_sqlite3_db():
    sqlite3_path = 'sqlite:{}'.format('///../datatools1.db')
    engine = sqla.create_engine(sqlite3_path)
    Base.metadata.create_all(engine)
    return engine


def insert(db):
    session = Session(db)

    interface = Interface()  # create Interface object
    interface.id = 1
    interface.interface_name = 'interface one'

    interface_source = InterfaceSource()  # create InterfaceSource object
    interface_source.id = 1
    interface_source.interface_id = 1
    interface_source.table_name = 'table one'

    session.add(interface)
    session.add(interface_source)
    session.commit()
    session.close()
    print('insert ok')


def select(db):
    session = Session(db)
    q_obj = session.query(Interface).filter(Interface.id == 1)
    for q in q_obj.all():
        print(q.id, q.interface_name)
    q_obj = session.query(InterfaceSource).filter(InterfaceSource.id == 1)
    for q in q_obj.all():
        print(q.id, q.interface_id, q.table_name)
    session.close()
    print('select ok')


def delete(db):
    session = Session(db)
    session.query(Interface).filter(Interface.id == 1).delete()
    session.commit()
    session.close()
    print('delete ok')


mysql_db = get_local_db()  # get mysql engine
sqlite_db = get_sqlite3_db()  # get sqlite engine
print('mysql')
insert(mysql_db)
select(mysql_db)
delete(mysql_db)
select(mysql_db)
print('\n\n')
print('sqlite')
insert(sqlite_db)
select(sqlite_db)
delete(sqlite_db)
select(sqlite_db)
```
![1537950826380.jpg](https://bitbucket.org/repo/ngx7X7k/images/2304889715-1537950826380.jpg)

[Sqlalchemy-tickets] Issue #4341: create_constraint=False not respected when inheritted (zzzeek/sqlalchemy)

[moon c. <iss...@bi...>]

New issue 4341: create_constraint=False not respected when inheritted
https://bitbucket.org/zzzeek/sqlalchemy/issues/4341/create_constraint-false-not-respected-when

moon cake:

Test case:
```py
import enum

from sqlalchemy import Column, Enum, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import CreateTable


TableBase = declarative_base()


class Language(enum.Enum):
    english = 'English'


class Test(TableBase):
    __tablename__ = 'enum'

    id = Column(Integer, primary_key=True)
    language = Column(Enum(Language, native_enum=False, create_constraint=False))


class TestBase(TableBase):
    __abstract__ = True

    id = Column(Integer, primary_key=True)
    language = Column(Enum(Language, native_enum=False, create_constraint=False))


class TestInheritted(TestBase):
    __tablename__ = 'enum_inheritted'


print(CreateTable(Test.__table__))
print(CreateTable(TestInheritted.__table__))
```
Output:
```
CREATE TABLE enum (
        id INTEGER NOT NULL,
        language VARCHAR(7),
        PRIMARY KEY (id)
)



CREATE TABLE enum_inheritted (
        id INTEGER NOT NULL,
        language VARCHAR(7),
        PRIMARY KEY (id),
        CONSTRAINT language CHECK (language IN ('english'))
)
```

[Sqlalchemy-tickets] Issue #4340: Allow selectinload to skip join and filter directly on the target table (zzzeek/sqlalchemy)

[Jayson R. <iss...@bi...>]

New issue 4340: Allow selectinload to skip join and filter directly on the target table
https://bitbucket.org/zzzeek/sqlalchemy/issues/4340/allow-selectinload-to-skip-join-and-filter

Jayson Reis:

This is a proposal to change how selectinload runs the query for 1xN queries.

I put an example [1] that will show you sort of how my data is structured so you can have an idea, but the gist is:

I have a main table (like customers) and another table (like order) which has the detailed data and then I created a view where summarizes customer’s orders.

The problem is that I am using selectinload and it runs the query on the view joining customer’s table and PostgreSQL won’t be able to use the proper indexes and will run it slowly. 

Here [2] you can see the output of the example with explain analyze of both possible queries.

When I run query(User).options(selectinload(SummarizedOrder)).all() it will run the query like this:

```sql
SELECT "fields"
FROM "user" AS "user_1"
       JOIN "view_summarized_order" ON "user_1"."id" = "view_summarized_order"."user_id"
WHERE "user_1"."id" IN (%(primary_keys_1) s, %(primary_keys_2) s)
ORDER BY "user_1"."id"
```

But to make it faster, it could be this:

```sql
SELECT "fields"
FROM "view_summarized_order"
WHERE "view_summarized_order"."user_id" IN (%(primary_keys_1) s, %(primary_keys_2) s)
ORDER BY "view_summarized_order"."user_id"
```

In my production database, the first one will take around 8 seconds to run and the second one around 100ms.

Talking on the mailing list Michael Bayer suggests having a flag called omit_join on selectinload to deal with this edge case without breaking compatibility.
Here [3] is a working in progress pull request.

[1] https://gist.github.com/jaysonsantos/e19af47ac5d57aa5e2e2a7ed2a950994

[2] https://gist.github.com/jaysonsantos/e19af47ac5d57aa5e2e2a7ed2a950994#file-2_output-txt

[3] https://bitbucket.org/zzzeek/sqlalchemy/pull-requests/7/selectinload-omit-join/diff#comment-76901579

[Sqlalchemy-tickets] Issue #4339: DeprecationWarnings on Python 3.7 (zzzeek/sqlalchemy)

[Karthikeyan <iss...@bi...>]

New issue 4339: DeprecationWarnings on Python 3.7
https://bitbucket.org/zzzeek/sqlalchemy/issues/4339/deprecationwarnings-on-python-37

Karthikeyan:

Trying to use the project on CPython master generates the below warnings. I think these were added in Python 3.7 and CPython master is the development branch for 3.8 release. Hence this might cause incompatibility with SQLAlchemy in the future. I don't know if there are other places where this causes issue. I came across this while using records which SQLAlchemy under the hood.

```
#!python

/home/karthi/records-venv/lib/python3.8/site-packages/sqlalchemy/sql/base.py:49: DeprecationWarning: Using or importing the ABCs from 'collections' instead of from 'collections.abc' is deprecated, and in 3.8 it will stop working
  class _DialectArgView(collections.MutableMapping):

/home/karthi/records-venv/lib/python3.8/site-packages/sqlalchemy/engine/result.py:182: DeprecationWarning: Using or importing the ABCs from 'collections' instead of from 'collections.abc' is deprecated, and in 3.8 it will stop working
  from collections import Sequence
```

CPython commit that added the warning : https://github.com/python/cpython/commit/c66f9f8d3909f588c251957d499599a1680e2320

Relevant files : 

https://bitbucket.org/zzzeek/sqlalchemy/src/0e1ee3f5ce6af9b20dab6593f4a34f3bf1871f84/lib/sqlalchemy/sql/base.py#lines-49

https://bitbucket.org/zzzeek/sqlalchemy/src/0e1ee3f5ce6af9b20dab6593f4a34f3bf1871f84/lib/sqlalchemy/engine/result.py#lines-182

Thanks much for SQLAlchemy :)

[Sqlalchemy-tickets] Issue #4338: Docs search does not return results (zzzeek/sqlalchemy)

[Steve P. <iss...@bi...>]

New issue 4338: Docs search does not return results
https://bitbucket.org/zzzeek/sqlalchemy/issues/4338/docs-search-does-not-return-results

Steve Piercy:

Within the last day, perhaps just after the release of 1.2.12, I noticed that trying to search the docs no longer works. When I enter a search term, I see the word "Search" then successive periods appearing until three appear,  then repeat.

Example search URL:
https://docs.sqlalchemy.org/en/latest/search.html?q=many-to-many&check_keywords=yes&area=default#

In the browser's developer tools console, there is this error message:


```
#!javascript

searchtools.js:144 Uncaught ReferenceError: Stemmer is not defined
    at Object.query (searchtools.js:144)
    at Object.setIndex (searchtools.js:83)
    at <anonymous>:1:8
    at p (jquery.js:2)
    at Function.globalEval (jquery.js:2)
    at text script (jquery.js:4)
    at Qb (jquery.js:4)
    at A (jquery.js:4)
    at XMLHttpRequest.<anonymous> (jquery.js:4)

```

Tried in both Firefox and Chrome with similar results.

[Sqlalchemy-tickets] Issue #4337: aggregate_order_by doesn't accept multiple columns as order_by argument (zzzeek/sqlalchemy)

[Pierre C. <iss...@bi...>]

New issue 4337: aggregate_order_by  doesn't accept multiple columns as order_by argument
https://bitbucket.org/zzzeek/sqlalchemy/issues/4337/aggregate_order_by-doesnt-accept-multiple

Pierre Collet:

Hello,

The [aggregate_order_by](http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.aggregate_order_by) function currently take only 2 arguments (target and order_by) while PostgreSQL (9.6 in my case) totally accept multiple columns in the ORDER BY clause.


For instance:
```
#!sql

SELECT array_agg(status ORDER BY pk_col1, pk_col2)
FROM table_with_pk_on_two_columns
GROUP BY  col_x;
```

As Mike Bayer emphasized ([here](https://groups.google.com/forum/#!topic/sqlalchemy/A_jfE8hOQHQ) ), it is possible to use a sqlalchemy.sql.expression.ClauseList to specified multiple columns as order_by argument.

Example:

```
#!python

from sqlalchemy.dialects.postgresql import aggregate_order_by 
from sqlalchemy import column 
from sqlalchemy.dialects import postgresql 
from sqlalchemy.sql.expression import ClauseList 

elem = aggregate_order_by(column('a'), ClauseList(column('b').asc(), column('c').desc())) 

print(elem.compile(dialect=postgresql.dialect())) 
a ORDER BY b ASC, c DESC 
```


Still, it would be convenient to make the order_by argument a list (*order_by).


Pierre.

[Sqlalchemy-tickets] Issue #4336: BooleanClauseList.compare fails when an expression uses func.cast (zzzeek/sqlalchemy)

[miso <iss...@bi...>]

New issue 4336: BooleanClauseList.compare fails when an expression uses func.cast
https://bitbucket.org/zzzeek/sqlalchemy/issues/4336/booleanclauselistcompare-fails-when-an

miso:

Hello.

I am using the method [compare](https://docs.sqlalchemy.org/en/latest/core/sqlelement.html?highlight=compare#sqlalchemy.sql.expression.ClauseList.compare) of some classes to perform unit test on a function that creates query expressions dynamically.

Two identical expressions fail to compare when **func.cast** is used, even when their string representations are exactly the same.

Here I am leaving a script to test this issue. In the last line, the compare method returns **False**.

```
#!python

from sqlalchemy import func, Column, Integer, String, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import or_

Base = declarative_base()


class Cartoon(Base):
    __tablename__ = 'cartoon'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    nickname = Column(String)
    age = Column(Integer)


# Create two identical expressions
A = or_(Cartoon.name.ilike('%black%'), Cartoon.nickname.ilike('%black%'))
B = or_(Cartoon.name.ilike('%black%'), Cartoon.nickname.ilike('%black%'))

assert str(A) == str(B)  # Ok
assert A.compare(B)  # Ok


# Create two identical expressions using cast
C = or_(Cartoon.name.ilike('%black%'), func.cast(Cartoon.age, Text).ilike('%black%'))
D = or_(Cartoon.name.ilike('%black%'), func.cast(Cartoon.age, Text).ilike('%black%'))

assert str(C) == str(D)  # The string expressions are equal
assert C.compare(D)  # Here the comparison fails
```

[Sqlalchemy-tickets] Issue #4335: TypeError: unhashable type: 'list' with Oracle Sequence sqlalchemy (zzzeek/sqlalchemy)

[Dileep J. <iss...@bi...>]

New issue 4335: TypeError: unhashable type: 'list' with Oracle Sequence sqlalchemy
https://bitbucket.org/zzzeek/sqlalchemy/issues/4335/typeerror-unhashable-type-list-with-oracle

Dileep Jayamal:

I'm trying to make **id** column **auto-increment** with **Sequence** in Oracle 12c as shown bellow:
```
class APIKey(db.Model):
     __tablename__ = 'APIKey'
    apikey_id_seq = db.Sequence('apikey_id_seq', metadata=db.Model.metadata)
    id = db.Column(db.Integer, apikey_id_seq, server_default= apikey_id_seq.next_value(),primary_key=True)
    name = db.Column(db.String(100))
    username = db.Column(db.String(20), index=True, unique=True)
    key = db.Column(db.String(40))
```
I created the the schema and add an object like bellow:
```
if __name__ == '__main__':
    db.drop_all()
    db.create_all()
    apikey = FingerAPIKey(name='dileep', username='dileep', key='1234')
    db.session.add(apikey)
    db.session.commit()
    app.run(host=host, port=port)
```
During the commit I get following error:
```
File "/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1688, in _register_newly_persistent
    if _none_set.intersection(instance_key[1]) and \
TypeError: unhashable type: 'list'
```

[Sqlalchemy-tickets] Issue #4334: Query.select_from_entity doesn't apply adaptation in the context of lateral joins (zzzeek/sqlalchemy)

[zifot <iss...@bi...>]

New issue 4334: Query.select_from_entity doesn't apply adaptation in the context of lateral joins
https://bitbucket.org/zzzeek/sqlalchemy/issues/4334/queryselect_from_entity-doesnt-apply

zifot:

Based on the documentation for [`select_entity_from`](http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.select_entity_from), specifically the part stating that* "it additionally applies adaptation to the other parts of the query that refer to the primary entity"*, I was expecting a proper adaptation inside lateral join subquery. This seems not to be the case:

```python
from sqlalchemy import create_engine, Column, Integer, Text, ForeignKey
from sqlalchemy.orm import Query
from sqlalchemy.ext.declarative import declarative_base


engine = create_engine('sqlite:///test.db')
Base = declarative_base()


class Parent(Base):
    __tablename__ = 'parents'

    id = Column(Integer, primary_key=True)
    name = Column(Text)


class Child(Base):
    __tablename__ = 'children'

    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey(Parent.id))
    name = Column(Text)


Base.metadata.create_all(engine)


parents_subquery = (
    Query(Parent)
    .subquery()
)
child_subquery = (
    Query(Child)
    .filter(Child.name == Parent.name)
    .subquery()
    .lateral()
)

print(
    '\n---Entity directly: As expected, here joined subquery correlates to parents table of'
    ' root query'
)
q = Query(Parent)
q = q.outerjoin(child_subquery)
q = q.filter(Parent.name == 'y')
print(q)

print(
    '\n---Entity via select_entity_from: Here joined subquery uses its own'
    ' instance of parents table (although final where gets adapted properly) '
)
q = Query(Parent).select_entity_from(parents_subquery)
q = q.outerjoin(child_subquery)
q = q.filter(Parent.name == 'y')
print(q)
```

This results in:

```shell
---Entity directly: As expected, here joined subquery correlates to parents table of root query
SELECT parents.id AS parents_id, parents.name AS parents_name 
FROM parents LEFT OUTER JOIN LATERAL (SELECT children.id AS id, children.parent_id AS parent_id, children.name AS name 
FROM children 
WHERE children.name = parents.name) AS anon_1 ON parents.id = anon_1.parent_id 
WHERE parents.name = :name_1

---Entity via select_entity_from: Here joined subquery uses its own instance of parents table (although final where gets adapted properly) 
SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name 
FROM (SELECT parents.id AS id, parents.name AS name 
FROM parents) AS anon_1 LEFT OUTER JOIN LATERAL (SELECT children.id AS id, children.parent_id AS parent_id, children.name AS name 
FROM children, parents 
WHERE children.name = parents.name) AS anon_2 ON anon_1.id = anon_2.parent_id 
WHERE anon_1.name = :name_1
```

Using `parents_subquery.c.name` instead of `Parent.name` when constructing `child_subquery` yields expected statement, but that requires tagging along a reference to subquery everywhere it's needed and seems to defeat the purpose of `select_entity_from`.

Tested on SQLAlchemy 1.1.6 and 1.2.11.

[Sqlalchemy-tickets] Issue #4333: Add case insensitive option to startswith (zzzeek/sqlalchemy)

[Alex R. <iss...@bi...>]

New issue 4333: Add case insensitive option to startswith
https://bitbucket.org/zzzeek/sqlalchemy/issues/4333/add-case-insensitive-option-to-startswith

Alex Rothberg:

Right now `startswith` uses `LIKE`. It would be good if there were a flag it took that would use `ILIKE`.

[Sqlalchemy-tickets] Issue #4332: hybrid_property gives AttributeError (zzzeek/sqlalchemy)

[René S. <iss...@bi...>]

New issue 4332: hybrid_property gives AttributeError
https://bitbucket.org/zzzeek/sqlalchemy/issues/4332/hybrid_property-gives-attributeerror

René Steetskamp:

I have a hybrid property "password" which used to work but after a package update gave "AttributeError: can't set attribute". 

Downgrading to "sqlalchemy<1.2" fixed the issue.

Using sqlite 3.22.0, flask and flask-sqlachemy.

Pipfile, execution+stack traces and test script attached.

[Sqlalchemy-tickets] Issue #4331: dialects/mysql/types.py:BIT.result_processor - "'int' object is not iterable" with some drivers (zzzeek/sqlalchemy)

[mark g. <iss...@bi...>]

New issue 4331: dialects/mysql/types.py:BIT.result_processor - "'int' object is not iterable" with some drivers
https://bitbucket.org/zzzeek/sqlalchemy/issues/4331/dialects-mysql-typespy-bitresult_processor

mark grandi:

**python version:** `Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 26 2018, 23:26:24)
[Clang 6.0 (clang-600.0.57)] on darwin`

**mysql version:** `/usr/local/mysql/bin/mysql  Ver 8.0.11 for macos10.13 on x86_64 (MySQL Community Server - GPL)`

**sqlalchemy version:** `1.2.11`

**drivers used:** `mysql-connector-python` and `pymysql`

**Description:** 
There seems to be a curious inconsistency with the BIT class inside `dialects/mysql/types.py` , which is provided below:

```
    def result_processor(self, dialect, coltype):
        """Convert a MySQL's 64 bit, variable length binary string to a long.

        TODO: this is MySQL-db, pyodbc specific.  OurSQL and mysqlconnector
        already do this, so this logic should be moved to those dialects.

        """

        def process(value):
            if value is not None:
                v = 0
                for i in value:
                    if not isinstance(i, int):
                        i = ord(i)  # convert byte to int on Python 2
                    v = v << 8 | i
                return v
            return value
        return process
```

It seems that some mysql drivers end up calling this class, while others don't (as the comment suggests) , but that leads to a difference in behavior.

 It seems that if you are using a driver that ends up calling BIT.result_processor to process BIT types, and have a mysql table that has a column that is of type `BIT(1)`, but your SQLAlchemy declarative model class says the column is a `Boolean` (aka `tinyint`), SQLAlchemy throws an exception because a `int` is not iterable, and in `BIT.result_processor`, it assumes that the data type is iterable (because it is of type `bytes` I guess) . 

I noticed this when switching drivers because i wanted server side cursors for a large data migration project i'm doing for work, and the database we migrated from was using a old version of the schema where we didn't standardize on the data type we use for booleans. I solved this locally by changing the `if` statement to:

```
if value is not None and not isinstance(value, int):
```

Attached is a script that demonstrates the problem, to run it, create a virtualenv and `pip3 install SQLAlchemy, mysql-connector-python, PyMySQL` , and set the database host/port/username/password in the `connection_dict` variable at the top

This is the output when running it on my own instance of mysql:

```(.venv) [2018-09-10 12:47:26] markgrandi@Gypaetus:~/Temp/sqla_bit_testing$ python3 test.py
2018-09-10 12:47:33,857 MainThread           root                                INFO    : mysqlconnector test starting
2018-09-10 12:47:33,857 MainThread           root.mysqlconnector.boolean         INFO    : creating and inserting rows with a SQLAlchemy table that has a Boolean column
2018-09-10 12:47:33,857 MainThread           root.mysqlconnector.boolean         INFO    : url is: `mysql+mysqlconnector://root:SNIP@localhost:3306/testing`
2018-09-10 12:47:33,899 MainThread           root.mysqlconnector.boolean         INFO    : engine: `Engine(mysql+mysqlconnector://root:***@localhost:3306/testing)`, session: `<sqlalchemy.orm.session.Session object at 0x108fdda58>`
2018-09-10 12:47:33,899 MainThread           root.mysqlconnector.boolean         INFO    : calling create_all on the metadata `MetaData(bind=None)`
2018-09-10 12:47:34,011 MainThread           root.mysqlconnector.boolean         INFO    : truncating the table to get rid of any existing rows
2018-09-10 12:47:34,077 MainThread           root.mysqlconnector.boolean         INFO    : adding rows to table
2018-09-10 12:47:34,079 MainThread           root.mysqlconnector.boolean         INFO    : adding row `<TestTableWithBoolean id=None colone=1932 coltwo=37bca76085f54bcb8767889b567be586 colthree=False >`
2018-09-10 12:47:34,079 MainThread           root.mysqlconnector.boolean         INFO    : adding row `<TestTableWithBoolean id=None colone=3932 coltwo=9f8fcc64d69e447ea860c183c800cd89 colthree=False >`
2018-09-10 12:47:34,110 MainThread           root.mysqlconnector.boolean         INFO    : querying for existing rows
2018-09-10 12:47:34,117 MainThread           root.mysqlconnector.boolean         INFO    : got row: `<TestTableWithBoolean id=1 colone=1932 coltwo=37bca76085f54bcb8767889b567be586 colthree=False >`
2018-09-10 12:47:34,118 MainThread           root.mysqlconnector.boolean         INFO    : got row: `<TestTableWithBoolean id=2 colone=3932 coltwo=9f8fcc64d69e447ea860c183c800cd89 colthree=False >`
2018-09-10 12:47:34,120 MainThread           root.mysqlconnector.boolean         INFO    : session closed, engine disposed
2018-09-10 12:47:34,120 MainThread           root.mysqlconnector.bit             INFO    : printing out rows that were created with a SQLAlchemy table that has a Boolean column, but now we use a SQLAlchemy table that has a BIT column
2018-09-10 12:47:34,120 MainThread           root.mysqlconnector.bit             INFO    : url is: `mysql+mysqlconnector://root:SNIP@localhost:3306/testing`
2018-09-10 12:47:34,123 MainThread           root.mysqlconnector.bit             INFO    : engine: `Engine(mysql+mysqlconnector://root:***@localhost:3306/testing)`, session: `<sqlalchemy.orm.session.Session object at 0x10904dac8>`
2018-09-10 12:47:34,123 MainThread           root.mysqlconnector.bit             INFO    : querying for existing rows
2018-09-10 12:47:34,196 MainThread           root.mysqlconnector.bit             INFO    : got row: `<TestTableWithBit id=1 colone=1932 coltwo=37bca76085f54bcb8767889b567be586 colthree=0 >`
2018-09-10 12:47:34,196 MainThread           root.mysqlconnector.bit             INFO    : got row: `<TestTableWithBit id=2 colone=3932 coltwo=9f8fcc64d69e447ea860c183c800cd89 colthree=0 >`
2018-09-10 12:47:34,197 MainThread           root.mysqlconnector.bit             INFO    : session closed, engine disposed
2018-09-10 12:47:34,198 MainThread           root                                INFO    : pymysql test starting
2018-09-10 12:47:34,198 MainThread           root.pymysql.boolean                INFO    : creating and inserting rows with a SQLAlchemy table that has a Boolean column
2018-09-10 12:47:34,198 MainThread           root.pymysql.boolean                INFO    : url is: `mysql+pymysql://root:SNIP@localhost:3306/testing`
2018-09-10 12:47:34,268 MainThread           root.pymysql.boolean                INFO    : engine: `Engine(mysql+pymysql://root:***@localhost:3306/testing)`, session: `<sqlalchemy.orm.session.Session object at 0x1091f0908>`
2018-09-10 12:47:34,268 MainThread           root.pymysql.boolean                INFO    : calling create_all on the metadata `MetaData(bind=None)`
2018-09-10 12:47:34,294 MainThread           root.pymysql.boolean                INFO    : truncating the table to get rid of any existing rows
2018-09-10 12:47:34,347 MainThread           root.pymysql.boolean                INFO    : adding rows to table
2018-09-10 12:47:34,348 MainThread           root.pymysql.boolean                INFO    : adding row `<TestTableWithBoolean id=None colone=4765 coltwo=e31402acaa6c44a7a606ee73062e1813 colthree=False >`
2018-09-10 12:47:34,349 MainThread           root.pymysql.boolean                INFO    : adding row `<TestTableWithBoolean id=None colone=5516 coltwo=c56e4c9c3113467eb63dc419fd1768da colthree=False >`
2018-09-10 12:47:34,361 MainThread           root.pymysql.boolean                INFO    : querying for existing rows
2018-09-10 12:47:34,364 MainThread           root.pymysql.boolean                INFO    : got row: `<TestTableWithBoolean id=1 colone=4765 coltwo=e31402acaa6c44a7a606ee73062e1813 colthree=False >`
2018-09-10 12:47:34,365 MainThread           root.pymysql.boolean                INFO    : got row: `<TestTableWithBoolean id=2 colone=5516 coltwo=c56e4c9c3113467eb63dc419fd1768da colthree=False >`
2018-09-10 12:47:34,366 MainThread           root.pymysql.boolean                INFO    : session closed, engine disposed
2018-09-10 12:47:34,367 MainThread           root.pymysql.bit                    INFO    : printing out rows that were created with a SQLAlchemy table that has a Boolean column, but now we use a SQLAlchemy table that has a BIT column
2018-09-10 12:47:34,367 MainThread           root.pymysql.bit                    INFO    : url is: `mysql+pymysql://root:SNIP@localhost:3306/testing`
2018-09-10 12:47:34,377 MainThread           root.pymysql.bit                    INFO    : engine: `Engine(mysql+pymysql://root:***@localhost:3306/testing)`, session: `<sqlalchemy.orm.session.Session object at 0x1091dc748>`
2018-09-10 12:47:34,378 MainThread           root.pymysql.bit                    INFO    : querying for existing rows
Traceback (most recent call last):
  File "test.py", line 171, in <module>
    app.test_with_pymysql()
  File "test.py", line 54, in test_with_pymysql
    self.print_out_rows_with_bit_column("mysql+pymysql", lg)
  File "test.py", line 90, in print_out_rows_with_bit_column
    for i in session.query(TestTableWithBit).all():
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 2836, in all
    return list(self)
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 98, in instances
    util.raise_from_cause(err)
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 249, in reraise
    raise value
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 79, in instances
    rows = [proc(row) for row in fetch]
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 79, in <listcomp>
    rows = [proc(row) for row in fetch]
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 511, in _instance
    loaded_instance, populate_existing, populators)
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 611, in _populate_full
    dict_[key] = getter(row)
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/types.py", line 371, in process
    for i in value:
TypeError: 'int' object is not iterable```

[Sqlalchemy-tickets] Issue #4330: Should JSON type default to 'jsonb' on Postgres? (zzzeek/sqlalchemy)

[Mark A. <iss...@bi...>]

New issue 4330: Should JSON type default to 'jsonb' on Postgres?
https://bitbucket.org/zzzeek/sqlalchemy/issues/4330/should-json-type-default-to-jsonb-on

Mark Amery:

https://www.postgresql.org/docs/current/static/datatype-json.html recommends using jsonb over json for most use cases, since its performance is superior. (The json type just stores the input as text, which has to be parsed in order to do any queries, and keys within the json can't be indexed. jsonb stores a proper structured representation of the given JSON that can be indexed for faster querying.) This guidance seems obviously good to me.

For that reason, wouldn't it make sense for sqlalchemy.types.JSON to translate to jsonb on Postgres instead of json?

Besides the "jsonb is just plain better in almost all cases" argument, it also seems relevant that PostgreSQL's "jsonb" type is a closer match behaviourally to MySQL's "JSON" type than PostgreSQL's "json" type is. Since SQLAlchemy translates sqlalchemy.types.JSON to "JSON" on MySQL, this perhaps means that translating to "jsonb" on Postgres is more portable?

[Sqlalchemy-tickets] Issue #4329: KeyError when using Association Objects with back_populates – example from documentation doesn't work (zzzeek/sqlalchemy)

[Lars B. <iss...@bi...>]

New issue 4329: KeyError when using Association Objects with back_populates – example from documentation doesn't work
https://bitbucket.org/zzzeek/sqlalchemy/issues/4329/keyerror-when-using-association-objects

Lars Blumberg:

[This is a cross post from StackOverflow](https://stackoverflow.com/questions/52101595/sqlalchemy-throwing-keyerror-when-using-association-objects-with-back-populates)

SQLAlchemy nicely documents how to use Association Objects with back_populates.

However, when copy-and-pasting the example from that documentation, adding children to a parent throws a KeyError as following code shows. The model classes are copied 100% from the documentation:

```
#!python

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.schema import MetaData

Base = declarative_base(metadata=MetaData())

class Association(Base):
    __tablename__ = 'association'
    left_id = Column(Integer, ForeignKey('left.id'), primary_key=True)
    right_id = Column(Integer, ForeignKey('right.id'), primary_key=True)
    extra_data = Column(String(50))
    child = relationship("Child", back_populates="parents")
    parent = relationship("Parent", back_populates="children")

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Association", back_populates="parent")

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    parents = relationship("Association", back_populates="child")

parent = Parent(children=[Child()])
```

Running that code with SQLAlchemy version 1.2.11 throws this exception:

```
lars$ venv/bin/python test.py
Traceback (most recent call last):
  File "test.py", line 26, in <module>
    parent = Parent(children=[Child()])
  File "<string>", line 4, in __init__
  File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/state.py", line 417, in _initialize_instance
    manager.dispatch.init_failure(self, args, kwargs)
  File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 249, in reraise
    raise value
  File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/state.py", line 414, in _initialize_instance
    return manager.original_init(*mixed[1:], **kwargs)
  File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/ext/declarative/base.py", line 737, in _declarative_constructor
    setattr(self, k, kwargs[k])
  File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py", line 229, in __set__
    instance_dict(instance), value, None)
  File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py", line 1077, in set
    initiator=evt)
  File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/collections.py", line 762, in bulk_replace
    appender(member, _sa_initiator=initiator)
  File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/collections.py", line 1044, in append
    item = __set(self, item, _sa_initiator)
  File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/collections.py", line 1016, in __set
    item = executor.fire_append_event(item, _sa_initiator)
  File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/collections.py", line 680, in fire_append_event
    item, initiator)
  File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py", line 943, in fire_append_event
    state, value, initiator or self._append_token)
  File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py", line 1210, in emit_backref_from_collection_append_event
    child_impl = child_state.manager[key].impl
KeyError: 'parent'
```

[Sqlalchemy-tickets] Issue #4328: TypeError when using bakery-cached ShardedQuery for Query (zzzeek/sqlalchemy)

[Carson I. <iss...@bi...>]

New issue 4328: TypeError when using bakery-cached ShardedQuery for Query
https://bitbucket.org/zzzeek/sqlalchemy/issues/4328/typeerror-when-using-bakery-cached

Carson Ip:

Create a bakery, bake a query using ShardedSession such that the cached query is of type ShardedQuery. Then query using a normal Session. Raises TypeError on unexpected kwarg "shard_id". The order of querying using ShardedSession and Session matters.

Stack trace:
```
Traceback (most recent call last):
  File "sqlalchemy/test/ext/test_horizontal_shard.py", line 330, in test_baked_mix
    t = get_tokyo(sess2)
  File "sqlalchemy/test/ext/test_horizontal_shard.py", line 318, in get_tokyo
    t = bq(sess).get(tokyo.id)
  File "sqlalchemy/test/../lib/sqlalchemy/ext/baked.py", line 458, in get
    return query._get_impl(ident, self._load_on_pk_identity)
  File "sqlalchemy/test/../lib/sqlalchemy/orm/query.py", line 1004, in _get_impl
    return db_load_fn(self, primary_key_identity)
  File "sqlalchemy/test/../lib/sqlalchemy/ext/baked.py", line 510, in _load_on_pk_identity
    result = list(bq.for_session(self.session).params(**params))
  File "sqlalchemy/test/../lib/sqlalchemy/ext/baked.py", line 355, in __iter__
    return q._execute_and_instances(context)
  File "sqlalchemy/test/../lib/sqlalchemy/ext/horizontal_shard.py", line 61, in _execute_and_instances
    partial.extend(iter_for_shard(shard_id))
  File "sqlalchemy/test/../lib/sqlalchemy/ext/horizontal_shard.py", line 49, in iter_for_shard
    shard_id=shard_id).execute(
  File "sqlalchemy/test/../lib/sqlalchemy/orm/query.py", line 2998, in _connection_from_session
    conn = self.session.connection(**kw)
  File "sqlalchemy/test/../lib/sqlalchemy/orm/session.py", line 1031, in connection
    bind = self.get_bind(mapper, clause=clause, **kw)
TypeError: get_bind() got an unexpected keyword argument 'shard_id'
```

To reproduce, add this test case under test_horizontal_shard: (I know it is a bad place to put the test case, but it is more convenient to reproduce here)
```
    def test_baked_mix(self):
        sess = self._fixture_data()

        tokyo = sess.query(WeatherLocation).filter_by(city="Tokyo").one()
        tokyo.city
        sess.expunge_all()

        from sqlalchemy.ext.baked import BakedQuery

        bakery = BakedQuery.bakery()

        def get_tokyo(sess):
            bq = bakery(lambda session: session.query(WeatherLocation))
            t = bq(sess).get(tokyo.id)
            return t

        Sess = sessionmaker(class_=Session, bind=db2,
                                      autoflush=True, autocommit=False)
        sess2 = Sess()

        t = get_tokyo(sess)
        eq_(t.city, tokyo.city)

        t = get_tokyo(sess2)
        eq_(t.city, tokyo.city)
```

This bug is not related to database drivers / dialects.

Questions about the fix:

1. Do we really want to cache a ShardedQuery instead of a Query in the bakery?
2. If yes, do we want to reuse a ShardedQuery for a Query? We can simply add the query class to cache key if we forbid this.

I am willing to work on the fix if I know the directions. If it is easier to fix it immediately, we can just save the trouble (of back and forth discussions about the fix). Thanks!

[Sqlalchemy-tickets] Issue #4327: Expire on delete (was not expired before) (zzzeek/sqlalchemy)

[Dmytro S. <iss...@bi...>]

New issue 4327: Expire on delete (was not expired before)
https://bitbucket.org/zzzeek/sqlalchemy/issues/4327/expire-on-delete-was-not-expired-before

Dmytro Starosud:

Regression from 1.1.15 to 1.2.11. Maybe related to [this](https://docs.sqlalchemy.org/en/latest/changelog/changelog_12.html#change-21bf00984dcf04fa68883e4588196816).

Please consider following code snippet.

```
#!python
Base = declarative_base()


class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)

    children = relationship(
        'Child',
        foreign_keys='Child.parent_id', back_populates='parent', cascade='all, delete-orphan')

    first_child_id = Column(Integer, ForeignKey('child.id'))
    first_child = relationship('Child', foreign_keys=first_child_id, post_update=True)

    updated_at = Column(DateTime(), onupdate=func.now())


class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey(Parent.id), nullable=False)
    parent = relationship(Parent, foreign_keys=parent_id, back_populates='children')


Base.metadata.create_all(session.bind, checkfirst=True)

child = Child()
parent = Parent(children=[child], first_child=child)
session.add(parent)
session.flush()
parent_id = parent.id
session.expunge_all()


@event.listens_for(Parent, 'expire')
def receive_expire(target, attrs):
    traceback.print_stack()


parent = session.query(Parent).get(parent_id)
session.delete(parent)
session.flush()

print(parent.updated_at)
```

With SQLAlchemy==1.1.15 it outputs `2018-08-23 12:06:19.291603`, nothing more and no failures.

With SQLAlchemy==1.2.11 it outputs two stack traces: one from expire event and one when trying access `parent.updated_at`.


```
#!console
  File "coze.py", line 62, in <module>
    session.flush()
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2254, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2344, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/unitofwork.py", line 391, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/unitofwork.py", line 542, in execute
    persistence.post_update(self.mapper, states, uow, cols)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 234, in post_update
    mapper, table, update)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 975, in _emit_post_update_statements
    c, c.context.compiled_parameters[0])
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 1163, in _postfetch_post_update
    for c in postfetch_cols if c in
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/state.py", line 593, in _expire_attributes
    self.manager.dispatch.expire(self, attribute_names)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/event/attr.py", line 284, in __call__
    fn(*args, **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/events.py", line 202, in wrap
    return fn(state.obj(), *arg, **kw)
  File "coze.py", line 57, in receive_expire
    traceback.print_stack()
Traceback (most recent call last):
  File "coze.py", line 64, in <module>
    print(parent.updated_at)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/attributes.py", line 242, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/attributes.py", line 594, in get
    value = state._load_expired(state, passive)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/state.py", line 608, in _load_expired
    self.manager.deferred_scalar_loader(self, toload)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/loading.py", line 881, in load_scalar_attributes
    raise orm_exc.ObjectDeletedError(state)
sqlalchemy.orm.exc.ObjectDeletedError: Instance '<Parent at 0x7fa8177dc160>' has been deleted, or its row is otherwise not present.
```

Please clarify. Does this mean deleted instance cannot be used after flush any more?

Thanks a lot in advance.