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

[Sqlalchemy-tickets] Issue #3055: Weird interaction between `subqueryload` and deeply-nested relationships causing exception sqlalchemy.exc.InvalidRequestError: Entity '<AliasedClass at 0x2b74a90; Employee>' has no property 'department' (zzzeek/sqlalchemy)

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

New issue 3055: Weird interaction between `subqueryload` and deeply-nested relationships causing exception sqlalchemy.exc.InvalidRequestError: Entity '<AliasedClass at 0x2b74a90; Employee>' has no property 'department'
https://bitbucket.org/zzzeek/sqlalchemy/issue/3055/weird-interaction-between-subqueryload-and

Jack Zhou:

The configuration:

```
#!python

class Company(Base):
    __tablename__ = "company"
    id = Column(Integer, primary_key=True)


class Department(Base):
    __tablename__ = "department"
    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey(Company.id), nullable=False)
    company = relationship(Company, lazy="subquery")


class Employee(Base):
    __tablename__ = "employee"
    id = Column(Integer, primary_key=True)
    type = Column(Enum("engineer", name="employee_type"), nullable=False)
    __mapper_args__ = {
        "polymorphic_on": type,
        "with_polymorphic": "*"
    }


class Engineer(Employee):
    __tablename__ = "engineer"
    id = Column(Integer, ForeignKey(Employee.id), primary_key=True)
    department_id = Column(Integer, ForeignKey(Department.id), nullable=False)
    department = relationship(Department, lazy="subquery")
    __mapper_args__ = {
        "polymorphic_identity": "engineer"
    }


class Car(Base):
    __tablename__ = "car"
    id = Column(Integer, primary_key=True)
    employee_id = Column(Integer, ForeignKey(Employee.id), nullable=False)
    employee = relationship(Employee, lazy="subquery")
```

The query:


```
#!python

session.add(Car(employee=Engineer(department=Department(company=Company()))))
session.query(Car).all()
```

This results in the following exception:


```
#!

Traceback (most recent call last):
  File "./bug.py", line 71, in <module>
    main()
  File "./bug.py", line 66, in main
    db.query(Car).all()
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2293, in all
    return list(self)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 72, in instances
    rows = [process[0](row, None) for row in fetch]
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 452, in _instance
    populate_state(state, dict_, row, isnew, only_load_props)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 305, in populate_state
    populator(state, dict_, row)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 1001, in load_scalar_from_subq
    (None,)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 937, in get
    self._load()
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 945, in _load
    lambda x: x[1:]
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 942, in <genexpr>
    (k, [vv[0] for vv in v])
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 75, in instances
    labels) for row in fetch]
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 347, in _instance
    return _instance(row, result)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 452, in _instance
    populate_state(state, dict_, row, isnew, only_load_props)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 305, in populate_state
    populator(state, dict_, row)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 1001, in load_scalar_from_subq
    (None,)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 937, in get
    self._load()
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 945, in _load
    lambda x: x[1:]
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2401, in __iter__
    context = self._compile_context()
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2764, in _compile_context
    entity.setup_context(self, context)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3145, in setup_context
    column_collection=context.primary_columns
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py", line 463, in setup
    strat.setup_query(context, entity, path, loader, adapter, **kwargs)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 735, in setup_query
    parent_alias, effective_entity)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 891, in _apply_joins
    q = q.join(parent_alias, attr, from_joinpoint=True)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 1699, in join
    from_joinpoint=from_joinpoint)
  File "<string>", line 2, in _join
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/base.py", line 165, in generate
    fn(self, *args[1:], **kw)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 1772, in _join
    descriptor = _entity_descriptor(left_entity, onclause)
  File "/home/dev/.venv/lib/python2.7/site-packages/sqlalchemy/orm/base.py", line 342, in _entity_descriptor
    (description, key)
sqlalchemy.exc.InvalidRequestError: Entity '<AliasedClass at 0x2b74a90; Employee>' has no property 'department'
```

One of the following things fixes the problem:

1. Removing the Department-Company relationship
2. Change any of the relationships to be `lazyload`
3. Remove `"with_polymorphic": "*"` from `Employee`

[Sqlalchemy-tickets] Issue #3054: new idea for bake (zzzeek/sqlalchemy)

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

New issue 3054: new idea for bake
https://bitbucket.org/zzzeek/sqlalchemy/issue/3054/new-idea-for-bake

Mike Bayer:

The issue with "bake" has always been the ability to derive an appropriate cache key for a query.   Based on the observation of other SQL products that a Python code object presents a direct way of establishing a unique line number in a unique source file, I'd like to see if we can make use of lambdas in order to establish this as a key.   Example:

```
#!python


def do_something(session):
    x1 = 5
    x2 = 10

    q = session.query(Foo).filter(Foo.x == bindparam('x1')).filter(Foo.y == bindparam('x2'))

    baked = Bake(lambda: q)

    return baked.params(x1=x1, x2=x2).all()

```

the above:

1. receives the lambda within Bake.  We use the code object present here to retrieve the source file and line number.

2. we establish some kind of cache, perhaps within the namespace of the source module itself, that will store everything we need about this query keyed to the line number.

3. Assuming the line number is not in the cache:

3a. we call the lambda and get the Query back.  We scan this query for bound parameters and establish those as the arguments we'd use in params().  Note that this includes bound parameters that were created implicitly (see below).

3b. we do the various "bake" things that we do in the [baked query recipe](https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/BakedQuery) with this new Query object.  We store what we need in the cache.

3c. we invoke the query with the params and return results.

4. if the line number *is* in the cache:

4a. we get the Query that we've "baked" from this lambda.

4b. we apply the current session, invoke the query with the params and return results.

Note that we can pull out the bound parameters without calling bindparam() as well.  *And*, if the whole Query is placed into the lambda, then we *dont even need to build the query* in order to get the cached version:


```
#!python

def do_something(session):
    x1 = 5
    x2 = 10

    baked = Bake(lambda: \
                       session.query(Foo).filter(Foo.x == 5).filter(Foo.y == 10)
                  )

    return baked.params(x1, x2).all()


```

to support the usage of the params without any explicit keys, the `params()` method will also maintain the order in which it encounters these bound parameters and allow the values to be passed positionally in that order.

[Sqlalchemy-tickets] Issue #3053: PostgreSQL HSTORE type should be marked hashable = False (zzzeek/sqlalchemy)

[Gunnlaugur Þ. B. <iss...@bi...>]

New issue 3053: PostgreSQL HSTORE type should be marked hashable = False
https://bitbucket.org/zzzeek/sqlalchemy/issue/3053/postgresql-hstore-type-should-be-marked

Gunnlaugur Þór Briem:

The `HSTORE` type returns `dict` objects, which are not hashable, so it should not have the default `hashable = True`. Fixing this (setting `hashable = False`) allows columns of this type to be loaded along with ORM entities in a `Query` as per #2592.

Will attach a totally trivial patch that does this, breaking no tests, and applies cleanly on rel_0_8, rel_0_9 and master.

[Sqlalchemy-tickets] Issue #3052: First class support for psycopg2cffi (zzzeek/sqlalchemy)

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

New issue 3052: First class support for psycopg2cffi
https://bitbucket.org/zzzeek/sqlalchemy/issue/3052/first-class-support-for-psycopg2cffi

Alex Gaynor:

It'd be great if there was first class support for psycopg2cffi (the name is pretty straightforward :P), which is the best drive for PostgreSQL on PyPy. I suspect it basically just works; adding it to the test matrix is valuable.

[Sqlalchemy-tickets] Issue #3051: Calling setdefault on a MutableDict does not trigger change (zzzeek/sqlalchemy)

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

New issue 3051: Calling setdefault on a MutableDict does not trigger change
https://bitbucket.org/zzzeek/sqlalchemy/issue/3051/calling-setdefault-on-a-mutabledict-does

John Jiang:

I'm using flask-sqlalchemy

```
#!python

class Data(db.Model):
    blob = db.Column(MutableDict.as_mutable(JSON), default={})

data.blob.setdefault('blah', {})
db.session.add(data)
db.session.commit()
```

blob does not save the changes.

[Sqlalchemy-tickets] Issue #3050: support lambdas / strings in column_property (zzzeek/sqlalchemy)

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

New issue 3050: support lambdas / strings in column_property
https://bitbucket.org/zzzeek/sqlalchemy/issue/3050/support-lambdas-strings-in-column_property

Mike Bayer:

mostly to support mixins, e.g.

class mymixin(object):
    bar = Column(Integer)
    foo = Column(Integer)

    @declared_attr
    def foo_plus_bar(cls):
        return column_property(lambda: cls.foo + cls.bar)

[Sqlalchemy-tickets] Issue #3049: support range specificaiton in window function (zzzeek/sqlalchemy)

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

New issue 3049: support range specificaiton in window function
https://bitbucket.org/zzzeek/sqlalchemy/issue/3049/support-range-specificaiton-in-window

Mike Bayer:

e.g.


```
#!sql

SELECT func(x) OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING)
```

