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

[Sqlalchemy-tickets] Issue #4276: add platform agnostic astext (and maybe asint) operators to JSON (zzzeek/sqlalchemy)

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

New issue 4276: add platform agnostic astext (and maybe asint) operators to JSON
https://bitbucket.org/zzzeek/sqlalchemy/issues/4276/add-platform-agnostic-astext-and-maybe

Michael Bayer:

see https://github.com/zzzeek/sqlalchemy/pull/443,  #3859, and https://groups.google.com/forum/#!topic/sqlalchemy/SohtCZ6zmDs which all refer to the need for a high quality means of pulling out the data of a JSON element as a plain textual value.

[Sqlalchemy-tickets] Issue #4275: CTE Compiles in Incorrect Order for Oracle Insert From Select (zzzeek/sqlalchemy)

[James F. <iss...@bi...>]

New issue 4275: CTE Compiles in Incorrect Order for Oracle Insert From Select
https://bitbucket.org/zzzeek/sqlalchemy/issues/4275/cte-compiles-in-incorrect-order-for-oracle

James Frick:

Oracle 12c

cx_Oracle==6.2.1

SQLalchemy==1.2.8

Description:
Oracle CTEs used in an insert().from_select() compile in the wrong order. Oracle expects the INSERT clause to render before the WITH clause.

