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

[Sqlalchemy-tickets] Issue #3482: `icontains`, `istartswith`, `iendswith` (zzzeek/sqlalchemy)

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

New issue 3482: `icontains`, `istartswith`, `iendswith`
https://bitbucket.org/zzzeek/sqlalchemy/issues/3482/icontains-istartswith-iendswith

jvanasco:


discussed previously in 
* https://groups.google.com/forum/#!searchin/sqlalchemy/icontains/sqlalchemy/MUyt8zYaP0c/ht4fGR9q9w8J
* https://groups.google.com/forum/#!searchin/sqlalchemy/icontains/sqlalchemy/331XoToT4lk/RPjIYNTkSEIJ

I tried doing this myself, but don't know enough of the internals to get this done on my own.

it looks like these need to be modified

* sqlalchemy/sql/default_comparator.py
* sqlalchemy/sql/operators.py

but not sure about

* sqlalchemy/sql/compiler.py

there are probably more files.

if someone can get one working, I can do the rest and build out tests.

[Sqlalchemy-tickets] Issue #3481: AttributeError: 'NoneType' object has no attribute '_getter' in sqlalchemy.engine.result (zzzeek/sqlalchemy)

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

New issue 3481: AttributeError: 'NoneType' object has no attribute '_getter' in sqlalchemy.engine.result
https://bitbucket.org/zzzeek/sqlalchemy/issues/3481/attributeerror-nonetype-object-has-no

Hanno Schlichting:

Got a Sentry report from a live site today, using SQLAlchemy 1.0.6, PyMySQL 0.6.3 against a Amazon RDS MySQL 5.6 instance.

This happened just once, in a code path that is frequently executed, so it must be some kind of weird state it got in, though I don't know what state that is.

Looking through the issue tracker, this might be related to a new 1.0 feature via issue #3175.

Full traceback:

```
#!python

Stacktrace (most recent call last):

  <removed app code specific stack frames>

  File "sqlalchemy/orm/query.py", line 2445, in first
    ret = list(self[0:1])
  File "sqlalchemy/orm/query.py", line 2281, in __getitem__
    return list(res)
  File "sqlalchemy/orm/loading.py", line 84, in instances
    util.raise_from_cause(err)
  File "sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "sqlalchemy/orm/loading.py", line 51, in instances
    for query_entity in query._entities
  File "sqlalchemy/orm/query.py", line 3312, in row_processor
    polymorphic_discriminator=self._polymorphic_discriminator
  File "sqlalchemy/orm/loading.py", line 313, in _instance_processor
    getter = result._getter(col)
  File "sqlalchemy/engine/result.py", line 495, in _getter
    return self._metadata._getter(key)

AttributeError: 'NoneType' object has no attribute
```

The query going into this is a simple:
```
#!python

session.query(SomeModel).filter(SomeModel.pk_column == 'somevalue')
.first()
```

In the last stackframe, the locals are:
```
#!python

key = Column('some_column', INTEGER(), table=<some_table>)
self = <sqlalchemy.engine.result.ResultProxy object at 0x4aa7e50>
```

It looks to me like `ResultProxy._metadata` ends up being `None`, even after `_init_metadata` is called. I see a bunch of `if self._metadata is None` cases in the other functions, so maybe this just needs one of those protections as well.

[Sqlalchemy-tickets] Issue #3480: abstractconcretebase regression with setting up columns via name vs. attribute name (zzzeek/sqlalchemy)

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

New issue 3480: abstractconcretebase regression with setting up columns via name vs. attribute name
https://bitbucket.org/zzzeek/sqlalchemy/issues/3480/abstractconcretebase-regression-with

Mike Bayer:

without the "key" workarounds uncommented, this fails:

```
#!python

from sqlalchemy.ext.declarative import declarative_base, AbstractConcreteBase
from sqlalchemy.ext.declarative.api import declared_attr
from sqlalchemy.orm.mapper import configure_mappers
from sqlalchemy.orm.session import Session
from sqlalchemy.sql.schema import Column, ForeignKey
from sqlalchemy.sql.sqltypes import Date, String, Integer

Base = declarative_base()


class Company(Base):
    __tablename__ = 'companies'
    id = Column(Integer, primary_key=True)


class Document(object):
    date = Column(Date)
    documentType = Column('documenttype', String) #, key="documentType")


class ContactDocument(AbstractConcreteBase, Base, Document):
    contactPersonName = Column('contactpersonname', String) #, key="contactPersonName")
    salesPersonName = Column(String)
    sendMethod = Column('sendmethod', String) #, key="sendMethod")

    @declared_attr
    def company_id(self):
        return Column(ForeignKey('companies.id'))


class Offer(ContactDocument):
    __tablename__ = 'offers'

    id = Column(Integer, primary_key=True)


class SalesOrder(ContactDocument):
    __tablename__ = 'orders'

    id = Column(Integer, primary_key=True)


configure_mappers()
session = Session()
query = session.query(ContactDocument)
print(query)
```

```
#!


  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line 627, in __init__
    self._configure_properties()
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line 1284, in _configure_properties
    self._configure_property(key, prop, False)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line 1525, in _configure_property
    prop = self._property_from_column(key, prop)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line 1679, in _property_from_column
    "attribute." % (key, self, c))
sqlalchemy.exc.ArgumentError: When configuring property 'documentType' on Mapper|ContactDocument|pjoin, column 'documenttype' is not represented in the mapper's table. Use the `column_property()` function to force this column to be mapped as a read-only attribute.
```

