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

[Sqlalchemy-tickets] Issue #3005: _determine_joins raises TypeError: 'dict_values' object does not support indexing (zzzeek/sqlalchemy)

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

New issue 3005: _determine_joins raises TypeError: 'dict_values' object does not support indexing
https://bitbucket.org/zzzeek/sqlalchemy/issue/3005/_determine_joins-raises-typeerror

Marek Baczyński:

Python 3.4.0, sqlalchemy master

When using sadisplay (https://pypi.python.org/pypi/sadisplay) like this:


```
#!python

desc = sadisplay.describe(getattr(db, attr) for attr in dir(db))
```

I get an exception:


```
#!
  File "...venv\lib\site-packages\sadisplay\describe.py", line 100, in describe
    mapper = class_mapper(item)
  File "...venv\lib\site-packages\sqlalchemy\orm\base.py", line 378, in class_mapper
    mapper = _inspect_mapped_class(class_, configure=configure)
  File "...venv\lib\site-packages\sqlalchemy\orm\base.py", line 355, in _inspect_mapped_class
    mapper._configure_all()
  File "...venv\lib\site-packages\sqlalchemy\orm\mapper.py", line 1129, in _configure_all
    configure_mappers()
  File "...venv\lib\site-packages\sqlalchemy\orm\mapper.py", line 2544, in configure_mappers
    mapper._post_configure_properties()
  File "...venv\lib\site-packages\sqlalchemy\orm\mapper.py", line 1657, in _post_configure_properties
    prop.init()
  File "...venv\lib\site-packages\sqlalchemy\orm\interfaces.py", line 143, in init
    self.do_init()
  File "...venv\lib\site-packages\sqlalchemy\orm\relationships.py", line 1510, in do_init
    self._setup_join_conditions()
  File "...venv\lib\site-packages\sqlalchemy\orm\relationships.py", line 1586, in _setup_join_conditions
    can_be_synced_fn=self._columns_are_mapped
  File "...venv\lib\site-packages\sqlalchemy\orm\relationships.py", line 1849, in __init__
    self._determine_joins()
  File "...venv\lib\site-packages\sqlalchemy\orm\relationships.py", line 1932, in _determine_joins
    consider_as_foreign_keys=consider_as_foreign_keys
  File "<string>", line 2, in join_condition
  File "...venv\lib\site-packages\sqlalchemy\sql\selectable.py", line 764, in _join_condition
    crit = [(x == y) for x, y in constraints.values()[0]]
TypeError: 'dict_values' object does not support indexing

```

This used to work a couple of days ago with sqlalchemy master.

[Sqlalchemy-tickets] Issue #3004: don't generate relationships for joined subclass to superclass / automap (zzzeek/sqlalchemy)

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

New issue 3004: don't generate relationships for joined subclass to superclass / automap
https://bitbucket.org/zzzeek/sqlalchemy/issue/3004/dont-generate-relationships-for-joined

Mike Bayer:


```
#!python

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

Base = automap_base(declarative_base=declarative_base())

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    type = Column(String(50))
    __mapper_args__ = {
        'polymorphic_identity':'employee', 'polymorphic_on':type
    }

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    engineer_name = Column(String(30))
    __mapper_args__ = {
        'polymorphic_identity':'engineer',
    }


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

Base.prepare(engine, reflect=True)
assert not Engineer.__mapper__.relationships


```

[Sqlalchemy-tickets] Issue #3003: Update of ordered relationship when position is used in PK (zzzeek/sqlalchemy)

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

New issue 3003: Update of ordered relationship when position is used in PK
https://bitbucket.org/zzzeek/sqlalchemy/issue/3003/update-of-ordered-relationship-when

ods:

Sometimes the only choice for primary key of objects in private relationship is parent ID and position in list pair. But update of such property doesn't work: property after flush contains other objects than where assigned to it.

[Sqlalchemy-tickets] Issue #3002: postgresql/base.py:1913: SAWarning: Did not recognize type 'oid' of column 'propertyvalue' (zzzeek/sqlalchemy)

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

New issue 3002: postgresql/base.py:1913: SAWarning: Did not recognize type 'oid' of column 'propertyvalue'
https://bitbucket.org/zzzeek/sqlalchemy/issue/3002/postgresql-basepy-1913-sawarning-did-not

Sorin Sbarnea:

It seems that I got an warning while using sqlalchemy with postgres and I do see any reason for having a warning for 'oid' columns on postgres.

/sqlalchemy/dialects/postgresql/base.py:1913: SAWarning: Did not recognize type 'oid' of column 'propertyvalue'

[Sqlalchemy-tickets] Issue #3001: pg8000: UnicodeEncodeError: 'decimal' codec can't encode characters in position 0-2: invalid decimal Unicode string (zzzeek/sqlalchemy)

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

New issue 3001: pg8000: UnicodeEncodeError: 'decimal' codec can't encode characters in position 0-2: invalid decimal Unicode string
https://bitbucket.org/zzzeek/sqlalchemy/issue/3001/pg8000-unicodeencodeerror-decimal-codec

Sorin Sbarnea:

It seems that sqlalchemy with pg8000 gives and UnicodeDecodeError while trying to perform reflection.


```
#!python
Traceback (most recent call last):
  File "import-ledger.py", line 101, in <module>
    meta.reflect(bind=db)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 2754, in reflect
    Table(name, self, **reflect_opts)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 332, in __new__
    table._init(name, metadata, *args, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 396, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 413, in _autoload
    self, include_columns, exclude_columns
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1118, in run_callable
    return callable_(self, *args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 262, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/reflection.py", line 494, in reflecttable
    indexes = self.get_indexes(table_name, schema)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/reflection.py", line 344, in get_indexes
    info_cache=self.info_cache, **kw)
  File "<string>", line 1, in <lambda>
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/reflection.py", line 49, in cache
    ret = fn(self, con, *args, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/dialects/postgresql/base.py", line 1979, in get_indexes
    index['key'] = [int(k.strip()) for k in idx_key.split()]
UnicodeEncodeError: 'decimal' codec can't encode characters in position 0-2: invalid decimal Unicode string
```

[Sqlalchemy-tickets] Issue #3000: cannot cast type int2vector to character varying, post sqlalchemy v0.8.3, for PostgreSQL 8.2.4 (zzzeek/sqlalchemy)

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

New issue 3000: cannot cast type int2vector to character varying, post sqlalchemy v0.8.3, for PostgreSQL 8.2.4
https://bitbucket.org/zzzeek/sqlalchemy/issue/3000/cannot-cast-type-int2vector-to-character

Cristian Codorean:

I have the following piece of code:


```
#!python
from sqlalchemy import Table, MetaData, create_engine
engine = create_engine('postgresql://...@.../...')
metadata = MetaData(bind=engine)
mytable = Table('...', metadata, autoload=True)
```

Running on:

```
#!shell
$ psql
# select version();
                                          version                                           
--------------------------------------------------------------------------------------------
 PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux)
(1 row)

```

Any version of sqlalchemy > 0.8.3 seems to fail when running this code with:

```
#!shell
sqlalchemy.exc.ProgrammingError: (ProgrammingError) cannot cast type int2vector to character varying
```

Have tried the following versions: 0.8.4, 0.8.5, 0.9.3 and got the failure.

See below the whole traceback with sqlalchemy 0.9.3:


```
#!shell
Traceback (most recent call last):
  File "x.py", line 4, in <module>
    mytable = Table('...', metadata, autoload=True)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 350, in __new__
    table._init(name, metadata, *args, **kw)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 423, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 446, in _autoload
    self, include_columns, exclude_columns
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1632, in run_callable
    return conn.run_callable(callable_, *args, **kwargs)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1160, in run_callable
    return callable_(self, *args, **kwargs)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 345, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 558, in reflecttable
    **reflection_options
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 350, in __new__
    table._init(name, metadata, *args, **kw)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 423, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 435, in _autoload
    self, include_columns, exclude_columns
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1160, in run_callable
    return callable_(self, *args, **kwargs)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 345, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 558, in reflecttable
    **reflection_options
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 350, in __new__
    table._init(name, metadata, *args, **kw)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 423, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 435, in _autoload
    self, include_columns, exclude_columns
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1160, in run_callable
    return callable_(self, *args, **kwargs)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 345, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 558, in reflecttable
    **reflection_options
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 350, in __new__
    table._init(name, metadata, *args, **kw)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 423, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 435, in _autoload
    self, include_columns, exclude_columns
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1160, in run_callable
    return callable_(self, *args, **kwargs)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 345, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 558, in reflecttable
    **reflection_options
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 350, in __new__
    table._init(name, metadata, *args, **kw)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 423, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 435, in _autoload
    self, include_columns, exclude_columns
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1160, in run_callable
    return callable_(self, *args, **kwargs)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 345, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 571, in reflecttable
    indexes = self.get_indexes(table_name, schema)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 383, in get_indexes
    info_cache=self.info_cache, **kw)
  File "<string>", line 2, in get_indexes
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 53, in cache
    ret = fn(self, con, *args, **kw)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py", line 2212, in get_indexes
    c = connection.execute(t, table_oid=table_oid)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 717, in execute
    return meth(self, multiparams, params)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 814, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 927, in _execute_context
    context)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1076, in _handle_dbapi_exception
    exc_info
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 920, in _execute_context
    context)
  File "/xxx/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 425, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) cannot cast type int2vector to character varying
 "\n          SELECT\n              i.relname as relname,\n              ix.indisunique, ix.indexprs, ix.indpred,\n              a.attname, a.attnum, ix.indkey::varchar\n          FROM\n              pg_class t\n                    join pg_index ix on t.oid = ix.indrelid\n                    join pg_class i on i.oid=ix.indexrelid\n                    left outer join\n                        pg_attribute a\n                        on t.oid=a.attrelid and a.attnum = ANY(ix.indkey)\n          WHERE\n              t.relkind = 'r'\n              and t.oid = %(table_oid)s\n              and ix.indisprimary = 'f'\n          ORDER BY\n              t.relname,\n              i.relname\n        " {'table_oid': 410127}

```

[Sqlalchemy-tickets] Issue #2999: Typo in Querying with Joins example (zzzeek/sqlalchemy)

[Borja M. <iss...@bi...>]

New issue 2999: Typo in Querying with Joins example
https://bitbucket.org/zzzeek/sqlalchemy/issue/2999/typo-in-querying-with-joins-example

Borja Morales:

From
http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#querying-with-joins

You have the following statement.

```
#!python

>>> session.query(User).join(Address).\
...         filter(Address.email_address=='ja...@go...').\
...         all() 
[<User(name='jack', fullname='Jack Bean', email_address='gjffdd')>]
```

Instead of stating email_address='gjffdd' it should be password='gjffdd'

[Sqlalchemy-tickets] Issue #2998: Problem with a sentence on the "Using the Session" page (zzzeek/sqlalchemy)

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

New issue 2998: Problem with a sentence on the "Using the Session" page
https://bitbucket.org/zzzeek/sqlalchemy/issue/2998/problem-with-a-sentence-on-the-using-the

EricSmith:

Just some kind of typo/editing error (emphasis added):

"Most web frameworks include infrastructure to establish a single Session, associated with the request, which is correctly constructed and **torn down corresponding torn down** at the end of a request."

This was just on the latest published docs (http://docs.sqlalchemy.org/en/latest/orm/session.html), so sorry if this has already been fixed in source.

[Sqlalchemy-tickets] Issue #2997: Mutable fails to track changes in certain conditions (zzzeek/sqlalchemy)

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

New issue 2997: Mutable fails to track changes in certain conditions
https://bitbucket.org/zzzeek/sqlalchemy/issue/2997/mutable-fails-to-track-changes-in-certain

Vsevolod Solovyov:

Hello, this is a code that illustrates my problem:

```
#!python
>>> product.attrdata
{'key': 1}
>>> product.attrdata = product.attrdata or {}
>>> product.attrdata['key'] = 20
>>> db_session.commit()
>>> db_session.refresh(product)
>>> product.attrdata
{'key': 1}

>>> product.attrdata['key'] = 20
>>> db_session.commit()
>>> db_session.refresh(product)
>>> product.attrdata
{'key': 20}
```

`product.attrdata` is a MutableDict. As a first step, I've found that `Mutable.changed()` fails:

```
#!python
    def changed(self):
        for parent, key in self._parents.items():
            flag_modified(parent, key)
```

Because `self._parents` are empty. That's because in `MutableBase.set()`, when I do `product.attrdata = product.attrdata`:

```
#!python
        def set(target, value, oldvalue, initiator):
            if not isinstance(value, cls):
                value = cls.coerce(key, value)
            if value is not None:
                value._parents[target.obj()] = key
            if isinstance(oldvalue, cls):
                oldvalue._parents.pop(target.obj(), None)
            return value
```

`value is oldvalue` and parent gets popped after it was set. If I switch these two if's, then `Mutable.changed()` will call `flag_modified()`, but it won't flag properly it anyway (I didn't dig much further and don't have a patch to fix a problem).

Is it a bug or I shouldn't do things like `product.attrdata = product.attrdata` in the first place?

SQLAlchemy version 0.9.3

[Sqlalchemy-tickets] Issue #2996: Could not locate any relevant foreign key columns with mixin and declared_attr (zzzeek/sqlalchemy)

[Charles-Axel D. <iss...@bi...>]

New issue 2996: Could not locate any relevant foreign key columns with mixin and declared_attr
https://bitbucket.org/zzzeek/sqlalchemy/issue/2996/could-not-locate-any-relevant-foreign-key

Charles-Axel Dein:

The following code:

```
#!python


from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.orm import foreign
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, Boolean

Base = declarative_base()


class Transaction(Base):
    __tablename__ = 'transactions'
    id = Column(Integer, primary_key=True)
    invoice_id = Column(Integer, ForeignKey('invoices.id'))


class InvoiceMixin(object):

    __tablename__ = 'invoices'

    id = Column(Integer, primary_key=True)
    approved = Column(Boolean)

    @declared_attr
    def transactions(cls):
        return relationship(
            'Transaction',
            primaryjoin=lambda: Transaction.invoice_id == cls.id
            # primaryjoin=lambda: remote(Transaction.invoice_id) == cls.id
            # primaryjoin='Transaction.invoice_id == %s.id' % cls.__name__,
        )


class Invoice1(InvoiceMixin, Base):
    __table_args__ = {'extend_existing': True}


class Invoice2(InvoiceMixin, Base):
    __table_args__ = {'extend_existing': True}


def main():
    engine = create_engine('sqlite:///:memory:')
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()
    session.query(Invoice1).get(0)


if __name__ == '__main__':
    main()

```

Will fail with the following:


```
#!python

Traceback (most recent call last):
  File "reproduce_sqla_bug.py", line 53, in <module>
    main()
  File "reproduce_sqla_bug.py", line 49, in main
    session.query(Invoice1).get(0)
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1151, in query
    return self._query_cls(entities, self, **kwargs)
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 106, in __init__
    self._set_entities(entities)
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 116, in _set_entities
    self._set_entity_selectables(self._entities)
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 149, in _set_entity_selectables
    ent.setup_entity(*d[entity])
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2980, in setup_entity
    self._with_polymorphic = ext_info.with_polymorphic_mappers
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 712, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1840, in _with_polymorphic_mappers
    configure_mappers()
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 2544, in configure_mappers
    mapper._post_configure_properties()
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1657, in _post_configure_properties
    prop.init()
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py", line 143, in init
    self.do_init()
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1512, in do_init
    self._setup_join_conditions()
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1588, in _setup_join_conditions
    can_be_synced_fn=self._columns_are_mapped
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1856, in __init__
    self._check_foreign_cols(self.primaryjoin, True)
  File "/Users/ca/.virtualenvs/sqlalchemy/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 2374, in _check_foreign_cols
    raise sa_exc.ArgumentError(err)
sqlalchemy.exc.ArgumentError: Could not locate any relevant foreign key columns for primary join condition 'transactions.invoice_id = invoices.id' on relationship Invoice1.transactions.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation.
```

If I don't specify the `primaryjoin`, it will be ok. If I remove one of the subclass (e.g. `Invoice1`), it will be ok. My guess is that this come from `_setup_pairs`'s deannotation step. The first time the relationship, it's gonna get rid of the annotation and the next time it runs, it won't have access to them. Actually, after having a second look, maybe it's not what's happening there. Am I missing anything?

Thanks a lot Mike!

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #2995: Query.exists() fails if the query doesn't select any columns (worked in 0.8.x) (zzzeek/sqlalchemy)

[Daniel G. <iss...@bi...>]

New issue 2995: Query.exists() fails if the query doesn't select any columns (worked in 0.8.x)
https://bitbucket.org/zzzeek/sqlalchemy/issue/2995/queryexists-fails-if-the-query-doesnt

Daniel Grace:

This appears to be a regression:

This (pseudocode) used to run with no issues in 0.8.x:

[...]
subquery = session.query().select_from(Bar) # Note no selected columns
query = query.filter(subquery.exists())

Now it fails on the .exists() line with the following exception:
sqlalchemy.exc.InvalidRequestError: Query contains no columns with which to SELECT from

The documentation for .exists() states that it is a "A convenience method that turns a query into an EXISTS subquery of the form EXISTS (SELECT 1 FROM ... WHERE ...).".  
Thus, there should be no need for the subquery to select any columns of its own since they will all be replaced by the implicit "SELECT 1" from the exists anyways.

Rewriting the above as:
subquery = session.query().select_from(Bar)
query = query.filter(subquery.add_columns(sql.true()).exists())

avoids the exception and makes the query work as intended.

[Sqlalchemy-tickets] Issue #2994: PickleType gets not updated in Database in some circumstances (zzzeek/sqlalchemy)

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

New issue 2994: PickleType gets not updated in Database in some circumstances
https://bitbucket.org/zzzeek/sqlalchemy/issue/2994/pickletype-gets-not-updated-in-database-in

Marc Schlaich:

Test:


```
#!python

import logging
logging.getLogger('sqlalchemy').setLevel(logging.INFO)
logging.basicConfig()

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

Base = declarative_base()


class Test(Base):
    __tablename__ = 'test'
    id = Column(Integer, primary_key=True)
    counters = Column(PickleType)


engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)


def test_update_dict():
    session = Session()
    session.add(Test(counters=[dict(counter=1)]))
    session.commit()

    t = session.query(Test).one()
    for d in t.counters:
        # it's obvious that this doesn't trigger a change in SQLA
        d['counter'] -= 1

    # try to force a change in SQLAlchemy, this should issue a
    # update query
    t.counters = list(t.counters)
    session.commit()

    t = session.query(Test).one()
    assert t.counters == [dict(counter=0)]
```

Result:


```
#!python

$ py.test sqla_test.py
============================= test session starts =============================
platform win32 -- Python 2.7.6 -- py-1.4.20 -- pytest-2.5.2
plugins: cov
collected 1 items / 1 skipped

sqla_test.py F

================================== FAILURES ===================================
______________________________ test_update_dict _______________________________

    def test_update_dict():
        session = Session()
        session.add(Test(counters=[dict(counter=1)]))
        session.commit()

        t = session.query(Test).one()
        for d in t.counters:
            # it's obvious that this doesn't trigger a change in SQLA
            d['counter'] -= 1

        # try to force a change in SQLAlchemy, this should issue a
        # update query
        t.counters = list(t.counters)
        print t.counters
        session.commit()

        t = session.query(Test).one()
>       assert t.counters == [dict(counter=0)]
E       assert [{'counter': 1}] == [{'counter': 0}]
E         At index 0 diff: {'counter': 1} != {'counter': 0}

sqla_test.py:43: AssertionError
------------------------------- Captured stdout -------------------------------
[{'counter': 0}]
------------------------------- Captured stderr -------------------------------
INFO:sqlalchemy.orm.mapper.Mapper:(Test|test) _post_configure_properties() started
INFO:sqlalchemy.orm.mapper.Mapper:(Test|test) initialize prop id
INFO:sqlalchemy.orm.mapper.Mapper:(Test|test) initialize prop counters
INFO:sqlalchemy.orm.mapper.Mapper:(Test|test) _post_configure_properties() complete
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO test (counters) VALUES (?)
INFO:sqlalchemy.engine.base.Engine:(<read-only buffer for 0x03971C50, size -1, offset 0 at 0x03913060>,)
INFO:sqlalchemy.engine.base.Engine:COMMIT
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:SELECT test.id AS test_id, test.counters AS test_counters
FROM test
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:SELECT test.id AS test_id, test.counters AS test_counters
FROM test
INFO:sqlalchemy.engine.base.Engine:()
===================== 1 failed, 1 skipped in 0.63 seconds =====================
```

[Sqlalchemy-tickets] Issue #2993: db.Model without primary key generates a confusing error message (zzzeek/sqlalchemy)

[Ling T. <iss...@bi...>]

New issue 2993: db.Model without primary key generates a confusing error message
https://bitbucket.org/zzzeek/sqlalchemy/issue/2993/dbmodel-without-primary-key-generates-a

Ling Thio:

When a model definition does not contain a primary key, the following error message appears:

sqlalchemy.exc.InvalidRequestError: Class <class 'XYZ'> does not have a __table__ or __tablename__ specified and does not inherit from an existing table-mapped class.

Example:

```
#!python

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
db = SQLAlchemy(app)

class Xyz(db.Model):
    name = db.Column(db.String(50))
```

It would be nice if the error message:
a) specifically mentions the missing primary key, or
b) prints the current error message, with the addition of the possibility of a missing primary key.

