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

[Sqlalchemy-tickets] Issue #3280: bug with JOIN precedence (zzzeek/sqlalchemy)

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

New issue 3280: bug with JOIN precedence
https://bitbucket.org/zzzeek/sqlalchemy/issue/3280/bug-with-join-precedence

yoch:

Hi,

I wrote this code in python :

db.query(A, B, C).\
       filter(func.coalesce(A.idB,A.idC)==B.id).\
       outerjoin(C, C.id==B.id).\
       all()

And it cause an strange error: (1054, "Unknown column 'A.id' in 'on clause'")

This happens because the produced SQL code looks like :

SELECT  A.*, B.*, C.*
FROM B, A
LEFT OUTER JOIN C
    ON C.id = B.id
WHERE coalesce(A.idB, A.idC) = B.id;

And this code is broken, because it mix commas joins withs others types of joins, and this lead an error.
http://stackoverflow.com/questions/10483421/mysql-unknown-column-in-on-clause

[Sqlalchemy-tickets] Issue #3279: CreateTable doesn't specify AUTO INCREMENT or indexes (zzzeek/sqlalchemy)

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

New issue 3279: CreateTable doesn't specify AUTO INCREMENT or indexes
https://bitbucket.org/zzzeek/sqlalchemy/issue/3279/createtable-doesnt-specify-auto-increment

Shatil Rafiullah:

Is `CreateTable` the best way to get the SQL syntax for table creation? 

If I run my program altogether, I see it in the echoed SQL output, but it seems like there has to be a better way. I'm at a loss for how to get `AUTO INCREMENT` sequences to print along with table creation syntax. 

```
#!python

>>> table = MyModel.__table__
>>> print sqlalchemy.schema.CreateTable(table)
```

This will print something like:

```
#!sql

CREATE TABLE table_name (
        sequence INTEGER NOT NULL, 
        id VARCHAR(128) NOT NULL, 
        status VARCHAR(128) NOT NULL, 
        status_message TEXT, 
        PRIMARY KEY (sequence)
)
```

It doesn't close the SQL statement with a semicolon. To get indexes/indices, I must write:

```
#!python

for index in table.indexes:
    print sqlalchemy.schema.CreateIndex(index)
```

To get:

```
#!sql

CREATE INDEX ix_table_name_id ON table_name (id)
CREATE INDEX ix_table_name_status ON table_name (status)
```

Now, compare the above to the echoed SQL output:

```
#!sql

2014-12-22 09:10:58,697 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE table_name (
        sequence INTEGER NOT NULL AUTO_INCREMENT, 
        id VARCHAR(128) NOT NULL, 
        status VARCHAR(128) NOT NULL, 
        status_message TEXT, 
        PRIMARY KEY (sequence),
)

2014-12-22 09:10:58,723 INFO sqlalchemy.engine.base.Engine CREATE INDEX ix_table_name_id ON table_name (id)

2014-12-22 09:10:58,744 INFO sqlalchemy.engine.base.Engine CREATE INDEX ix_table_name_status ON table_name (status)
```

[Sqlalchemy-tickets] Issue #3278: typedecorator of typedecorator (zzzeek/sqlalchemy)

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

New issue 3278: typedecorator of typedecorator
https://bitbucket.org/zzzeek/sqlalchemy/issue/3278/typedecorator-of-typedecorator

Mike Bayer:


```
#!python

from sqlalchemy import types as sa_types, Column


class BigText(sa_types.TypeDecorator):
    impl = sa_types.Text


class BigJSONEncodedDict(sa_types.TypeDecorator):
    impl = BigText


c1 = Column('foo', BigJSONEncodedDict())

print(c1 == {'x': 'y'})

```

```
#!


Traceback (most recent call last):
  File "test.py", line 14, in <module>
    print(c1 == {'x': 'y'})
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/sql/operators.py", line 298, in __eq__
    return self.operate(eq, other)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/sql/elements.py", line 732, in operate
    return op(self.comparator, *other, **kwargs)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/util/langhelpers.py", line 729, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/sql/elements.py", line 718, in comparator
    return self.type.comparator_factory(self)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/sql/type_api.py", line 635, in comparator_factory
    {})
TypeError: Cannot create a consistent method resolution
order (MRO) for bases Comparator, TDComparator

```

[Sqlalchemy-tickets] Issue #3277: One-to-one mapping issues extra query when accessing parent_obj.child.parent_obj (zzzeek/sqlalchemy)

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

New issue 3277: One-to-one mapping issues extra query when accessing parent_obj.child.parent_obj
https://bitbucket.org/zzzeek/sqlalchemy/issue/3277/one-to-one-mapping-issues-extra-query-when

Priit Laes:

We've been investigating an issue where we have a one-to-one relationship between A and B, where A->B is lazy=joined relationship.

When querying A, both A and its child B are fetched, but when accessing A.B.A via (I even tried setting backref lazy='joined' to backref) it still issues another query.

Eventual workaround/fix was to use options(contains_eager('b.a')) which also uses cleaner query.

Now there's also with NULL check when passing nested path to contains_eager, but I'm not sure whether this is user or orm responsibility ;)

[Sqlalchemy-tickets] Issue #3276: normalize name for oracle constraint / index names? (zzzeek/sqlalchemy)

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

New issue 3276: normalize name for oracle constraint / index names?
https://bitbucket.org/zzzeek/sqlalchemy/issue/3276/normalize-name-for-oracle-constraint-index

Mike Bayer:

we are normalizing names for the columns in FKs, PKs and indexes, but not the name of the object itself.  This causes alembic autogenerates to have UPPERCASE names which is not what they want. 

[Sqlalchemy-tickets] Issue #3275: gaerdbms.py is using a deprecated API to talk to Google Cloud SQL (zzzeek/sqlalchemy)

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

New issue 3275: gaerdbms.py is using a deprecated API to talk to Google Cloud SQL
https://bitbucket.org/zzzeek/sqlalchemy/issue/3275/gaerdbmspy-is-using-a-deprecated-api-to

razvanm:

The `lib/sqlalchemy/dialects/mysql/gaerdbms.py` is using a deprecated API to talk to Google Cloud SQL. The recommend way for production environment is to use MySQLdb and connect to /cloudsql/*project:instance* Unix socket. For dev_appserver the recommended way is to request and IP and use regular TCP connectivity to it.

Disclaimer: I'm part of the Google Cloud SQL team. :-)

Reference: https://cloud.google.com/appengine/docs/python/cloud-sql/#Python_complete_python_example

[Sqlalchemy-tickets] Issue #3274: sqlalchemy 0.9.8 got exception on getting has_utf8_bin value from mysql 4.1.x server (zzzeek/sqlalchemy)

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

New issue 3274: sqlalchemy 0.9.8 got exception on getting has_utf8_bin value from mysql 4.1.x server
https://bitbucket.org/zzzeek/sqlalchemy/issue/3274/sqlalchemy-098-got-exception-on-getting

Donghyun Kim:

sqlalchemy 0.9.8 w/ mysql 4.1.x servers raised exception on running "show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'" sql statement which is supported only mysql >= 5.0

To support mysql 4.1.x servers, this should be fixed
(attached simple ignore exception patch)

here is the error

...
  File "../trunk/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 86, in _check_unicode_returns
    self.identifier_preparer.quote("Collation")
  File "../trunk/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 659, in scalar
    return self.execute(object, *multiparams, **params).scalar()
  File "../trunk/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 721, in execute
    return self._execute_text(object, multiparams, params)
  File "../trunk/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 870, in _execute_text
    statement, parameters
  File "../trunk/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context
    context)
  File "../trunk/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception
    exc_info
  File "../trunk/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "../trunk/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
    context)
  File "../trunk/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute
    cursor.execute(statement, parameters)
  File "../trunk/virtualenv/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "../trunk/virtualenv/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where `Charset` = 'utf8' and `Collation` = 'utf8_bin'' at line 1") "show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'" ()

[Sqlalchemy-tickets] Issue #3273: check constraint missing in the reflected metadata for firebird (zzzeek/sqlalchemy)

[florent b. <iss...@bi...>]

New issue 3273: check constraint missing in the reflected metadata for firebird
https://bitbucket.org/zzzeek/sqlalchemy/issue/3273/check-constraint-missing-in-the-reflected

florent breheret:

python 2.7.8, firebird 2.5.3, sqlalchemy 0.9.8, fdb 1.4.3

For a table with a check constraint, I noticed that this check constraint is missing in the reflected metadata.
The test case to reproduce:

import unittest
from sqlalchemy import *

class TestSuite(unittest.TestCase):

    def setUp(self):
        self.engine = create_engine('firebird+fdb://SYSDBA:masterkey@/c:/unitest.fdb')
        self.connection = self.engine.connect()

    def tearDown(self):
        self.connection.execute("DROP TABLE TMP_TABLE")
        self.connection.close()

    def test_check_constraint_reflected(self):
        table = Table('TMP_TABLE', MetaData(bind=self.connection),
            Column('dt', Integer),
            CheckConstraint('dt > 1')
        )
        table.create(self.engine)
        reflected_constraints = MetaData(bind=self.connection, reflect=True).tables['tmp_table'].constraints
        self.assertItemsEqual(
            [c.__class__.__name__ for c in table.constraints],
            [c.__class__.__name__ for c in reflected_constraints]
        )
        
if __name__ == '__main__':
    unittest.main()

[Sqlalchemy-tickets] Issue #3272: .label() addition of two columns (zzzeek/sqlalchemy)

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

New issue 3272: .label() addition of two columns
https://bitbucket.org/zzzeek/sqlalchemy/issue/3272/label-addition-of-two-columns

Dani Hodovic:

I know it's possible to label an arbitrary column in a query:

session.query(Person.name.label("name"))

It isn't possible to do this:

session.query((Person.age + Person.wealth).label("sum"))

How would I go about labeling an addition of different columns?

[Sqlalchemy-tickets] Issue #3271: attempt to add multi-table UPDATE criteria for joined-inh when the update is detected as impacting multiple tables in any case (zzzeek/sqlalchemy)

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

New issue 3271: attempt to add multi-table UPDATE criteria for joined-inh when the update is detected as impacting multiple tables in any case
https://bitbucket.org/zzzeek/sqlalchemy/issue/3271/attempt-to-add-multi-table-update-criteria

Mike Bayer:

[Sqlalchemy-tickets] Issue #3270: in the loop of large quantity query result, update operation costs strange time (zzzeek/sqlalchemy)

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

New issue 3270: in the loop of large quantity query result,  update operation costs strange time
https://bitbucket.org/zzzeek/sqlalchemy/issue/3270/in-the-loop-of-large-quantity-query-result

awanabe:

When I query a large quantity records, then loop the result. 
if I update one record, it will cost a lot time.

```
_users = db.session.query(WxUser).limit(10000).all()
for _user in _users:
    t1 = time()
    db.session.query(WxUser).filter_by(id=_user.id).update(dict(sex=WxUser.Sex.MALE))
    db.session.commit()
    t2 = time()
    print t2 - t1
```

out put:
```
0.242075920105
15.5323040485
16.6957418919
...
```
the commit operation cost time is more than normal.
when open the 'SQLALCHEMY_ECHO',  shows that SQLAlchemy query the every record by id. 

[Sqlalchemy-tickets] Issue #3269: Simple typo in sqlelement docs (zzzeek/sqlalchemy)

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

New issue 3269: Simple typo in sqlelement docs
https://bitbucket.org/zzzeek/sqlalchemy/issue/3269/simple-typo-in-sqlelement-docs

command_tab:

Noticed while reading the [sqlelement docs here](http://docs.sqlalchemy.org/en/rel_0_9/core/sqlelement.html#sqlalchemy.sql.expression.text):

```fom sqlalchemy import text
```

`fom` instead of `from`

[Sqlalchemy-tickets] Issue #3268: Custom data type when doing cascaded delete -- errors out (zzzeek/sqlalchemy)

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

New issue 3268: Custom data type when doing cascaded delete -- errors out
https://bitbucket.org/zzzeek/sqlalchemy/issue/3268/custom-data-type-when-doing-cascaded

taibit:

For custom data type that is UUID-like: 

If custom data types is defined so that python uses hex string and database uses char(16):
The delete statement needs to convert hex string to bytes for database, but
there seems to be a bug when doing cascaded deletes:
the delete does not convert the hex string to bytes.

If python uses byte constant for the custom data type (and bytes for database), then the cascaded delete is ok.


This bug test case uses a custom data type similar to the GUID type listed in sqlalchemy docs:
    Based on tip from sqlalchemy UUID/GUID docs: Can be modified to store binary in CHAR(16) if desired:
    http://docs.sqlalchemy.org/en/rel_0_9/core/types.html#backend-agnostic-guid-type


When you run this file, in the working cases, the last few lines will show successful delete from order_details:

2014-12-09 18:38:49,481 INFO sqlalchemy.engine.base.Engine COMMIT
2014-12-09 18:38:49,482 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2014-12-09 18:38:49,483 INFO sqlalchemy.engine.base.Engine SELECT orders.id AS o
rders_id, orders.order_name AS orders_order_name, orders.create_at AS orders_cre
ate_at
FROM orders
WHERE orders.id = ?
2014-12-09 18:38:49,483 INFO sqlalchemy.engine.base.Engine ('short_term_order_2'
,)
2014-12-09 18:38:49,486 INFO sqlalchemy.engine.base.Engine SELECT order_details.
a_long_key_id AS order_details_a_long_key_id, order_details.order_id AS order_de
tails_order_id, order_details.create_at AS order_details_create_at
FROM order_details
WHERE ? = order_details.order_id
2014-12-09 18:38:49,486 INFO sqlalchemy.engine.base.Engine ('short_term_order_2'
,)
2014-12-09 18:38:49,488 INFO sqlalchemy.engine.base.Engine DELETE FROM order_det
ails WHERE order_details.a_long_key_id = ?
2014-12-09 18:38:49,488 INFO sqlalchemy.engine.base.Engine ('11e4342387457d747ee
b51180c627777',)
2014-12-09 18:38:49,489 INFO sqlalchemy.engine.base.Engine DELETE FROM orders WH
ERE orders.id = ?
2014-12-09 18:38:49,490 INFO sqlalchemy.engine.base.Engine ('short_term_order_2'
,)
2014-12-09 18:38:49,490 INFO sqlalchemy.engine.base.Engine COMMIT




But in the broken case, the last lines will be an error with this message:

sqlalchemy.exc.StatementError: must be str, not bytes (original cause: TypeError
: must be str, not bytes) 'DELETE FROM order_details WHERE order_details.a_long_
key_id = ?' [{'a_long_key_id': b'11e4342387457d747eeb51180c627777'}]

[Sqlalchemy-tickets] Issue #3267: `__declare_last__` and multiple mixins (zzzeek/sqlalchemy)

[Jack Z. <iss...@bi...>]

New issue 3267: `__declare_last__` and multiple mixins
https://bitbucket.org/zzzeek/sqlalchemy/issue/3267/__declare_last__-and-multiple-mixins

Jack Zhou:

Currently, declarative appears to only support a single mixin with `__declare_last__` defined.

The relevant code from 0.9.8:

```
#!python
    for base in cls.__mro__:
        _is_declarative_inherits = hasattr(base, '_decl_class_registry')

        if '__declare_last__' in base.__dict__:
            @event.listens_for(mapper, "after_configured")
            def go():
                cls.__declare_last__()
        if '__declare_first__' in base.__dict__:
            @event.listens_for(mapper, "before_configured")
            def go():
                cls.__declare_first__()
```

As you can see, even though it inspects each class in the MRO, declarative calls `cls.__declare_last__`, which happens to be the first base class in the MRO that defines `__declare_last__`; it also calls it multiple times, as many times as there are bases that define `__declare_first__`. (master seems to have fixed the multiple calls issue, but is still limited to a single `__declare_last__`.)

I think it's better to allow each base class in the MRO to define its own `__declare_last__`:

```
#!python
    for base in cls.__mro__:
        _is_declarative_inherits = hasattr(base, '_decl_class_registry')

        if '__declare_last__' in base.__dict__:
            def closure(base):
                @event.listens_for(mapper, "after_configured")
                def go():
                    base.__dict__["__declare_last__"].__func__(cls)
            closure(base)
        if '__declare_first__' in base.__dict__:
            def closure(base):
                @event.listens_for(mapper, "before_configured")
                def go():
                    base.__dict__["__declare_first__"].__func__(cls)
            closure(base)
```

To me, this is more intuitive and useful for mixins. One downside I see is not being able to override the behavior of a mixin in a base class, which can potentially be troublesome. If you want to go with the multiple `__declare_last__` approach, I'd be glad to provide a patch for master.

[Sqlalchemy-tickets] Issue #3266: handle_dbapi_exception is missed on _revalidate_connection (zzzeek/sqlalchemy)

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

New issue 3266: handle_dbapi_exception is missed on _revalidate_connection
https://bitbucket.org/zzzeek/sqlalchemy/issue/3266/handle_dbapi_exception-is-missed-on

Mike Bayer:

this might be too risky for 0.9, here's a stack trace under 0.9.8 (the second one):


```
#!

(py27)vsergeev@vsergeev-pc:~/sandbox/nova$ python
Python 2.7.6 (default, Mar 22 2014, 22:59:56) 
[GCC 4.8.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from nova.db.sqlalchemy import api
>>> conn = api.get_engine().connect()
No handlers could be found for logger "oslo.db.sqlalchemy.session"
>>> conn.execute("select 1").scalar()
1L
>>> # stop mysql
... 
>>> conn.execute("select 1").scalar()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 721, in execute
    return self._execute_text(object, multiparams, params)
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 870, in _execute_text
    statement, parameters
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context
    context)
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1155, in _handle_dbapi_exception
    if newraise:
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
    context)
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute
    cursor.execute(statement, parameters)
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
oslo.db.exception.DBConnectionError: (OperationalError) (2006, 'MySQL server has gone away') 'select 1' ()
>>> conn.execute("select 1").scalar()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 721, in execute
    return self._execute_text(object, multiparams, params)
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 870, in _execute_text
    statement, parameters
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 893, in _execute_context
    None, None)
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1155, in _handle_dbapi_exception
    if newraise:
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 236, in connection
    return self._revalidate_connection()
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 244, in _revalidate_connection
    self.__connection = self.engine.raw_connection()
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1848, in raw_connection
    return self.pool.unique_connection()
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 280, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 644, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 442, in checkout
    dbapi_connection = rec.get_connection()
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 506, in get_connection
    self.connection = self.__connect()
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 538, in __connect
    connection = self.__pool._creator()
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 96, in connect
    connection_invalidated=invalidated
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 90, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 377, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/home/vsergeev/sandbox/nova/.tox/py27/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 193, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (OperationalError) (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)") None None
```

[Sqlalchemy-tickets] Issue #3265: _AssociationSet has odd __eq__ behaviour (zzzeek/sqlalchemy)

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

New issue 3265: _AssociationSet has odd __eq__ behaviour
https://bitbucket.org/zzzeek/sqlalchemy/issue/3265/_associationset-has-odd-__eq__-behaviour

Saif Hakim:

If I have association proxies for sets, I noticed that `__eq__` does't behave as expected. Namely, two empty association sets are not considered equal, nor is an association set equal to itself. 

This results from the implementation:

```
    def __eq__(self, other):
        return set(self) == other
```

If we reverse the terms, and instead use:

```
    def __eq__(self, other):
        return other == set(self)
```

Then the comparison of `set_a == set_b`, will end up doing `set_a.__eq__(set_b)` --> `set_b.__eq__(set(a))` --> `set(a).__eq__(set(b))` which seems to be the desired behavior. This same fix would be applied to most, if not all, of the comparison operators.

Though, `set() == empty_association_set` will still be False... but there isn't much you can do about that.

See the attached example using the User, Keyword example of AssociationProxy.

[Sqlalchemy-tickets] Issue #3264: has_table() in Postgresql doesnt work for temporary (zzzeek/sqlalchemy)

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

New issue 3264: has_table() in Postgresql doesnt work for temporary
https://bitbucket.org/zzzeek/sqlalchemy/issue/3264/has_table-in-postgresql-doesnt-work-for

Mike Bayer:


```
#!python

from sqlalchemy import *

metadata = MetaData()
user_tmp = Table(
    "user_tmp", metadata,
    Column("id", INT, primary_key=True),
    Column('name', VARCHAR(50)),
    prefixes= ['TEMPORARY']
)

e = create_engine("postgresql://scott:tiger@localhost/test", echo='debug')
user_tmp.create(e, checkfirst=True)

user_tmp.create(e, checkfirst=True)
```

[Sqlalchemy-tickets] Issue #3263: ValueError on fulltext match in MySQL (zzzeek/sqlalchemy)

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

New issue 3263: ValueError on fulltext match in MySQL
https://bitbucket.org/zzzeek/sqlalchemy/issue/3263/valueerror-on-fulltext-match-in-mysql

Ian Carroll:

Column.match() implements the MATCH...AGAINST query on a MySQL FullText index incorrectly, in that it expects a Boolean value (but only in some uses). The attached, annotated codelet generates two errors (protected by try...except statements). The first one is only loosely related. The second one is the ValueError raised when returning results from a query with Column.match().

SQLAlchemy (0.9.8)
MySQL (5.6.22)
mysql-connector-python (2.0.2)

[Sqlalchemy-tickets] Issue #3262: Warn on duplicate polymorphic_identity (zzzeek/sqlalchemy)

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

New issue 3262: Warn on duplicate polymorphic_identity
https://bitbucket.org/zzzeek/sqlalchemy/issue/3262/warn-on-duplicate-polymorphic_identity

Sebastian Bank:

Currently, reuse of a value for `polymorphic_identity` just overwrites the old mapping.
This can lead hard to debug failiures (queried instances having unexpected types):
```python
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base


class Spam(declarative_base()):

    __tablename__ = 'spam'

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

    __mapper_args__ = {'polymorphic_on': kind, 'polymorphic_identity': 'spam'}


class Eggs(Spam):

   __tablename__ = 'eggs'

   id = Column(Integer, ForeignKey('spam.id'), primary_key=True)

   __mapper_args__ = {'polymorphic_identity': 'spam'}  # warn here


engine = create_engine('sqlite://')

Spam.metadata.create_all(engine)

session = Session(engine)

session.add(Spam())
session.flush()
spam = session.query(Spam).one()

assert type(spam) is Spam  # fails
```
Provided there is no use case for overwriting, it would be nice to make the user aware of such misconfiguration at an early development stage.

[Sqlalchemy-tickets] Issue #3261: sqlite backend ignores names on foreign key constraints (zzzeek/sqlalchemy)

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

New issue 3261: sqlite backend ignores names on foreign key constraints
https://bitbucket.org/zzzeek/sqlalchemy/issue/3261/sqlite-backend-ignores-names-on-foreign

Mike Bayer:

this is to complement #3244.  While for UNIQUE, we use `.schema` to get at the unique constraints, therefore we have the names, for FOREIGN KEY we use `.pragma` which does *not* give us the names, however `.schema` does.  Foreign key reflection should therefore be rewritten to use parsing of `.schema`, however at the same time I'd like to keep the `.pragma` call in place as well as a means to double check what we get back.

this is also critical for Alembic support.

[Sqlalchemy-tickets] Issue #3260: CHECK constraints assocaited w/ Boolean, Enum get doubled on tometadata() (zzzeek/sqlalchemy)

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

New issue 3260: CHECK constraints assocaited w/ Boolean, Enum get doubled on tometadata()
https://bitbucket.org/zzzeek/sqlalchemy/issue/3260/check-constraints-assocaited-w-boolean

Mike Bayer:


```
#!diff

diff --git a/test/sql/test_metadata.py b/test/sql/test_metadata.py
index 3f24fd0..74044e3 100644
--- a/test/sql/test_metadata.py
+++ b/test/sql/test_metadata.py
@@ -1473,6 +1473,46 @@ class SchemaTypeTest(fixtures.TestBase):
 
         m1.create_all(testing.db)
 
+    def test_boolean_constraint_type_doesnt_double(self):
+        m1 = MetaData()
+
+        t1 = Table('x', m1, Column("flag", Boolean()))
+        eq_(
+            len([
+                c for c in t1.constraints
+                if isinstance(c, CheckConstraint)]),
+            1
+        )
+        m2 = MetaData()
+        t2 = t1.tometadata(m2)
+
+        eq_(
+            len([
+                c for c in t2.constraints
+                if isinstance(c, CheckConstraint)]),
+            1
+        )
+
+    def test_enum_constraint_type_doesnt_double(self):
+        m1 = MetaData()
+
+        t1 = Table('x', m1, Column("flag", Enum('a', 'b', 'c')))
+        eq_(
+            len([
+                c for c in t1.constraints
+                if isinstance(c, CheckConstraint)]),
+            1
+        )
+        m2 = MetaData()
+        t2 = t1.tometadata(m2)
+
+        eq_(
+            len([
+                c for c in t2.constraints
+                if isinstance(c, CheckConstraint)]),
+            1
+        )
+
 

```

[Sqlalchemy-tickets] Issue #3259: Session and "pool_recycle" option (zzzeek/sqlalchemy)

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

New issue 3259: Session and "pool_recycle" option
https://bitbucket.org/zzzeek/sqlalchemy/issue/3259/session-and-pool_recycle-option

theking-laptop:

This is not issue, actually, it's a question.
I suppose I have a global scoped session object to use in Pyramid web application.
```
DBSession = scoped_session(
                sessionmaker(
                    autoflush=False,
                    autocommit=False,
                    extension=ZopeTransactionExtension()
                    )
                )
```

I want to use `pool_recycle` option because if no activity in specific time, client will be closed automatically and an error `MySQL Server has gone away` will be thrown. But I wonder, this operation pings to MySQL server only, that means current session if not be committed still remains or this operation drops everything such as connection, engine, etc... including all sessions?

This aspect is very important for web application, so I really want to know exact answer. I could not find this information in documentation.

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3258: gevent + sqlalchemy connection bug back in later version (zzzeek/sqlalchemy)

[Lx Y. <iss...@bi...>]

New issue 3258: gevent + sqlalchemy connection bug back in later version
https://bitbucket.org/zzzeek/sqlalchemy/issue/3258/gevent-sqlalchemy-connection-bug-back-in

Lx Yu:

the same error reported in https://bitbucket.org/zzzeek/sqlalchemy/issue/3043/error-file-descriptor-was-closed-in back again.

Currently the most stable version is 0.9.3, which only produce small amount of AttributeError, and versions after produces different exceptions, especially more excs since 0.9.5

I tested python-mysql-connector + gevent + sqlalchemy too and the problem is the same, so I assume the bug is in the sqlalchemy side.

The test was done with your benchmark script, both test script and produced log pasted here:

https://gist.github.com/lxyu/7cbd6cbe83b47e581c8d

The bug is very serious since it may cause strange bug in mysql transaction, we have captured situation where only part of the transaction succeed in our prod servers.

[Sqlalchemy-tickets] Issue #3257: DATE/DATETIME/TIME names on SQLite use NUMERIC affinity (zzzeek/sqlalchemy)

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

New issue 3257: DATE/DATETIME/TIME names on SQLite use NUMERIC affinity
https://bitbucket.org/zzzeek/sqlalchemy/issue/3257/date-datetime-time-names-on-sqlite-use

Mike Bayer:

this NUMERIC affinity is bypassed when our ISO date format puts non-numerics in, but if the format is changed to be all digits, it fails.  We can consider changing the DDL names on SQLite to DATE_CHAR, DATETIME_CHAR, TIME_CHAR or similar.   would need to receive both names within a reflection context:

```
#!python


from sqlalchemy.dialects.sqlite import DATE, DATETIME, TIME
from sqlalchemy.ext.compiler import compiles

# fix by using CHAR_ (or TEXT, whatever)
#@compiles(DATE, 'sqlite')
#@compiles(DATETIME, 'sqlite')
#@compiles(TIME, 'sqlite')
def compile_date(element, compiler, **kw):
    return "CHAR_%s" % element.__class__.__name__

from sqlalchemy import Table, Column, create_engine, MetaData, select
import datetime


m = MetaData()
t = Table(
    'dates', m,
    Column('date', DATE(
        storage_format="%(year)04d%(month)02d%(day)02d",
        regexp=r"(\d{4})(\d{2})(\d{2})",
    )),
    Column('datetime', DATETIME(
        storage_format="%(year)04d%(month)02d%(day)02d%(hour)02d%(minute)02d%(second)02d",
        regexp=r"(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})",
    )),
    Column('time', TIME(
        storage_format="%(hour)02d%(minute)02d%(second)02d",
        regexp=r"(\d{2})(\d{2})(\d{2})",
    ))
)

e = create_engine("sqlite://", echo='debug')
m.create_all(e)

now = datetime.datetime.today().replace(microsecond=0)
nowdate = now.date()
nowtime = now.time()

with e.begin() as conn:
    conn.execute(
        t.insert().values(
            date=nowdate,
            datetime=now,
            time=nowtime
        )
    )
    row = conn.execute(select([t.c.date, t.c.datetime, t.c.time])).first()
    assert row == (nowdate, now, nowtime), "%s %s" % (row, (nowdate, now, nowtime))

```


Responsible: sqlalchemy_sprinters

[Sqlalchemy-tickets] Issue #3256: allow of_type() strategies to be stacked (zzzeek/sqlalchemy)

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

New issue 3256: allow of_type() strategies to be stacked
https://bitbucket.org/zzzeek/sqlalchemy/issue/3256/allow-of_type-strategies-to-be-stacked

Mike Bayer:


```
#!python

from sqlalchemy import Integer, ForeignKey, String, Column, create_engine
from sqlalchemy.orm import relationship, joinedload, with_polymorphic, Session
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

import logging
logging.basicConfig()
logging.getLogger("sqlalchemy.orm.path_registry").setLevel(logging.DEBUG)


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    b_id = Column(ForeignKey('b.id'))
    b = relationship("B")


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

    __mapper_args__ = {
        'polymorphic_on': type,
    }


class BSub1(B):
    __mapper_args__ = {
        'polymorphic_identity': 'sub1',
    }
    c = relationship("C1", uselist=False)


class BSub2(B):
    __mapper_args__ = {
        'polymorphic_identity': 'sub2',
    }
    c = relationship("C2", uselist=False)


class C1(Base):
    __tablename__ = 'c1'
    id = Column(Integer, primary_key=True)
    b_id = Column(ForeignKey('b.id'))


class C2(Base):
    __tablename__ = 'c2'
    id = Column(Integer, primary_key=True)
    b_id = Column(ForeignKey('b.id'))

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

s = Session(e)
s.add_all([
    A(b=BSub1(c=C1())),
    A(b=BSub2(c=C2()))
])
s.commit()

b_poly = with_polymorphic(B, [BSub1, BSub2], aliased=True)

q = s.query(A).options(
    # works
    # joinedload(A.b.of_type(b_poly)).joinedload(b_poly.BSub1.c),
    # joinedload(A.b.of_type(b_poly)).joinedload(b_poly.BSub2.c),
    # fails, eager loads only one of them
    joinedload(A.b.of_type(BSub1)).joinedload(BSub1.c),
    joinedload(A.b.of_type(BSub2)).joinedload(BSub2.c),
)

for a in q:
    b = a.b
    print b.c

```