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

[Sqlalchemy-tickets] Issue #3230: Overlapping Composite Keys confuse the Dependency Rule (zzzeek/sqlalchemy)

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

New issue 3230: Overlapping Composite Keys confuse the Dependency Rule
https://bitbucket.org/zzzeek/sqlalchemy/issue/3230/overlapping-composite-keys-confuse-the

nickretallack:

I see there was [already an issue about overlapping composite keys in the past](https://bitbucket.org/zzzeek/sqlalchemy/issue/2965/foreign_keys-logic-with-overlapping), but maybe mine's different, as this issue still exists in 0.9.8.  The issue occurs when you try to un-relate some records with overlapping keys.

Here's my example:

```
#!python

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

Base = declarative_base()

# Lets simulate a multi-tenant database system that doesn't use multiple schemas
class Tenant(Base):
	__tablename__ = 'tenant'
	id = Column(Integer, primary_key=True)
	name = Column(String)

# Something to be related to
class Friend(Base):
	__tablename__ = 'friend'
	id = Column(Integer, primary_key=True)
	tenant_id = Column(ForeignKey('tenant.id'), primary_key=True)
	name = Column(String)
	tenant = relationship(Tenant)

# Our model
class Person(Base):
	__tablename__ = 'person'
	id = Column(Integer, primary_key=True)
	tenant_id = Column(ForeignKey('tenant.id'), primary_key=True)
	friend_id = Column(Integer)
	name = Column(String)

	__table_args__ = (
		# friend relationship
		ForeignKeyConstraint(
			(friend_id, tenant_id),
			('friend.id','friend.tenant_id'),
		),
	)

	tenant = relationship(Tenant)
	friend = relationship(Friend)

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)

def run():
	# Basic Setup
	Base.metadata.create_all(engine)
	session = Session()

	# Make a person with a friend
	tenant = Tenant(name="testing", id=1)
	person = Person(name="Bob", tenant=tenant, id=1)
	friend = Friend(name="George", tenant=tenant, id=1)
	person.friend = friend
	session.add_all([tenant, person, friend])
	session.commit()

	# Take away the friend
	person = session.query(Person).first()
	person.friend = None
	session.commit()
	# it tries to take away the tenant_id too, thus breaking the primary key

if __name__ == '__main__':
	run()
```

When I run it, I get this:

```
AssertionError: Dependency rule tried to blank-out primary key column 'person.tenant_id' on instance '<Person at 0x105c05310>'
```

It shouldn't be blanking out the tenant_id, since that id still references the tenant.  The relationship needs to know that, despite joining on a composite key here, only one of those columns is specific to this relationship.  The other is shared with many other relationships.

[Sqlalchemy-tickets] Issue #3229: query.update shouldn' use the hybrid_property expression method (zzzeek/sqlalchemy)

[Jean-Sébastien S. <iss...@bi...>]

New issue 3229: query.update shouldn' use the hybrid_property expression method
https://bitbucket.org/zzzeek/sqlalchemy/issue/3229/queryupdate-shouldn-use-the

Jean-Sébastien Suzanne:

Hi Mike, 

I have a second request with the [report #3228](https://bitbucket.org/zzzeek/sqlalchemy/issue/3228/queryupdate-does-not-resolve-string-names)

this is the example:

    from sqlalchemy import Column, Integer, String, create_engine, func
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.orm import sessionmaker
    from sqlalchemy.ext.hybrid import hybrid_property


    engine = create_engine('sqlite:///memory')
    Base = declarative_base()


    class Person(Base):
        __tablename__ = 'person'

        id = Column(Integer, primary_key=True)
        first_name = Column(String(64))
        last_name = Column(String(64))

        @hybrid_property
        def name(self):
            return self.first_name + ' ' + self.last_name

        @name.setter
        def name(self, value):
            self.first_name, self.last_name = value.split(' ', 1)

        @name.expression
        def name(cls):
            return func.concat(cls.first_name, ' ', cls.last_name)


    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)

    session = Session()
    t = Person(name='James BOND')
    session.add(t)
    session.query(Person).update({Person.name: 'Dr. No'})

And the traceback:

    Traceback (most recent call last):
      File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1022, in _execute_context
        context)
      File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/engine/default.py", line 436, in do_execute
        cursor.execute(statement, parameters)
    sqlite3.OperationalError: near "(": syntax error

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

    Traceback (most recent call last):
      File "./bin/python", line 63, in <module>
        exec(compile(__file__f.read(), __file__, "exec"))
      File "hybrid_property.py", line 37, in <module>
        session.query(Person).update({Person.name: 'Dr. No'})
      File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/orm/query.py", line 2848, in update
        update_op.exec_()
      File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/orm/persistence.py", line 906, in exec_
        self._do_exec()
      File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/orm/persistence.py", line 1036, in _do_exec
        update_stmt, params=self.query._params)
      File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/orm/session.py", line 982, in execute
        bind, close_with_result=True).execute(clause, params or {})
      File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/engine/base.py", line 800, in execute
        return meth(self, multiparams, params)
      File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
        return connection._execute_clauseelement(self, multiparams, params)
      File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/engine/base.py", line 897, in _execute_clauseelement
        compiled_sql, distilled_params
      File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1029, in _execute_context
        context)
      File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1230, in _handle_dbapi_exception
        exc_info
      File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/util/compat.py", line 188, in raise_from_cause
        reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
      File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/util/compat.py", line 181, in reraise
        raise value.with_traceback(tb)
      File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1022, in _execute_context
        context)
      File "/Users/jssuzanne/erpblok/anyblok/sqlalchemy/lib/sqlalchemy/engine/default.py", line 436, in do_execute
        cursor.execute(statement, parameters)
      sqlalchemy.exc.OperationalError: (OperationalError) near "(": syntax error 'UPDATE person SET concat(first_name, ?, last_name)=?' ('Dr. No', ' ')

I think the better way is adding new callback in hybrid_property like

    @name.update_expression
    def name(cls, value):
        first_name, last_name = value.split(' ', 1)
        return cls.first_name = first_name, cls.last_name = last_name

Regard,

[Sqlalchemy-tickets] Issue #3228: doesn't update a hybrid_property by a query (zzzeek/sqlalchemy)

[Jean-Sébastien S. <iss...@bi...>]