see the syntax [here](http://www.postgresql.org/docs/9.1/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS) and [here](http://tapoueh.org/blog/2013/08/20-Window-Functions), also might think of having the named window function.

so for this we have the option for strings:

    func.foo(x).over(frame="RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING)

or maybe an elaborate generative thing

    func.foo(x).over().rows(start="unbounded preceding", end="current row")

[Sqlalchemy-tickets] Issue #3048: Validator methods are not called for synonyms and inherited columns (zzzeek/sqlalchemy)

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

New issue 3048: Validator methods are not called for synonyms and inherited columns
https://bitbucket.org/zzzeek/sqlalchemy/issue/3048/validator-methods-are-not-called-for

Dariusz Górecki:

Consider following example of joined-table inheritance:

    class Parent(DeclarativeBase):
        # ...
        parent_column = Column(String(255))

    class Child(Parent):
        # ...
        child_column = synonym('parent_column')

        @validates('child_column')  # @validates('parent_column') also does not work
        def validator(self, name, value):
            # not called at all
            raise ValueError()

The `validator` method is never called

[Sqlalchemy-tickets] Issue #3047: Errors when creating models are hidden (zzzeek/sqlalchemy)

[Matthias U. <iss...@bi...>]

New issue 3047: Errors when creating models are hidden
https://bitbucket.org/zzzeek/sqlalchemy/issue/3047/errors-when-creating-models-are-hidden

Matthias Urlichs:

The problem I saw was that the _first_ access to MyDeclarativeTable.query returned None, but after that everything worked.

I would like to actually see the error. I cannot understand why you're hiding it, in multiple places.

The first is in lib/sqlalchemy/orm/scoping.py:query_property() where inside the query class you return None if there was an UnmappedClassError.

The second is in sqlalchemy/orm/base.py:_inspect_mapped_class() where you return None upon catching exc.NO_STATE, which is an AttributeError, which neatly masks off the AttributeError that my model caused because of a typo.

I submit that this makes no sense whatsoever, and I'd like to ask you to do something about these nonproductive try/except statements. They mask real problems. I can't see any practical use for them.

This is what I did:
```
#!python

Traceback (most recent call last):
  File "./manage.py", line 42, in <module>
    manager.run()
  File "/usr/lib/python2.7/dist-packages/flask_script/__init__.py", line 412, in run
    result = self.handle(sys.argv[0], sys.argv[1:])
  File "/usr/lib/python2.7/dist-packages/flask_script/__init__.py", line 383, in handle
    res = handle(*args, **config)
  File "/daten/src/git/pip/pybble/pybble/manager/main.py", line 119, in __call__
    return create_app(**kw)
  File "/daten/src/git/pip/pybble/pybble/app/__init__.py", line 342, in create_app
    site = Site.q.get_by(domain=text_type(site))
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/scoping.py", line 130, in __get__
    mapper = class_mapper(owner)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/base.py", line 383, in class_mapper
    raise exc.UnmappedClassError(class_)
UnmappedClassError: Class 'pybble.core.models.site.Site' is not mapped
> /usr/lib/python2.7/dist-packages/sqlalchemy/orm/base.py(383)class_mapper()
-> raise exc.UnmappedClassError(class_)
(Pdb) q
smurf@data:/daten/src/git/pip/pybble$ test/run.sh populate
> /daten/src/git/pip/pybble/pybble/app/__init__.py(342)create_app()
-> site = Site.q.get_by(domain=text_type(site))
(Pdb) b /usr/lib/python2.7/dist-packages/sqlalchemy/orm/base.py:378
Breakpoint 1 at /usr/lib/python2.7/dist-packages/sqlalchemy/orm/base.py:378
(Pdb) c
> /usr/lib/python2.7/dist-packages/sqlalchemy/orm/base.py(378)class_mapper()
-> mapper = _inspect_mapped_class(class_, configure=configure)
(Pdb) cl
Clear all breaks? y
(Pdb) s
--Call--
> /usr/lib/python2.7/dist-packages/sqlalchemy/orm/base.py(347)_inspect_mapped_class()
-> @inspection._inspects(type)
(Pdb) n
> /usr/lib/python2.7/dist-packages/sqlalchemy/orm/base.py(349)_inspect_mapped_class()
-> try:
(Pdb) 
> /usr/lib/python2.7/dist-packages/sqlalchemy/orm/base.py(350)_inspect_mapped_class()
-> class_manager = manager_of_class(class_)
(Pdb) 
> /usr/lib/python2.7/dist-packages/sqlalchemy/orm/base.py(351)_inspect_mapped_class()
-> if not class_manager.is_mapped:
(Pdb) 
> /usr/lib/python2.7/dist-packages/sqlalchemy/orm/base.py(353)_inspect_mapped_class()
-> mapper = class_manager.mapper
(Pdb) 
> /usr/lib/python2.7/dist-packages/sqlalchemy/orm/base.py(354)_inspect_mapped_class()
-> if configure and mapper._new_mappers:
(Pdb) 
> /usr/lib/python2.7/dist-packages/sqlalchemy/orm/base.py(355)_inspect_mapped_class()
-> mapper._configure_all()
(Pdb) 
AttributeError: "type object 'SiteConfigVar' has no attribute 'name'"
> /usr/lib/python2.7/dist-packages/sqlalchemy/orm/base.py(355)_inspect_mapped_class()
-> mapper._configure_all()
(Pdb) 

```

It's not realistic to expect users to find their bugs by trial and error when Python has error handling which is perfectly capable of reporting exactly what went wrong.

Removing these exception handlers is (mostly) straightforward. See the attached patch, which does not break any of the basic (sqlite) test cases.

[Sqlalchemy-tickets] Issue #3046: PostgreSQL range types need better documentation (zzzeek/sqlalchemy)

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

New issue 3046: PostgreSQL range types need better documentation
https://bitbucket.org/zzzeek/sqlalchemy/issue/3046/postgresql-range-types-need-better

David Lord:

When initializing a model, what is the correct value to pass to a range column?  Prompted by [this StackOverflow question](https://stackoverflow.com/questions/23563736/how-to-use-postgres-numeric-range-with-sqlalchemy), I looked at [the tests](https://bitbucket.org/zzzeek/sqlalchemy/src/55eacc8dbea3c3f98197bde9034fd6558fb2bc09/test/dialect/postgresql/test_types.py?at=master#cl-1589) and found they were using a [type helper from psycopg2](http://pythonhosted.org//psycopg2/extras.html#range-data-types).  Is that the proper way, or is there something on SQLAlchemy's side too?  Either way, it's not made clear in the docs.

[Sqlalchemy-tickets] Issue #3045: Empty OR clause with table.delete() produces invalid SQL (zzzeek/sqlalchemy)

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

New issue 3045: Empty OR clause with table.delete() produces invalid SQL
https://bitbucket.org/zzzeek/sqlalchemy/issue/3045/empty-or-clause-with-tabledelete-produces

monsanto:

See attached test case. Results from running the script:

```
Expected: DELETE FROM test
Got: DELETE FROM test WHERE 
```

[Sqlalchemy-tickets] Issue #3044: INSERT from SELECT UNION (zzzeek/sqlalchemy)

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

New issue 3044: INSERT from SELECT UNION
https://bitbucket.org/zzzeek/sqlalchemy/issue/3044/insert-from-select-union

Mike Bayer:

see if we don't need the subquery here:


```
#!python

from sqlalchemy import *
from sqlalchemy.sql import table, column

product = table('product', column('id'), column('other_id'))

b_id = 2
s_id = 3
id = product.c.id
sel = select(
    [b_id, product.c.id],
).union(
    select([b_id, s_id])
)

ins = insert(product).from_select([
    product.c.id, product.c.other_id
    ],
    sel
)

print ins
```

patch:


```
#!diff

index 61abe81..2b4320a 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -42,7 +42,7 @@ def _interpret_as_select(element):
     element = _interpret_as_from(element)
     if isinstance(element, Alias):
         element = element.original
-    if not isinstance(element, Select):
+    if not isinstance(element, SelectBase):
         element = element.select()
     return element
 

```

[Sqlalchemy-tickets] Issue #3043: Error "File descriptor was closed in another greenlet" using v0.9.4 and gevent.monkey.patch_all (zzzeek/sqlalchemy)

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

New issue 3043: Error "File descriptor was closed in another greenlet" using v0.9.4 and gevent.monkey.patch_all
https://bitbucket.org/zzzeek/sqlalchemy/issue/3043/error-file-descriptor-was-closed-in

Gaofeng Liang:

I'm using gevent and sqlalchemy in my thrift(RPC) project. I upgraded SQLAlchemy from v0.9.3 to v0.9.4 a few days ago and then found the error.
Following is how I use sqlalchemy and gevent:
* scoped_session(thread-local)
* use gevent.spawn to handle every request and session.close after finishing handling

The error is gone when SQLAlchemy is  downgraded to v0.9.3.

I noticed that the version 0.9.4 has "Key fixes include an enhancement to the mechanics of connection pool recycling to be more efficient ".  Does it have anything to do with the error.


```
#!python
File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 233, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 577, in get
    value = callable_(state, passive)
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/sqlalchemy/orm/state.py", line 360, in __call__
    self.manager.deferred_scalar_loader(self, toload)
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 606, in load_scalar_attributes
    
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 230, in load_on_ident
    return q.one()
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2361, in one
    raise orm_exc.NoResultFound("No row was found for one()")
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2404, in __iter__
    conn = conn.execution_options(**self._execution_options)
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2419, in _execute_and_instances
    returned by this :class:`.Query`.
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 720, in execute
    """Execute a sql.FunctionElement object."""
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 817, in _execute_clauseelement
    self.dispatch.after_execute(self,
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 947, in _execute_context
    context._fetch_implicit_returning(result)
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1108, in _handle_dbapi_exception
    The operations inside the function are all invoked within the
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 940, in _execute_context
    context.post_insert()
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 435, in do_execute
    # after the initial set of 'isolation_level', if any, so is
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 132, in execute
    result = self._query(query)
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 271, in _query
    conn.query(q)
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/pymysql/connections.py", line 726, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/pymysql/connections.py", line 861, in _read_query_result
    result.read()
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1064, in read
    first_packet = self.connection._read_packet()
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/pymysql/connections.py", line 825, in _read_packet
    packet = packet_type(self)
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/pymysql/connections.py", line 242, in __init__
    self._recv_packet(connection)
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/pymysql/connections.py", line 248, in _recv_packet
    packet_header = connection._read_bytes(4)
  File "/srv/virtualenvs/zeus2env/local/lib/python2.7/site-packages/pymysql/connections.py", line 838, in _read_bytes
    2013, "Lost connection to MySQL server during query (%r)" % (e,))
OperationalError: (OperationalError) (2013, "Lost connection to MySQL server during query (error(9, 'File descriptor was closed in another greenlet'))")



```

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3042: ensure warning for implicitly combined columns occurs for inherited classes as well (zzzeek/sqlalchemy)

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

New issue 3042: ensure warning for implicitly combined columns occurs for inherited classes as well
https://bitbucket.org/zzzeek/sqlalchemy/issue/3042/ensure-warning-for-implicitly-combined

Mike Bayer:

test:


```
#!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(A):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    a_id = Column(Integer, ForeignKey('a.id'))


```

note that we don't get the "same named columns" warning for A.id and B.id because our test for this is too simplistic, assuming that in an inheritance scenario, a subtable with the same column should implicitly combine with that of a supertable.   I'm not sure if I intended this to be this way for all implicitly combined columns that were in an inheritance situation or somehow thought these would always be tables in the sync condition, but these are separate columns and the "same named" error should still apply.  We will make it a warning in 0.9 and an error in 1.0 as follows:


```
#!diff

diff --git a/lib/sqlalchemy/orm/mapper.py b/lib/sqlalchemy/orm/mapper.py
index 3e93840..1377945 100644
--- a/lib/sqlalchemy/orm/mapper.py
+++ b/lib/sqlalchemy/orm/mapper.py
@@ -1604,13 +1604,17 @@ class Mapper(_InspectionAttr):
         prop = self._props.get(key, None)
 
         if isinstance(prop, properties.ColumnProperty):
-            if prop.parent is self:
-                raise sa_exc.InvalidRequestError(
-                        "Implicitly combining column %s with column "
-                        "%s under attribute '%s'.  Please configure one "
-                        "or more attributes for these same-named columns "
-                        "explicitly."
-                         % (prop.columns[-1], column, key))
+            if (prop.columns[0], column) not in self._inherits_equated_pairs and \
+                    not prop.columns[0].shares_lineage(column):
+                warn_only = prop.parent is not self
+                msg = ("Implicitly combining column %s with column "
+                      "%s under attribute '%s'.  Please configure one "
+                      "or more attributes for these same-named columns "
+                      "explicitly." % (prop.columns[-1], column, key))
+                if warn_only:
+                    util.warn(msg)
+                else:
+                    raise sa_exc.InvalidRequestError(msg)
 
             # existing properties.ColumnProperty from an inheriting
             # mapper. make a copy and append our column to it

```

this is extracted from #3041.

needs tests for this specific case. 

patch is attached which modifies quite a *lot* of tests which suffer from this warning.

[Sqlalchemy-tickets] Issue #3041: ORM primary key retreival can populate unrelated column in mappers used for inheritance (zzzeek/sqlalchemy)

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

New issue 3041: ORM primary key retreival can populate unrelated column in mappers used for inheritance
https://bitbucket.org/zzzeek/sqlalchemy/issue/3041/orm-primary-key-retreival-can-populate

ebnull:

When in an inheritance relationship, SA can overwrite a child table's column when setting the parent's primary key (as grabbed from the database) with the parent's primary key if the the child's column's attribute name matches the parent's attribute name.

See attached bug2.py for example. Sample output follows:


```
$ python bug2.py
INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE bug_parent (
        discriminator VARCHAR(50), 
        id INTEGER NOT NULL, 
        parent_attr INTEGER, 
        PRIMARY KEY (id)
)


INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE bug_child_a (
        id INTEGER NOT NULL, 
        parent_id INTEGER NOT NULL, 
        child_attr INTEGER, 
        PRIMARY KEY (id),         UNIQUE (parent_id), 
        FOREIGN KEY(parent_id) REFERENCES bug_parent (id)
)

INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE bug_child_b (
        id INTEGER NOT NULL,         parent_id INTEGER NOT NULL, 
        child_attr INTEGER, 
        PRIMARY KEY (id), 
        UNIQUE (parent_id), 
        FOREIGN KEY(parent_id) REFERENCES bug_parent (id)
)
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE bug_child_c (
        some_primary_key INTEGER NOT NULL, 
        id INTEGER, 
        parent_id INTEGER NOT NULL, 
        child_attr INTEGER, 
        PRIMARY KEY (some_primary_key), 
        UNIQUE (parent_id), 
        FOREIGN KEY(parent_id) REFERENCES bug_parent (id)
)


INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT


INFO:__main__:The following SQL should not be setting bug_child_a.id (incorrect)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO bug_parent (discriminator, parent_attr) VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine:('child_a', 100)
DEBUG:__main__:Validating ChildA.id=1
INFO:sqlalchemy.engine.base.Engine:INSERT INTO bug_child_a (id, parent_id, child_attr) VALUES (?, ?, ?)
INFO:sqlalchemy.engine.base.Engine:(1, 1, 200)


INFO:__main__:The following SQL does not set bug_child_b.id (debatably correct)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO bug_parent (discriminator, parent_attr) VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine:('child_b', 300)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO bug_child_b (parent_id, child_attr) VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine:(2, 400)
DEBUG:__main__:Validating ChildB.child_extra_id=1


INFO:__main__:The following SQL sets bug_child_c.id to None (correct)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO bug_parent (discriminator, parent_attr) VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine:('child_c', 500)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO bug_child_c (id, parent_id, child_attr) VALUES (?, ?, ?)
INFO:sqlalchemy.engine.base.Engine:(None, 3, 600)

NFO:sqlalchemy.engine.base.Engine:COMMIT
Testing objects
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:SELECT bug_parent.discriminator AS bug_parent_discriminator, bug_child_a.id AS bug_child_a_id, bug_parent.id AS bug_parent_id, bug_parent.parent_attr AS bug_parent_parent_attr, bug_child_a.parent_id AS bug_child_a_parent_id, bug_child_
a.child_attr AS bug_child_a_child_attr 
FROM bug_parent JOIN bug_child_a ON bug_parent.id = bug_child_a.parent_id 
WHERE bug_parent.parent_attr = ?
INFO:sqlalchemy.engine.base.Engine:(100,)
INFO:sqlalchemy.engine.base.Engine:SELECT bug_child_b.id AS bug_child_b_id, bug_parent.discriminator AS bug_parent_discriminator, bug_parent.id AS bug_parent_id, bug_parent.parent_attr AS bug_parent_parent_attr, bug_child_b.parent_id AS bug_child_b_parent_id, bug_child_
b.child_attr AS bug_child_b_child_attr 
FROM bug_parent JOIN bug_child_b ON bug_parent.id = bug_child_b.parent_id 
WHERE bug_parent.parent_attr = ?
INFO:sqlalchemy.engine.base.Engine:(300,)
INFO:sqlalchemy.engine.base.Engine:SELECT bug_child_c.id AS bug_child_c_id, bug_parent.discriminator AS bug_parent_discriminator, bug_parent.id AS bug_parent_id, bug_parent.parent_attr AS bug_parent_parent_attr, bug_child_c.some_primary_key AS bug_child_c_some_primary_k
ey, bug_child_c.parent_id AS bug_child_c_parent_id, bug_child_c.child_attr AS bug_child_c_child_attr 
FROM bug_parent JOIN bug_child_c ON bug_parent.id = bug_child_c.parent_id 
WHERE bug_parent.parent_attr = ?
INFO:sqlalchemy.engine.base.Engine:(500,)
{'ChildA.parent_attr': 100, 'ChildA.child_attr': 200, 'ChildA._discriminator': u'child_a', 'ChildA.id': 1, 'ChildA.parent_id': 1}
{'ChildB._discriminator': u'child_b', 'ChildB.parent_id': 2, 'ChildB.parent_attr': 300, 'ChildB.id': 2, 'ChildB.child_attr': 400, 'ChildB.child_extra_id': 1}
{'ChildC.id': 3, 'ChildC.child_attr': 600, 'ChildC._discriminator': u'child_c', 'ChildC.parent_attr': 500, 'ChildC.some_primary_key': 1, 'ChildC.child_extra_id': None, 'ChildC.parent_id': 3}
Traceback (most recent call last):
  File "bug2.py", line 124, in <module>
    assert cb.child_extra_id == None, "SQLA set bug_child_b.id to a non-null value"
AssertionError: SQLA set bug_child_b.id to a non-null value
```

[Sqlalchemy-tickets] Issue #3040: association proxies are not json serializable (zzzeek/sqlalchemy)

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

New issue 3040: association proxies are not json serializable
https://bitbucket.org/zzzeek/sqlalchemy/issue/3040/association-proxies-are-not-json

metagriffin:

this can be easily circumvented by simply casting them when needed, e.g. calling ``json.dumps(list(assoc_list))``.

however, it would be great if these objects were directly serializable. the ORM instrumented objects, InstrumentedList, InstrumentedSet, and InstrumentedDict already achieve this because they respectively subclass list, set, and dict -- the ``json`` package handles them appropriately then.

[Sqlalchemy-tickets] Issue #3039: ntext - sql server (zzzeek/sqlalchemy)

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

New issue 3039: ntext - sql server
https://bitbucket.org/zzzeek/sqlalchemy/issue/3039/ntext-sql-server

Landon Neale:

In SQL Server 2012, the ntext has been deprecated; however, for sql alchemy 0.9.4 for p2.7, it still uses the ntext in the mssql dialects which causes issues/sql type errors. Found solution in C:\Python2732\Lib\site-packages\sqlalchemy-0.9.4-py2.7.egg\sqlalchemy\dialects\mssql\base.py and replacing references to nvarchar.

[Sqlalchemy-tickets] Issue #3038: compilers that apply binds in select precolumns can be out of sync with column-nested subqueries (zzzeek/sqlalchemy)

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

New issue 3038: compilers that apply binds in select precolumns can be out of sync with column-nested subqueries
https://bitbucket.org/zzzeek/sqlalchemy/issue/3038/compilers-that-apply-binds-in-select

Mike Bayer:

e.g. firebird, sql server, others:


```
#!python

from sqlalchemy import *
from sqlalchemy.sql import column, literal

s1 = select([column('x')]).select_from('a').limit(5).as_scalar()
s2 = select([s1]).limit(10)

from sqlalchemy.engine import default
from sqlalchemy.sql import compiler
class MyCompiler(compiler.SQLCompiler):
    def get_select_precolumns(self, select):
        result = ""
        if select._limit:
            result += "FIRST %s " % self.process(literal(select._limit))
        if select._offset:
            result += "SKIP %s " % self.process(literal(select._offset))
        return result

    def limit_clause(self, select):
        return ""


dialect = default.DefaultDialect()
dialect.statement_compiler = MyCompiler
dialect.paramstyle = 'qmark'
dialect.positional = True
compiled = s2.compile(dialect=dialect)
assert \
  [compiled.params[name] for name in compiled.positiontup] == [10, 5]

```

[Sqlalchemy-tickets] Issue #3037: support setting load options on instances (zzzeek/sqlalchemy)

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

New issue 3037: support setting load options on instances
https://bitbucket.org/zzzeek/sqlalchemy/issue/3037/support-setting-load-options-on-instances

Mike Bayer:

proof of concept


```
#!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)
    bs = relationship("B")

class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    a_id = Column(Integer, ForeignKey('a.id'))
    cs = relationship("C")

class C(Base):
    __tablename__ = 'c'

    id = Column(Integer, primary_key=True)
    b_id = Column(Integer, ForeignKey('b.id'))

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

sess = Session(e)
sess.add_all([
    A(bs=[
        B(cs=[C(), C()]),
        B(cs=[C(), C()])
    ])
])
sess.commit()

a1 = sess.query(A).first()
inspect(a1).load_options = inspect(a1).load_options.union([joinedload(B.cs)])

for b in a1.bs:
    print b.cs

```

im not sure why the above option doesn't have to mention "A.bs" at all, there should be some path logic taking this into account.  The public API should be:

    inspect(a1).set_load_option("bs", joinedload(...))

[Sqlalchemy-tickets] Issue #3036: Select.with_hint() documentation flaw (zzzeek/sqlalchemy)

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

New issue 3036: Select.with_hint() documentation flaw
https://bitbucket.org/zzzeek/sqlalchemy/issue/3036/selectwith_hint-documentation-flaw

Kent Bower:

The documentation for Select.with_hint() method gives two Oracle examples with the "+ " included as the passed text argument, but sqlalchemy generates that part of the hint automatically.

So, contrary to the docs, using 

```
#!python
 select([mytable]).with_hint(mytable, "+ index(%(name)s ix_mytable)")
```

Actually renders: 
```
SELECT /*+ + index(mytable ix_mytable) */
```

Docs should remove the leading "+ " in both places.

[Sqlalchemy-tickets] Issue #3035: overhaul session.binds to support classes fully; fix up tests (zzzeek/sqlalchemy)

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

New issue 3035: overhaul session.binds to support classes fully; fix up tests
https://bitbucket.org/zzzeek/sqlalchemy/issue/3035/overhaul-sessionbinds-to-support-classes

Mike Bayer:

right now session.__binds uses mappers completely.  This makes it impossible to feed in abstract classes and also because it links to base_mapper it's not possible to change the bind along a class hierarchy either (like a concrete hierarchy).

propose that __binds stores classes directly, and this can likely be a fallback in addition to what we have now.  this is kind of a proof of concept


```
#!diff

diff --git a/lib/sqlalchemy/orm/session.py b/lib/sqlalchemy/orm/session.py
index 89d9946..f63adfb 100644
--- a/lib/sqlalchemy/orm/session.py
+++ b/lib/sqlalchemy/orm/session.py
@@ -636,13 +636,20 @@ class Session(_SessionClassMethods):

         if binds is not None:
             for mapperortable, bind in binds.items():
-                insp = inspect(mapperortable)
-                if insp.is_selectable:
-                    self.bind_table(mapperortable, bind)
-                elif insp.is_mapper:
-                    self.bind_mapper(mapperortable, bind)
+                try:
+                    insp = inspect(mapperortable)
+                except exc.NoInspectionAvailable:
+                    if isinstance(mapperortable, type):
+                        self.bind_cls(mapperortable, bind)
+                    else:
+                        raise exc.ArgumentError("Not acceptable bind target: %s" % mapperortable)
                 else:
-                    assert False
+                    if insp.is_selectable:
+                        self.bind_table(mapperortable, bind)
+                    elif insp.is_mapper:
+                        self.bind_mapper(mapperortable, bind)
+                    else:
+                        assert False


         if not self.autocommit:
@@ -1039,6 +1046,17 @@ class Session(_SessionClassMethods):
         for t in mapper._all_tables:
             self.__binds[t] = bind

+    def bind_class(self, cls, bind):
+        """Bind operations for an unmapped class to a Connectable.
+
+        The behavior here is that a subclass of cls which is mapped
+        will be linked to this bind.
+
+        .. versionadded:: 1.0
+
+        """
+        self.__binds[cls] = bind
+
     def bind_table(self, table, bind):
         """Bind operations on a Table to a Connectable.

@@ -1124,6 +1142,12 @@ class Session(_SessionClassMethods):
                     return self.__binds[c_mapper.base_mapper]
                 elif c_mapper.mapped_table in self.__binds:
                     return self.__binds[c_mapper.mapped_table]
+                else:
+                    for cls in c_mapper.class_.__mro__:
+                        if cls in self.__binds:
+                            bind = self.__binds[cls]
+                            self.bind_mapper(c_mapper, bind)
+                            return bind
             if clause is not None:
                 for t in sql_util.find_tables(clause, include_crud=True):
                     if t in self.__binds:

```

the tests here also need a rework, with an emphasis on straight unit testing of get_bind().  test_session -> BindTest is really just integration tests.

[Sqlalchemy-tickets] Issue #3034: Use my own bindparam for Query.limit() (zzzeek/sqlalchemy)

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

New issue 3034: Use my own bindparam for Query.limit()
https://bitbucket.org/zzzeek/sqlalchemy/issue/3034/use-my-own-bindparam-for-querylimit

Dobes Vandermeer:

I am trying to use the "baked query" pattern to reduce the time spent generating SQL, which currently is quite significant for our app.

One thing I can't seem to parameterize using a bindparam, however, is the limit on the query.

Although in #805 the limit was changed into a bindparam, this doesn't allow me to provide my own bindparam but rather always creates its own bindparam, assuming the limit I procide is a number already.

I think that if the places where currently it wraps a numeric limit into a bindparam using sql.literal() it would also check if limit was already a bindparam then it would be good.

Here's an example that can be pasted into the test suite:

```
#!python
    def test_select_with_bindparam_limit(self):
        """Does a query allow bindparam for the limit?"""
        sess = self._downgrade_fixture()
        users = []

        def go():
            users[:] = sess.query(self.classes.User)\
                .order_by(self.classes.User.id)\
                .limit(sa.bindparam('n'))\
                .params(n=10)\
                .all()
        self.assert_sql_count(testing.db, go, 2)
```

[Sqlalchemy-tickets] Issue #3033: WHERE 0 = 1 silently generated (zzzeek/sqlalchemy)

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

New issue 3033: WHERE 0 = 1 silently generated
https://bitbucket.org/zzzeek/sqlalchemy/issue/3033/where-0-1-silently-generated

Jefffrey:

Running:


```
#!python

query = session.query(A).filter( and_( A.id == string_a
                                                          , A.b.key == string_b ) )
```


where:


```
#!python

class A(Base):
    __tablename__   = 'a_table'
    id              = Column(Integer, primary_key = True)
    # ...

class B(Base):
    __tablename__       = 'b_table'
    id                  = Column(Integer, primary_key = True)
    # ...
    a_id = Column(Integer, ForeignKey('a_table.id'), nullable = False)
    a = relationship("A", backref = backref("b", uselist = False))
```

produces a query like:


```
#!sql

SELECT ... 
FROM a_table
WHERE 0 = 1
```

[Sqlalchemy-tickets] Issue #3032: doc navigation - fixed header, sidebar? (zzzeek/sqlalchemy)

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

New issue 3032: doc navigation - fixed header, sidebar?
https://bitbucket.org/zzzeek/sqlalchemy/issue/3032/doc-navigation-fixed-header-sidebar

Mike Bayer:

I've always been frustrated with how you can't figure out where you are when you're deep into a documentation page.     Can we try a layout like this page: https://foundationdb.com/layers/sql/documentation/GettingStarted/SQLAlchemy where:

1. our yellow box at the top with "prev/next" etc. stays fixed as it hits the top of the page

2. the sidebar is an independent scrollable bar with dynamic highlighting

3. we move "search" somewhere so that it at least is always on the page

[Sqlalchemy-tickets] Issue #3031: Docs: Relevant results not on top when searching for class (zzzeek/sqlalchemy)

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

New issue 3031: Docs: Relevant results not on top when searching for class
https://bitbucket.org/zzzeek/sqlalchemy/issue/3031/docs-relevant-results-not-on-top-when

benselme:

Today I needed to read the docs for the Query class. So I typed "Query" in the search box. The first result was "sqlalchemy.orm.session.Session.query" which led me here: http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html?highlight=query#sqlalchemy.orm.session.Session.query

At that point I got "Return a new Query object corresponding to this Session." but "Query" is not a link so I went back to the list where I then had to scroll two pages to finally find "sqlalchemy.orm.query.Query".

Shouldn't a class whose name matches the search term exactly be on, or near the top ?