[Sqlalchemy-tickets] Issue #3479: detecting whether outerjoin finds a match or not (zzzeek/sqlalchemy)

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

New issue 3479: detecting whether outerjoin finds a match or not
https://bitbucket.org/zzzeek/sqlalchemy/issues/3479/detecting-whether-outerjoin-finds-a-match

Tim Tisdall:

When doing an outerjoin it's possible an object is returned as a `None`.  I wanted to filter rows based on whether one of the objects would be returned or would be a `None` so I tried something like the following:

```python
DBSession.query(
    TableA,
    TableB
).outerjoin(
    TableB
).filter(
    TableB == None
)
```

Looking at the resulting SQL, it seems the `TableB == None` filter is translated into `false = 1`.  `TableB !=None` seems to translate into `1 = 1`.

It seems that the way to get this to work is:

```python
DBSession.query(
    TableA,
    TableB
).outerjoin(
    TableB
).filter(
    TableB.some_primary_key == None
)
```

Essentially you need to test for `None` on an attribute that should never be `None`.  However, that seems a little confusing as the result of the query is not a `TableB` object with that attribute equal to `None`, but the whole object is instead `None`.

This issue is a feature request to make the top-most query work as expected.  This could be done by translating `TableB == None` into a test for a `NULL` value on any of the primary keys of `TableB`.

[Sqlalchemy-tickets] Issue #3478: Inherit condition doesn't work with a ForeignKeyConstraint on multi column (zzzeek/sqlalchemy)

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

New issue 3478: Inherit condition doesn't work with a ForeignKeyConstraint on multi column
https://bitbucket.org/zzzeek/sqlalchemy/issues/3478/inherit-condition-doesnt-work-with-a

Jean-Sébastien Suzanne:

Hi, this is an example

```
#!python

from sqlalchemy import (Column, Integer, String, create_engine,
                        ForeignKeyConstraint)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker


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


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


class Test2(Test):
    __tablename__ = 'test2'
    id = Column(Integer, primary_key=True)
    id2 = Column(Integer, primary_key=True)

    __table_args__ = (
        ForeignKeyConstraint([id, id2], [Test.id, Test.id2]),
    )

    __mapper_args__ = {
        'inherit_condition': (id == Test.id and id2 == Test.id2),
    }


Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
print(str(session.query(Test2)))
```

We get a bad result:
```
#!

SELECT test2.id AS test2_id, test.id AS test_id, test2.id2 AS test2_id2, test.id2 AS test_id2 
FROM test JOIN test2 ON test2.id = test.id
```

Without the ``inherit_condition``, it is seem ok

```
#!
SELECT test2.id AS test2_id, test.id AS test_id, test2.id2 AS test2_id2, test.id2 AS test_id2 
FROM test JOIN test2 ON test.id = test2.id AND test.id2 = test2.id2

```

[Sqlalchemy-tickets] Issue #3477: different results from sqlalchemy generated sql compared to result from orm - sqlalchemy version 1.0.6 and below (zzzeek/sqlalchemy)

[Michał S. <iss...@bi...>]

New issue 3477: different results from sqlalchemy generated sql compared to result from orm - sqlalchemy version 1.0.6 and below
https://bitbucket.org/zzzeek/sqlalchemy/issues/3477/different-results-from-sqlalchemy

Michał Szczepański:

When I'm trying to join two tables using String values that are duplicating in each column I get less rows then expected

The query from database returns 10 rows but filtered is set to True and after 
```
#!python

        if filtered:
            rows = util.unique_list(rows, filter_fn)

```
in sqlalchemy.orm.loading.py one row is left. 

I think it's a problem with hashing.

```
#!python


            def filter_fn(row):
                return tuple(fn(x) for x, fn in zip(row, filter_fns))

```

Exist with 1.0.6 and below.


Script with full example in attachment with workaround.
Script create tables then execute bugged query and workaround query.
You can vary result number from bugged query by changing result_rows to number from 1 to 10

```
#!python
s = Scaffold(db, result_rows=2)

```
Test script deletes the database file after run so it's always fresh start.

I tried it on 1.0.6 on debian and 0.9.8 on windows same results

[Sqlalchemy-tickets] Issue #3476: StaleDataError in sqlalchemy 1.0.6 (zzzeek/sqlalchemy)

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

New issue 3476: StaleDataError in sqlalchemy 1.0.6
https://bitbucket.org/zzzeek/sqlalchemy/issues/3476/staledataerror-in-sqlalchemy-106

Patrick Hayes:

When calling `bulk_update_mappings` in version 1.0.6, occasionally StaleDataErrors emerge.

Previously I was using the fork at https://github.com/pfhayes/sqlalchemy (from this GitHub PR: https://github.com/zzzeek/sqlalchemy/pull/181) and no StaleDataErrors were ever observed.

I tried updating to 1.0.6 and StaleDataErrors started to occur frequently. They happen intermittently when calling code like this:

```python
Base = declarative_base()
class User(Base):
  id = Column(BigInteger, primary_key=True)
  score = Column(DOUBLE_PRECISION)

session.bulk_update_mappings(User, {
  'id': X, 'score': Y,
  ...
})
```

The StaleDataError typically looks like 

```
StaleDataError: UPDATE statement on table 'users' expected to update 18 row(s); 17 were matched.
```

It seems to always expect n+1 when n are matched.

I haven't been able to get a consistent repro. Any suggestions on this?

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3475: OrderedProperties suffers from clashes between table columns names and its own methods (zzzeek/sqlalchemy)

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

New issue 3475: OrderedProperties suffers from clashes between table columns names and its own methods
https://bitbucket.org/zzzeek/sqlalchemy/issue/3475/orderedproperties-suffers-from-clashes

Alessandro Molina:

OrderedProperties provides the __getattr__ method which allows to lookup for column information using the dot notation, the issue is that in case my column was actually called like a method of OrderedProperties it returns the method instead of the column.

This is probably not a bug, but it's an unexpected behaviour in most cases. See https://github.com/TurboGears/tg2/issues/64 for origin of this issue.

Probably dot notation access should be deprecated to avoid confusion and only allow dictionary stile access?

[Sqlalchemy-tickets] Issue #3474: Support for SQL triggers (zzzeek/sqlalchemy)

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

New issue 3474: Support for SQL triggers
https://bitbucket.org/zzzeek/sqlalchemy/issue/3474/support-for-sql-triggers

Konsta Vesterinen:

The more complex my projects have become using SQLAlchemy the more I've used SQL triggers. It would be great if SQLAlchemy had builtin support for triggers. This would make it possible to:

1. Have re-usable trigger code instead of ugly string based CREATE / DROP DDL statements 
2. Add later support in alembic for these triggers. My triggers have evolved greatly over time, thus it would be great if alembic noticed these changes automatically when issuing autogenerate.

[Sqlalchemy-tickets] Issue #3473: Broken link to FAQ in docs (zzzeek/sqlalchemy)

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

New issue 3473: Broken link to FAQ in docs
https://bitbucket.org/zzzeek/sqlalchemy/issue/3473/broken-link-to-faq-in-docs

Jeff Widman:

https://github.com/zzzeek/sqlalchemy/edit/master/doc/build/orm/session_basics.rst#l161

the '</faq/index>' part isn't actually hyperlinked in the generated docs: http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#session-frequently-asked-questions

I spent some time trying to figure out the syntax for Restructured text links and just decided I'll report it as someone else can probably fix in about two seconds...

[Sqlalchemy-tickets] Issue #3472: run _postfetch() for post_update to expire onupdates (zzzeek/sqlalchemy)

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

New issue 3472: run _postfetch() for post_update to expire onupdates
https://bitbucket.org/zzzeek/sqlalchemy/issue/3472/run-_postfetch-for-post_update-to-expire

Mike Bayer:

right now onupdates aren't caught


```
#!python

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

Base = declarative_base()

from itertools import count

value = count()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    favorite_b_id = Column(ForeignKey('b.id'))
    bs = relationship("B", primaryjoin="A.id == B.a_id")
    favorite_b = relationship(
        "B", primaryjoin="A.favorite_b_id == B.id", post_update=True)
    updated = Column(Integer, onupdate=lambda: next(value))


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

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

s = Session(e)
a1 = A()
b1 = B()

a1.bs.append(b1)
a1.favorite_b = b1
s.add(a1)
s.flush()
assert a1.updated == 0, a1.updated

```

this is definitely for 1.1 at earliest

[Sqlalchemy-tickets] Issue #3471: onupdate runs for post_update updates (zzzeek/sqlalchemy)

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

New issue 3471: onupdate runs for post_update updates
https://bitbucket.org/zzzeek/sqlalchemy/issue/3471/onupdate-runs-for-post_update-updates

thiefmaster:

I'm inserting a new row where I specify an explicit value for a column that usually has a value set via `onupdate` in the model.

This works fine except when a relationship with `post_update=True` is involved. In this case the UPDATE used for the relationship also uses the value from `onupdate`.

[Sqlalchemy-tickets] Issue #3470: Integer(mysql) != Integer(postgres) (zzzeek/sqlalchemy)

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

New issue 3470: Integer(mysql) != Integer(postgres)
https://bitbucket.org/zzzeek/sqlalchemy/issue/3470/integer-mysql-integer-postgres

Leonardo Rossi:

I found that a `Integer` column in mysql means `long` in python and for PostgreSQL means `int` in python.
It means that to have a `long` in both I need to use `BigInteger`?

e.g.
```python
from sqlalchemy import Column, Integer, Text, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()


class FuuTable(Base):

    __tablename__ = 'fuutable'
    id = Column(Integer(), primary_key=True, autoincrement=True)
    name = Column(Text(), nullable=False)

mysql_e = create_engine("mysql://root:@localhost/invenio", echo=True)
postgres_e = create_engine(
    "postgres://postgres:postgres@localhost/testdb5",
    echo=True)

# mysql
Base.metadata.drop_all(mysql_e)
Base.metadata.create_all(mysql_e)
Session = sessionmaker(bind=mysql_e)
mysql_session = Session()

mysql_fuu = FuuTable(name="bar")

mysql_session.add(mysql_fuu)
mysql_session.commit()

mysql_test = mysql_session.query(FuuTable).filter(
    FuuTable.id == mysql_fuu.id).first()

# postgres
Base.metadata.drop_all(postgres_e)
Base.metadata.create_all(postgres_e)
Session = sessionmaker(bind=postgres_e)
postgres_session = Session()

postgres_fuu = FuuTable(name="bar")

postgres_session.add(postgres_fuu)
postgres_session.commit()

postgres_test = postgres_session.query(FuuTable).filter(
    FuuTable.id == postgres_fuu.id).first()

# test if Integer(mysql) == Integer(postgres)
print "Integer(mysql) = ", type(postgres_test.id)
print "Integer(postgres) = ", type(mysql_test.id)
print type(postgres_test.id) == type(mysql_test.id)
```

[Sqlalchemy-tickets] Issue #3469: comparison in persistence.py can trip up on clauseelement sitautions (zzzeek/sqlalchemy)

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

New issue 3469: comparison in persistence.py can trip up on clauseelement sitautions
https://bitbucket.org/zzzeek/sqlalchemy/issue/3469/comparison-in-persistencepy-can-trip-up-on

Mike Bayer:

We're seeing stack traces like this:


```
#!

 File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2122, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2086, in _flush
    flush_context.execute()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 373, in execute
    rec.execute(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 532, in execute
    uow
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 170, in save_obj
    mapper, table, update)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 630, in _emit_update_statements
    lambda rec: (
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 459, in _collect_update_commands
    value, state.committed_state[propkey]):
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 2726, in __bool__
    raise TypeError("Boolean value of this clause is not defined")
TypeError: Boolean value of this clause is not defined
```

examples include numpy objects as well as geoalchemy2 objects:


```
#!python

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


Base = declarative_base()


class A(Base):
    __tablename__ = 'tests'
    id = Column(Integer, primary_key=True)
    geom = Column(geoalchemy2.Geometry('POLYGON'))


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

s = Session(e)
s.add(A(id=1, geom='POLYGON((0 0,1 0,1 1,0 1,0 0))'))
s.commit()
a1 = s.query(A).first()
a1.geom = None

s.commit()

```

in 0.9 we use attributes.from_scalar_attribute() which does the is_equals() and then compares it to "is True", so that's why we're suddenly failing.

[Sqlalchemy-tickets] Issue #3468: Deferred column_property and subclassing (zzzeek/sqlalchemy)

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

New issue 3468: Deferred column_property and subclassing
https://bitbucket.org/zzzeek/sqlalchemy/issue/3468/deferred-column_property-and-subclassing

harut:

Hi! There is a quite specific test case for sqlalchemy 1.0.*. Deferred column_property if defined in a subclass, fails after flush.

The test passes in sqlalchemy 0.9.7 and fails in 1.0.0-1.0.9


        class A(self.Base):
            id = Column(Integer, primary_key=True)
        class B(A):
            id = Column(Integer, ForeignKey(A.id), primary_key=True)
            data = Column(String)
            expr = column_property(data+' '+data, deferred=True)

        b = B(id=2, data='aaa')
        db.add(b)
        db.flush()
        b.expr

Gives a following exception:

      File "sqlalchemy/orm/attributes.py", line 237, in __get__
        return self.impl.get(instance_state(instance), dict_)
      File "sqlalchemy/orm/attributes.py", line 592, in get
        "correctly" % key)
    KeyError: "Deferred loader for attribute 'data' failed to populate correctly"

I have no idea what is going on, but the code of `get` function is a little bit strange for me:

    def get(self, state, dict_, passive=PASSIVE_OFF):
        """..."""
        if self.key in dict_:
            return dict_[self.key]
        else:
            key = self.key
            if key not in state.committed_state or \
                    state.committed_state[key] is NEVER_SET:

                ....

                elif value is ATTR_WAS_SET:
                    try:
                        # XXX dict_[key] seems to be always not defined, because we have checked
                        #          self.key in dict_ before
                        return dict_[key]
                    except KeyError:
                        # TODO: no test coverage here.
                        raise KeyError(
                            "Deferred loader for attribute "
                            "%r failed to populate "
                            "correctly" % key)
    ...

[Sqlalchemy-tickets] Issue #3467: Array of enums does not allow assigning (zzzeek/sqlalchemy)

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

New issue 3467: Array of enums does not allow assigning
https://bitbucket.org/zzzeek/sqlalchemy/issue/3467/array-of-enums-does-not-allow-assigning

Wichert Akkerman:

There is an overlap with #2940 here.

I have an array of enums:

```python
topping = sa.Enum('spinach', 'feta', 'pinenuts', name='topping')

Base = declarative_base()

class Pizza(Base):
    __tablename__ = 'pizza'
    id = sa.Column(sa.Integer(), primary_key=True)
    toppings = sa.Column(ARRAY(topping))
```

With that minimal model I try to creating a new pizza instance with some toppings:

```python
pizza = Pizza(toppings=['feta', 'spinach'])
session.add(pizza)
session.flush()
```

which results in this error:

```
Traceback (most recent call last):
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 442, in do_execute
    cursor.execute(statement, parameters)
psycopg2.ProgrammingError: column "toppings" is of type topping[] but expression is of type text[]
LINE 1: INSERT INTO pizza (toppings) VALUES (ARRAY['feta', 'spinach'...
                                             ^
HINT:  You will need to rewrite or cast the expression.


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

Traceback (most recent call last):
  File "x.py", line 25, in <module>
    session.flush()
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 2004, in flush
    self._flush(objects)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 2122, in _flush
    transaction.rollback(_capture_exception=True)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 182, in reraise
    raise value
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 2086, in _flush
    flush_context.execute()
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/orm/unitofwork.py", line 373, in execute
    rec.execute(self)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/orm/unitofwork.py", line 532, in execute
    uow
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/orm/persistence.py", line 174, in save_obj
    mapper, table, insert)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/orm/persistence.py", line 761, in _emit_insert_statements
    execute(statement, params)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1339, in _handle_dbapi_exception
    exc_info
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 188, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 181, in reraise
    raise value.with_traceback(tb)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 442, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "toppings" is of type topping[] but expression is of type text[]
LINE 1: INSERT INTO pizza (toppings) VALUES (ARRAY['feta', 'spinach'...
                                             ^
HINT:  You will need to rewrite or cast the expression.
 [SQL: 'INSERT INTO pizza (toppings) VALUES (%(toppings)s) RETURNING pizza.id'] [parameters: {'toppings': ['feta', 'spinach']}]
```

This is using SQLAlchemy 1.0.4 and psycopg2 2.5.4.

[Sqlalchemy-tickets] Issue #3466: 1.0.x regression involving aliases and synonyms (zzzeek/sqlalchemy)

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

New issue 3466: 1.0.x regression involving aliases and synonyms
https://bitbucket.org/zzzeek/sqlalchemy/issue/3466/10x-regression-involving-aliases-and

inklesspen:

This issue seems remarkably similar to #3445 but I've verified it still exists in 1.0.6 (and does not exist in 0.9.9).

In the below test case, generating the query without an explicit `select_from` causes the query to be generated without the label for the first table. (The problem goes away if you explicitly specify `select_from` or if you stop using the `public_id` synonym column and use the username column directly.)

```python
from sqlalchemy import (
    Column, Integer, String, Boolean, DateTime,
    ForeignKey)
from sqlalchemy.orm import Session, aliased, Bundle, relationship, synonym
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class AuthUser(Base):
    __tablename__ = 'auth_user'
    id = Column('user_ptr_id', Integer, primary_key=True, nullable=False)
    username = Column(String(32), nullable=False)
    public_id = synonym('username')  # Please use this field in preference to username

    first_name = Column(String(length=30), nullable=False)
    last_name = Column(String(length=30), nullable=False)

    email = Column(String(length=75), nullable=False)

    company_id = Column(Integer, ForeignKey('company.id'), nullable=False)

    company = relationship('Company', backref="axial_user", uselist=False)


class Company(Base):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True, nullable=False)
    public_id = Column(String(32), unique=True, nullable=False)
    name = Column(String(length=255), nullable=False)
    company_type = Column(String(length=5))
    last_activity_date = Column(DateTime, nullable=False)


class License(Base):
    __tablename__ = 'license'
    public_id = Column(String(32), primary_key=True)
    is_active = Column(Boolean, nullable=False, server_default="FALSE")
    company_public_id = Column(String(32), nullable=False, index=True)


def query(db_session, explicit):
    AU = aliased(AuthUser)
    CMP = aliased(Company)
    LIC = aliased(License)

    USER = Bundle('user',
                  AU.public_id,
                  AU.first_name,
                  AU.last_name,
                  AU.email,
                  )
    COMP = Bundle('comp',
                  CMP.public_id,
                  CMP.name,
                  CMP.company_type,
                  CMP.last_activity_date,
                  LIC.is_active,
                  )

    sql = db_session.query(USER, COMP)
    if explicit:
        sql = sql.select_from(AU)
    sql = sql.join(CMP, CMP.id == AU.company_id)
    sql = sql.join(LIC, LIC.company_public_id == CMP.public_id)

    return sql


s = Session()

print query(s, False)
print "-----"
print query(s, True)
```

The relevant bits are in the FROM clause:

```sql
FROM auth_user JOIN company AS company_1 ON ...
```

vs

```sql
FROM auth_user AS auth_user_1 JOIN company AS company_1 ON ...
```

[Sqlalchemy-tickets] Issue #3465: using version_id_col & version_id_generator: False makes SQLAlchemy emit bad SQL on relationship update (zzzeek/sqlalchemy)

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

New issue 3465: using version_id_col & version_id_generator: False makes SQLAlchemy emit bad SQL on relationship update
https://bitbucket.org/zzzeek/sqlalchemy/issue/3465/using-version_id_col-version_id_generator

tonymillion:

I have a model as such (in this case I'm using Flask-SQLAlchemy, but thats not relevant to the error):
```
class Person(BaseModel):
    <snip - stuff for model, id as primary key etc>
    purchase_uuid = db.Column(postgresql.UUID(as_uuid=True), default=uuid.uuid4)

    __mapper_args__ = {
        'version_id_col': purchase_uuid,
        'version_id_generator': False
    }
```
this works fine i.e. we can block a certain `UPDATES` unless purchase_uuid is being updated (taken from http://docs.sqlalchemy.org/en/latest/orm/versioning.html#programmatic-or-conditional-version-counters )

We also have another model

```
class Session(BaseModel):
    <snip - stuff for model, id as primary key etc>

    # the person who is represented by this Session
    person_id = db.Column(db.Integer, db.ForeignKey("people.id"), index=True)

    person = db.relationship("Person",
                             # backref=db.backref("sessions", lazy="dynamic"),
                             lazy="joined",
                             uselist=False,
                             foreign_keys=[person_id])
```

When I uncomment the backref and then create a session object and assign a person:

```
    session = Session()
    session.person = person
    db.session.add(session)
    db.session.commit()
```

I get the following:

```
ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near "WHERE"
LINE 1: UPDATE people SET  WHERE people.id = 1 AND people.purchase_u...
                           ^
 [SQL: 'UPDATE people SET  WHERE people.id = %(people_id)s AND people.purchase_uuid = %(people_purchase_uuid)s RETURNING people.purchase_uuid'] [parameters: {'people_id': 1, 'people_purchase_uuid': UUID('e3a73745-0979-42e3-90c5-72e6a32219c7')}]
```

As you can see SQLAlchemy is outputting some invalid SQL i.e. `UPDATE people SET  WHERE`

If I comment out either the backref (the `sessions` relationship) or the `__mapper_args__` the problem goes away.

[Sqlalchemy-tickets] Issue #3464: varbinary types fail w/ mssql + pyodbc + NULL (zzzeek/sqlalchemy)

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

New issue 3464: varbinary types fail w/ mssql + pyodbc + NULL
https://bitbucket.org/zzzeek/sqlalchemy/issue/3464/varbinary-types-fail-w-mssql-pyodbc-null

Mike Bayer:

inserting a NULL into a VARBINARY requires that pyodbc.BinaryNull be used.  mxodbc probably has something similar.   The tests have been failing for a long time for this type since we added VARBINARY as a default type for LargeBinary in #3039.  which makes this a regression since 0.9 isn't doing that.

[Sqlalchemy-tickets] Issue #3463: RowProxy and ResultProxy should implement complete standard interfaces (zzzeek/sqlalchemy)

[Javier D. C. <iss...@bi...>]

New issue 3463: RowProxy and ResultProxy should implement complete standard interfaces
https://bitbucket.org/zzzeek/sqlalchemy/issue/3463/rowproxy-and-resultproxy-should-implement

Javier Domingo Cansino:

I have been using sqlalchemy core and I have found that in RowProxy and ResultProxy, standard interfaces are implemented (and even recommended), but not totally, confusing users (like me).

Although these interfaces implement some of the methods to treat the results as tuples or dicts, they don't implement the ones that give the actual support for these datatypes.

The implementation of standard basetype interfaces should be done using ABCs: https://docs.python.org/3/library/collections.abc.html

In my case, .get() was the function that I expected to be supported (given the dict access they have) but wasn't working, so I switched to a getattr() call instead.

[Sqlalchemy-tickets] Issue #3462: new single inh polymorphic identity logic in query should skip if there's no actual identity (zzzeek/sqlalchemy)

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

New issue 3462: new single inh polymorphic identity logic in query should skip if there's no actual identity
https://bitbucket.org/zzzeek/sqlalchemy/issue/3462/new-single-inh-polymorphic-identity-logic

Mike Bayer:

e.g.


```
#!python

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

Base = declarative_base()


class Client(Base):
    __tablename__ = 'client'
    id = Column(Integer, primary_key=True)


class SubClient(Client):
    pass


class Thing(Base):
    __tablename__ = 'thing'
    id = Column(Integer, primary_key=True)
    cid = Column(ForeignKey('client.id'))

s = Session()

print s.query(Thing).join(SubClient, Thing.cid == SubClient.id)


```

produces:

```
#!sql


SELECT thing.id AS thing_id, thing.cid AS thing_cid 
FROM thing JOIN client ON thing.cid = client.id AND NULL
```

[Sqlalchemy-tickets] Issue #3461: Autoloading table from database causes ~1 minute delay when shutting down PostgreSQL (zzzeek/sqlalchemy)

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

New issue 3461: Autoloading table from database causes ~1 minute delay when shutting down PostgreSQL
https://bitbucket.org/zzzeek/sqlalchemy/issue/3461/autoloading-table-from-database-causes-1

Jurie Horneman:

When I connect to a PostgreSQL database and autoload a table, and do nothing else, shutting down PostgreSQL hangs for about a minute, unless I call engine.dispose() before doing so.

I'm not sure if this is a bug or something that I missed in the documentation. I only found out yesterday by trial and error how to solve this, and it wasn't clear from me from the docs that I needed to do this.

My environment:

* Windows 7 Professional SP1
* Python 2.7.8 64 bit
* PostgreSQL 9.3.3
* SQLAlchemy==1.0.5
* psycopg2==2.6.1

(Also happens with:

* SQLAlchemy==0.9.3
* psycopg2==2.5.3)

Repro code:


```
#!python

import logging
from sqlalchemy import *


logger = logging.getLogger(__name__)


def repro(_host, _port, _username, _password, _dbname, _table_name):
    """Reproduce slow PostgreSQL shutdown issue.

    Keyword arguments:
    _host -- The host address of the PostgreSQL database server.
    _port -- The port used by the PostgreSQL database server.
    _username -- The username to be used with the PostgreSQL database server.
    _password -- The password for the PostgreSQL database server.
    _dbname -- The name of the database in the PostgreSQL database server.
    _table_name -- The name of any table in the database.
    """

    # Maximum logging.
    
    # See http://docs.sqlalchemy.org/en/rel_0_9/core/engines.html#configuring-logging.
    # "set to logging.INFO for SQL query output"
    logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO)
    # "set to logging.INFO or lower to log connection pool checkouts/checkins."
    logging.getLogger("sqlalchemy.pool").setLevel(logging.INFO)
    # "set to logging.INFO for information on mapper configurations."
    logging.getLogger("sqlalchemy.orm").setLevel(logging.INFO)
    # See http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html.
    # "The psycopg2 dialect will log Postgresql NOTICE messages via the
    #  sqlalchemy.dialects.postgresql logger"
    logging.getLogger("sqlalchemy.dialects.postgresql").setLevel(logging.INFO)

    # Create engine for the indended database.
    engine = _create_engine(_host, _port, _username, _password, _dbname, True)

    # Load tables from the database.
    metadata = MetaData(bind=engine)
    a_table = Table(_table_name, metadata, autoload=True)

    # With the following line disabled, shutting down PostgreSQL will take about 1 minute longer.
    # engine.dispose()


def _build_database_URI(_host, _port, _username, _password, _dbname):
    return "postgresql+psycopg2://{user}:{password}@{host}:{port}/{dbname}".\
        format(user=_username, password=_password, host=_host, port=_port, dbname=_dbname)

def _create_engine(_host, _port, _username, _password, _dbname, _debug):
    return create_engine(_build_database_URI(_host, _port, _username, _password, _dbname), echo=_debug, echo_pool=_debug)


if __name__ == "__main__":
    # Set up logging.
    console_handler = logging.StreamHandler()
    console_handler.setLevel(logging.INFO)
    console_handler.setFormatter(logging.Formatter('%(message)s'))

    root_logger = logging.getLogger("")
    root_logger.setLevel(logging.DEBUG)
    root_logger.addHandler(console_handler)

    logger.info("Connecting to the database...")
    repro(
        _host = "localhost",
        _port = 5439,
        _username = "<user>",
        _password = "<password>",
        _dbname = "<db name>",
        _table_name ="<any table name>"        
    )

    logger.info("Connected to the database. Try stopping the PostgreSQL server.")

    raw_input("Press enter to stop...")

```

Repro steps:

* Start PostgreSQL server.
* Start Python code above.
* Shut down PostgreSQL server.

Stopping the PostgreSQL server takes about a minute (and may fail).

Enable the engine.dispose() line, repeat, shutting down PostgreSQL takes under a second.

My use case is unusual: I have a Windows executable that starts and stops its own local PostgreSQL server.

I can sorta see in retrospect that the autoloading does something that stops the shutdown from proceeding smoothly, but overall this behavior was surprising to me.

The documentation of engine.dispose() does not indicate to me that this is something I need to call to enable a smooth PostgreSQL shutdown. I also couldn't find other obvious "here's how to shut down bits of SQLAlchemy" advice in the documentation.

Once I had more or less accidentally reduced the problem to these few lines, I found this solution by going through the APIs of Engine, Metadata, and Table, and trying to call everything that sounded like it should be called during shutdown.

[Sqlalchemy-tickets] Issue #3460: relatioship behaviour (zzzeek/sqlalchemy)

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

New issue 3460: relatioship behaviour
https://bitbucket.org/zzzeek/sqlalchemy/issue/3460/relatioship-behaviour

Leonardo Rossi:

This is my code:

```python
from sqlalchemy import Column, Integer, Text, and_, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker

Base = declarative_base()


class Mytable(Base):

    __tablename__ = 'mytable'
    id = Column(Integer(), primary_key=True, autoincrement=True)
    name = Column(Text(), nullable=False)


class ConnectedTable(Base):

    __tablename__ = 'connectedtable'
    id = Column(Integer(), primary_key=True, autoincrement=True)
    id_mytable = Column(Integer(), nullable=True, index=True)
    connection = relationship(
        Mytable,
        backref='mytable',
        primaryjoin=and_(
            Mytable.id == id_mytable,
            id_mytable != -1,
            id_mytable is not None
        ),
        foreign_keys=Mytable.id,
        uselist=False,
    )


e = create_engine("mysql://root:@localhost/invenio", echo=True)
Base.metadata.create_all(e)
Session = sessionmaker(bind=e)
session = Session()

mytable_1 = Mytable(name="fuu")
mytable_2 = Mytable(name="bar")

session.add(mytable_1)
session.add(mytable_2)

session.commit()

conn_1 = ConnectedTable(connection=mytable_1)
conn_2 = ConnectedTable(id_mytable=mytable_2.id)
conn_3 = ConnectedTable(id_mytable=-1)

session.add(conn_1)
session.add(conn_2)
session.add(conn_3)

session.commit()
```

The commit generate these queries:

```sql
INSERT INTO mytable (name) VALUES ('fuu')
INSERT INTO mytable (name) VALUES ('bar')
COMMIT
INSERT INTO connectedtable (id_mytable) VALUES (None)
INSERT INTO connectedtable (id_mytable) VALUES (2)
INSERT INTO connectedtable (id_mytable) VALUES (-1)
UPDATE mytable SET id=%s WHERE mytable.id = (None, 1)
```

Genereting the error `sqlalchemy.orm.exc.FlushError: Instance <Mytable at 0x7fbb55fbcd90> has a NULL identity key.`

Questions:

1. Why `ConnectedTable(connection=mytable_1)` generate insert a record (id_mytable=None) instead of (id_mytable=1)?
2. Why generate also a UPDATE?

[Sqlalchemy-tickets] Issue #3459: Add .cast shortcut method (zzzeek/sqlalchemy)

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

New issue 3459: Add .cast shortcut method
https://bitbucket.org/zzzeek/sqlalchemy/issue/3459/add-cast-shortcut-method

Sebastian Bank:

I often find myself expecting columns/expressions/orm attributes to have a `.cast(type_)` shortcut method (just as they provide `.label`, `.desc`, `.op`, etc.), such that one could write `SELECT CAST(eggs AS text) FROM SPAM` as

```
from sqlalchemy import select, Text

eggs_as_text = select([spam.c.eggs.cast(Text)])
```

(cf. postgres `SELECT eggs::text FROM spam`) instead of

```
from sqlalchemy import select, cast, Text

eggs_as_text = select([cast(spam.c.eggs, Text)])
```

With the recent addition of a [cast method](http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONElement.cast) on `postgresql.JSONElement`, this might be a good occasion to add it (API consistency).

In a quick test, this wasn't more than two lines in `sqlalchemy.sql.elements.ColumnElement`, but maybe I forgot some edge cases.

[Sqlalchemy-tickets] Issue #3458: super-self-referential m2m joins need to use annotations (zzzeek/sqlalchemy)

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

New issue 3458: super-self-referential m2m joins need to use annotations
https://bitbucket.org/zzzeek/sqlalchemy/issue/3458/super-self-referential-m2m-joins-need-to

Mike Bayer:

e.g. "node.id == node.id" essentially, one side is on the secondary, we need to make use of remote() annotations on "secondary" for this case.  just need to re-review how the annotation/join thing works and add it here.


```
#!python

from sqlalchemy import (
    Column,
    Integer,
    and_,
    create_engine,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import joinedload
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import select


Base = declarative_base()


class Node(Base):
    __tablename__ = 'node'

    id = Column(Integer, primary_key=True, nullable=False)

    left = Column(Integer, nullable=False)
    right = Column(Integer, nullable=False)

    def __repr__(self):
        return "<Node {self.id}>".format(self=self)

_node_parent = Node.__table__.alias()
_node_child = Node.__table__.alias()
_node_bridge = (
    select([_node_child.c.id.label('child_id'), _node_parent.c.id.label('parent_id')])
    .where(and_(
        _node_parent.c.left < _node_child.c.left,
        _node_child.c.right < _node_parent.c.right,
    ))
    .order_by(_node_child.c.id, _node_parent.c.left)
    .distinct(_node_child.c.id).alias()
)
Node.parent = relationship(
    Node,
    secondary=_node_bridge,
    primaryjoin=Node.id == _node_bridge.c.child_id,
    secondaryjoin=_node_bridge.c.parent_id == Node.id,
    viewonly=True,
    uselist=False
)


engine = create_engine('postgresql://scott:tiger@localhost/test', echo=True)
Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)
session = sessionmaker(bind=engine)()

session.query(Node).delete()

node1 = Node(id=1, left=1, right=4)
node2 = Node(id=2, left=2, right=3)
node3 = Node(id=3, left=5, right=6)
session.add_all([node1, node2, node3])
session.commit()


# Note that the bridge table itself has the correct results -- the only
# parent/child link is that 1 is the parent of 2.
# Output:
# (2, 1)
for row in session.execute(_node_bridge):
    print(row)


# PROBLEM 1
# Output:
# <Node 1> <Node 1>
# <Node 2> <Node 1>
# <Node 3> <Node 1>
# This is incorrect -- node 3 has no parent.
# Generated SQL looks like:
#   SELECT node.id AS node_id, node."left" AS node_left, node."right" AS node_right, node_1.id AS node_1_id, node_1."left" AS node_1_left, node_1."right" AS node_1_right
#   FROM node
#   LEFT OUTER JOIN (
#       (
#           SELECT DISTINCT ON (node_2.id) node_2.id AS child_id, node_3.id AS parent_id
#           FROM node AS node_2, node AS node_3
#           WHERE node_3."left" < node_2."left" AND node_2."right" < node_3."right"
#           ORDER BY node_2.id, node_3."left"
#       ) AS anon_1
#       -- why are we joining node a second time here?
#       JOIN node AS node_1 ON anon_1.parent_id = node_1.id
#   -- what on earth is up with this ON clause?
#   ) ON anon_1.child_id = anon_1.child_id
q = (
    session.query(Node)
    .options(
        joinedload(Node.parent)
    )
    .all()
)
for node in q:
    print(node, node.parent)


```