New issue 3228: doesn't update a hybrid_property by a query
https://bitbucket.org/zzzeek/sqlalchemy/issue/3228/doesnt-update-a-hybrid_property-by-a-query

Jean-Sébastien Suzanne:

Hi,

One example is better than my english.

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.hybrid import hybrid_property


engine = create_engine('sqlite:///memory')
Base = declarative_base()


class Test(Base):
    __tablename__ = 'plop'

    id = Column(Integer, primary_key=True)
    code = Column(String(64))

    @hybrid_property
    def code2(self):
        return self.code

    @code2.setter
    def code2(self, value):
        self.code = value


Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

session = Session()
t = Test(code2='test')
session.add(t)
assert t.code == t.code2
t.code2 = 'other value'
assert t.code == t.code2
session.query(Test).update({'code': 'New value'})
session.query(Test).update({'code2': 'New value'})

If i can get an instance with 'code2' parameter, it would be normal to update the hybrid_property too in the query.

File "./bin/python", line 64, in <module>
    exec(compile(__file__f.read(), __file__, "exec"))
  File "selection.py", line 36, in <module>
    session.query(Test).update({'code2': 'New value'})
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-0.9.7-py3.3-macosx-10.9-x86_64.egg/sqlalchemy/orm/query.py", line 2757, in update
    update_op.exec_()
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-0.9.7-py3.3-macosx-10.9-x86_64.egg/sqlalchemy/orm/persistence.py", line 897, in exec_
    self._do_exec()
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-0.9.7-py3.3-macosx-10.9-x86_64.egg/sqlalchemy/orm/persistence.py", line 995, in _do_exec
    update_stmt, params=self.query._params)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-0.9.7-py3.3-macosx-10.9-x86_64.egg/sqlalchemy/orm/session.py", line 991, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-0.9.7-py3.3-macosx-10.9-x86_64.egg/sqlalchemy/engine/base.py", line 729, in execute
    return meth(self, multiparams, params)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-0.9.7-py3.3-macosx-10.9-x86_64.egg/sqlalchemy/sql/elements.py", line 321, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-0.9.7-py3.3-macosx-10.9-x86_64.egg/sqlalchemy/engine/base.py", line 819, in _execute_clauseelement
    inline=len(distilled_params) > 1)
  File "<string>", line 1, in <lambda>
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-0.9.7-py3.3-macosx-10.9-x86_64.egg/sqlalchemy/sql/elements.py", line 492, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-0.9.7-py3.3-macosx-10.9-x86_64.egg/sqlalchemy/sql/elements.py", line 498, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-0.9.7-py3.3-macosx-10.9-x86_64.egg/sqlalchemy/sql/compiler.py", line 395, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-0.9.7-py3.3-macosx-10.9-x86_64.egg/sqlalchemy/sql/compiler.py", line 199, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-0.9.7-py3.3-macosx-10.9-x86_64.egg/sqlalchemy/sql/compiler.py", line 222, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-0.9.7-py3.3-macosx-10.9-x86_64.egg/sqlalchemy/sql/visitors.py", line 80, in _compiler_dispatch
    return meth(self, **kw)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-0.9.7-py3.3-macosx-10.9-x86_64.egg/sqlalchemy/sql/compiler.py", line 1806, in visit_update
    colparams = self._get_colparams(update_stmt, **kw)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-0.9.7-py3.3-macosx-10.9-x86_64.egg/sqlalchemy/sql/compiler.py", line 2217, in _get_colparams
    (", ".join("%s" % c for c in check))
sqlalchemy.exc.CompileError: Unconsumed column names: code2

[Sqlalchemy-tickets] Issue #3227: count() doesnt send the mapper to get_bind() (zzzeek/sqlalchemy)

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

New issue 3227: count() doesnt send the mapper to get_bind()
https://bitbucket.org/zzzeek/sqlalchemy/issue/3227/count-doesnt-send-the-mapper-to-get_bind

Mike Bayer:

no solution at this time as we pass literal_column('*') in, which can't be related to the mapper directly.  we can add parententity to it, but it still is not a FROM.   new state would need to be added to Query.

[Sqlalchemy-tickets] Issue #3226: Database diagram generator in Theatrum Chemicum does not work with sqla-0.9 (zzzeek/sqlalchemy)

[Priit L. <iss...@bi...>]

New issue 3226: Database diagram generator in Theatrum Chemicum does not work with sqla-0.9
https://bitbucket.org/zzzeek/sqlalchemy/issue/3226/database-diagram-generator-in-theatrum

Priit Laes:

Applied simple patch and tested with postgresql-9.4

[Sqlalchemy-tickets] Issue #3225: query heuristic extension (zzzeek/sqlalchemy)

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

New issue 3225: query heuristic extension
https://bitbucket.org/zzzeek/sqlalchemy/issue/3225/query-heuristic-extension

Mike Bayer:

given issues like #3223 and the openstack issues with model_query(), here's a sketch of an idea:

```
#!python




@event.listens_for(EventedQuery, "whereclause", retval=True)
def append_whereclause(existing_query, new_query, clause):
    if clause.selects_against(MyOtherClass):
        for entity, expression in new_query.selects_against(MyClass):
            new_query = new_query.join(MyOtherClass, MyClass.myother)
    return new_query

@event.listens_for(EventedQuery, "selects_from_entity", retval=True)
def append_order_by(existing_query, new_query, entity, expression):
    for entity, expression in new_query.selects_against(MyClass):
        new_query = new_query.order_by(entity.whatever)
    return new_query

@event.listens_for(EventedQuery, "before_compile", retval=True)
def append_order_by(new_query):
    if new_query.selects_against(MyClass):
        for entity, expression in new_query.selects_against(MyClass):
            new_query = new_query.order_by(entity.whatever)
    return query

```

[Sqlalchemy-tickets] Issue #3224: Filtering on a union of join silently fails to filter. (zzzeek/sqlalchemy)

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

New issue 3224: Filtering on a union of join silently fails to filter.
https://bitbucket.org/zzzeek/sqlalchemy/issue/3224/filtering-on-a-union-of-join-silently

Christophe Biocca:

I attached a reproduction of the bug. The important bit:


```
#!python

# This doesn't filter both side of the union, and the generated sql looks really wrong.
session.query(B).join(A).union(session.query(B).join(A)).filter(A.id == '2').count()
```

Simple workaround was to duplicate the filter call to each query before the union. To be honest, I'm not even sure the filter call is valid (but at least it should throw an error, not let everything go through).

[Sqlalchemy-tickets] Issue #3223: 'order_by' in __mapper_args__ breaks queries that use union(). (zzzeek/sqlalchemy)

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

New issue 3223: 'order_by' in __mapper_args__ breaks queries that use union().
https://bitbucket.org/zzzeek/sqlalchemy/issue/3223/order_by-in-__mapper_args__-breaks-queries

Christophe Biocca:

I attached a reproduction. On Postgres it manifests as a different error, but the causes are the same.

We're working around it by not using order_by in mapper args, and instead setting it manually on the relevant queries.

[Sqlalchemy-tickets] Issue #3222: join to single-inh subclass without using a property-based join fails to apply single inh criterion (zzzeek/sqlalchemy)

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

New issue 3222: join to single-inh subclass without using a property-based join fails to apply single inh criterion
https://bitbucket.org/zzzeek/sqlalchemy/issue/3222/join-to-single-inh-subclass-without-using

Mike Bayer:


```
#!python

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

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    type = Column(String)
    cid = Column(ForeignKey('c.id'))
    c = relationship("C", backref="a")
    __mapper_args__ = {'polymorphic_on': type}


class B(A):
    __mapper_args__ = {'polymorphic_identity': 'b'}


class C(Base):
    __tablename__ = 'c'
    id = Column(Integer, primary_key=True)

s = Session()

print s.query(C, B).filter(C.id == B.cid)

print s.query(C, B).join(B, C.id == B.cid)

print s.query(C, B).join(B, C.a)

```

first query is OK:


```
#!sql

SELECT c.id AS c_id, a.id AS a_id, a.type AS a_type, a.cid AS a_cid 
FROM c, a 
WHERE c.id = a.cid AND a.type IN (:type_1)

```

second query lacks the discriminator:


```
#!sql

SELECT c.id AS c_id, a.id AS a_id, a.type AS a_type, a.cid AS a_cid 
FROM c JOIN a ON c.id = a.cid

```

third one is OK:


```
#!sql

SELECT c.id AS c_id, a.id AS a_id, a.type AS a_type, a.cid AS a_cid 
FROM c JOIN a ON c.id = a.cid AND a.type IN (:type_1)

```

the issue is the check inside of _join_entities assumes the ON clause was rendered orm.join() which also adds the criteria in.   So this patch resolves:


```
#!diff

diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index 7b2ea79..0c3bc26 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -1979,7 +1979,7 @@ class Query(object):
             info.selectable, \
             getattr(info, 'is_aliased_class', False)
 
-        if right_mapper:
+        if right_mapper and prop is not None:
             self._join_entities += (info, )
 
         if right_mapper and prop and \

```

[Sqlalchemy-tickets] Issue #3221: More detailed "develop" guidelines (zzzeek/sqlalchemy)

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

New issue 3221: More detailed "develop" guidelines
https://bitbucket.org/zzzeek/sqlalchemy/issue/3221/more-detailed-develop-guidelines

jvanasco:

It would be nice to extend http://www.sqlalchemy.org/develop.html with a few things to further help contributions: 

* Preferred method of submissions:  
** bitbucket vs github
** develop on master/branch or create a release/issue branch.  if release/issue, is there a preferred naming convention?
** should the fix be based on the branch on master, a release, etc

* should a fix be backported?  are there any critera for this?  if so, are there any guidelines?

* Is there anything that the package maintainers would prefer in terms of workflow or style that is not covered by the above or the existing guidelines

[Sqlalchemy-tickets] Issue #3220: Several small issues with Oracle recursive queries (zzzeek/sqlalchemy)

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

New issue 3220: Several small issues with Oracle recursive queries
https://bitbucket.org/zzzeek/sqlalchemy/issue/3220/several-small-issues-with-oracle-recursive

Mariano Mara:

Hi Mike, I'm trying to use the same CTE I have working for PG with Oracle (since Oracle now supports CTE), however there are several small issues that prevents the use of SQLAlchemy's CTE with Oracle as transparently as it is possible with PG (I need to make it work with SQL Server too but so far I have not tested it). 

1- The first issue is regarding the **RECURSIVE** keyword. Here is a simple script that includes everything required to reproduce the problem.


```
#!python

from sqlalchemy.orm import aliased
from sqlalchemy import String, Integer, Column, func, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine('oracle://scott:tiger@localhost:1521/xe', echo=True)
Base = declarative_base()
class Part(Base):
    __tablename__ = 'part'
    part = Column(String(200), primary_key=True)
    sub_part = Column(String(200), primary_key=True)
    quantity = Column(Integer)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
included_parts = session.query(
                Part.sub_part,
                Part.part,
                Part.quantity).\
                    filter(Part.part=="our part").\
                    cte(name="included_parts", recursive=True)

incl_alias = aliased(included_parts, name="pr1")
parts_alias = aliased(Part, name="p")
included_parts = included_parts.union_all(
    session.query(
        parts_alias.sub_part,
        parts_alias.part,
        parts_alias.quantity).\
            filter(parts_alias.part==included_parts.c.sub_part)
    )

q = session.query(
        included_parts.c.sub_part,
        func.sum(included_parts.c.quantity).
            label('total_quantity')
    ).\
    group_by(included_parts.c.sub_part)
session.execute(q)
```
if you try to execute this version, you will get the following error (because Oracle does not like the word RECURSIVE):

```
#!python

sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00905: missing keyword
 'WITH RECURSIVE included_parts(sub_part, part, quantity) AS \n(SELECT part.sub_part AS sub_part, part.part AS part, part.quantity AS quantity \nFROM part \nWHERE part.part = :part_1 UNION ALL SELECT p.sub_part AS p_sub_part, p.part AS p_part, p.quantity AS p_quantity \nFROM part p, included_parts \nWHERE p.part = included_parts.sub_part)\n SELECT included_parts.sub_part AS included_parts_sub_part, sum(included_parts.quantity) AS total_quantity \nFROM included_parts GROUP BY included_parts.sub_part' {'part_1': 'our part'}
```

