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

[Sqlalchemy-tickets] Issue #4186: URL plugins modify the URL that is attached to engine (zzzeek/sqlalchemy)

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

New issue 4186: URL plugins modify the URL that is attached to engine
https://bitbucket.org/zzzeek/sqlalchemy/issues/4186/url-plugins-modify-the-url-that-is

Michael Bayer:

applications that take engine.url to copy it won't see plugin or plugin query arguments.    propose making a copy of the URL that gets altered for passing to the dialect/DBAPI.

see also #4170

[Sqlalchemy-tickets] Issue #4185: AssociationProxy is not properly initialized when created by a declared_attr of a mixin class (zzzeek/sqlalchemy)

[Eric A. (Omni) <iss...@bi...>]

New issue 4185: AssociationProxy is not properly initialized when created by a declared_attr of a mixin class
https://bitbucket.org/zzzeek/sqlalchemy/issues/4185/associationproxy-is-not-properly

Eric Atkin (Omni):

After upgrading from 1.1.14 to 1.2.2, my models broke. A simplified representation is included below that demonstrates the issue. If the Parent.children association proxy is not accessed before any subclasses are defined, it's owning_class attribute will be None. This caused the following traceback, but I'm sure it would be the root cause of many more issues throughout the library:

```
Traceback (most recent call last):
  File "{...}/.venv/lib/python3.6/site-packages/pyramid_mako/__init__.py", line 148, in __call__
    result = template.render_unicode(**system)
  File "{...}/.venv/lib/python3.6/site-packages/mako/template.py", line 471, in render_unicode
    as_unicode=True)
  File "{...}/.venv/lib/python3.6/site-packages/mako/runtime.py", line 838, in _render
    **_kwargs_for_callable(callable_, data))
  File "{...}/.venv/lib/python3.6/site-packages/mako/runtime.py", line 873, in _render_context
    _exec_template(inherit, lclcontext, args=args, kwargs=kwargs)
  File "{...}/.venv/lib/python3.6/site-packages/mako/runtime.py", line 899, in _exec_template
    callable_(context, *args, **kwargs)
  File "{...}/dispatch/templates/base/base.mako", line 297, in render_body
    <%block name="content">
  File "{...}/dispatch/templates/base/detail_carriage.mako", line 710, in render_content
    % if editable or _context.child_invoice_meta:
  File "{...}/sqlalchemy_auth/block_base.py", line 86, in __getattribute__
    return super().__getattribute__(name)
  File "{...}/.venv/lib/python3.6/site-packages/sqlalchemy/ext/associationproxy.py", line 263, in __get__
    if self.scalar:
  File "{...}/.venv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 767, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "{...}/.venv/lib/python3.6/site-packages/sqlalchemy/ext/associationproxy.py", line 233, in scalar
    scalar = not self._get_property().uselist
  File "{...}/.venv/lib/python3.6/site-packages/sqlalchemy/ext/associationproxy.py", line 215, in _get_property
    return (orm.class_mapper(self.owning_class).
  File "{...}/.venv/lib/python3.6/site-packages/sqlalchemy/orm/base.py", line 426, in class_mapper
    raise exc.UnmappedClassError(class_)
UnmappedClassError: Class 'builtins.NoneType' is not mapped
```

```
#!python

from sqlalchemy import Column, ForeignKey, Integer, Text
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declared_attr, declarative_base
from sqlalchemy.orm import backref, relationship
from sqlalchemy.orm.collections import attribute_mapped_collection

Declarative_Base = declarative_base()


class Mixin:
    @declared_attr
    def children(cls):
        class Child(Declarative_Base):
            parent_id = Column(Integer, ForeignKey(f'{cls.__tablename__}.id'), primary_key=True)
            key = Column(Text, primary_key=True)
            value = Column(Text, nullable=False)
            
            parent = relationship(cls, backref=backref(
                '_children',
                cascade='all, delete-orphan',
                collection_class=attribute_mapped_collection('key')
            ))
            
            __tablename__ = f'{cls.__tablename__}_child'
        
        return association_proxy(
            '_children',
            'value',
            creator=lambda k, v: Child(key=k, value=v),
        )


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


#Parent.children  # uncomment this for workaround


class SubParent(Parent):
    id = Column(Integer, ForeignKey(Parent.id), primary_key=True)
    
    __tablename__ = 'subparent'


Parent.children.owning_class  # <class 'NoneType'>

```

This seems to be caused by the fix for https://bitbucket.org/zzzeek/sqlalchemy/issues/4116/accessing-associationproxy-of-aliased
in commit https://bitbucket.org/zzzeek/sqlalchemy/commits/f14a58dea4b825beb4baaef44389880927543cc4

I don't know how to fix this, but am able to work around it for now by accessing the proxy attribute prior to the subclass definitions. I hope that by demonstrating it, you would be able to design a change that addresses the original issue and mitigates this side-effect.

Thank you,
Eric

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #4184: `MySQL server has gone away` when raising DisconnectionError in checkout (zzzeek/sqlalchemy)

[Sylvain D. <iss...@bi...>]

New issue 4184: `MySQL server has gone away` when raising DisconnectionError in checkout
https://bitbucket.org/zzzeek/sqlalchemy/issues/4184/mysql-server-has-gone-away-when-raising

Sylvain Duchesne:

The following code should trigger the error:


```
#!python

import logging
logging.basicConfig(level=logging.DEBUG)
import time
from sqlalchemy import create_engine
from sqlalchemy.exc import DisconnectionError
import sqlalchemy
e = create_engine("mysql://test:test@localhost/test", echo=True)

def _check_out(dbapi_connection, connection_record, connection_proxy):
 logging.getLogger().info("checking out")
 checkout_time = connection_record.info.get('checkout_time')
 now = time.time()
 if checkout_time and now - checkout_time > 1:
  logging.getLogger().info("connection expired")
  raise DisconnectionError()
 connection_record.info['checkout_time'] = now

sqlalchemy.event.listen(e, 'checkout', _check_out)

r = e.execute("select 1")
del r
time.sleep(2)
r = e.execute("select 1")
del r
```

Executing the last execute should yield the following output:

```
#!python

>>> del r
>>> time.sleep(2)
>>> r = e.execute("select 1")
INFO:root:checking out
INFO:root:connection expired
INFO:root:checking out
2018-02-08 11:27:52,270 INFO sqlalchemy.engine.base.Engine select 1
INFO:sqlalchemy.engine.base.Engine:select 1
2018-02-08 11:27:52,272 INFO sqlalchemy.engine.base.Engine ()
INFO:sqlalchemy.engine.base.Engine:()
>>> del r
ERROR:sqlalchemy.pool.QueuePool:Exception during reset or similar
Traceback (most recent call last):
  File "F:\rdv_git\rendezvous\venv\rendezvous\lib\site-packages\sqlalchemy\pool.py", line 703, in _finalize_fairy
    fairy._reset(pool)
  File "F:\rdv_git\rendezvous\venv\rendezvous\lib\site-packages\sqlalchemy\pool.py", line 873, in _reset
    pool._dialect.do_rollback(self)
  File "F:\rdv_git\rendezvous\venv\rendezvous\lib\site-packages\sqlalchemy\dialects\mysql\base.py", line 1775, in do_rollback
    dbapi_connection.rollback()
OperationalError: (2006, 'MySQL server has gone away')
>>>
```

I traced the error back to the `weakref` 
https://bitbucket.org/zzzeek/sqlalchemy/src/a54b3bb0a8a3a33c4c68349484ef4f3dc915416e/lib/sqlalchemy/pool.py?at=master&fileviewer=file-view-default#pool.py-540
which has its callback bound with the `dbapi connection` that will be closed.
When the gc passes and the callback gets called, 
https://bitbucket.org/zzzeek/sqlalchemy/src/a54b3bb0a8a3a33c4c68349484ef4f3dc915416e/lib/sqlalchemy/pool.py?at=master&fileviewer=file-view-default#pool.py-703
the `dbapi connection` happens to be closed.

Due to a lack of time (and having already spent quite a bit debugging), I am not able to create a PR. Also, better let the experts tackle the issue (I would verify if the connection is already closed prior to calling `reset`).

[Sqlalchemy-tickets] Issue #4183: Using func.sum(BinaryExp), returns boolean instead of a sum (wasn't the case in 1.1.12) (zzzeek/sqlalchemy)

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

New issue 4183: Using func.sum(BinaryExp), returns boolean instead of a sum (wasn't the case in 1.1.12)
https://bitbucket.org/zzzeek/sqlalchemy/issues/4183/using-funcsum-binaryexp-returns-boolean

Bastien Gérard:

Hi,
First of all, we are using MySQL 5.6

After switching from sqlalchemy 1.1.12 to 1.2.2, we observed that one of our test failed.

In fact the following scenario should return a RowProxy with the values being the sum but the values are boolean instead.

```
#!python
from sqlalchemy import select
from sqlalchemy.sql.functions import func

engine, tbl, col = ...
sel = select([func.sum(col.is_(None))])
print sel # SELECT sum(smart_result.signal_predicted IS NULL) AS sum_1 FROM smart_result
row = engine.execute(sel).fetchone()
print dict(row) # {u'sum_1': True}

# And if I execute it manually:
r = engine.execute('SELECT sum(smart_result.signal_predicted IS NULL) AS sum_1 FROM smart_result')
print r.fetchall() # [(Decimal('120'),)]
```

I don't know if it could be related but by printing row._key_map, I see references to "<function sqlalchemy.cprocessors.int_to_boolean>".

[Sqlalchemy-tickets] Issue #4182: pool_pre_ping does not properly detect the disconnect when the database user is dropped (zzzeek/sqlalchemy)

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

New issue 4182: pool_pre_ping does not properly detect the disconnect when the database user is dropped
https://bitbucket.org/zzzeek/sqlalchemy/issues/4182/pool_pre_ping-does-not-properly-detect-the

Mike Shantz:

sqlalchemy version: 1.2.2
database: oracle (cx_Oracle 6.1, oracle client 12.1)

Dropping the user of an open connection results in an exception being raised, instead of a graceful attempt to reconnect. (Note that my actual use case also provides a creator callback to create_engine, which is able to recover from this condition on the new connection).

As demonstrated below, the attempt to close the cursor in the finally clause, when the connection has entered this error state, results in a second uncaught exception.

```
#!python

Python 3.5.2 (default, Nov 23 2017, 16:37:01) 
[GCC 5.4.0 20160609] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> import sqlalchemy
>>> cx_Oracle.__version__
'6.1'
>>> cx_Oracle.clientversion
<built-in function clientversion>
>>> cx_Oracle.clientversion()
(12, 1, 0, 2, 0)
>>> sqlalchemy.__version__
'1.2.2'
>>> dsn = cx_Oracle.makedsn('xxx.xxx.xxx.xxx', 1521, service_name='some_oracle_service')
>>> user = 'valid_user'
>>> password = 'itsasecret'
>>> engine = sqlalchemy.create_engine('oracle://{user}:{passwd}@{dsn}'.format(user=user, passwd=password, dsn=dsn), pool_pre_ping=True)
>>> with engine.connect() as cx:
...     q = cx.execute('select * from dual')
...     print(q.fetchall())
... 
[('X',)]

```

At this point I drop the user from the database. Attempting to run the query a second time (with the same connection) I get:

```
#!python

>>> with engine.connect() as cx:
...     q = cx.execute('select * from dual')
...     print(q.fetchall())
... 
Traceback (most recent call last):
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/pool.py", line 345, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/pool.py", line 812, in _checkout
    result = pool._dialect.do_ping(fairy.connection)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 474, in do_ping
    cursor.execute(self._dialect_specific_select_one)
cx_Oracle.OperationalError: ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 139 Serial number: 5637

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

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2102, in connect
    return self._connection_cls(self, **kwargs)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 90, in __init__
    if connection is not None else engine.raw_connection()
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2188, in raw_connection
    self.pool.unique_connection, _connection)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2162, in _wrap_pool_connect
    e, dialect, self)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception_noconnection
    exc_info
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/pool.py", line 345, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/pool.py", line 812, in _checkout
    result = pool._dialect.do_ping(fairy.connection)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 474, in do_ping
    cursor.execute(self._dialect_specific_select_one)
sqlalchemy.exc.OperationalError: (cx_Oracle.OperationalError) ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 139 Serial number: 5637 (Background on this error at: http://sqlalche.me/e/e3q8)
>>> 

```

[Sqlalchemy-tickets] Issue #4181: @event.listens_for(Engine, 'before_cursor_execute') seems to be firing twice for an engine created `with_execution_options` from one that was created without! (zzzeek/sqlalchemy)

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

New issue 4181: @event.listens_for(Engine, 'before_cursor_execute') seems to be firing twice for an engine created `with_execution_options` from one that was created without!
https://bitbucket.org/zzzeek/sqlalchemy/issues/4181/eventlistens_for-engine

Antti Haapala:

This seems to be reproducible with SQLAlchemy 1.1, 1.2, 1.2.2 in *our* code, I've yet to produce a MCVE though, but we have an engine, out of which 2 other engines are created. The intention is that one of the engines is used to explicitly create sessions that have transaction isolation level at SERIALIZABLE, the other at READ COMMITTED - this seemed tricky to get right otherwise 

However, now we noticed some friction with Pyramid debug toolbar integration, which uses 

    @event.listens_for(Engine, "before_cursor_execute")

and 

    @event.listens_for(Engine, "after_cursor_execute")

These get called twice with the exact same arguments (str(args) is equal) when using an Engine that has been derived with `execution_options`, and just once when using an Engine that is created with plain constructor... so it would also mean that sharding example code would emit `use` twice per each cursor creation in the example code in http://docs.sqlalchemy.org/en/latest/core/connections.html#sqlalchemy.engine.Engine.execution_options

[Sqlalchemy-tickets] Issue #4180: Enum type stores wrong values in DB when aliases are used (zzzeek/sqlalchemy)

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

New issue 4180: Enum type stores wrong values in DB when aliases are used
https://bitbucket.org/zzzeek/sqlalchemy/issues/4180/enum-type-stores-wrong-values-in-db-when

Daniel Knell:

There is a problem with how SQLAlchemy stores Python Enum types when native enums are disabled and said Enum contains aliases.

```python
class Country(enum.Enum):
    GB = 'gb'
    FR = 'fr'
    UK = 'gb' # alias the GB item

foo = Table(
    'foo',
    metadata,
    Column('country', Enum(Country,native_enum=False,create_constraint=False))
)
```

```python
>>> Country.GB
<Country.GB: 'gb'>
>>> Country.UK
<Country.GB: 'gb'>
>>> Country.GB.name
'GB'
>>> Country.UK.name
'GB'
>>>
```

If you store the value Country.GB in the table foo it will be stored as UK instead of GB.

I believe this is caused by flipping the dictionary keys/values in Enum._setup_for_values, instead of using the name param of the enum.

https://github.com/zzzeek/sqlalchemy/blob/699272e4dcb9aa71ebbc0d9487fb6de82d3abc2b/lib/sqlalchemy/sql/sqltypes.py#L1357

```python
>>> import enum
>>>
>>> class Country(enum.Enum):
...     GB = 'gb'
...     FR = 'fr'
...     UK = 'gb' # alias the GB item
...
>>> values = list(Country.__members__)
>>> objects = [Country.__members__[k] for k in values]
>>> dict(zip(values, objects))
{'GB': <Country.GB: 'gb'>, 'FR': <Country.FR: 'fr'>, 'UK': <Country.GB: 'gb'>}
>>> dict((value, key) for key, value in _.items())
{<Country.GB: 'gb'>: 'UK', <Country.FR: 'fr'>: 'FR'}
>>>
```

https://gist.github.com/danielknell/e3c9c7b65b395a2e295ee8237a70286e

[Sqlalchemy-tickets] Issue #4179: table.tometadata(...) doesn't copy event handlers (zzzeek/sqlalchemy)

[Chris W. <iss...@bi...>]

New issue 4179: table.tometadata(...) doesn't copy event handlers
https://bitbucket.org/zzzeek/sqlalchemy/issues/4179/tabletometadata-doesnt-copy-event-handlers

Chris Withers:

I added some custom DDL to a table definition as follows:


```
#!python

event.listen(
    Observation.__table__,
    'after_create',
    DDL(
        'alter table observation add constraint observation_best ...'
    )
)
```

...but it wasn't firing, not even an exception when I deliberately misspelled the table name ;-)

Tracked it down to this class I use to allow one schema to be managed by multiple python packages:

https://github.com/Mortar/mortar_rdb/blob/master/mortar_rdb/controlled.py#L59

This line in particular:

https://github.com/Mortar/mortar_rdb/blob/master/mortar_rdb/controlled.py#L81

It appears that call isn't carrying the event handlers across to the table created in the new metadata object. For my use case, it needs to, but I appreciate my use case may well be a bit of an edge case.

[Sqlalchemy-tickets] Issue #4178: ORM reconstructor does not trigger when decorating an __init__ method (zzzeek/sqlalchemy)

[Joël P. <iss...@bi...>]

New issue 4178: ORM reconstructor does not trigger when decorating an __init__ method
https://bitbucket.org/zzzeek/sqlalchemy/issues/4178/orm-reconstructor-does-not-trigger-when

Joël Perras:

As mentioned in the [documentation](http://docs.sqlalchemy.org/en/latest/orm/constructors.html?highlight=reconstructor#constructors-and-object-initialization):

> Any method may be tagged as the orm.reconstructor(), even the `__init__` method itself. 

This, however, does not appear to be the case.

I've provided a simple test case based on existing reconstructor tests in `test/orm/test_mapper.py`, shown here as a diff against the current master branch (`399988aaed401ea6fc69aa580b9b71c236a30f16`).

```
#!diff

diff --git a/test/orm/test_mapper.py b/test/orm/test_mapper.py
index 42d114f6f..5f5ed11b9 100644
--- a/test/orm/test_mapper.py
+++ b/test/orm/test_mapper.py
@@ -1652,6 +1652,25 @@ class MapperTest(_fixtures.FixtureTest, AssertsCompiledSQL):
         create_session().query(User).first()
         eq_(recon, ['go'])

+    def test_reconstructor_init(self):
+
+        users = self.tables.users
+
+        recon = []
+
+        class User(object):
+
+            @reconstructor
+            def __init__(self):
+                recon.append('go')
+
+        mapper(User, users)
+
+        User()
+        eq_(recon, [])
+        create_session().query(User).first()
+        eq_(recon, ['go'])
+
```

I attempted to trace down why this was occurring in `orm.mapper.Mapper._configure_class_instrumentation`, but some of the indirection with regards to load events is more opaque to my limited understanding of how the mapper works than I'd like.

This is clearly not a major bug, since the workaround is relatively simple, but wanted to flag it.

[Sqlalchemy-tickets] Issue #4177: LargeBinary should truly be unlimited in MySQL (zzzeek/sqlalchemy)

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

New issue 4177: LargeBinary should truly be unlimited in MySQL
https://bitbucket.org/zzzeek/sqlalchemy/issues/4177/largebinary-should-truly-be-unlimited-in

NameZero912:

The [docs](http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.LargeBinary) mention "...a large and/or unlengthed binary type for the target platform, such as BLOB on MySQL". Using BLOB on MySQL doesn't make sense (as it is limited to 64 kb). LONGBLOB seems to be a better choice (4 GB limit).

[Sqlalchemy-tickets] Issue #4176: Accessing deferred column property after flush loads all deferred column properties (zzzeek/sqlalchemy)

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

New issue 4176: Accessing deferred column property after flush loads all deferred column properties
https://bitbucket.org/zzzeek/sqlalchemy/issues/4176/accessing-deferred-column-property-after

Adrian:

Example to reproduce:

```
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *
from sqlalchemy.dialects.postgresql import JSON

Base = declarative_base()


class ProtectionMode(object):
    public = 0
    inheriting = 1
    protected = 2


class Event(Base):
    __tablename__ = 'events'
    id = Column(Integer, primary_key=True)
    category_id = Column(Integer, ForeignKey('categories.id'), nullable=True, index=True)
    category = relationship('Category', backref='events')

    def __repr__(self):
        return '<Event {}>'.format(self.id)


class Category(Base):
    __tablename__ = 'categories'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('categories.id'), index=True, nullable=True)
    protection_mode = Column(SmallInteger, nullable=False, default=ProtectionMode.inheriting)
    icon_metadata = Column(JSON, nullable=False, default=lambda: None)
    children = relationship('Category', primaryjoin=(id == remote(parent_id)),
                            backref=backref('parent', primaryjoin=(remote(id) == parent_id)))

    @classmethod
    def get_icon_data_cte(cls):
        cat_alias = aliased(cls)
        cte_query = (select([cat_alias.id, cat_alias.id.label('source_id'), cat_alias.icon_metadata])
                     .where(cat_alias.parent_id.is_(None))
                     .cte(recursive=True))
        rec_query = (select([cat_alias.id,
                             case({'null': cte_query.c.source_id}, else_=cat_alias.id,
                                  value=func.json_typeof(cat_alias.icon_metadata)),
                             case({'null': cte_query.c.icon_metadata}, else_=cat_alias.icon_metadata,
                                  value=func.json_typeof(cat_alias.icon_metadata))])
                     .where(cat_alias.parent_id == cte_query.c.id))
        return cte_query.union_all(rec_query)

    @classmethod
    def get_protection_cte(cls):
        cat_alias = aliased(cls)
        cte_query = (select([cat_alias.id, cat_alias.protection_mode])
                     .where(cat_alias.parent_id.is_(None))
                     .cte(recursive=True))
        rec_query = (select([cat_alias.id,
                             case({ProtectionMode.inheriting: cte_query.c.protection_mode},
                                  else_=cat_alias.protection_mode, value=cat_alias.protection_mode)])
                     .where(cat_alias.parent_id == cte_query.c.id))
        return cte_query.union_all(rec_query)

    def __repr__(self):
        return '<Category {}>'.format(self.id)


cte = Category.get_protection_cte()
query = select([cte.c.protection_mode]).where(cte.c.id == Category.id).correlate_except(cte)
Category.effective_protection_mode = column_property(query, deferred=True)

cte = Category.get_icon_data_cte()
query = (select([func.json_build_object('source_id', cte.c.source_id,
                                        'metadata', cte.c.icon_metadata)])
         .where(cte.c.id == Category.id)
         .correlate_except(cte))
Category.effective_icon_data = column_property(query, deferred=True)


e = create_engine('postgresql:///test', echo=True)
Base.metadata.create_all(e)
s = Session(e)


if not s.query(Category).count():
    root = Category(protection_mode=ProtectionMode.public)
    cat = Category(parent=root)
    s.add(root)
    e = Event(category=root)
    s.add(e)
    s.commit()
    print 'created data'
else:
    root = s.query(Category).filter_by(parent_id=None).one()
    cat = root.children[0]
    e = root.events[0]
    print '\n\n\nFIRST READ\n'
    print cat.effective_protection_mode
    e.category = cat
    s.flush()
    print '\n\n\nSECOND READ\n'
    print cat.effective_protection_mode
```

---

Relevant output (on the second run):

```
FIRST READ

2018-01-31 16:05:18,233 INFO sqlalchemy.engine.base.Engine WITH RECURSIVE anon_2(id, protection_mode) AS
(SELECT categories_1.id AS id, categories_1.protection_mode AS protection_mode
FROM categories AS categories_1
WHERE categories_1.parent_id IS NULL UNION ALL SELECT categories_1.id AS id, CASE categories_1.protection_mode WHEN %(param_1)s THEN anon_2.protection_mode ELSE categories_1.protection_mode END AS anon_3
FROM categories AS categories_1, anon_2
WHERE categories_1.parent_id = anon_2.id)
 SELECT (SELECT anon_2.protection_mode
FROM anon_2
WHERE anon_2.id = categories.id) AS anon_1
FROM categories
WHERE categories.id = %(param_2)s
2018-01-31 16:05:18,233 INFO sqlalchemy.engine.base.Engine {'param_1': 1, 'param_2': 2}
0
2018-01-31 16:05:18,234 INFO sqlalchemy.engine.base.Engine UPDATE events SET category_id=%(category_id)s WHERE events.id = %(events_id)s
2018-01-31 16:05:18,234 INFO sqlalchemy.engine.base.Engine {'category_id': 2, 'events_id': 1}



SECOND READ

2018-01-31 16:05:18,236 INFO sqlalchemy.engine.base.Engine WITH RECURSIVE anon_2(id, protection_mode) AS
(SELECT categories_1.id AS id, categories_1.protection_mode AS protection_mode
FROM categories AS categories_1
WHERE categories_1.parent_id IS NULL UNION ALL SELECT categories_1.id AS id, CASE categories_1.protection_mode WHEN %(param_1)s THEN anon_2.protection_mode ELSE categories_1.protection_mode END AS anon_3
FROM categories AS categories_1, anon_2
WHERE categories_1.parent_id = anon_2.id),
anon_5(id, source_id, icon_metadata) AS
(SELECT categories_2.id AS id, categories_2.id AS source_id, categories_2.icon_metadata AS icon_metadata
FROM categories AS categories_2
WHERE categories_2.parent_id IS NULL UNION ALL SELECT categories_2.id AS id, CASE json_typeof(categories_2.icon_metadata) WHEN %(param_2)s THEN anon_5.source_id ELSE categories_2.id END AS anon_6, CASE json_typeof(categories_2.icon_metadata) WHEN %(param_3)s THEN anon_5.icon_metadata ELSE categories_2.icon_metadata END AS anon_7
FROM categories AS categories_2, anon_5
WHERE categories_2.parent_id = anon_5.id)
 SELECT (SELECT anon_2.protection_mode
FROM anon_2
WHERE anon_2.id = categories.id) AS anon_1, (SELECT json_build_object(%(json_build_object_2)s, anon_5.source_id, %(json_build_object_3)s, anon_5.icon_metadata) AS json_build_object_1
FROM anon_5
WHERE anon_5.id = categories.id) AS anon_4
FROM categories
WHERE categories.id = %(param_4)s
2018-01-31 16:05:18,236 INFO sqlalchemy.engine.base.Engine {'param_4': 2, 'param_1': 1, 'param_3': 'null', 'param_2': 'null', 'json_build_object_3': 'metadata', 'json_build_object_2': 'source_id'}
0
```

As you can see, the second time the property is accessed, the other deferred column property is loaded as well - this results in a massive performance drop in the real (non-simplified) code since some of the deferred relationships are quite expensive.

This issue happens in 1.1.15 but not in 1.2.2, but I couldn't find any related changelog entry.

[Sqlalchemy-tickets] Issue #4175: selectinload raises an exception when used with sharded sessions (zzzeek/sqlalchemy)

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

New issue 4175: selectinload raises an exception when used with sharded sessions
https://bitbucket.org/zzzeek/sqlalchemy/issues/4175/selectinload-raises-an-exception-when-used

Rishi Sharma:

```
#!python

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

Base = declarative_base()


class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer, primary_key=True)
    pages = relationship('Page')


class Page(Base):
    __tablename__ = 'page'
    id = Column(Integer, primary_key=True)
    book_id = Column(ForeignKey('book.id'))


def test_query_with_selectinload(session):
    book = Book()
    book.pages.append(Page())

    session.add(book)
    session.commit()

    session.query(Book).options(selectinload('pages')).all()

if __name__ == "__main__":
    engine = create_engine("mysql://root:@127.0.0.1:3306/test", echo=True)
    Base.metadata.create_all(engine)

    # vanilla_session = Session(engine)
    # test_query_with_selectinload(vanilla_session)

    sharded_session = scoped_session(
        sessionmaker(
            class_=ShardedSession,
        )
    )

    sharded_session.configure(
        shards=dict(test=engine),
        shard_chooser=lambda *args: 'test',
        id_chooser=lambda *args: None,
        query_chooser=lambda *args: ['test']
    )

    test_query_with_selectinload(sharded_session)

```
produces the following stack trace:


```
#!text

Traceback (most recent call last):
  File "selectinload_horizontal_shard_query_test_case.py", line 50, in <module>
    test_query_with_selectinload(sharded_session)
  File "selectinload_horizontal_shard_query_test_case.py", line 28, in test_query_with_selectinload
    session.query(Book).options(selectinload('pages')).all()
  File "/Users/rsharma/src/evolve/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2726, in all
    return list(self)
  File "/Users/rsharma/src/evolve/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2878, in __iter__
    return self._execute_and_instances(context)
  File "/Users/rsharma/src/evolve/env/lib/python2.7/site-packages/sqlalchemy/ext/horizontal_shard.py", line 59, in _execute_and_instances
    partial.extend(iter_for_shard(shard_id))
  File "/Users/rsharma/src/evolve/env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 97, in instances
    util.raise_from_cause(err)
  File "/Users/rsharma/src/evolve/env/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Users/rsharma/src/evolve/env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 85, in instances
    post_load.invoke(context, path)
  File "/Users/rsharma/src/evolve/env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 759, in invoke
    context, path, states, self.load_keys, *arg, **kw)
  File "<string>", line 1, in <lambda>
  File "/Users/rsharma/src/evolve/env/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 1966, in _load_for_path
    lambda x: x[0]
  File "/Users/rsharma/src/evolve/env/lib/python2.7/site-packages/sqlalchemy/ext/baked.py", line 341, in __iter__
    return q._execute_and_instances(context)
  File "/Users/rsharma/src/evolve/env/lib/python2.7/site-packages/sqlalchemy/ext/horizontal_shard.py", line 59, in _execute_and_instances
    partial.extend(iter_for_shard(shard_id))
  File "/Users/rsharma/src/evolve/env/lib/python2.7/site-packages/sqlalchemy/ext/horizontal_shard.py", line 48, in iter_for_shard
    mapper=self._mapper_zero(),
  File "/Users/rsharma/src/evolve/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 328, in _mapper_zero
    return self._entities[0].mapper
AttributeError: '_BundleEntity' object has no attribute 'mapper'
```

[Sqlalchemy-tickets] Issue #4174: Parenthesis Ignored (zzzeek/sqlalchemy)

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

New issue 4174: Parenthesis Ignored
https://bitbucket.org/zzzeek/sqlalchemy/issues/4174/parenthesis-ignored

Lukas Siemon:

Minimal Test Case below (uses postgresql 9.6 latest).

Generates SQL (which is obviously invalid):
```sql
SELECT
  now() + CAST(%(param_1)s AS INTERVAL) at time zone %(param_2)s AS anon_1, 
  person.id AS person_id
FROM
  person
```

We are solving this for now by casting explicitly to DateTime, but we would expect parenthesis to be handled correctly.


```python
import unittest
from sqlalchemy import Column, Integer, func
from sqlalchemy.dialects.postgresql import INTERVAL
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import column_property
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = (
    "postgresql://postgres:password@localhost:5432/gitl_tmp")
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

Base = declarative_base()


class Person(db.Model):
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True, nullable=False)
    time = column_property(
        (
            func.now() +
            func.cast('1 week', INTERVAL)
        ).op("at time zone")('America/Vancouver')
    )


db.drop_all()
db.create_all()


class TestExecutionOrder(unittest.TestCase):

    def test_execution_order(self):

        db.session.add(Person())
        db.session.commit()

        query = Person.query
        print query
        person = Person.query.one()
        print person.time
```

[Sqlalchemy-tickets] Issue #4173: begin_twophase() method throws error as 'Not ImplmentedError' for mssql+pyodbc Dialect (zzzeek/sqlalchemy)

[Siddhesh N. <iss...@bi...>]

New issue 4173: begin_twophase() method throws error as 'Not ImplmentedError' for mssql+pyodbc Dialect
https://bitbucket.org/zzzeek/sqlalchemy/issues/4173/begin_twophase-method-throws-error-as-not

Siddhesh Naik:

Hi,

I am using sql alchemy core library. When I am trying to open XA transaction for MS Sql Server using mssql+pyodbc Dialects its throws error  as Not ImplmentedError.

My connection string is 
```
#!python

"mssql+pyodbc://****:*****@192.168.2.138:1433/SourceDB?driver=FreeTDS&; odbc_options='TDS_Version=7.2'"
```
Kindly help

Responsible: _diana_

[Sqlalchemy-tickets] Issue #4172: 'on_duplicate_key_update' for MySQL doesn't let using values from already inserted row (zzzeek/sqlalchemy)

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

New issue 4172: 'on_duplicate_key_update' for MySQL doesn't let using values from already inserted row
https://bitbucket.org/zzzeek/sqlalchemy/issues/4172/on_duplicate_key_update-for-mysql-doesnt

Softbreakers:

>From sqlalchemy.dialects.mysql.base code comments:

*``ON DUPLICATE KEY UPDATE`` is used to perform an update of the already
existing row, using any combination of new values as well as values
from the proposed insertion.*

But in case of duplicate, MySQL 5.7 supports update using new values, values from proposed insertion **and values from the already existing row**.

[For example](https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html):
*INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;*

Suppose a table with two columns: 'name' as string and 'counter' as integer.
If I try something like:

   *insert_stmt = insert(tableobject)*

  *on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(counter='counter+1')*

   *values =[{'name':valname, 'counter':1, }]*

   *conexBD.execute(on_duplicate_key_stmt, values)*

The engine assumes 'counter+1' as a literal value, and generates a SQL statement using quotes like:

*'INSERT INTO mytable (name, counter) VALUES (%s, %s) ON DUPLICATE KEY UPDATE counter = %s'] [parameters: ('any_name', 1, 'counter+1')]*

Which results in a exception message like:

*(1366, "Incorrect integer value: 'counter+1' for column 'counter' at row 1")*

[Sqlalchemy-tickets] Issue #4171: list assignment does not seem to be affecting remove from previous object correctly (zzzeek/sqlalchemy)

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

New issue 4171: list assignment does not seem to be affecting remove from previous object correctly
https://bitbucket.org/zzzeek/sqlalchemy/issues/4171/list-assignment-does-not-seem-to-be

Michael Bayer:

major regression, needs immediate release


```
#!python

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

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)


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

e = create_engine("sqlite://", echo='debug')
Base.metadata.create_all(e)

session = Session(e)

for i in xrange(100):
    a1 = A()
    a2 = A()

    b = B(a=a1)  # Set B.a = a1
    a2.bs = [b]  # ... but then put b onto a2's list

    session.add_all([a1, a2])
    session.commit()

    print len(a1.bs)


```

a1.bs is not consistently zero, depending on which of a1 or a2 is processed first.  list assignment not updating state correctly.    http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#a-validates-method-receives-all-values-on-bulk-collection-set-before-comparison is likely cause

[Sqlalchemy-tickets] Issue #4170: URL w/ repeated params (zzzeek/sqlalchemy)

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

New issue 4170: URL w/ repeated params
https://bitbucket.org/zzzeek/sqlalchemy/issues/4170/url-w-repeated-params

Michael Bayer:

bug number one:


```
#!python

>>> from sqlalchemy.engine import url
>>> u = url.make_url("mysql://s:p@host/db?plugin=foo&plugin=bar")
>>> u.query
{'plugin': 'bar'}

```

that's a big deal.   the plugin system didn't assume this was the case.

then, of course, 


```
#!python

>>> u.query['plugin'] = ['foo', 'bar']
>>> str(u)
"mysql://s:p@host/db?plugin=['foo', 'bar']"

```

[Sqlalchemy-tickets] Issue #4169: Inconsistent Execution order for "mapper_configured" (zzzeek/sqlalchemy)

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

New issue 4169: Inconsistent Execution order for "mapper_configured"
https://bitbucket.org/zzzeek/sqlalchemy/issues/4169/inconsistent-execution-order-for

Lukas Siemon:

We have been getting random failures. I narrowed this down to non deterministic execution order. I've included a (somewhat) minimal test case below.

The way the test case is currently set up it sometimes fails and other times it doesn't (might depend on compilation order?). If I comment the first `sleep(1)` the test always fails. If I comment the second `sleep(1)` the test never fails.

We would expect the execution order to be consistent.

```python
import unittest
from time import sleep
from sqlalchemy import Column, Integer, event, literal, func, select
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import mapper, column_property
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = ("sqlite://")
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

Base = declarative_base()


class Venue(db.Model):
    __tablename__ = 'venue'
    id = Column(Integer, primary_key=True, nullable=False)


class Offer(db.Model):
    __tablename__ = 'offer'
    id = Column(Integer, primary_key=True, nullable=False)


@event.listens_for(mapper, "mapper_configured")
def mapper_listener(mapper_, cls):
    if issubclass(cls, Venue):
        sleep(1)
        cls.info = column_property(
            select([
                func.SUM(Offer.info)
            ]).where(
                Offer.id <= Venue.id
            ).as_scalar()
        )

@event.listens_for(mapper, "mapper_configured")
def mapper_listener(mapper_, cls):
    if issubclass(cls, Offer):
        sleep(1)
        cls.info = column_property(cls.id + literal("1"))

db.drop_all()
db.create_all()


class TestExecutionOrder(unittest.TestCase):

    def test_execution_order(self):

        db.session.add(Venue())
        db.session.add(Offer())
        db.session.commit()

        venue = Venue.query.one()
        print venue.info
```

Stacktrace when test fails:
```sh
Traceback (most recent call last):
  File "/usr/lib/python2.7/unittest/case.py", line 329, in run
    testMethod()
  File ".../test.py", line 53, in test_execution_order
    db.session.add(Venue())
  File "<string>", line 2, in __init__
  File ".../env/local/lib/python2.7/site-packages/sqlalchemy/orm/instrumentation.py", line 347, in _new_state_if_none
    state = self._state_constructor(instance, self)
  File ".../env/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 767, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File ".../env/local/lib/python2.7/site-packages/sqlalchemy/orm/instrumentation.py", line 177, in _state_constructor
    self.dispatch.first_init(self, self.class_)
  File ".../env/local/lib/python2.7/site-packages/sqlalchemy/event/attr.py", line 256, in __call__
    fn(*args, **kw)
  File ".../env/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 3123, in _event_on_first_init
    configure_mappers()
  File ".../env/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 3016, in configure_mappers
    mapper, mapper.class_)
  File ".../env/local/lib/python2.7/site-packages/sqlalchemy/event/attr.py", line 218, in __call__
    fn(*args, **kw)
  File ".../env/local/lib/python2.7/site-packages/sqlalchemy/orm/events.py", line 621, in wrap
    fn(*arg, **kw)
  File ".../test.py", line 33, in mapper_listener
    func.SUM(Offer.info)
AttributeError: type object 'Offer' has no attribute 'info'
```

Running latest PostgreSQL 9.6

[Sqlalchemy-tickets] Issue #4168: Nested composite column types (zzzeek/sqlalchemy)

[Psyche N. <iss...@bi...>]

New issue 4168: Nested composite column types
https://bitbucket.org/zzzeek/sqlalchemy/issues/4168/nested-composite-column-types

Psyche NA:

According to the [document](http://docs.sqlalchemy.org/en/latest/orm/composites.html)

I have a `Point` class:


```
#!python

class Point(object):
    def __init__(self, x, y):
        self.x = x
        self.y = y

    def __composite_values__(self):
        return self.x, self.y
```

I could create a mapping to a table `vertices`, which represents two points as x1/y1 and x2/y2. 
```
#!python

class Vertex(Base):
    __tablename__ = 'vertices'

    id = Column(Integer, primary_key=True)
    x1 = Column(Integer)
    y1 = Column(Integer)
    x2 = Column(Integer)
    y2 = Column(Integer)

    start = composite(Point, x1, y1)
    end = composite(Point, x2, y2)
```
But the problem is that I have two classes `Point` and `Line`:

```
#!python

class Point(object):
    def __init__(self, x, y):
        self.x = x
        self.y = y


class Line(object):
    def __init__(self, start: Point, end: Point):
        self.start = start
        self.end = end
```
I could not create a mapping to a table `Shape` like this:


```
#!python
class Shape(Base):
    __tablename__ = 'shapes'

    id = Column(Integer, primary_key=True)
    a1 = Column(Integer)
    a2 = Column(Integer)
    b1 = Column(Integer)
    b2 = Column(Integer)
    c1 = Column(Integer)
    c2 = Column(Integer)
    d1 = Column(Integer)
    d2 = Column(Integer)
    ...

    line1 = composite(Line, composite(Point, a1, a2), composite(Point, b1, b2))
    line2 = composite(Line, composite(Point, c1, c2), composite(Point, d1, d2))
    ...
```
Maybe the example above is not good enough. But the point is that could composite be nested?

[Sqlalchemy-tickets] Issue #4167: Support baked query for update and delete operations (zzzeek/sqlalchemy)

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

New issue 4167: Support baked query for update and delete operations
https://bitbucket.org/zzzeek/sqlalchemy/issues/4167/support-baked-query-for-update-and-delete

Hu Bo:

Currently only SELECT operations can be "baked". query(...).update and query(...).delete cannot be baked.

Maybe it is possible to use the same technique to cache bulk queries generated by update() and delete().

[Sqlalchemy-tickets] Issue #4166: Documentation typo under External Dialects (zzzeek/sqlalchemy)

[Edward k. <iss...@bi...>]

New issue 4166: Documentation typo under External Dialects
https://bitbucket.org/zzzeek/sqlalchemy/issues/4166/documentation-typo-under-external-dialects

Edward kovarski:

Hi,

Small typo on the following page,

http://docs.sqlalchemy.org/en/latest/dialects/index.html

Under Production Ready it says "pythone-tds" instead of "python-tds"

e.g. sqlalchemy-tds - driver for MS-SQL, on top of pythone-tds.

[Sqlalchemy-tickets] Issue #4165: Recursive CRE as secondary for ORM relationship? (zzzeek/sqlalchemy)

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

New issue 4165: Recursive CRE as secondary for ORM relationship?
https://bitbucket.org/zzzeek/sqlalchemy/issues/4165/recursive-cre-as-secondary-for-orm

Sebastian Bank:

Not sure whether I did not read the docs carefully enough, or this is a bug, or a new feature.
Trying to use a recursive CTE (e.g. in [sqlite3](https://www.sqlite.org/lang_with.html)) as join table for a relationship:
```python
import sqlalchemy as sa
import sqlalchemy.ext.declarative

class Node(sa.ext.declarative.declarative_base()):

    __tablename__ = 'node'

    id = sa.Column(sa.Integer, primary_key=True)
    parent_id = sa.Column(sa.ForeignKey('node.id'))

    @classmethod
    def tree(cls):
        child, parent = (sa.orm.aliased(cls, name=n) for n in ('child', 'parent'))
        tree_1 = sa.select([
                child.id.label('child_id'),
                child.parent_id.label('parent_id'),
            ]).where(child.parent_id != None)\
            .cte(recursive=True)\
            .alias('tree')
        tree_2 = sa.select([tree_1.c.child_id, parent.parent_id])\
            .select_from(tree_1.join(parent, parent.id == tree_1.c.parent_id))\
            .where(parent.parent_id != None)
        return tree_1.union_all(tree_2)

tree = Node.tree()

Node.ancestors = sa.orm.relationship(Node, secondary=tree,
    primaryjoin=Node.id == tree.c.child_id,
    secondaryjoin=Node.id == tree.c.parent_id)

session = sa.orm.Session()

print(session.query(Node).filter(Node.ancestors.any(id=42)))

# output
'''
WITH RECURSIVE tree(child_id, parent_id) AS 
(SELECT child.id AS child_id, child.parent_id AS parent_id 
FROM node AS child 
WHERE child.parent_id IS NOT NULL UNION ALL SELECT tree.child_id AS child_id, parent.parent_id AS parent_id 
FROM tree JOIN node AS parent ON parent.id = tree.parent_id 
WHERE parent.parent_id IS NOT NULL)
 SELECT node.id AS node_id, node.parent_id AS node_parent_id 
FROM node 
WHERE EXISTS (SELECT 1 
FROM tree AS anon_1, tree, node AS node_1 
WHERE anon_1.child_id = tree.child_id AND node_1.id = tree.parent_id AND node_1.id = :id_1)
'''

# expected
'''
(...)
WHERE EXISTS (SELECT 1 
FROM tree AS anon_1, node AS node_1 
WHERE anon_1.child_id = node.child_id AND node_1.id = anon_1.parent_id AND node_1.id = :id_1)
'''
```
Sorry for the long example (and thanks in advance for taking a look).

[Sqlalchemy-tickets] Issue #4164: Disconnect Handling not working with MSSQL (zzzeek/sqlalchemy)

[Boris Z. <iss...@bi...>]

New issue 4164: Disconnect Handling not working with MSSQL
https://bitbucket.org/zzzeek/sqlalchemy/issues/4164/disconnect-handling-not-working-with-mssql

Boris Zyranov:

SqlAlchemy VERSION 1.2.1
Microsoft SQL Server 2014
Python 3.6.3

SqlAlchemy fails to detect disconnect as per http://docs.sqlalchemy.org/en/latest/core/pooling.html#dealing-with-disconnects


```
#!python

engine_connect_str = "mssql+pyodbc://{uid}:{pwd}@{srv}/{db}?driver=SQL Server".format(...)
en = create_engine(engine_connect_str)
print(list(en.execute("select * from t")))
```


[(11, 'eleven'), (12, 'twelve')]

... MSSQL server restart ...


```
#!python

try:
    print(list(en.execute("select * from t")))
except exc.DBAPIError as e:
    print(type(e))
    print("connection invalidated", e.connection_invalidated)
    print(e)

```

<class 'sqlalchemy.exc.DBAPIError'>

connection invalidated **False**

(pyodbc.Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Driver]
[DBNETLIB]ConnectionWrite (send()). (10054) (SQLExecDirectW)') (Background on this error at: http://sqlalche.me/e/dbapi)


Third call works without me having to manually refresh engine:


```
#!python

print(list(en.execute("select * from t")))
```

[(11, 'eleven'), (12, 'twelve')]

Pessimistic approach also does not work:


```
#!python

en = create_engine(engine_connect_str, pool_pre_ping=True)
print(list(en.execute("select * from t")))
```

[(11, 'eleven'), (12, 'twelve')]


```
#!python

try:
    print(list(en.execute("select * from t")))
except exc.DBAPIError as e:
    print(type(e))
    print("connection invalidated", e.connection_invalidated)
    print(e)
```

    
<class 'sqlalchemy.exc.DBAPIError'>

connection invalidated **False**

(pyodbc.Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()). (10054) (SQLExecDirectW)') (Background on this error at: http://sqlalche.me/e/dbapi)


```
#!python

print(list(en.execute("select * from t")))
```

[(11, 'eleven'), (12, 'twelve')]

[Sqlalchemy-tickets] Issue #4163: cx_oracle requires method to force use of NCHAR and setinputsizes for strings (zzzeek/sqlalchemy)

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

New issue 4163: cx_oracle requires method to force use of NCHAR and setinputsizes for strings
https://bitbucket.org/zzzeek/sqlalchemy/issues/4163/cx_oracle-requires-method-to-force-use-of

Michael Bayer:

thread at:

https://groups.google.com/d/msg/sqlalchemy/vsJBLakBP4Y/PVz5zSKCAAAJ

Anthony's info for a similar issue at:

https://github.com/oracle/python-cx_Oracle/issues/119

potential APIs:

    e = create_engine("oracle://...", use_nchar_for_unicode=True)

or:


```
#!python

from sqlalchemy.dialects.oracle import NVARCHAR2

...   Column('data', NVARCHAR2(50, use_nchar=True))
```

A more open-ended approach, since we occasionally have issues with setinputsizes being needed or not:


```
#!python

e = create_engine("oracle://...")
e.dialect.setinputsizes(Unicode, cx_Oracle.NCHAR)

```

the third approach could also accommodate for user-defined types.   it would need some changes in DefaultDialect.set_input_sizes as well.

it's not that easy to let users just override get_dbapi_type() because that is only called upon the "impl" type that is mostly dialect-private.





the former is way easier to use but I don't know if people are going to want this applied to all datatypes across the board.

[Sqlalchemy-tickets] Issue #4162: columns as keys for insert.values() with multi-values (zzzeek/sqlalchemy)

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

New issue 4162: columns as keys for insert.values() with multi-values
https://bitbucket.org/zzzeek/sqlalchemy/issues/4162/columns-as-keys-for-insertvalues-with

Michael Bayer:

even though columns-as-keys is de-emphasized in the documentation for insert, it does work, except for multi-values:

```
#!python


from sqlalchemy import *
from sqlalchemy.dialects import sqlite

t = table('t', column('a'), column('b'))

print t.insert().values([
    {"a": 1, "b": 2},
    {"a": 2, "b": 2},
    {"a": 3, "b": 2}
]).compile(dialect=sqlite.dialect())


print t.insert().values([
    {t.c.a: 1, t.c.b: 2},
    {t.c.a: 2, t.c.b: 2},
    {t.c.a: 3, t.c.b: 2},
]).compile(dialect=sqlite.dialect())
```

output:


```
#!

INSERT INTO t (a, b) VALUES (?, ?), (?, ?), (?, ?)
Traceback (most recent call last):
...
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/crud.py", line 419, in _process_multiparam_default_bind
    "a Python-side value or SQL expression is required" % c)
sqlalchemy.exc.CompileError: INSERT value for column t.a is explicitly rendered as a boundparameter in the VALUES clause; a Python-side value or SQL expression is required

```