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

[Sqlalchemy-tickets] Issue #3155: nullable is ignored when creating TIMESTAMP COLUMN in mysql (zzzeek/sqlalchemy)

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

New issue 3155: nullable is ignored when creating TIMESTAMP COLUMN in mysql
https://bitbucket.org/zzzeek/sqlalchemy/issue/3155/nullable-is-ignored-when-creating

David Lents:


e.g:
last_access = Column(TIMESTAMP(), nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')))

results in:
`last_access` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

This appears to be caused by dialects/mysql/base.py line 1679:

if not column.nullable and not is_timestamp:
            colspec.append('NOT NULL')

This doesn't seem correct, as NOT NULL changes the behavior of timestamp type in mysql if I'm not mistaken.

[Sqlalchemy-tickets] Issue #3154: Using aliased table makes literal bindings fail for CTE statement (zzzeek/sqlalchemy)

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

New issue 3154: Using aliased table makes literal bindings fail for CTE statement
https://bitbucket.org/zzzeek/sqlalchemy/issue/3154/using-aliased-table-makes-literal-bindings

mike_solomon:

I've had no problems thus far compiling complex insert statements with literal bind parameters, but the example below fails, binding some parameters into the statement and not binding others when an alias of a table is used.  I've put a big fat comment saying LOOK HERE at the point in the code where you can comment in and out a line to see the behavior kicking in.


```
#!python

from sqlalchemy import Table, Column, String, Integer, Float, MetaData, select, cast, literal, and_

_metadata = MetaData()

string_box = Table('string_box', _metadata,
  Column('name', String, primary_key = True),
  Column('str', Integer, primary_key = True),
  Column('x', Integer),
  Column('y', Integer),
  Column('width', Integer),
  Column('height', Integer)
  )

font_name = Table('font_name', _metadata,
  Column('id', Integer),
  Column('val', String))

name = Table('name', _metadata,
  Column('id', Integer),
  Column('val', String))

font_size = Table('font_size', _metadata,
  Column('id', Integer),
  Column('val', Float))

time_signature = Table('time_signature', _metadata,
  Column('id', Integer),
  Column('num', Integer),
  Column('den', Integer))

height = Table('height', _metadata,
  Column('id', Integer),
  Column('val', Float))

width = Table('width', _metadata,
  Column('id', Integer),
  Column('val', Float))

stencil = Table('stencil', _metadata,
  Column('id', Integer, primary_key = True),
  Column('sub_id', Integer, primary_key = True),
  Column('font_name', String),
  Column('font_size', Float),
  Column('str', String),
  Column('x', Float),
  Column('y', Float))

#######################################

time_signatures_to_xy_info = select([
  name.c.id.label('id'),
  font_name.c.val.label('font_name'),
  font_size.c.val.label('font_size'),
  time_signature.c.num.label('num_str'),
  time_signature.c.den.label('den_str'),
  literal(0.0).label('num_x'),
  literal(1.0).label('den_x'),
  literal(1.0).label('num_y'),
  literal(0.0).label('den_y')
]).where(and_(name.c.val == 'time_signature',
              name.c.id == font_name.c.id,
              name.c.id == font_size.c.id,
              name.c.id == time_signature.c.id)).\
cte(name='time_signatures_to_xy_info')

'''
LOOK HERE
'''
time_signatures_to_xy_info_num = time_signatures_to_xy_info.alias('time_signatures_to_xy_info_num')
# the line above causes literal binding to fail
# comment out the line above and uncomment the line below for the binding to succeed
#time_signatures_to_xy_info_num = time_signatures_to_xy_info

time_signatures_to_stencils = select([
   time_signatures_to_xy_info_num.c.id.label('id'),
   literal(0).label('sub_id'),
   time_signatures_to_xy_info_num.c.font_name.label('font_name'),
   time_signatures_to_xy_info_num.c.font_size.label('font_size'),
   time_signatures_to_xy_info_num.c.num_str.label('str'),
   time_signatures_to_xy_info_num.c.num_x.label('x'),
   time_signatures_to_xy_info_num.c.num_y.label('y'),
 ]).cte(name="time_signatures_to_stencils")

insert_stmt = stencil.insert().from_select(['id', 'sub_id', 'font_name', 'font_size', 'str', 'x', 'y'], select([time_signatures_to_stencils]))
print str(insert_stmt)
print "!=+~"*20
print "binding partially fails, partially succeeds"
print "!=+~"*20
print insert_stmt.compile(compile_kwargs={"literal_binds": True})
```

[Sqlalchemy-tickets] Issue #3153: postgresql+pg8000 fails to connect when postgresql server is using SSL (zzzeek/sqlalchemy)

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

New issue 3153: postgresql+pg8000 fails to connect when postgresql server is using SSL
https://bitbucket.org/zzzeek/sqlalchemy/issue/3153/postgresql-pg8000-fails-to-connect-when

Sorin Sbarnea:

postgresql+pg8000 fails to connect when postgresql server is using SSL

pg8000/core.py", line 873, in __init__
    raise InterfaceError("communication error", exc_info()[1])
sqlalchemy.exc.InterfaceError: (InterfaceError) ('communication error', error(61, 'Connection refused')) None None

I even tried to use the same parameter as the one used in the psycopg2 and to append '&sslmode=require" to the URL but this has no effect.

pg8000 has a parameter for enabling SSL, but this seems not to be supported by sqlalchemy or not documented.

In fact the proper way to implement this would be to use SSL without having to ask for an additional parameter. A very good example is "psql" utility which works in both cases, without asking you to specify if is to use SSL or not.

[Sqlalchemy-tickets] Issue #3152: sqlalchemy+pymssql cannot work with SQL Azure except on master database (zzzeek/sqlalchemy)

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

New issue 3152: sqlalchemy+pymssql cannot work with SQL Azure except on master database
https://bitbucket.org/zzzeek/sqlalchemy/issue/3152/sqlalchemy-pymssql-cannot-work-with-sql

Daniel Halperin:

SQL Azure does not support the `USE` statement -- the database must be set at connection create time.

I verified that this is handled correctly by pymssql when invoked directly.

However, it looks like SQLAlchemy does not support this behavior.

[Sqlalchemy-tickets] Issue #3151: mssql+pymssql recently broken with SQL Azure (zzzeek/sqlalchemy)

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

New issue 3151: mssql+pymssql recently broken with SQL Azure
https://bitbucket.org/zzzeek/sqlalchemy/issue/3151/mssql-pymssql-recently-broken-with-sql

Daniel Halperin:

The issue is that the `self.server_version_info` struct is not being populated because SQL Azure changed their version string. The new string:

```
Microsoft SQL Azure (RTM) - 11.0.9216.62 
	Jul 18 2014 22:00:21 
	Copyright (c) Microsoft Corporation
```

The [regex used by pymssql](https://bitbucket.org/zzzeek/sqlalchemy/src/32165f50209036a98959553e1c5e81537a091a15/lib/sqlalchemy/dialects/mssql/pymssql.py?at=master#cl-66):

```python
        m = re.match(
            r"Microsoft SQL Server.*? - (\d+).(\d+).(\d+).(\d+)", vers)
```

This does not match, which leads to a `None` version info, which breaks the code:

```python
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "sqlq/explain.py", line 30, in explain
    _do_explain(db, expl_db)
  File "sqlq/explain.py", line 13, in _do_explain
    with expl_db.connect() as connection:
  File "/Users/dhalperi/Envs/sdss-query/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1779, in connect
    return self._connection_cls(self, **kwargs)
  File "/Users/dhalperi/Envs/sdss-query/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 60, in __init__
    self.__connection = connection or engine.raw_connection()
  File "/Users/dhalperi/Envs/sdss-query/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1848, in raw_connection
    return self.pool.unique_connection()
  File "/Users/dhalperi/Envs/sdss-query/lib/python2.7/site-packages/sqlalchemy/pool.py", line 280, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/Users/dhalperi/Envs/sdss-query/lib/python2.7/site-packages/sqlalchemy/pool.py", line 641, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/Users/dhalperi/Envs/sdss-query/lib/python2.7/site-packages/sqlalchemy/pool.py", line 440, in checkout
    rec = pool._do_get()
  File "/Users/dhalperi/Envs/sdss-query/lib/python2.7/site-packages/sqlalchemy/pool.py", line 961, in _do_get
    return self._create_connection()
  File "/Users/dhalperi/Envs/sdss-query/lib/python2.7/site-packages/sqlalchemy/pool.py", line 285, in _create_connection
    return _ConnectionRecord(self)
  File "/Users/dhalperi/Envs/sdss-query/lib/python2.7/site-packages/sqlalchemy/pool.py", line 416, in __init__
    exec_once(self.connection, self)
  File "/Users/dhalperi/Envs/sdss-query/lib/python2.7/site-packages/sqlalchemy/event/attr.py", line 250, in exec_once
    self(*args, **kw)
  File "/Users/dhalperi/Envs/sdss-query/lib/python2.7/site-packages/sqlalchemy/event/attr.py", line 260, in __call__
    fn(*args, **kw)
  File "/Users/dhalperi/Envs/sdss-query/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 1219, in go
    return once_fn(*arg, **kw)
  File "/Users/dhalperi/Envs/sdss-query/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 165, in first_connect
    dialect.initialize(c)
  File "/Users/dhalperi/Envs/sdss-query/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/base.py", line 1374, in initialize
    if self.server_version_info[0] not in list(range(8, 17)):
TypeError: 'NoneType' object has no attribute '__getitem__'
```

[Sqlalchemy-tickets] Issue #3150: use-case specific declared_attr (mixin.*?) descriptors (zzzeek/sqlalchemy)

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

New issue 3150: use-case specific declared_attr (mixin.*?) descriptors
https://bitbucket.org/zzzeek/sqlalchemy/issue/3150/use-case-specific-declared_attr-mixin

Mike Bayer:

poc should allow us to consolidate #2670, #2952, #3149, #3050.

the new descriptors include the ability to cache the result per class, or to do "cascade", guarantees that the callable fn is called only once per target class, as well as to name attributes that are set up after the mapping is complete, so that relationship and column_property declared_attrs have the whole mapping to work with when they are called.  and the whole thing doesn't modify any existing functionality, only adds new things we can take time to stabilize.  win win win win.


```
#!python

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr, has_inherited_table

Base = declarative_base()


class VehicleModel(Base):
    __tablename__ = "vehicle_model"
    id = Column(Integer, primary_key=True)
    name = Column(String(20))


class VehicleInfo(object):
    vehicle_plate_region = Column(String(5))
    vehicle_plate = Column(String(20))

    @declared_attr.column
    def vehicle_model_id(cls):
        return Column(Integer, ForeignKey("vehicle_model.id"))

    @declared_attr.property
    def vehicle_model(cls):
        # 1. called after the class is fully mapped
        # 2. called only once for each class

        assert cls.__table__ is not None and \
            cls.__table__.c.vehicle_model_id.shares_lineage(
                cls.vehicle_model_id.__clause_element__()
            )

        return relationship(VehicleModel, foreign_keys=[cls.vehicle_model_id])

    @declared_attr.column.cascading
    def id(cls):
        if has_inherited_table(cls):
            return Column(Integer, ForeignKey("vehicle.id"), primary_key=True)
        else:
            return Column(Integer, primary_key=True)


class Vehicle(VehicleInfo, Base):
    __tablename__ = 'vehicle'


class SubVehicle(Vehicle):
    __tablename__ = 'subveh'

    @declared_attr.property
    def some_other_thing(cls):
        # called way at the end
        assert cls.id.__clause_element__().references(Vehicle.__table__.c.id)
        return column_property(cls.id)

configure_mappers()


```

poc patch, however still needs integration for #2670:


```
#!diff

diff --git a/lib/sqlalchemy/ext/declarative/api.py b/lib/sqlalchemy/ext/declarative/api.py
index daf8bff..8adec1a 100644
--- a/lib/sqlalchemy/ext/declarative/api.py
+++ b/lib/sqlalchemy/ext/declarative/api.py
@@ -13,7 +13,7 @@ from ...orm import synonym as _orm_synonym, mapper,\
     interfaces, properties
 from ...orm.util import polymorphic_union
 from ...orm.base import _mapper_or_none
-from ...util import OrderedDict
+from ...util import OrderedDict, classproperty
 from ... import exc
 import weakref
 
@@ -164,6 +164,49 @@ class declared_attr(interfaces._MappedAttribute, property):
     def __get__(desc, self, cls):
         return desc.fget(cls)
 
+    @classproperty
+    def column(cls):
+        return _declared_column
+
+    @classproperty
+    def property(cls):
+        return _declared_property
+
+    defer_defer_defer = False
+
+class _memoized_declared_attr(declared_attr):
+    def __init__(self, fget, cascading=False):
+        super(_memoized_declared_attr, self).__init__(fget)
+        self.reg = weakref.WeakKeyDictionary()
+        self._cascading = cascading
+
+    def __get__(desc, self, cls):
+        if desc.defer_defer_defer:
+            return desc
+        elif desc._cascading:
+            search = [cls]
+        else:
+            search = cls.__mro__
+        for super_ in search:
+            if super_ in desc.reg:
+                return desc.reg[super_]
+        else:
+            desc.reg[cls] = obj = desc.fget(cls)
+            return obj
+
+    @classproperty
+    def cascading(cls):
+        return lambda decorated: cls(decorated, cascading=True)
+
+
+class _declared_column(_memoized_declared_attr):
+    pass
+
+
+class _declared_property(_memoized_declared_attr):
+    defer_defer_defer = True
+
+
 
 def declarative_base(bind=None, metadata=None, mapper=None, cls=object,
                      name='Base', constructor=_declarative_constructor,
diff --git a/lib/sqlalchemy/ext/declarative/base.py b/lib/sqlalchemy/ext/declarative/base.py
index 94baeeb..cee2263 100644
--- a/lib/sqlalchemy/ext/declarative/base.py
+++ b/lib/sqlalchemy/ext/declarative/base.py
@@ -33,7 +33,7 @@ def _declared_mapping_info(cls):
 
 
 def _as_declarative(cls, classname, dict_):
-    from .api import declared_attr
+    from .api import declared_attr, _memoized_declared_attr
 
     # dict_ will be a dictproxy, which we can't write to, and we need to!
     dict_ = dict(dict_)
@@ -132,6 +132,14 @@ def _as_declarative(cls, classname, dict_):
                         "column_property(), relationship(), etc.) must "
                         "be declared as @declared_attr callables "
                         "on declarative mixin classes.")
+                elif isinstance(obj, _memoized_declared_attr): # and \
+                    if obj._cascading:
+                        dict_[name] = ret = obj.__get__(obj, cls)
+                    else:
+                        dict_[name] = ret = getattr(cls, name)
+                    if isinstance(ret, (Column, MapperProperty)) and \
+                            ret.doc is None:
+                        ret.doc = obj.__doc__
                 elif isinstance(obj, declarative_props):
                     dict_[name] = ret = \
                         column_copies[obj] = getattr(cls, name)
@@ -148,6 +156,7 @@ def _as_declarative(cls, classname, dict_):
 
     clsregistry.add_class(classname, cls)
     our_stuff = util.OrderedDict()
+    add_later = util.OrderedDict()
 
     for k in list(dict_):
 
@@ -157,7 +166,10 @@ def _as_declarative(cls, classname, dict_):
 
         value = dict_[k]
         if isinstance(value, declarative_props):
-            value = getattr(cls, k)
+            if value.defer_defer_defer:
+                add_later[k] = value
+            else:
+                value = getattr(cls, k)
 
         elif isinstance(value, QueryableAttribute) and \
                 value.class_ is not cls and \
@@ -324,7 +336,8 @@ def _as_declarative(cls, classname, dict_):
                  declared_columns,
                  column_copies,
                  our_stuff,
-                 mapper_args_fn)
+                 mapper_args_fn,
+                 add_later)
     if not defer_map:
         mt.map()
 
@@ -339,7 +352,8 @@ class _MapperConfig(object):
                  inherits,
                  declared_columns,
                  column_copies,
-                 properties, mapper_args_fn):
+                 properties, mapper_args_fn,
+                 add_later):
         self.mapper_cls = mapper_cls
         self.cls = cls
         self.local_table = table
@@ -348,6 +362,7 @@ class _MapperConfig(object):
         self.mapper_args_fn = mapper_args_fn
         self.declared_columns = declared_columns
         self.column_copies = column_copies
+        self.add_later = add_later
 
     def _prepare_mapper_arguments(self):
         properties = self.properties
@@ -410,6 +425,8 @@ class _MapperConfig(object):
             self.local_table,
             **mapper_args
         )
+        for k, v in self.add_later.items():
+            setattr(self.cls, k, v.fget(self.cls))
 
 
 class _DeferredMapperConfig(_MapperConfig):
diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
index f3af46c..ea8c32f 100644
--- a/lib/sqlalchemy/sql/schema.py
+++ b/lib/sqlalchemy/sql/schema.py
@@ -1161,8 +1161,10 @@ class Column(SchemaItem, ColumnClause):
         existing = getattr(self, 'table', None)
         if existing is not None and existing is not table:
             raise exc.ArgumentError(
-                "Column object already assigned to Table '%s'" %
-                existing.description)
+                "Column object '%s' already assigned to Table '%s'" % (
+                    self.key,
+                    existing.description
+                ))
 
         if self.key in table._columns:
             col = table._columns.get(self.key)

```

[Sqlalchemy-tickets] Issue #3149: declared_attr do not work with mixin relationship declaration (zzzeek/sqlalchemy)

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

New issue 3149: declared_attr do not work with mixin relationship declaration
https://bitbucket.org/zzzeek/sqlalchemy/issue/3149/declared_attr-do-not-work-with-mixin

SowingSadness:

```
#!python

# -*- coding: utf-8 -*-
__author__ = 'SowingSadness'

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


Base = declarative_base()
DBSession = scoped_session(sessionmaker())
"""@type : sqlalchemy.orm.session.Session|sqlalchemy.orm.session.SessionTransaction"""


class VehicleModel(Base):
    __tablename__ = "vehicle_model"
    id = Column(Integer, primary_key=True)
    name = Column(String(20))


class VehicleInfo(object):
    vehicle_plate_region = Column(String(5))
    vehicle_plate = Column(String(20))

    @declared_attr
    def vehicle_model_id(cls):
        return Column(Integer, ForeignKey("vehicle_model.id"))

    @declared_attr
    def vehicle_model(cls):
        return relationship(VehicleModel, foreign_keys=[cls.vehicle_model_id])


class Vehicle(VehicleInfo, Base):
    __tablename__ = 'vehicle'
    id = Column(Integer, primary_key=True)


def main():
    engine = create_engine('postgresql+psycopg2://postgres@localhost:5432/t1')
    DBSession.configure(bind=engine)
    Base.metadata.bind = engine

    Base.metadata.drop_all()
    Base.metadata.create_all(checkfirst=True)

    vm = VehicleModel()
    vehicle = Vehicle(vehicle_model=vm)
    DBSession.add(vm)
    DBSession.add(vehicle)
    DBSession.commit()


if __name__ == '__main__':
    main()

```


```
#!text

C:\Users\Kir\Documents\Work\pyramid_p27\Scripts\pythonw.exe C:/Users/Kir/Documents/Work/sqlalchemy-test/main.py
Traceback (most recent call last):
  File "C:/Users/Kir/Documents/Work/sqlalchemy-test/main.py", line 58, in <module>
    main()
  File "C:/Users/Kir/Documents/Work/sqlalchemy-test/main.py", line 50, in main
    vm = VehicleModel()
  File "<string>", line 2, in __init__
  File "C:\Users\Kir\Documents\Work\pyramid_p27\lib\site-packages\sqlalchemy\orm\instrumentation.py", line 324, in _new_state_if_none
    state = self._state_constructor(instance, self)
  File "C:\Users\Kir\Documents\Work\pyramid_p27\lib\site-packages\sqlalchemy\util\langhelpers.py", line 725, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "C:\Users\Kir\Documents\Work\pyramid_p27\lib\site-packages\sqlalchemy\orm\instrumentation.py", line 158, in _state_constructor
    self.dispatch.first_init(self, self.class_)
  File "C:\Users\Kir\Documents\Work\pyramid_p27\lib\site-packages\sqlalchemy\event\attr.py", line 260, in __call__
    fn(*args, **kw)
  File "C:\Users\Kir\Documents\Work\pyramid_p27\lib\site-packages\sqlalchemy\orm\mapper.py", line 2687, in _event_on_first_init
    configure_mappers()
  File "C:\Users\Kir\Documents\Work\pyramid_p27\lib\site-packages\sqlalchemy\orm\mapper.py", line 2583, in configure_mappers
    mapper._post_configure_properties()
  File "C:\Users\Kir\Documents\Work\pyramid_p27\lib\site-packages\sqlalchemy\orm\mapper.py", line 1688, in _post_configure_properties
    prop.init()
  File "C:\Users\Kir\Documents\Work\pyramid_p27\lib\site-packages\sqlalchemy\orm\interfaces.py", line 144, in init
    self.do_init()
  File "C:\Users\Kir\Documents\Work\pyramid_p27\lib\site-packages\sqlalchemy\orm\relationships.py", line 1550, in do_init
    self._setup_join_conditions()
  File "C:\Users\Kir\Documents\Work\pyramid_p27\lib\site-packages\sqlalchemy\orm\relationships.py", line 1624, in _setup_join_conditions
    can_be_synced_fn=self._columns_are_mapped
  File "C:\Users\Kir\Documents\Work\pyramid_p27\lib\site-packages\sqlalchemy\orm\relationships.py", line 1892, in __init__
    self._determine_joins()
  File "C:\Users\Kir\Documents\Work\pyramid_p27\lib\site-packages\sqlalchemy\orm\relationships.py", line 1996, in _determine_joins
    "specify a 'primaryjoin' expression." % self.prop)
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Vehicle.vehicle_model - there are no foreign keys linking these tables.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

Process finished with exit code 1
```

I try to use code in https://bitbucket.org/zzzeek/sqlalchemy/issue/2471/add-example-of-declared_attr-columns but result is same.

[Sqlalchemy-tickets] Issue #3148: Column property labels with classes using with_polymorphic (zzzeek/sqlalchemy)

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

New issue 3148: Column property labels with classes using with_polymorphic
https://bitbucket.org/zzzeek/sqlalchemy/issue/3148/column-property-labels-with-classes-using

Konsta Vesterinen:

It seems labeling column_properties with classes using with_polymorphic='*' doesn't work. The following test case outlines the problem:

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


engine = sa.create_engine('sqlite:///:memory:')
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()


class TextItem(Base):
    __tablename__ = 'text_item'
    id = sa.Column(sa.Integer, primary_key=True)

    type = sa.Column(sa.Unicode(255))

    __mapper_args__ = {
        'polymorphic_on': type,
        'with_polymorphic': '*'
    }


class Article(TextItem):
    __tablename__ = 'article'
    id = sa.Column(
        sa.Integer, sa.ForeignKey(TextItem.id), primary_key=True
    )
    __mapper_args__ = {
        'polymorphic_identity': u'article'
    }


class Tag(Base):
    __tablename__ = 'tag'
    id = sa.Column(sa.Integer, primary_key=True)
    text_item_id = sa.Column(sa.Integer, sa.ForeignKey(TextItem.id))


TextItem.tag_count = sa.orm.column_property(
    sa.select(
        [
            sa.func.count('1')
        ],
    )
    .select_from(Tag.__table__)
    .where(Tag.__table__.c.text_item_id == Tag.__table__.c.id)
    .correlate(TextItem.__table__)
    .label('tag_count')
)


print session.query(TextItem) 
```

This gives me (notice the anon_1 alias for the labeled column property):

```
SELECT text_item.id AS text_item_id, text_item.type AS text_item_type, (SELECT count(:param_1) AS count_1
FROM tag
WHERE tag.text_item_id = tag.id) AS anon_1, article.id AS article_id
FROM text_item LEFT OUTER JOIN article ON text_item.id = article.id
```

The expected query would be

```
SELECT text_item.id AS text_item_id, text_item.type AS text_item_type, (SELECT count(:param_1) AS count_1
FROM tag
WHERE tag.text_item_id = tag.id) AS tag_count, article.id AS article_id
FROM text_item LEFT OUTER JOIN article ON text_item.id = article.id
```

[Sqlalchemy-tickets] Issue #3147: query works in 0.7.10 and not 0.8.7 (zzzeek/sqlalchemy)

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

New issue 3147: query works in 0.7.10 and not 0.8.7
https://bitbucket.org/zzzeek/sqlalchemy/issue/3147/query-works-in-0710-and-not-087

Mike Bernson:

The query works in 0.7.10 but not 0.8.7.

Code

```
#!python
from sqlalchemy import create_engine, Column, Date, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

from datetime import date


Base = declarative_base()


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

engine = create_engine('sqlite:///')

session = sessionmaker()
session.configure(bind=engine)

Base.metadata.create_all(engine)

s = session()
s.query(Test).filter(date(2010, 1, 1) <= Test.start_date <= date(2010, 12, 31))
```

output when running:

```

Traceback (most recent call last):
  File "test.py", line 24, in <module>
    s.query(Test).filter(date(2010, 1, 1) <= Test.start_date <= date(2010, 12, 31))
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/expression.py", line 4479, in __nonzero__
TypeError: Boolean value of this clause is not defined
Traceback (most recent call last):
  File "test.py", line 24, in <module>
    s.query(Test).filter(date(2010, 1, 1) <= Test.start_date <= date(2010, 12, 31))
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/expression.py", line 4479, in __nonzero__
TypeError: Boolean value of this clause is not defined

[Sqlalchemy-tickets] Issue #3146: mysql client_flags option cannot be overriden (zzzeek/sqlalchemy)

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

New issue 3146: mysql client_flags option cannot be overriden
https://bitbucket.org/zzzeek/sqlalchemy/issue/3146/mysql-client_flags-option-cannot-be

Mathieu Lacage:

Hi,

The mysql connector sets by default the FOUND_ROWS bit in the client_flag set on each connection with the server. The documentation is explicit about this (good): http://docs.sqlalchemy.org/en/rel_0_9/dialects/mysql.html#rowcount-support

However, the documentation also says that it is possible to override this setting through the client_flag url argument. While it is indeed possible to set manually this client_flag argument, the FOUND_ROWS bit is set unconditionally in dialects/mysql/mysqlconnector.py

Hence, the attached patch (generated against git master) to allow callers who know what they are doing to override this default behavior in sqlalchemy

[Sqlalchemy-tickets] Issue #3145: AttributeError: 'NoneType' object has no attribute '_sa_appender' (zzzeek/sqlalchemy)

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

New issue 3145: AttributeError: 'NoneType' object has no attribute '_sa_appender'
https://bitbucket.org/zzzeek/sqlalchemy/issue/3145/attributeerror-nonetype-object-has-no

Dobes Vandermeer:

Under certain circumstances the above error can occur.  It's a bit hard to explain, but I will try.

In our case:

1. An entity joins to itself in a parent/child relation
2. An instance has its parent relation set to itself
3. A collection in the parent entity is eagerly loaded as part of a query
4. The properties of the entity are ordered such that the collection is loaded after the parent

This triggers a sequence of events in which:

1. The child is loaded
2. The parent is loaded (which is actually the child) as part of loading properties
3. The parent initializes the collection for eager loading
4. The child continues setting up its properties, setting the same collection to the default (lazy loaded) collection
5. The collection set up in the parent to collect the eagerly loaded entities is garbage collected
6. When a member of the collection is loaded on a subsequent row, there is a crash because the underlying collection was garbage collected

The stack trace is as follows:


```
#!python

Traceback (most recent call last):
  File "/home/dobes/sqlalchemy/test/orm/test_temp.py", line 62, in test_bug
    res = session.query(A).join(A.b).join(b_parent, b_parent.b_id == B.parent_id).join(b_parent.z).filter(BC.value>0).options(joinedload('b').joinedload('parent').joinedload('z').joinedload('c')).all()
  File "/home/dobes/sqlalchemy/test/../lib/sqlalchemy/orm/query.py", line 2300, in all
    return list(self)
  File "/home/dobes/sqlalchemy/test/../lib/sqlalchemy/orm/loading.py", line 73, in instances
    rows = [process[0](row, None) for row in fetch]
  File "/home/dobes/sqlalchemy/test/../lib/sqlalchemy/orm/loading.py", line 455, in _instance
    populate_state(state, dict_, row, isnew, only_load_props)
  File "/home/dobes/sqlalchemy/test/../lib/sqlalchemy/orm/loading.py", line 305, in populate_state
    populator(state, dict_, row)
  File "/home/dobes/sqlalchemy/test/../lib/sqlalchemy/orm/strategies.py", line 1483, in load_scalar_from_joined_existing_row
    existing = _instance(row, None)
  File "/home/dobes/sqlalchemy/test/../lib/sqlalchemy/orm/loading.py", line 484, in _instance
    populate_state(state, dict_, row, isnew, attrs)
  File "/home/dobes/sqlalchemy/test/../lib/sqlalchemy/orm/loading.py", line 309, in populate_state
    populator(state, dict_, row)
  File "/home/dobes/sqlalchemy/test/../lib/sqlalchemy/orm/strategies.py", line 1483, in load_scalar_from_joined_existing_row
    existing = _instance(row, None)
  File "/home/dobes/sqlalchemy/test/../lib/sqlalchemy/orm/loading.py", line 484, in _instance
    populate_state(state, dict_, row, isnew, attrs)
  File "/home/dobes/sqlalchemy/test/../lib/sqlalchemy/orm/loading.py", line 309, in populate_state
    populator(state, dict_, row)
  File "/home/dobes/sqlalchemy/test/../lib/sqlalchemy/orm/strategies.py", line 1465, in load_collection_from_joined_existing_row
    _instance(row, result_list)
  File "/home/dobes/sqlalchemy/test/../lib/sqlalchemy/orm/loading.py", line 503, in _instance
    result.append(instance)
  File "/home/dobes/sqlalchemy/test/../lib/sqlalchemy/util/_collections.py", line 757, in append
    self._data_appender(item)
  File "/home/dobes/sqlalchemy/test/../lib/sqlalchemy/orm/collections.py", line 657, in append_without_event
    self._data()._sa_appender(item, _sa_initiator=False)
AttributeError: 'NoneType' object has no attribute '_sa_appender'

```

I've attached a test case that reproduces the issue.  Note that because the order of properties is significant, I did a bit of a hack to sort the properties.

[Sqlalchemy-tickets] Issue #3144: failure of test testmeta in test.sql.test_types in release 0.9.7 (zzzeek/sqlalchemy)

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

New issue 3144: failure of test testmeta in test.sql.test_types in release 0.9.7
https://bitbucket.org/zzzeek/sqlalchemy/issue/3144/failure-of-test-testmeta-in

idella5:

This single failure occurs under pythons 2.7 3.2, 3.3 3.4 * pypy. Here is the traceback under pypy;

```python

....E...................................................SSSSSSSS...............
=================================================================
ERROR: test.sql.test_types.PickleMetadataTest.testmeta
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib64/pypy/site-packages/nose/case.py", line 197, in runTest
    self.test(*self.arg)
  File "/mnt/gen2/TmpDir/portage/dev-python/sqlalchemy-0.9.7/work/SQLAlchemy-0.9.7-pypy/test/sql/test_types.py", line 260, in testmeta
    loads(dumps(column_type))
  File "/usr/lib64/pypy/lib_pypy/cPickle.py", line 600, in loads
    return Unpickler(f).load()
  File "/usr/lib64/pypy/lib_pypy/cPickle.py", line 170, in load
    self.dispatch[key](self)
  File "/usr/lib64/pypy/lib_pypy/cPickle.py", line 439, in load_reduce
    value = self.stack[-1](*args)
TypeError: __new__() takes exactly 2 arguments (3 given)

----------------------------------------------------------------------
Ran 5926 tests in 705.219s

FAILED (SKIP=221, errors=1)
```

and under py3.4;

```python

===============================================================
ERROR: test.sql.test_types.PickleMetadataTest.testmeta
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib64/python3.4/site-packages/nose/case.py", line 198, in runTest
    self.test(*self.arg)
  File "/mnt/gen2/TmpDir/portage/dev-python/sqlalchemy-0.9.7/work/SQLAlchemy-0.9.7-python3_4/test/sql/test_types.py", line 260, in testmeta
    loads(dumps(column_type))
TypeError: __new__() takes 2 positional arguments but 3 were given

----------------------------------------------------------------------
Ran 5960 tests in 162.581s

FAILED (SKIP=193, errors=1)
```

errors=1 out of 5960 tests is a fine job any day of the week.   The no. or args appears a triviality.  Do you require anything further

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3143: UnicodeEncodeError in StatementError (zzzeek/sqlalchemy)

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

New issue 3143: UnicodeEncodeError in StatementError
https://bitbucket.org/zzzeek/sqlalchemy/issue/3143/unicodeencodeerror-in-statementerror

kodemi:

My models contains unicode fields. From time to time I get this error when quering records:

```
#!python

File "/usr/local/services/app/flights_handler.py", line 99, in fetch_flights_from_db
    flights = self.session.query(Flight).all()
  File "/home/fidsadm/.virtualenvs/vipport-services/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2286, in all
    return list(self)
  File "/home/fidsadm/.virtualenvs/vipport-services/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2398, in __iter__
    return self._execute_and_instances(context)
  File "/home/fidsadm/.virtualenvs/vipport-services/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2411, in _execute_and_instances
    close_with_result=True)
  File "/home/fidsadm/.virtualenvs/vipport-services/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2402, in _connection_from_session
    **kw)
  File "/home/fidsadm/.virtualenvs/vipport-services/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 843, in connection
    close_with_result=close_with_result)
  File "/home/fidsadm/.virtualenvs/vipport-services/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 847, in _connection_for_bind
    return self.transaction._connection_for_bind(engine)
  File "/home/fidsadm/.virtualenvs/vipport-services/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 298, in _connection_for_bind
    self._assert_active()
  File "/home/fidsadm/.virtualenvs/vipport-services/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 210, in _assert_active
    % self._rollback_exception
  File "/home/fidsadm/.virtualenvs/vipport-services/local/lib/python2.7/site-packages/sqlalchemy/exc.py", line 242, in __str__
    repr(self.statement), repr(params_repr)
  File "/home/fidsadm/.virtualenvs/vipport-services/local/lib/python2.7/site-packages/sqlalchemy/sql/util.py", line 254, in __repr__
    return repr(self.params)
UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-8: ordinal not in range(128)
```

[Sqlalchemy-tickets] Issue #3142: with_entities improvement (zzzeek/sqlalchemy)

[Kirill I. <iss...@bi...>]

New issue 3142: with_entities improvement
https://bitbucket.org/zzzeek/sqlalchemy/issue/3142/with_entities-improvement

Kirill Ivantsov:

Hello there! I'm looking the way how to access related object field with use of with_entities method. I want to use this like

```
#!python

lim_results = self.product.query.with_entities(models.Product.category.name).all()
```
Could i override this method?

[Sqlalchemy-tickets] Issue #3141: postgresql array literal constructor wrong for un-wrapped comparisons (zzzeek/sqlalchemy)

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

New issue 3141: postgresql array literal constructor wrong for un-wrapped comparisons
https://bitbucket.org/zzzeek/sqlalchemy/issue/3141/postgresql-array-literal-constructor-wrong

Mike Bayer:

e.g.:

    array([1, 2, 3]) > [4, 5, 6]

this is in _bind_param(), the tuple constructor uses * but array() does not.

[Sqlalchemy-tickets] Issue #3140: The pyodbc driver truncates the column names (zzzeek/sqlalchemy)

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

New issue 3140: The pyodbc driver truncates the column names
https://bitbucket.org/zzzeek/sqlalchemy/issue/3140/the-pyodbc-driver-truncates-the-column

Viktor Nagy:

Detailed explanation can be found here

http://stackoverflow.com/questions/24944857/mapping-non-ascii-long-column-names-in-sqlalchemy-fails-with-nosuchcolumnerror/24945106#24945106

[Sqlalchemy-tickets] Issue #3139: expunge after delete() fails (zzzeek/sqlalchemy)

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

New issue 3139: expunge after delete() fails
https://bitbucket.org/zzzeek/sqlalchemy/issue/3139/expunge-after-delete-fails

Mike Bayer:


```
#!python

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

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String)


if __name__ == "__main__":
    engine = create_engine("sqlite:///:memory:", echo=False)
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()

    ed = User(name="ed")

    session.add(ed)

    session.flush()

    session.delete(ed)

    session.flush()

    # should raise
    session.expunge(ed)

    assert object_session(ed) is None
    assert ed not in session

```

[Sqlalchemy-tickets] Issue #3138: Streamlined API docs? (zzzeek/sqlalchemy)

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

New issue 3138: Streamlined API docs?
https://bitbucket.org/zzzeek/sqlalchemy/issue/3138/streamlined-api-docs

jvanasco:

First off -- I'm not sure if this is possible.  I looked into the SqlA docs, and couldn't figure out a way to do this with RST.  If there is a way and consensus, I would be glad to do the busywork on this idea....

The docs are getting hard for me to read and reference.  They're absolutely great in content --  it's just the interface.  

The two big issues I've encountered are:

a- Many classes in the docs require paginating across dozens of screens to see all the available methods.  `Object` and `Session` are good examples.  I think they'd benefit greatly by an "Section Table-Of-Contents".  RST doesn't seem to support that though.

a- The narrative docs are amazing, possibly the best in Python.  But there is no "API Reference" style documentation online, where one can quickly navigate into a specific module or method.  I think it might be worth adding to the docs generation (online and local) some automatic API output , along the lines of what Twisted does with pydoctor or Pyramid does with Sphinx.

[Sqlalchemy-tickets] Issue #3137: Certain subqueries broken since 0.9.4 (zzzeek/sqlalchemy)

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

New issue 3137: Certain subqueries broken since 0.9.4
https://bitbucket.org/zzzeek/sqlalchemy/issue/3137/certain-subqueries-broken-since-094

thiefmaster:

The following query is causing an error inside the SQLAlchemy code since 0.9.4:

```
subquery = (select(['latitude'])
            .select_from(func.unnest(func.array_agg(Room.latitude))
                         .alias('latitude')).limit(1).as_scalar())
Room.query.with_entities(Room.building, subquery).group_by(Room.building).all()
```

Partial traceback:

```
  File ".../sqlalchemy/sql/selectable.py", line 429, in columns
    self._populate_column_collection()
  File ".../sqlalchemy/sql/selectable.py", line 992, in _populate_column_collection
    for col in self.element.columns._all_columns:
AttributeError: 'list' object has no attribute '_all_columns'
```

Related SO question with more details: http://stackoverflow.com/questions/24930155/strange-error-after-sqlalchemy-update-list-object-has-no-attribute-all-colu

[Sqlalchemy-tickets] Issue #3136: MySQL doesn't accept timezone offsets in datetime literals (zzzeek/sqlalchemy)

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

New issue 3136: MySQL doesn't accept timezone offsets in datetime literals
https://bitbucket.org/zzzeek/sqlalchemy/issue/3136/mysql-doesnt-accept-timezone-offsets-in

Will Ross:

Datetime literals with a timezone offset ("+0000" or "-0500") are invalid in MySQL. The issue is more visible with MySQL 5.6 because STRICT_TRANS_TABLES was added to the default configuration, causing statements that worked in older versions to fail. If no tzinfo is given, the datetime works fine. I'd have submitted a patch for this, but I'm not sure where to look in the code. That and I'm not sure it's even an actual bug, or intended behavior for backends that don't support timezones being passed timezone aware objects.

[Sqlalchemy-tickets] Issue #3135: Multiple dialect support in Call Level Interface (CLI/ODBC) connections (zzzeek/sqlalchemy)

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

New issue 3135: Multiple dialect support in Call Level Interface (CLI/ODBC) connections
https://bitbucket.org/zzzeek/sqlalchemy/issue/3135/multiple-dialect-support-in-call-level

David Jung:

Several enterprise RDBMs support connection via the Call Level Interface (CLI) protocol (ISO/IEC 9075-3:2003) - commonly referred to as ODBC or as various vendor-specific names, though technically ODBC is only Microsoft's implementation of CLI.

For example, the IBM PureData product line of data warehouses run Redhat Linux and a modified version of PostgreSQL internally, but provide only CLI (ODBC) as an external service protocol.

Such RDBMSs can be connected to via SQLAlchemy using pyodbc, but alas SQLAlchemy seems to assume it is connecting to an MSSQL server and only allows the mssql dialect to be specified.

For example:

```
#!python

engine = create_engine('postgresql+pyodbc://user:pw@server')
```
results in the exception:
NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgresql.pyodbc 

Whereas using:

```
#!python

engine = create_engine('mssql+pyodbc://user:pw@server')
```
will succeed in creating the connection, but attempted use results in the error:

```
#!python

ProgrammingError: (ProgrammingError) ('42S02', "[42S02] ERROR:  Function 'USER_NAME()' does not exist\n\tUnable to identify a function that satisfies the given argument types\n\tYou may need to add explicit typecasts (29) (SQLExecDirectW)") 'SELECT user_name()' () 
```
due to the use of an MSSQL specific function with PostgreSQL (the query issued was a simple SELECT * FROM table - so the SELECT user_name() was presumably generated by SQLAlchemy).

Please allow the specification of arbitrary dialects when connecting via the CLI/ODBC protocol (pyodbc) so that the dialect can be specified to match the RDBMS in question.

[Sqlalchemy-tickets] Issue #3134: pg8000 broken in 0.9.6 (zzzeek/sqlalchemy)

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

New issue 3134: pg8000 broken in 0.9.6
https://bitbucket.org/zzzeek/sqlalchemy/issue/3134/pg8000-broken-in-096

Maik Riechert:

I just upgraded from 0.9.4 to 0.9.6 and get the following error when running a query. I use pg8000.

```
#!python
  File "../site-packages/sqlalchemy/orm/query.py", line 2334, in first
    ret = list(self[0:1])
  File "../site-packages/sqlalchemy/orm/query.py", line 2201, in __getitem__
    return list(res)
  File "../site-packages/sqlalchemy/orm/query.py", line 2405, in __iter__
    return self._execute_and_instances(context)
  File "../site-packages/sqlalchemy/orm/query.py", line 2418, in _execute_and_instances
    close_with_result=True)
  File "../site-packages/sqlalchemy/orm/query.py", line 2409, in _connection_from_session
    **kw)
  File "../site-packages/sqlalchemy/orm/session.py", line 846, in connection
    close_with_result=close_with_result)
  File "../site-packages/sqlalchemy/orm/session.py", line 850, in _connection_for_bind
    return self.transaction._connection_for_bind(engine)
  File "../site-packages/sqlalchemy/orm/session.py", line 315, in _connection_for_bind
    conn = bind.contextual_connect()
  File "../site-packages/sqlalchemy/engine/base.py", line 1737, in contextual_connect
    self.pool.connect(),
  File "../site-packages/sqlalchemy/pool.py", line 332, in connect
    return _ConnectionFairy._checkout(self)
  File "../site-packages/sqlalchemy/pool.py", line 630, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "../site-packages/sqlalchemy/pool.py", line 433, in checkout
    rec = pool._do_get()
  File "../site-packages/sqlalchemy/pool.py", line 949, in _do_get
    return self._create_connection()
  File "../site-packages/sqlalchemy/pool.py", line 278, in _create_connection
    return _ConnectionRecord(self)
  File "../site-packages/sqlalchemy/pool.py", line 409, in __init__
    exec_once(self.connection, self)
  File "../site-packages/sqlalchemy/event/attr.py", line 247, in exec_once
    self(*args, **kw)
  File "../site-packages/sqlalchemy/event/attr.py", line 257, in __call__
    fn(*args, **kw)
  File "../site-packages/sqlalchemy/engine/strategies.py", line 156, in on_connect
    do_on_connect(conn)
  File "../site-packages/sqlalchemy/dialects/postgresql/base.py", line 1620, in connect
    self.set_isolation_level(conn, self.isolation_level)
  File "../site-packages/sqlalchemy/dialects/postgresql/pg8000.py", line 155, in set_isolation_level
    connection.connection.autocommit = False
AttributeError: 'Connection' object has no attribute 'connection'
```

[Sqlalchemy-tickets] Issue #3133: support DB's that magically get SQL expressions to come out in cursor.lastrowid (zzzeek/sqlalchemy)

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

New issue 3133: support DB's that magically get SQL expressions to come out in cursor.lastrowid
https://bitbucket.org/zzzeek/sqlalchemy/issue/3133/support-dbs-that-magically-get-sql

Mike Bayer:

key to this is that we should never put a "primary key" column in "postfetch".  If we define "postfetch" as, "columns we will fetch by selecting by PK", then that's how we should go.

patch:


```
#!diff

diff --git a/lib/sqlalchemy/orm/persistence.py b/lib/sqlalchemy/orm/persistence.py
index 6669efc..410bf33 100644
--- a/lib/sqlalchemy/orm/persistence.py
+++ b/lib/sqlalchemy/orm/persistence.py
@@ -595,13 +595,19 @@ def _emit_insert_statements(base_mapper, uowtransaction,
                                         execute(statement, params)
 
                 primary_key = result.context.inserted_primary_key
-
                 if primary_key is not None:
                     # set primary key attributes
                     for pk, col in zip(primary_key,
                                     mapper._pks_by_table[table]):
                         prop = mapper_rec._columntoproperty[col]
-                        if state_dict.get(prop.key) is None:
+                        existing = state_dict.get(prop.key)
+                        if existing is None or \
+                            (
+                                # inline executed statement somehow made
+                                # it into last inserted rec.  OK !
+                                pk is not None and
+                                isinstance(existing, sql.ClauseElement)
+                            ):
                             # TODO: would rather say:
                             #state_dict[prop.key] = pk
                             mapper_rec._set_state_attr_by_column(
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 384cf27..6cabe50 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -2080,7 +2080,8 @@ class SQLCompiler(Compiled):
                         self.returning.append(c)
                         value = self.process(value.self_group(), **kw)
                     else:
-                        self.postfetch.append(c)
+                        if not c.primary_key:
+                            self.postfetch.append(c)
                         value = self.process(value.self_group(), **kw)
                 values.append((c, value))
 

```

SQLite then lets us get at SQL expressions using lastrowid, who knew:


```
#!python

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

Base = declarative_base()


class Foo(Base):
    __tablename__ = 'foo'
    pk = Column(Integer, primary_key=True)
    bar = Column(Integer)

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

session = Session(e)

foo = Foo()
foo.pk = sql.select([sql.func.coalesce(sql.func.max(Foo.pk) + 1, 1)])
session.add(foo)
session.commit()

```



Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3132: Please add array_agg function (zzzeek/sqlalchemy)

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

New issue 3132: Please add array_agg function
https://bitbucket.org/zzzeek/sqlalchemy/issue/3132/please-add-array_agg-function

Christian H:

PostgreSQL has this nice array_agg function, and it'd be awesome if sqlalchemy would know that it's return type is of ARRAY(type of thing passed in).

BTW, Thanks for making SQLAlchemy!

[Sqlalchemy-tickets] Issue #3131: innerjoin=True annotation is ignored sometimes (zzzeek/sqlalchemy)

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

New issue 3131: innerjoin=True annotation is ignored sometimes
https://bitbucket.org/zzzeek/sqlalchemy/issue/3131/innerjoin-true-annotation-is-ignored

Gabor Gombas:

Hi,

See the attached test script - the only difference between classes C and D is the order of the definition of the two relations, yet querying C generates a LEFT OUTER JOIN to A, while querying D generates an inner JOIN as expected. Tested with rel_0_9; 0.8.1 generates the same query in both cases.

Gabor