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

[Sqlalchemy-tickets] Issue #4044: SQLite, CREATE TABLE AS and DATETIME: TypeError: must be real number, not str (zzzeek/sqlalchemy)

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

New issue 4044: SQLite, CREATE TABLE AS and DATETIME: TypeError: must be real number, not str
https://bitbucket.org/zzzeek/sqlalchemy/issues/4044/sqlite-create-table-as-and-datetime

Brecht Machiels:

The following fails with SQLALchemy 1.1.11, 1.1.13 and 1.2.0b2. I'm running CPython 3.6.1 (conda-forge, 64-bit on Windows 7).

```
#!python

from datetime import datetime

from sqlalchemy import create_engine, MetaData, Table, Column, DateTime


engine = create_engine('sqlite:///:memory:')
meta = MetaData(bind=engine)
mytable = Table('mytable', meta,
                Column('dt', DateTime))
meta.create_all()

engine.execute(mytable.insert().values(dt=datetime(1982, 10, 1, 10, 00, 00)))

for row in engine.execute(mytable.select()):
    print(row)


engine.execute('CREATE TABLE mytable2 AS SELECT * FROM mytable')


# (works) using the DBAPI connection
conn = engine.connect()
for row in conn.execute('SELECT * FROM mytable2'):
    print(row)

# (fails) using SQLAlchemy
mytable2 = Table('mytable2', meta, autoload=True)
for row in engine.execute(mytable2.select()):
    print(row)

```

Stack trace:
```
#!



C:\Users\bmachie\AppData\Local\Continuum\Miniconda3\envs\ml_irissearch\lib\site-packages\sqlalchemy\sql\sqltypes.py:596: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.
  'storage.' % (dialect.name, dialect.driver))
Traceback (most recent call last):
  File "C:/Users/bmachie/.PyCharmCE2017.2/config/scratches/scratch_6.py", line 30, in <module>
    print(row)
  File "C:\Users\bmachie\AppData\Local\Continuum\Miniconda3\envs\ml_irissearch\lib\site-packages\sqlalchemy\engine\result.py", line 156, in __repr__
    return repr(sql_util._repr_row(self))
  File "C:\Users\bmachie\AppData\Local\Continuum\Miniconda3\envs\ml_irissearch\lib\site-packages\sqlalchemy\sql\util.py", line 325, in __repr__
    ", ".join(trunc(value) for value in self.row),
TypeError: must be real number, not str
```

[Sqlalchemy-tickets] Issue #4043: sqlite does not support deferred keyword on (unique) constraint (zzzeek/sqlalchemy)

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

New issue 4043: sqlite does not support deferred keyword on (unique) constraint
https://bitbucket.org/zzzeek/sqlalchemy/issues/4043/sqlite-does-not-support-deferred-keyword

Martin Babka:

When using sqlite and setting 


```
#!python

class Model(Base):
    __tablename__ = 'users'

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

    __table_args__ = (UniqueConstraint('order', name='single_occurrence_of_order', deferrable=True),)

```

then creating the tables throws sqlite's syntax error:


```
#!text

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "DEFERRABLE": syntax error [SQL: '\nCREATE TABLE users (\n\tid INTEGER NOT NULL, \n\tname VARCHAR, \n\t"order" INTEGER, \n\tPRIMARY KEY (id), \n\tCONSTRAINT single_occurrence_of_order UNIQUE ("order") DEFERRABLE\n)\n\n']
```

[Issue 2841](https://bitbucket.org/zzzeek/sqlalchemy/issues/2841) addresses the problem for MySQL, a similar solution may be done in case of sqlite. 

I could resolve the problem as in [this commit](https://bitbucket.org/zzzeek/sqlalchemy/commits/cf1ac72bca8b0bc28e09cdb4cdf052bcf82e5076).

[Sqlalchemy-tickets] Issue #4042: reflect oracle no-column (functional) index when table has no primray key (zzzeek/sqlalchemy)

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

New issue 4042: reflect oracle no-column (functional) index when table has no primray key
https://bitbucket.org/zzzeek/sqlalchemy/issues/4042/reflect-oracle-no-column-functional-index

Michael Bayer:

```
#!python

from sqlalchemy import *

m = MetaData()

t = Table(
   't', m,
   Column('q', Integer)
)

Index('t_idx_1', func.lower(t.c.q))
Index('t_idx_2', t.c.q)

e = create_engine("oracle://scott:tiger@xe", echo='debug')
m.drop_all(e)
m.create_all(e)

insp = inspect(e)

print insp.get_indexes('t')

```


fix:


```
#!diff

diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index d9fa80d..83a76e5 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -1476,7 +1476,7 @@ class OracleDialect(default.DefaultDialect):
 
         def remove_if_primary_key(index):
             # don't include the primary key index
-            if index is not None and \
+            if pk_names and index is not None and \
                upper_name_set(index['column_names']) == pk_names:
                 indexes.pop()
 

```

[Sqlalchemy-tickets] Issue #4041: pg8000 fails to accept quoted_name as param type (zzzeek/sqlalchemy)

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

New issue 4041: pg8000 fails to accept quoted_name as param type
https://bitbucket.org/zzzeek/sqlalchemy/issues/4041/pg8000-fails-to-accept-quoted_name-as

Michael Bayer:

```
#!

NotSupportedError: (pg8000.core.NotSupportedError) type <class 'sqlalchemy.sql.elements.quoted_name'>not mapped to pg type [SQL: u"SELECT c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = %s AND c.relkind = 'r'"] [parameters: ('test_schema',)]

```

[Sqlalchemy-tickets] Issue #4040: expunge pending orphans on flush that weren't caught by other means (zzzeek/sqlalchemy)

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

New issue 4040: expunge pending orphans on flush that weren't caught by other means
https://bitbucket.org/zzzeek/sqlalchemy/issues/4040/expunge-pending-orphans-on-flush-that

Michael Bayer:

```
#!python

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

Base = declarative_base()


class A1(Base):
    __tablename__ = 'a1'
    id = Column(Integer, primary_key=True)
    bs = relationship("B", cascade="all, delete-orphan")


class A2(Base):
    __tablename__ = 'a2'
    id = Column(Integer, primary_key=True)
    bs = relationship("B", cascade="all, delete-orphan")


class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a1_id = Column(ForeignKey('a1.id'))
    a2_id = Column(ForeignKey('a2.id'))

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

s = Session(e)

b_orphan1, b_orphan2 = B(), B()

a1a, a1b = A1(), A1()

a2a = A2()

a2a.bs.append(b_orphan1)
a2a.bs.append(b_orphan2)

s.add(a2a)
s.add(a1a)

# add it here, it works
# s.add(a1b)

a1a.bs.append(b_orphan1)
a1b.bs.append(b_orphan2)

a1a.bs.remove(b_orphan1)
a1b.bs.remove(b_orphan2)

# down here, fails
s.add(a1b)

s.commit()

assert len(s.query(B).all()) == 0


```

fix is to check for pending orphans once more at the same time we check for persistent orphans:


```
#!diff



diff --git a/lib/sqlalchemy/orm/session.py b/lib/sqlalchemy/orm/session.py
index 7c313e6..bade700 100644
--- a/lib/sqlalchemy/orm/session.py
+++ b/lib/sqlalchemy/orm/session.py
@@ -2271,11 +2271,13 @@ class Session(_SessionClassMethods):
             proc = new.union(dirty).difference(deleted)
 
         for state in proc:
-            is_orphan = (
-                _state_mapper(state)._is_orphan(state) and state.has_identity)
-            _reg = flush_context.register_object(state, isdelete=is_orphan)
-            assert _reg, "Failed to add object to the flush context!"
-            processed.add(state)
+            is_orphan = _state_mapper(state)._is_orphan(state)
+            if is_orphan and not state.has_identity:
+                self._expunge_states([state])
+            else:
+                _reg = flush_context.register_object(state, isdelete=is_orphan)
+                assert _reg, "Failed to add object to the flush context!"
+                processed.add(state)
 
         # put all remaining deletes into the flush context.
         if objset:

```

[Sqlalchemy-tickets] Issue #4039: pool_pre_ping not a valid kwarg for create_engine for MySQL (zzzeek/sqlalchemy)

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

New issue 4039: pool_pre_ping not a valid kwarg for create_engine for MySQL
https://bitbucket.org/zzzeek/sqlalchemy/issues/4039/pool_pre_ping-not-a-valid-kwarg-for

Heather Lent:

I am on the most up-to-date SQLAlchemy (`1.1.12`), and trying to use the `pool_pre_ping=True` in the `create_engine()` function. Attempting to use pool_pre_ping results in an error, that pool_pre_ping is not a valid keyword argument.

Here is a test that anyone can run to re-create the problem.
Instructions for needed dependencies, my version of MySQL, and my driver are in the comments at the top of the code. 
You will need to replace `"mysql://...."` with the credentials for your own database.

```
#!python
'''
Setup instructions for Python 3.4 or 3.5 (will probably work for any version of 3):
pip install sqlalchemy=='1.1.12'
pip install mysqlclient #database driver; necessary for MySQL & sqlalchemy for Python 3 

I have MySQL version 5.6.33 on Ubuntu 14.04
'''
from sqlalchemy import create_engine #import create_engine

engine = create_engine("mysql://username:password@localhost:port/dbname?charset=utf8mb4", pool_pre_ping=True) #attempt to create engine

'''
FULL STACK TRACE ERROR: 
Traceback (most recent call last):
  File "test.py", line 10, in <module>
    engine = create_engine("mysql://username:password@localhost:port/dbname?charset=utf8mb4", pool_pre_ping=True)
  File "/home/hclent/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/__init__.py", line 387, in create_engine
    return strategy.create(*args, **kwargs)
  File "/home/hclent/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/strategies.py", line 160, in create
    engineclass.__name__))
TypeError: Invalid argument(s) 'pool_pre_ping' sent to create_engine(), using configuration MySQLDialect_mysqldb/QueuePool/Engine.  Please check that the keyword arguments are appropriate for this combination of components.
'''
```

I have attached this code in a `.py` file as well. Running it will show the error just as I have documented it (but make sure to follow the setup instructions!)

Other comments: 

If I navigate to the directory where SQLALchemy files are `.../lib/python3.5/site-packages/sqlalchemy`, and try:
`grep -r pool_pre_ping  * | less `
There are no results, suggesting that pool_pre_ping is not actually valid for the stable release of sqlalchemy. 
I also checked the `__init__.py` in `sqlalchemy/engine`, where the `create_engine( )` function lives, and it does not mention pool_pre_ping anywhere.

According to [this documentation](http://docs.sqlalchemy.org/en/latest/core/pooling.html#dealing-with-disconnects), `create_engine(..., pool_pre_ping=True)` should not fail. Since I am using MySQL and need a reliable connection pool for many users, disabling the pool as [suggested in Issue 3919](https://bitbucket.org/zzzeek/sqlalchemy/issues/3919) and [spelled out here](http://docs.sqlalchemy.org/en/latest/core/pooling.html#switching-pool-implementations) is not a good alternative. 

That being said, the release of [1.2.0b1](http://docs.sqlalchemy.org/en/latest/changelog/changelog_12.html#change-1.2.0b1) seems to have a bugfix that would allow me to do `create_engine.pool_pre_ping( ... )`. Does this mean I should be using an unstable version of SQLAlchemy? 

In summary, given the [documentation](http://docs.sqlalchemy.org/en/latest/core/pooling.html#pool-disconnects-pessimistic), the fact I'm using `SQLAlchemy 1.1.12`, I believe that there is a bug here.

Please let me know if I am missing anything in my Issue (I believe I properly followed the guidelines) and if there's any way I can help contribute :)

[Sqlalchemy-tickets] Issue #4038: Additional dialect kwargs are not reflected (zzzeek/sqlalchemy)

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

New issue 4038: Additional dialect kwargs are not reflected
https://bitbucket.org/zzzeek/sqlalchemy/issues/4038/additional-dialect-kwargs-are-not

chebee7i:

Some keyword kwargs like 'mysql_partition_by' and 'mysql_stats_sample_pages' are not reflected and stored in kwargs.

```
#!python

import sqlalchemy as sa
m = sa.MetaData()

t = sa.Table('ti', m,
    sa.Column('id', sa.Integer),
    sa.Column('amount', sa.DECIMAL(7, 2)),
    sa.Column('tr_date', sa.Date),
    mysql_engine='InnoDB',
    mysql_partition_by='HASH( MONTH(tr_date) )',
    mysql_partitions='6'
)

engine = ...
t.create(engine)
m2 = sa.Metadata(bind=engine, reflect=True)
assert 'mysql_partition_by' in m2.tables['ti'].kwargs  # AssertionError
```

[Sqlalchemy-tickets] Issue #4037: Glitch in the custom DDL documentation (zzzeek/sqlalchemy)

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

New issue 4037: Glitch in the custom DDL documentation
https://bitbucket.org/zzzeek/sqlalchemy/issues/4037/glitch-in-the-custom-ddl-documentation

Lele Gaifax:

In the `trigger` definition near line 62 of `doc/build/core/ddl.rst` (sorry, could not find the proper syntax to hyperlink that line), where it is explicitly showing an example for PostgreSQL, the `DDL` uses a syntax which is not valid for a PG trigger:
```
CREATE TRIGGER dt_ins BEFORE INSERT ON mytable
FOR EACH ROW BEGIN SET NEW.data='ins'; END
```

Accordingly to the [documentation](https://www.postgresql.org/docs/9.6/static/sql-createtrigger.html) a trigger cannot execute an arbitrary code block, but must run a *procedure*, so for example it could be written as:
```
CREATE TRIGGER dt_ins BEFORE INSERT ON mytable
FOR EACH ROW EXECUTE PROCEDURE set_data_field('ins');
```

[Sqlalchemy-tickets] Issue #4036: mysql decimal cast on bind parameters (zzzeek/sqlalchemy)

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

New issue 4036: mysql decimal cast on bind parameters
https://bitbucket.org/zzzeek/sqlalchemy/issues/4036/mysql-decimal-cast-on-bind-parameters

Michael Bayer:

as in https://bitbucket.org/zzzeek/sqlalchemy/src/2ae2c25c5d24c26fdca979b091cd432c64be822d/lib/sqlalchemy/testing/suite/test_types.py?at=master&fileviewer=file-view-default#test_types.py-442, decimals are sent as strings to MySQL which doesn't know to interpret them with numeric accuracy.   ensure #3981 is done using "method two" to allow for dialect-level bind expressions and implement a cast in bind_expression().

[Sqlalchemy-tickets] Issue #4035: Binding strings in Oracle is very slow (zzzeek/sqlalchemy)

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

New issue 4035: Binding strings in Oracle is very slow
https://bitbucket.org/zzzeek/sqlalchemy/issues/4035/binding-strings-in-oracle-is-very-slow

Anthony Tuininga:

See this issue that was raised in cx_Oracle: https://github.com/oracle/python-cx_Oracle/issues/58 and the analysis there. It also contains the test case that demonstrates the problem. Let me know if you require any additional information.

[Sqlalchemy-tickets] Issue #4034: test failure as of Python 3.6.2 (zzzeek/sqlalchemy)

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

New issue 4034: test failure as of Python 3.6.2
https://bitbucket.org/zzzeek/sqlalchemy/issues/4034/test-failure-as-of-python-362

Michael Bayer:

released just on July 17:

_________________________________________ 
```
#!python

SessionStateTest.test_with_no_autoflush_after_exception _________________________________________
Traceback (most recent call last):
  File "/home/classic/dev/sqlalchemy/test/orm/test_session.py", line 324, in test_with_no_autoflush_after_exception
    lambda obj: 1 / 0
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/assertions.py", line 279, in assert_raises
    assert success, "Callable did not raise an exception"
AssertionError: Callable did not raise an exception
============================================= 1 failed, 77 passed, 4 skipped in 1.92 seconds ==============================================

```

clearly, 1/0 raises ZeroDivisionError by itself, someting is up with the fixtures or pytest or something.  does not appear on 3.6.0.

[Sqlalchemy-tickets] Issue #4033: dont crash for add_columns() when subqueryload=True (zzzeek/sqlalchemy)

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

New issue 4033: dont crash for add_columns() when subqueryload=True
https://bitbucket.org/zzzeek/sqlalchemy/issues/4033/dont-crash-for-add_columns-when

Michael Bayer:

#4032 fixes this for the .exists() case, however at the moment a simple call to add_columns() makes subqueryload break:


```
#!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)
    bs = relationship("B", lazy="subquery")


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

s = Session()

print s.query(A).add_columns('1').statement


```

for the test, make sure all of .exists() and .subquery() can succeed; do for all eager load styles.

[Sqlalchemy-tickets] Issue #4032: enable_eagerloads(False) automatically for .subquery(), .exists() (zzzeek/sqlalchemy)

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

New issue 4032: enable_eagerloads(False) automatically for .subquery(), .exists()
https://bitbucket.org/zzzeek/sqlalchemy/issues/4032/enable_eagerloads-false-automatically-for

Michael Bayer:

calling .exists() on an entity that has a subqueryload set up raises an error in 1.1 due to the internals not handling it correctly.    There should be no reason eagerloads are called upon at all for an exists() and probably for .subquery() in general.

[Sqlalchemy-tickets] Issue #4031: eval_none logic isn't resolving col to prop (zzzeek/sqlalchemy)

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

New issue 4031: eval_none logic isn't resolving col to prop
https://bitbucket.org/zzzeek/sqlalchemy/issues/4031/eval_none-logic-isnt-resolving-col-to-prop

Michael 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)
    column1 = Column('column1', JSON, nullable=False)
    _column2 = Column('column2', JSON, nullable=False)

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

session = Session(e)

# Succeeds
session.add(A(column1=[], _column2=[]))
session.flush()

# Succeeds
session.add(A(column1=None, _column2=[]))
session.flush()

# Fails with integrity error
session.add(A(column1=[], _column2=None))
session.flush()




```

this would be the patch but need to confirm key is always present in _columntoproperty in this context:

```
#!diff


diff --git a/lib/sqlalchemy/orm/mapper.py b/lib/sqlalchemy/orm/mapper.py
index 1042442..68103d2 100644
--- a/lib/sqlalchemy/orm/mapper.py
+++ b/lib/sqlalchemy/orm/mapper.py
@@ -2020,7 +2020,7 @@ class Mapper(InspectionAttr):
             (
                 table,
                 frozenset(
-                    col.key for col in columns
+                    self._columntoproperty[col].key for col in columns
                     if col.type.should_evaluate_none
                 )
             )

```

[Sqlalchemy-tickets] Issue #4030: Very weird `KeyError` exception (zzzeek/sqlalchemy)

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

New issue 4030: Very weird `KeyError` exception
https://bitbucket.org/zzzeek/sqlalchemy/issues/4030/very-weird-keyerror-exception

ddzialak:

```
Traceback (most recent call last):
  File "site-packages/cherrypy/_cprequest.py", line 670, in respond
  File "site-packages/cherrypy/lib/encoding.py", line 221, in __call__
  File "site-packages/cherrypy/_cpdispatch.py", line 60, in __call__
  File "sfutils/perf_stats.py", line 206, in wrapper
  File "sfutils/base_service.py", line 461, in wrapper
  File "sfutils/base_service.py", line 395, in call_f_and_return_json
  File "sfvolumes/service.py", line 260, in request_save_volume
  File "sfvolumes/service.py", line 229, in _save_volume
  File "sfutils/perf_stats.py", line 206, in wrapper
  File "sfvolumes/dao.py", line 206, in get_volume_by_name
  File "sfvolumes/dao.py", line 190, in _get_volume_by_name
  File "site-packages/sqlalchemy/orm/query.py", line 2814, in one
  File "site-packages/sqlalchemy/orm/query.py", line 2784, in one_or_none
  File "site-packages/sqlalchemy/orm/loading.py", line 90, in instances
  File "site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
  File "site-packages/sqlalchemy/orm/loading.py", line 75, in instances
  File "site-packages/sqlalchemy/orm/loading.py", line 385, in _instance
  File "site-packages/sqlalchemy/orm/identity.py", line 151, in get
KeyError: (<class 'sfutils.pg_api.alchemy_schema.Volume'>, (2L,))
```
It's very weird because code within `orm/identity.py` looks like:
```
[148]    def get(self, key, default=None):
[149]        if key not in self._dict:
[150]            return default
[151]        state = self._dict[key]
```

First time I've though that `session` is used in two threads but session is stored only within `dao` and dao is a local parameter so it's not possible that more than one thread is using the same session.
The other strange thing  is backtrace - can't explain why `raise_from_cause` (from util.py) invoked `instances` (from loading.py).
I've saw that `weakref` is used so is it possible that default `_identity_cls = identity.WeakInstanceDict` from session caused that fired garbage collector freed some objects and removed some key from `self._dict` within identity_map? (just while thread was between `identity.py:149` and `identity.py:151`)?

We use sqlalchemy very intensively and that problem happened three times since last four months (so it seems to be very rare case).

Versions:
SQLAlchemy (1.1.10)
psycopg2 (2.7.1)
postgresql   9.6+184.pgdg14.04+1
python 2.7.12

Unfortunately can't create any Minimal, Complete and Verifiable Example as it seems to be very rare case.

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #4029: RuntimeError error with gevent after few hours running (zzzeek/sqlalchemy)

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

New issue 4029: RuntimeError error with gevent after few hours running
https://bitbucket.org/zzzeek/sqlalchemy/issues/4029/runtimeerror-error-with-gevent-after-few

TonyWangBit:

The code communicate with database, looks like the code below.
https://gist.github.com/tonywangcn/6dadbd58d7778063b0c7969f62505537

I tried to reproduce the error, but everything seems ok. 

SQLAlchemy==1.1.4
gevent==1.2.0
greenlet==0.4.11
PyMySQL==0.7.11

error 1
```
#!python

2017-07-15 10:59:15,688 INFO sqlalchemy.engine.threadlocal.TLEngine ROLLBACK
Exception during reset or similar
Traceback (most recent call last):
  File "/root/proxyserver/db/SqlHelper.py", line 133, in update
    updateNum = query.update(updatevalue)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 3288, in update
    update_op.exec_()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 1168, in exec_
    self._do_exec()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 1323, in _do_exec
    mapper=self.mapper)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 1107, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 971, in _connection_for_bind
    engine, execution_options)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 382, in _connection_for_bind
    self._assert_active()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 264, in _assert_active
    "This session is in 'prepared' state; no further "
sqlalchemy.exc.InvalidRequestError: This session is in 'prepared' state; no further SQL can be emitted within this transaction.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 687, in _finalize_fairy
    fairy._reset(pool)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 829, in _reset
    pool._dialect.do_rollback(self)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/dialects/mysql/base.py", line 1547, in do_rollback
    dbapi_connection.rollback()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 793, in rollback
    self._read_ok_packet()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 767, in _read_ok_packet
    pkt = self._read_packet()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 987, in _read_packet
    packet_header = self._read_bytes(4)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 1021, in _read_bytes
    data = self._rfile.read(num_bytes)
RuntimeError: reentrant call inside <_io.BufferedReader name=29>
```




error 2
```
#!python

2017-07-15 10:59:21,558 INFO sqlalchemy.engine.threadlocal.TLEngine ROLLBACK
Exception during reset or similar
Traceback (most recent call last):
  File "/root/proxyserver/db/SqlHelper.py", line 133, in update
    updateNum = query.update(updatevalue)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 3288, in update
    update_op.exec_()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 1168, in exec_
    self._do_exec()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 1323, in _do_exec
    mapper=self.mapper)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 1107, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1396, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 469, in do_execute
    cursor.execute(statement, parameters)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/cursors.py", line 166, in execute
    result = self._query(query)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 856, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 1057, in _read_query_result
    result.read()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 1340, in read
    first_packet = self.connection._read_packet()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 987, in _read_packet
    packet_header = self._read_bytes(4)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 1021, in _read_bytes
    data = self._rfile.read(num_bytes)
RuntimeError: reentrant call inside <_io.BufferedReader name=27>

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 687, in _finalize_fairy
    fairy._reset(pool)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 829, in _reset
    pool._dialect.do_rollback(self)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/dialects/mysql/base.py", line 1547, in do_rollback
    dbapi_connection.rollback()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 793, in rollback
    self._read_ok_packet()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 767, in _read_ok_packet
    pkt = self._read_packet()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 987, in _read_packet
    packet_header = self._read_bytes(4)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 1021, in _read_bytes
    data = self._rfile.read(num_bytes)
RuntimeError: reentrant call inside <_io.BufferedReader name=27>
```

[Sqlalchemy-tickets] Issue #4028: move can't connect handling outside of "except Empty" (zzzeek/sqlalchemy)

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

New issue 4028: move can't connect handling outside of "except Empty"
https://bitbucket.org/zzzeek/sqlalchemy/issues/4028/move-cant-connect-handling-outside-of

Michael Bayer:

When a pool can't connect to the DB, Python 3 does this:


```
#!

Traceback (most recent call last):
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 1122, in _do_get
    return self._pool.get(wait, self._timeout)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/util/queue.py", line 145, in get
    raise Empty
sqlalchemy.util.queue.Empty
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/engine/base.py", line 2147, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 328, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 766, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 516, in checkout
    rec = pool._do_get()
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 1138, in _do_get
    self._dec_overflow()
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 1135, in _do_get
    return self._create_connection()
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 333, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 461, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 651, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/engine/strategies.py", line 105, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/engine/default.py", line 393, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib64/python3.5/site-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib64/python3.5/site-packages/MySQLdb/connections.py", line 191, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server on 'dbhost_obviously' (4)")
```


this confuses everyone.  move the handling outside of Empty.

[Sqlalchemy-tickets] Issue #4027: JSON Type throws error when using Dialect that does not support it (zzzeek/sqlalchemy)

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

New issue 4027: JSON Type throws error when using Dialect that does not support it
https://bitbucket.org/zzzeek/sqlalchemy/issues/4027/json-type-throws-error-when-using-dialect

Adrian Partl:

I want to use the JSON Column Type with the sqlite. This throws the following error, suggesting that this should work (i.e. JSON being serialised into string). However the code is broken at that point, since `_json_serializer` is not part of the dialect. This should be a `hasattr` test...

```
...
  File "/Users/adrian/.virtualenvs/venv/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 493, in <genexpr>
    (key, value) for key, value in
  File "/Users/adrian/.virtualenvs/venv/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 496, in <genexpr>
    for bindparam in self.bind_names)
  File "/Users/adrian/.virtualenvs/venv/lib/python2.7/site-packages/sqlalchemy/sql/type_api.py", line 459, in _cached_bind_processor
    d['bind'] = bp = d['impl'].bind_processor(dialect)
  File "/Users/adrian/.virtualenvs/venv/lib/python2.7/site-packages/sqlalchemy/sql/sqltypes.py", line 2050, in bind_processor
    json_serializer = dialect._json_serializer or json.dumps
StatementError: (exceptions.AttributeError) 'SQLiteDialect_pysqlite' object has no attribute '_json_serializer' [SQL: u'INSERT INTO metadata...
```

[Sqlalchemy-tickets] Issue #4026: mixing selectin load on top of inline (zzzeek/sqlalchemy)

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

New issue 4026: mixing selectin load on top of inline
https://bitbucket.org/zzzeek/sqlalchemy/issues/4026/mixing-selectin-load-on-top-of-inline

Michael Bayer:

pushing the new feature beyond what was planned see how hard this would be


```
#!python

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


Base = declarative_base()


class Person(Base):
    __tablename__ = 'person'

    id = Column(Integer, primary_key=True)
    type = Column(String(50), nullable=False)
    name = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity': 'person',
        'polymorphic_on': type
    }


class Manager(Person):
    __tablename__ = 'manager'

    id = Column(ForeignKey('person.id'), primary_key=True)
    manager_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity': 'manager',
        'polymorphic_load': 'selectin'
    }


class EngineerBase(Person):
    __tablename__ = 'engineer'

    id = Column(ForeignKey('person.id'), primary_key=True)
    engineer_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_load': 'selectin'
    }


class EngineerType1(EngineerBase):
    __mapper_args__ = {
        'polymorphic_identity': 'engineer_t1',
        'polymorphic_load': 'inline'
    }


class EngineerType2(EngineerBase):
    __mapper_args__ = {
        'polymorphic_identity': 'engineer_t2',
        'polymorphic_load': 'inline'
    }


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

engine.echo = True

session = Session(engine)

eng1 = EngineerType1()
eng2 = EngineerType2()

session.add_all([eng1, eng2])
session.commit()

session.query(Person).all()
```

[Sqlalchemy-tickets] Issue #4025: Arguments dropped in tuple comparison (zzzeek/sqlalchemy)

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

New issue 4025: Arguments dropped in tuple comparison
https://bitbucket.org/zzzeek/sqlalchemy/issues/4025/arguments-dropped-in-tuple-comparison

Patrick Hayes:

Observed in sqlalchemy==1.2.0b1, psycopg2==2.7.1, postgres 9.5

When the `filter`/`having` clause contains a tuple comparison, the additional args of the tuple are unexpectedly discarded.

```
from sqlalchemy import Column, BigInteger, create_engine
from sqlalchemy.engine.url import URL
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine(URL(
  'postgresql',
  username='postgres',
  host='localhost',
  port=5432,
))

Base = declarative_base()

class Model(Base):
  __tablename__ = 'model'
  id = Column(BigInteger, primary_key=True)
  other = Column(BigInteger)

Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
session_factory = sessionmaker(bind=engine)
session = session_factory()
print session.query(Model).filter((Model.id, Model.other) < (100, 100))
```

This prints out:

```
SELECT model.id AS model_id, model.other AS model_other 
FROM model 
WHERE model.id < %(id_1)s
```

But I would expect

```
...
WHERE (model.id, model.other) < (%(id_1)s, %(other_1)s)
```

While I was able to get around this by manually casting as a `tuple_`, is there a reason why this does not automatically manifest as a tuple comparison? Or throw an error to warn that arguments will be discarded?

I'm happy to investigate a PR, unless this is expected behaviour

[Sqlalchemy-tickets] Issue #4024: Mention the "advanced" technique to have raw SQL with inline param in the FAQ (zzzeek/sqlalchemy)

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

New issue 4024: Mention the "advanced" technique to have raw SQL with inline param in the FAQ
https://bitbucket.org/zzzeek/sqlalchemy/issues/4024/mention-the-advanced-technique-to-have-raw

Lele Gaifax:

On IRC, one of the most frequently asked question is [this one](http://docs.sqlalchemy.org/en/latest/faq/sqlexpressions.html): it would be nice if it could be integrated with the *more advanced* technique depicted on [StackOverflow](https://stackoverflow.com/questions/21735943/retrieving-ultimate-sql-query-sentence-with-the-values-in-place-of-any), with a particular mention of the function `visitors.replacement_traverse()`.

[Sqlalchemy-tickets] Issue #4023: SQLAlchemy with cx_oracle does not recognize UROWID type (zzzeek/sqlalchemy)

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

New issue 4023: SQLAlchemy with cx_oracle does not recognize UROWID type
https://bitbucket.org/zzzeek/sqlalchemy/issues/4023/sqlalchemy-with-cx_oracle-does-not

Peter Karimeddini:

Database: **Oracle 11g**
Relevant substring of error message: *python3.5/site-packages/sqlalchemy/dialects/oracle/base.py:1334: SAWarning: Did not recognize type 'UROWID' of column*

Oracle's UROWID is a type for referring to either the logical or physical address of a row. I am reading reference tables (and thus have no control over the DB design nor can I read the actual data), one of which contains rule exceptions for specific rows across many unrelated tables (hence using UROWID since you can't FK to multiple tables). Even if this was just implemented as Binary, it would be appreciated, as I can't load this table since the UROWID column is the PK.

[Sqlalchemy-tickets] Issue #4022: Select removes duplicate columns, but Insert.from_select might want them (zzzeek/sqlalchemy)

[Stijn v. D. <iss...@bi...>]

New issue 4022: Select removes duplicate columns, but Insert.from_select might want them
https://bitbucket.org/zzzeek/sqlalchemy/issues/4022/select-removes-duplicate-columns-but

Stijn van Drongelen:

I programmatically create a bunch of (complex) `INSERT ... SELECT` statements. I regularly use the pattern

    target_columns, source_elements = *query_components
    statement = target_table.insert().from_select(
        target_columns,
        select(source_elements).select_from(source_selectable)
    )

after building `query_components` and `source_selectable` from some specification. When I (granted: inadvertedly) had a duplicate column in `source_elements`, the database complained that there was a mismatch in the numer of columns in the `INSERT ... SELECT` statement.

It took me a while to figure out what the problem was. I'm not sure what the reasoning behind it is, as having duplicate columns after `SELECT` isn't semantically wrong. If I wanted to target multiple columns with the same source column, I could of course work around it by labeling every source column, but unlike `.alias()`, `.label()` can't be called without an argument (and I'd like to leverage SQLAlchemy's ability to generate fresh names).

So, in short: simple code may end up generating broken SQL because duplicate columns get dropped by `Select`. Why does it do that, and how do I generate fresh `.label()`s if I'd want to work around that for `Insert.from_select`?

[Sqlalchemy-tickets] Issue #4021: Related objects are not reloaded when using contains_eager (zzzeek/sqlalchemy)

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

New issue 4021: Related objects are not reloaded when using contains_eager
https://bitbucket.org/zzzeek/sqlalchemy/issues/4021/related-objects-are-not-reloaded-when

Boris Serebrov:

I have a query like this (where `Author` can have many `Book`s):

```
#!python

query = session.query(Author)
query = query.join(Author.books)
query = query.filter(Book._id == book1._id)
query = query.options(contains_eager(Author.books))
author = query.one()
```

It works as expected, I get the `author` object with one related book (`book1`).

Now, if I run same query, but filter it by `book2._id`, I get the `author` object which still has `book1` in the `author.books`.

Tracing the issue through the SQLAlchemy code, I found a workaround - I can use `populate_existing()` and then it works. But it doesn't look like a good solution, I feel like it should work without it (or that there is a better workaround - is there a way to instruct SQLAlchemy that specific relation need to be reloaded?).

The complete example:

```
#!python

from sqlalchemy import __version__
from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
from sqlalchemy.orm import relationship, backref, contains_eager, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

ModelBase = declarative_base()


class Author(ModelBase):
    __tablename__ = 'authors'
    _id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String(255))


class Book(ModelBase):
    __tablename__ = 'books'
    _id = Column(Integer, primary_key=True)
    name = Column(String)
    author_id = Column(Integer, ForeignKey('authors._id'))
    author = relationship(
        'Author', backref=backref('books'))


if __name__ == "__main__":
    print('SQLAlchemy version', __version__)
    engine = create_engine("sqlite://")
    ModelBase.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()

    author = Author(name="author1")
    session.add(author)
    session.commit()

    book1 = Book(name="book_a1_1", author_id=author._id)
    session.add(book1)
    book2 = Book(name="book_a1_2", author_id=author._id)
    session.add(book2)
    session.commit()
    print('book1', book1._id)
    print('book2', book2._id)
    print()

    query = session.query(Author)
    query = query.join(Author.books)
    query = query.filter(Book._id == book1._id)
    query = query.options(contains_eager(Author.books))
    print('Expect [book1]', [x._id for x in query.one().books])
    assert query.one().books[0]._id == book1._id

    query = session.query(Author)
    # it works with populate_existing()
    # query = session.query(Author).populate_existing()
    query = query.join(Author.books)
    query = query.filter(Book._id == book2._id)
    query = query.options(contains_eager(Author.books))
    print('Expect [book2]', [x._id for x in query.one().books])
    assert query.one().books[0]._id == book2._id

```

And the output:

```
SQLAlchemy version 1.1.11
book1 1
book2 2

Expect [book1] [1]
Expect [book2] [1]
Traceback (most recent call last):
  File "contains_eager.py", line 58, in <module>
    assert query.one().books[0]._id == book2._id
AssertionError

```

The second assert fails - there is still `book1` in the related `books` property.
I reproduced this initially with version `1.0.11`, tested on `1.1.11` - it works the same.

[Sqlalchemy-tickets] Issue #4020: add base float coercion to float (zzzeek/sqlalchemy)

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

New issue 4020: add base float coercion to float
https://bitbucket.org/zzzeek/sqlalchemy/issues/4020/add-base-float-coercion-to-float

Michael Bayer:

e.g. on MySQL, and possibly others in some cases, the driver may not be able to distinguish "float" from "numeric" and even though we know we want "float", Float currently assumes no result processing:


```
#!python

from sqlalchemy import *

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

print repr(e.scalar(select([literal(4.5, Float)])))
```

output:

    Decimal('4.5')

change would be:

```
#!diff


diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index 7a3c505..1bf75c7 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -700,6 +700,8 @@ class Float(Numeric):
             return processors.to_decimal_processor_factory(
                 decimal.Decimal,
                 self._effective_decimal_return_scale)
+        elif dialect.supports_native_decimal:
+            return processors.to_float
         else:
             return None

```

the concern of course would be the extra fn call, however it does not seem to non-negligble overhead.    Oddly, the pure Python version is faster than the c processor one so we might want to consider simplfying that:


```
#!python

from sqlalchemy import processors
import timeit


def go1():
    row = []
    for fl in [3.4, 5.10, 23.235, 7.0, 18.999999]:
        row.append(fl)

proc = processors.to_float


def go2():
    row = []
    for fl in [3.4, 5.10, 23.235, 7.0, 18.999999]:
        row.append(proc(fl))

print timeit.timeit("go1", "from __main__ import go1", number=10000000)

print timeit.timeit("go2", "from __main__ import go2", number=10000000)

```

cprocessors:

    0.227154016495
    0.238603830338

non cprocessors:

    0.226067066193
    0.226836919785