[Sqlalchemy-tickets] Issue #2992: Strings not escaped when used as filter arguments (zzzeek/sqlalchemy)

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

New issue 2992: Strings not escaped when used as filter arguments
https://bitbucket.org/zzzeek/sqlalchemy/issue/2992/strings-not-escaped-when-used-as-filter

malthe:

The ``filter()`` method accepts string arguments and uses them as raw SQL. This is unexpected, because everywhere else, only strings wrapped in ``text()`` are used as-is.

I consider this a bug, because it is a very questionable feature to allow raw SQL implicitly.

[Sqlalchemy-tickets] Issue #2991: constraint name unexpected when using metadata.naming_conventions (zzzeek/sqlalchemy)

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

New issue 2991: constraint name unexpected when using metadata.naming_conventions
https://bitbucket.org/zzzeek/sqlalchemy/issue/2991/constraint-name-unexpected-when-using

Marek Baczyński:

DB: SQL Server 2008R2

SQLAlchemy==0.9.3


```
#!python

from sqlalchemy import Column, Integer, String, MetaData, ForeignKey
from sqlalchemy import Enum, Text, Sequence, BigInteger, DateTime, Boolean
from sqlalchemy import UniqueConstraint, CheckConstraint
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

convention = {
    "ix": 'ix_%(column_0_label)s',
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s"
}

metadata = MetaData(naming_convention=convention)
Base = declarative_base(metadata=metadata)


class Table(Base):
    __tablename__ = 'table'
    id = Column(Integer, primary_key=True)
    col1 = Column(Enum('one', 'two', name='col1'), nullable=True)
    col2 = Column(Boolean(name='col2'), nullable=True)

```

metadata.create_all() results in this sql:


```
#!sql

INFO  [sqlalchemy.engine.base.Engine]
CREATE TABLE [table] (
        id INTEGER NOT NULL IDENTITY(1,1),
        col1 VARCHAR(3) NULL,
        col2 BIT NULL,
        CONSTRAINT pk_table PRIMARY KEY (id),
 -- expected ck_table_col1
        CONSTRAINT ck_table_ck_table_col1 CHECK (col1 IN ('one', 'two')),
 -- expected ck_table_col2
        CONSTRAINT ck_table_ck_table_col2 CHECK (col2 IN (0, 1))
)


INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] COMMIT
```

[Sqlalchemy-tickets] Issue #2990: BETWEEN SYMMETRIC not supported (zzzeek/sqlalchemy)

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

New issue 2990: BETWEEN SYMMETRIC not supported
https://bitbucket.org/zzzeek/sqlalchemy/issue/2990/between-symmetric-not-supported

malthe:

It's a convenient comparison operation – see PostgreSQL chapter on [comparison functions](http://www.postgresql.org/docs/8.2/static/functions-comparison.html).

[Sqlalchemy-tickets] Issue #2989: Query oerder_by with relationship as parameter uses the according join condition in the order by clause (zzzeek/sqlalchemy)

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