so, of course, we change the CTE (even if we are doing a recursive query) from


```
#!python

included_parts = session.query(
                Part.sub_part,
                Part.part,
                Part.quantity).\
                    filter(Part.part=="our part").\
                    cte(name="included_parts", recursive=True)
```

to


```
#!python

included_parts = session.query(
                Part.sub_part,
                Part.part,
                Part.quantity).\
                    filter(Part.part=="our part").\
                    cte(name="included_parts", recursive=False)
```

but now we get


```
#!python

sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-32039: recursive WITH clause must have column alias list
 'WITH included_parts AS \n(SELECT part.sub_part AS sub_part, part.part AS part, part.quantity AS quantity \nFROM part \nWHERE part.part = :part_1 UNION ALL SELECT p.sub_part AS p_sub_part, p.part AS p_part, p.quantity AS p_quantity \nFROM part p, included_parts \nWHERE p.part = included_parts.sub_part)\n SELECT included_parts.sub_part AS included_parts_sub_part, sum(included_parts.quantity) AS total_quantity \nFROM included_parts GROUP BY included_parts.sub_part' {'part_1': 'our part'}
```
because we are now missing the list of columns required.

2- the second issue deals with some special keywords oracle has in order to provide ordering and to prevent cycle issues within a recursive query. The complete spec is available [here](http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#BCEDDGGE) and I found these [two](http://rwijk.blogspot.jp/2009/11/recursive-subquery-factoring.html) [examples](http://rajeshwaranbtech.blogspot.com.ar/2012/12/recursive-with-clause-to-implement.html) floating around. The **cycle** option is specially useful since it can prevent circular errors in the recursive relation.  In PG you can do some magic with *arrays*, *rows* and *any* but in Oracle it is really hard to prevent circular issues unless you use the suggested features.

To tell you the thruth it looks like a pretty troublesome extension to add to sqlalchemy but I guess it could be great if at least we can input some text() condition with this .

Thanks for your patience reading this and I am available for anything you need me to test in case you think this issue is worth your time.

Mariano

[Sqlalchemy-tickets] Issue #3219: Intermediate __abstract__ inheriting from model doesn't transfer model's attrs (zzzeek/sqlalchemy)

[David L. <iss...@bi...>]

New issue 3219: Intermediate __abstract__ inheriting from model doesn't transfer model's attrs
https://bitbucket.org/zzzeek/sqlalchemy/issue/3219/intermediate-__abstract__-inheriting-from

David Lord:

I have the following models:

* `Device`
* `DeviceSource` has a foreign key and relationship to `Device`
* `LDAPDeviceSource` is a joined-table inheritance of `DeviceSource`

In order to make defining new `DeviceSource` subclasses easier, I created `DeviceSourceMixin`, which inherits from `DeviceSource` and provides declared attrs for `__tablename__` and the foreign primary `id`.  `DeviceSourceMixin` is `__abstract__` so that it doesn't create a table of its own.

The issue is that this intermediate `__abstract__` seems to break the declarative model.  The foreign key and relationship in `DeviceSource` do not get inherited by `LDAPDeviceSource` when subclassing `DeviceSourceMixin`.

This code demonstrates the issue:

```
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declared_attr, as_declarative
from sqlalchemy.orm import Session, relationship

engine = sa.create_engine('sqlite:///:memory:', echo=True)
session = Session(bind=engine)


@as_declarative(bind=engine)
class Base(object):
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()

    id = sa.Column(sa.Integer, primary_key=True)


class Device(Base):
    pass


class DeviceSource(Base):
    type = sa.Column(sa.String, nullable=False)
    device_id = sa.Column(sa.Integer, sa.ForeignKey(Device.id), nullable=False)

    device = relationship(Device, backref='sources')

    __mapper_args__ = {
        'polymorphic_on': type
    }


class DeviceSourceMixin(DeviceSource):
    __abstract__ = True

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

    @declared_attr
    def id(cls):
        return sa.Column(sa.Integer, sa.ForeignKey(DeviceSource.id), primary_key=True)


class LDAPDeviceSource(DeviceSourceMixin):
    name = sa.Column(sa.String, nullable=False)

    __mapper_args__ = {
        'polymorphic_identity': 'ldap'
    }


Base.metadata.create_all()
d1 = Device()
s1 = LDAPDeviceSource(device=d1, name='s1')
session.add(s1)
session.commit()
```

It produces the following error:

```
Traceback (most recent call last):
  File "/home/david/Projects/cedar/example2.py", line 62, in <module>
    s1 = LDAPDeviceSource(device=d1, name='s1')
  File "<string>", line 4, in __init__
  File "/home/david/.virtualenvs/cedar/lib/python3.4/site-packages/sqlalchemy/orm/state.py", line 260, in _initialize_instance
    return manager.original_init(*mixed[1:], **kwargs)
  File "<string>", line 6, in __init__
  File "/home/david/.virtualenvs/cedar/lib/python3.4/site-packages/sqlalchemy/ext/declarative/base.py", line 526, in _declarative_constructor
    setattr(self, k, kwargs[k])
  File "/home/david/.virtualenvs/cedar/lib/python3.4/site-packages/sqlalchemy/orm/attributes.py", line 226, in __set__
    instance_dict(instance), value, None)
  File "/home/david/.virtualenvs/cedar/lib/python3.4/site-packages/sqlalchemy/orm/attributes.py", line 812, in set
    value = self.fire_replace_event(state, dict_, value, old, initiator)
  File "/home/david/.virtualenvs/cedar/lib/python3.4/site-packages/sqlalchemy/orm/attributes.py", line 832, in fire_replace_event
    state, value, previous, initiator or self._replace_token)
  File "/home/david/.virtualenvs/cedar/lib/python3.4/site-packages/sqlalchemy/orm/attributes.py", line 1148, in emit_backref_from_scalar_set_event
    passive=PASSIVE_NO_FETCH)
  File "/home/david/.virtualenvs/cedar/lib/python3.4/site-packages/sqlalchemy/orm/attributes.py", line 980, in append
    collection.append_with_event(value, initiator)
  File "/home/david/.virtualenvs/cedar/lib/python3.4/site-packages/sqlalchemy/orm/collections.py", line 653, in append_with_event
    self._data()._sa_appender(item, _sa_initiator=initiator)
  File "/home/david/.virtualenvs/cedar/lib/python3.4/site-packages/sqlalchemy/orm/collections.py", line 1047, in append
    item = __set(self, item, _sa_initiator)
  File "/home/david/.virtualenvs/cedar/lib/python3.4/site-packages/sqlalchemy/orm/collections.py", line 1019, in __set
    item = executor.fire_append_event(item, _sa_initiator)
  File "/home/david/.virtualenvs/cedar/lib/python3.4/site-packages/sqlalchemy/orm/collections.py", line 716, in fire_append_event
    item, initiator)
  File "/home/david/.virtualenvs/cedar/lib/python3.4/site-packages/sqlalchemy/orm/attributes.py", line 929, in fire_append_event
    value = fn(state, value, initiator or self._append_token)
  File "/home/david/.virtualenvs/cedar/lib/python3.4/site-packages/sqlalchemy/orm/attributes.py", line 1157, in emit_backref_from_collection_append_event
    child_impl = child_state.manager[key].impl
KeyError: 'device'
```

[Sqlalchemy-tickets] Issue #3218: __module__ should be set on functions returned by public_factory? (zzzeek/sqlalchemy)

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

New issue 3218: __module__ should be set on functions returned by public_factory?
https://bitbucket.org/zzzeek/sqlalchemy/issue/3218/__module__-should-be-set-on-functions

Steven Winfield:

This would be more in line with what functools.wraps() does, and code introspection tools could then determine where the returned function is supposed to live.

For example, if I do this:

from sqlalchemy.orm import relationship 

...then sphinx (in particular) will generate documentation for relationship as if it were defined in the module containing the import.

I think the change is a one-liner - sqlalchemy/util/langhelpers.py before the return statement of public_factory (line 154):

decorated.__module__ = "sqlalchemy" + location.rsplit(".", 1)[0]

[Sqlalchemy-tickets] Issue #3217: make `.join()` more standard, or improve error (zzzeek/sqlalchemy)

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

New issue 3217: make `.join()` more standard, or improve error
https://bitbucket.org/zzzeek/sqlalchemy/issue/3217/make-join-more-standard-or-improve-error

jvanasco:

This kept tripping me up, largely from how searching for `join` data online (or on the docs) eventually directs queries.

it seems that every version of `.join()` within sqlalchemy accepts an `isouter=BOOLEAN` argument -- except for `sqlalchemy.orm.query.Query.join` 

    sqlalchemy.orm.query.Query.join(*props, **kwargs)
    sqlalchemy.sql.expression.join(left, right, onclause=None, isouter=False)
    sqlalchemy.sql.expression.CLASS.join(right, onclause=None, isouter=False)
    sqlalchemy.schema.Table.join(right, onclause=None, isouter=False)
    sqlalchemy.orm.join(left, right, onclause=None, isouter=False, join_to_left=None)

In practice, they all behave similarly and will accept this form:

    FROM_CLAUSE.join( Class, ClassLeft.on_column == ClassRight.OnColumn )

Everything pretty much works the same... except the `orm.query.Query.join` doesn't work if you pass in `isouter=True`.  You need to call the `outerjoin()` method instead.

Things only get confusing when searching online or in the docs, and you end up looking at the docs for the wrong class.  Passing isouter to a Query.join just raises a generic TypeError that makes it look like you're joining on the wrong columns:

	Traceback (most recent call last):
	  File "/Users/jvanasco/Desktop/test.py", line 24, in <module>
		q1.join( A.id, A.id == A.id, isouter=False)
	  File "/Users/jvanasco/webserver/environments/test-2.7.5/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-macosx-10.6-intel.egg/sqlalchemy/orm/query.py", line 1701, in join
		','.join(kwargs.keys))
	TypeError

My suggestion is for one of two behaviors:

1. `orm.query.Query.join` accepts `isouter` and just proxies the request to `outerjoin()`
2. if `isouter` is supplied to `orm.query.Query.join`, an informative error is raised that just instructs developers that they're using the wrong class and `outerjoin` is what they want.

[Sqlalchemy-tickets] Issue #3216: Don't create serial column when there's more than one PK column (zzzeek/sqlalchemy)

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

New issue 3216: Don't create serial column when there's more than one PK column
https://bitbucket.org/zzzeek/sqlalchemy/issue/3216/dont-create-serial-column-when-theres-more

thiefmaster:

E.g. in this example:

```
class Foo(Base):                                   
    __tablename__ = 'foo'
    id1 = Column(Integer, primary_key=True, nullable=False)
    id2 = Column(Integer, primary_key=True, nullable=False)
```

I believe it does not make much sense to create `id1` as autoincrement/serial while `id2` is a plain integer columns.

Unless there are database engines which actually support multiple serial columns, I would either disable autoincrement for both columns in that case or raise an error, forcing the user to enable/disable autoincrement explictly.

[Sqlalchemy-tickets] Issue #3215: branched connection does not propagate invalidation to parent (zzzeek/sqlalchemy)

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

New issue 3215: branched connection does not propagate invalidation to parent
https://bitbucket.org/zzzeek/sqlalchemy/issue/3215/branched-connection-does-not-propagate

Mike Bayer:


```
#!python

from sqlalchemy import create_engine, exc

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

def test_one():
    c1 = e.connect()

    dbapi_conn = c1.connection.connection
    dbapi_conn.close()

    try:
        c1.execute("select 'hi'")
    except exc.DBAPIError:
        pass

    assert c1.invalidated
    c1.execute("select 'hi'")
    assert not c1.invalidated
    c1.close()

def test_two():
    c2 = e.connect()

    c2_branch = c2.connect()

    dbapi_conn = c2.connection.connection
    dbapi_conn.close()

    try:
        c2_branch.execute("select 'hi'")
    except exc.DBAPIError:
        pass

    assert c2_branch.invalidated

    # fails
    assert c2.invalidated

    # sqlalchemy.exc.StatementError: 'NoneType' object
    # has no attribute 'cursor' (original cause: AttributeError:
    # 'NoneType' object has no attribute 'cursor') u"select 'hi'" []
    c2.execute("select 'hi'")

    assert not c2_branch.invalidated
    c2.close()



test_one()
test_two()
```

