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

[Sqlalchemy-tickets] Issue #3105: get_all_pending TypeError introduced in SQLAlchemy 0.9.5 (zzzeek/sqlalchemy)

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

New issue 3105: get_all_pending TypeError introduced in SQLAlchemy 0.9.5
https://bitbucket.org/zzzeek/sqlalchemy/issue/3105/get_all_pending-typeerror-introduced-in

Wichert Akkerman:

After upgrading from 0.9.4 to 0.9.5 or 0.9.6 I am suddenly seeing an error appear in my tests:

```
src/lets_lynk/factory.py:51: in factory
>       return query.first()
../../Library/buildout/eggs/SQLAlchemy-0.9.5-py2.7-macosx-10.9-x86_64.egg/sqlalchemy/orm/query.py:2334: in first
>           ret = list(self[0:1])
../../Library/buildout/eggs/SQLAlchemy-0.9.5-py2.7-macosx-10.9-x86_64.egg/sqlalchemy/orm/query.py:2201: in __getitem__
>               return list(res)
../../Library/buildout/eggs/SQLAlchemy-0.9.5-py2.7-macosx-10.9-x86_64.egg/sqlalchemy/orm/query.py:2404: in __iter__
>           self.session._autoflush()
../../Library/buildout/eggs/SQLAlchemy-0.9.5-py2.7-macosx-10.9-x86_64.egg/sqlalchemy/orm/session.py:1188: in _autoflush
>               self.flush()
../../Library/buildout/eggs/SQLAlchemy-0.9.5-py2.7-macosx-10.9-x86_64.egg/sqlalchemy/orm/session.py:1907: in flush
>           self._flush(objects)
../../Library/buildout/eggs/SQLAlchemy-0.9.5-py2.7-macosx-10.9-x86_64.egg/sqlalchemy/orm/session.py:2025: in _flush
>               transaction.rollback(_capture_exception=True)
../../Library/buildout/eggs/SQLAlchemy-0.9.5-py2.7-macosx-10.9-x86_64.egg/sqlalchemy/util/langhelpers.py:57: in __exit__
>           compat.reraise(exc_type, exc_value, exc_tb)
../../Library/buildout/eggs/SQLAlchemy-0.9.5-py2.7-macosx-10.9-x86_64.egg/sqlalchemy/orm/session.py:1989: in _flush
>               flush_context.execute()
../../Library/buildout/eggs/SQLAlchemy-0.9.5-py2.7-macosx-10.9-x86_64.egg/sqlalchemy/orm/unitofwork.py:350: in execute
>       postsort_actions = self._generate_actions()
../../Library/buildout/eggs/SQLAlchemy-0.9.5-py2.7-macosx-10.9-x86_64.egg/sqlalchemy/orm/unitofwork.py:324: in _generate_actions
>               for rec in cycles
../../Library/buildout/eggs/SQLAlchemy-0.9.5-py2.7-macosx-10.9-x86_64.egg/sqlalchemy/orm/unitofwork.py:324: in <genexpr>
>   for rec in cycles
../../Library/buildout/eggs/SQLAlchemy-0.9.5-py2.7-macosx-10.9-x86_64.egg/sqlalchemy/orm/unitofwork.py:541: in per_state_flush_actions
>           dep.per_state_flush_actions(uow, states_for_prop, False)
../../Library/buildout/eggs/SQLAlchemy-0.9.5-py2.7-macosx-10.9-x86_64.egg/sqlalchemy/orm/dependency.py:166: in per_state_flush_actions
>                                       else attributes.PASSIVE_NO_INITIALIZE)
E           TypeError: get_all_pending() takes exactly 3 arguments (4 given)
```

[Sqlalchemy-tickets] Issue #3104: transaction replay extension (zzzeek/sqlalchemy)

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

New issue 3104: transaction replay extension
https://bitbucket.org/zzzeek/sqlalchemy/issue/3104/transaction-replay-extension

Mike Bayer:

see attached.

[Sqlalchemy-tickets] Issue #3103: NoSuchColumnError: "Could not locate column in row for column (zzzeek/sqlalchemy)

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

New issue 3103: NoSuchColumnError: "Could not locate column in row for column
https://bitbucket.org/zzzeek/sqlalchemy/issue/3103/nosuchcolumnerror-could-not-locate-column

Praveen Arimbrathodiyil:

 File "/usr/lib/python2.7/dist-packages/gnukhataserver/rpc_reports.py", line 134, in xmlrpc_getLedger
    grandTotal =float(balanceRow["total_CrBal"])  + float(balanceRow["curbal"])
  File "/usr/lib/python2.7/dist-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 'total_CrBal'"
org.apache.xmlrpc.XmlRpcException: error

I'm packaging gnukhata (gnukhata.org) for debian and hit this error. Krishnakant Mane (core developer of gnukhata) said everything is working fine with sqlalchemy 0.7.x. Debian sid has sqlalchemy 0.9.6. He also mentioned you have promised this would be fixed in the next release. Hopefully we can get a patch sooner so it debian and ubuntu packages can be updated. Currently we are forced to create a different repository and ship 0.7.x version of sqlalchemy.  

[Sqlalchemy-tickets] Issue #3102: TypeError: Cannot create a consistent method resolution order (MRO) for bases Comparator, TDComparator (zzzeek/sqlalchemy)

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

New issue 3102: TypeError: Cannot create a consistent method resolution order (MRO) for bases Comparator, TDComparator
https://bitbucket.org/zzzeek/sqlalchemy/issue/3102/typeerror-cannot-create-a-consistent

Martijn van Oosterhout:

I received the above error trying to generate query comparing a variant type. Test program:

=====
import sqlalchemy
from sqlalchemy.types import TypeDecorator
from sqlalchemy import Text, Column
from sqlalchemy.ext.declarative import declarative_base

class TestType(TypeDecorator):
    impl = Text

MyType = TestType().with_variant(Text, 'postgresql')

Base = declarative_base()

class TestTable(Base):
    __tablename__ = 'test'
    column = Column('test', MyType, primary_key=True)

print sqlalchemy.__version__
TestTable.column == None    # Dies
=====

Exception:
=====
0.9.6
Traceback (most recent call last):
  File "test.py", line 18, in <module>
    TestTable.column == None
  File "/tmp/test/local/lib/python2.7/site-packages/sqlalchemy/sql/operators.py", line 296, in __eq__
    return self.operate(eq, other)
  File "/tmp/test/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 171, in operate
    return op(self.comparator, *other, **kwargs)
  File "/tmp/test/local/lib/python2.7/site-packages/sqlalchemy/sql/operators.py", line 296, in __eq__
    return self.operate(eq, other)
  File "/tmp/test/local/lib/python2.7/site-packages/sqlalchemy/orm/properties.py", line 251, in operate
    return op(self.__clause_element__(), *other, **kwargs)
  File "/tmp/test/local/lib/python2.7/site-packages/sqlalchemy/sql/annotation.py", line 90, in __eq__
    return self.__element.__class__.__eq__(self, other)
  File "/tmp/test/local/lib/python2.7/site-packages/sqlalchemy/sql/operators.py", line 296, in __eq__
    return self.operate(eq, other)
  File "/tmp/test/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 664, in operate
    return op(self.comparator, *other, **kwargs)
  File "/tmp/test/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 712, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/tmp/test/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 650, in comparator
    return self.type.comparator_factory(self)
  File "/tmp/test/local/lib/python2.7/site-packages/sqlalchemy/sql/type_api.py", line 635, in comparator_factory
    {})
TypeError: Cannot create a consistent method resolution
order (MRO) for bases Comparator, TDComparator
=====

I can fix it in type_api.py by reversing the order of the bases classes, but I'm not sure if that has any other effects.

=====
class TypeDecorator(TypeEngine):
    @property
    def comparator_factory(self):
        return type("TDComparator",
                    (self.impl.comparator_factory, TypeDecorator.Comparator,),  # <- Order reversed
                    {})
=====

Tested on 0.8.4 and 0.9.6.

[Sqlalchemy-tickets] Issue #3101: mysql dialect is_disconnect incorrectly classifies COMMANDS_OUT_OF_SYNC (zzzeek/sqlalchemy)

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

New issue 3101: mysql dialect is_disconnect incorrectly classifies COMMANDS_OUT_OF_SYNC
https://bitbucket.org/zzzeek/sqlalchemy/issue/3101/mysql-dialect-is_disconnect-incorrectly

novas0x2a:

In 0.9.7 (and going back to at least 0.7, I think) [mysql/base's is_disconnect](
https://bitbucket.org/zzzeek/sqlalchemy/src/f2cf62090c4f9812bf4a57a9f68172b1b2eafd6c/lib/sqlalchemy/dialects/mysql/base.py?at=master#cl-2227) catches OperationalError and, if the error is on the whitelist, reports it as a disconnect. One of the items on the list, error 2014 (COMMANDS_OUT_OF_SYNC), is actually a ProgrammingError, not an OperationalError (See [mysqldb source](https://github.com/farcepest/MySQLdb1/blob/master/_mysql.c#L161) and [constants list](https://github.com/farcepest/MySQLdb1/blob/63ab17980da8b31ca02ad31236420ed57006e315/MySQLdb/constants/CR.py#L3)).

One potential easy fix is attached.

[Sqlalchemy-tickets] Issue #3100: session.flush_object() ? (zzzeek/sqlalchemy)

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

New issue 3100: session.flush_object() ?
https://bitbucket.org/zzzeek/sqlalchemy/issue/3100/sessionflush_object

Mike Bayer:

the common use case where folks need to INSERT or UPDATE a single row, fast.

    session.flush_object(some_object)

This would pull into the persistence.py mechanics to flush *just that one object*.  No collections, no related items.  it would skip all of flush(), unitofwork.py and go directly to save_obj, or if the object were marked deleted to delete_obj.  

Essentially its what a simple ORM would do with an obj.save() method.

for starters, it would *not* do any foreign key syncs from a related m2o, that's a UOW function.   im sure people will complain soon enough though.

[Sqlalchemy-tickets] Issue #3099: get_all_pending() takes 3 positional arguments but 4 were given (zzzeek/sqlalchemy)

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

New issue 3099: get_all_pending() takes 3 positional arguments but 4 were given
https://bitbucket.org/zzzeek/sqlalchemy/issue/3099/get_all_pending-takes-3-positional

NotTheEvilOne:

Hi,

the "autoflush" feature triggers an error in SQLAlchemy 0.9.6.

```
#!python

  File "/opt/pas/src/dNG/pas/data/upnp/resources/mp_entry.py", line 588, in init_cds_id
    .first()
  File "/usr/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2334, in first
    ret = list(self[0:1])
  File "/usr/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2201, in __getitem__
    return list(res)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2404, in __iter__
    self.session._autoflush()
  File "/usr/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 1188, in _autoflush
    self.flush()
  File "/usr/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 1907, in flush
    self._flush(objects)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 2025, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 57, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 172, in reraise
    raise value
  File "/usr/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 1989, in _flush
    flush_context.execute()
  File "/usr/lib/python3.4/site-packages/sqlalchemy/orm/unitofwork.py", line 350, in execute
    postsort_actions = self._generate_actions()
  File "/usr/lib/python3.4/site-packages/sqlalchemy/orm/unitofwork.py", line 324, in _generate_actions
    for rec in cycles
  File "/usr/lib/python3.4/site-packages/sqlalchemy/orm/unitofwork.py", line 324, in <genexpr>
    for rec in cycles
  File "/usr/lib/python3.4/site-packages/sqlalchemy/orm/unitofwork.py", line 541, in per_state_flush_actions
    dep.per_state_flush_actions(uow, states_for_prop, False)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/orm/dependency.py", line 166, in per_state_flush_actions
    else attributes.PASSIVE_NO_INITIALIZE)
TypeError: get_all_pending() takes 3 positional arguments but 4 were given
```

I think it's related to commit 69dbcdd which in turn is related to #3060 as far as I understand.

Best regards
Tobias

[Sqlalchemy-tickets] Issue #3098: Percent sign formatting of ddl strings breaks for modulo operations on columns that start with f, d, etc. (zzzeek/sqlalchemy)

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

New issue 3098: Percent sign formatting of ddl strings breaks for modulo operations on columns that start with f, d, etc.
https://bitbucket.org/zzzeek/sqlalchemy/issue/3098/percent-sign-formatting-of-ddl-strings

mike_solomon:

The following code does not compile because there is a % f in it (from the modulo operation on the table foo) and the visit_ddl function in compiler.py uses python's percent sign formatting syntax.

```
#!python

from sqlalchemy import Table, MetaData, Column, Integer, DDL
from sqlalchemy import create_engine, event

ECHO = False
engine = create_engine('sqlite:///memory', echo=ECHO)

ddl = DDL(
'''CREATE TRIGGER foo AFTER INSERT ON bar
      BEGIN
          INSERT INTO bar SELECT * FROM foo WHERE 2 % foo.id = 0;
      END;
    '''  
)

md = MetaData()
Foo = Table('foo', md, Column('id', Integer))
Bar = Table('bar', md, Column('id', Integer))

event.listen(Foo, 'after_create', ddl.execute_if(dialect='sqlite'))

Foo.metadata.create_all(engine)

```

[Sqlalchemy-tickets] Issue #3097: Table not being create with __abstract__ = False (zzzeek/sqlalchemy)

[Victor O. <iss...@bi...>]

New issue 3097: Table not being create with __abstract__ = False
https://bitbucket.org/zzzeek/sqlalchemy/issue/3097/table-not-being-create-with-__abstract__

Victor Olex:


```
#!python

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

Base = declarative_base()

class A(Base):
    __tablename__ = 'atab'
    id = Column(Integer, primary_key=True)

class B(Base):
    __tablename__ = 'btab'
    __abstract__ = True
    id = Column(Integer, primary_key=True)

class C(Base):
    __tablename__ = 'ctab'
    __abstract__ = False
    id = Column(Integer, primary_key=True)

table_names = Base.metadata.tables.keys()

assert 'atab' in table_names, 'atab not found, this table has not __abstract__ property'
assert 'btab' not in table_names, 'btab found, but this table has __abstract__ = True'
assert 'ctab' in table_names, 'ctab not found, this table has __abstract__ = False'
```

[Sqlalchemy-tickets] Issue #3096: Misnamed datetime attributes in mysql/base.py (zzzeek/sqlalchemy)

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

New issue 3096: Misnamed datetime attributes in mysql/base.py
https://bitbucket.org/zzzeek/sqlalchemy/issue/3096/misnamed-datetime-attributes-in-mysql

Tara Andrews:

Using SQLAlchemy with a DateTime column on MySQL gives me the following stack trace:

```
#!pycon

Traceback (most recent call last):
  File "/Users/tla/Projects/PBW/main.py", line 9, in <module>
    our_people = session.query(data.Person).filter_by(name="Alexios").all()
  File "/Users/tla/anaconda/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2279, in all
    return list(self)
  File "/Users/tla/anaconda/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 72, in instances
    rows = [process[0](row, None) for row in fetch]
  File "/Users/tla/anaconda/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 451, in _instance
    populate_state(state, dict_, row, isnew, only_load_props)
  File "/Users/tla/anaconda/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 305, in populate_state
    populator(state, dict_, row)
  File "/Users/tla/anaconda/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 154, in fetch_col
    dict_[key] = row[col]
  File "/Users/tla/anaconda/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 769, in process
    microseconds = value.microseconds
AttributeError: 'datetime.datetime' object has no attribute 'microseconds'
```

...which makes sense, as indeed the attribute is called 'microsecond' and, on the following line, it should be 'second' rather than 'seconds'. Here is the very simple patch:


```
#!diff

--- base.py	2014-06-25 11:03:02.000000000 +0200
+++ /Users/tla/anaconda/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py	2014-06-25 11:03:08.000000000 +0200
@@ -766,8 +766,8 @@
         def process(value):
             # convert from a timedelta value
             if value is not None:
-                microseconds = value.microseconds
-                seconds = value.seconds
+                microseconds = value.microsecond
+                seconds = value.second
                 minutes = seconds // 60
                 return time(minutes // 60,
                             minutes % 60,

```

[Sqlalchemy-tickets] Issue #3095: ARRAY of ENUM doesn't do any validation (zzzeek/sqlalchemy)

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

New issue 3095: ARRAY of ENUM doesn't do any validation
https://bitbucket.org/zzzeek/sqlalchemy/issue/3095/array-of-enum-doesnt-do-any-validation

rach:

SQLAlchemy is not complaining when I have a model with 

     array_enum = Column(ARRAY(Enum(*LIST_OF_ENUM, native_enum=False))) 

but doens't seem to do any validation and I have been able to insert value outside the enum in the array.

[Sqlalchemy-tickets] Issue #3094: Bug with MSSQL backend and multiple primary key columns, when creating tables using MetaData (zzzeek/sqlalchemy)

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

New issue 3094: Bug with MSSQL backend and multiple primary key columns, when creating tables using MetaData
https://bitbucket.org/zzzeek/sqlalchemy/issue/3094/bug-with-mssql-backend-and-multiple

rekcahpassyla:

Before submitting this I tried to search for an existing issue  - https://bitbucket.org/zzzeek/sqlalchemy/issues?q=primary_key+identity did not return any results. 

Apologies, I don't know how to categorise this so I have left the default settings as Kind=bug, Priority=major, Milestone=0.9.5. 

OS: Windows 7 Enterprise Service Pack 1

Python version: Python 2.7.5 |Anaconda 1.9.0 (64-bit)| (default, Jul  1 2013, 12:37:52) [MSC v.1500 64 bit (AMD64)]

SQLAlchemy version: https://github.com/zzzeek/sqlalchemy/commit/ea54b635d66bc695c5149ede5279cc6ee2f43e7c (I built a conda package from this) 

Target database: SQL Server 11.0.5058

Driver: SQL Server Native Client 11.0 Version 2011.110.3000.00 

Using the sqlalchemy.MetaData class, I have been trying to create a table with a multiple-column primary key. I observed that the first integer column with primary_key=True is created as column type IDENTITY(1, 1). 

Here are some code samples. Many thanks. 

Using sqlite backend:

```python
import sqlalchemy as sa

engine = sa.create_engine(r'sqlite:///:memory:',echo=True)
metadata = sa.MetaData(bind=engine)

table = sa.Table(
    'MYTABLE', metadata,
    sa.Column('DateInsertedUTC', sa.DateTime, primary_key=True, default=sa.func.sysutcdatetime()),
    sa.Column('Key1', sa.String(32), primary_key=True),                
    sa.Column('Key2', sa.String(64), primary_key=True),
    sa.Column('Key3', sa.String(2), primary_key=True),
    sa.Column('DayOfWeek', sa.Integer, sa.CheckConstraint("DayOfWeek >= 0 AND DayOfWeek <= 8"), primary_key=True),
    sa.Column('Month', sa.Integer, sa.CheckConstraint("Month >= 1 AND Month <= 12"), primary_key=True),
    sa.Column('Key4', sa.Integer, primary_key=True),
    sa.Column('Value', sa.Float(53), nullable=False),
)

metadata.create_all()
```

generates the following SQL (from the echo trace)

```sql
CREATE TABLE "MYTABLE" (
        "DateInsertedUTC" DATETIME NOT NULL, 
        "Key1" VARCHAR(32) NOT NULL, 
        "Key2" VARCHAR(64) NOT NULL, 
        "Key3" VARCHAR(2) NOT NULL, 
        "DayOfWeek" INTEGER NOT NULL CHECK (DayOfWeek >= 0 AND DayOfWeek <= 8), 
        "Month" INTEGER NOT NULL CHECK (Month >= 1 AND Month <= 12), 
        "Key4" INTEGER NOT NULL, 
        "Value" FLOAT NOT NULL, 
        PRIMARY KEY ("DateInsertedUTC", "Key1", "Key2", "Key3", "DayOfWeek", "Month", "Key4")
)

```

The following code using mssql backend

```python
import sqlalchemy as sa

engine = sa.create_engine(r'mssql://redacted;TrustedConnection=Yes',echo=True)
metadata = sa.MetaData(bind=engine)

table = sa.Table(
    'MYTABLE', metadata,
    sa.Column('DateInsertedUTC', sa.DateTime, primary_key=True, default=sa.func.sysutcdatetime()),
    sa.Column('Key1', sa.String(32), primary_key=True),                
    sa.Column('Key2', sa.String(64), primary_key=True),
    sa.Column('Key3', sa.String(2), primary_key=True),
    sa.Column('DayOfWeek', sa.Integer, sa.CheckConstraint("DayOfWeek >= 0 AND DayOfWeek <= 8"), primary_key=True),
    sa.Column('Month', sa.Integer, sa.CheckConstraint("Month >= 1 AND Month <= 12"), primary_key=True),
    sa.Column('Key4', sa.Integer, primary_key=True),
    sa.Column('Value', sa.Float(53), nullable=False),
    schema='REDACTED'
)

metadata.create_all()

```

generates the following SQL:

```sql
CREATE TABLE [REDACTED].[MYTABLE] (
        [DateInsertedUTC] DATETIME NOT NULL, 
        [Key1] VARCHAR(32) NOT NULL, 
        [Key2] VARCHAR(64) NOT NULL, 
        [Key3] VARCHAR(2) NOT NULL, 
        [DayOfWeek] INTEGER NOT NULL IDENTITY(1,1) CHECK (DayOfWeek >= 0 AND DayOfWeek <= 8), 
        [Month] INTEGER NOT NULL CHECK (Month >= 1 AND Month <= 12), 
        [Key4] INTEGER NOT NULL, 
        [Value] FLOAT(53) NOT NULL, 
        PRIMARY KEY ([DateInsertedUTC], [Key1], [Key2], [Key3], [DayOfWeek], [Month], [Key4])
)

```

and the desired SQL was

```
CREATE TABLE [REDACTED].[MYTABLE] (
        [DateInsertedUTC] DATETIME NOT NULL, 
        [Key1] VARCHAR(32) NOT NULL, 
        [Key2] VARCHAR(64) NOT NULL, 
        [Key3] VARCHAR(2) NOT NULL, 
        [DayOfWeek] INTEGER NOT NULL CHECK (DayOfWeek >= 0 AND DayOfWeek <= 8), 
        [Month] INTEGER NOT NULL CHECK (Month >= 1 AND Month <= 12), 
        [Key4] INTEGER NOT NULL, 
        [Value] FLOAT(53) NOT NULL, 
        PRIMARY KEY ([DateInsertedUTC], [Key1], [Key2], [Key3], [DayOfWeek], [Month], [Key4])
)
```

[Sqlalchemy-tickets] Issue #3093: MutableDict.setdefault doesn't return the new value (zzzeek/sqlalchemy)

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

New issue 3093: MutableDict.setdefault doesn't return the new value
https://bitbucket.org/zzzeek/sqlalchemy/issue/3093/mutabledictsetdefault-doesnt-return-the

Thomas Hervé:

MutableDict.setdefault was recently changed to properly propagate changes, but it broke the dictionary interface by not returning the value. You should be able to do that with a dict:

a = {}
a.setdefault('b', 1) += 1

But with the current MutableDict behavior, it will fail with a NoneError.

[Sqlalchemy-tickets] Issue #3092: CTEs do not work in exists statements used in delete statements (zzzeek/sqlalchemy)

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

New issue 3092: CTEs do not work in exists statements used in delete statements
https://bitbucket.org/zzzeek/sqlalchemy/issue/3092/ctes-do-not-work-in-exists-statements-used

mike_solomon:

The code below :


```
#!python

from sqlalchemy import Table, Column, Integer, MetaData, select, and_, exists

metadata = MetaData()
foo = Table('foo', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('val', Integer))

bar = Table('bar', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('val', Integer))


first = select([bar.c.id.label('elt')]).where(bar.c.id == 0).\
          cte(name = 'first_row', recursive = True)
grow_me = first.alias(name = 'grow_me')
stmt = first.union_all(select([bar.c.val.label('next')]).\
     where(and_(grow_me.c.elt == bar.c.id,
                grow_me.c.elt != 1)))

print foo.delete().where(exists(select([stmt])))
```

generates the result below:


```
#!sql

DELETE FROM foo WHERE EXISTS (SELECT first_row.elt 
FROM first_row)
```
I would have expected it to generate this code:

```
#!sql

DELETE FROM foo WHERE EXISTS (
WITH RECURSIVE first_row(elt) AS 
(SELECT bar.id AS elt 
FROM bar 
WHERE bar.id = :id_1 UNION ALL SELECT bar.val AS next 
FROM bar, first_row AS grow_me 
WHERE grow_me.elt = bar.id AND grow_me.elt != :elt_1)
 SELECT first_row.elt 
FROM first_row)
```

The code above is legal SQL.  As a workaround for my project, I've created a simple string formatter that will insert the SELECT statement into the EXISTS statement, but it would be great if this could be done automatically.

[Sqlalchemy-tickets] Issue #3091: update description_encoding for pyodbc (zzzeek/sqlalchemy)

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

New issue 3091: update description_encoding for pyodbc
https://bitbucket.org/zzzeek/sqlalchemy/issue/3091/update-description_encoding-for-pyodbc

Mike Bayer:

it seems like our defaults on this are out of date, examine pyodbc behavior re: cursor.description and see if we can either change the default, or put in a detection for it, possibly for 1.0 since this may be destabilizing:


```
#!python

#! coding: utf-8

from sqlalchemy import *

engine = create_engine("mssql+pyodbc://scott:tiger@ms_2008", echo=True,
                        description_encoding='utf8')

colname = u'Заказ.Номер заказа'

m = MetaData()
t = Table(u"Заказы", m, Column(colname, String(30), key='somecol'))

m.drop_all(engine)
m.create_all(engine)

engine.execute(t.insert().values(somecol='some value'))
result = engine.execute(t.select().where(t.c.somecol == 'some value'))
row = result.fetchone()
print row[t.c.somecol]


```

[Sqlalchemy-tickets] Issue #3090: Parameter order wrong for SQLite request (zzzeek/sqlalchemy)

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

New issue 3090: Parameter order wrong for SQLite request
https://bitbucket.org/zzzeek/sqlalchemy/issue/3090/parameter-order-wrong-for-sqlite-request

mike_solomon:

The following code fails to compile for SQLite with the correct parameter order.  The user-defined callback function "pow" shows the wrong arguments coming in (strings instead of numbers).  These strings are used as positional arguments elsewhere in the statement.

Sorry that I can't get it any smaller than this, but for the smaller examples I concocted SQLAlchemy gets things right so it is difficult to isolate the problem.


```
#!python

from sqlalchemy import Table, MetaData, Column, Integer, Float, String
from sqlalchemy import select
from sqlalchemy import and_, or_
from sqlalchemy import func
from plain import *

_metadata = MetaData()

def make_table(name, tp) :
  return Table(name, _metadata,
                     Column('id', Integer, primary_key = True),
                     Column('val', tp))

class JoinK(object) :
  def __init__(self, table) :
    self.table = table

class Join(JoinK) : pass
class Outerjoin(JoinK) : pass

def easy_sj(l, use_id = False, extras=[]) :
  to_join = filter(lambda x : isinstance(x, Table) or isinstance(x,JoinK), l)
  return easy_join(easy_select(l, use_id), to_join + extras)

def easy_select(l, use_id = False) :
  vals = [(t if not isinstance(t,Table) else t.c.val.label(t.name+'_val')) for t in l]
  if use_id :
    vals = [l[0].c.id.label('id')] + vals
  return select(vals)

def easy_join(stmt, lTables) :
  joins = lTables[0]
  for table in lTables[1:] :
    joink = table if isinstance(table, JoinK) else Join(table)
    joins = getattr(joins, 'join' if isinstance(joink, Join) else 'outerjoin' )(joink.table, onclause = joink.table.c.id == lTables[0].c.id)
  return stmt.select_from(joins)

def and_eq(table1, table2, vals) :
  return and_(*[table1.c[x] == table2.c[x] for x in vals])

def product(row) :
  return func.round(func.exp(func.sum(func.log(1.0 * row))))

Score = make_table('score', Integer)
Staff = make_table('staff', Integer)
Voice = make_table('voice', Integer)
Time_next = make_table('next', Integer)
Dots = make_table('dots', Integer)
Onset = make_table('onset', Float)  
Duration = make_table('duration', Float)
Duration_log = make_table('duration_log', Integer)
Name = make_table('name', String(50))
Numerator = make_table('numerator', Integer)
Denominator = make_table('denominator', Integer)
Left_bound = make_table('left_bound', Integer)
Right_bound = make_table('right_bound', Integer)

def make_statement(score, voice, name, left_bound, right_bound, duration_log, time_next, dots) :

  tuplets = easy_sj([score, voice, name, left_bound, right_bound,
                     Numerator, Denominator],
                    use_id = True).\
              where(name.c.val == "tuplet").\
              cte("tuplets")

  rhythmic_events = easy_sj([score, voice, Onset, duration_log],
                            use_id = True,
                            extras=[name]).\
                      where(or_(name.c.val == "chord",
                                name.c.val == "rest",
                                name.c.val == "space")).\
                      cte(name = "rhythmic_events")


  first_rhythmic_events = select([rhythmic_events.c.score_val,
                                  rhythmic_events.c.voice_val,
                                  func.max(rhythmic_events.c.onset_val).label('onset_val')]).\
                            group_by(rhythmic_events.c.score_val,
                                     rhythmic_events.c.voice_val).\
                            cte(name = "first_rhythmic_events")

  first_rhythmic_events_to_ids =\
       select([rhythmic_events.c.id,
               rhythmic_events.c.score_val,
               rhythmic_events.c.voice_val,
               rhythmic_events.c.onset_val]).\
         select_from(first_rhythmic_events.\
           join(rhythmic_events,
             onclause = and_eq(rhythmic_events,
                               first_rhythmic_events,
                               ['score_val', 'voice_val', 'onset_val']))).\
           cte("first_rhythmic_events_to_ids")

  rhythmic_events_to_tuplets =\
    select([rhythmic_events.c.id.label('rhythmic_id'),
            tuplets.c.id.label('tuplet_id'),
            tuplets.c.left_bound_val.label('left_val'),
            tuplets.c.right_bound_val.label('right_val')]).\
      where(and_eq(rhythmic_events, tuplets, ['score_val', 'voice_val'])).\
      cte(name = "rhythmic_events_to_tuplets", recursive = True)

  rhythmic_events_to_tuplets_a = rhythmic_events_to_tuplets.alias(name="rhythmic_events_to_tuplets_prev")

  rhythmic_events_to_tuplets =\
    rhythmic_events_to_tuplets.union_all(
      select([rhythmic_events_to_tuplets_a.c.rhythmic_id,
              rhythmic_events_to_tuplets_a.c.tuplet_id,
              time_next.c.val,
              rhythmic_events_to_tuplets_a.c.right_val]).\
      where(and_(time_next.c.val == rhythmic_events_to_tuplets_a.c.left_val,
                 time_next.c.val != rhythmic_events_to_tuplets_a.c.right_val)))

  rhythmic_events_to_matching_tuplets =\
    select([rhythmic_events_to_tuplets.c.rhythmic_id,
            product(tuplets.c.numerator_val).label('combined_numerator'),
            product(tuplets.c.denominator_val).label('combined_denominator')]).\
      select_from(rhythmic_events_to_tuplets.\
        join(tuplets, onclause = tuplets.c.id == rhythmic_events_to_tuplets.c.tuplet_id)).\
      where(rhythmic_events_to_tuplets.c.rhythmic_id ==\
            rhythmic_events_to_tuplets.c.left_val).\
      group_by(rhythmic_events_to_tuplets.c.rhythmic_id).\
      cte(name="rhythmic_events_to_matching_tuplets")

  rhythmic_events_to_durations =\
    select([duration_log.c.id,
            ((1.0 * func.ifnull(rhythmic_events_to_matching_tuplets.c.combined_numerator, 1.0) /\
                   func.ifnull(rhythmic_events_to_matching_tuplets.c.combined_denominator, 1.0)) *\
             (1.0 + ((func.pow(2, func.ifnull(dots.c.val, 0)) - 1) / func.pow(2, func.ifnull(dots.c.val,0)))) *\
             func.pow(2, duration_log.c.val))]).\
      select_from(duration_log.\
        outerjoin(rhythmic_events_to_matching_tuplets, onclause = duration_log.c.id == rhythmic_events_to_matching_tuplets.c.rhythmic_id).\
        outerjoin(dots, onclause = dots.c.id == duration_log.c.id))

  return rhythmic_events_to_durations

if __name__ == "__main__" :
  import math
  from properties import *
  from sqlalchemy import create_engine
  stmt = make_statement(score = Score,
                        voice = Voice,
                        name = Name,
                        left_bound = Left_bound,
                        right_bound = Right_bound,
                        duration_log = Duration_log,
                        time_next = Time_next,
                        dots = Dots)
  #print stmt

  engine = create_engine('sqlite:///:memory:', echo=False)
  conn = engine.connect()
  def my_log(in_val) :
    print in_val, "log"
    return math.log(in_val)
  def my_exp(x) :
    print in_val, "exp"
    return math.log(in_val)
  def my_pow(in_val_0, in_val_1) :
    print in_val_0, in_val_1, "pow", type(in_val_0)
    return in_val_0 ** in_val_1
  conn.connection.create_function("log",
                                  1,
                                  my_log)
  conn.connection.create_function("exp",
                                  1,
                                  my_exp)
  conn.connection.create_function("pow",
                                  2,
                                  my_pow)
  Score.metadata.create_all(engine)
  stmts = []
  for x in range(32) :
    stmts.append(Score.insert().values(id=x, val=0))
    stmts.append(Voice.insert().values(id=x, val=x%2))
    stmts.append(Duration_log.insert().values(id=x, val=-2))
  for st in stmts :
    conn.execute(st)
  #print stmt
  for row in conn.execute(stmt).fetchall() :
    print row
  

```

[Sqlalchemy-tickets] Issue #3089: sqlite db with non-ascii characters in path (zzzeek/sqlalchemy)

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

New issue 3089: sqlite db with non-ascii characters in path
https://bitbucket.org/zzzeek/sqlalchemy/issue/3089/sqlite-db-with-non-ascii-characters-in

Scott Horowitz:

If you have a database path with non-ascii characters (e.g., "Á"), then sqlalchemy will fail with:
"unable to open database file None None"

Python's sqlite3 also exhibits this behavior. However, you can workaround this in sqlite3 by doing:

```
#!python

import sqlite3, os
os.chdir("/path/with/non/ascii/character")
conn = sqlite3.connect("file.db")
```

This does not work with sqlalchemy, however, because the create_connect_args() method in sqlalchemy\dialects\sqlite\pysqlite3.py has the following code where an absolute path is always provided to sqlite:

```
#!python

        if filename != ':memory:':
            filename = os.path.abspath(filename)

```

If this code is removed, then the workaround works as expected.

That said, it will not resolve the issue of non-ascii characters in the filename itself, just the path.

[Sqlalchemy-tickets] Issue #3088: Sqlalchemy: possible bug when inserting rows and returning the primary key (zzzeek/sqlalchemy)

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

New issue 3088: Sqlalchemy: possible bug when inserting rows and returning the primary key
https://bitbucket.org/zzzeek/sqlalchemy/issue/3088/sqlalchemy-possible-bug-when-inserting

lfiaschi:

Hi, 
I noticed something strange when inserting multiple rows and returning the primary keys. 
If I add use the parameter values in the isert query I get the expected behaviour, hower when passing the values to the cursor, nothing is returned.

Is it possibly a bug?

My sqlachemy version is 0.9.4, below how to reproduce the error:




```
#!python

from sqlalchemy import create_engine, Column, Unicode, DateTime
from sqlalchemy import Float, ForeignKey, BigInteger, Enum, Integer, Boolean
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base


cmd= 'postgresql+psycopg2://lfiaschi:mypswd@localhost:5432/test_db'

Base = declarative_base()

class Test(Base):
    __tablename__ = 'test'
    db_id = Column(BigInteger, primary_key=True, autoincrement=True)
    name = Column(Unicode(255), nullable=False)


engine=create_engine(cmd, encoding='utf8', implicit_returning=False)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)


N=10
rows = [{'name': str(i)} for i in range(N)]

#If the values are specified at this point the primary keys seem correctly returned
ins_statement1 = Test.__table__.insert(returning=[Test.__table__.c.db_id]).values(rows)

connection = engine.connect()


res1 = connection.execute(ins_statement1)
correct_ids = res1.fetchall()
print correct_ids
assert len(correct_ids) == N


ins_statement1 = Test.__table__.insert(returning=[Test.__table__.c.db_id])

#If we specify the values here
res2 = connection.execute(ins_statement1,rows)

res2.fetchall() #BOOOM!
```

The error is 


```
#!

Traceback (most recent call last):
  File "bug_sqlalchemy.py", line 42, in <module>
    res2.fetchall() #BOOOM!
  File "/home/lfiaschi/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 788, in fetchall
    self.cursor, self.context)
  File "/home/lfiaschi/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1108, in _handle_dbapi_exception
    exc_info
  File "/home/lfiaschi/anaconda/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/lfiaschi/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 782, in fetchall
    l = self.process_rows(self._fetchall_impl())
  File "/home/lfiaschi/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 749, in _fetchall_impl
    return self.cursor.fetchall()
sqlalchemy.exc.ProgrammingError: (ProgrammingError) no results to fetch None None


```

[Sqlalchemy-tickets] Issue #3087: literal_binds in server_default (zzzeek/sqlalchemy)

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

New issue 3087: literal_binds in server_default
https://bitbucket.org/zzzeek/sqlalchemy/issue/3087/literal_binds-in-server_default

Mike Bayer:

similar to #2742  (9bc9d5c1068be878), let's look into literal_binds for server_default as well.  1.0 for now.

example:


```
#!python

tbl = Table("derp", metadata,
    Column("arr", ARRAY(Text),
                server_default=array(["foo", "bar", "baz"])),
)

```

[Sqlalchemy-tickets] Issue #3086: server_default poorly documented (zzzeek/sqlalchemy)

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

New issue 3086: server_default poorly documented
https://bitbucket.org/zzzeek/sqlalchemy/issue/3086/server_default-poorly-documented

Mike Bayer:

it only includes text().  illustrate how enough information to support an example like:


```
#!python

tbl = Table("derp", metadata,
    Column("arr", ARRAY(Text),
                server_default=array([literal_column("'foo'"),
                                    literal_column("'bar'"),
                                    literal_column("'baz'")])),
)

```

that is, expressions, literals, etc.   as a task elsewhere we have to figure out if we should drop the literal_column aspect of this.

[Sqlalchemy-tickets] Issue #3085: Index mysql_length does not work with column ames with spaces (zzzeek/sqlalchemy)

[Iuri de S. <iss...@bi...>]

New issue 3085: Index mysql_length does not work with column ames with spaces
https://bitbucket.org/zzzeek/sqlalchemy/issue/3085/index-mysql_length-does-not-work-with

Iuri de Silvio:

I have a `Column 1` in MySQL database. I tried to define an index with:

    mysql_length={'Column 1': 255}

It doesn't work. I have to add backticks to column name:

    mysql_length={'`Column 1`': 255}

[Sqlalchemy-tickets] Issue #3084: make sort_tables() stable (zzzeek/sqlalchemy)

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

New issue 3084: make sort_tables() stable
https://bitbucket.org/zzzeek/sqlalchemy/issue/3084/make-sort_tables-stable

Sebastian Bank:

I use the [mock-engine recipe](http://docs.sqlalchemy.org/en/latest/faq.html#how-can-i-get-the-create-table-drop-table-output-as-a-string) from the FAQ to get and compare the schema of different instances of a database on different platforms. As the table creation order is not completely fixed, I need to monkey-patch `sqlalchemy.util.topological.sort` to make `diff` work on the resulting SQL dumps.

I propose to change `sqlalchemy.sql.ddl.sort_tables` to first **totally order** the tables and then use a **stable topological sort** roughly like the following:

    def sort_as_subsets_stable(tuples, allitems):
    
        edges = collections.defaultdict(set)
        for parent, child in tuples:
            edges[child].add(parent)
    
        todo = set(allitems)
        queue = list(allitems)
    
        while queue:
            output = []
            later = []
            for node in queue:
                if not todo.intersection(edges[node]):
                    output.append(node)
                else:
                    later.append(node)
    
            if not output:
                raise RuntimeError
    
            todo.difference_update(output)
            queue = later
            yield output

    def sort_stable(tuples, allitems):
        for set_ in sort_as_subsets_stable(tuples, allitems):
            for s in set_:
                yield s

For the deterministic ordering prior to the topological sort, name or definition order would do (I prefer the latter).

I suppose the performance impact does not matter, if this is only used in database creation.

I have not tried, but I think that the [hash randomization](https://docs.python.org/3/reference/datamodel.html#object.__hash__) in Python 3.3+ might shuffle the table order with each invocation with the current implementation.

[Sqlalchemy-tickets] Issue #3083: clarify filter_by() (non-)interaction with select_from() (zzzeek/sqlalchemy)

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

New issue 3083: clarify filter_by() (non-)interaction with select_from()
https://bitbucket.org/zzzeek/sqlalchemy/issue/3083/clarify-filter_by-non-interaction-with

Sebastian Bank:

When using `filter_by` to simplify `filter` expressions

    session.query(User).filter(User.id == 1)
    session.query(User).filter_by(id=1)

where `filter_by` applies to the last join entity

    session.query(User).join(user_group).filter(user_group.c.group_id == 5)
    session.query(User).join(user_group).filter_by(group_id=5)
	
I stumbled on the fact that it does not follow a `select_from` (which I expected intuitively):

    session.query(User).select_from(user_group).filter(user_group.c.group_id == 5).join(User)
    session.query(User).select_from(user_group).filter_by(group_id=5).join(User)

The second one fails. In my concrete case, the primary entity actually had an attribute with the same name. Hence, I ended up with the wrong filter condition (discovering that much later).

Maybe a hint in the documentation of `filter_by` and/or `select_from` could help so that others don't make the same mistake.

In the long run, I might also suggest to change this behaviour.

[Sqlalchemy-tickets] Issue #3082: cresultproxy bug (zzzeek/sqlalchemy)

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

New issue 3082: cresultproxy bug
https://bitbucket.org/zzzeek/sqlalchemy/issue/3082/cresultproxy-bug

limodou:

![Snip20140613_3.png](https://bitbucket.org/repo/z9qoM7/images/3247075300-Snip20140613_3.png)

If I disable c extension when install sqlalchemy, it'll be ok. So I think it's may a bug of cresultproxy extension.

[Sqlalchemy-tickets] Issue #3081: use session.get_bind() for query.__str__() (zzzeek/sqlalchemy)

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

New issue 3081: use session.get_bind() for query.__str__()
https://bitbucket.org/zzzeek/sqlalchemy/issue/3081/use-sessionget_bind-for-query__str__

Mike Bayer:

this should be fine.  tests should include queries that have no session, sessions with no binds, etc.


```
#!diff

diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index 5d60c4e..39dc1a1 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -2404,6 +2404,11 @@ class Query(object):
             self.session._autoflush()
         return self._execute_and_instances(context)
 
+    def __str__(self):
+        context = self._compile_context()
+        bind = self._get_bind_args(context, self.session.get_bind)
+        return str(context.statement.compile(bind))
+
     def _connection_from_session(self, **kw):
         conn = self.session.connection(
                         **kw)
@@ -2412,14 +2417,19 @@ class Query(object):
         return conn
 
     def _execute_and_instances(self, querycontext):
-        conn = self._connection_from_session(
-                        mapper=self._mapper_zero_or_none(),
-                        clause=querycontext.statement,
-                        close_with_result=True)
+        conn = self._get_bind_args(querycontext, self._connection_from_session,
+                                    close_with_result=True)
 
         result = conn.execute(querycontext.statement, self._params)
         return loading.instances(self, result, querycontext)
 
+    def _get_bind_args(self, querycontext, fn, **kw):
+        return fn(
+            mapper=self._mapper_zero_or_none(),
+            clause=querycontext.statement,
+            **kw
+        )
+
     @property
     def column_descriptions(self):
         """Return metadata about the columns which would be
@@ -2920,8 +2930,6 @@ class Query(object):
                                     sql.True_._ifnone(context.whereclause),
                                     single_crit)
 
-    def __str__(self):
-        return str(self._compile_context().statement)
 
 from ..sql.selectable import ForUpdateArg
 

```