New issue 2989: Query oerder_by with relationship as parameter uses the according join condition in the order by clause
https://bitbucket.org/zzzeek/sqlalchemy/issue/2989/query-oerder_by-with-relationship-as

Ronny Pfannschmidt:

this problem has shown up when porting to oracle, which reported a random error uppon seeing the sql

after inspection i was sure the code was certainly wrong - at least the join condition makes absolutely no sense there

imho it should be an error to pass a relationship there


```
#!python
from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, Session

Base = declarative_base()

class Fun(Base):
    __tablename__ = 'fun'
    id = Column(Integer, primary_key=True)
    pos = Column(Integer)

class FunDetail(Base):
    __tablename__ = 'fun_detail'
    id = Column(Integer, primary_key=True)
    fun_id = Column(Integer, ForeignKey('fun.id'))
    fun = relationship(Fun)
    pos = Column(Integer)

s = Session()
print s.query(FunDetail).order_by(FunDetail.pos, FunDetail.fun)

```
results in
```
#!sql
SELECT fun_detail.id AS fun_detail_id, fun_detail.fun_id AS fun_detail_fun_id, fun_detail.pos AS fun_detail_pos 
FROM fun_detail ORDER BY fun_detail.pos, fun.id = fun_detail.fun_id
```

[Sqlalchemy-tickets] Issue #2988: Don't register tables in metadata if autoloading fails (zzzeek/sqlalchemy)

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

