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

[Sqlalchemy-tickets] Issue #3457: modernize adapt_like_to_iterable (zzzeek/sqlalchemy)

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

New issue 3457: modernize adapt_like_to_iterable
https://bitbucket.org/zzzeek/sqlalchemy/issue/3457/modernize-adapt_like_to_iterable

Mike Bayer:

a lot of time is wasted here duck typing the object's current collection, when this is a fixed value.   this is run on every list assignment so this should be fixed up.   there is also the notion that if the target collection falls under the category of list or set, the incoming object would be checked only that it is an iterable, that is #3456, however that is a more controversial change.

[Sqlalchemy-tickets] Issue #3456: Cannot initialize or set relations with tuples (zzzeek/sqlalchemy)

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

New issue 3456: Cannot initialize or set relations with tuples
https://bitbucket.org/zzzeek/sqlalchemy/issue/3456/cannot-initialize-or-set-relations-with

dwt:

In our applications we like to use immutable types where not otherwise needed, just as a default to prevent bugs where you get a collection set on your object and then it changes without you noticing because it's actually a shared value and somebody else still thinks he owns it.

Since these bugs are very hard to spot, we like to use tuple() over list() to alleviate that concern. SQLA however insists on list() or list-like structures for initializing or set to many relations.

Since it already does quite  a lot to adapt whatever type is incoming that might be iterable in any sensible way - can't it not also support tuples?

We're seeing this crop up (using declarative) in object initialization in code like ``User(groups=(group1, group2))`` or ``user.groups = (group1, group2)``

As far as I can see ``adapt_like_to_iterable`` seems to be the culprit here as it only accepts the mutable variants of the types.

[Sqlalchemy-tickets] Issue #3455: add postgresql storage parameters (zzzeek/sqlalchemy)

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

New issue 3455: add postgresql storage parameters
https://bitbucket.org/zzzeek/sqlalchemy/issue/3455/add-postgresql-storage-parameters

Mike Bayer:

https://github.com/zzzeek/sqlalchemy/pull/179/

[Sqlalchemy-tickets] Issue #3454: Can't use a CAST in a PostgreSQL ExcludeConstraint (zzzeek/sqlalchemy)

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

New issue 3454: Can't use a CAST in a PostgreSQL ExcludeConstraint
https://bitbucket.org/zzzeek/sqlalchemy/issue/3454/cant-use-a-cast-in-a-postgresql

vr2262:

In PostgreSQL I can create a table with an exclusion constraint involving a `CAST` (the `CAST` is necessary because the `UUID` type doesn't have a default operator class for `gist`):

```
#!sql
CREATE EXTENSION btree_gist;
CREATE TABLE example (
  id         UUID,
  some_range INT4RANGE,
  EXCLUDE USING gist (CAST("id" AS TEXT) WITH =, some_range WITH &&)
);
```

When I try to do the same in SQLAlchemy:

```
#!python
from sqlalchemy import *
from sqlalchemy.dialects.postgresql import (
    UUID, INT4RANGE, ExcludeConstraint, TEXT
)

class Example(Base):
    __tablename__ = 'example'
    id = Column(UUID)
    some_range = Column(INT4RANGE)
    __table_args__ = (
        ExcludeConstraint(
            (cast('id', TEXT), '='), ('some_range', '&&')
        ),
    )
```

I get the `error sqlalchemy.exc.ArgumentError: Can't add unnamed column to column collection`.

`('id::TEXT', '=')` doesn't work because SQLAlchemy doesn't recognize `'id::TEXT'` as a column.

[Sqlalchemy-tickets] Issue #3453: custom sequency not attached to primary key for PostgreSQL (zzzeek/sqlalchemy)

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

New issue 3453: custom sequency not attached to primary key for PostgreSQL
https://bitbucket.org/zzzeek/sqlalchemy/issue/3453/custom-sequency-not-attached-to-primary

Gijs Molenaar:

When I supply a primary key a custom Sequence the sequence is created but not attached to the primary key column. Tested only with PostgreSQL and SQLAlchemy 1.0.5

example code
```
#!python


from sqlalchemy import Column,Integer, Sequence
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine

Base = declarative_base()


class TestCustomSeq(Base):
    __tablename__ = 'testseq'
    id = Column(Integer, Sequence('customseq'), primary_key=True)

   
alchemy_engine = create_engine('postgresql://gijs:gijs@localhost:5433/gijs', echo=True)
Base.metadata.create_all(alchemy_engine)

```

generated sql:


```
#!sql

CREATE SEQUENCE customseq
COMMIT;
CREATE TABLE testseq (
	id INTEGER NOT NULL, 
	PRIMARY KEY (id)
);
```

[Sqlalchemy-tickets] Issue #3452: relationship() defined in child class does not work as expected. (zzzeek/sqlalchemy)

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

New issue 3452: relationship() defined in child class does not work as expected.
https://bitbucket.org/zzzeek/sqlalchemy/issue/3452/relationship-defined-in-child-class-does

Unknown Name:

This maybe a features, rather than a bug. However, I was stuck with a strange problem with merge() that had me stumped for a long time as the issue was obfuscated by the use of the get_one_or_create() function from [http://skien.cc/blog/2014/01/15/sqlalchemy-and-race-conditions-implementing/](http://skien.cc/blog/2014/01/15/sqlalchemy-and-race-conditions-implementing/) which I had modified to use the created = session.modify(created) if a boolean was passed to it. Otherwise it would just add() the created object to the session. 

I've pin pointed the core problem to creating a relationship in the child class, rather than the parent class. As detailed in the "Merge tip" section of [http://docs.sqlalchemy.org/en/latest/orm/session_state_management.html#merge-tips](http://docs.sqlalchemy.org/en/latest/orm/session_state_management.html#merge-tips) by default, adding an object that is already in the session to a new object that you wish to merge() will result in the error:

New instance Y with identity key X conflicts with persistent instance Z

As stated in the docs, adding the property "cascade_backrefs=False" should resolve the problem. Which it does if the relationship() is defined in the parent and not the child. However, if you define the relationship in the child, then this directive is ignored and the error above is present.

Using the "Many to one" example given in "[http://docs.sqlalchemy.org/en/rel_1_0/orm/basic_relationships.html#many-to-one](http://docs.sqlalchemy.org/en/rel_1_0/orm/basic_relationships.html#many-to-one)" as a basis. 
Instead of defining the relationship between the User (parent) and the Address (child) using the relationship() function in the User class, I defined it in the Address class.


```
#!python

from sqlalchemy import *
from sqlalchemy.orm import *

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base();

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    password = Column(String(12))

    # This works as described in http://docs.sqlalchemy.org/en/rel_1_0/orm/basic_relationships.html#many-to-one
    #addresses = relationship("Address", backref=backref('user', order_by=id), cascade_backrefs=False)
    

    def __repr__(self):
        return "<User(id='{0}', name='{1}', fullname='{2}', password='{3}')>".format(
                        self.id, self.name, self.fullname, self.password)


class Address(Base):
    __tablename__ = 'addresses'
    
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))
    comment = Column(String)
    
    # This does not works as expected.
    user = relationship("User", backref=backref('addresses', order_by=id), cascade_backrefs=False)
    
    def __repr__(self):
        return "<Address(id='{0}', email_address='{1}', user='{2}', comment='{3}')>".format(self.id, self.email_address, self.user, self.comment)
    

engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)

session = sessionmaker(bind=engine)()


print '## Create new objects ##'
user1 = User()
user1.name = 'Fred'
user1.fullname = 'Fred A'
user1.password = 'abc123'

user1 = session.merge(user1)

print 'commit()'
session.commit()

address1 = Address()
address1.user = user1
address1.email_address = 'us...@ab...'
address1.comment = 'hello world'

address1 = session.merge(address1)

print 'commit()'
session.commit()


print '## New Objects in session. ##'
print user1
print address1


print '## Updating Address ##'

address1_update = Address()
address1_update.id = address1.id
address1_update.user = user1 # Error due to assignment of object already in session.
address1_update.email_address = 'us...@ab...'
address1_update.comment = 'hello world'

address1 = session.merge(address1_update)

print 'commit()'
session.commit()

print '## Updated Objects in session ##'
print user1
print address1
```
The output is:


```
#!bash

## Updating Address ##

<<SNIP>>

Traceback (most recent call last):
  File "example_declarative_relationship_1tomany_childside.py", line 106, in <module>
    address1 = session.merge(address1_update)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/session.py", line 1689, in merge
    self._autoflush()
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/session.py", line 1282, in _autoflush
    self.flush()
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/session.py", line 2004, in flush
    self._flush(objects)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/session.py", line 2122, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/session.py", line 2086, in _flush
    flush_context.execute()
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/unitofwork.py", line 373, in execute
    rec.execute(self)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/unitofwork.py", line 532, in execute
    uow
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/persistence.py", line 149, in save_obj
    base_mapper, states, uowtransaction
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/persistence.py", line 301, in _organize_states_for_save
    state_str(existing)))
sqlalchemy.orm.exc.FlushError: New instance <Address at 0x2323090> with identity key (<class '__main__.Address'>, (1,)) conflicts with persistent instance <Address at 0x224f690>
```

>From the documentation ([http://docs.sqlalchemy.org/en/rel_1_0/orm/backref.html](http://docs.sqlalchemy.org/en/rel_1_0/orm/backref.html)), it says that defining the relationship using backref is a shortcut for defining the bi-directional relationship between parent and child. Hence, I assumed that it should also work in reverse.

Other info:

Python and module versions

```
#!python

$ /usr/bin/python
Python 2.6.6 (r266:84292, Jan 22 2014, 09:42:36)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.0.5'

```

[Sqlalchemy-tickets] Issue #3451: bulk update adds the PK to the SET clause; no tests (zzzeek/sqlalchemy)

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

New issue 3451: bulk update adds the PK to the SET clause; no tests
https://bitbucket.org/zzzeek/sqlalchemy/issue/3451/bulk-update-adds-the-pk-to-the-set-clause

Mike Bayer:


```
#!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)
    data = Column(String)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add(A(id=1, data='foo'))
s.commit()


s.bulk_update_mappings(
    A, [{'id': 1, 'data': 'bar'}]
)

```


the UPDATE statement is:


```
#!

UPDATE a SET id=?, data=? WHERE a.id = ?
(1, 'bar', 1)
```

[Sqlalchemy-tickets] Issue #3450: Accessing iterator on a query that has an error shows as not iterable (zzzeek/sqlalchemy)

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

New issue 3450: Accessing iterator on a query that has an error shows as not iterable
https://bitbucket.org/zzzeek/sqlalchemy/issue/3450/accessing-iterator-on-a-query-that-has-an

Michael Brown:

Found on 1.0.3 and 1.0.4

I have a query that when evaluated throws a SQL error, except if the iterator is used. If the iterator is used then it shows as not iterable:

Normal:
```
In [66]: q = session.query(m.Panel).filter(m.Panel.ptype=='blood')

In [67]: map(lambda x: x, q)
Out[67]: 
[Panel(ptype='blood', id=74, name='bottle'),
 Panel(ptype='blood', id=75, name='space shuttle'),
 Panel(ptype='blood', id=76, name='russian'),
…
```

Failing:
```
In [68]: q = session.query(m.Panel).filter(m.Panel.ptype=='bloody')

In [69]: next(q)
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
panel.py in <module>()
----> 1 next(q)

TypeError: Query object is not an iterator
```

What I think *should* happen is that the underlying error should be thrown like so when the iterator gets accessed (note the Query still shows as Iterable):

```
In [70]: q.first()
---------------------------------------------------------------------------
DataError                                 Traceback (most recent call last)
…
DataError: (psycopg2.DataError) invalid input value for enum panel_type: "bloody"
```

[Sqlalchemy-tickets] Issue #3449: automap_base fails on my database (zzzeek/sqlalchemy)

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

New issue 3449: automap_base fails on my database
https://bitbucket.org/zzzeek/sqlalchemy/issue/3449/automap_base-fails-on-my-database

yoch:

Hi,

I use 1.0.5 version, but this bug occurs also with 0.9.8.

Here a simple test-case to reproduce the bug :

```
#!SQL
CREATE TABLE `hardware` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `hdw_type` varchar(15) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `hdw_type` (`hdw_type`),
  CONSTRAINT `hardware_ibfk_1` FOREIGN KEY (`hdw_type`) 
    REFERENCES `hdw_type` (`type`) 
    ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS `hdw_type` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(15) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `hdw_type_UNIQUE` (`type` ASC)
);
```

This simple code snippet fail :

```
#!python
Base = automap_base()
Base.prepare(engine, reflect=True)
```

Traceback :

```
#!console
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/automap.py", line 795, in prepare
    map_config.map()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/declarative/base.py", line 593, in map
    return super(_DeferredMapperConfig, self).map()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/declarative/base.py", line 530, in map
    **self.mapper_args
  File "<string>", line 2, in mapper
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/mapper.py", line 627, in __init__
    self._configure_properties()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/mapper.py", line 1318, in _configure_properties
    setparent=True)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/mapper.py", line 1525, in _configure_property
    prop = self._property_from_column(key, prop)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/mapper.py", line 1693, in _property_from_column
    (key, self, column.key, prop))
sqlalchemy.exc.ArgumentError: WARNING: when configuring property 'hdw_type' on Mapper|hardware|hardware, column 'hdw_type' conflicts with property '<RelationshipProperty at 0xfc0bb0; hdw_type>'. To resolve this, map the column to the class under a different name in the 'properties' dictionary.  Or, to remove all awareness of the column entirely (including its availability as a foreign key), use the 'include_properties' or 'exclude_properties' mapper arguments to control specifically which table columns get mapped.
```

I think this is related to my columns/indexes naming.

Thank you

[Sqlalchemy-tickets] Issue #3448: row_processor is None when querying with hybrid_property (zzzeek/sqlalchemy)

[Richard F. <iss...@bi...>]

New issue 3448: row_processor is None when querying with hybrid_property
https://bitbucket.org/zzzeek/sqlalchemy/issue/3448/row_processor-is-none-when-querying-with

Richard Frank:

I'm using a hybrid_property to map a column containing int identifiers to the un-persisted python objects they identify.  We're writing queries that compare that column with those python objects. In this example, I'm mapping an int "id" column to a python IdObject wrapper.  

In SQLAlchemy 0.9.9, this example ran with 1 row being returned, but in SQLAlchemy 1.0.5, I get a TypeError (traceback pasted below).  Is there a better (working) way to do this?

```
#!python
from sqlalchemy import Table, Column, Integer, MetaData, create_engine
from sqlalchemy.ext.hybrid import hybrid_property, Comparator
from sqlalchemy.orm import mapper, Query, Session


class Entity(object):
    pass


class IdObject(object):
    def __init__(self, id_):
        self.id = id_

    def __cmp__(self, other):
        if isinstance(other, int):
            return cmp(self.id, other)
        return cmp(self.id, other.id)


class IdObjComparator(Comparator):
    def __init__(self, id_):
        if isinstance(id_, (IdObject, IdObjComparator)):
            self.id = id_.id
        else:
            self.id = id_

    def operate(self, op, *other, **kwargs):
        return op(self.id,
                  *(IdObjComparator(id_).id
                    for id_ in other),
                  **kwargs)

    def __clause_element__(self):
        return self.id


def test_hybrid_property(url):
    engine = create_engine(url)
    metadata = MetaData(bind=engine)
    table = Table('test_tbl', metadata,
                  Column('id', Integer, primary_key=True),
                  Column('value', Integer))

    metadata.create_all()

    engine.execute(table.insert().values([{'value': 1}]))

    mapper(Entity,
           table,
           properties={'_id': table.c.id},
           include_properties=())

    hybrid_id = hybrid_property(
        lambda zelf: IdObject(zelf._id)
    ).comparator(lambda klass: IdObjComparator(klass._id))

    Entity.id = hybrid_id

    query_for_id1 = (Query([Entity.id])
                     .filter(Entity.id == IdObject(1))
                     .order_by(Entity.id))

    results = list(query_for_id1.with_session(Session(bind=engine)))
    assert len(results) == 1


if __name__ == '__main__':
    test_hybrid_property('postgresql://rich@/test_hybrid_prop')

```

```
Traceback (most recent call last):
  File "/Users/rich/Dev/test/test/test_hybrid_property.py", line 63, in <module>
    test_hybrid_property('postgresql://rich@/test_hybrid_prop')
  File "/Users/rich/Dev/test/test/test_hybrid_property.py", line 58, in test_hybrid_property
    results = list(query_for_id1.with_session(Session(bind=engine)))
  File "/Users/rich/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 84, in instances
    util.raise_from_cause(err)
  File "/Users/rich/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/Users/rich/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 72, in instances
    for row in fetch]
TypeError: 'NoneType' object is not callable
```

[Sqlalchemy-tickets] Issue #3447: Unable to install sqlalchemy (zzzeek/sqlalchemy)

[kracekumar r. <iss...@bi...>]

New issue 3447: Unable to install sqlalchemy
https://bitbucket.org/zzzeek/sqlalchemy/issue/3447/unable-to-install-sqlalchemy

kracekumar ramaraju:

```
➜  ~  sudo pip install sqlalchemy
Collecting sqlalchemy
  Downloading SQLAlchemy-1.0.5.tar.gz (4.6MB)
    9% |███                             | 442kB 393bytes/s eta 2:56:04
  Hash of the package https://pypi.python.org/packages/source/S/SQLAlchemy/SQLAlchemy-1.0.5.tar.gz#md5=e9e54eb24b148d065e42e0826523657a (from https://pypi.python.org/simple/sqlalchemy/) (941496e19e5ca0699d152adcd0f18d83) doesn't match the expected hash e9e54eb24b148d065e42e0826523657a!
  Bad md5 hash for package https://pypi.python.org/packages/source/S/SQLAlchemy/SQLAlchemy-1.0.5.tar.gz#md5=e9e54eb24b148d065e42e0826523657a (from https://pypi.python.org/simple/sqlalchemy/)
➜  ~  pip --version
pip 6.1.1 from /Library/Python/2.7/site-packages (python 2.7)
(switch_api_pypy)➜  switch_api  pip install sqlalchemy
You are using pip version 6.1.1, however version 7.0.3 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.
Collecting sqlalchemy
  Using cached SQLAlchemy-1.0.5.tar.gz
  Hash of the package https://pypi.python.org/packages/source/S/SQLAlchemy/SQLAlchemy-1.0.5.tar.gz#md5=e9e54eb24b148d065e42e0826523657a (from https://pypi.python.org/simple/sqlalchemy/) (955de2f2b4aefd1ccf14279584388604) doesn't match the expected hash e9e54eb24b148d065e42e0826523657a!
  Bad md5 hash for package https://pypi.python.org/packages/source/S/SQLAlchemy/SQLAlchemy-1.0.5.tar.gz#md5=e9e54eb24b148d065e42e0826523657a (from https://pypi.python.org/simple/sqlalchemy/)
(switch_api_pypy)➜  ~  pip --version
pip 6.1.1 from /Users/krace/.virtualenvs/switch_api_pypy/site-packages (python 2.7)
```

[Sqlalchemy-tickets] Issue #3446: Regression: Date takes no parameters (zzzeek/sqlalchemy)

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

New issue 3446: Regression: Date takes no parameters
https://bitbucket.org/zzzeek/sqlalchemy/issue/3446/regression-date-takes-no-parameters

Thomas Beiganz:

In Version 0.8.3 you could format Date with storage_format


```
#!python

 Date(storage_format="%(day)02d.%(month)02d.%(year)04d")
```

In 1.0.5 this is no longer possible


```
#!python

    Column('timestamp', Date(storage_format="%(day)02d.%(month)02d.%(year)04d"), primary_key=True),
TypeError: object() takes no parameters
```

[Sqlalchemy-tickets] Issue #3445: column_property ignores aliased (zzzeek/sqlalchemy)

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

New issue 3445: column_property ignores aliased
https://bitbucket.org/zzzeek/sqlalchemy/issue/3445/column_property-ignores-aliased

Lukas Siemon:

The test case below generates the following query:

```
#!sql
SELECT
   venue.id AS venue_id   
FROM
   venue   
WHERE
   EXISTS (
      SELECT
         1   
      FROM
         label,
         venue AS venue_alias2 
      JOIN
         venue_to_label AS venue_to_label_1 
            ON venue_alias2.id = venue_to_label_1.venue_id 
      JOIN
         label AS label_alias 
            ON label_alias.id = venue_to_label_1.label_id   
      WHERE
         venue.id = venue_alias2.id 
         AND (
            label.id = :param_1
         ) IS 1
   )
```

However I'd expect the subquery to use the aliased Label model, i.e. instead of "label.id = :param_1" it should say "label_alias.id = :param_1".

The problem seems to be with the column_property.

I've tested the issue with
SQLAlchemy==1.0.5
Flask==0.10.1
Flask-SQLAlchemy==2.0

Minimal test case:
```
#!python

import unittest
from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, aliased, column_property
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

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

Base = declarative_base()

venue_to_label = Table(
    'venue_to_label', db.metadata,
    Column('venue_id', Integer, ForeignKey('venue.id'), primary_key=True),
    Column('label_id', Integer, ForeignKey('label.id'), primary_key=True)
)
label_to_label = Table(
    'label_to_label', db.metadata,
    Column('label_id', Integer, ForeignKey('label.id'), primary_key=True),
    Column('label_id', Integer, ForeignKey('label.id'), primary_key=True)
)


class Label(db.Model):
    __tablename__ = 'label'
    id = Column(Integer, primary_key=True, nullable=False)
    is_first = column_property(id == 1)


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

Base.metadata.drop_all(bind=db.engine)
Base.metadata.create_all(bind=db.engine)


class TestColPropAliasBug(unittest.TestCase):

    def test_column_property_aliased_bug(self):

        query = db.session.query(Venue)

        venue_alias2 = aliased(Venue, name="venue_alias2")
        subquery = db.session.query(venue_alias2)
        label_alias = aliased(venue_alias2.labels, name="label_alias")
        subquery = subquery.join(label_alias, venue_alias2.labels)

        # relate queries
        subquery = subquery.filter(Venue.id == venue_alias2.id)

        # filter_ = label_alias.id.is_(1)  # works as expected
        filter_ = label_alias.is_first.is_(True)  # doesn't work as expected
        subquery = subquery.filter(filter_)
        query = query.filter(subquery.exists())

        print query

```

[Sqlalchemy-tickets] Issue #3444: sqlalchemy does not emit server_onupdate ON UPDATE clause with MySQL (zzzeek/sqlalchemy)

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

New issue 3444: sqlalchemy does not emit server_onupdate ON UPDATE clause with MySQL
https://bitbucket.org/zzzeek/sqlalchemy/issue/3444/sqlalchemy-does-not-emit-server_onupdate

Charles-Axel Dein:

This seems similar to #3155 and #2631.

I have this simple script to reproduce the problem:

```
from sqlalchemy import create_engine
from sqlalchemy import func
from sqlalchemy.dialects.mysql import DATETIME
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import Column
from sqlalchemy.types import Integer, DateTime

Base = declarative_base()


class Timestamp(Base):

    __tablename__ = 'timestamps'

    id = Column(Integer(), primary_key=True)
    created_at = Column(DateTime(), nullable=False,
                        server_default=func.current_timestamp())
    updated_at = Column(DateTime(), nullable=False,
                        server_default=func.current_timestamp(),
                        server_onupdate=func.current_timestamp())


if __name__ == '__main__':
    engine = create_engine('mysql://root:root@localhost/test', echo=True)
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
```

The SQL that sqlalchemy generates does not have the `ON UPDATE` clause:

```
CREATE TABLE timestamps (
	id INTEGER NOT NULL AUTO_INCREMENT,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id)
)
```

A `SHOW CREATE TABLE` query confirms that fact:

```
CREATE TABLE `timestamps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
```

I'm not 100% sure I'm not causing the problem or have fully understood the two similar tickets. I'll read about it and follow up on this thread if I find something.

[Sqlalchemy-tickets] Issue #3443: Deprecated set() type in lib/sqlalchemy/sql/schema.py (zzzeek/sqlalchemy)

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

New issue 3443: Deprecated set() type in lib/sqlalchemy/sql/schema.py
https://bitbucket.org/zzzeek/sqlalchemy/issue/3443/deprecated-set-type-in-lib-sqlalchemy-sql

rokie:

While doing some work I stumbled upon some sets on an object.  Further research led me to the source file: lib/sqlalchemy/sql/schema.py; line #454 specifically.  At which point I hit gold.

My plan was to re-cursing through the foreign keys in the Table object to auto-build an outputted serialized object (as there are some serious problems with Eve and MySQL, it has been sadly easier to re-invent some of the wheel :L).  As the object is type `set`, that proves more difficult and as of Python 2.6 it's usage is deprecated (running on 2.7).

[Sqlalchemy-tickets] Issue #3442: no control of DDL sequences for indexes, FK constraints that are mutually dependent (zzzeek/sqlalchemy)

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

New issue 3442: no control of DDL sequences for indexes, FK constraints that are mutually dependent
https://bitbucket.org/zzzeek/sqlalchemy/issue/3442/no-control-of-ddl-sequences-for-indexes-fk

Mike Bayer:

the whole system described at http://docs.sqlalchemy.org/en/rel_1_0/core/ddl.html#controlling-ddl-sequences only takes place for constraints that are rendered inline within the table definition via the _create_rule() callable checked in compiler.py.  This callable is not consulted anywhere in ddl.py when it goes to create indexes, foreign keys, or for that matter sequences.  

```
#!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)
    x = Column(Integer)
    favorite_b = Column(Integer)


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

from sqlalchemy.schema import CreateIndex, AddConstraint

idx = Index('aidx', A.x)

fk1 = ForeignKeyConstraint([B.a_id], [A.id], name='afk')
fk2 = ForeignKeyConstraint([A.favorite_b], [B.id], name='bfk')

from sqlalchemy import event

event.listen(
    Base.metadata,
    'after_create',
    CreateIndex(idx).execute_if(dialect='postgresql')
)

event.listen(
    Base.metadata,
    'after_create',
    AddConstraint(fk1).execute_if(dialect='postgresql')
)
event.listen(
    Base.metadata,
    'after_create',
    AddConstraint(fk2).execute_if(dialect='postgresql')
)

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


```

[Sqlalchemy-tickets] Issue #3441: sqlite_raw_colnames option documented nowhere (zzzeek/sqlalchemy)

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

New issue 3441: sqlite_raw_colnames option documented nowhere
https://bitbucket.org/zzzeek/sqlalchemy/issue/3441/sqlite_raw_colnames-option-documented

Mike Bayer:

no idea how c6d4471a got committed without a single doc.   people need to know about this behavior.

[Sqlalchemy-tickets] Issue #3440: Problem with selects using CTEs inside function constructs (zzzeek/sqlalchemy)

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

New issue 3440: Problem with selects using CTEs inside function constructs
https://bitbucket.org/zzzeek/sqlalchemy/issue/3440/problem-with-selects-using-ctes-inside

Konsta Vesterinen:

I've been having problems with CTEs inside function constructs. The CTEs seem to get duplicated in the parent query FROM part. This in turn causes variety of problems. For now I've been using workarounds (in other words not using CTEs at all). However now I have a scenario where this feature desperately needed.

I created a simplistic scenario which illustrates this problem. The generated SQL is also wrong (at least in PostgreSQL) since the select clause in from part does not have an alias.


```
#!python

import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base


engine = sa.create_engine(
    'postgres://postgres@localhost/sqlalchemy_utils_test'
)
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()


class User(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)
    def __repr__(self):
        return 'User(name=%r)' % self.name

Base.metadata.create_all(bind=session.bind)

users = sa.select([User.name]).limit(1).cte('users')

query = sa.select(
    [
        sa.func.json_build_object('user', sa.select([users.c.name]))
    ]
)
# query IS:
#
# WITH users AS
# (SELECT "user".name AS name
# FROM "user"
#  LIMIT :param_1)
#  SELECT json_build_object(:json_build_object_2, (SELECT users.name
# FROM users)) AS json_build_object_1
# FROM (SELECT users.name AS name
# FROM users)
#
# query SHOULD BE:
#
# WITH users AS
# (SELECT "user".name AS name
# FROM "user"
#  LIMIT 1)
#  SELECT json_build_object('something', (SELECT users.name FROM users))

session.execute('DROP TABLE "user"')
session.commit()

```
There might also be a simple workaround for this that I just haven't noticed. 

[Sqlalchemy-tickets] Issue #3439: MutableDict not working under pypy2.6 (zzzeek/sqlalchemy)

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

New issue 3439: MutableDict not working under pypy2.6
https://bitbucket.org/zzzeek/sqlalchemy/issue/3439/mutabledict-not-working-under-pypy26

Pablo Marti:

Hi there,

I have a User model class with a profile attribute marked as mutable

```
#!python

class User(db.Model):
    ...
    profile = db.Column(MutableDict.as_mutable(JSONB()))
```

This works perfectly under python2.7, but now when I issue a query using pypy 2.6 I get this:

```
Traceback (most recent call last):
  File "/Users/pablo/.virtualenvs/drop/src/flask/flask/app.py", line 1537, in full_dispatch_request
    rv = self.dispatch_request()
  File "/Users/pablo/.virtualenvs/drop/src/flask/flask/app.py", line 1523, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/Users/pablo/Development/drop-server/drop/api/decorators.py", line 73, in decorated_function
    .get(user_id)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/orm/query.py", line 818, in get
    return self._get_impl(ident, loading.load_on_ident)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/orm/query.py", line 851, in _get_impl
    return fallback_fn(self, key)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/orm/loading.py", line 217, in load_on_ident
    return q.one()
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/orm/query.py", line 2472, in one
    ret = list(self)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/orm/loading.py", line 84, in instances
    util.raise_from_cause(err)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/orm/loading.py", line 69, in instances
    rows = [proc(row) for row in fetch]
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/orm/loading.py", line 430, in _instance
    state.manager.dispatch.load(state, context)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/event/attr.py", line 258, in __call__
    fn(*args, **kw)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/ext/mutable.py", line 428, in load
    val = cls.coerce(key, val)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/ext/mutable.py", line 638, in coerce
    return Mutable.coerce(key, value)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/ext/mutable.py", line 403, in coerce
    raise ValueError(msg % (key, type(value)))
ValueError: Attribute 'profile' does not accept objects of type <type 'list'>
```

Dependencies:

SQLAlchemy==1.0.4
psycopg2cffi==2.7.0

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3438: support explicit SERIAL on postgresql (zzzeek/sqlalchemy)

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

New issue 3438: support explicit SERIAL on postgresql
https://bitbucket.org/zzzeek/sqlalchemy/issue/3438/support-explicit-serial-on-postgresql

Mike Bayer:

[Sqlalchemy-tickets] Issue #3437: Enum type not created by create_all if only used in an ARRAY (zzzeek/sqlalchemy)

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

New issue 3437: Enum type not created by create_all if only used in an ARRAY
https://bitbucket.org/zzzeek/sqlalchemy/issue/3437/enum-type-not-created-by-create_all-if

Wichert Akkerman:

I'm used to metadata.create_all() being very smart and creating everything as needed. I seem to have found a gap in its knowledge though: enum types that are only used in PostgreSQL ARRAY types are not created. The attached test case gives this output:

```
2015-05-29 14:40:08,477 INFO sqlalchemy.engine.base.Engine select version()
2015-05-29 14:40:08,477 INFO sqlalchemy.engine.base.Engine {}
2015-05-29 14:40:08,478 INFO sqlalchemy.engine.base.Engine select current_schema()
2015-05-29 14:40:08,478 INFO sqlalchemy.engine.base.Engine {}
2015-05-29 14:40:08,479 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2015-05-29 14:40:08,479 INFO sqlalchemy.engine.base.Engine {}
2015-05-29 14:40:08,479 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2015-05-29 14:40:08,479 INFO sqlalchemy.engine.base.Engine {}
2015-05-29 14:40:08,480 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2015-05-29 14:40:08,480 INFO sqlalchemy.engine.base.Engine {}
2015-05-29 14:40:08,481 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2015-05-29 14:40:08,481 INFO sqlalchemy.engine.base.Engine {'name': 'pizza'}
2015-05-29 14:40:08,482 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE pizza (
	id SERIAL NOT NULL, 
	toppings topping[], 
	PRIMARY KEY (id)
)


2015-05-29 14:40:08,482 INFO sqlalchemy.engine.base.Engine {}
2015-05-29 14:40:08,483 INFO sqlalchemy.engine.base.Engine ROLLBACK
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: type "topping[]" does not exist
LINE 4:  toppings topping[], 
```

[Sqlalchemy-tickets] Issue #3436: Supports set in addition to list/tuple in postgres ARRAY type (zzzeek/sqlalchemy)

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

New issue 3436: Supports set in addition to list/tuple in postgres ARRAY type
https://bitbucket.org/zzzeek/sqlalchemy/issue/3436/supports-set-in-addition-to-list-tuple-in

thiefmaster:

In some cases it would be useful to have a set on the python side, e.g. because you don't need/want duplicates and the set api is nicer to use in that case (as you won't have to worry about removing dupes).

Since there's already `as_tuple`, I'd deprecate this option and have a `python_type` kwarg instead that defaults to `list` but can be set to `tuple` or `set`, too. `as_tuple` and `python_type` would be mutually exclusive.

[Sqlalchemy-tickets] Issue #3435: boolean parsing in engine_from_config wrt engine strategies is incorrect (zzzeek/sqlalchemy)

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

New issue 3435: boolean parsing in engine_from_config wrt engine strategies is incorrect
https://bitbucket.org/zzzeek/sqlalchemy/issue/3435/boolean-parsing-in-engine_from_config-wrt

Mike Bayer:


```
#!diff

diff --git a/test/engine/test_parseconnect.py b/test/engine/test_parseconnect.py
index fb1f338..dfd39d0 100644
--- a/test/engine/test_parseconnect.py
+++ b/test/engine/test_parseconnect.py
@@ -138,6 +138,16 @@ class CreateEngineTest(fixtures.TestBase):
                             'z=somevalue')
         assert e.echo is True
 
+    def test_pool_threadlocal_from_config(self):
+        dbapi = mock_dbapi
+
+        config = {
+            'sqlalchemy.url': 'postgresql://scott:tiger@somehost/test',
+            'sqlalchemy.pool_threadlocal': "false"}
+
+        e = engine_from_config(config, module=dbapi, _initialize=False)
+        eq_(e.pool._use_threadlocal, False)
+
     def test_pool_reset_on_return_from_config(self):
         dbapi = mock_dbapi

```

fails.  We have 'bool' as the handler in default.py and there is no string processing here.

[Sqlalchemy-tickets] Issue #3434: set MSSQL legacy_schema_aliasing flag to False (zzzeek/sqlalchemy)

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

New issue 3434: set MSSQL legacy_schema_aliasing flag to False
https://bitbucket.org/zzzeek/sqlalchemy/issue/3434/set-mssql-legacy_schema_aliasing-flag-to

Mike Bayer:

[Sqlalchemy-tickets] Issue #3433: Add support for Oracle XMLTYPE (zzzeek/sqlalchemy)

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

New issue 3433: Add support for Oracle XMLTYPE
https://bitbucket.org/zzzeek/sqlalchemy/issue/3433/add-support-for-oracle-xmltype

Pavel Chernikov: