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

[Sqlalchemy-tickets] Issue #3130: 0.9 regression: polymorphic subclasses from outer query contaminate the subquery (zzzeek/sqlalchemy)

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

New issue 3130: 0.9 regression: polymorphic subclasses from outer query contaminate the subquery
https://bitbucket.org/zzzeek/sqlalchemy/issue/3130/09-regression-polymorphic-subclasses-from

Gabor Gombas:

Hi,

The attached test case runs fine with 0.8.1, fails with rel_0_9 branch (tested commit: 2c41a702adca96d6847b72eb642ccbcfd21a0828). The subquery gets unexpectedly transformed into a join that contains polymorphic subclasses mentioned in the enclosing query only.

Gabor

[Sqlalchemy-tickets] Issue #3129: automap: Please include documentation how to remap the relationship (zzzeek/sqlalchemy)

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

New issue 3129: automap: Please include documentation how to remap the relationship
https://bitbucket.org/zzzeek/sqlalchemy/issue/3129/automap-please-include-documentation-how

Priit Laes:

When trying to automap legacy database which has some issues with foreign key naming I ran into following issue.

Having following two tables, it barfs because of naming conflict between column `table_b.table_a` and relationship `table_b->table_a`
```
#!python

table_a:
id

table_b:
id
table_a (foreign key to table_a.id)
```

Error message nicely tells me to `To resolve this, map the column to the class under a different name in the 'properties' dictionary.`, but automap part of documentation doesn't mention much about this case.

I have tried to override the class generation using following constructs, but still fail with error above:


```
#!python

class Table_B(Base):
    __tablename__ = 'table_b'
    table_a = Column(ForeignKey('table_a.id'))
```

[Sqlalchemy-tickets] Issue #3128: Oracle: allow running the tests using Kerberos authentication (zzzeek/sqlalchemy)

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

New issue 3128: Oracle: allow running the tests using Kerberos authentication
https://bitbucket.org/zzzeek/sqlalchemy/issue/3128/oracle-allow-running-the-tests-using

Gabor Gombas:

Hi,

test_oracle.py assumed the username can be extracted from the connection URL, but that's not true if Kerberos authentication is used (like `./sqla-nose.py --dburi=oracle://:@TNSNAME`). Ask the name of the connected user from the database instead.

Gabor

[Sqlalchemy-tickets] Issue #3127: Oracle: add support for table and index compression and bitmap indexes (zzzeek/sqlalchemy)

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

New issue 3127: Oracle: add support for table and index compression and bitmap indexes
https://bitbucket.org/zzzeek/sqlalchemy/issue/3127/oracle-add-support-for-table-and-index

Gabor Gombas:

Hi,

The 0.9 series made it a lot more difficult to create ad-hoc extensions to the existing dialects by disallowing parameters the core does not understand. We need a couple Oracle-specific features, so I spent some time generalizing them for inclusion in SQLAlchemy. I've tried to make the patch work for older Oracle releases too, but I have access to 11g only so I could not test anything older. I don't have DBA access either, so I was not able to run some of the tests that require extra privileges.

Gabor

[Sqlalchemy-tickets] Issue #3126: fallback to pg8000 if native postgres module is not installed (zzzeek/sqlalchemy)

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

New issue 3126: fallback to pg8000 if native postgres module is not installed
https://bitbucket.org/zzzeek/sqlalchemy/issue/3126/fallback-to-pg8000-if-native-postgres

Sorin Sbarnea:

Instead of hardcoding the backend engine to use for postgresql, sql alchemy should try to use pg8000 if the native module is not present.

[Sqlalchemy-tickets] Issue #3125: Enum objects linked to a specific metadata lock up SQLA when attempting to create_all on sqlite (zzzeek/sqlalchemy)

[Tiemo K. <iss...@bi...>]

New issue 3125: Enum objects linked to a specific metadata lock up SQLA when attempting to create_all on sqlite
https://bitbucket.org/zzzeek/sqlalchemy/issue/3125/enum-objects-linked-to-a-specific-metadata

Tiemo Kieft:

I have a schema that defines some Enum's, running create_all() works fine on Postgres, but on SQLite it locks up.

The code below demonstrates the problem:

```
#!python

from sqlalchemy import create_engine, Enum
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
e = Enum('foo', 'bar', metadata=Base.metadata)

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

Remove the explicit `metadata` kwarg from the Enum constructor solves the problem. I tried running the above code with echo=True, but it doesn't report anything.

[Sqlalchemy-tickets] Issue #3124: Infinite loop when using create_all() on an Enum(metadata=...) in SQLite (zzzeek/sqlalchemy)

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

New issue 3124: Infinite loop when using create_all() on an Enum(metadata=...) in SQLite
https://bitbucket.org/zzzeek/sqlalchemy/issue/3124/infinite-loop-when-using-create_all-on-an

eevee:

Reported here, but seen someone else experience it too: http://stackoverflow.com/q/24720380/17875

My diagnosis from figuring it out with the someone else on IRC:

    18:43 <!eevee> somehow calling self._on_metadata_create within the metadata's create event is adding itself as an event listener again
    18:44 <!eevee> because, i think, it re-creates a dialect-specific enum with the same args...
    18:44 <!eevee> looks pretty clearly like a bug wow
    18:44 <@Zhorken> it specifically hangs with SQLite
    18:44 <@Zhorken> or at least, not with Postgres
    18:45 <!eevee> ah, postgres has a real enum type; sqlite doesn't so it gets a fallback

[Sqlalchemy-tickets] Issue #3123: mysql reflection on python-2.6 causes error (zzzeek/sqlalchemy)

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

New issue 3123: mysql reflection on python-2.6 causes error
https://bitbucket.org/zzzeek/sqlalchemy/issue/3123/mysql-reflection-on-python-26-causes-error

barnabas79:

The essential problem is that mysql seems to return some table names as unicode, and in python 2.6, you can't use unicode as keys in a dict that you're unpacking for function kwargs - ie, in python 2.6, this:


```
#!python

def foo(**kwargs):
    print kwargs
foo(**{u'thing':1})
```

will raise an error:


```
#!python

TypeError: foo() keywords must be strings
```

To replicate:

* Set up a mysql server
    * On fedora 19, I just did:
        * yum install mysql-server
        * service mysqld start
* Using **python-2.6**, Run the following code (assuming you're connecting to the server running on the same machine, and that there's a database called "test", which should exist by default):


```
#!python

from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey

# connect to the "test" database, which should be set up by default

DEFAULT_DB_HOST = 'sv-db05'
engine = create_engine("mysql+mysqldb://127.0.0.1/test", echo=True)

# Set up a basic table

metadata = MetaData()
users = Table(
    'users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(255)),
)
metadata.create_all(engine)

# now reflect..

metadata2 = MetaData()
metadata2.reflect(engine, only=['users'])
```

This should raise this error:


```
#!python

Traceback (most recent call last):
  File "<stdin>", line 22, in <module>
    metadata2.reflect(engine, only=['users'])
  File "./sqlalchemy/lib/sqlalchemy/sql/schema.py", line 3288, in reflect
    Table(name, self, **reflect_opts)
  File "./sqlalchemy/lib/sqlalchemy/sql/schema.py", line 353, in __new__
    table._init(name, metadata, *args, **kw)
  File "./sqlalchemy/lib/sqlalchemy/sql/schema.py", line 426, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "./sqlalchemy/lib/sqlalchemy/sql/schema.py", line 438, in _autoload
    self, include_columns, exclude_columns
  File "./sqlalchemy/lib/sqlalchemy/engine/base.py", line 1239, in run_callable
    return callable_(self, *args, **kwargs)
  File "./sqlalchemy/lib/sqlalchemy/engine/default.py", line 356, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "./sqlalchemy/lib/sqlalchemy/engine/reflection.py", line 474, in reflecttable
    for col_d in self.get_columns(table_name, schema, **table.dialect_kwargs):