[Sqlalchemy-tickets] Issue #3214: Multi-level polymorphic inheritance broken instances. (zzzeek/sqlalchemy)

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

New issue 3214: Multi-level polymorphic inheritance broken instances.
https://bitbucket.org/zzzeek/sqlalchemy/issue/3214/multi-level-polymorphic-inheritance-broken

Justin Solms:

# Problem

I get unexpected inheritance instances from the multi-level polymorphic example below. I have attached self-contained test (see attached files) for anyone to re-create the problem.

```
#!python

class Entity(Base):
    __tablename__ = 'entity'
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    entity_type = Column('type',String)

    __mapper_args__ = {
        'polymorphic_identity':'entity',
        'polymorphic_on':entity_type,
        }
    
class Asset(Entity):    
    __tablename__ = 'asset'
    
    id = Column('entity_id',Integer, ForeignKey('entity.id'), primary_key=True)
    asset_type = Column('asset_type',String)
    
    __mapper_args__ = {
        'polymorphic_identity':'asset',
        'polymorphic_on':asset_type,
        }

class Listed(Asset):
    __tablename__ = 'listed'

    id = Column('asset_entity_id',Integer, ForeignKey('asset.entity_id'), primary_key=True)
    ticker = Column(String)
    asset_class = Column(String)
 
    __mapper_args__ = {
        'polymorphic_identity':'listed',
        'polymorphic_on':asset_class,
        }

class Equity(Listed):

    __mapper_args__ = {
            'polymorphic_identity':'equity',
            }

```

# Test code snippets

There are 5 instances of the same class in each test. 

The unexpected behavior:
* Note test 2 and test 3 the last output line. The class appears different from the previous 4 lines.
* The 1st test I believe should deliver the type `test.Equity`. It does not. Instead it is `test.Asset`.

## Test 1

* I believe should deliver the type `test.Equity`. It does not. Instead it is `test.Asset`.

```
#!python
print 'Test 1'
entities = session.query(Entity)
for ent in entities:
    print '<Type:%s, entity_type:%s, asset_type:%s>' % (type(ent), ent.entity_type, ent.asset_type)
```

Output:

```
Test 1
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
```

## Test 2

* I believe should deliver the type `test.Equity`. It does not. Instead it is `test.Listed`.
* Note the last output line. The class appears different from the previous 4 lines.

```
#!python
print 'Test 2'
entities = session.query(Asset)
for ent in entities:
    print '<Type:%s, entity_type:%s, asset_type:%s>' % (type(ent), ent.entity_type, ent.asset_type)
```

Output:

```
<Type:<class 'test.Listed'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Listed'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Listed'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Listed'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
```

## Test 3

* I believe should deliver the type `test.Equity`. It does except for the last output line which is `test.Asset`. The class appears different from the previous 4 lines.

```
#!python
print 'Test 3'
entities = session.query(Listed)
for ent in entities:
    print '<Type:%s, entity_type:%s, asset_type:%s>' % (type(ent), ent.entity_type, ent.asset_type)

```

Output

```
<Type:<class 'test.Equity'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Equity'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Equity'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Equity'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
```


# The self-contained test case

This is a very minimal case of the actual schema I am trying to map to Python.

Please re-construct the test case in a MySQL or other database. I have attached files that re-construct the test case for you.

The test files are:

1. `test.py` - The test code.
2. `test_db_create.sql` - Creates the simple test schema
3. `test_result.txt` - The odd output of the test.
4. `table_listing.txt` - A quick glance at the test schema tables.

Feel free to contact me. This issue is critical to my project and I cannot re-design the dependent table schema.

Kind regards,
Justin

[Sqlalchemy-tickets] Issue #3213: positional_names doesn't get passed to whereclause in visit_select (zzzeek/sqlalchemy)

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

New issue 3213: positional_names doesn't get passed to whereclause in visit_select
https://bitbucket.org/zzzeek/sqlalchemy/issue/3213/positional_names-doesnt-get-passed-to

Tony Roberts:

Hi,

when compiling a query using a dialect that expects positional arguments, when there are CTEs in the expression the positional arguments can end up in the wrong order.

This is because when visit_select (in sql/compiler.py) is called via visit_cte there are various points where the positional_names don't get passed when building the cte clauses, such as the where clause:

```
#!python
    def visit_select(self, select, asfrom=False, parens=True,
                            iswrapper=False, fromhints=None,
                            compound_index=0,
                            positional_names=None,
                            force_result_map=False,
                            **kwargs):
        ....
        ....

        if select._whereclause is not None:
            t = select._whereclause._compiler_dispatch(self, **kwargs)
            if t:
                text += " \nWHERE " + t
```

This could be fixed by not listing positional_names as an explicit kwarg to visit_select and instead picking it out of the kwargs. That way it will be passed everywhere kwargs is passed without having to check every case, ie:

```
#!python

    def visit_select(self, select, asfrom=False, parens=True,
                            iswrapper=False, fromhints=None,
                            compound_index=0,
                            force_result_map=False,
                            **kwargs):
        positional_names = kwargs.get("positional_names")
```