Example modified from [here](https://stackoverflow.com/questions/45899044/sqlalchemy-with-clause-cte-with-insert-is-not-compiling-correctly-for-oracle).


```
#!python
from sqlalchemy import Table, Column, String, MetaData, select

engine = get_oracle_engine(...)
metadata = MetaData()

foo = Table('foo', metadata, Column('name', String(1)))
baz = Table('baz', metadata, Column('name', String(1)))

cte_1 = select([foo.c.name]).cte('first_cte')
cte_2 = select([cte_1.c.name]).select_from(cte_1).cte('second_cte')
ins = baz.insert().from_select([baz.c.name], cte_2)
metadata.drop_all(clarity.engine)
metadata.create_all(clarity.engine)
print(ins.compile(engine))
engine.execute(ins)
```

Out:
```
#!sql
WITH first_cte AS
(SELECT foo.name AS name
FROM foo)
 INSERT INTO baz (name) SELECT first_cte.name
FROM first_cte
```

Trace:
```
Traceback (most recent call last):
  File "*******\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "*******\lib\site-packages\sqlalchemy\engine\default.py", line 508, in do_execute
    cursor.execute(statement, parameters)
cx_Oracle.DatabaseError: ORA-00928: missing SELECT keyword

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

Traceback (most recent call last):
  File "minimum_example.py", line 28, in <module>
    engine.execute(ins)
  File "*******\lib\site-packages\sqlalchemy\engine\base.p
y", line 2075, in execute
    return connection.execute(statement, *multiparams, **params)
  File "*******\lib\site-packages\sqlalchemy\engine\base.p
y", line 948, in execute
    return meth(self, multiparams, params)
  File "*******\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "*******\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "*******\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context
    context)
  File "*******\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "*******\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "*******\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "*******\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "*******\lib\site-packages\sqlalchemy\engine\default.py", line 508, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00928: missing SELECT keyword [SQL: 'WITH first_cte AS \n(SELECT foo.name AS name \nFROM foo)\n INSERT INTO baz (name) SELECT first_cte.name \nFROM first_cte'] (Background on this error at: http://sqlalche.me/e/4xp6)
```

Following the examples in [test_compiler](https://bitbucket.org/zzzeek/sqlalchemy/src/master/test/dialect/oracle/test_compiler.py) I think the following test case would work (if I'm understanding assert_compile correctly).

```
#!python
t = table('foo', column('name'))
cte_1 = select([t.c.name]).cte('first_cte')
cte_2 = select([cte_1.c.name]).select_from(cte_1).cte('second_cte')
ins = t.insert().from_select([cte_2.c.name], cte_2)

self.assert_compile(ins,
                    'INSERT INTO foo (name) '
                    'WITH first_cte AS '
                    '(SELECT foo.name AS name '
                    'FROM foo) '
                    'SELECT first_cte.name '
                    'FROM first_cte ')
```

Posted to the mailing list [here](https://groups.google.com/forum/#!topic/sqlalchemy/_90piNnGGwg)

[Sqlalchemy-tickets] Issue #4274: join Query.from_statement (zzzeek/sqlalchemy)

[dima-starosud <iss...@bi...>]

New issue 4274: join Query.from_statement
https://bitbucket.org/zzzeek/sqlalchemy/issues/4274/join-queryfrom_statement

dima-starosud:

This is using SQLAlchemy=1.2.8.

I am trying to join two models by relationship, but I want to emulate values of second model using `from_statement`.

```
#!python
Base = declarative_base()


class X(Base):
    __tablename__ = 'x'
    id = Column(Integer, primary_key=True)


class Y(Base):
    __tablename__ = 'y'
    id = Column(Integer, primary_key=True)
    x_id = Column(Integer, ForeignKey(X.id))
    x = relationship(X)


Base.metadata.create_all(session.bind)

session.add(Y(x=X()))
session.flush()

Y1 = aliased(Y, name='y1')
X1 = Query(X).from_statement(select([label('id', literal(5))])).subquery(name='x1')
session.query(Y1).join(X1, Y.x).all()

```

Traceback:
```
Traceback (most recent call last):
  File "join_test.py", line 49, in <module>
    session.query(Y1).join(X1, Y.x).all()
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2090, in join
    from_joinpoint=from_joinpoint)
  File "<string>", line 2, in _join
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/base.py", line 201, in generate
    fn(self, *args[1:], **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2234, in _join
    outerjoin, full, create_aliases, prop)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2295, in _join_left_to_right
    prop, overlap)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2339, in _prepare_right_side
    right_mapper.mapped_table.description))
sqlalchemy.exc.InvalidRequestError: Selectable 'x1' is not derived from 'x'
```

The error message is pretty clear. But I would like to know whether it's possible to achieve what I wanted? Maybe I just misused the API?

Thanks in advance!

[Sqlalchemy-tickets] Issue #4273: UnboundLocalError in mssql/base.py:1925 (zzzeek/sqlalchemy)

[Umberto P. <iss...@bi...>]

New issue 4273: UnboundLocalError in mssql/base.py:1925
https://bitbucket.org/zzzeek/sqlalchemy/issues/4273/unboundlocalerror-in-mssql-basepy-1925

Umberto Prim:

In my call to `engine.raw_connection()` I have got this error:

```
#!python

File "D:\Anaconda\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1925, in get_isolation_level
    "tried views: %s; final error was: %s" % (views, err))
UnboundLocalError: local variable 'err' referenced before assignment
```
Which looks like an easy fix.

[Sqlalchemy-tickets] Issue #4272: MetaData.create_all() with a table that has a composite primary key and autoincrement (zzzeek/sqlalchemy)

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

New issue 4272: MetaData.create_all() with a table that has a composite primary key and autoincrement
https://bitbucket.org/zzzeek/sqlalchemy/issues/4272/metadatacreate_all-with-a-table-that-has-a

Rishi Sharma:

**Database**: MySQL 5.6

**SQLAlchemy**: 1.1

Using an example model definition as follows:
```
#!python

    class Page(base):
        __tablename__ = 'page'

        __table_args__ = (
            PrimaryKeyConstraint('book_id', 'page_id'),
        )

        book_id = Column(Integer())
        page_id = Column(Integer(), autoincrement=True)

        Index('idx_page_id', 'page_id', unique=True)
```

which emits the following SQL:


```
#!sql

CREATE TABLE page (
  book_id INTEGER NOT NULL,
  page_id INTEGER NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (book_id, page_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci

```

which results in the following error from MySQL:


```
#!text

OperationalError: (_mysql_exceptions.OperationalError) (1075, 'Incorrect table definition; there can be only one auto column and it must be defined as a key')
```

because the emitted SQL does not include the index "idx_page_id". Looking at SQLAlchemy's source, indexes are created in a separate DDL after the CREATE.

I wanted to get your thoughts on this, and was curious if you know of a workaround when the primary key is composite with the second column being autoincrement.

[Sqlalchemy-tickets] Issue #4271: support empty list with expanding IN (zzzeek/sqlalchemy)

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

New issue 4271: support empty list with expanding IN
https://bitbucket.org/zzzeek/sqlalchemy/issues/4271/support-empty-list-with-expanding-in

Michael Bayer:

e.g.

```
#!python


    def test_empty_set(self):
        table = self.tables.some_table

        stmt = select([table.c.id]).where(
            table.c.x.in_(bindparam('q', expanding=True))).order_by(table.c.id)

        self._assert_result(
            stmt,
            [],
            params={"q": []},
        )

```

right now this doesn't work due to:


```
#!python

raise exc.InvalidRequestError(
                        "'expanding' parameters can't be used with an "
                        "empty list"
                    )

```

e.g. we are still outputting bound parameter markers.    per the discussion at https://groups.google.com/forum/#!topic/sqlalchemy/NLJCu_vqK4g we can inject SQL expressions that essentially represent empty sets - for all dialects where we can get consistent behavior we can turn this on.   if for example Oracle just won't behave the same way we can mark it as not supported, but for the sqlite/mysql/pg case this should be feasible.

ongoing work at https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/760/.

[Sqlalchemy-tickets] Issue #4270: cache key calc for loader options w/ reuse has performance issue (zzzeek/sqlalchemy)

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

New issue 4270: cache key calc for loader options w/ reuse has performance issue
https://bitbucket.org/zzzeek/sqlalchemy/issues/4270/cache-key-calc-for-loader-options-w-reuse

Michael Bayer:

copied from the mailing list

We have just upgraded to sqlalchemy 1.2.7 (from 1.1.14), and had a performance issue with a query that uses a lot of joinedloads that was caused by the automatic baking of all relationship queries that was introduced in 1.2.

Say we have a set of tables with relationships Book.pages, Page.font and Font.layout. We have a query of this form:


```
#!python

pages = joinedload(Book.pages)
option1 = pages.joinedload(Page.font)
option2 = pages.joinedload(Page.layout)

query = session().query(Book).options(option1, option2)

```

The important point here is that the pages object defined on line 1 is reused in both option1 and option2. Now suppose we fetch another relationship that wasn't joined-loaded on the returned instances. This will case another query as it is not loaded already, and this query will be baked due to the change in 1.2 to bake all relationship loads.

We found that the construction of the cache key for baking this query becomes very slow as the number of options of this form increases, and is in fact quadratic in the number of such options (we have ~25 such options in our problematic query). This is due to each option containing all of the joinedloads inside its _to_bind attribute, and _UnboundLoad._generate_cache_key has to process everything in the _to_bind list. E.g. in this example:


```
#!python

print([[str(i) for i in load.path] for load in option1._to_bind])
print([[str(i) for i in load.path] for load in option2._to_bind])

[['Book.pages'], ['Book.pages', 'Page.font'], ['Book.pages', 'Page.layout']]

[['Book.pages'], ['Book.pages', 'Page.font'], ['Book.pages', 'Page.layout']]

```

Therefore, when generating the key for each option we are processing the joinedloads from all of the options, leading to the quadratic performance degradation.

We fixed it by avoiding reusing the joinedload for Book.pages by doing this:


```
#!python

option1 = joinedload(Book.pages).joinedload(Page.font)
option2 = joinedload(Book.pages).joinedload(Page.layout)

```

The resulting query is unchanged, but the cache key function is now just linear in the number of joinedloads as each option has only its relationships in its _to_bind attribute. In our case, this completely solved the performance issue.

[Sqlalchemy-tickets] Issue #4269: orm.Query() constructor raising "Boolean value of this clause is not defined" exception (zzzeek/sqlalchemy)

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

New issue 4269: orm.Query() constructor raising "Boolean value of this clause is not defined" exception
https://bitbucket.org/zzzeek/sqlalchemy/issues/4269/ormquery-constructor-raising-boolean-value

Patrick Rusk:

(This problem was noted in comments in https://github.com/zzzeek/sqlalchemy/commit/3fa38a1a2313b4644daa431d629394d6bb14497a but not followed up on.)

The `sqlalchemy.orm.Query()` class takes an `entities` parameter defined to be "a sequence of entities and/or SQL expressions", but it used to actual accept a single SQL expression as well. Now, some such expressions cause a "TypeError: Boolean value of this clause is not defined" exception to be raised.

The offending line is https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/orm/query.py#L150

As noted in the comments, changing it to `if entities is not None:` would probably fix the issue.

Test case:

```
#!python

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

class User(declarative_base()):
    __tablename__ = 'users'
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)

select = sqlalchemy.sql.func.max(User.id).label('max')
query = sqlalchemy.orm.Query(select)
```
...works under 1.2.7, but generates this stack trace under 1.2.8...

```
#!python

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-1-cf50a8343d19> in <module>()
      8 
      9 select = sqlalchemy.sql.func.max(User.id).label('max')
---> 10 query = sqlalchemy.orm.Query(select)

/Users/patrick/.virtualenvs/shackleton/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in __init__(self, entities, session)
    140         self.session = session
    141         self._polymorphic_adapters = {}
--> 142         self._set_entities(entities)
    143 
    144     def _set_entities(self, entities, entity_wrapper=None):

/Users/patrick/.virtualenvs/shackleton/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in _set_entities(self, entities, entity_wrapper)
    148         self._primary_entity = None
    149         self._has_mapper_entities = False
--> 150         if entities:
    151             for ent in util.to_list(entities):
    152                 entity_wrapper(self, ent)

/Users/patrick/.virtualenvs/shackleton/lib/python2.7/site-packages/sqlalchemy/sql/elements.pyc in __bool__(self)
    485 
    486     def __bool__(self):
--> 487         raise TypeError("Boolean value of this clause is not defined")
    488 
    489     __nonzero__ = __bool__

TypeError: Boolean value of this clause is not defined
```
Anyone experiencing this can easily fix their issue by throwing [] around their expression, but it is a breaking change (of an undocumented feature).

(Please forgive the nonsensical test case above. I've never used SQLAlchemy. Just diagnosing a bug in someone else's code.)

[Sqlalchemy-tickets] Issue #4268: AssociationProxy becomes useless when parent goes out of scope (zzzeek/sqlalchemy)

[Chris W. <iss...@bi...>]

New issue 4268: AssociationProxy becomes useless when parent goes out of scope
https://bitbucket.org/zzzeek/sqlalchemy/issues/4268/associationproxy-becomes-useless-when

Chris Wilson:

This fails:

```
#!python
from sqlalchemy import Column, ForeignKey, Integer, Table, Text, create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker

Base = declarative_base()

userkeywords_table = Table('userkeywords', Base.metadata,
    Column('user_id', Integer, ForeignKey("user.id"),
           primary_key=True),
    Column('keyword_id', Integer, ForeignKey("keyword.id"),
           primary_key=True)
)

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(Text)
    kw = relationship("Keyword", secondary=lambda: userkeywords_table)

    def __init__(self, name):
        self.name = name

    # proxy the 'keyword' attribute from the 'kw' relationship
    keywords = association_proxy('kw', 'keyword')
    
class Keyword(Base):
    __tablename__ = 'keyword'
    id = Column(Integer, primary_key=True)
    keyword = Column('keyword', Text)

    def __init__(self, keyword):
        self.keyword = keyword

engine = create_engine('sqlite://')
engine.echo = True
Base.metadata.create_all(engine)

DBSession = sessionmaker(bind=engine)
session = DBSession(autocommit=True)

with session.begin():
    user = User('jek')
    user.keywords.append('cheese inspector')
    session.add(user)
    user = None
    
with session.begin():
    print(session.query(User).one().keywords)
```

Because User is garbage-collected before the association can be read:

```
File untitled0.py, line 60, in : print(session.query(User).one().keywords) 
File ...\sqlalchemy-1.2.7-py3.6-win-amd64.egg\sqlalchemy\ext\associationproxy.py, line 780, in __repr__ : return repr(list(self)) 
File ...\sqlalchemy-1.2.7-py3.6-win-amd64.egg\sqlalchemy\ext\associationproxy.py, line 583, in __len__ : return len(self.col) 
File ...\sqlalchemy-1.2.7-py3.6-win-amd64.egg\sqlalchemy\ext\associationproxy.py, line 580, in : col = property(lambda self: self.lazy_collection()) 
File ...\sqlalchemy-1.2.7-py3.6-win-amd64.egg\sqlalchemy\ext\associationproxy.py, line 536, in __call__ : "stale association proxy, parent object has gone out of " 
sqlalchemy.exc.InvalidRequestError: stale association proxy, parent object has gone out of scope
```

Whereas if you forcibly keep a reference in a variable, then it works:

```
#!python
user = session.query(User).one()
print(user.keywords)
```

Other people have run into this issue as well:

* https://stackoverflow.com/questions/30044069/stale-association-proxy-parent-object-has-gone-out-of-scope-with-flask-sqlalc
* https://groups.google.com/forum/#!topic/sqlalchemy/b-ams8tgcDU

The latter has some explanation as to why we only keep a weakref:

> As a proxy, it doesn't actually store the collection that 
> it proxies, because that collection itself might not have been loaded 
> yet, or can be expired, replaced, etc. and it's just less complicated 
> for it to look at whatever collection is currently on the parent. 
> 
> If the parent object is lost, and you still are pointing to the proxied 
> collection, it can't give you the collection anymore.   The association 
> proxy stores only a weak reference to the parent... I would guess that's 
> just to prevent reference cycles; I tried adding a strong ref and of 
> course all the tests still pass, so I'm not totally sure why that was so 
> important here, but there you go. 

Preventing reference cycles doesn't seem like a good reason to have such counter-intuitive behaviour. Also we may not need to hold a strong reference to the underlying *association*. A strong reference to the *parent* of that association should be enough to prevent it from going out of scope, keeping the association object accessible/constructable.

[Sqlalchemy-tickets] Issue #4267: explain example is not working w/ crud (zzzeek/sqlalchemy)

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

New issue 4267: explain example is not working w/ crud
https://bitbucket.org/zzzeek/sqlalchemy/issues/4267/explain-example-is-not-working-w-crud

Michael Bayer:

I'm not following exactly how this happens:

given https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Explain

```
#!python


query = delete(MyMapper.__table__).where(MyMapper.my_column == value)
result = session.execute(explain(query)) # exception occurs
```

is said by user to produce:
```
#!python



SubTest error: Traceback (most recent call last):
  File "/usr/lib/python3.6/unittest/case.py", line 59, in testPartExecutor
    yield
  File "/usr/lib/python3.6/unittest/case.py", line 523, in subTest
    yield
  File "/home/charles/PycharmProjects/my_project/models.py", line 39, in test_delete_cascade
    result = self.session.execute(explain_query).fetchall()
  File "/home/charles/PycharmProjects/my_project/.env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1170, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/home/charles/PycharmProjects/my_project/.env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/home/charles/PycharmProjects/my_project/.env/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/charles/PycharmProjects/my_project/.env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    if not self.schema_for_object.is_default else None)
  File "<string>", line 1, in <lambda>
  File "/home/charles/PycharmProjects/my_project/.env/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 442, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/home/charles/PycharmProjects/my_project/.env/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 448, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/home/charles/PycharmProjects/my_project/.env/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 457, in __init__
    ) and statement._returning:
AttributeError: 'explain' object has no attribute '_returning'
```

i'm not following how .isinsert/.isdelete/.isupdate is getting set in the first place.   also if these affect the compilation they should be taken into account by the recipe (can add recipe to the tests).

[Sqlalchemy-tickets] Issue #4266: automap.prepare() creates classes that are referenced but not mapped, causes errors under concurrency (zzzeek/sqlalchemy)

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

New issue 4266: automap.prepare() creates classes that are referenced but not mapped, causes errors under concurrency
https://bitbucket.org/zzzeek/sqlalchemy/issues/4266/automapprepare-creates-classes-that-are

Michael Bayer:

demo:


```
#!python

from sqlalchemy import *
from sqlalchemy.orm import configure_mappers
from sqlalchemy.ext.automap import automap_base
import time
import random
import threading


def make_tables(e):
    m = MetaData()
    for i in range(15):
        Table(
            'table_%d' % i,
            m,
            Column('id', Integer, primary_key=True),
            Column('data', String(50)),
            Column(
                't_%d_id' % (i - 1),
                ForeignKey('table_%d.id' % (i - 1))
            ) if i > 4 else None
        )
    m.drop_all(e)
    m.create_all(e)


def automap(e):
    id_ = threading.get_ident()
    Base = automap_base()

    Base.prepare(e, reflect=True)
    print("thread %s prepared!" % id_)

    time.sleep(.01)
    configure_mappers()
    print("thread %s we're good!" % id_)


def chaos():
    while True:
        automap(e)
        time.sleep(random.random())

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

make_tables(e)

threads = [threading.Thread(target=chaos) for i in range(30)]
for t in threads:
    t.start()

for t in threads:
    t.join()

```

output:

```
#!python


thread 139667543086848 prepared!
thread 139667526301440 prepared!
thread 139667559872256 prepared!
Exception in thread Thread-5:
Traceback (most recent call last):
  File "/usr/lib64/python3.6/threading.py", line 916, in _bootstrap_inner
    self.run()
  File "/usr/lib64/python3.6/threading.py", line 864, in run
    self._target(*self._args, **self._kwargs)
  File "test.py", line 40, in chaos
    automap(e)
  File "test.py", line 34, in automap
    configure_mappers()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line 3026, in configure_mappers
    raise e
sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'Mapper|table_14|table_14'. Original exception was: Class 'sqlalchemy.ext.automap.table_13' is not mapped

Exception in thread Thread-3:
Traceback (most recent call last):
  File "/usr/lib64/python3.6/threading.py", line 916, in _bootstrap_inner
    self.run()
  File "/usr/lib64/python3.6/threading.py", line 864, in run
    self._target(*self._args, **self._kwargs)
  File "test.py", line 40, in chaos
    automap(e)
  File "test.py", line 34, in automap
    configure_mappers()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line 3029, in configure_mappers
    mapper._post_configure_properties()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line 1828, in _post_configure_properties
    prop.init()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/interfaces.py", line 184, in init
    self.do_init()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/relationships.py", line 1655, in do_init
    self._process_dependent_arguments()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/relationships.py", line 1712, in _process_dependent_arguments
    self.target = self.mapper.mapped_table
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/util/langhelpers.py", line 767, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/relationships.py", line 1634, in mapper
    configure=False)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/base.py", line 426, in class_mapper
    raise exc.UnmappedClassError(class_)
sqlalchemy.orm.exc.UnmappedClassError: Class 'sqlalchemy.ext.automap.table_13' is not mapped

... continues until all threads are done

```

this can be patched as:


```
#!diff

diff --git a/lib/sqlalchemy/ext/automap.py b/lib/sqlalchemy/ext/automap.py
index 30f48fcea..b2fdbf802 100644
--- a/lib/sqlalchemy/ext/automap.py
+++ b/lib/sqlalchemy/ext/automap.py
@@ -518,6 +518,7 @@ from .declarative.base import _DeferredMapperConfig
 from ..sql import and_
 from ..schema import ForeignKeyConstraint
 from ..orm import relationship, backref, interfaces
+from ..orm.mapper import _CONFIGURE_MUTEX
 from .. import util
 
 
@@ -754,49 +755,53 @@ class AutomapBase(object):
                 autoload_replace=False
             )
 
-        table_to_map_config = dict(
-            (m.local_table, m)
-            for m in _DeferredMapperConfig.
-            classes_for_base(cls, sort=False)
-        )
-
-        many_to_many = []
+        _CONFIGURE_MUTEX.acquire()
+        try:
+            table_to_map_config = dict(
+                (m.local_table, m)
+                for m in _DeferredMapperConfig.
+                classes_for_base(cls, sort=False)
+            )
 
-        for table in cls.metadata.tables.values():
-            lcl_m2m, rem_m2m, m2m_const = _is_many_to_many(cls, table)
-            if lcl_m2m is not None:
-                many_to_many.append((lcl_m2m, rem_m2m, m2m_const, table))
-            elif not table.primary_key:
-                continue
-            elif table not in table_to_map_config:
-                mapped_cls = type(
-                    classname_for_table(cls, table.name, table),
-                    (cls, ),
-                    {"__table__": table}
-                )
-                map_config = _DeferredMapperConfig.config_for_cls(mapped_cls)
-                cls.classes[map_config.cls.__name__] = mapped_cls
-                table_to_map_config[table] = map_config
-
-        for map_config in table_to_map_config.values():
-            _relationships_for_fks(cls,
-                                   map_config,
-                                   table_to_map_config,
-                                   collection_class,
-                                   name_for_scalar_relationship,
-                                   name_for_collection_relationship,
-                                   generate_relationship)
-
-        for lcl_m2m, rem_m2m, m2m_const, table in many_to_many:
-            _m2m_relationship(cls, lcl_m2m, rem_m2m, m2m_const, table,
-                              table_to_map_config,
-                              collection_class,
-                              name_for_scalar_relationship,
-                              name_for_collection_relationship,
-                              generate_relationship)
-
-        for map_config in _DeferredMapperConfig.classes_for_base(cls):
-            map_config.map()
+            many_to_many = []
+
+            for table in cls.metadata.tables.values():
+                lcl_m2m, rem_m2m, m2m_const = _is_many_to_many(cls, table)
+                if lcl_m2m is not None:
+                    many_to_many.append((lcl_m2m, rem_m2m, m2m_const, table))
+                elif not table.primary_key:
+                    continue
+                elif table not in table_to_map_config:
+                    mapped_cls = type(
+                        classname_for_table(cls, table.name, table),
+                        (cls, ),
+                        {"__table__": table}
+                    )
+                    map_config = _DeferredMapperConfig.config_for_cls(mapped_cls)
+                    cls.classes[map_config.cls.__name__] = mapped_cls
+                    table_to_map_config[table] = map_config
+
+            for map_config in table_to_map_config.values():
+                _relationships_for_fks(cls,
+                                       map_config,
+                                       table_to_map_config,
+                                       collection_class,
+                                       name_for_scalar_relationship,
+                                       name_for_collection_relationship,
+                                       generate_relationship)
+
+            for lcl_m2m, rem_m2m, m2m_const, table in many_to_many:
+                _m2m_relationship(cls, lcl_m2m, rem_m2m, m2m_const, table,
+                                  table_to_map_config,
+                                  collection_class,
+                                  name_for_scalar_relationship,
+                                  name_for_collection_relationship,
+                                  generate_relationship)
+
+            for map_config in _DeferredMapperConfig.classes_for_base(cls):
+                map_config.map()
+        finally:
+            _CONFIGURE_MUTEX.release()
 
     _sa_decl_prepare = True
     """Indicate that the mapping of classes should be deferred.

```

[Sqlalchemy-tickets] Issue #4265: support oracle floating point binary precision (zzzeek/sqlalchemy)

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

New issue 4265: support oracle floating point binary precision
https://bitbucket.org/zzzeek/sqlalchemy/issues/4265/support-oracle-floating-point-binary

Michael Bayer:

the Float type supports "precision", however this is taken to mean decimal precision.  Oracle's FLOAT supports a binary precision argument which is totally a different thing: http://www.exploringbinary.com/decimal-precision-of-binary-floating-point-numbers/.       We'd like to support oracle.FLOAT(binary_precision=X) and potentially support conversion of Float(precision) to binary precision, if possible.


```
#!diff


@@ -585,6 +566,9 @@ class OracleTypeCompiler(compiler.GenericTypeCompiler):
     def visit_BINARY_FLOAT(self, type_, **kw):
         return self._generate_numeric(type_, "BINARY_FLOAT", **kw)
 
+    def visit_FLOAT(self, type_, **kw):
+        return self._generate_numeric(type_, "FLOAT", **kw)
+
     def visit_NUMBER(self, type_, **kw):
         return self._generate_numeric(type_, "NUMBER", **kw)
 
@@ -1418,6 +1402,11 @@ class OracleDialect(default.DefaultDialect):
                     coltype = INTEGER()
                 else:
                     coltype = NUMBER(precision, scale)
+            elif coltype == 'FLOAT':
+                if precision is None:
+                    coltype = FLOAT()
+                else:
+                    coltype = FLOAT(precision)
             elif coltype in ('VARCHAR2', 'NVARCHAR2', 'CHAR'):
                 coltype = self.ischema_names.get(coltype)(length)
             elif 'WITH TIME ZONE' in coltype:
```

[Sqlalchemy-tickets] Issue #4264: Passing NaN to Float column in Oracle leads to crash (zzzeek/sqlalchemy)

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

New issue 4264: Passing NaN to Float column in Oracle leads to crash
https://bitbucket.org/zzzeek/sqlalchemy/issues/4264/passing-nan-to-float-column-in-oracle

Leonid Butenko:

Hi,

I think it might be somehow related to the #4259, but with floats now.

I use Oracle 11g, cx_oracle 6.3, oracle_instantclient 11.2 and sqlalchemy 1.2.7

here is my test program
```
import sqlalchemy as sa
import sqlalchemy.orm
import datetime
import sqlalchemy.ext.declarative

connection_string = "oracle://scott:tiger@oracle1120/"

engine = sa.create_engine(connection_string, echo = True, max_overflow = 0, pool_size = 3)
_session = sa.orm.sessionmaker()
_session.configure(bind=engine)
session = _session()

table = sa.Table(
    "nan_test", sa.MetaData(),
    sa.Column("id", sa.Integer, primary_key=True),
    sa.Column("fval", sa.Float)
)
table.drop(engine)
table.create(engine, checkfirst=True)

Base = sa.ext.declarative.declarative_base()

class Pod(Base):
    __table__ = sa.Table(
        "nan_test", Base.metadata, autoload = True, autoload_with = engine)

session.add(Pod(id=1, fval=float('nan')))
session.commit()
```

Here is the error I get
```
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) DPI-1055: value 
is not a number (NaN) and cannot be used in Oracle numbers [SQL: 
u'INSERT INTO nan_test (id, fval) VALUES (:id, :fval)'] [parameters: 
{'id': 1, 'fval': nan}] (Background on this error 
at: http://sqlalche.me/e/4xp6)
```

Here is the way how to reproduce it in anaconda docker:
```
>docker run -it --rm continuumio/miniconda bash
>conda install -c conda-forge -c anaconda  sqlalchemy cx_oracle=6.0.3 oracle-instantclient libaio

The following NEW packages will be INSTALLED:

    cx_oracle:            6.0.3-py27hc82e58e_0  anaconda
    libaio:               0.3.110-h14c3975_0    anaconda
    oracle-instantclient: 11.2.0.4.0-0          anaconda
    sqlalchemy:           1.2.7-py27h65ede16_0  conda-forge

>python test_nan.py

2018-05-22 20:24:56,975 INFO sqlalchemy.engine.base.Engine SELECT USER FROM DUAL
2018-05-22 20:24:56,975 INFO sqlalchemy.engine.base.Engine {}
2018-05-22 20:24:56,977 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
2018-05-22 20:24:56,978 INFO sqlalchemy.engine.base.Engine {}
2018-05-22 20:24:56,979 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
2018-05-22 20:24:56,979 INFO sqlalchemy.engine.base.Engine {}
2018-05-22 20:24:56,981 INFO sqlalchemy.engine.base.Engine select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'
2018-05-22 20:24:56,982 INFO sqlalchemy.engine.base.Engine {}
2018-05-22 20:24:56,984 INFO sqlalchemy.engine.base.Engine
DROP TABLE nan_test
2018-05-22 20:24:56,984 INFO sqlalchemy.engine.base.Engine {}
2018-05-22 20:24:57,031 INFO sqlalchemy.engine.base.Engine COMMIT
2018-05-22 20:24:57,033 INFO sqlalchemy.engine.base.Engine SELECT table_name FROM all_tables WHERE table_name = :name AND owner = :schema_name
2018-05-22 20:24:57,033 INFO sqlalchemy.engine.base.Engine {'name': u'NAN_TEST', 'schema_name': u'Test'}
2018-05-22 20:24:57,039 INFO sqlalchemy.engine.base.Engine
CREATE TABLE nan_test (
        id INTEGER NOT NULL,
        fval FLOAT,
        PRIMARY KEY (id)
)


2018-05-22 20:24:57,039 INFO sqlalchemy.engine.base.Engine {}
2018-05-22 20:24:57,055 INFO sqlalchemy.engine.base.Engine COMMIT
2018-05-22 20:24:57,057 INFO sqlalchemy.engine.base.Engine SELECT table_name, compression, compress_for FROM ALL_TABLES WHERE table_name = :table_name AND owner = :owner
2018-05-22 20:24:57,057 INFO sqlalchemy.engine.base.Engine {'owner': u'Test', 'table_name': u'NAN_TEST'}
2018-05-22 20:24:57,064 INFO sqlalchemy.engine.base.Engine
            SELECT col.column_name, col.data_type, col.char_length,
              col.data_precision, col.data_scale, col.nullable,
              col.data_default, com.comments            FROM all_tab_columns col
            LEFT JOIN all_col_comments com
            ON col.table_name = com.table_name
            AND col.column_name = com.column_name
            AND col.owner = com.owner
            WHERE col.table_name = :table_name
         AND col.owner = :owner  ORDER BY col.column_id
2018-05-22 20:24:57,064 INFO sqlalchemy.engine.base.Engine {'owner': u'Test', 'table_name': u'NAN_TEST'}
2018-05-22 20:24:57,069 INFO sqlalchemy.engine.base.Engine SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos,
ac.search_condition,
ac.delete_rule
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P', 'U', 'C')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
AND (rem.position IS NULL or loc.position=rem.position)
ORDER BY ac.constraint_name, loc.position
2018-05-22 20:24:57,069 INFO sqlalchemy.engine.base.Engine {'owner': u'Test', 'table_name': u'NAN_TEST'}
2018-05-22 20:24:57,102 INFO sqlalchemy.engine.base.Engine SELECT a.index_name, a.column_name,
b.index_type, b.uniqueness, b.compression, b.prefix_length
FROM ALL_IND_COLUMNS a,
ALL_INDEXES b
WHERE
a.index_name = b.index_name
AND a.table_owner = b.table_owner
AND a.table_name = b.table_name
AND a.table_name = :table_name AND a.table_owner = :schema ORDER BY a.index_name, a.column_position
2018-05-22 20:24:57,102 INFO sqlalchemy.engine.base.Engine {'table_name': u'NAN_TEST', 'schema': u'Test'}
2018-05-22 20:24:57,105 INFO sqlalchemy.engine.base.Engine SELECT a.index_name, a.column_name,
b.index_type, b.uniqueness, b.compression, b.prefix_length
FROM ALL_IND_COLUMNS a,
ALL_INDEXES b
WHERE
a.index_name = b.index_name
AND a.table_owner = b.table_owner
AND a.table_name = b.table_name
AND a.table_name = :table_name AND a.table_owner = :schema ORDER BY a.index_name, a.column_position
2018-05-22 20:24:57,105 INFO sqlalchemy.engine.base.Engine {'table_name': u'NAN_TEST', 'schema': u'Test'}
2018-05-22 20:24:57,106 INFO sqlalchemy.engine.base.Engine SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos,
ac.search_condition,
ac.delete_rule
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P', 'U', 'C')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
AND (rem.position IS NULL or loc.position=rem.position)
ORDER BY ac.constraint_name, loc.position
2018-05-22 20:24:57,106 INFO sqlalchemy.engine.base.Engine {'owner': u'Test', 'table_name': u'NAN_TEST'}
2018-05-22 20:24:57,136 INFO sqlalchemy.engine.base.Engine
            SELECT comments
            FROM user_tab_comments
            WHERE table_name = :table_name

2018-05-22 20:24:57,137 INFO sqlalchemy.engine.base.Engine {'table_name': u'NAN_TEST'}
2018-05-22 20:24:57,140 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-05-22 20:24:57,141 INFO sqlalchemy.engine.base.Engine INSERT INTO nan_test (id, fval) VALUES (:id, :fval)
2018-05-22 20:24:57,141 INFO sqlalchemy.engine.base.Engine {'id': 1, 'fval': nan}
2018-05-22 20:24:57,142 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "test_nan.py", line 29, in <module>
    session.commit()
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 943, in commit
    self.transaction.commit()
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 467, in commit
    self._prepare_impl()
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 447, in _prepare_impl
    self.session.flush()
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2254, in flush
    self._flush(objects)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2380, in _flush
    transaction.rollback(_capture_exception=True)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2344, in _flush
    flush_context.execute()
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 391, in execute
    rec.execute(self)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 556, in execute
    uow
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
    mapper, table, insert)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 830, in _emit_insert_statements
    execute(statement, multiparams)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/opt/conda/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) DPI-1055: value is not a number (NaN) and cannot be used in Oracle numbers [SQL: u'INSERT INTO nan_test (id, fval) VALUES (:id, :fval)'] [parameters: {'id': 1, 'fval': nan}] (Background on this error at: http://sqlalche.me/e/4xp6)

```

I'm not sure whether it is the correct place to report about this problem. It might be cx_oracle related issue. From my point of view, passing NaNs to floating columns should be a normal practice at least in the scientific world, so I thought sqlalchemy might handle this situation as well. Pls advise! 

The solution I found so far is to downgrade the version of cx_oracle to 6.0.1 or ealier.
```
>  conda install cx_oracle=6.0b2

The following packages will be DOWNGRADED:

    cx_oracle: 6.0.3-py27hc82e58e_0 --> 6.0b2-py27_0
> python test.py
....
2018-05-22 20:53:13,774 INFO sqlalchemy.engine.base.Engine {'table_name': u'NAN_TEST'}
2018-05-22 20:53:13,780 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-05-22 20:53:13,782 INFO sqlalchemy.engine.base.Engine INSERT INTO nan_test (id, fval) VALUES (:id, :fval)
2018-05-22 20:53:13,782 INFO sqlalchemy.engine.base.Engine {'id': 1, 'fval': nan}
2018-05-22 20:53:13,835 INFO sqlalchemy.engine.base.Engine COMMIT
```
So the problem should appear somewhere between version 6.0.1 and 6.0.3 of cx_Oracle and still exist in the most recent version 6.3.

[Sqlalchemy-tickets] Issue #4263: MapperEvents before_update, after_update do not work (zzzeek/sqlalchemy)

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

New issue 4263: MapperEvents before_update, after_update do not work
https://bitbucket.org/zzzeek/sqlalchemy/issues/4263/mapperevents-before_update-after_update-do

Karol Narowski:

Function is not called when set in listener. **before_insert** and **after_insert** is working well but **before_update** and **after_update** are not called.
Ex.

```
#!python
class User(Base):
    __tablename__ = 'users'

    uid = Column(String(36), primary_key=True, unique=True, default=generate_uuid)
    name = Column(String(15), nullable=False)
    description = Column(String(25), nullable=False)
    phone = Column(String(20), nullable=False)
    hash = Column(String(128), nullable=True, unique=True)
    created_on = Column(DateTime(), default=datetime.utcnow)
    updated_on = Column(DateTime(), default=datetime.utcnow, onupdate=datetime.utcnow)
@listens_for(User, 'before_insert')
def before_insert_function(mapper, connection, target):
    print(target.phone)
    target.hash = "OtherInsert"


@listens_for(User, 'before_update')
def before_update_function(mapper, connection, target):
    print(target.uid)
    print(target.phone)
    target.hash = "OtherUpdate"

```

[Sqlalchemy-tickets] Issue #4262: 3rd party dialect Type not resolved for compile (zzzeek/sqlalchemy)

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

New issue 4262: 3rd party dialect Type not resolved for compile
https://bitbucket.org/zzzeek/sqlalchemy/issues/4262/3rd-party-dialect-type-not-resolved-for

Patrick Buxton:

A type declared in a 3rd party dialect does not get resolved when you ask it to compile and results in a:
UnsupportedCompilationError: Compiler <sqlalchemy.sql.compiler.GenericTypeCompiler object at 0x0000000005C48CC0> can't render element of type <class 'sqlalchemy_hana.types.TINYINT'>

```
#!python

from sqlalchemy import Table, MetaData, Column
from sqlalchemy_hana.types import TINYINT
meta = MetaData()
t = Table('test_table', meta, Column('Col1', TINYINT))
str(t.columns['Col1'].type)
```
The problem stems from type_api _default_dialect where it's happy to lookup within the included dialects. Given that the 3rd party dialects are supposed to follow specific naming conventions, would it be possible to provide a lookup on that basis too?


```
#!python

    def _default_dialect(self, default):
        if self.__class__.__module__.startswith("sqlalchemy.dialects"):
            tokens = self.__class__.__module__.split(".")[0:3]
            mod = ".".join(tokens)
            return getattr(__import__(mod).dialects, tokens[-1]).dialect()
        else:
            return default.DefaultDialect()
```

[Sqlalchemy-tickets] Issue #4261: TypeError while using sqlalchemy.Enum with enum.Enum (zzzeek/sqlalchemy)

[Piotr P. <iss...@bi...>]

New issue 4261: TypeError while using sqlalchemy.Enum with enum.Enum
https://bitbucket.org/zzzeek/sqlalchemy/issues/4261/typeerror-while-using-sqlalchemyenum-with

Piotr Pokorski:

Hi!

I'm working on a database, where some of the columns need to have an enumeration/dictionary attribute, so I decided to use a sqlalchemy.Enum type like described in the [doc](http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.Enum). Here is an example showing the problem:

```
#!python

import enum
import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Sizes(enum.Enum):
    SMALL = 0
    MEDIUM = 1
    LARGE = 2

class Test(Base):
    __tablename__ = 'Test'
    size = db.Column(db.Enum(Sizes), nullable=False)
 
engine = db.create_engine('sqlite:///test.db')
Base.metadata.create_all(engine)
```

which throws:

```
#!python

Traceback (most recent call last):
  File "C:/Users/User/PycharmProjects/project/test.py", line 15, in <module>
    class Test(Base):
  File "C:/Users/User/PycharmProjects/project/test.py", line 18, in Test
    size = db.Column(db.Enum(Sizes), nullable=False)
  File "C:\Python27\lib\site-packages\sqlalchemy\sql\sqltypes.py", line 1296, in __init__
    self._enum_init(enums, kw)
  File "C:\Python27\lib\site-packages\sqlalchemy\sql\sqltypes.py", line 1332, in _enum_init
    length = max(len(x) for x in self.enums)
  File "C:\Python27\lib\site-packages\sqlalchemy\sql\sqltypes.py", line 1332, in <genexpr>
    length = max(len(x) for x in self.enums)
TypeError: object of type 'type' has no len()
```
Setup information:

```
#!python
Python 2.7.15 (v2.7.15:ca079a3ea3, Apr 30 2018, 16:30:26) [MSC v.1500 64 bit (AMD64)] on win32
sqlalchemy.__version__
'1.2.7'

```

Thank you for your help!

[Sqlalchemy-tickets] Issue #4260: PGDialect should support native decimal (zzzeek/sqlalchemy)

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

New issue 4260: PGDialect should support native decimal
https://bitbucket.org/zzzeek/sqlalchemy/issues/4260/pgdialect-should-support-native-decimal

pachewise:

supports_native_decimal is set to False by default, but postgres (and dialects derived from it, like Vertica) has support for Decimal/Numeric. I submitted a PR for the particular dialect we use at work (https://github.com/LocusEnergy/sqlalchemy-vertica-python/pull/18), but thought it made sense to propose the change here, too.

Test case: 
```
>> import decimal; decimal.Decimal(float(-6054546742273406781))
Decimal('-6054546742273406976')
```
(0) Have a table with a Decimal(19, 0) or Numeric(19, 0) column (or any precision > 16)
(1) Add a value like -6054546742273406781, which uses all the sig figs
(2) Do a select on that table; you'll see an SAWarning regarding decimal -> float conversion, and precision loss when it comes back as Decimal (-6054546742273406976)

using Ubuntu 14.04.5, Python 2.7.12

[Sqlalchemy-tickets] Issue #4259: version 1.2 breaks correct Boolean handling on Oracle 11g (zzzeek/sqlalchemy)

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

New issue 4259: version 1.2 breaks correct Boolean handling on Oracle 11g
https://bitbucket.org/zzzeek/sqlalchemy/issues/4259/version-12-breaks-correct-boolean-handling

Leonid Butenko:

Since I have upgraded from sqlalchemy 1.1.13 to 1.2.7 my ingestion scripts have stopped working. I get the following error:


```
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-03115: unsupported network datatype or representation [SQL: u'UPDATE pod SET converge=:converge WHERE pod.sat = :pod_sat AND pod.stime_utc = :pod_stime_utc AND pod.etime_utc = :pod_etime_utc'] [parameters: {'pod_etime_utc': datetime.datetime(2018, 5, 14, 18, 10, 42), 'converge': False, 'pod_stime_utc': datetime.datetime(2018, 5, 14, 18, 5, 42), 'pod_sat': 'M03'}] (Background on this error at: http://sqlalche.me/e/4xp6)
```

the test code is here:

```
import utils.databases  as sa
import utils.times as dt

connection_string = "oracle://user:pass@DBS:1521/dbname"

engine = sa.create_engine(connection_string, echo = True, max_overflow = 0, pool_size = 3)
_session = sa.orm.sessionmaker()
_session.configure(bind=engine)
session = _session()

Base = sa.ext.declarative.declarative_base()

try:
    class Pod(Base):
        __table__ = sa.Table("pod", Base.metadata, autoload = True,
                             autoload_with = engine)
except:
    raise IOError, "Cannot connect to database engine. Possibly database does not exist."

# Create new object

nap = Pod()

# Fill row with new information

try:
    nap.sat = "M03"
    nap.stime_utc = dt.DateTime(2018, 5, 14, 18, 5, 42)
    nap.etime_utc = dt.DateTime(2018, 5, 14, 18, 10, 42)
    nap.converge = False

    session.merge(nap)

    # Commit session

    session.commit()

    del nap

except OSError, e:
    logging.warning(e)
    del nap

# Dispose database engine

engine.dispose()
```

I use Oracle 11g, cx_oracle 6.3 and oracle instantclient 12.2.

If I change the code and set pod.converge = 1 or 0, everything works perfect. The field is declared as Boolean and I assume it should convert it internally to 1 and 0 as far as Oracle 11g doesn't have a native boolean support. The 'converge' column in the table has a type NUMBER(38,0).

[Sqlalchemy-tickets] Issue #4258: relationship caching in dogile example breaks in 1.2.x series (zzzeek/sqlalchemy)

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

New issue 4258: relationship caching in dogile example breaks in 1.2.x series
https://bitbucket.org/zzzeek/sqlalchemy/issues/4258/relationship-caching-in-dogile-example

Michael Bayer:

with #4256 we are repairing a regression in 1.2.7 for dogpile.advanced, however running it on prior 1.2.x series, the baked lazy load is preventing the caching from occurring.  after the fix for #4256 it *seems* to fix it but it is not clear what's going on here.

[Sqlalchemy-tickets] Issue #4257: add .info to InstanceState (zzzeek/sqlalchemy)

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

New issue 4257: add .info to InstanceState
https://bitbucket.org/zzzeek/sqlalchemy/issues/4257/add-info-to-instancestate

Michael Bayer:

I'm always looking for this, let's add it

[Sqlalchemy-tickets] Issue #4256: SQLAlchemy 1.2.7 breaks dogpile caching example (zzzeek/sqlalchemy)

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

New issue 4256: SQLAlchemy 1.2.7 breaks dogpile caching example
https://bitbucket.org/zzzeek/sqlalchemy/issues/4256/sqlalchemy-127-breaks-dogpile-caching

Frazer McLean:

To reproduce:

```
python3 -m examples.dogpile_caching.advanced
```

```
Traceback (most recent call last):
  File "/Users/frazer/.pyenv/versions/3.6.5/lib/python3.6/runpy.py", line 193, in _run_module_as_main
    "__main__", mod_spec)
  File "/Users/frazer/.pyenv/versions/3.6.5/lib/python3.6/runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "/Users/frazer/projects/personal/sqlalchemy/examples/dogpile_caching/advanced.py", line 68, in <module>
    print(p.format_full())
  File "/Users/frazer/projects/personal/sqlalchemy/examples/dogpile_caching/model.py", line 95, in format_full
    return "\t".join([str(x) for x in [self] + list(self.addresses)])
  File "/Users/frazer/projects/personal/sqlalchemy/examples/dogpile_caching/model.py", line 95, in <listcomp>
    return "\t".join([str(x) for x in [self] + list(self.addresses)])
  File "/Users/frazer/projects/personal/sqlalchemy/examples/dogpile_caching/model.py", line 74, in __str__
    "%s" % (self.street, self.city.name,
  File "/Users/frazer/projects/personal/sqlalchemy/examples/dogpile_caching/model.py", line 65, in city
    return self.postal_code.city
  File "/Users/frazer/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/orm/attributes.py", line 242, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "/Users/frazer/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/orm/attributes.py", line 599, in get
    value = self.callable_(state, passive)
  File "/Users/frazer/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/orm/strategies.py", line 619, in _load_for_state
    instance = session._query_cls._identity_lookup(
AttributeError: 'function' object has no attribute '_identity_lookup'
```

See [this commit](https://bitbucket.org/zzzeek/sqlalchemy/commits/43f278356d94b5342a1020a9a97feea0bb7cd88f#Llib/sqlalchemy/orm/strategies.pyT619)

As of 1.2.7 `session._query_cls` is expected to be a class, but previously the function returned by `query_callable` in the dogpile caching example worked.

[Sqlalchemy-tickets] Issue #4255: break out pool into a package; include prioritypool? (zzzeek/sqlalchemy)

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

New issue 4255: break out pool into a package; include prioritypool?
https://bitbucket.org/zzzeek/sqlalchemy/issues/4255/break-out-pool-into-a-package-include

Michael Bayer:

There's enough attention going on with the connection pool these days that it might be nice to break it into a package, such that the main Pool/ConnectionFairy/ConnectionRecord is in one place, the ancient/never-used/crufty "manage" stuff can be elsewhere (and probably deprecated), and then the implementation classes like QueuePool, SingletonThreadPool can each get their own file and be kind of manageable.  Then we can think about evaluating haalchemy's pool https://bitbucket.org/zzzeek/haalchemy/src/master/haalchemy/clients/sqlalchemy/pool.py for inclusion as well.   For AbstractPriorityPool it still might be nice to have the "queue" part of it broken out into a separate class so that it can be more easily compared to QueuePool.

[Sqlalchemy-tickets] Issue #4254: No way to combine case-insensitive contains() with autoescape (zzzeek/sqlalchemy)

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

New issue 4254: No way to combine case-insensitive contains() with autoescape
https://bitbucket.org/zzzeek/sqlalchemy/issues/4254/no-way-to-combine-case-insensitive

vr2262:

I would like to filter a query with `ILIKE` while escaping special characters like `%` and `_`. I tried this:

```
#!python
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()


class Example(Base):
    __tablename__ = 'example'
    id = Column(Integer, primary_key=True)
    string_col = Column(String)


session = sessionmaker()()
session.add(Example(string_col='S%mething'))

search_string = 's%'
print(
    session.query(Example)
    .filter(
        func.lower(Example.string_col)
        .contains(func.lower(search_string), autoescape=True)
    )
)
```
But I get the error `TypeError: String value expected when autoescape=True` because `contains()` expects an actual string as the first argument.

Ideally SQLAlchemy would provide this feature out of the box, perhaps with `icontains()` or `contains(insensitive=True, ...)`.

[Sqlalchemy-tickets] Issue #4253: Python exe giving error with sqlalchemy. However it works fine while running from a .py file. (zzzeek/sqlalchemy)

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

New issue 4253: Python exe giving error with sqlalchemy. However it works fine while running from a .py file.
https://bitbucket.org/zzzeek/sqlalchemy/issues/4253/python-exe-giving-error-with-sqlalchemy

Jorge:

Sqlalchemy package will give the error while running the below line

engine =  sqlalchemy.create_engine('teradata://userid:pwd@DBASE?driver=Teradata')

However this works fine while running from spyder as a .py file. 
Converting to exe and trying to run will give the following error. 

sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:teradata

[Sqlalchemy-tickets] Issue #4252: double checkin due to handle_error called inside of _finalize_fairy -> reset (zzzeek/sqlalchemy)

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

New issue 4252: double checkin due to handle_error called inside of _finalize_fairy -> reset
https://bitbucket.org/zzzeek/sqlalchemy/issues/4252/double-checkin-due-to-handle_error-called

Michael Bayer:

```
#!python

from sqlalchemy import create_engine
import mock
import psycopg2

e = create_engine("postgresql://scott:tiger@localhost/test")
# fill pool with one connection
e.connect().close()

print(e.pool._pool.queue)

# have an exception be a disconnect error
e.dialect.is_disconnect = lambda *arg, **kw: True

conn = e.connect()
trans = conn.begin()

# have a rollback raise, will cause disconnect + invalidate
with mock.patch.object(
    conn.connection, "rollback",
    side_effect=psycopg2.OperationalError("something went wrong with the rollback")
):
    conn.close()

# connection is checked in twice
print(e.pool._pool.queue)

```

at the end we get:


```
#!python

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) something went wrong with the rollback (Background on this error at: http://sqlalche.me/e/e3q8)
deque([<sqlalchemy.pool._ConnectionRecord object at 0x7f71910976a0>, <sqlalchemy.pool._ConnectionRecord object at 0x7f71910976a0>])

```

this is due to the reset agent calling _handle_error which then goes and does the invalidation sequence.