TypeError: get_columns() keywords must be strings
```

[Sqlalchemy-tickets] Issue #3122: TypeError: unbound method _compiler_dispatch() must be called with Text instance as first argument (zzzeek/sqlalchemy)

[Martijn v. O. <iss...@bi...>]

New issue 3122: TypeError: unbound method _compiler_dispatch() must be called with Text instance as first argument
https://bitbucket.org/zzzeek/sqlalchemy/issue/3122/typeerror-unbound-method

Martijn van Oosterhout:

There appears to be a problem using with_variant with sqlite. Consider this example program:

    import sqlalchemy
    from sqlalchemy import Text, Column, create_engine
    from sqlalchemy.ext.declarative import declarative_base
    
    #MyType = Text().with_variant(Text, 'postgresql')   # works
    MyType = Text().with_variant(Text, 'sqlite')    # breaks
    
    Base = declarative_base()
    
    Base.metadata.bind = create_engine('sqlite://')
    
    class TestTable(Base):
        __tablename__ = 'test'
        column = Column('test', MyType, primary_key=True)
    
    print sqlalchemy.__version__
    Base.metadata.create_all()

Dies for me with the following error on 0.8.4 and 0.9.6:

    Traceback (most recent call last):
      File "test2.py", line 17, in <module>
        Base.metadata.create_all()
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 2848, in create_all
        tables=tables)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1479, in _run_visitor
        conn._run_visitor(visitorcallable, element, **kwargs)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1122, in _run_visitor
        **kwargs).traverse_single(element)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 122, in traverse_single
        return meth(obj, **kw)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/ddl.py", line 70, in visit_metadata
        self.traverse_single(table, create_ok=True)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 122, in traverse_single
        return meth(obj, **kw)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/ddl.py", line 89, in visit_table
        self.connection.execute(schema.CreateTable(table))
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 662, in execute
        params)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 714, in _execute_ddl
        compiled = ddl.compile(dialect=dialect)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/expression.py", line 1920, in compile
        return self._compiler(dialect, bind=bind, **kw)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 2954, in _compiler
        return dialect.ddl_compiler(dialect, self, **kw)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/interfaces.py", line 804, in __init__
        self.string = self.process(self.statement, **compile_kwargs)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/interfaces.py", line 823, in process
        return obj._compiler_dispatch(self, **kwargs)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 80, in _compiler_dispatch
        return meth(self, **kw)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 1941, in visit_create_table
        and not first_pk)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/interfaces.py", line 823, in process
        return obj._compiler_dispatch(self, **kwargs)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 80, in _compiler_dispatch
        return meth(self, **kw)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 1980, in visit_create_column
        first_pk=first_pk
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/dialects/sqlite/base.py", line 484, in get_column_specification
        coltype = self.dialect.type_compiler.process(column.type)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/interfaces.py", line 869, in process
        return type_._compiler_dispatch(self)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 80, in _compiler_dispatch
        return meth(self, **kw)
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 2366, in visit_type_decorator
        return self.process(type_.type_engine(self.dialect))
      File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/interfaces.py", line 869, in process
        return type_._compiler_dispatch(self)
    TypeError: unbound method _compiler_dispatch() must be called with Text instance as first argument (got SQLiteTypeCompiler instance instead)

If you use the line involving 'postgresql' instead, it works just fine.

[Sqlalchemy-tickets] Issue #3121: mysqldb can do unicode statements now, update that? (zzzeek/sqlalchemy)

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

New issue 3121: mysqldb can do unicode statements now, update that?
https://bitbucket.org/zzzeek/sqlalchemy/issue/3121/mysqldb-can-do-unicode-statements-now

Mike Bayer:

setting `supports_unicode_statements = True` even without a charset works ?  test that out.   maybe do version detection, not sure.   this is 1.0 b.c. it's a surprise.

[Sqlalchemy-tickets] Issue #3120: sql.true() does not work for IBM DB2 (zzzeek/sqlalchemy)

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

New issue 3120: sql.true() does not work for IBM DB2
https://bitbucket.org/zzzeek/sqlalchemy/issue/3120/sqltrue-does-not-work-for-ibm-db2

xtdxhw:

When using sql.true() in query filter to query data in db2. It parse the sql.true() to 'true', which is a string. However, if changed sql.true() to 'True', it works well.

E.g. the following code works well.

```
#!python

class TT(Base):
    __tablename__ = 'tt'

    id = Column(Integer, primary_key = True)
    btest = Column('btest', Boolean, default = True)

db2 = sqlalchemy.create_engine('ibm_db_sa://neutron:neutron@localhost:50000/ntrnovs?charset=utf8')
session = sessionmaker( autocommit = False, autoflush = False, bind = db2)()
print session.query(TT).filter(TT.btest==True).all()[0].btest
```
However, if I changed the 'True' to 'sql.true()', it raise the following errors.

```
#!python
[root@rhel62 ~]# python db2_select.py 
Traceback (most recent call last):
  File "db2_select.py", line 18, in <module>
    print session.query(TT).filter(TT.btest==sql.true()).all()[0].btest
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py", line 2115, in all
    return list(self)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py", line 2227, in __iter__
    return self._execute_and_instances(context)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py", line 2242, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1449, in execute
    params)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1584, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1698, in _execute_context
    context)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1691, in _execute_context
    context)
  File "/usr/lib/python2.6/site-packages/ibm_db_sa/ibm_db.py", line 104, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/lib64/python2.6/site-packages/ibm_db_dbi.py", line 1334, in execute
    self._set_cursor_helper()
  File "/usr/lib64/python2.6/site-packages/ibm_db_dbi.py", line 1217, in _set_cursor_helper
    raise self.messages[len(self.messages) - 1]
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0401N  The data types of the operands for the operation "=" are not compatible or comparable.  SQLSTATE=42818 SQLCODE=-401 'SELECT tt.id AS tt_id, tt.btest AS tt_btest \nFROM tt \nWHERE tt.btest = true' ()
```
[root@rhel62 ~]# rpm -qa | grep sqlalchemy
python-sqlalchemy-0.7.9-1

[Sqlalchemy-tickets] Issue #3119: sqlalchemy.dialects.postgresql.BYTEA has incorrect documentation (zzzeek/sqlalchemy)

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

New issue 3119: sqlalchemy.dialects.postgresql.BYTEA has incorrect documentation
https://bitbucket.org/zzzeek/sqlalchemy/issue/3119/sqlalchemydialectspostgresqlbytea-has

jvanasco:

URL

http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html#sqlalchemy.dialects.postgresql.BYTEA

DOCS:
"""
Construct a LargeBinary type.

Parameters:	length – optional, a length for the column for use in DDL statements, for those BLOB types that accept a length (i.e. MySQL). It does not produce a small BINARY/VARBINARY type - use the BINARY/VARBINARY types specifically for those. May be safely omitted if no CREATE TABLE will be issued. Certain databases may require a length for use in DDL, and will raise an exception when the CREATE TABLE DDL is issued.
"""

Issue:

BYTEA isn't a BLOB.  ( see http://www.postgresql.org/docs/9.1/static/datatype-binary.html )

The sql standard "BLOB" isn't really in postgres.  From what i've read, it should be something between a "postgres BLOB" ( as defined by the `lo` extension - http://www.postgresql.org/docs/9.1/static/lo.html ) and a BYTEA.

But the BYTEA format is not considered to be a BLOB within postges, and postgres has a separate "blob", so the docs shouldn't refer to this a BLOB .  Possibly only mention it as the "binary Byte Array".




There's a separate discussion about actual BLOB support here - https://groups.google.com/d/topic/sqlalchemy/4TCg_TVLgHc/discussion

[Sqlalchemy-tickets] Issue #3118: update SqlAlchemy Website to link to existing issues, not create new ones. (zzzeek/sqlalchemy)

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

New issue 3118: update SqlAlchemy Website to link to existing issues, not create new ones.
https://bitbucket.org/zzzeek/sqlalchemy/issue/3118/update-sqlalchemy-website-to-link-to

jvanasco:

The "Reporting Bugs" section ( http://www.sqlalchemy.org/participate.html#bugs ) has multiple links to "Create a New Issue" ( https://bitbucket.org/zzzeek/sqlalchemy/issues/new ).  

it does not have any links to "active issues"

It would probably be better to link to the issues index instead ( https://bitbucket.org/zzzeek/sqlalchemy/issues ).  

Most open source projects use the "Tickets View" as a gateway for people to report something.

[Sqlalchemy-tickets] Issue #3117: AttributeError: 'Child' object has no attribute 'parent_id' (zzzeek/sqlalchemy)

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

New issue 3117: AttributeError: 'Child' object has no attribute 'parent_id'
https://bitbucket.org/zzzeek/sqlalchemy/issue/3117/attributeerror-child-object-has-no

Gabriel Becedillas:

I get an error when executing an update ONLY after eager loading an association object.
I can reproduce this on 0.9.4 and 0.9.6.

In order to reproduce it you need to create a database named 'bugtest'.

The output should look like this:


```
#!python

Insert data
Update ok
Update fails on 0.9.4
Traceback (most recent call last):
  File "bugtest.py", line 73, in <module>
    trigger_bug(True)
  File "bugtest.py", line 64, in trigger_bug
    offending_update(session, parent)
  File "bugtest.py", line 52, in offending_update
    Child.name: "Doe",
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/query.py", line 2748, in update
    update_op.exec_()
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/persistence.py", line 887, in exec_
    self._do_pre_synchronize()
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/persistence.py", line 948, in _do_pre_synchronize
    eval_condition(obj)]
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/evaluator.py", line 72, in evaluate
    value = sub_evaluate(obj)
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/evaluator.py", line 97, in evaluate
    left_val = eval_left(obj)
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/evaluator.py", line 54, in <lambda>
    return lambda obj: get_corresponding_attr(obj)
AttributeError: 'Child' object has no attribute 'parent_id'

```

[Sqlalchemy-tickets] Issue #3116: NoSuchColumnError when creating label on subquery column (zzzeek/sqlalchemy)

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

New issue 3116: NoSuchColumnError when creating label on subquery column
https://bitbucket.org/zzzeek/sqlalchemy/issue/3116/nosuchcolumnerror-when-creating-label-on

lsowen:

When applying an alias to a column (via ``label``) on a subquery, I get a ``NoSuchColumnError`` in each of the four cases shown below.

```
#!python

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.schema import MetaData
from datetime import datetime

import sqlalchemy.types as types
from sqlalchemy.schema import Column

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine, func



BASE = declarative_base(metadata=MetaData(schema='redacted'))

class WS_TABLE(BASE):
    __tablename__ = 'WS_TABLE' 

    STARTDATETIME_CS = Column(types.DATETIME, primary_key = True)
    STOPDATETIME = Column('STOPDATETIME_CS', types.DATETIME, primary_key = True)   
    ACTUALMW = Column(types.INTEGER)
  
 
 
def main():
    username = 'redacted'
    password = 'redacted'
    
    engine = create_engine('oracle+cx_oracle://{0}:{1}@{2}'.format(username, password, 'redacted'), echo=True)
    session = sessionmaker(bind=engine)()

    
    sub_query = session.query(WS_TABLE)\
        .filter(WS_TABLE.STARTDATETIME_CS < datetime(2014,6,2))\
        .filter(WS_TABLE.STOPDATETIME > datetime(2014,6,1))\
        .subquery()
    
    ## CASE A   
    #print(session.query(sub_query.c.STARTDATETIME_CS.label('STARTDATETIME_CS')).all())
    
    ## CASE B
    #print(session.query(sub_query.c.STOPDATETIME_CS.label('STOPDATETIME')).all())
    
    ## CASE C
    #print(session.query(sub_query.c.STOPDATETIME_CS.label('STOPDATETIME_CS')).all())
    
    ## CASE D
    print(session.query(func.sum(sub_query.c.ACTUALMW).label('ACTUALMW')).all())

if __name__ == '__main__':
    main()

```

The traceback is the same (with different column names) in each of the four cases:


```
#!text

Traceback (most recent call last):
  File "C:\Python33\lib\site-packages\sqlalchemy\engine\result.py", line 70, in __getitem__
    processor, obj, index = self._keymap[key]
KeyError: <sqlalchemy.sql.elements.Label object at 0x000000000364BA20>

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\m02332\workspace\test_automation\test_case.py", line 47, in <module>
    main()
  File "C:\Users\m02332\workspace\test_automation\test_case.py", line 44, in main
    print(session.query(func.sum(sub_query.c.ACTUALMW).label('ACTUALMW')).all())
  File "C:\Python33\lib\site-packages\sqlalchemy\orm\query.py", line 2293, in all
    return list(self)
  File "C:\Python33\lib\site-packages\sqlalchemy\orm\loading.py", line 75, in instances
    labels) for row in fetch]
  File "C:\Python33\lib\site-packages\sqlalchemy\orm\loading.py", line 75, in <listcomp>
    labels) for row in fetch]
  File "C:\Python33\lib\site-packages\sqlalchemy\orm\loading.py", line 74, in <listcomp>
    rows = [util.KeyedTuple([proc(row, None) for proc in process],
  File "C:\Python33\lib\site-packages\sqlalchemy\orm\query.py", line 3469, in proc
    return row[column]
  File "C:\Python33\lib\site-packages\sqlalchemy\engine\result.py", line 72, in __getitem__
    processor, obj, index = self._parent._key_fallback(key)
  File "C:\Python33\lib\site-packages\sqlalchemy\engine\result.py", line 332, in _key_fallback
    expression._string_or_unprintable(key))
sqlalchemy.exc.NoSuchColumnError: 'Could not locate column in row for column \'sum(anon_1."ACTUALMW")\''
```


Versions:

* SQLAlchemy: 0.9.6
* cx-Oracle: 5.1.2

[Sqlalchemy-tickets] Issue #3115: mapper has_property method to do return True when hybrid_property (zzzeek/sqlalchemy)

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

New issue 3115: mapper has_property method to do return True when hybrid_property
https://bitbucket.org/zzzeek/sqlalchemy/issue/3115/mapper-has_property-method-to-do-return

delegans:

When I try to get property from mapper by name I can't do it when hybrid_property name specified.

```
#!python

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

metadata = MetaData()
Base = declarative_base(metadata=metadata)    

class Duration(Base):
    __tablename__ = "duration"

    pk = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

    @hybrid_property
    def duration(obj):
        return obj.name    

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

duration = Duration(name="Test")
session.add(duration)
session.commit()    

print duration.__mapper__.has_property('duration') # Returns False, needs True
```
As you can see the has_property('duration') does not see the hybrid_property duration and returns False instead True.

Is it a bug?

Source: http://stackoverflow.com/questions/24579185/sqlalchemy-how-mapper-has-property-method-to-do-return-true-when-hybrid-propert

[Sqlalchemy-tickets] Issue #3114: Typo in example in ORM documentation (zzzeek/sqlalchemy)

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

New issue 3114: Typo in example in ORM documentation
https://bitbucket.org/zzzeek/sqlalchemy/issue/3114/typo-in-example-in-orm-documentation

wolfgang p:

On
http://docs.sqlalchemy.org/en/rel_0_9/orm/loading.html#sqlalchemy.orm.subqueryload_all
there is an example for method chaining. It contains a typo.
There ought to be a comma (not a period) between the two subqueryloads, I think.

[Sqlalchemy-tickets] Issue #3113: Warning for custom insert (MySQL) (zzzeek/sqlalchemy)

[theking-laptop <iss...@bi...>]

New issue 3113: Warning for custom insert (MySQL)
https://bitbucket.org/zzzeek/sqlalchemy/issue/3113/warning-for-custom-insert-mysql

theking-laptop:

I use MySQL as my database backend and I really want to use `INSERT... ON DUPLICATE KEY UPDATE` feature. This is my code:

```
@compiles(Insert, 'mysql')
def suffix_insert(insert, compiler, **kw):
    stmt = compiler.visit_insert(insert, **kw)
    if 'on_duplicate_key_update_cols' in insert.kwargs:
        update_cols = insert.kwargs['on_duplicate_key_update_cols']
        update_cols_length = len(update_cols)
        if update_cols_length > 0:
            stmt += ' ON DUPLICATE KEY UPDATE'
            stmt += " %s=VALUES(%s)" % (update_cols[0], update_cols[0])
            for i in xrange(1, update_cols_length):
                stmt += ",%s=VALUES(%s)" % (update_cols[i], update_cols[i])
    return stmt
```

It works perfectly when I use SqlAlchemy 0.9.1 or below. With higher version, everything is still fine, but I got warning: 
```
lib/python2.7/site-packages/sqlalchemy/sql/dml.py:465: SAWarning: Can't validate argument 'on_duplicate_key_update'; can't locate any SQLAlchemy dialect named 'on'
  self._validate_dialect_kwargs(dialect_kw)
```

How can I workaround this problem?

[Sqlalchemy-tickets] Issue #3112: UnicodeDecodeError with pg8000 1.9.10 (zzzeek/sqlalchemy)

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

New issue 3112: UnicodeDecodeError with pg8000 1.9.10
https://bitbucket.org/zzzeek/sqlalchemy/issue/3112/unicodedecodeerror-with-pg8000-1910

Raphael Mutschler:

since i've updated pg8000 from 1.08 to 1.9.10 i get UnicodeDecodeError when i query a databse that contains any unicode chars (in this special case german umlauts) setting the `encoding='utf-8'` on option in sqlalchemy seems to be ignored completely since 1.9

some traceback:
```
Traceback (most recent call last):
  File "debug.py", line 37, in <module>
    main.import_mediaserver()
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/synodlnatrakt/main.py", line 207, in import_mediaserver
    for result in dbresult:
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/sqlalchemy/orm/query.py", line 2176, in __iter__
    return self._execute_and_instances(context)
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/sqlalchemy/orm/query.py", line 2191, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/sqlalchemy/engine/base.py", line 1450, in execute
    params)
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/sqlalchemy/engine/base.py", line 1583, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/sqlalchemy/engine/base.py", line 1690, in _execute_context
    context)
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/sqlalchemy/engine/default.py", line 335, in do_execute
    cursor.execute(statement, parameters)
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/pg8000/core.py", line 531, in execute
    self._c.execute(self, operation, args)
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/pg8000/core.py", line 1554, in execute
    self.handle_messages(cursor)
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/pg8000/core.py", line 1619, in handle_messages
    self._read(data_len - 4), cursor)
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/pg8000/core.py", line 1607, in handle_DATA_ROW
    row.append(func(data, data_idx, vlen))
  File "/volume1/@appstore/synodlnatrakt/share/SynoDLNAtrakt/lib/pg8000/core.py", line 976, in text_recv
    data[offset: offset + length], self._client_encoding)
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 64: ordinal not in range(128)
```

with 1.08 it works perfeclty but sadly 1.08 gives me a different error sometimes:
```pg8000.errors.NotSupportedError: type oid 705 not mapped to py type```

[Sqlalchemy-tickets] Issue #3111: default Unicode to use UNICODE charset in mysql (zzzeek/sqlalchemy)

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

New issue 3111: default Unicode to use UNICODE charset in mysql
https://bitbucket.org/zzzeek/sqlalchemy/issue/3111/default-unicode-to-use-unicode-charset-in

Mike Bayer:

see https://github.com/zzzeek/sqlalchemy/pull/102

[Sqlalchemy-tickets] Issue #3110: Add explicit link to documentation page (zzzeek/sqlalchemy)

[Andrea D'A. <iss...@bi...>]

New issue 3110: Add explicit link to documentation page
https://bitbucket.org/zzzeek/sqlalchemy/issue/3110/add-explicit-link-to-documentation-page

Andrea D'Amore:

Please add an explicit link to [sqlite dialect documentation page][1] in [metadata documentation page][2], I was mislead by

    See the SQLite dialect documentation for information on SQLite’s AUTOINCREMENT.

and went checking [www.sqlite.org/autoinc.html](http://www.sqlite.org/autoinc.html) instead

[1]: http://docs.sqlalchemy.org/en/rel_0_9/dialects/sqlite.html?highlight=autoincrement#auto-incrementing-behavior
[2]: http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html#sqlalchemy.schema.Column.params.autoincrement

[Sqlalchemy-tickets] Issue #3109: PostgreSQL ARRAY_AGG TypeError (zzzeek/sqlalchemy)

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

New issue 3109: PostgreSQL ARRAY_AGG TypeError
https://bitbucket.org/zzzeek/sqlalchemy/issue/3109/postgresql-array_agg-typeerror

Scott Milliken:

I'm getting a TypeError at `util/_collections.py:729` in 0.9.6 (also tested in 0.9.4) when using the postgresql ARRAY_AGG function and a GROUP BY. Here's a repro script:

```
import sqlalchemy
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session