To reproduce this problem try a query like this slightly contrived example on a database that uses positional parameters, eg sqlite (although you need a recent version that supports CTEs, I've been testing with 3.8.6).

```
#!sql
WITH cte_0 AS (
 SELECT coalesce(table_a.x, '?') AS x, table_a.y as y
 FROM x WHERE x.z == '?'
),
cte_1 AS (
  SELECT coalesce(table_a.x, '?') AS x, table_a.y as y
  FROM x WHERE x.z == '?'
), 
SELECT cte_0.x, cte_1.x
FROM cte_0
JOIN cte_1 ON cte_0.y = cte_1.y
WHERE cte_0.x IN (?) AND cte_1.x IN (?)
```

You'll find that the params for the cte where clauses get added to the main positiontup list instead of the cte_positional list and so the final parameter order is wrong.

thanks!
Tony

[Sqlalchemy-tickets] Issue #3212: Bad SQL syntax for query.exists in mssql (zzzeek/sqlalchemy)

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

New issue 3212: Bad SQL syntax for query.exists in mssql
https://bitbucket.org/zzzeek/sqlalchemy/issue/3212/bad-sql-syntax-for-queryexists-in-mssql

Leo Hemsted:

Using:

* Python 2.6.6
* sqlalchemy 0.9.7
* pyodbc 3.0.6
* Microsoft SQL Server 2008 (version 10.50.4000.0)


query.exists produces `SELECT EXISTS (...) AS anon_1`, that's bad syntax for SQL Server. SQL Server only accepts it as part of a where clause, ie the equivalent query would be `SELECT 1 WHERE EXISTS (...)`


```
#!python

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

engine = sqlalchemy.create_engine('mssql+pyodbc://*****:*****@*****/*****')
session = scoped_session(sessionmaker(bind=engine))

Base = declarative_base()

class User(Base):
    __tablename__ = 'User'
    id = Column('Id', Integer, primary_key=True)

session.query(session.query(User).exists()).scalar()
```


```

Traceback (most recent call last):
  File "test.py", line 15, in <module>
    session.query(session.query(User).exists()).scalar()
  File "c:\Python26\lib\site-packages\sqlalchemy\orm\query.py", line 2400, in scalar
    ret = self.one()
  File "c:\Python26\lib\site-packages\sqlalchemy\orm\query.py", line 2369, in one
    ret = list(self)
  File "c:\Python26\lib\site-packages\sqlalchemy\orm\query.py", line 2412, in __iter__
    return self._execute_and_instances(context)
  File "c:\Python26\lib\site-packages\sqlalchemy\orm\query.py", line 2427, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 729, in execute
    return meth(self, multiparams, params)
  File "c:\Python26\lib\site-packages\sqlalchemy\sql\elements.py", line 321, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 826, in _execute_clauseelement
    compiled_sql, distilled_params
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 958, in _execute_context
    context)
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 1160, in _handle_dbapi_exception
    exc_info
  File "c:\Python26\lib\site-packages\sqlalchemy\util\compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 951, in _execute_context
    context)
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\default.py", line 436, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'EXISTS'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AS'. (156)") u'SELECT EXISTS (SELECT 1 \nFROM [User]) AS anon_1' ()
```

[Sqlalchemy-tickets] Issue #3211: SQLiteExecutionContext._translate_colname bug when using attached databases (zzzeek/sqlalchemy)

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

New issue 3211: SQLiteExecutionContext._translate_colname bug when using attached databases
https://bitbucket.org/zzzeek/sqlalchemy/issue/3211/sqliteexecutioncontext_translate_colname

Tony Roberts:

Hi,

if using a sqlite database with attached databases, eg

```
#!python
    def on_connect(dbapi_connection, connection_record):
        dbapi_connection.execute("ATTACH DATABASE 'test.db' AS 'test'")

    engine = create_engine("sqlite+pysqlite:///:memory:")
    event.listen(engine, "connect", on_connect)
```

when selecting columns from tables from attached databases the column format is "database.tablename.colname".

In SQLiteExecutionContext._translate_colname it is assumed the format is always "tablename.colname" and so selects always result in the wrong column names in the ResultMetaData.

A possible fix is to change SQLiteExecutionContext._translate_colname to take the last part of the column name split on "." instead of the second:

```
#!python

    def _translate_colname(self, colname):
        # adjust for dotted column names.  SQLite
        # in the case of UNION may store col names as
        # "tablename.colname" or "database.tablename.colname"
        # in cursor.description
        if not self._preserve_raw_colnames  and "." in colname:
            return colname.split(".")[-1], colname
        else:
            return colname, None
```

thanks,
Tony

[Sqlalchemy-tickets] Issue #3210: automap_base don't use cascades properly (zzzeek/sqlalchemy)

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

New issue 3210: automap_base don't use cascades properly
https://bitbucket.org/zzzeek/sqlalchemy/issue/3210/automap_base-dont-use-cascades-properly

yoch:

I'm use automap_base() to reflect existing MySQL database into the ORM.

There are some Foreign Key constraints which intended to cascade deletions, but the child rows are not deleted. 

If we add an NOT NULL constraint on these rows, I got Integrity violation error, because SQLAlchemy try to set IDs to NULL instead of delete them : "UPDATE gestion_horaire SET idscenario=%s WHERE gestion_horaire.id = %s' (None, 41L)"

[Sqlalchemy-tickets] Issue #3209: Can't set to None relationship property of object being in collection of collections (zzzeek/sqlalchemy)

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

New issue 3209: Can't set to None relationship property of object being in collection of collections
https://bitbucket.org/zzzeek/sqlalchemy/issue/3209/cant-set-to-none-relationship-property-of

ods:

Test case:


```
#!python

from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
 
 
Base = declarative_base()
 
 
class DocLink(Base):
    __tablename__ = 'DocLink'
    id = Column(Integer, primary_key=True)
    block_id = Column(ForeignKey('DocLinkBlock.id', ondelete='CASCADE'),
                      nullable=False)
    block = relationship('DocLinkBlock',
                         primaryjoin='DocLink.block_id==DocLinkBlock.id')
    ref_url = Column(String(250), nullable=True)
    ref_doc_id = Column(ForeignKey('Doc.id'))
    ref_doc = relationship('Doc', primaryjoin='DocLink.ref_doc_id==Doc.id')
 
 
class DocLinkBlock(Base):
    __tablename__ = 'DocLinkBlock'
    id = Column(Integer, primary_key=True)
    doc_id = Column(ForeignKey('Doc.id', ondelete='CASCADE'), nullable=False)
    doc = relationship('Doc')
    links = relationship(DocLink, cascade='all, delete-orphan')
 
 
class Doc(Base):
    __tablename__ = 'Doc'
    id = Column(Integer, primary_key=True)
    link_blocks = relationship(DocLinkBlock, cascade='all, delete-orphan')
 
 
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)()
 
ref_doc = Doc(id=1)
session.add(ref_doc)
session.commit()
 
doc = Doc(
    id=2,
    link_blocks=[
        DocLinkBlock(
            id=1,
            links=[
                DocLink(
                    id=1,
                    ref_url='url',
                    ref_doc=ref_doc,
                ),
            ],
        ),
    ],
)
session.add(doc)
session.commit()
assert doc.link_blocks[0].links[0].ref_url=='url'
assert doc.link_blocks[0].links[0].ref_doc is ref_doc
 
doc.link_blocks = [
    DocLinkBlock(
        id=1,
        links=[
            DocLink(
                id=1,
                ref_url=None,
                ref_doc=None,
            ),
        ],
    ),
]
session.commit()
assert doc.link_blocks[0].links[0].ref_url is None
assert doc.link_blocks[0].links[0].ref_doc is None # Fails
```

There is no such problem when `DocLinkBlock.links` is updated directly (no nested collections).

[Sqlalchemy-tickets] Issue #3208: AttributeError in sqlalchemy.ext.declarative.clsregistry._MultipleClassMarker.add_item (zzzeek/sqlalchemy)

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

New issue 3208: AttributeError in sqlalchemy.ext.declarative.clsregistry._MultipleClassMarker.add_item
https://bitbucket.org/zzzeek/sqlalchemy/issue/3208/attributeerror-in

Scott Milliken:

We've been getting occasional AttributeError exceptions raised from the `sqlalchemy.ext.declarative.clsregistry._MultipleClassMarker.add_item` method as it tries to access a `__module__` attribute here: https://github.com/zzzeek/sqlalchemy/blob/28dd15081db4b7e978fa7a187c3aef1c0e4ad4e3/lib/sqlalchemy/ext/declarative/clsregistry.py#L106.

I haven't been able to identify the root cause or reliably reproduce the bug, but we are doing something fancy that's almost certainly related.

Using SQLAlchemy 0.9.6, Python 2.7.2, running inside Apache2.

Stacktrace:

```
 Module ourmodule1:37 in __init__
<<
>>  partition = aliased(get_partition_model(session, PartitionedTable))
Module ourmodule2:203 in get_partition_model
Module ourmodule2:190 in cons_model
Module sqlalchemy.ext.declarative.api:53 in __init__
<<      def __init__(cls, classname, bases, dict_):
               if '_decl_class_registry' not in cls.__dict__:
                   _as_declarative(cls, classname, cls.__dict__)
               type.__init__(cls, classname, bases, dict_)
>>  _as_declarative(cls, classname, cls.__dict__)
Module sqlalchemy.ext.declarative.base:148 in _as_declarative
<<          table_args = None
      
           clsregistry.add_class(classname, cls)
           our_stuff = util.OrderedDict()
>>  clsregistry.add_class(classname, cls)
Module sqlalchemy.ext.declarative.clsregistry:64 in add_class
<<          for token in tokens:
                   module = module.get_module(token)
               module.add_class(classname, cls)
>>  module.add_class(classname, cls)
Module sqlalchemy.ext.declarative.clsregistry:160 in add_class
<<          if name in self.contents:
                   existing = self.contents[name]
                   existing.add_item(cls)
               else:
                   existing = self.contents[name] = \
>>  existing.add_item(cls)
Module sqlalchemy.ext.declarative.clsregistry:105 in add_item
<<      def add_item(self, item):
               modules = set([cls().__module__ for cls in self.contents])
               if item.__module__ in modules:
                   util.warn(
>>  modules = set([cls().__module__ for cls in self.contents])
AttributeError: 'NoneType' object has no attribute '__module__'
```

The fanciness I mentioned is the `get_partition_model` function referenced in the stacktrace. The purpose of this function is to take the SQLAlchemy model for a PostgreSQL paritioned table (using table inheritance), and return a new SQLAlchemy model for the named partition. We cannot use SQLAlchemy's inheritance facilities because there's no discriminator column in the table partitions we can use. Here's the implementation of this function:


```
#!python

def get_partition_model(session, model, partition_id=None):
    '''
    Get a model specialized for the child partition identified by @partition_id.

    Assumes partitions are named like '%(parent_table)s_%(partition_id)s'.

    If @partition_id is None, then the partition with the latest lexical sort will be returned.
    '''
    def cons_model(partition_name):
        classname = str('%s%s' % (model.__name__, partition_name.split('_')[-1]))
        if classname in model._decl_class_registry:
            return model._decl_class_registry[classname]
        newtable = copy.copy(model.__table__)
        newtable.name = partition_name
        for col in newtable.columns:
            col.table = newtable
        return type(classname, (model,), {
            '__table__': newtable,
            '__tablename__': partition_name,
            '__mapper_args__': {'concrete': True}
        })
    tablename = model.__table__.name
    # NB: get_child_tables returns a list of partition tables using pg_class and pg_inherits
    partitions = get_child_tables(session, tablename)
    if not partitions:
        return
    if partition_id:
        partition_name = '%s_%s' % (tablename, partition_id)
        if partition_name not in partitions:
            raise ValueError('No such partition %r for %r' % (partition_id, tablename))
    else:
        partition_name = max(partitions)
    return cons_model(partition_name)
```

This code works approximately 100% of the time, but on rare occasions triggers the AttributeError mentioned. If I were to guess, I'd assume there's a race condition/thread safety issue (not neccessarily in SQLAlchemy).

Admittedly, this is a poor bug report since we're doing something fancy with dynamically created models, and I haven't given you any way to reproduce this. I thought I'd report it anyway in case it's obvious to you what's going on or if it invalidates some assumption in the code.

[Sqlalchemy-tickets] Issue #3207: SQLAlchemy, postgres and schema (zzzeek/sqlalchemy)

[charles p. <iss...@bi...>]

New issue 3207: SQLAlchemy, postgres and schema
https://bitbucket.org/zzzeek/sqlalchemy/issue/3207/sqlalchemy-postgres-and-schema

charles pierre:

Hi, 
I am trying to work to setup a multi-schema environnement with sqlalchemy and postgres but i am struggling for two reasons :
-It seems that you cannot change dynamically the schema (i did metadata.schema = 'new_schema' and looped over all tables to also do table.schema = 'new_schema' but it feels like a hack). Does the library have a function to do this properly ?
-Some object does not inherit the schema option from the table or metadata. An example would be the postgresql enum type. How do you control in which schema the type gets created ? 

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3206: support statement level hints (zzzeek/sqlalchemy)

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

New issue 3206: support statement level hints
https://bitbucket.org/zzzeek/sqlalchemy/issue/3206/support-statement-level-hints

Mike Bayer:

    stmt = select([...]).with_statement_hint("some statement hint", "mysql")

this will send None into with_hint(), which will collect statement-level hints separately and render them by default at the end of the SELECT.