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

[Sqlalchemy-tickets] Issue #3382: how create database (zzzeek/sqlalchemy)

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

New issue 3382: how create database
https://bitbucket.org/zzzeek/sqlalchemy/issue/3382/how-create-database

Unknown Name:

Only in this way to create:

from sqlalchemy import create_engine
from sqlalchemy.engine.url import make_url

url = mysql://root:root@localhost:3306/wiki20?charset=utf8
engine = create_engine(url, echo=True)
database=make_url(url).translate_connect_args().get('database')
if database:
        engine.connect().execute("create database if not exists %s"%database)

Responsible: sqlalchemy_sprinters

[Sqlalchemy-tickets] Issue #3381: literal binds mode doesn't work for firebird limit/offset (zzzeek/sqlalchemy)

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

New issue 3381: literal binds mode doesn't work for firebird limit/offset
https://bitbucket.org/zzzeek/sqlalchemy/issue/3381/literal-binds-mode-doesnt-work-for

Mike Bayer:

we need to add **kw to limit_clause(), but also get_select_precolumns() so that these can be propagated through.

```
#!

________________________________________________________________ LimitOffsetTest_firebird_kinterbasdb.test_limit_offset_nobinds ________________________________________________________________
Traceback (most recent call last):
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/suite/test_select.py", line 155, in test_limit_offset_nobinds
    compile_kwargs={"literal_binds": True})
  File "<string>", line 1, in <lambda>
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/sql/elements.py", line 494, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/sql/elements.py", line 500, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/sql/compiler.py", line 392, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/sql/compiler.py", line 190, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/sql/compiler.py", line 213, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/sql/compiler.py", line 1605, in visit_select
    text, select, inner_columns, froms, byfrom, kwargs)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/sql/compiler.py", line 1708, in _compose_select_body
    text += self.limit_clause(select, **kwargs)
TypeError: limit_clause() got an unexpected keyword argument 'literal_binds'

```

[Sqlalchemy-tickets] Issue #3380: invalid boolean check for limit/offset clause in firebird (zzzeek/sqlalchemy)

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

New issue 3380: invalid boolean check for limit/offset clause in firebird
https://bitbucket.org/zzzeek/sqlalchemy/issue/3380/invalid-boolean-check-for-limit-offset

Mike Bayer:


```
#!python

from sqlalchemy import table, column, select
from sqlalchemy.dialects import firebird


t = table('test', column('id'))
stmt = select([t]).limit(5)


print(stmt.compile(dialect=firebird.dialect()))
```


regression in 1.0 due to #3034

[Sqlalchemy-tickets] Issue #3379: allow external APIs control over invalidation (zzzeek/sqlalchemy)

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

New issue 3379: allow external APIs control over invalidation
https://bitbucket.org/zzzeek/sqlalchemy/issue/3379/allow-external-apis-control-over

Mike Bayer:

so that pool events can be used for clustering and other special cases where we want to invalidate only a subset of connections and have control over whether connections are bumped immediately, or only on next checkout, e.g.:

```
#!python




from sqlalchemy import create_engine
from sqlalchemy import event
import collections
import random

e = create_engine("mysql://scott:tiger@localhost/test")

hosts = ["host1", "host2", "host3"]

conn_recs = collections.defaultdict(set)


@event.listens_for(e, "do_connect")
def do_connect(dialect, conn_rec, carg, cparam):
    host = random.choice(hosts)

    cparam['host'] = host

    conn_rec.info['host'] = host
    conn_recs['host'].add(conn_rec)


@event.listens_for(e, "handle_error")
def handle_error(ctx):
    if ctx.is_disconnect:
        host = ctx.connection.info['host']
        ctx.invalidate_pool_on_disconnect = False
        ctx.connection.invalidate(soft=True)
        for conn_rec in conn_recs[host]:
            conn_rec.invalidate(soft=True)

```

[Sqlalchemy-tickets] Issue #3378: ddl sorting of tables doesn't occur for SQLite, due to "no alter support" (zzzeek/sqlalchemy)

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

New issue 3378: ddl sorting of tables doesn't occur for SQLite, due to "no alter support"
https://bitbucket.org/zzzeek/sqlalchemy/issue/3378/ddl-sorting-of-tables-doesnt-occur-for

Mike Bayer:

due to #3282, we turn of FK sorting altogether if there's no ALTER support, for whatever reason only in the DROP and not the CREATE  (https://bitbucket.org/zzzeek/sqlalchemy/src/4442eb3e1edea1652ce0ab5f4d16925207dcf2c5/lib/sqlalchemy/sql/ddl.py?at=master#cl-810).  Which breaks if you have constraints enabled on SQLite.

[Sqlalchemy-tickets] Issue #3377: PDF Build broken since 0.9.9 (zzzeek/sqlalchemy)

[Gabi D. <iss...@bi...>]

New issue 3377: PDF Build broken since 0.9.9
https://bitbucket.org/zzzeek/sqlalchemy/issue/3377/pdf-build-broken-since-099

Gabi Davar:

See https://readthedocs.org/builds/sqlalchemy/

Obviously all link the the above PDF are broken too.

[Sqlalchemy-tickets] Issue #3376: SingletonThreadPool and threading.local() usage (zzzeek/sqlalchemy)

[André A. <iss...@bi...>]

New issue 3376: SingletonThreadPool and threading.local() usage
https://bitbucket.org/zzzeek/sqlalchemy/issue/3376/singletonthreadpool-and-threadinglocal

André Anjos:

I'm a bit puzzled by the SingletonThreadPool implementation. As per recommendation (see discussion on [this StackOverflow thread](http://stackoverflow.com/questions/1408171/thread-local-storage-in-python)), the call to `threading.local()` must be global, whereas in `sqlalchemy.pool`, it is rather local, at the constructor of the `SingletonThreadPool` object.

As a result, If many sessions are attempted to the database on the same process, the constructor of the SingletonThreadPool object will be triggered many times and a fake perception that the singleton does not exist will kick-in and trigger the re-construction of a connection.

After sometime, we start getting spurious messages:

```
Exception during reset or similar
Traceback (most recent call last):
  File "/idiap/project/beat/beat.env.develop/usr/lib/python2.7/site-packages/sqlalchemy/pool.py", line 568, in _finalize_fairy
    fairy._reset(pool)
  File "/idiap/project/beat/beat.env.develop/usr/lib/python2.7/site-packages/sqlalchemy/pool.py", line 702, in _reset
    pool._dialect.do_rollback(self)
  File "/idiap/project/beat/beat.env.develop/usr/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 412, in do_rollback
    dbapi_connection.rollback()
ProgrammingError: Cannot operate on a closed database.
```

We wonder if this is not the cause. Could somebody verify this please?

[Sqlalchemy-tickets] Issue #3375: parse None/True/False in engine_from_config (for pool_reset_on_return) (zzzeek/sqlalchemy)

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

New issue 3375: parse None/True/False in engine_from_config (for pool_reset_on_return)
https://bitbucket.org/zzzeek/sqlalchemy/issue/3375/parse-none-true-false-in

Daniel Kraus:

engine_from_config doesn't translate .ini true/false/None values to their python counterparts which means "pool_reset_on_return = None" doesn't work. (only 'commit' or 'rollback')

[Sqlalchemy-tickets] Issue #3374: negated_contains_or_equals inconsistently uses committed state, not state attr (zzzeek/sqlalchemy)

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

New issue 3374: negated_contains_or_equals inconsistently uses committed state, not state attr
https://bitbucket.org/zzzeek/sqlalchemy/issue/3374/negated_contains_or_equals-inconsistently

Mike Bayer:


```
#!python

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

Base = declarative_base()


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

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

s = Session()


stmt = s.query(B).filter(B.a == A(id=7)).statement.compile()
print stmt
print stmt.params


stmt = s.query(B).filter(B.a != A(id=7)).statement.compile()
print stmt
print stmt.params


```

output:


```
#!

SELECT b.id, b.a_id 
FROM b 
WHERE :param_1 = b.a_id
{u'param_1': 7}

SELECT b.id, b.a_id 
FROM b 
WHERE b.a_id != :a_id_1 OR b.a_id IS NULL
{u'a_id_1': None}

```

because for the latter its pulling from committed state.

[Sqlalchemy-tickets] Issue #3373: relationship comparisons to objects with None don't render IS NULL (zzzeek/sqlalchemy)

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

New issue 3373: relationship comparisons to objects with None don't render IS NULL
https://bitbucket.org/zzzeek/sqlalchemy/issue/3373/relationship-comparisons-to-objects-with

Mike Bayer:

e.g.:

    session.query(Address).filter(Address.user == User(id=None))

will produce:

    SELECT address.id AS address_id, address.user_id AS address_user_id,
    address.email_address AS address_email_address
    FROM address WHERE ? = address.user_id
    (None,)

This have to be fixed for all the relationship filter conditions, including filter() with both == and !=, with_parent(), etc.

see also #3371, which adds a warning for these.

[Sqlalchemy-tickets] Issue #3372: SQLAlchemy 1.0.0 tries to process type value when not expected (zzzeek/sqlalchemy)

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

New issue 3372: SQLAlchemy 1.0.0 tries to process type value when not expected
https://bitbucket.org/zzzeek/sqlalchemy/issue/3372/sqlalchemy-100-tries-to-process-type-value

Charles-Axel Dein:

Attached is the code to reproduce the error. Works fine with 0.9.9, fails with 1.0.0

```
ca@CAMBPB Downloads (chariots) % python poc_sqla_uuid.py
2015-04-20 13:02:42,846 INFO sqlalchemy.engine.base.Engine select version()
2015-04-20 13:02:42,847 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:42,848 INFO sqlalchemy.engine.base.Engine select current_schema()
2015-04-20 13:02:42,848 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:42,850 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2015-04-20 13:02:42,850 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:42,851 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2015-04-20 13:02:42,851 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:42,852 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2015-04-20 13:02:42,852 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:42,854 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
2015-04-20 13:02:42,854 INFO sqlalchemy.engine.base.Engine {'name': u'users'}
2015-04-20 13:02:42,856 INFO sqlalchemy.engine.base.Engine SELECT EXISTS (SELECT *
FROM users
WHERE users.uuid = %(uuid_1)s)
2015-04-20 13:02:42,857 INFO sqlalchemy.engine.base.Engine {'uuid_1': UUID('863599a9-05af-498e-ac8b-f593aec85f3b')}

ca@CAMBPB Downloads (chariots) % pip install sqlalchemy==1.0.0

...

ca@CAMBPB Downloads (chariots) % python poc_sqla_uuid.py
2015-04-20 13:02:59,597 INFO sqlalchemy.engine.base.Engine select version()
2015-04-20 13:02:59,597 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:59,598 INFO sqlalchemy.engine.base.Engine select current_schema()
2015-04-20 13:02:59,598 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:59,599 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2015-04-20 13:02:59,599 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:59,600 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2015-04-20 13:02:59,600 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:59,601 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2015-04-20 13:02:59,601 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:59,602 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2015-04-20 13:02:59,602 INFO sqlalchemy.engine.base.Engine {'name': u'users'}
2015-04-20 13:02:59,605 INFO sqlalchemy.engine.base.Engine SELECT EXISTS (SELECT *
FROM users
WHERE users.uuid = %(uuid_1)s)
2015-04-20 13:02:59,605 INFO sqlalchemy.engine.base.Engine {'uuid_1': UUID('66d2e759-3c42-47b5-906d-90df8456cc76')}
Traceback (most recent call last):
  File "poc_sqla_uuid.py", line 35, in <module>
    main()
  File "poc_sqla_uuid.py", line 32, in main
    conn.execute(query).scalar()
  File "/Users/ca/.virtualenvs/chariots/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 1053, in scalar
    return row[0]
  File "/Users/ca/.virtualenvs/chariots/lib/python2.7/site-packages/sqlalchemy/sql/type_api.py", line 979, in process
    return process_value(impl_processor(value), dialect)
  File "/Users/ca/.virtualenvs/chariots/lib/python2.7/site-packages/sqlalchemy_utils/types/uuid.py", line 76, in process_result_value
    return uuid.UUID(value)
  File "/usr/local/Cellar/python/2.7.9/Frameworks/Python.framework/Versions/2.7/lib/python2.7/uuid.py", line 134, in __init__
    raise ValueError('badly formed hexadecimal UUID string')
ValueError: badly formed hexadecimal UUID string
```