print('version: %s' % sqlalchemy.__version__)

Base = declarative_base()

class Parent(Base):
    __tablename__ = 'parent'

    id = Column(Integer, primary_key=True)
    name = Column(String)


class Child(Base):
    __tablename__ = 'child'

    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey(Parent.id))

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

conn = engine.connect()
session = Session(conn)
Base.metadata.create_all(conn)
session.add(Parent(id=1, name='Test'))
session.flush()
session.add(Child(id=1, parent_id=1))
session.flush()
session.query(Parent, func.ARRAY_AGG(Child.id)).filter(Parent.id == Child.parent_id).group_by(Parent).all()
```

And the output:

```
version: 0.9.6
2014-07-01 23:33:35,088 INFO sqlalchemy.engine.base.Engine select version()
2014-07-01 23:33:35,088 INFO sqlalchemy.engine.base.Engine {}
2014-07-01 23:33:35,090 INFO sqlalchemy.engine.base.Engine select current_schema()
2014-07-01 23:33:35,090 INFO sqlalchemy.engine.base.Engine {}
2014-07-01 23:33:35,092 INFO sqlalchemy.engine.base.Engine {}                                                                                                                                            
2014-07-01 23:33:35,093 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-07-01 23:33:35,093 INFO sqlalchemy.engine.base.Engine {}
2014-07-01 23:33:35,093 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2014-07-01 23:33:35,094 INFO sqlalchemy.engine.base.Engine {}
2014-07-01 23:33:35,096 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s
2014-07-01 23:33:35,096 INFO sqlalchemy.engine.base.Engine {'name': u'parent'}
2014-07-01 23:33:35,098 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s
2014-07-01 23:33:35,098 INFO sqlalchemy.engine.base.Engine {'name': u'child'}
2014-07-01 23:33:35,100 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2014-07-01 23:33:35,101 INFO sqlalchemy.engine.base.Engine INSERT INTO parent (id, name) VALUES (%(id)s, %(name)s)
2014-07-01 23:33:35,101 INFO sqlalchemy.engine.base.Engine {'name': 'Test', 'id': 1}
2014-07-01 23:33:35,104 INFO sqlalchemy.engine.base.Engine INSERT INTO child (id, parent_id) VALUES (%(id)s, %(parent_id)s)
2014-07-01 23:33:35,104 INFO sqlalchemy.engine.base.Engine {'parent_id': 1, 'id': 1}
2014-07-01 23:33:35,107 INFO sqlalchemy.engine.base.Engine SELECT parent.id AS parent_id, parent.name AS parent_name, ARRAY_AGG(child.id) AS "ARRAY_AGG_1" 
FROM parent, child 
WHERE parent.id = child.parent_id GROUP BY parent.id, parent.name
2014-07-01 23:33:35,108 INFO sqlalchemy.engine.base.Engine {}

Traceback (most recent call last):
  File "./bin/python", line 123, in <module>
    exec(compile(__file__f.read(), __file__, "exec"))
  File "test.py", line 32, in <module>
    session.query(Parent, func.ARRAY_AGG(Child.id)).filter(Parent.id == Child.parent_id).group_by(Parent).all()
  File "eggs/SQLAlchemy-0.9.6-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2293, in all
    return list(self)
  File "eggs/SQLAlchemy-0.9.6-py2.7-linux-x86_64.egg/sqlalchemy/orm/loading.py", line 78, in instances
    rows = util.unique_list(rows, filter_fn)
  File "eggs/SQLAlchemy-0.9.6-py2.7-linux-x86_64.egg/sqlalchemy/util/_collections.py", line 729, in unique_list
    if hashfunc(x) not in seen
TypeError: unhashable type: 'list'
```

[Sqlalchemy-tickets] Issue #3108: Difference between flushing in outer transaction and committing in nested transaction (zzzeek/sqlalchemy)

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

New issue 3108: Difference between flushing in outer transaction and committing in nested transaction
https://bitbucket.org/zzzeek/sqlalchemy/issue/3108/difference-between-flushing-in-outer

Torsten Landschoff:

After introducing nested transactions into our software I was greeted with a flurry of ObjectDeletedErrors. It turned out that it makes a huge difference if a change is committed inside a nested transaction or flushed inside the main transaction.

>From the database side I expect these to be equivalent:

## Original instructions ##

```
#!sql