New issue 2988: Don't register tables in metadata if autoloading fails
https://bitbucket.org/zzzeek/sqlalchemy/issue/2988/dont-register-tables-in-metadata-if

Roman Podolyaka:

When autoloading of a table fails, it nevertheless gets added to the metadata registry. This behaviour seems to be accidentally introduced in f6198d9abf453182f4b111e0579a7a4ef1614e79.

Snippet to reproduce is attached. Fix is coming soon.

[Sqlalchemy-tickets] Issue #2987: Oracle DATE column mapped to sqltypes.DATE on reflection (zzzeek/sqlalchemy)

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

New issue 2987: Oracle DATE column mapped to sqltypes.DATE on reflection
https://bitbucket.org/zzzeek/sqlalchemy/issue/2987/oracle-date-column-mapped-to-sqltypesdate

William Edwards:

If you load an Oracle table (using cx_Oracle) via MetaData.reflect, Oracle DATE columns have the sqlalchemy.sqltypes.DATE type.

However, Oracle DATE columns have time components, and should be mapped to sqlalchemy.sqltypes.DATETIME.

[Sqlalchemy-tickets] Issue #2986: setuptools has removed Feature (zzzeek/sqlalchemy)

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

New issue 2986: setuptools has removed Feature
https://bitbucket.org/zzzeek/sqlalchemy/issue/2986/setuptools-has-removed-feature