This fails because value is `False` (it's an exists query). Not sure why sqlalchemy tries to parse it as a UUID.

Using sqlachemy 0.9.9 (works) and 1.0.0 (does not work), sqlalchemy-utils (0.30.0).

[Sqlalchemy-tickets] Issue #3371: relationship comparisons to None are also leaking NEVER_SET, but also are potentially not forwards-compatible (zzzeek/sqlalchemy)

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

New issue 3371: relationship comparisons to None are also leaking NEVER_SET, but also are potentially not forwards-compatible
https://bitbucket.org/zzzeek/sqlalchemy/issue/3371/relationship-comparisons-to-none-are-also

Mike Bayer:


```
#!python

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

Base = declarative_base()


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

    status = Column(Integer)


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

    status = Column(Integer)

    a = relationship("A")

    special_a = relationship(
        "A",
        primaryjoin="and_(A.id == B.a_id, A.status == B.status)"
    )

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

s = Session(e)
print s.query(B).filter(B.special_a == A()).all()

```


in SA 1.0 we get the NEVER_SET symbols:

```
#!


SELECT b.id AS b_id, b.a_id AS b_a_id, b.status AS b_status 
FROM b 
WHERE ? = b.a_id AND ? = b.status
2015-04-20 15:04:06,533 INFO sqlalchemy.engine.base.Engine (symbol('NEVER_SET'), symbol('NEVER_SET'))
```

however, in 0.9 we still get a query that isn't forwards compatible, if we learn to fix those = comparisons to be "IS" for a NULL value:

```
#!


SELECT b.id AS b_id, b.a_id AS b_a_id, b.status AS b_status 
FROM b 
WHERE ? = b.a_id AND ? = b.status
2015-04-20 15:04:48,556 INFO sqlalchemy.engine.base.Engine (None, None)
```

above, if we ever fixed "? = b.status" to be "b.status IS NULL", the results would change.

therefore we should emit a warning, at least when the comparison is against a column that is not primary_key=True (for PK=true, which is the common case, PK cols are never NULL so the "False" comparison is OK).

[Sqlalchemy-tickets] Issue #3370: Text and length (zzzeek/sqlalchemy)

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

New issue 3370: Text and length
https://bitbucket.org/zzzeek/sqlalchemy/issue/3370/text-and-length

Leonardo Rossi:

Hi,
I tried to use PostgreSQL instead MySQL.
I note a problem connected with Text:

```python
ProgrammingError: (psycopg2.ProgrammingError) type modifier is not allowed for type "text"
LINE 5:  value TEXT(35) NOT NULL,
                ^
[SQL: "\nCREATE TABLE bib00x (\n\tid SERIAL NOT NULL, \n\ttag VARCHAR(6) DEFAULT '' NOT NULL, \n\tvalue TEXT(35) NOT NULL, \n\tPRIMARY KEY (id)\n)\n\n"]
```

And, as they wrote in [docs](http://www.postgresql.org/docs/9.1/static/datatype-character.html), Text doesn't accept length.

Docs: http://docs.sqlalchemy.org/en/latest/core/type_basics.html?highlight=text#sqlalchemy.types.Text

There is a possibility to specify length only for MySQL and ignore for other DB server?

[Sqlalchemy-tickets] Issue #3369: ORM attributed updated but not reflected in queries. (zzzeek/sqlalchemy)

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

New issue 3369: ORM attributed updated but not reflected in queries.
https://bitbucket.org/zzzeek/sqlalchemy/issue/3369/orm-attributed-updated-but-not-reflected

timtadh:

This is for SQL Alchemy 1.0.0b1 - release. 

The attached file reproduces the bug. It will print "passed test" if the bug is not present or "failed test" if the bug is present. You must first setup the database credentials in the test file.

The bug:

I create a new ORM object and then override a foreign key manually. I then add the row and commit it. In prior versions of SQLAlchemy (for instance 0.9.9) this will cause an integrity error as the child row is not present. In 1.0.0 the wrong attribute is rendered in the SQL insert query. This means no integrity error occurs and the insert succeeds. However, it has inserted the wrong data.

Enivronment:

pypy: 2.5.1 (cpython 2.7.9 compat)
SQLAlchemy: 1.0.0 (works in 0.9.9)
MySQL: 5.6

[Sqlalchemy-tickets] Issue #3368: symbol('NOT_SET') being used in a query (zzzeek/sqlalchemy)

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

New issue 3368: symbol('NOT_SET') being used in a query
https://bitbucket.org/zzzeek/sqlalchemy/issue/3368/symbol-not_set-being-used-in-a-query

Christophe Biocca:

Relation without Foreign Key, when lazyloading (immediately after a `session.flush()`), tries to use `symbol('NOT_SET')` in a query.

I've attached a simple script that reproduces the problem on postgres, although I don't believe it to be backend specific.

Only happens on 1.0.0, earlier versions aren't affected.

[Sqlalchemy-tickets] Issue #3367: sqlalchemy 1.0.0 does not allow specifying a join more than once (zzzeek/sqlalchemy)

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

New issue 3367: sqlalchemy 1.0.0 does not allow specifying a join more than once
https://bitbucket.org/zzzeek/sqlalchemy/issue/3367/sqlalchemy-100-does-not-allow-specifying-a

Charles-Axel Dein:

I'm upgrading a codebase to sqlalchemy 1.0.0beta5  from 0.9.9.

It seems that version 1 removed the ability to be forgiving about mentioning a join twice:

```
session.query(Toaster).join(Toast).join(Toast).all()
```

This will raise with postgres: `ProgrammingError: (psycopg2.ProgrammingError) table name "financing_statuses" specified more than once`

The same query run with sqla 0.9.9 runs fine, because I guess it removes the duplicate join.

I'm not sure that can be considered a regression given that joins should not be mentioned more than once, but it's definitely a different behavior from 0.9.9

[Sqlalchemy-tickets] Issue #3366: annotate parentmapper in primaryjoin / secondaryjoin (zzzeek/sqlalchemy)

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

New issue 3366: annotate parentmapper in primaryjoin / secondaryjoin
https://bitbucket.org/zzzeek/sqlalchemy/issue/3366/annotate-parentmapper-in-primaryjoin

Mike Bayer:

this is related to #3365 but is a more fundamental change:


```
#!diff

diff --git a/lib/sqlalchemy/orm/evaluator.py b/lib/sqlalchemy/orm/evaluator.py
index 1e828ff..fb59186 100644
--- a/lib/sqlalchemy/orm/evaluator.py
+++ b/lib/sqlalchemy/orm/evaluator.py
@@ -59,7 +59,9 @@ class EvaluatorCompiler(object):
                 )
             key = parentmapper._columntoproperty[clause].key
         else:
-            key = clause.key
+            raise UnevaluatableError(
+                "Cannot evaluate column: %s" % clause
+            )
 
         get_corresponding_attr = operator.attrgetter(key)
         return lambda obj: get_corresponding_attr(obj)
diff --git a/lib/sqlalchemy/orm/relationships.py b/lib/sqlalchemy/orm/relationships.py
index b649c9e..291af09 100644
--- a/lib/sqlalchemy/orm/relationships.py
+++ b/lib/sqlalchemy/orm/relationships.py
@@ -1942,6 +1942,7 @@ class JoinCondition(object):
         self._annotate_fks()
         self._annotate_remote()
         self._annotate_local()
+        self._annotate_parentmapper()
         self._setup_pairs()
         self._check_foreign_cols(self.primaryjoin, True)
         if self.secondaryjoin is not None:
@@ -2405,6 +2406,19 @@ class JoinCondition(object):
             self.primaryjoin, {}, locals_
         )
 
+    def _annotate_parentmapper(self):
+        if self.prop is None:
+            return
+
+        def parentmappers_(elem):
+            if "remote" in elem._annotations:
+                return elem._annotate({"parentmapper": self.prop.mapper})
+            elif "local" in elem._annotations:
+                return elem._annotate({"parentmapper": self.prop.parent})
+        self.primaryjoin = visitors.replacement_traverse(
+            self.primaryjoin, {}, parentmappers_
+        )
+
     def _check_remote_side(self):
         if not self.local_remote_pairs:
             raise sa_exc.ArgumentError(
@@ -2811,9 +2825,6 @@ class JoinCondition(object):
 
         bind_to_col = dict((binds[col].key, col) for col in binds)
 
-        # this is probably not necessary
-        lazywhere = _deep_deannotate(lazywhere)
-
         return lazywhere, bind_to_col, equated_columns
 
 

```

We'd just start tracking "parentmapper" throughout all PJ/SJ conditions.

[Sqlalchemy-tickets] Issue #3365: evaluator can't locate ORM entity when using a many-to-one comparison (zzzeek/sqlalchemy)

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

New issue 3365: evaluator can't locate ORM entity when using a many-to-one comparison
https://bitbucket.org/zzzeek/sqlalchemy/issue/3365/evaluator-cant-locate-orm-entity-when

Mike Bayer:

because we don't include the parentmapper annotation in the _optimized_compare():


```
#!python

from sqlalchemy import Column, Integer, Text, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, relationship

Base = declarative_base()


class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)


class Child(Base):
    __tablename__ = "child"
    _id_parent = Column(
        "id_parent", Integer, ForeignKey(Parent.id), primary_key=True)
    name = Column(Text, primary_key=True)
    parent = relationship(Parent)

engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
session = Session(engine)

p = Parent(id=1)
session.add(p)
session.commit()

c = Child(name="foo", parent=p)
session.add(c)
session.commit()

session.query(Child).filter(Child.parent == p).delete("evaluate")
```


There are two approaches.    

One is that we look into getting parentmapper back into PJ/SJ.   This was removed a long time ago but we might want to revisit this assumption as mechanics have changed a lot.  It would be nice if these annotations were set up at relationship startup time, rather than having to re-inject when we build up relationship comparator operations.

The other is probably what we should do here and is just search for the column in the local mapper.  We also shouldn't assume ".key" on the clause itself is important, there don't seem to be tests for that case and it should probably raise, so this needs a lot more tests:

```
#!diff

diff --git a/lib/sqlalchemy/orm/evaluator.py b/lib/sqlalchemy/orm/evaluator.py
index 1e828ff..814b28f 100644
--- a/lib/sqlalchemy/orm/evaluator.py
+++ b/lib/sqlalchemy/orm/evaluator.py
@@ -7,6 +7,7 @@
 
 import operator
 from ..sql import operators
+from .. import inspect
 
 
 class UnevaluatableError(Exception):
@@ -28,6 +29,7 @@ _notimplemented_ops = set(getattr(operators, op)
 class EvaluatorCompiler(object):
     def __init__(self, target_cls=None):
         self.target_cls = target_cls
+        self.mapper = inspect(target_cls) if target_cls is not None else None
 
     def process(self, clause):
         meth = getattr(self, "visit_%s" % clause.__visit_name__, None)
@@ -58,8 +60,12 @@ class EvaluatorCompiler(object):
                     parentmapper.class_
                 )
             key = parentmapper._columntoproperty[clause].key
+        elif self.mapper and clause in self.mapper._columntoproperty:
+            key = self.mapper._columntoproperty[clause].key
         else:
-            key = clause.key
+            raise UnevaluatableError(
+                "Can't evaluate column: %s" % clause
+            )
 
         get_corresponding_attr = operator.attrgetter(key)
         return lambda obj: get_corresponding_attr(obj)
diff --git a/lib/sqlalchemy/orm/relationships.py b/lib/sqlalchemy/orm/relationships.py
index b649c9e..7ac0ab5 100644
--- a/lib/sqlalchemy/orm/relationships.py
+++ b/lib/sqlalchemy/orm/relationships.py
@@ -1372,6 +1372,7 @@ class RelationshipProperty(StrategizedProperty):
 
         if adapt_source:
             criterion = adapt_source(criterion)
+
         return criterion
 
     def _lazy_none_clause(self, reverse_direction=False, adapt_source=None):

```

[Sqlalchemy-tickets] Issue #3364: Non-simple relationship between two joined table subclasses is queried incorrectly (zzzeek/sqlalchemy)

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

New issue 3364: Non-simple relationship between two joined table subclasses is queried incorrectly
https://bitbucket.org/zzzeek/sqlalchemy/issue/3364/non-simple-relationship-between-two-joined

Scott Torborg:

The test case below explains it better, but basically, creating a relationship between two joined table subclasses which includes a parent class column in the join conditions causes the relationship to be queried incorrectly.

Admittedly, this case *does* raise a warning, so it may be unintended usage:

```
/.../sqlalchemy/lib/sqlalchemy/orm/relationships.py:2368: SAWarning: Non-simple
column elements in primary join condition for property Cheese.published_pizzas
- consider using remote() annotations to mark the remote side.
```

However, it used to work, and I'm not sure this particular example can actually be fixed with annotations--so I think this may indicate a regression which could cause other problems as well.

Specifically, it works up until 0611baa889198421afa932f2af1524bd8826ed7d,
at which point the mapper initialization starts breaking (with ``TypeError:
'dict_values' object does not support indexing``). Then, 610e0594e249cd0bb28cb2bd4a7624f63f4510bb "fixes" that exception, after which the script below runs, but the assertion at the end fails.

Here's the test:

```python
from sqlalchemy import MetaData, Column, ForeignKey, types
from sqlalchemy.orm import create_session, relationship
from sqlalchemy.ext.declarative import declarative_base


metadata = MetaData('sqlite://')
metadata.bind.echo = True
Base = declarative_base(metadata=metadata)


class Food(Base):
    __tablename__ = 'foods'
    id = Column(types.Integer, primary_key=True)
    name = Column(types.Unicode(255), nullable=False)
    published = Column(types.Boolean, nullable=False, default=False)


class Pizza(Food):
    __tablename__ = 'pizzas'
    food_id = Column(None, ForeignKey('foods.id'), primary_key=True)
    cheese_id = Column(None, ForeignKey('cheeses.food_id'), nullable=False)

    cheese = relationship('Cheese',
                          primaryjoin='Pizza.cheese_id == Cheese.food_id',
                          backref='pizzas')


class Cheese(Food):
    __tablename__ = 'cheeses'
    food_id = Column(None, ForeignKey('foods.id'), primary_key=True)

    published_pizzas = relationship(
        'Pizza',
        primaryjoin=('and_(Pizza.cheese_id == Cheese.food_id,'
                     'Pizza.published == True)'))


metadata.create_all()
sess = create_session()

cheese = Cheese(name='Mozzarella', published=True)

pizza1 = Pizza(name='Plain', published=True, cheese=cheese)
pizza2 = Pizza(name='Pepperoni', published=False, cheese=cheese)
pizza3 = Pizza(name='Vegetarian', published=True, cheese=cheese)

sess.add_all([cheese, pizza1, pizza2, pizza3])
sess.flush()
sess.expunge_all()

mozzarella = sess.query(Cheese).first()

names = set(pizza.name for pizza in mozzarella.published_pizzas)
# Pepperoni isn't published, it shouldn't be returned
assert 'Pepperoni' not in names
```

[Sqlalchemy-tickets] Issue #3363: Implement bitmap indexes & index compression for MS SQL (zzzeek/sqlalchemy)

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

New issue 3363: Implement bitmap indexes & index compression for MS SQL
https://bitbucket.org/zzzeek/sqlalchemy/issue/3363/implement-bitmap-indexes-index-compression

Anthony Valencia:

currently, arguments such as **oracle_bitmap** & **oracle_compress** clearly specify the database type.  while this clearly indicates the supported databases, it leads me to believe that every database supporting bitmap indexes & index compression will have its own set of named arguments, as opposed to having the named arguments, **bitmap** & **compress**, while listing supported databases for those arguments.  perhaps implement a warning if it is turned on for an unsupporting database?  just some thoughts.

[Sqlalchemy-tickets] Issue #3362: metadata doesn't serialize naming convention (zzzeek/sqlalchemy)

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

New issue 3362: metadata doesn't serialize naming convention
https://bitbucket.org/zzzeek/sqlalchemy/issue/3362/metadata-doesnt-serialize-naming

Mike Bayer:


```
#!python

from sqlalchemy import Table, Column, MetaData, Integer
import pickle

m = MetaData(naming_convention={"pk": "%(table_name)s_pk"})
t = Table('t', m, Column('id', Integer, primary_key=True))

m2 = pickle.loads(pickle.dumps(m))

t2a = Table('t2', m, Column('id', Integer, primary_key=True))
t2b = Table('t2', m2, Column('id', Integer, primary_key=True))

assert t2a.primary_key.name == t2b.primary_key.name

```

[Sqlalchemy-tickets] Issue #3361: Python3.4 and SQLAlchemy documentation generator (zzzeek/sqlalchemy)

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

New issue 3361: Python3.4 and SQLAlchemy documentation generator
https://bitbucket.org/zzzeek/sqlalchemy/issue/3361/python34-and-sqlalchemy-documentation

eirnym:

I have project with SQLAlchemy. Almost all my models are with documentation as described in documentation. 

I want to use builtin `help()` to have some interactive help on my models but I have exception and don't know how to deal with.

I have project with SQLAlchemy. Almost all my models are with documentation as described in documentation. 

I want to use builtin `help()` to have some interactive help on my models but I have exception and don't know how to deal with.

Exception is: `TypeError: Boolean value of this clause is not defined`. After digging the code with `pdb`, I've found that it is caused with `__table__` attribute and python 3.4 (py3k?) `inspect` changes.

This example works fine with Python 2.7, but I develop Python 3.4+ application and I really need it fixed.

Python version: 3.4.3
SQLAlchemy version: 0.9.9, 1.0.0b5

My model:

    from sqlalchemy import Column, Integer, String, Unicode
    from sqlalchemy.ext.declarative import declarative_base
    
    Base = declarative_base()

    class Model(Base):
        __tablename__ = 'model'

        id = Column(Integer, primary_key=True, doc='ID column for this model')
        data = Column(String(250), nullable=False, unique=True, doc='Some model data')


    from sqlalchemy import Column, Integer, String, Unicode
    from sqlalchemy.ext.declarative import declarative_base
    
    Base = declarative_base()

    class Category(Base):
        __tablename__ = 'category'

        id = Column(Integer, primary_key=True)
        slug = Column(String(250), nullable=False, unique=True)


Exception:

    >>> help(model.Category)
    Traceback (most recent call last):
        File "<stdin>", line 1, in <module>
        File "/opt/local/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/_sitebuiltins.py", line 103, in __call__
            return pydoc.help(*args, **kwds)
        File "/opt/local/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/pydoc.py", line 1827, in __call__
            self.help(request)
       File "/opt/local/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/pydoc.py", line 1877, in help
         else: doc(request, 'Help on %s:', output=self._output)
       File "/opt/local/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/pydoc.py", line 1614, in doc
        pager(render_doc(thing, title, forceload))
       File "/opt/local/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/pydoc.py", line 1607, in render_doc
         return title % desc + '\n\n' + renderer.document(object, name)
      File "/opt/local/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/pydoc.py", line 372, in document
        if inspect.isclass(object): return self.docclass(*args)
      File "/opt/local/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/pydoc.py", line 1272, in docclass
        for name, kind, cls, value in classify_class_attrs(object)
      File "/opt/local/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/pydoc.py", line 205, in classify_class_attrs
        for (name, kind, cls, value) in inspect.classify_class_attrs(object):
      File "/opt/local/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/inspect.py", line 405, in classify_class_attrs
        obj = get_obj or dict_obj
      File "/Users/eirnym/venv/34/ens/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 538, in __bool__
        raise TypeError("Boolean value of this clause is not defined")
    TypeError: Boolean value of this clause is not defined

[Sqlalchemy-tickets] Issue #3360: mssql test failure regressions in 1.0 (zzzeek/sqlalchemy)

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

New issue 3360: mssql test failure regressions in 1.0
https://bitbucket.org/zzzeek/sqlalchemy/issue/3360/mssql-test-failure-regressions-in-10

Mike Bayer:

in test/sql/test_query to start; these do not occur in 0.9

```
#!


======================================================================= FAILURES ========================================================================
________________________________________________ QueryTest_mssql_pymssql.test_implicit_id_insert_select _________________________________________________
Traceback (most recent call last):
  File "/Users/classic/dev/sqlalchemy/test/sql/test_query.py", line 283, in test_implicit_id_insert_select
    testing.db.execute(stmt)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/engine/base.py", line 1978, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/engine/base.py", line 1332, in _handle_dbapi_exception
    exc_info
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/engine/default.py", line 442, in do_execute
    cursor.execute(statement, parameters)
  File "pymssql.pyx", line 465, in pymssql.Cursor.execute (pymssql.c:6572)
OperationalError: (pymssql.OperationalError) (544, "Cannot insert explicit value for identity column in table 'query_users' when IDENTITY_INSERT is set to OFF.DB-Lib error message 544, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n") [SQL: 'INSERT INTO query_users (user_id, user_name) SELECT query_users.user_id, query_users.user_name \nFROM query_users \nWHERE query_users.user_id = %(user_id_1)s'] [parameters: {'user_id_1': 20}]
_____________________________________________________ QueryTest_mssql_pymssql.test_lastrow_accessor _____________________________________________________
Traceback (most recent call last):
  File "/Users/classic/dev/sqlalchemy/test/sql/test_query.py", line 226, in test_lastrow_accessor
    i = insert_values(engine, table, values)
  File "/Users/classic/dev/sqlalchemy/test/sql/test_query.py", line 133, in insert_values
    ret[c.key] = row[c]
TypeError: 'NoneType' object has no attribute '__getitem__'
____________________________________________________ QueryTest_mssql_pymssql.test_misordered_lastrow ____________________________________________________
Traceback (most recent call last):
  File "<string>", line 2, in test_misordered_lastrow
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/exclusions.py", line 95, in decorate
    return self._do(config._current, fn, *args, **kw)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/exclusions.py", line 124, in _do
    self._expect_failure(config, ex, name=fn.__name__)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/exclusions.py", line 136, in _expect_failure
    util.raise_from_cause(ex)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/exclusions.py", line 122, in _do
    return_value = fn(*args, **kw)
  File "/Users/classic/dev/sqlalchemy/test/sql/test_query.py", line 273, in test_misordered_lastrow
    assert id == 12
AssertionError: assert None == 12
_____________________________________________________ TableInsertTest_mssql_pymssql.test_uppercase ______________________________________________________
Traceback (most recent call last):
  File "/Users/classic/dev/sqlalchemy/test/sql/test_query.py", line 1538, in test_uppercase
    inserted_primary_key=[1]
  File "/Users/classic/dev/sqlalchemy/test/sql/test_query.py", line 1512, in _test
    eq_(r.inserted_primary_key, inserted_primary_key)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/assertions.py", line 211, in eq_
    assert a == b, msg or "%r != %r" % (a, b)
AssertionError: [None] != [1]
______________________________________________ TableInsertTest_mssql_pymssql.test_uppercase_direct_params _______________________________________________
Traceback (most recent call last):
  File "/Users/classic/dev/sqlalchemy/test/sql/test_query.py", line 1573, in test_uppercase_direct_params
    inserted_primary_key=[1]
  File "/Users/classic/dev/sqlalchemy/test/sql/test_query.py", line 1512, in _test
    eq_(r.inserted_primary_key, inserted_primary_key)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/assertions.py", line 211, in eq_
    assert a == b, msg or "%r != %r" % (a, b)
AssertionError: [None] != [1]
================================================================ short test summary info ================================================================
FAIL test/sql/test_query.py::QueryTest_mssql_pymssql::()::test_implicit_id_insert_select
FAIL test/sql/test_query.py::QueryTest_mssql_pymssql::()::test_lastrow_accessor
FAIL test/sql/test_query.py::QueryTest_mssql_pymssql::()::test_misordered_lastrow
FAIL test/sql/test_query.py::TableInsertTest_mssql_pymssql::()::test_uppercase
FAIL test/sql/test_query.py::TableInsertTest_mssql_pymssql::()::test_uppercase_direct_params
```

[Sqlalchemy-tickets] Issue #3359: py3k support for MySQLdb via pymysql (zzzeek/sqlalchemy)

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

New issue 3359: py3k support for MySQLdb via pymysql
https://bitbucket.org/zzzeek/sqlalchemy/issue/3359/py3k-support-for-mysqldb-via-pymysql

Thomas Tanner:

sqlalchemy HEAD, mysql 5.5.38, pymysql 0.6.6,
mysql=mysql+pysql://user:password@localhost/test_schema
running
`py.test --db mysql --dropfirst --backend-only --log-info=sqlalchemy.orm.mapper  --log-debug=sqlalchemy.pool --log-info=sqlalchemy.engine`

```
platform darwin -- Python 3.4.3 -- py-1.4.26 -- pytest-2.7.0 --

DEBUG:sqlalchemy.pool.QueuePool:Created new connection <pymysql.connections.Connection object at 0x10800cf60>
INFO:sqlalchemy.engine.base.Engine:SHOW VARIABLES LIKE 'sql_mode'
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:SELECT DATABASE()
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1
INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> checked out from pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> rollback-on-return
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> checked out from pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> rollback-on-return
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> checked out from pool
INFO:sqlalchemy.engine.base.Engine:SHOW FULL TABLES FROM `test_schema`
INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> rollback-on-return
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> checked out from pool
INFO:sqlalchemy.engine.base.Engine:SHOW FULL TABLES FROM `test_schema`
INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> rollback-on-return
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> checked out from pool
DEBUG:sqlalchemy.pool.QueuePool:Created new connection <pymysql.connections.Connection object at 0x108031c18>
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x108031c18> checked out from pool
INFO:sqlalchemy.engine.base.Engine:SHOW FULL TABLES FROM `test_schema`
INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x108031c18> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x108031c18> rollback-on-return
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> rollback-on-return
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x108031c18> checked out from pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> checked out from pool
INFO:sqlalchemy.engine.base.Engine:SHOW FULL TABLES FROM `test_schema`
INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> rollback-on-return
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x108031c18> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x108031c18> rollback-on-return
============================= test session starts ==============================
platform darwin -- Python 3.4.3 -- py-1.4.26 -- pytest-2.7.0 -- 

collecting ... DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> checked out from pool
INFO:sqlalchemy.engine.base.Engine:SELECT CURRENT_TIMESTAMP AS current_timestamp_1
INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> rollback-on-return
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x108031c18> checked out from pool
INFO:sqlalchemy.engine.base.Engine:SHOW VARIABLES LIKE 'lower_case_table_names'
INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x108031c18> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x108031c18> rollback-on-return
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> checked out from pool
INFO:sqlalchemy.engine.base.Engine:SHOW VARIABLES LIKE 'lower_case_table_names'
INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> rollback-on-return
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x108031c18> checked out from pool
INFO:sqlalchemy.engine.base.Engine:SHOW VARIABLES LIKE 'lower_case_table_names'
INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x108031c18> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x108031c18> rollback-on-return
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> checked out from pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> rollback-on-return
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x108031c18> checked out from pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x108031c18> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x108031c18> rollback-on-return
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> checked out from pool
INFO:sqlalchemy.engine.base.Engine:SHOW VARIABLES LIKE 'lower_case_table_names'
INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pymysql.connections.Connection object at 0x10800cf60> rollback-on-return
collected 1846 items

test/aaa_profiling/test_compiler.py::CompileTest_mysql_pymysql::test_insert PASSED
test/aaa_profiling/test_compiler.py::CompileTest_mysql_pymysql::test_select PASSED
test/aaa_profiling/test_compiler.py::CompileTest_mysql_pymysql::test_select_labels PASSED
test/aaa_profiling/test_compiler.py::CompileTest_mysql_pymysql::test_update PASSED
test/aaa_profiling/test_compiler.py::CompileTest_mysql_pymysql::test_update_whereclause PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_DecimalResultProcessor_init PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_DecimalResultProcessor_process PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_UnicodeResultProcessor_init PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_ad_hoc_types PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_alias_pathing PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_fixture PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_join_cache PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_key_fallback_result PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_many_discarded_relationships PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_many_updates PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_mapper_reset PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_orm_many_engines PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_path_registry PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_session PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_sessionmaker PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_type_compile PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_unicode_warnings PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_warnings_util PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_with_inheritance PASSED
test/aaa_profiling/test_memusage.py::MemUsageTest_mysql_pymysql::test_with_manytomany PASSED
test/aaa_profiling/test_resultset.py::ExecutionTest_mysql_pymysql::test_minimal_connection_execute FAILEDINFO:sqlalchemy.pool.QueuePool:Pool disposed. Pool size: 5  Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0
INFO:sqlalchemy.pool.QueuePool:Pool recreating
Encountered a stray connection in test cleanup: {<sqlalchemy.pool._ConnectionRecord object at 0x10ad34f28>}
Traceback (most recent call last):
  File "bin/py.test", line 9, in <module>
    load_entry_point('pytest==2.7.0', 'console_scripts', 'py.test')()
  File "$site/_pytest/config.py", line 41, in main
    return config.hook.pytest_cmdline_main(config=config)
  File "$site/_pytest/core.py", line 521, in __call__
    return self._docall(self.methods, kwargs)
  File "$site/_pytest/core.py", line 528, in _docall
    firstresult=self.firstresult).execute()
  File "$site/_pytest/core.py", line 394, in execute
    res = method(*args)
  File "$site/_pytest/main.py", line 116, in pytest_cmdline_main
    return wrap_session(config, _main)
  File "$site/_pytest/main.py", line 109, in wrap_session
    exitstatus=session.exitstatus)
  File "$site/_pytest/core.py", line 521, in __call__
    return self._docall(self.methods, kwargs)
  File "$site/_pytest/core.py", line 528, in _docall
    firstresult=self.firstresult).execute()
  File "$site/_pytest/core.py", line 393, in execute
    return wrapped_call(method(*args), self.execute)
  File "$site/_pytest/core.py", line 109, in wrapped_call
    wrap_controller.send(call_outcome)
  File "$site/_pytest/terminal.py", line 356, in pytest_sessionfinish
    outcome.get_result()
  File "$site/_pytest/core.py", line 137, in get_result
    raise ex[1].with_traceback(ex[2])
  File "$site/_pytest/core.py", line 123, in __init__
    self.result = func()
  File "$site/_pytest/core.py", line 394, in execute
    res = method(*args)
  File "$site/_pytest/runner.py", line 55, in pytest_sessionfinish
    session._setupstate.teardown_all()
  File "$site/_pytest/runner.py", line 375, in teardown_all
    self._pop_and_teardown()
  File "$site/_pytest/runner.py", line 348, in _pop_and_teardown
    self._teardown_with_finalization(colitem)
  File "$site/_pytest/runner.py", line 366, in _teardown_with_finalization
    self._callfinalizers(colitem)
  File "$site/_pytest/runner.py", line 363, in _callfinalizers
    py.builtin._reraise(*exc)
  File "$site/py/_builtin.py", line 227, in _reraise
    raise cls.with_traceback(val, tb)
  File "$site/_pytest/runner.py", line 356, in _callfinalizers
    fin()
  File "$path/test/../lib/sqlalchemy/testing/plugin/pytestplugin.py", line 149, in finalize
    class_teardown(item.parent.parent)
  File "$path/test/../lib/sqlalchemy/testing/plugin/pytestplugin.py", line 178, in class_teardown
    plugin_base.stop_test_class(item.cls)
  File "$path/test/../lib/sqlalchemy/testing/plugin/plugin_base.py", line 426, in stop_test_class
    assertions.global_cleanup_assertions()
  File "$path/test/../lib/sqlalchemy/testing/assertions.py", line 165, in global_cleanup_assertions
    _assert_no_stray_pool_connections()
  File "$path/test/../lib/sqlalchemy/testing/assertions.py", line 203, in _assert_no_stray_pool_connections
    "after gc.collect(): %s" % err
AssertionError: Stray connection refused to leave after gc.collect(): {<sqlalchemy.pool._ConnectionRecord object at 0x10ad34f28>}
```

without logging and with duplicate errors removed (table users exists):
```
==================================== ERRORS ====================================
 ERROR at setup of ComponentReflectionTest_mysql_pymysql.test_autoincrement_col 
Traceback (most recent call last):
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "$path/test/../lib/sqlalchemy/engine/default.py", line 442, in do_execute
    cursor.execute(statement, parameters)
  File "$site/pymysql/cursors.py", line 134, in execute
    result = self._query(query)
  File "$site/pymysql/cursors.py", line 282, in _query
    conn.query(q)
  File "$site/pymysql/connections.py", line 768, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "$site/pymysql/connections.py", line 929, in _read_query_result
    result.read()
  File "$site/pymysql/connections.py", line 1125, in read
    first_packet = self.connection._read_packet()
  File "$site/pymysql/connections.py", line 893, in _read_packet
    packet.check_error()
  File "$site/pymysql/connections.py", line 369, in check_error
    err.raise_mysql_exception(self._data)
  File "$site/pymysql/err.py", line 120, in raise_mysql_exception
    _check_mysql_exception(errinfo)
  File "$site/pymysql/err.py", line 115, in _check_mysql_exception
    raise InternalError(errno, errorvalue)
pymysql.err.InternalError: (1050, "Table 'users' already exists")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "$path/test/../lib/sqlalchemy/testing/fixtures.py", line 83, in setup_class
    cls._setup_once_tables()
  File "$path/test/../lib/sqlalchemy/testing/fixtures.py", line 112, in _setup_once_tables
    cls.metadata.create_all(cls.bind)
  File "$path/test/../lib/sqlalchemy/sql/schema.py", line 3611, in create_all
    tables=tables)
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 1843, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 1471, in _run_visitor
    **kwargs).traverse_single(element)
  File "$path/test/../lib/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "$path/test/../lib/sqlalchemy/sql/ddl.py", line 727, in visit_metadata
    _is_metadata_operation=True)
  File "$path/test/../lib/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "$path/test/../lib/sqlalchemy/sql/ddl.py", line 761, in visit_table
    include_foreign_key_constraints=include_foreign_key_constraints
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "$path/test/../lib/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 968, in _execute_ddl
    compiled
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 1332, in _handle_dbapi_exception
    exc_info
  File "$path/test/../lib/sqlalchemy/util/compat.py", line 188, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "$path/test/../lib/sqlalchemy/util/compat.py", line 181, in reraise
    raise value.with_traceback(tb)
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "$path/test/../lib/sqlalchemy/engine/default.py", line 442, in do_execute
    cursor.execute(statement, parameters)
  File "$site/pymysql/cursors.py", line 134, in execute
    result = self._query(query)
  File "$site/pymysql/cursors.py", line 282, in _query
    conn.query(q)
  File "$site/pymysql/connections.py", line 768, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "$site/pymysql/connections.py", line 929, in _read_query_result
    result.read()
  File "$site/pymysql/connections.py", line 1125, in read
    first_packet = self.connection._read_packet()
  File "$site/pymysql/connections.py", line 893, in _read_packet
    packet.check_error()
  File "$site/pymysql/connections.py", line 369, in check_error
    err.raise_mysql_exception(self._data)
  File "$site/pymysql/err.py", line 120, in raise_mysql_exception
    _check_mysql_exception(errinfo)
  File "$site/pymysql/err.py", line 115, in _check_mysql_exception
    raise InternalError(errno, errorvalue)
sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1050, "Table 'users' already exists") [SQL: '\nCREATE TABLE test_schema.users (\n\tuser_id INTEGER NOT NULL AUTO_INCREMENT, \n\ttest1 CHAR(5) NOT NULL, \n\ttest2 FLOAT(5) NOT NULL, \n\tparent_user_id INTEGER, \n\tPRIMARY KEY (user_id), \n\tFOREIGN KEY(parent_user_id) REFERENCES test_schema.users (user_id)\n)ENGINE=InnoDB\n\n']

...
=================================== FAILURES ===================================
_________________ EnumSetTest_mysql_pymysql.test_unicode_enum __________________
Traceback (most recent call last):
  File "<string>", line 2, in test_unicode_enum
  File "$path/test/../lib/sqlalchemy/testing/util.py", line 195, in provide_metadata
    return fn(*args, **kw)
  File "$path/test/dialect/mysql/test_types.py", line 914, in test_unicode_enum
    metadata.create_all()
  File "$path/test/../lib/sqlalchemy/sql/schema.py", line 3611, in create_all
    tables=tables)
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 1843, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 1471, in _run_visitor
    **kwargs).traverse_single(element)
  File "$path/test/../lib/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "$path/test/../lib/sqlalchemy/sql/ddl.py", line 727, in visit_metadata
    _is_metadata_operation=True)
  File "$path/test/../lib/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "$path/test/../lib/sqlalchemy/sql/ddl.py", line 761, in visit_table
    include_foreign_key_constraints=include_foreign_key_constraints
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "$path/test/../lib/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 968, in _execute_ddl
    compiled
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 1335, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "$path/test/../lib/sqlalchemy/util/compat.py", line 182, in reraise
    raise value
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "$path/test/../lib/sqlalchemy/engine/default.py", line 442, in do_execute
    cursor.execute(statement, parameters)
  File "$site/pymysql/cursors.py", line 134, in execute
    result = self._query(query)
  File "$site/pymysql/cursors.py", line 282, in _query
    conn.query(q)
  File "$site/pymysql/connections.py", line 766, in query
    sql = sql.encode(self.encoding, 'surrogateescape')
UnicodeEncodeError: 'latin-1' codec can't encode character '\u2019' in position 95: ordinal not in range(256)
_______________ EnumSetTest_mysql_pymysql.test_unicode_roundtrip _______________
Traceback (most recent call last):
  File "<string>", line 2, in test_unicode_roundtrip
  File "$path/test/../lib/sqlalchemy/testing/util.py", line 195, in provide_metadata
    return fn(*args, **kw)
  File "$path/test/dialect/mysql/test_types.py", line 830, in test_unicode_roundtrip
    set_table.create()
  File "$path/test/../lib/sqlalchemy/sql/schema.py", line 725, in create
    checkfirst=checkfirst)
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 1843, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 1471, in _run_visitor
    **kwargs).traverse_single(element)
  File "$path/test/../lib/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "$path/test/../lib/sqlalchemy/sql/ddl.py", line 761, in visit_table
    include_foreign_key_constraints=include_foreign_key_constraints
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "$path/test/../lib/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 968, in _execute_ddl
    compiled
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 1335, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "$path/test/../lib/sqlalchemy/util/compat.py", line 182, in reraise
    raise value
  File "$path/test/../lib/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "$path/test/../lib/sqlalchemy/engine/default.py", line 442, in do_execute
    cursor.execute(statement, parameters)
  File "$site/pymysql/cursors.py", line 134, in execute
    result = self._query(query)
  File "$site/pymysql/cursors.py", line 282, in _query
    conn.query(q)
  File "$site/pymysql/connections.py", line 766, in query
    sql = sql.encode(self.encoding, 'surrogateescape')
UnicodeEncodeError: 'latin-1' codec can't encode character '\u2019' in position 87: ordinal not in range(256)
=========================== short test summary info ============================
FAIL test/dialect/mysql/test_types.py::EnumSetTest_mysql_pymysql::()::test_unicode_enum
FAIL test/dialect/mysql/test_types.py::EnumSetTest_mysql_pymysql::()::test_unicode_roundtrip
!!!!!!!!!!!!!!!!!!! Interrupted: stopping after 25 failures !!!!!!!!!!!!!!!!!!!!
========= 2 failed, 95 passed, 334 skipped, 23 error in 62.13 seconds ==========
```

[Sqlalchemy-tickets] Issue #3357: Connection.get_isolation_level() returns incorrect isolation level for autocommit on psycopg2 (zzzeek/sqlalchemy)

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

New issue 3357: Connection.get_isolation_level() returns incorrect isolation level for autocommit on psycopg2
https://bitbucket.org/zzzeek/sqlalchemy/issue/3357/connectionget_isolation_level-returns

Timothy Cardenas:

Simply create a engine with isolation_level set to autocommit, connect, and compare the return value from get_isolation_level from the value returned from the psycopg2 connection. They do not match.

Example:

```
#!python

url='postgresql+psycopg2://me:pass@host/db'
engine = create_engine(
            url,
            encoding='utf8',
            pool_size=5,
            poolclass=QueuePool,
            isolation_level="AUTOCOMMIT"
        )

with engine.connect() as connection:
  print(connection.get_isolation_level())  # => READ COMMITED
  print(connection.connection.isolation_level)  # => 0 (psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
```

Responsible: zzzeek