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

[Sqlalchemy-tickets] Issue #4226: SQLite and JSON: AttributeError (zzzeek/sqlalchemy)

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

New issue 4226: SQLite and JSON: AttributeError
https://bitbucket.org/zzzeek/sqlalchemy/issues/4226/sqlite-and-json-attributeerror

Sebastian Rittau:

Please consider the following code:

```
#!python

from sqlalchemy import create_engine, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.types import Integer, JSON


DeclarativeBase = declarative_base()


class Foo(DeclarativeBase):

    __tablename__ = "foo"

    id = Column(Integer, primary_key=True)
    json = Column(JSON)


engine = create_engine("sqlite:///")
engine.execute("CREATE TABLE foo(id INTEGER PRIMARY KEY, json JSON)")
engine.execute("INSERT INTO foo(id, json) VALUES(123, '\"bar\"')")
session = sessionmaker(bind=engine)()
session.query(Foo).first()
```

Running this with SQLAlchemy 1.2.5 and Python 3.6.5rc1, I get the following traceback:

```
Traceback (most recent call last):
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/sql/type_api.py", line 471, in _cached_result_processor
    return dialect._type_memos[self][coltype]
  File "/usr/lib/python3.6/weakref.py", line 394, in __getitem__
    return self.data[ref(key)]
KeyError: <weakref at 0x7f7466d6f688; to 'JSON' at 0x7f746a797588>

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/srittau/.PyCharm2017.3/config/scratches/scratch_13.py", line 22, in <module>
    session.query(Foo).first()
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2789, in first
    ret = list(self[0:1])
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2581, in __getitem__
    return list(res)
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2889, in __iter__
    return self._execute_and_instances(context)
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2912, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1215, in _execute_context
    result = context.get_result_proxy()
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 1008, in get_result_proxy
    return result.ResultProxy(self)
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 649, in __init__
    self._init_metadata()
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 678, in _init_metadata
    self._metadata = ResultMetaData(self, cursor_description)
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 215, in __init__
    num_ctx_cols, cols_are_ordered, textual_ordered)
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 379, in _merge_cursor_description
    in enumerate(result_columns)
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 378, in <listcomp>
    ) for idx, (key, name, obj, type_)
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 971, in get_result_processor
    return type_._cached_result_processor(self.dialect, coltype)
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/sql/type_api.py", line 477, in _cached_result_processor
    d[coltype] = rp = d['impl'].result_processor(dialect, coltype)
  File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/sql/sqltypes.py", line 2148, in result_processor
    json_deserializer = dialect._json_deserializer or json.loads
AttributeError: 'SQLiteDialect_pysqlite' object has no attribute '_json_deserializer'
```

Line 2148 of `sqlalchemy/sql/sqltypes.py` reads:

```
        json_deserializer = dialect._json_deserializer or json.loads
```

I suspect this is supposed to read

```
        json_deserializer = dialect._json_deserializer if hasattr(dialect, "_json_deserializer") else json.loads
```

to support dialects without native JSON support.

[Sqlalchemy-tickets] Issue #4225: MySQL error 1047 should be handled as disconnect (zzzeek/sqlalchemy)

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

New issue 4225: MySQL error 1047 should be handled as disconnect
https://bitbucket.org/zzzeek/sqlalchemy/issues/4225/mysql-error-1047-should-be-handled-as

Andras Kovi:

It seems like WSREP errors can cause #3497 to reappear.

Example error: InternalError: (1047, u'WSREP has not yet prepared node for application use')

The error was produced in a HA testing environment where the Galera cluster was being being bombarded with errors. The connecting Openstack Mistral service correctly identified the disconnect errors 2013, but seems to have missed the 1047 which caused erroneous connections stay in the pool.

[Sqlalchemy-tickets] Issue #4224: Query API slice, imit, offset seem broken when using python-future datatypes on Python2 (zzzeek/sqlalchemy)

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

New issue 4224: Query API slice, imit, offset seem broken when using python-future datatypes on Python2
https://bitbucket.org/zzzeek/sqlalchemy/issues/4224/query-api-slice-imit-offset-seem-broken

dheeraj_gupta:

I am trying to port my existing Python2 compatible code to Py2/Py3 form using python-future. Most of SQLAlchemy related features work OK (atleast my tests pass), but trying to pass python-future's datatypes breaks query API's slice, limit and offset functions when using Python2.

Small code that fails when using sqlalchemy-1.1.18, future-0.16.0 on both Python2.7 and Python2.6


```
#!python

from __future__ import print_function

from sqlalchemy.orm import sessionmaker
from sqlalchemy import (Column,
                        engine_from_config,
                        Integer,
                        Text)
from sqlalchemy.ext.declarative import declarative_base

from builtins import *     # This is after installing python-future

Base = declarative_base()


class MyTest(Base):
    __table_args__ = {'mysql_charset': 'utf8', 'mysql_engine': 'InnoDB'}
    __tablename__ = "mytest"

    id_ = Column("id", Integer, primary_key=True)
    description = Column(Text)
    quantity = Column(Integer)


_ENGINE = engine_from_config({"url": "mysql+mysqldb://root:root123@localhost/test"}, prefix="")
_SESSION = sessionmaker(bind=_ENGINE)
session = _SESSION()

Base.metadata.create_all(bind=_ENGINE)

session.add(MyTest(description="foo", quantity=1))
session.add(MyTest(description="bar", quantity=2))
session.add(MyTest(description="baz", quantity=3))
```

Now using the `int` type from python-future to do something like

```
print(session.query(MyTest).slice(0,int(2)).count())
```

gives me the following traceback


```
#!python

Traceback (most recent call last):
  File "/home/test/Desktop/sqlalbug.py", line 40, in <module>
    print(session.query(MyTest).slice(0,int(2)).count())
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/orm/query.py", line 3089, in count
    return self.from_self(col).scalar()
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/orm/query.py", line 2843, in scalar
    ret = self.one()
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/orm/query.py", line 2814, in one
    ret = self.one_or_none()
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/orm/query.py", line 2784, in one_or_none
    ret = list(self)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/orm/query.py", line 2855, in __iter__
    return self._execute_and_instances(context)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/orm/query.py", line 2878, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception
    exc_info
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
    cursor.execute(statement, parameters)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
sqlalchemy.exc.ProgrammingError: (_mysql_exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2') AS anon_1' at line 4") [SQL: u'SELECT count(*) AS count_1 \nFROM (SELECT mytest.id AS mytest_id, mytest.description AS mytest_description, mytest.quantity AS mytest_quantity \nFROM mytest \n LIMIT %s) AS anon_1'] [parameters: (2,)]
```

The same traceback is there if I use `limit` or `offset` instead of `slice`

Other query methods (such as filter) seem unaffected


```
#!python

print(session.query(MyTest).filter(MyTest.quantity==1).first().description)
foo

print(session.query(MyTest).filter(MyTest.quantity==int(1)).first().description)
foo
```

The type information for `int`


```
#!python

print(type(int(1)))
<class 'future.types.newint.newint'>
```

To work-around this, I cast the modified types back into original types using the `native` function provided by future.


```
#!python

from future.utils import native
print(session.query(MyTest).slice(0,native(int(2))).count())
```
 works correctly

[Sqlalchemy-tickets] Issue #4223: TypeError when trying to connect to mysql using pyodbc (zzzeek/sqlalchemy)

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

New issue 4223: TypeError when trying to connect to mysql using pyodbc
https://bitbucket.org/zzzeek/sqlalchemy/issues/4223/typeerror-when-trying-to-connect-to-mysql

Dmitriy Shashkin:

python 3.6, 64 bit, fedora 27, sqlalchemy 1.2.5, mysql 5.7

When trying to connect to mysql using pyodbc I get the following error: TypeError: The first argument to execute must be a string or unicode query.

This problem does not exist in 1.1.18, it was introduced in 1.2.0

Code to reproduce error (requires and existing mysql database):
```
import sqlalchemy
sqlalchemy.create_engine('mysql+pyodbc://dsn').connect()
```

The text of error message:

```
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-6-2a6312fe45dd> in <module>()
----> 1 sqlalchemy.create_engine('mysql+pyodbc://replica_lan_mob_api_stats').connect()

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in connect(self, **kwargs)
   2100         """
   2101 
-> 2102         return self._connection_cls(self, **kwargs)
   2103 
   2104     def contextual_connect(self, close_with_result=False, **kwargs):

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in __init__(self, engine, connection, close_with_result, _branch_from, _execution_options, _dispatch, _has_events)
     88         else:
     89             self.__connection = connection \
---> 90                 if connection is not None else engine.raw_connection()
     91             self.__transaction = None
     92             self.__savepoint_seq = 0

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in raw_connection(self, _connection)
   2186         """
   2187         return self._wrap_pool_connect(
-> 2188             self.pool.unique_connection, _connection)
   2189 
   2190 

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _wrap_pool_connect(self, fn, connection)
   2156         dialect = self.dialect
   2157         try:
-> 2158             return fn()
   2159         except dialect.dbapi.Error as e:
   2160             if connection is None:

~/.local/lib/python3.6/site-packages/sqlalchemy/pool.py in unique_connection(self)
    343 
    344         """
--> 345         return _ConnectionFairy._checkout(self)
    346 
    347     def _create_connection(self):

~/.local/lib/python3.6/site-packages/sqlalchemy/pool.py in _checkout(cls, pool, threadconns, fairy)
    782     def _checkout(cls, pool, threadconns=None, fairy=None):
    783         if not fairy:
--> 784             fairy = _ConnectionRecord.checkout(pool)
    785 
    786             fairy._pool = pool

~/.local/lib/python3.6/site-packages/sqlalchemy/pool.py in checkout(cls, pool)
    530     @classmethod
    531     def checkout(cls, pool):
--> 532         rec = pool._do_get()
    533         try:
    534             dbapi_connection = rec.get_connection()

~/.local/lib/python3.6/site-packages/sqlalchemy/pool.py in _do_get(self)
   1187             except:
   1188                 with util.safe_reraise():
-> 1189                     self._dec_overflow()
   1190         else:
   1191             return self._do_get()

~/.local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback)
     64             self._exc_info = None   # remove potential circular references
     65             if not self.warn_only:
---> 66                 compat.reraise(exc_type, exc_value, exc_tb)
     67         else:
     68             if not compat.py3k and self._exc_info and self._exc_info[1]:

~/.local/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    185         if value.__traceback__ is not tb:
    186             raise value.with_traceback(tb)
--> 187         raise value
    188 
    189 else:

~/.local/lib/python3.6/site-packages/sqlalchemy/pool.py in _do_get(self)
   1184         if self._inc_overflow():
   1185             try:
-> 1186                 return self._create_connection()
   1187             except:
   1188                 with util.safe_reraise():

~/.local/lib/python3.6/site-packages/sqlalchemy/pool.py in _create_connection(self)
    348         """Called by subclasses to create a new ConnectionRecord."""
    349 
--> 350         return _ConnectionRecord(self)
    351 
    352     def _invalidate(self, connection, exception=None, _checkin=True):

~/.local/lib/python3.6/site-packages/sqlalchemy/pool.py in __init__(self, pool, connect)
    475         self.__pool = pool
    476         if connect:
--> 477             self.__connect(first_connect_check=True)
    478         self.finalize_callback = deque()
    479 

~/.local/lib/python3.6/site-packages/sqlalchemy/pool.py in __connect(self, first_connect_check)
    675                 pool.dispatch.first_connect.\
    676                     for_modify(pool.dispatch).\
--> 677                     exec_once(self.connection, self)
    678             if pool.dispatch.connect:
    679                 pool.dispatch.connect(self.connection, self)

~/.local/lib/python3.6/site-packages/sqlalchemy/event/attr.py in exec_once(self, *args, **kw)
    272                 if not self._exec_once:
    273                     try:
--> 274                         self(*args, **kw)
    275                     finally:
    276                         self._exec_once = True

~/.local/lib/python3.6/site-packages/sqlalchemy/event/attr.py in __call__(self, *args, **kw)
    282             fn(*args, **kw)
    283         for fn in self.listeners:
--> 284             fn(*args, **kw)
    285 
    286     def __len__(self):

~/.local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py in go(*arg, **kw)
   1332         if once:
   1333             once_fn = once.pop()
-> 1334             return once_fn(*arg, **kw)
   1335 
   1336     return go

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py in first_connect(dbapi_connection, connection_record)
    181                                     _has_events=False)
    182                 c._execution_options = util.immutabledict()
--> 183                 dialect.initialize(c)
    184             event.listen(pool, 'first_connect', first_connect, once=True)
    185 

~/.local/lib/python3.6/site-packages/sqlalchemy/dialects/mysql/base.py in initialize(self, connection)
   1902                     "CHECK constraints, which impact handling of NULL values "
   1903                     "with SQLAlchemy's boolean datatype (MDEV-13596). An "
-> 1904                     "additional issue prevents proper migrations of columns "
   1905                     "with CHECK constraints (MDEV-11114).  Please upgrade to "
   1906                     "MariaDB 10.2.9 or greater, or use the MariaDB 10.1 "

~/.local/lib/python3.6/site-packages/sqlalchemy/dialects/mysql/pyodbc.py in _detect_charset(self, connection)
     59         # If it's decided that issuing that sort of SQL leaves you SOL, then
     60         # this can prefer the driver value.
---> 61         rs = connection.execute("SHOW VARIABLES LIKE 'character_set%%'")
     62         opts = {row[0]: row[1] for row in self._compat_fetchall(rs)}
     63         for key in ('character_set_connection', 'character_set'):

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in execute(self, object, *multiparams, **params)
    940         """
    941         if isinstance(object, util.string_types[0]):
--> 942             return self._execute_text(object, multiparams, params)
    943         try:
    944             meth = object._execute_on_connection

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_text(self, statement, multiparams, params)
   1102             statement,
   1103             parameters,
-> 1104             statement, parameters
   1105         )
   1106         if self._has_events or self.engine._has_events:

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1198                 parameters,
   1199                 cursor,
-> 1200                 context)
   1201 
   1202         if self._has_events or self.engine._has_events:

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1414                 )
   1415             else:
-> 1416                 util.reraise(*exc_info)
   1417 
   1418         finally:

~/.local/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    185         if value.__traceback__ is not tb:
    186             raise value.with_traceback(tb)
--> 187         raise value
    188 
    189 else:

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1191                         statement,
   1192                         parameters,
-> 1193                         context)
   1194         except BaseException as e:
   1195             self._handle_dbapi_exception(

~/.local/lib/python3.6/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    505 
    506     def do_execute(self, cursor, statement, parameters, context=None):
--> 507         cursor.execute(statement, parameters)
    508 
    509     def do_execute_no_params(self, cursor, statement, context=None):

TypeError: The first argument to execute must be a string or unicode query.
```

[Sqlalchemy-tickets] Issue #4222: Query yields different result via SQLA than via mysql command line client (zzzeek/sqlalchemy)

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

New issue 4222: Query yields different result via SQLA than via mysql command line client
https://bitbucket.org/zzzeek/sqlalchemy/issues/4222/query-yields-different-result-via-sqla

dwt:

Hi there,

I have to admit I'm really not sure where this bug is - but at least I could simmer it down to this reproduction that might be helpful to fix it.

First some context: I recently wrote a migration that had a query like this:

```sql
select seq, replace(uuid(), "-", "") from seq_0_to_9
```

which works beautifully from the mysql (mariadb actually) command line client, in that it returns a sequence of UUIDs that are all different.

However, when executed through sqlalchemy, this query returns the same UUID for each result line. 

Here's my reduction:

```python
#!/usr/bin/env python

"""
Versions:
mysql  Ver 15.1 Distrib 10.2.13-MariaDB, for osx10.13 (x86_64) using readline 5.1
PyMySQL==0.8.0
SQLAlchemy==1.2.5
"""

import sqlalchemy as sa
from sqlalchemy import sql
from pprint import pprint

engine = sa.create_engine('mysql+pymysql://test:test@localhost/test?charset=utf8mb4', echo=True)

pprint(engine.execute('select seq, uuid() from seq_0_to_9').fetchall())
pprint(engine.execute('select seq, replace(uuid(), "-", "") from seq_0_to_9').fetchall())

"""
The two queries produce different output, in that the second query 
returns one and the same UUID for each row, while the first returns 
a different UUID for each row.

To the best of my understanding the two queries should return a 
different UUID for each row - and that is also the behaviour I get 
when I do this from the mysql command line client.
"""
```

[Sqlalchemy-tickets] Issue #4221: non-mapped declared_attr warning gets in the way; is of questionable value (zzzeek/sqlalchemy)

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

New issue 4221: non-mapped declared_attr warning gets in the way; is of questionable value
https://bitbucket.org/zzzeek/sqlalchemy/issues/4221/non-mapped-declared_attr-warning-gets-in

Michael Bayer:

at http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/mixins.html#combining-table-mapper-arguments-from-multiple-mixins we document invoking `__table_args__` from a mixin.  if `@declared_attr` is used we get a warning:

```
#!python


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

Base = declarative_base()


class Mixin(object):
    a = Column(Integer)

    @declared_attr
    def __table_args__(cls):
        return (UniqueConstraint("a"), )


class Baz(Base, Mixin):
    __tablename__ = 'baz'

    id = Column(Integer, primary_key=True)
    d = Column(Integer)

    @declared_attr
    def __table_args__(cls):
        ret = list(Mixin.__table_args__)
        ret.append(UniqueConstraint("d"))
        return tuple(ret)
```

    SAWarning: Unmanaged access of declarative attribute __table_args__ from non-mapped class Mixin

the thing we're guarding against here is the case in https://bitbucket.org/zzzeek/sqlalchemy/issues/3149/mixin_column-to-provide-more-context-for, which is already prevented here.

not clear what additional case we are guarding against here however we could also limit the warning only for names that aren't `__table_args__`, `__mapper_args__`, etc.

[Sqlalchemy-tickets] Issue #4220: Wrong `bindparam()` call signature in "Specifying Bound Parameter Behaviors" (zzzeek/sqlalchemy)

[Ilja E. <iss...@bi...>]

New issue 4220: Wrong `bindparam()` call signature in "Specifying Bound Parameter Behaviors"
https://bitbucket.org/zzzeek/sqlalchemy/issues/4220/wrong-bindparam-call-signature-in

Ilja Everilä:

In ["Specifying Bound Parameter Behaviors"](http://docs.sqlalchemy.org/en/latest/core/tutorial.html#specifying-bound-parameter-behaviors) there is:

> The parameters can also be explicitly typed:
>
>     stmt = stmt.bindparams(bindparam("x", String), bindparam("y", String))

but the call signature of [`bindparam()`](http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.bindparam) is:

    sqlalchemy.sql.expression.bindparam(key, value=symbol('NO_ARG'), type_=None, ...)

Hence the example in the documentation would end up passing the type as the `value` argument. The error is not apparent given the example from the documentation, since the values are overridden when executing the statement, but will become apparent, if not:

```
#!python

>>> stmt = text("SELECT * FROM users WHERE users.name BETWEEN :x AND :y")
>>> stmt = stmt.bindparams(bindparam("x", String), bindparam("y", String))
>>> result = conn.execute(stmt)
Traceback (most recent call last):
  File "/home/saarni/Work/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/saarni/Work/sqlalchemy/lib/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

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

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/saarni/Work/sqlalchemy/lib/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/home/saarni/Work/sqlalchemy/lib/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/saarni/Work/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/saarni/Work/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/home/saarni/Work/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/home/saarni/Work/sqlalchemy/lib/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/saarni/Work/sqlalchemy/lib/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/home/saarni/Work/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/saarni/Work/sqlalchemy/lib/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.InterfaceError: (sqlite3.InterfaceError) Error binding parameter 0 - probably unsupported type. [SQL: 'SELECT * FROM users WHERE users.name BETWEEN ? AND ?'] [parameters: (<class 'sqlalchemy.sql.sqltypes.String'>, <class 'sqlalchemy.sql.sqltypes.String'>)] (Background on this error at: http://sqlalche.me/e/rvf5)
```

The example should be:

    stmt = stmt.bindparams(bindparam("x", type_=String), bindparam("y", type_=String))

[Sqlalchemy-tickets] Issue #4219: autoload existing mysql table throws Unknown schema content (zzzeek/sqlalchemy)

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

New issue 4219: autoload existing mysql table throws Unknown schema content
https://bitbucket.org/zzzeek/sqlalchemy/issues/4219/autoload-existing-mysql-table-throws

LiQuan:

My existing table created with following sql
```
CREATE TABLE `fx_orders_items` (
  .....
  FULLTEXT KEY `ix_fulltext_oi_g_name` (`oi_g_name`) /*!50100 WITH PARSER `ngram` */ 
) ENGINE=InnoDB AUTO_INCREMENT=100018857 DEFAULT CHARSET=utf8 COMMENT=''
```
when I use autoload like this
```
Table("fx_orders_items", metadata, autoload=True)
```

it throws error
```
C:\Python27\lib\site-packages\sqlalchemy\dialects\mysql\reflection.py:57: SAWarning: Unknown schema content: u'  FULLTEXT KEY `ix_fulltext_oi_g_name` (`oi_g_name`) /*!50100 WITH PARSER `ngram` */ '
  util.warn("Unknown schema content: %r" % line)
```

I just debug it and found the line 
```
FULLTEXT KEY `ix_fulltext_oi_g_name` (`oi_g_name`) /*!50100 WITH PARSER `ngram` */ 
```
didn't go into any of this and return None
```
def _parse_constraints(self, line):
        """Parse a KEY or CONSTRAINT line.

        :param line: A line of SHOW CREATE TABLE output
        """
        m = self._re_key.match(line)
        m = self._re_fk_constraint.match(line)
        m = self._re_ck_constraint.match(line)
        m = self._re_partition.match(line)

        # No match.
        return (None, line)
```

[Sqlalchemy-tickets] Issue #4218: is_active remains True after a database exception is thrown (zzzeek/sqlalchemy)

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

New issue 4218: is_active remains True after a database exception is thrown
https://bitbucket.org/zzzeek/sqlalchemy/issues/4218/is_active-remains-true-after-a-database

Christophe Biocca:

is_active should be False when a session is in a "partial rollback" state. The test case attached demonstrates that after catching an exception (one that should put the database in a "partial rollback" state AFAICT), session.is_active is still True.

The kind of db exception doesn't seem to make a difference (IntegrityError was the original case I ran into).

Postgres 10.3
Psycopg2 2.7.4
SQLAlchemy 1.2.5

[Sqlalchemy-tickets] Issue #4217: Can't pickle psycopg2.extensions objects (zzzeek/sqlalchemy)

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

New issue 4217: Can't pickle psycopg2.extensions objects
https://bitbucket.org/zzzeek/sqlalchemy/issues/4217/cant-pickle-psycopg2extensions-objects

Marat Sharafutdinov:

I'm using Python 3.6.4, SQLAlchemy 1.2.5 (psycopg2 2.7.4 as adapter) and PostgreSQL 10.3.

Some exceptions are raised with `psycopg2.extensions` objects included. But I can't pickle these exceptions due to impossibility of pickling such objects.

Listing `test_pg.py`:
```
#!python

import pickle

import sqlalchemy as sa

url = 'postgresql://user:pass@host/test_db'
engine = sa.create_engine(url, echo=True)
connection = engine.connect()
metadata = sa.MetaData()
table = sa.Table('test_table', metadata,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('data', sa.LargeBinary),
)
metadata.create_all(engine)

try:
    engine.execute(table.insert(), {'id': 'invalid_integer', 'data': b'test'})
except Exception as exc:
    pickle.dumps(exc)
```
Output:
```
Traceback (most recent call last):
  File "test_pg.py", line 18, in <module>
    pickle.dumps(exc)
TypeError: can't pickle psycopg2.extensions.Binary objects
```

Refs to https://github.com/psycopg/psycopg2/issues/688.

[Sqlalchemy-tickets] Issue #4216: MySQL-specific binary warnings (zzzeek/sqlalchemy)

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

New issue 4216: MySQL-specific binary warnings
https://bitbucket.org/zzzeek/sqlalchemy/issues/4216/mysql-specific-binary-warnings

Michael Bayer:

over at https://github.com/PyMySQL/PyMySQL/issues/644 we are starting to deal with the warning that MySQL 5.6/5.7 now render, which is unlikely to be resolved by MySQL and additionally may not be resolvable by drivers either.  this issue would require #3981 to implement correctly since this would be a dialect-specific bind expression.

[Sqlalchemy-tickets] Issue #4215: AttributeError for `Mutable.associate_with` when using a relationship to non primary mapper (zzzeek/sqlalchemy)

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

New issue 4215: AttributeError for `Mutable.associate_with` when using a relationship to non primary mapper
https://bitbucket.org/zzzeek/sqlalchemy/issues/4215/attributeerror-for-mutableassociate_with

David McDonald:

### Outline of the problem ###
I have several models that have JSON fields. I also have a relationship to non primary mapper as following the SQLAlchemy docs.

I tried to introduce mutability tracking to my JSON fields using https://github.com/edelooff/sqlalchemy-json. I ran into a problem with using `associate_with`. 

I've stripped back my code to remove the use of `sqlalchemy-json` and the problem still remains just when using `sqlalchemy.ext.mutable.Mutable`. `listen_for_type` seems to assume that all `mapper.column_attrs` are attributes existing on the original class.

I believe this is a bug but feel free to point out if I am doing something incorrect.

### Requirements ###
```
Python 3.6.4
Postgres 9.5.3
SQLAlchemy 1.2.5
psycopg2 2.7.4
```

### Code example ###
A full working example is available on Github for easy running - https://github.com/idavidmcdonald/sqlalchemy-bug-example

```
from sqlalchemy import Column, Integer, ForeignKey, join, create_engine
from sqlalchemy.dialects.postgresql import JSON
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.mutable import Mutable
from sqlalchemy.orm import mapper, relationship


# The following example is taken from the docs regarding a non primary relationship mapper
# http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper
Base = declarative_base()

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    b_id = Column(ForeignKey('b.id'))
    a_json = Column(JSON)

class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    b_json = Column(JSON)

class C(Base):
    __tablename__ = 'c'

    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))
    c_json = Column(JSON)

class D(Base):
    __tablename__ = 'd'

    id = Column(Integer, primary_key=True)
    c_id = Column(ForeignKey('c.id'))
    b_id = Column(ForeignKey('b.id'))
    d_json = Column(JSON)

# 1. set up the join() as a variable, so we can refer
# to it in the mapping multiple times.
j = join(B, D, D.b_id == B.id).join(C, C.id == D.c_id)

# 2. Create a new mapper() to B, with non_primary=True.
# Columns in the join with the same name must be
# disambiguated within the mapping, using named properties.
B_viacd = mapper(B, j, non_primary=True, properties={
    "b_id": [j.c.b_id, j.c.d_b_id],
    "d_id": j.c.d_id
    })

A.b = relationship(B_viacd, primaryjoin=A.b_id == B_viacd.c.b_id)

# Create DB
engine = create_engine('postgresql://localhost/test_db')
Base.metadata.create_all(engine)

# If we associate `Mutable` with `JSON` then `A` can not be instantiated succesfully
Mutable.associate_with(JSON)
item1 = A()
```

### Stack trace ###
```
Traceback (most recent call last):
  File "example.py", line 62, in <module>
    item1 = A()
  File "<string>", line 2, in __init__
  File "/Users/davidmcdonald/sqlalchemy-bug-example/venv/lib/python3.6/site-packages/sqlalchemy/orm/instrumentation.py", line 391, in _new_state_if_none
    state = self._state_constructor(instance, self)
  File "/Users/davidmcdonald/sqlalchemy-bug-example/venv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 767, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/Users/davidmcdonald/sqlalchemy-bug-example/venv/lib/python3.6/site-packages/sqlalchemy/orm/instrumentation.py", line 221, in _state_constructor
    self.dispatch.first_init(self, self.class_)
  File "/Users/davidmcdonald/sqlalchemy-bug-example/venv/lib/python3.6/site-packages/sqlalchemy/event/attr.py", line 284, in __call__
    fn(*args, **kw)
  File "/Users/davidmcdonald/sqlalchemy-bug-example/venv/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py", line 3139, in _event_on_first_init
    configure_mappers()
  File "/Users/davidmcdonald/sqlalchemy-bug-example/venv/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py", line 3032, in configure_mappers
    mapper, mapper.class_)
  File "/Users/davidmcdonald/sqlalchemy-bug-example/venv/lib/python3.6/site-packages/sqlalchemy/event/attr.py", line 246, in __call__
    fn(*args, **kw)
  File "/Users/davidmcdonald/sqlalchemy-bug-example/venv/lib/python3.6/site-packages/sqlalchemy/orm/events.py", line 621, in wrap
    fn(*arg, **kw)
  File "/Users/davidmcdonald/sqlalchemy-bug-example/venv/lib/python3.6/site-packages/sqlalchemy/ext/mutable.py", line 571, in listen_for_type
    cls.associate_with_attribute(getattr(class_, prop.key))
AttributeError: type object 'B' has no attribute 'd_json'
```

[Sqlalchemy-tickets] Issue #4193: versioning logic fails for mapping against a select stmt, 1.2 regression (zzzeek/sqlalchemy)

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

New issue 4193: versioning logic fails for mapping against a select stmt, 1.2 regression
https://bitbucket.org/zzzeek/sqlalchemy/issues/4193/versioning-logic-fails-for-mapping-against

Michael Bayer:

```
#!python

class VersioningMappedSelectTest(fixtures.MappedTest):

    __backend__ = True

    @classmethod
    def define_tables(cls, metadata):
        Table('version_table', metadata,
              Column('id', Integer, primary_key=True,
                     test_needs_autoincrement=True),
              Column('version_id', Integer, nullable=False),
              Column('value', String(40), nullable=False))

    @classmethod
    def setup_classes(cls):
        class Foo(cls.Basic):
            pass

    def _fixture(self):
        Foo, version_table = self.classes.Foo, self.tables.version_table

        current = version_table.select().\
            where(version_table.c.id > 0).alias('current_table')

        mapper(Foo, current, version_id_col=version_table.c.version_id)
        s1 = Session()
        return s1

    @testing.emits_warning(r".*versioning cannot be verified")
    def test_multiple_updates(self):
        Foo = self.classes.Foo

        s1 = self._fixture()
        f1 = Foo(value='f1')
        f2 = Foo(value='f2')
        s1.add_all((f1, f2))
        s1.commit()

        f1.value = 'f1rev2'
        f2.value = 'f2rev2'
        s1.commit()

        eq_(
            s1.query(Foo.id, Foo.value, Foo.version_id).order_by(Foo.id).all(),
            [(f1.id, 'f1rev2', 2), (f2.id, 'f2rev2', 2)]
        )
```

result:


```
#!

Traceback (most recent call last):
  File "/home/classic/dev/sqlalchemy/test/orm/test_versioning.py", line 1682, in test_multiple_updates
    s1.commit()
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/orm/session.py", line 943, in commit
    self.transaction.commit()
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/orm/session.py", line 467, in commit
    self._prepare_impl()
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/orm/session.py", line 447, in _prepare_impl
    self.session.flush()
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/orm/session.py", line 2243, in flush
    self._flush(objects)
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/orm/session.py", line 2369, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/orm/session.py", line 2333, in _flush
    flush_context.execute()
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/orm/unitofwork.py", line 391, in execute
    rec.execute(self)
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/orm/unitofwork.py", line 556, in execute
    uow
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/orm/persistence.py", line 193, in save_obj
    update_version_id in states_to_update
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/orm/persistence.py", line 1132, in _finalize_insert_update_commands
    if state_dict[mapper._version_id_prop.key] is None:
KeyError: 'version_id'

```

[Sqlalchemy-tickets] Issue #4195: can't use version_id_generator=False with select-mapped class (zzzeek/sqlalchemy)

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

New issue 4195: can't use version_id_generator=False with select-mapped class
https://bitbucket.org/zzzeek/sqlalchemy/issues/4195/cant-use-version_id_generator-false-with

Michael Bayer:

relies upon #4193, see also #4194 for notes


```
#!python

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


Base = declarative_base()


version_table = Table(
    'version_table', Base.metadata,
    Column('id', Integer, primary_key=True),
    Column('version_id', Integer, nullable=False),
    Column('value', String(40), nullable=False))

current = version_table.select().\
    where(version_table.c.id > 0).alias('current_table')


class Versioned(Base):
    __table__ = current

    __mapper_args__ = {
        # inserts assuming we fixed #4193
        "version_id_col": version_table.c.version_id,
        "version_id_generator": False
    }

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

s = Session(e)
v1 = Versioned(value='x', version_id=1)
s.add(v1)

s.flush()

# comment this out for it to pass
s.expire_all()

# we get here

v1.value = 'y'
v1.version_id = 2

# fails
s.flush()


```

might be for 1.3

[Sqlalchemy-tickets] Issue #4194: can't specify select-bound versioning column (zzzeek/sqlalchemy)

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

New issue 4194: can't specify select-bound versioning column
https://bitbucket.org/zzzeek/sqlalchemy/issues/4194/cant-specify-select-bound-versioning

Michael Bayer:

this is dependent on #4193 being resolved

```
#!python


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


Base = declarative_base()


version_table = Table(
    'version_table', Base.metadata,
    Column('id', Integer, primary_key=True),
    Column('version_id', Integer, nullable=False),
    Column('value', String(40), nullable=False))

current = version_table.select().\
    where(version_table.c.id > 0).alias('current_table')


class Versioned(Base):
    __table__ = current

    __mapper_args__ = {
        # won't insert,
        # "version_id_col": current.c.version_id

        # inserts assuming we fixed #4193, but then expires the column
        "version_id_col": version_table.c.version_id
    }

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

s = Session(e)
v1 = Versioned(value='x')
s.add(v1)
s.flush()

print("-------------------")
# emits SQL if we mapped to table-bound column
print(v1.version_id)
```

this is because:


```
#!diff

diff --git a/lib/sqlalchemy/orm/persistence.py b/lib/sqlalchemy/orm/persistence.py
index dc0ae1c38..07fb50d6e 100644
--- a/lib/sqlalchemy/orm/persistence.py
+++ b/lib/sqlalchemy/orm/persistence.py
@@ -429,6 +429,10 @@ def _collect_insert_commands(
         else:
             has_all_defaults = has_all_pks = True
 
+        # HERE vvvvvvvvvvvvv
+        # version_id_col needs to be the table bound column.
+        # the only way it could "work" the other way would be if we maintain
+        # corresponding column and all that.
         if mapper.version_id_generator is not False \
                 and mapper.version_id_col is not None and \
                 mapper.version_id_col in mapper._cols_by_table[table]:
@@ -1081,7 +1085,6 @@ def _finalize_insert_update_commands(base_mapper, uowtransaction, states):
 
     """
     for state, state_dict, mapper, connection, has_identity in states:
-
         if mapper._readonly_props:
             readonly = state.unmodified_intersection(
                 [

```

[Sqlalchemy-tickets] Issue #4192: Fix references to utf8 in mysql documentation to utf8mb4 instead (zzzeek/sqlalchemy)

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

New issue 4192: Fix references to utf8 in mysql documentation to utf8mb4 instead
https://bitbucket.org/zzzeek/sqlalchemy/issues/4192/fix-references-to-utf8-in-mysql

dwt:

Hi there,

it is a sadly not so well known shortcoming that mysql utf8 is actually just a subset of utf8 (they also call it utf8mb3) that can only encode up to 3 bytes of utf8 into the database. (Supposedly this was added for performance reasons at some point in the past).

Well, we are stuck with it now, and as a user you will definitely _not_ want to use utf8 as an encoding in mysql.

So here is the bug report: in the documentation, for example here: http://docs.sqlalchemy.org/en/latest/dialects/mysql.html?highlight=mysql utf8 is casually used as an example, and I think you should use utf8mb4 instead. Perhaps even with a note as to why utf8 is bad. I haven't prepped through the documentation, but I suspect this is used in more places.

[Sqlalchemy-tickets] Issue #4190: AttributeError when removing an event in SQLAlchemy 1.2.3 (zzzeek/sqlalchemy)

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

New issue 4190: AttributeError when removing an event in SQLAlchemy 1.2.3
https://bitbucket.org/zzzeek/sqlalchemy/issues/4190/attributeerror-when-removing-an-event-in

href:

When removing a registered Event on the Engine class in SQLAlchemy 1.2.3 I get an AttributeError.

Code:
```
#!python
from sqlalchemy import event
from sqlalchemy.engine import Engine

@event.listens_for(Engine, 'after_cursor_execute')
def after_cursor_execute(*args, **kwargs):
    pass

event.remove(Engine, 'after_cursor_execute', after_cursor_execute)
```
Setup:
```
#!bash
python3.6 -m venv test
cd test
bin/pip install sqlalchemy
bin/python test.py
```
Stacktrace:
```
#!python

Traceback (most recent call last):
  File "test.py", line 8, in <module>
    event.remove(Engine, 'after_cursor_execute', after_cursor_execute)
  File "/Users/denis/.virtualenvs/onegov/lib/python3.6/site-packages/sqlalchemy/event/api.py", line 178, in remove
    _event_key(target, identifier, fn).remove()
  File "/Users/denis/.virtualenvs/onegov/lib/python3.6/site-packages/sqlalchemy/event/registry.py", line 220, in remove
    collection.remove(self.with_wrapper(listener_fn))
  File "/Users/denis/.virtualenvs/onegov/lib/python3.6/site-packages/sqlalchemy/event/attr.py", line 167, in remove
    self._clslevel[cls].remove(event_key._listen_fn)
AttributeError: '_empty_collection' object has no attribute 'remove'
```
Only SQLAlchemy 1.2.3 seems to be affected. 1.2.2 works. I'm using Python 3.6 on MacOS High Sierra. I use SQLAlchemy with Psycopg2.7.4 and Postgres 10.1, though from what I can tell that is not relevant here.

[Sqlalchemy-tickets] Issue #4199: selectin polymorphic hitting expanding in w/ no entries (zzzeek/sqlalchemy)

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

New issue 4199: selectin polymorphic hitting expanding in w/ no entries
https://bitbucket.org/zzzeek/sqlalchemy/issues/4199/selectin-polymorphic-hitting-expanding-in

Michael Bayer:

```
#!python

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

Base = declarative_base()


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

    __mapper_args__ = {
        'polymorphic_on': type,
    }


class A1(A):
    __tablename__ = 'a1'
    id = Column(Integer, ForeignKey('a.id'), primary_key=True)
    __mapper_args__ = {
        'polymorphic_identity': 'a1',
        'polymorphic_load': 'selectin',
    }


class A2(A):
    __tablename__ = 'a2'
    id = Column(Integer, ForeignKey('a.id'), primary_key=True)
    __mapper_args__ = {
        'polymorphic_identity': 'a2',
        'polymorphic_load': 'selectin',
    }


class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_list = relationship('A')


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

s = Session(e)

b = B(a_list=[A1(), A2()])
s.add(b)
x = b.a_list[0]

# uncomment for pass
#y = b.a_list[1]

s.commit()  # L3

print(b.a_list)  # crashes
```


```
#!python

File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/default.py", line 736, in _expand_in_parameters
    "'expanding' parameters can't be used with an "
sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) 'expanding' parameters can't be used with an empty list [SQL: 'SELECT a1.id AS a1_id, a.id AS a_id, a.type AS a_type \nFROM a JOIN a1 ON a.id = a1.id \nWHERE a.id IN ([EXPANDING_primary_keys]) ORDER BY a.id'] [parameters: [{'primary_keys': []}]]

```

[Sqlalchemy-tickets] Issue #4198: Expanding bindparam does not support Enums (zzzeek/sqlalchemy)

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

New issue 4198: Expanding bindparam does not support Enums
https://bitbucket.org/zzzeek/sqlalchemy/issues/4198/expanding-bindparam-does-not-support-enums

Robert Ditthardt:

I'm assuming there are just missing features here because experimental. That said...

Attached is a test file that demonstrates the observed behavior.

I think that it should be unnecessary to need to get the "name" attribute of an enumeration for "in_" queries that require bound parameters that are enumerations. It should be sufficient to just pass in the enumeration as it is when the Table is properly configured.

I should also note that the mysql driver has slightly different behavior. It doesn't throw an exception, but instead just silently generates a bad query, where each enum in the raw sql ends up as "TestEnum.FOO" instead of "FOO".

[Sqlalchemy-tickets] Issue #4197: quoting for CTE names (zzzeek/sqlalchemy)

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

New issue 4197: quoting for CTE names
https://bitbucket.org/zzzeek/sqlalchemy/issues/4197/quoting-for-cte-names

Michael Bayer:

```
#!python

 def test_named_alias_no_quote(self):
        cte = select([literal(1).label("id")]).cte(name='CTE')

        s1 = select([cte.c.id]).alias(name="no_quotes")

        s = select([s1])
        self.assert_compile(
            s,
            'WITH "CTE" AS (SELECT :param_1 AS id) '
            'SELECT no_quotes.id FROM '
            '(SELECT "CTE".id AS id FROM "CTE") AS no_quotes'
        )

    def test_named_alias_quote(self):
        cte = select([literal(1).label("id")]).cte(name='CTE')

        s1 = select([cte.c.id]).alias(name="Quotes Required")

        s = select([s1])
        self.assert_compile(
            s,
            'WITH "CTE" AS (SELECT :param_1 AS id) '
            'SELECT "Quotes Required".id FROM '
            '(SELECT "CTE".id AS id FROM "CTE") AS "Quotes Required"'
        )
```

[Sqlalchemy-tickets] Issue #4196: support sharded query for bulk update/delete methods (zzzeek/sqlalchemy)

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

New issue 4196: support sharded query for bulk update/delete methods
https://bitbucket.org/zzzeek/sqlalchemy/issues/4196/support-sharded-query-for-bulk-update

Michael Bayer:

ongoing effort at https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/656/.    
this needs to include tests with the sharded query itself, including all three fetch methods.

additional issue:  

> Got a problem when synchronize_session='fetch'. In that case used BulkFetch._do_pre_synchronize (as you know :)). There is query instance with set `_bind_id` attribute, but it don't passed through `execute`.

> Here is an example that can illustrate that problem: https://gist.github.com/aCLr/a992ca92138aeee86bf9432693be6d6c
It contains Query and Session implementations, which used in my project: MultiBoundQuery and MultiBoundSession.
Also, it contains `delete` and `update` execution and checking for update and delete operation. Tracebacks included.

[Sqlalchemy-tickets] Issue #4191: Why does SQLAlchemy not update relations after object deletion? (zzzeek/sqlalchemy)

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

New issue 4191: Why does SQLAlchemy not update relations after object deletion?
https://bitbucket.org/zzzeek/sqlalchemy/issues/4191/why-does-sqlalchemy-not-update-relations

Antoine Lizée:

## Problem

Within the same session, after deletion of an object, the relation attributes of other objects in the session that included or pointed to this deleted object are not updated. In short: 

    sesion.add(a, b)
    a.parent = b
    print(a.parent)  # b
    session.delete(b)
    print(a.parent)  # b

The complete, reproducible examples are below.

### Material

I have read at length SQLA doc, including:

- the misleading part on ['deleting from collections'](http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#deleting-from-collections). Misleading becasue it makes you think this lack of update after deletion is restricted to collections. The example below shows it's not.
- the part on the [cascades](http://docs.sqlalchemy.org/en/latest/orm/cascades.html), and especially the 'delete-orphan'. This is *not* what I am looking for since I do not want all removal of relation to delete the related object.

### Potential solution

Individual objects and even single object attributes can be manually set to be reloaded using `session.expire()`. I thought the ORM was responsible for doing this job :-)

### Questions

- Have I missed a simple solution?
- Have I missed a place in the doc where it's explicitly written and explained? (I spent quite some time trying to narrow down the problem)
- Is there a reason why SQLAlchemy doesn't / cannot mark for update the relations tied to an object that is removed?

## Code Example

Version: `SQLAlchemy==1.1.2`
DB: All DBs, example with sqlite.

### Setup

   
```
#!python

from sqlalchemy import create_engine, Table, Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref, sessionmaker
    
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

class Country(Base):
    __tablename__ = 'countries'
    
    id = Column(Integer(), primary_key=True)
    name = Column(String(255))
    
    def __repr__(self):
        return '<Country {} [{}]>'.format(self.name, self.id)
    
    
class Capital(Base):
    __tablename__ = 'capitals'

    id = Column(Integer(), primary_key=True)
    name = Column(String(255))
    country_id = Column(Integer(), ForeignKey(Country.id), unique=True)
    country = relationship('Country', backref=backref('capital', uselist=False))
    
    def __repr__(self):
        return '<Capital {} [{}]>'.format(self.name, self.id)
    
    
Base.metadata.create_all(engine)
```


### Tests
    
    #!python
    # Creating both objects
    us = Country(name="USA")
    ny = Capital(name="NYC")
    session.add(us)
    session.add(ny)
    session.commit()
    
    print("\n### 1. Creating the relation:")
    # Loading relations:
    print("## us.capital: ", us.capital)
    print("## ny.country: ", ny.country)
    # Creating the relation:
    us.capital = ny
    # Checking that it's upated on the other side:
    print("## ny.country (after rel creation): ", ny.country)
    # Saving
    session.commit()
    
    
    print("\n### 2. Deleting relation:")
    # Loading relations:
    print("## us.capital: ", us.capital)
    print("## ny.country: ", ny.country)
    # Deleting one object of the relation:
    us.capital = None
    # The relation from the other side are updated accordingly
    print("## ny.country (after rel deletion): ", ny.country)
    # Rolling back
    session.rollback()
    
    
    print("\n### 3. Deleting one object:")
    # Loading relations:
    print("## us.capital: ", us.capital)
    print("## ny.country: ", ny.country)
    # Deleting one object of the relation:
    session.delete(us)
    # The relations are not updated!
    print("## ny.country (after deletion of us): ", ny.country)
    # Flushing doesn't change anything (undersantably so)
    session.flush()
    print("## ny.country (+ flush): ", ny.country)
    # Expiring manually
    session.expire(ny, ['country'])
    # Looks okay
    print("## ny.country (+ expire): ", ny.country)
    # Rolling back
    session.rollback()
    
    
    print("\n### 4. Deleting the other object:")
    # Loading relations:
    print("## us.capital: ", us.capital)
    print("## ny.country: ", ny.country)
    # Deleting one object of the relation:
    session.delete(ny)
    # The relations are not updated!
    print("## us.capital (after deletion of ny): ", us.capital)
    # Flushing doesn't change anything (undersantably so)
    session.flush()
    print("## us.capital (+ flush): ", us.capital)
    # Expiring manually
    session.expire(us, ['capital'])
    # Looks okay
    print("## us.capital (+ expire): ", us.capital)
    # Rolling back
    session.rollback()

### Results

    ### 1. Creating the relation:
    ## us.capital:  None
    ## ny.country:  None
    ## ny.country (after rel creation):  <Country USA [1]>
    
    ### 2. Deleting relation:
    ## us.capital:  <Capital NYC [1]>
    ## ny.country:  <Country USA [1]>
    ## ny.country (after rel deletion):  None
    
    ### 3. Deleting one object:
    ## us.capital:  <Capital NYC [1]>
    ## ny.country:  <Country USA [1]>
    ## ny.country (after deletion of us):  <Country USA [1]>
    ## ny.country (+ flush):  <Country USA [1]>
    ## ny.country (+ expire):  None
    
    ### 4. Deleting the other object:
    ## us.capital:  <Capital NYC [1]>
    ## ny.country:  <Country USA [1]>
    ## us.capital (after deletion of ny):  <Capital NYC [1]>
    ## us.capital (+ flush):  <Capital NYC [1]>
    ## us.capital (+ expire):  None

[Sqlalchemy-tickets] Issue #4189: server_version_info doesn't return tuple of int to be compred with (zzzeek/sqlalchemy)

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

New issue 4189: server_version_info doesn't return tuple of int to be compred with
https://bitbucket.org/zzzeek/sqlalchemy/issues/4189/server_version_info-doesnt-return-tuple-of

Nicolas Bur:

Traceback (most recent call last):
      print(db.table_names())
  File "/home/nbur/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2139, in table_names
    return self.dialect.get_table_names(conn, schema)
  File "<string>", line 2, in get_table_names
  File "/home/nbur/.local/lib/python3.5/site-packages/sqlalchemy/engine/reflection.py", line 42, in cache
    return fn(self, con, *args, **kw)
  File "/home/nbur/.local/lib/python3.5/site-packages/sqlalchemy/dialects/mysql/base.py", line 1950, in get_table_names
    if self.server_version_info < (5, 0, 2):
TypeError: unorderable types: str() < int()

As written in this log server_version_info doesn't return a tuple of three int
adding
```
#!python
print(self.server_version_info)

```
line 1950 in File "/home/nbur/.local/lib/python3.5/site-packages/sqlalchemy/dialects/mysql/base.py"
displays (5, 0, '51a', '24+lenny5') which clearly can't be compared with (5, 0, 2)…

I mention there is no problem changing and running my script with python 2.7

SQLAlchemy-1.2.2

[Sqlalchemy-tickets] Issue #4188: mapper places ConcreteInheritedProperty on top of a hybrid (zzzeek/sqlalchemy)

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

New issue 4188: mapper places ConcreteInheritedProperty on top of a hybrid
https://bitbucket.org/zzzeek/sqlalchemy/issues/4188/mapper-places-concreteinheritedproperty-on

Michael Bayer:

```
#!python

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.ext.declarative import AbstractConcreteBase
from sqlalchemy.ext.hybrid import hybrid_property

Base = declarative_base()


class AbstractBase(AbstractConcreteBase, Base):
    @declared_attr
    def column1(cls):
        return Column(Integer)

    @declared_attr
    def column2(cls):
        return Column(Integer)


class Concrete1(AbstractBase):
    __tablename__ = "concrete1"

    id = Column(Integer, primary_key=True)

    attribute1 = Column(Integer)

    __mapper_args__ = {
        'polymorphic_identity': 'concrete1',
        'concrete': True
    }


class Concrete2(AbstractBase):
    __tablename__ = "concrete2"

    id = Column(Integer, primary_key=True)
    attribute2 = Column(Integer)

    @hybrid_property
    def attribute1(self):
        return self.attribute2

    @attribute1.expression
    def attribute1(cls):
        return cls.attribute2

    __mapper_args__ = {
        'polymorphic_identity': 'concrete2',
        'concrete': True
    }

print(Concrete2.attribute1.__clause_element__())
c2 = Concrete2(attribute2=5)
c2.attribute1
```

produces:

```
#!


concrete2.attribute2
Traceback (most recent call last):
  File "test.py", line 55, in <module>
    c2.attribute1
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line 298, in __get__
    return self.descriptor.__get__(instance, owner)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/descriptor_props.py", line 512, in __get__
    warn()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/descriptor_props.py", line 500, in warn
    (self.parent, self.key, self.parent))
AttributeError: Concrete Mapper|Concrete2|concrete2 does not implement attribute 'attribute1' at the instance level.  Add this property explicitly to Mapper|Concrete2|concrete2.

```

suggestion:

```
#!diff


diff --git a/lib/sqlalchemy/orm/mapper.py b/lib/sqlalchemy/orm/mapper.py
index cd9e00b8b..31100d9c8 100644
--- a/lib/sqlalchemy/orm/mapper.py
+++ b/lib/sqlalchemy/orm/mapper.py
@@ -1612,7 +1612,7 @@ class Mapper(InspectionAttr):
     def _adapt_inherited_property(self, key, prop, init):
         if not self.concrete:
             self._configure_property(key, prop, init=False, setparent=False)
-        elif key not in self._props:
+        elif not isinstance(getattr(self.class_, key, None), InspectionAttr):
             self._configure_property(
                 key,
                 properties.ConcreteInheritedProperty(),
```

[Sqlalchemy-tickets] Issue #4187: post_update fails if the parent object is deleted and the child isn't (zzzeek/sqlalchemy)

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

New issue 4187: post_update fails if the parent object is deleted and the child isn't
https://bitbucket.org/zzzeek/sqlalchemy/issues/4187/post_update-fails-if-the-parent-object-is

Michael Bayer:

it sounds unbelievable but it's true.   a needless UPDATE is emitted that only recenly raises an error due to #3496, but it's always been there.   nothing else is needed:

```
#!python


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

Base = declarative_base()


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    preferred_address_id = Column(ForeignKey("address.id"))

    preferred_address = relationship(
        "Address",
        primaryjoin=lambda: User.preferred_address_id == Address.id,
        post_update=True
    )


class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)

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

sess = Session(e)

a1 = Address()
bart = User(preferred_address=a1)

sess.add(bart)

sess.flush()

sess.delete(bart)

sess.flush()


```

```
#!python


Traceback (most recent call last):
  File "test.py", line 38, in <module>
    sess.flush()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2243, in flush
    self._flush(objects)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2369, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2333, in _flush
    flush_context.execute()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 391, in execute
    rec.execute(self)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 542, in execute
    persistence.post_update(self.mapper, states, uow, cols)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/persistence.py", line 234, in post_update
    mapper, table, update)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/persistence.py", line 982, in _emit_post_update_statements
    (table.description, len(records), rows))
sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'user' expected to update 1 row(s); 0 were matched.

```

no actual relationship cycle is needed, just the two objects.  the test_cycles test suite fails to test for this, here's a failing test:


```
#!diff

diff --git a/test/orm/test_cycles.py b/test/orm/test_cycles.py
index abd05067a..dc0be41bb 100644
--- a/test/orm/test_cycles.py
+++ b/test/orm/test_cycles.py
@@ -639,6 +639,28 @@ class OneToManyManyToOneTest(fixtures.MappedTest):
         sess.add(p)
         sess.flush()
 
+    def test_post_update_m2o_no_cascade(self):
+        person, ball, Ball, Person = (self.tables.person,
+                                      self.tables.ball,
+                                      self.classes.Ball,
+                                      self.classes.Person)
+
+        mapper(Ball, ball)
+        mapper(Person, person, properties=dict(
+            favorite=relationship(
+                Ball, primaryjoin=person.c.favorite_ball_id == ball.c.id,
+                post_update=True)))
+        b = Ball(data='some data')
+        p = Person(data='some data')
+        p.favorite = b
+        sess = create_session()
+        sess.add(b)
+        sess.add(p)
+        sess.flush()
+
+        sess.delete(p)
+        sess.flush()
+
     def test_post_update_m2o(self):
         """A cycle between two rows, with a post_update on the many-to-one"""
 

```

hoping this can be for 1.1.x however I am worried we might need #1063 for this to work, though "don't emit UPDATE if the object was deleted" seems simple...