Mike Bayer:

lets wait and see if there's another way to get this, otherwise we have to take the ``--without-cextensions`` flag out of the documentation.

backport to 0.8, 0.7

```
#!diff

diff --git a/doc/build/changelog/changelog_07.rst b/doc/build/changelog/changelog_07.rst
index da89bbd..14849fe 100644
--- a/doc/build/changelog/changelog_07.rst
+++ b/doc/build/changelog/changelog_07.rst
@@ -7,6 +7,15 @@
     :version: 0.7.11
 
     .. change::
+        :tags: bug, general
+        :versions: 0.8.6, 0.9.4
+
+        Made a critical fix in the ``setup.py`` file due to the removal
+        of the ``setuptools.Feature`` extension.  For the moment,
+        the ``--without-cextensions`` flag will not function with
+        the latest versions of setuptools.
+
+    .. change::
         :tags: bug, engine
         :tickets: 2851
         :versions: 0.8.3, 0.9.0b1
diff --git a/setup.py b/setup.py
index f682081..0b32522 100644
--- a/setup.py
+++ b/setup.py
@@ -11,8 +11,13 @@ from distutils.command.build_ext import build_ext
 from distutils.errors import (CCompilerError, DistutilsExecError,
                               DistutilsPlatformError)
 try:
-    from setuptools import setup, Extension, Feature
+    from setuptools import setup, Extension
     has_setuptools = True
+
+    try:
+        from setuptools import Feature
+    except ImportError:
+        Feature = None
 except ImportError:
     has_setuptools = False
     from distutils.core import setup, Extension

```

[Sqlalchemy-tickets] Issue #2985: simplify pool recycle logic (zzzeek/sqlalchemy)

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

New issue 2985: simplify pool recycle logic
https://bitbucket.org/zzzeek/sqlalchemy/issue/2985/simplify-pool-recycle-logic

Mike Bayer:

using a simple invalidation time we can do away with all the "pool replacement" logic.    the current logic is subject to a pretty obvious race condition, where as many connections all hit a disconnect wall, all of the Connection objects hosting them will simultaneously call upon self.engine.dispose().  this means we could have N pools generated and immediately chucked within a disconnect cycle.

the patch below removes all of that and replaces with a simple timeout which incurs no overhead and no race conditions.   the only difference is that the "bad" connections hang around until they are invalidated on checkout.


```
#!diff

diff --git a/lib/sqlalchemy/engine/base.py b/lib/sqlalchemy/engine/base.py
index 888a15f..20b5227 100644
--- a/lib/sqlalchemy/engine/base.py
+++ b/lib/sqlalchemy/engine/base.py
@@ -1084,9 +1084,7 @@ class Connection(Connectable):
                 del self._is_disconnect
                 dbapi_conn_wrapper = self.connection
                 self.invalidate(e)
-                if not hasattr(dbapi_conn_wrapper, '_pool') or \
-                        dbapi_conn_wrapper._pool is self.engine.pool:
-                    self.engine.dispose()
+                self.engine.pool._invalidate(dbapi_conn_wrapper)
             if self.should_close_with_result:
                 self.close()
 
@@ -1496,7 +1494,7 @@ class Engine(Connectable, log.Identified):
         the engine are not affected.
 
         """
-        self.pool = self.pool._replace()
+        self.pool.dispose()
 
     def _execute_default(self, default):
         with self.contextual_connect() as conn:
diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py
index 473b665..4a07e78 100644
--- a/lib/sqlalchemy/orm/strategies.py
+++ b/lib/sqlalchemy/orm/strategies.py
@@ -528,7 +528,6 @@ class LazyLoader(AbstractRelationshipLoader):
     def _emit_lazyload(self, strategy_options, session, state, ident_key, passive):
         q = session.query(self.mapper)._adapt_all_clauses()
 
-
         if self.parent_property.secondary is not None:
             q = q.select_from(self.mapper, self.parent_property.secondary)
 
diff --git a/lib/sqlalchemy/pool.py b/lib/sqlalchemy/pool.py
index af9b8fc..f78825e 100644
--- a/lib/sqlalchemy/pool.py
+++ b/lib/sqlalchemy/pool.py
@@ -210,6 +210,7 @@ class Pool(log.Identified):
         self._threadconns = threading.local()
         self._creator = creator
         self._recycle = recycle
+        self._invalidate_time = 0
         self._use_threadlocal = use_threadlocal
         if reset_on_return in ('rollback', True, reset_rollback):
             self._reset_on_return = reset_rollback
@@ -276,6 +277,22 @@ class Pool(log.Identified):
 
         return _ConnectionRecord(self)
 
+    def _invalidate(self, connection):
+        """Mark all connections established within the generation
+        of the given connection as invalidated.
+
+        If this pool's last invalidate time is before when the given
+        connection was created, update the timestamp til now.  Otherwise,
+        no action is performed.
+
+        Connections with a start time prior to this pool's invalidation
+        time will be recycled upon next checkout.
+        """
+        rec = getattr(connection, "_connection_record", None)
+        if not rec or self._invalidate_time < rec.starttime:
+            self._invalidate_time = time.time()
+
+
     def recreate(self):
         """Return a new :class:`.Pool`, of the same class as this one
         and configured with identical creation arguments.
@@ -301,17 +318,6 @@ class Pool(log.Identified):
 
         raise NotImplementedError()
 
-    def _replace(self):
-        """Dispose + recreate this pool.
-
-        Subclasses may employ special logic to
-        move threads waiting on this pool to the
-        new one.
-
-        """
-        self.dispose()
-        return self.recreate()
-
     def connect(self):
         """Return a DBAPI connection from the pool.
 
@@ -483,6 +489,7 @@ class _ConnectionRecord(object):
         self.connection = None
 
     def get_connection(self):
+        recycle = False
         if self.connection is None:
             self.connection = self.__connect()
             self.info.clear()
@@ -493,6 +500,15 @@ class _ConnectionRecord(object):
             self.__pool.logger.info(
                     "Connection %r exceeded timeout; recycling",
                     self.connection)
+            recycle = True
+        elif self.__pool._invalidate_time > self.starttime:
+            self.__pool.logger.info(
+                    "Connection %r invalidated due to pool invalidation; recycling",
+                    self.connection
+                    )
+            recycle = True
+
+        if recycle:
             self.__close()
             self.connection = self.__connect()
             self.info.clear()
@@ -911,8 +927,6 @@ class QueuePool(Pool):
         try:
             wait = use_overflow and self._overflow >= self._max_overflow
             return self._pool.get(wait, self._timeout)
-        except sqla_queue.SAAbort as aborted:
-            return aborted.context._do_get()
         except sqla_queue.Empty:
             if use_overflow and self._overflow >= self._max_overflow:
                 if not wait:
@@ -974,12 +988,6 @@ class QueuePool(Pool):
         self._overflow = 0 - self.size()
         self.logger.info("Pool disposed. %s", self.status())
 
-    def _replace(self):
-        self.dispose()
-        np = self.recreate()
-        self._pool.abort(np)
-        return np
-
     def status(self):
         return "Pool size: %d  Connections in pool: %d "\
                 "Current Overflow: %d Current Checked out "\
diff --git a/test/engine/test_pool.py b/test/engine/test_pool.py
index fc6f3dc..cde19b3 100644
--- a/test/engine/test_pool.py
+++ b/test/engine/test_pool.py
@@ -1069,7 +1069,8 @@ class QueuePoolTest(PoolTestBase):
                 # inside the queue, before we invalidate the other
                 # two conns
                 time.sleep(.2)
-                p2 = p._replace()
+                p._invalidate(c2)
+                c2.invalidate()
 
                 for t in threads:
                     t.join(join_timeout)
@@ -1079,19 +1080,18 @@ class QueuePoolTest(PoolTestBase):
     @testing.requires.threading_with_mock
     def test_notify_waiters(self):
         dbapi = MockDBAPI()
+
         canary = []
-        def creator1():
+        def creator():
             canary.append(1)
             return dbapi.connect()
-        def creator2():
-            canary.append(2)
-            return dbapi.connect()
-        p1 = pool.QueuePool(creator=creator1,
+        p1 = pool.QueuePool(creator=creator,
                            pool_size=1, timeout=None,
                            max_overflow=0)
-        p2 = pool.NullPool(creator=creator2)
+        #p2 = pool.NullPool(creator=creator2)
         def waiter(p):
             conn = p.connect()
+            canary.append(2)
             time.sleep(.5)
             conn.close()
 
@@ -1104,12 +1104,14 @@ class QueuePoolTest(PoolTestBase):
             threads.append(t)
         time.sleep(.5)
         eq_(canary, [1])
-        p1._pool.abort(p2)
+
+        c1.invalidate()
+        p1._invalidate(c1)
 
         for t in threads:
             t.join(join_timeout)
 
-        eq_(canary, [1, 2, 2, 2, 2, 2])
+        eq_(canary, [1, 1, 2, 2, 2, 2, 2])
 
     def test_dispose_closes_pooled(self):
         dbapi = MockDBAPI()
diff --git a/test/engine/test_reconnect.py b/test/engine/test_reconnect.py
index ba336a1..a3ad9c5 100644
--- a/test/engine/test_reconnect.py
+++ b/test/engine/test_reconnect.py
@@ -146,16 +146,20 @@ class MockReconnectTest(fixtures.TestBase):
         # close shouldnt break
 
         conn.close()
-        is_not_(self.db.pool, db_pool)
-
-        # ensure all connections closed (pool was recycled)
 
+        # ensure one connection closed...
         eq_(
             [c.close.mock_calls for c in self.dbapi.connections],
-            [[call()], [call()]]
+            [[call()], []]
         )
 
         conn = self.db.connect()
+
+        eq_(
+            [c.close.mock_calls for c in self.dbapi.connections],
+            [[call()], [call()], []]
+        )
+
         conn.execute(select([1]))
         conn.close()
 
@@ -534,8 +538,6 @@ class RealReconnectTest(fixtures.TestBase):
         # invalidate() also doesn't screw up
         assert_raises(exc.DBAPIError, engine.connect)
 
-        # pool was recreated
-        assert engine.pool is not p1
 
     def test_null_pool(self):
         engine = \

```

[Sqlalchemy-tickets] Issue #2984: dump mysqldb as a connector; push out drizzle to 3rd party (zzzeek/sqlalchemy)

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

New issue 2984: dump mysqldb as a connector; push out drizzle to 3rd party
https://bitbucket.org/zzzeek/sqlalchemy/issue/2984/dump-mysqldb-as-a-connector-push-out

Mike Bayer:

mysqldb has no business being in connectors, restore this back to being just a single dialect.

[Sqlalchemy-tickets] Issue #2983: Insert deleted object problem with after flush listeners (zzzeek/sqlalchemy)

[Konsta V. <iss...@bi...>]

New issue 2983: Insert deleted object problem with after flush listeners
https://bitbucket.org/zzzeek/sqlalchemy/issue/2983/insert-deleted-object-problem-with-after

Konsta Vesterinen:

My guess is this problem is related to: https://bitbucket.org/zzzeek/sqlalchemy/issue/2501/the-delete-before-insert-problem

Failing test case:
```
#!python

import sqlalchemy as sa


dns = 'sqlite:///:memory:'

engine = sa.create_engine(dns)
engine.echo = True
connection = engine.connect()
Base = sa.ext.declarative.declarative_base()


class ModelA(Base):
    __tablename__ = 'a'

    id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
    name = sa.Column(sa.Unicode(255), nullable=False)


class ModelB(Base):
    __tablename__ = 'b'

    id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
    name = sa.Column(sa.Unicode(255), nullable=False)

Base.metadata.create_all(connection)

Session = sa.orm.sessionmaker(bind=connection)
session = Session()


@sa.event.listens_for(sa.orm.session.Session, 'after_flush')
def after_flush(session, flush_context):
    for obj in session:
        if not isinstance(obj, ModelA):
            continue
        b = session.query(ModelB).get(obj.id)
        if not b:
            b = ModelB(id=obj.id, name=u'b')
            session.add(b)
        else:
            b.name = u'updated b!'

a = ModelA(name=u'A')
session.add(a)
session.flush()
session.delete(a)
session.flush()
session.add(ModelA(id=a.id, name=u'A'))
session.commit()
b = session.query(ModelB).first()
assert b.name == u'updated b!'
```

This also throws a warning (which I think it should not throw): SAWarning: Attribute history events accumulated on 1 previously clean instances within inner-flush event handlers have been reset, and will not result in database updates. Consider using set_committed_value() within inner-flush event handlers to avoid this warning.

[Sqlalchemy-tickets] Issue #2982: enhance association_proxy to support specifying fetch strategy (zzzeek/sqlalchemy)

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

New issue 2982: enhance association_proxy to support specifying fetch strategy
https://bitbucket.org/zzzeek/sqlalchemy/issue/2982/enhance-association_proxy-to-support

Ezra Epstein:

I've encountered a use case where being able to override the default fetch strategy of the underlying relationships would come in handy.

In this case the underlying relationships are both lazy - a one-to-many (backref) to a intermediate join table, followed by a to-one to the target table for the association.  There are good reasons for each to be lazy "joins" (separate select statement required).

However, when the association_proxy is accessed I would like to indicate that the second relationship should be fetch eagerly using an (inner) join.   Without that the association_proxy ends up giving rise to the N+1 select problem.

I do have several work-around via the org Query APIs directly.  Would be very nice, however, to be able to do this directly via a mapped attribute.

Thanks for considering it.  (Or clarifying what I may be doing wrong if this is already a feature!)

[Sqlalchemy-tickets] Issue #2981: Backref relationship lazy-load failure (resulting in AttributeError) (zzzeek/sqlalchemy)

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

New issue 2981: Backref relationship lazy-load failure (resulting in AttributeError)
https://bitbucket.org/zzzeek/sqlalchemy/issue/2981/backref-relationship-lazy-load-failure

Roman Alexeev:

I seems that relationship declared as a backref does not load until you do something with a model for the first time (just requesting a list of relationship is enough).

Here is a gist showcasing the problem: https://gist.github.com/letit-bee/9323748