begin;
-- add stuff to the database
rollback;
```

## Updated instructions using nested transactions ##

```
#!sql

begin;
savepoint inner;
-- add stuff to the database
release savepoint inner;
rollback;
```

# Rationale #

When the instructions inside the nested transaction complete successfully, it should make no difference if they are inside a savepoint demarcation or not.

# Example code #

Please have a look at the attached file nested_rollback.rst. It runs to completion here without error but illustrates the current problem. You can run it using

```
#!sh

python -m doctest nested_rollback.rst
```

This seems to be related to issue #2658.

[Sqlalchemy-tickets] Issue #3107: MySQL server gone away (zzzeek/sqlalchemy)

[Juda K. <iss...@bi...>]

New issue 3107: MySQL server gone away
https://bitbucket.org/zzzeek/sqlalchemy/issue/3107/mysql-server-gone-away

Juda Kaleta:

We are using SQLAlchemy to pool connections to MySQL server (together with django-mysqlpool). After upgrading to 0.9.6 many error like this have started to appear frequently.

```
sqlalchemy.pool.QueuePool	Exception during reset or similar

Traceback (most recent call last):
  File "/home/mcat/venv_sk/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 557, in _finalize_fairy
    fairy._reset(pool, echo)
  File "/home/mcat/venv_sk/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 688, in _reset
    pool._dialect.do_rollback(self)
  File "/home/mcat/venv_sk/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 79, in do_rollback
    dbapi_connection.rollback()
OperationalError: (2006, 'MySQL server has gone away')
```

Recycle and other arguments passed to pool haven't changed and should be correct. 

[Sqlalchemy-tickets] Issue #3106: subquery load on with_polymorphic entity (zzzeek/sqlalchemy)

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

New issue 3106: subquery load on with_polymorphic entity
https://bitbucket.org/zzzeek/sqlalchemy/issue/3106/subquery-load-on-with_polymorphic-entity

Mike Bayer:


```
#!python

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

Base = declarative_base()


class Employee(Base):
    __tablename__ = "employee"
    id = Column(Integer, primary_key=True)
    type = Column(String(100))
    cars = relationship("Car")
    __mapper_args__ = {
        "polymorphic_on": type,
    }

class Car(Base):
    __tablename__ = "car"
    id = Column(Integer, primary_key=True)
    employee_id = Column(Integer, ForeignKey(Employee.id))

class Engineer(Employee):
    __tablename__ = "engineer"
    id = Column(Integer, ForeignKey(Employee.id), primary_key=True)
    specialty = Column(String(100))
    __mapper_args__ = {
        "polymorphic_identity": "engineer",
    }

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

session = Session(c)

session.add(Engineer())
session.flush()

E = with_polymorphic(Employee, [Engineer])

# needs:
# session.query(E).select_from(E).options(subqueryload(E.cars)).order_by(E.Engineer.specialty).first()

session.query(E).options(subqueryload(E.cars)).order_by(Engineer.specialty).first()
```

patch:


```
#!diff

--- a/lib/sqlalchemy/orm/strategies.py
+++ b/lib/sqlalchemy/orm/strategies.py
@@ -725,7 +725,7 @@ class SubqueryLoader(AbstractRelationshipLoader):
         left_alias = self._generate_from_original_query(
                             orig_query, leftmost_mapper,
                             leftmost_attr, leftmost_relationship,
-                            entity.mapper
+                            entity.entity_zero
         )
 
         # generate another Query that will join the
@@ -744,7 +744,6 @@ class SubqueryLoader(AbstractRelationshipLoader):
                     self._prep_for_joins(left_alias, subq_path)
         q = q.order_by(*local_attr)
         q = q.add_columns(*local_attr)
-
         q = self._apply_joins(q, to_join, left_alias,
                             parent_alias, effective_entity)
 
@@ -779,7 +778,7 @@ class SubqueryLoader(AbstractRelationshipLoader):
     def _generate_from_original_query(self,
             orig_query, leftmost_mapper,
             leftmost_attr, leftmost_relationship,
-            entity_mapper
+            orig_entity
     ):
         # reformat the original query
         # to look only for significant columns
@@ -787,9 +786,8 @@ class SubqueryLoader(AbstractRelationshipLoader):
 
         # set a real "from" if not present, as this is more
         # accurate than just going off of the column expression
-        if not q._from_obj and entity_mapper.isa(leftmost_mapper):
-            q._set_select_from([entity_mapper], False)
-
+        if not q._from_obj and orig_entity.mapper.isa(leftmost_mapper):
+            q._set_select_from([orig_entity], False)
         target_cols = q._adapt_col_list(leftmost_attr)
 
         # select from the identity columns of the outer

```