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

[Sqlalchemy-tickets] Issue #4251: Buld general queries for getting table names, etc. (zzzeek/sqlalchemy)

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

New issue 4251: Buld general queries for getting table names, etc.
https://bitbucket.org/zzzeek/sqlalchemy/issues/4251/buld-general-queries-for-getting-table

Raul Chavez:

This is not an error, but a request for enhancement.

When sqlalchemy is used only as a query builder (for instance, in asyncronous context), it is not possible to build a query that returns all tables other than hard coding it. However, table_names is a method supported for engines from all dialects.

In the same way that general create table queries can be built, I think it should be possible to provide a way to build this kind of queries (also for shema_names and view_names) via DDL or some similar way.

[Sqlalchemy-tickets] Issue #4250: Boolean Column Filter in MS SQL Server (zzzeek/sqlalchemy)

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

New issue 4250: Boolean Column Filter in MS SQL Server
https://bitbucket.org/zzzeek/sqlalchemy/issues/4250/boolean-column-filter-in-ms-sql-server

Hei:

# Summary #

After upgrading sqlalchemy from 1.1.5 to 1.2.7 (the latest version), the SQL complied for MS SQL Server that has a boolean check in the where clause is changed from `select * from table where bool_flag = 1` to `select * from table where bool_flag`, and from`select * from table where bool_flag = 0` to `select * from table where not bool_flag`. The new complied SQL is not supported by MS SQL Server (I am testing on MS SQL Server 11.0), the error is `An expression of non-boolean type specified in a context where a condition is expected`.

# Test Case #

## Environment ##
* SQLAlchemy Version: 1.2.7
* Database: MS SQL Server 11.0
* Platform: Windows 7
* Python Version: 3.5

## Code ##

```
#!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)
    flag = Column(Boolean)


engine = create_engine('mssql+pyodbc://user:pass@host/db?driver=SQL Server', echo=True)
Session = sessionmaker(engine)

Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

conn = engine.connect()

engine.execute(select([A.id]).where(A.flag)).fetchall()
engine.execute(select([A.id]).where(~A.flag)).fetchall()
```

## Result ##
Failed in SQLAlchemy 1.2.7

```
#!python
Traceback (most recent call last):
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'flag'. (4145) (SQLExecDirectW)")

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

Traceback (most recent call last):
  File "C:/Users/Public/Quant/Workspace/python/datastore/test_query.py", line 26, in <module>
    engine.execute(select([A.id]).where(A.flag)).fetchall()
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\base.py", line 2075, in execute
    return connection.execute(statement, *multiparams, **params)
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context
    context)
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "C:\Users\mlam\AppData\Local\Continuum\Miniconda3\envs\datastore\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'flag'. (4145) (SQLExecDirectW)") [SQL: 'SELECT a.id \nFROM a \nWHERE a.flag'] (Background on this error at: http://sqlalche.me/e/f405)
```

The executed SQL are:
```
#!sql
SELECT a.id 
FROM a 
WHERE a.flag

SELECT a.id 
FROM a 
WHERE not a.flag
```

SQLAlchemy 1.1.5 works correctly without error, the executed SQL are:
```
#!sql
SELECT a.id 
FROM a 
WHERE a.flag = 1

SELECT a.id 
FROM a 
WHERE a.flag = 0
```

# Potential Cause #
It seems to be related to Issue #4061, treating the BIT in MS SQL Server as "native boolean". I am not very familiar with the SQLAlchemy code base, but I found something suspicious [here](https://bitbucket.org/zzzeek/sqlalchemy/src/31f80b9eaeb3c3435b7f6679b41e434478b1d11c/lib/sqlalchemy/sql/compiler.py?at=oracle_numeric&fileviewer=file-view-default#compiler.py-1015). For dialect that support native boolean, the `visit_istrue_unary_operator` and `visit_isfalse_unary_operator` compile the where clause to the `WHERE bool_column` and `WHERE NOT bool_column`, which are not supported by MS SQL Server.

[Sqlalchemy-tickets] Issue #4249: Error when testing dialect with an exclusion (zzzeek/sqlalchemy)

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

New issue 4249: Error when testing dialect with an exclusion
https://bitbucket.org/zzzeek/sqlalchemy/issues/4249/error-when-testing-dialect-with-an

Patrick Buxton:

The error shown below is seen when testing a dialect with:
    @property
    def json_type(self):
        return exclusions.closed()

=================================== ERRORS ====================================
___________ ERROR at setup of JSONTest.test_crit_against_int_basic ____________
Traceback (most recent call last):
  File "C:\Users\pat\Anaconda2\lib\site-packages\sqlalchemy\testing\plugin\pytestplugin.py", line 173, in pytest_runtest_setup
    class_setup(item.parent.parent)
  File "C:\Users\pat\Anaconda2\lib\site-packages\sqlalchemy\testing\plugin\pytestplugin.py", line 206, in class_setup
    plugin_base.start_test_class(item.cls)
  File "C:\Users\pat\Anaconda2\lib\site-packages\sqlalchemy\testing\plugin\plugin_base.py", line 452, in start_test_class
    _do_skips(cls)
  File "C:\Users\pat\Anaconda2\lib\site-packages\sqlalchemy\testing\plugin\plugin_base.py", line 567, in _do_skips
    for config_obj in config.Config.all_configs()
  File "C:\Users\pat\Anaconda2\lib\site-packages\sqlalchemy\testing\plugin\plugin_base.py", line 567, in <genexpr>
    for config_obj in config.Config.all_configs()
TypeError: 'NoneType' object is not iterable

I'm working on two dialects with this skip in place and it doesn't affect one of them, but I have seen that other people have also had this issue. (https://github.com/gijzelaerr/sqlalchemy-monetdb/issues/30)

The easiest way to reproduce will be to run the dialect tests:

git clone gi...@gi...:PlaidCloud/sqlalchemy-hana.git
cd sqlalchemy-hana
python setup.py test

Just comment out line 62 in testing\provision.py if you don't have a HANA server to point at and you'll still get the error generated even if the tests fail.

[Sqlalchemy-tickets] Issue #4248: PostgreSQL: Insufficient parenthesis-wrapping for (boolean) result of `IS NULL` (zzzeek/sqlalchemy)

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

New issue 4248: PostgreSQL: Insufficient parenthesis-wrapping for (boolean) result of `IS NULL`
https://bitbucket.org/zzzeek/sqlalchemy/issues/4248/postgresql-insufficient-parenthesis

Robert Scott:

Using sqlalchemy 1.1.4 with postgresql 9.6.

The result of an `IS NULL` comparison is a boolean and in postgres it is usable as such - for instance the following works:

```
db=> SELECT (123 IS NULL) = ('blah' IS NULL);
 ?column? 
----------
 t
(1 row)
```

However the following fails due to operator precedence:

```
db=> SELECT 1 IS NULL = 'blah' IS NULL;
ERROR:  invalid input syntax for type boolean: "blah"
LINE 1: SELECT 1 IS NULL = 'blah' IS NULL;
```

sqlalchemy 1.1.4 unfortunately doesn't include these parentheses:

```
>>> str(db.session.query((User.id == None) == (User.name == None)))
'SELECT users.id IS NULL = users.name IS NULL AS anon_1 \nFROM users'
```

which of course fails in a similar way. Luckily this wrapping *is* performed correctly for the result of equalities:

```
>>> str(db.session.query((User.id == 123) == (User.name == "blah")))
'SELECT (users.id = %(id_1)s) = (users.name = %(name_1)s) AS anon_1 \nFROM users'
```

and also the workaround of manually casting the result to a boolean works fine:

```
>>> str(db.session.query(db.cast(User.id == None, db.Boolean) == db.cast(User.name == None, db.Boolean)))
'SELECT CAST(users.id IS NULL AS BOOLEAN) = CAST(users.name IS NULL AS BOOLEAN) AS anon_1 \nFROM users'
```

[Sqlalchemy-tickets] Issue #4247: use shard id in identity key for refresh() (zzzeek/sqlalchemy)

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

New issue 4247: use shard id in identity key for refresh()
https://bitbucket.org/zzzeek/sqlalchemy/issues/4247/use-shard-id-in-identity-key-for-refresh

Michael Bayer:

related to #4243 as well as https://github.com/zzzeek/sqlalchemy/pull/424, since we have the shard identifier in the identity key as a result of #4137 we should use this for all refresh / unexpire operations.

[Sqlalchemy-tickets] Issue #4246: compound Query select apply for update to inner query? (zzzeek/sqlalchemy)

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

New issue 4246: compound Query select apply for update to inner query?
https://bitbucket.org/zzzeek/sqlalchemy/issues/4246/compound-query-select-apply-for-update-to

Michael Bayer:

```
#!diff

diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index 6d2b144e3..d9a0f9f47 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -3500,6 +3500,8 @@ class Query(object):
         if self._correlate:
             inner = inner.correlate(*self._correlate)
 
+        inner._for_update_arg = context._for_update_arg
+
         inner = inner.alias()
 
         equivs = self.__all_equivs()
@@ -3510,7 +3512,7 @@ class Query(object):
             [inner] + context.secondary_columns,
             use_labels=context.labels)
 
-        statement._for_update_arg = context._for_update_arg
+        #statement._for_update_arg = context._for_update_arg
 
         from_clause = inner
         for eager_join in context.eager_joins.values():

```

so that given:


```
#!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 A(Base):
    __tablename__ = 'a'
    id = Column(Integer, Sequence('aid_seq', optional=True), primary_key=True)
    bs = relationship("B")


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

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


Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = Session(e)
s.add_all([A(bs=[B(), B()]), A(bs=[B()])])

s.commit()

s.query(A).options(joinedload(A.bs)).with_for_update().first()




```

we get:


```
#!python

SELECT anon_1.a_id AS anon_1_a_id, b_1.id AS b_1_id, b_1.a_id AS b_1_a_id 
FROM (SELECT a.id AS a_id 
FROM a 
 LIMIT %s FOR UPDATE) AS anon_1 LEFT OUTER JOIN b AS b_1 ON anon_1.a_id = b_1.a_id

```

per https://bitbucket.org/zzzeek/sqlalchemy/issues/4100/with_for_update-interacts-badly-with-lazy#comment-44989256 the claim is that on MySQL, the lock is not applied if the FOR UPDATE is on the outside.

The above passes on MySQL and Posgresql.  On Oracle, the LIMIT is not rendering for some reason and that may be an additional issue.

[Sqlalchemy-tickets] Issue #4245: Expose row locking information in InstanceState (zzzeek/sqlalchemy)

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

New issue 4245: Expose row locking information in InstanceState
https://bitbucket.org/zzzeek/sqlalchemy/issues/4245/expose-row-locking-information-in

Robert Scott:

I don't know whether this is at all possible, but it would be extremely useful if InstanceState had information on the locking status of an instance's row in this transation, i.e. whether it was fetched through a `with_for_update` or the row had already been written back to the database already (hence having an implicit update lock).

I don't even know if sqlalchemy tracks this information but the use case I'm thinking of for this is having validators that are able to make cross-relationship checks with *some* degree of confidence.

[Sqlalchemy-tickets] Issue #4244: Impossible to insert subarray of nulls in multidimensional arrays (zzzeek/sqlalchemy)

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

New issue 4244: Impossible to insert subarray of nulls in multidimensional arrays
https://bitbucket.org/zzzeek/sqlalchemy/issues/4244/impossible-to-insert-subarray-of-nulls-in

Eoghan Murray:

Very similar issue to https://bitbucket.org/zzzeek/sqlalchemy/issues/3916/not-possible-to-insert-nulls-in-postgresql which was solved by an upgrade of psycopg2 to 2.6.2.

I'm experiencing the following issue with latest psycopg2  2.7.4 and sqlalchemy 1.2.7

Test case:


```
#!python

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import ARRAY

Base = declarative_base()


class Test (Base):

    __tablename__ = 'test_subarray_nulls'

    id = Column(Integer, primary_key=True)
    pay = Column(ARRAY(Integer))

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

s = Session(e)
member = Test()
member.pay = [[1.1, 1.2]]  # fine
member.pay = [[None, 2.2]]  # fine
member.pay = [[3.1, None]] # fine
member.pay = [[4.1, 4.2], [None, None]] # fine
member.pay = [[None, None]] # broken: sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "pay" is of type integer[] but expression is of type text[]
s.add(member)
s.commit()

```

Output:
```
[snip]
2018-04-26 14:08:34,264 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-04-26 14:08:34,265 INFO sqlalchemy.engine.base.Engine INSERT INTO test_psycopg_4 (pay) VALUES (%(pay)s) RETURNING test_psycopg_4.id
2018-04-26 14:08:34,265 INFO sqlalchemy.engine.base.Engine {'pay': [[None, None]]}
2018-04-26 14:08:34,266 INFO sqlalchemy.engine.base.Engine ROLLBACK
[snip (traceback)]
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "pay" is of type integer[] but expression is of type text[]
LINE 1: INSERT INTO test_psycopg_4 (pay) VALUES (ARRAY['{NULL, NULL}...
                                                 ^
HINT:  You will need to rewrite or cast the expression.
```

[Sqlalchemy-tickets] Issue #4243: lazyload parent state context for sharding (zzzeek/sqlalchemy)

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

New issue 4243: lazyload parent state context for sharding
https://bitbucket.org/zzzeek/sqlalchemy/issues/4243/lazyload-parent-state-context-for-sharding

Michael Bayer:

continuing from #4228, information needs to be present in the Query regarding what state we are lazy loading from.   Additionally, when a lazyload does hit the DB we have no chance to know what the parent shard we are querying from is which could help when the assumption is that we lazy-load from the same shard.

Additionally, a basic refresh of an object uses id_chooser() and we should be able to have the token available as well though this might be a separate issue

We'd like id_chooser to be writable as:


```
#!python

def id_chooser(query, ident):
    if query.lazy_loaded_from:
        return [query.lazy_loaded_from.identity_token]
    else:
        return [< all shard ids>]

```

this needs to happen both for identity map lookup as well as a real load, and since BakedQuery does its own version of "get()" we need to fill through:



```
#!diff

diff --git a/lib/sqlalchemy/ext/baked.py b/lib/sqlalchemy/ext/baked.py
index 13ad4cf7c..fa63d35d9 100644
--- a/lib/sqlalchemy/ext/baked.py
+++ b/lib/sqlalchemy/ext/baked.py
@@ -154,6 +154,11 @@ class BakedQuery(object):
         self._spoiled = True
         return self
 
+    def _add_lazyloaded_state(self, state):
+        self.add_criteria(
+            lambda q: q._set_lazyload_from(state)
+        )
+
     def _add_lazyload_options(self, options, effective_path, cache_path=None):
         """Used by per-state lazy loaders to add options to the
         "lazy load" query from a parent query.
diff --git a/lib/sqlalchemy/ext/horizontal_shard.py b/lib/sqlalchemy/ext/horizontal_shard.py
index 266bd784e..a612e6aca 100644
--- a/lib/sqlalchemy/ext/horizontal_shard.py
+++ b/lib/sqlalchemy/ext/horizontal_shard.py
@@ -65,7 +65,7 @@ class ShardedQuery(Query):
     @classmethod
     def _identity_lookup(
             cls, session, mapper, primary_key_identity, identity_token=None,
-            **kw):
+            lazyload_from=None, **kw):
         """override the default Query._identity_lookup method so that we
         search for a given non-token primary key identity across all
         possible identity tokens (e.g. shard ids).
@@ -80,6 +80,8 @@ class ShardedQuery(Query):
             )
         else:
             q = cls([mapper], session)
+            if lazyload_from:
+                q = q._set_lazyload_from(lazyload_from)
             for shard_id in q.id_chooser(q, primary_key_identity):
                 obj = super(ShardedQuery, cls)._identity_lookup(
                     session, mapper, primary_key_identity,
diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index a17f590e9..44749cac8 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -110,6 +110,7 @@ class Query(object):
     _orm_only_from_obj_alias = True
     _current_path = _path_registry
     _has_mapper_entities = False
+    _lazyload_from = None
 
     def __init__(self, entities, session=None):
         """Construct a :class:`.Query` directly.
@@ -258,6 +259,10 @@ class Query(object):
             for o in cols
         ]
 
+    @_generative()
+    def _set_lazyload_from(self, state):
+        self._lazyload_from = state
+
     @_generative()
     def _adapt_all_clauses(self):
         self._orm_only_adapt = False
@@ -884,7 +889,7 @@ class Query(object):
     @classmethod
     def _identity_lookup(
             cls, session, mapper, primary_key_identity, identity_token=None,
-            passive=attributes.PASSIVE_OFF):
+            passive=attributes.PASSIVE_OFF, on_behalf_of=None):
         """Locate an object in the identity map.
 
         Given a primary key identity, constructs an identity key and then
@@ -904,6 +909,13 @@ class Query(object):
          :func:`.loading.get_from_identity`, which impacts the behavior if
          the object is found; the object may be validated and/or unexpired
          if the flag allows for SQL to be emitted.
+        :param on_behalf_of: an :class:`.InstanceState` that is specifically
+         asking for this identity as a related identity.  Used for sharding
+         schemes where there is a correspondence between an object and
+         a related object being lazy-loaded (or otherwise relationship-loaded).
+
+         .. versionadded:: 1.2.8
+
         :return: None if the object is not found in the identity map, *or*
          if the object was unexpired and found to have been deleted.
          if passive flags disallow SQL and the object is expired, returns
diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py
index 9bce5593d..6241d4840 100644
--- a/lib/sqlalchemy/orm/strategies.py
+++ b/lib/sqlalchemy/orm/strategies.py
@@ -618,7 +618,7 @@ class LazyLoader(AbstractRelationshipLoader, util.MemoizedSlots):
             # identity_token would be for this identity
             instance = session._query_cls._identity_lookup(
                 session, self.mapper, primary_key_identity,
-                passive=passive
+                passive=passive, lazyload_from=state
             )
 
             if instance is not None:
@@ -709,14 +709,17 @@ class LazyLoader(AbstractRelationshipLoader, util.MemoizedSlots):
             # is usually a throwaway object.
             effective_path = state.load_path[self.parent_property]
             q._add_lazyload_options(
-                state.load_options, effective_path
+                state, state.load_options, effective_path
             )
 
+        q._add_lazyloaded_state(state)
+
         if self.use_get:
             if self._raise_on_sql:
                 self._invoke_raise_load(state, passive, "raise_on_sql")
             return q(session)._load_on_pk_identity(
-                session.query(self.mapper), primary_key_identity)
+                session.query(self.mapper),
+                primary_key_identity)
 
         if self.parent_property.order_by:
             q.add_criteria(
diff --git a/test/ext/test_horizontal_shard.py b/test/ext/test_horizontal_shard.py
index 0bcacad37..29cd7c30d 100644
--- a/test/ext/test_horizontal_shard.py
+++ b/test/ext/test_horizontal_shard.py
@@ -390,11 +390,23 @@ class LazyLoadFromIdentityMapTest(fixtures.DeclarativeMappedTest):
             book_id = Column(ForeignKey('book.id'))
 
     def test_lazy_load_from_identity_map(self):
+        def id_chooser(query, ident):
+            assert query._lazyload_from and \
+                query._lazyload_from.identity_token == 'test'
+            return ['test']
+
+        def no_query_chooser(query):
+            if query._lazyload_from is None:
+                assert query.column_descriptions[0]['type'] is Page
+            else:
+                assert query._lazyload_from.identity_token == 'test'
+            return ['test']
+
         session = ShardedSession(
             shards={"test": testing.db},
             shard_chooser=lambda *args: 'test',
-            id_chooser=lambda *args: ['test'],
-            query_chooser=lambda *args: ['test']
+            id_chooser=id_chooser,
+            query_chooser=no_query_chooser
         )
 
         Book, Page = self.classes("Book", "Page")
@@ -402,11 +414,11 @@ class LazyLoadFromIdentityMapTest(fixtures.DeclarativeMappedTest):
         book.pages.append(Page())
 
         session.add(book)
-        session.commit()
+        session.flush()
 
-        book = session.query(Book).first()
         page = session.query(Page).first()
 
+        session.expire(page, ['book'])
         def go():
             eq_(page.book, book)
 
@@ -415,3 +427,44 @@ class LazyLoadFromIdentityMapTest(fixtures.DeclarativeMappedTest):
             testing.db,
             go,
             0)
+
+    def test_lazy_load_from_db(self):
+        def id_chooser(query, ident):
+            assert query._lazyload_from and \
+                query._lazyload_from.identity_token == 'test'
+            return ['test']
+
+        def no_query_chooser(query):
+            if query._lazyload_from is None:
+                assert query.column_descriptions[0]['type'] is Page
+            else:
+                assert query._lazyload_from.identity_token == 'test'
+            return ['test']
+
+        session = ShardedSession(
+            shards={"test": testing.db},
+            shard_chooser=lambda *args: 'test',
+            id_chooser=id_chooser,
+            query_chooser=no_query_chooser
+        )
+
+        Book, Page = self.classes("Book", "Page")
+        book = Book()
+        book.pages.append(Page())
+
+        session.add(book)
+        session.flush()
+        book_id = inspect(book).identity_key
+        session.expunge(book)
+
+        page = session.query(Page).first()
+        session.expire(page, ['book'])
+
+        def go():
+            eq_(inspect(page.book).identity_key, book_id)
+
+        # emits one query
+        self.assert_sql_count(
+            testing.db,
+            go,
+            1)

```

this is compatible w/ a 1.2.8 release as this is more regression stuff due to #4137

[Sqlalchemy-tickets] Issue #4242: Bad Precision Error thrown only in SQLAlchemy (zzzeek/sqlalchemy)

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

New issue 4242: Bad Precision Error thrown only in SQLAlchemy
https://bitbucket.org/zzzeek/sqlalchemy/issues/4242/bad-precision-error-thrown-only-in

Araam Borhanian:

When I run this date query, SQLAlchemy has cx_Oracle throw an error, but I can't replicate this with just cx_Oracle alone. This started showing up after version 1.2.2 of SQLAlchemy. Earlier versions don't have this issue. Occurs on all versions of cx_Oracle from 5.2.1 to 6.2.1

DB Version I'm running on is Oracle Database 11g Release 11.2.0.4.0 - 64bit Production.

import cx_Oracle
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql.expression import func


# This works fine
conn = cx_Oracle.connect("conn_str")
cursor = conn.cursor()
result = cursor.execute("SELECT trunc(sysdate, :day) - :back FROM DUAL",
                        {'day': 'dd', 'back': 1}).fetchall()
cursor.close()
conn.close()

# This throws a bad precision specifier error.
some_engine = create_engine("oracle+cx_oracle://conn_str")
Session = sessionmaker(bind=some_engine)
session = Session()
session.query(func.trunc(func.sysdate(), 'dd') - 1).all()


The stacktrace after running this code:

Traceback (most recent call last):
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "...\.env\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
cx_Oracle.DatabaseError: ORA-01899: bad precision specifier

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

Traceback (most recent call last):
  File "testcase.py", line 19, in <module>
    session.query(func.trunc(func.sysdate(), 'dd') - 1).all()
  File "...\.env\lib\site-packages\sqlalchemy\orm\query.py", line 2726, in all
    return list(self)
  File "...\.env\lib\site-packages\sqlalchemy\orm\query.py", line 2878, in __iter__
    return self._execute_and_instances(context)
  File "...\.env\lib\site-packages\sqlalchemy\orm\query.py", line 2901, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "...\.env\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context
    context)
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "...\.env\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "...\.env\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "...\.env\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-01899: bad precision specifier [SQL: 'SELECT trunc(sysdate, :trunc_1) - :trunc_2 AS anon_1 FROM DUAL'] [parameters: {'trunc_1': 'dd', 'trunc_2': 1}] (Background on this error at: http://sqlalche.me/e/4xp6)

[Sqlalchemy-tickets] Issue #4241: selectin loading in Single Table Inheritance produces cross join (zzzeek/sqlalchemy)

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

New issue 4241: selectin loading in Single Table Inheritance produces cross join
https://bitbucket.org/zzzeek/sqlalchemy/issues/4241/selectin-loading-in-single-table

Tim Chen:

```
#!python

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

Base = declarative_base()


class User(Base):
    __tablename__ = 'user'

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

    __mapper_args__ = {
        'polymorphic_on': type,
    }


class EmployerUser(User):
    # __tablename__ = 'employer_user'

    # id = Column(Integer, ForeignKey('user.id'), primary_key=True)
    roles = relationship('Role', lazy='selectin')

    __mapper_args__ = {
        'polymorphic_identity': 'employer',
    }


class Role(Base):
    __tablename__ = 'role'

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


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

s = Session(e)

user = EmployerUser(roles=[Role(), Role(), Role()])
s.add(user)
s.commit()

s.query(EmployerUser).all()

```

The generated selectin load SQL looks like this:
```
#!sql

SELECT user_1.id AS user_1_id, role.id AS role_id, role.user_id AS role_user_id
FROM user, user AS user_1 JOIN role ON user_1.id = role.user_id
WHERE user_1.id IN (?) AND user.type IN (?) AND user_1.type IN (?) ORDER BY user_1.id
```

You can see that user gets cross joined to itself, blowing up the result set unnecessarily.  This becomes a huge problem for large tables.

[Sqlalchemy-tickets] Issue #4240: Cannot copy new ForeignKeyConstraint (zzzeek/sqlalchemy)

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

New issue 4240: Cannot copy new ForeignKeyConstraint
https://bitbucket.org/zzzeek/sqlalchemy/issues/4240/cannot-copy-new-foreignkeyconstraint

fantix NA:

* SQLAlchemy version: 1.2.6
 * Database: N/A

Scenario: Using a list of common columns/constraints to define different tables.

Code to reproduce:

    import sqlalchemy
    sqlalchemy.ForeignKeyConstraint(['user_id'], ['users.id']).copy()

Traceback:

    Traceback (most recent call last):
      File "test.py", line 2, in <module>
        sqlalchemy.ForeignKeyConstraint(['user_id'], ['users.id']).copy()
      File "sqlalchemy/sql/schema.py", line 3063, in copy
        [x.parent.key for x in self.elements],
      File "sqlalchemy/sql/schema.py", line 3063, in <listcomp>
        [x.parent.key for x in self.elements],
    AttributeError: 'NoneType' object has no attribute 'key'

[Sqlalchemy-tickets] Issue #4239: engine.execute does not commit grant request (zzzeek/sqlalchemy)

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

New issue 4239: engine.execute does not commit grant request
https://bitbucket.org/zzzeek/sqlalchemy/issues/4239/engineexecute-does-not-commit-grant

Alexis B:

We using postgresql 9.6.8 tried to add rights to a role.

It's not considered as a ddl query thus it does not issue a
commit at the end of the query.

This query issues a rollback:
```
engine.execute("GRANT SELECT ON ALL TABLES IN SCHEMA sch TO rol;")
```

This query works:
```
engine.execute(text("GRANT SELECT ON ALL TABLES IN SCHEMA sch TO rol;").execution_options(autocommit=True)) 
```

Shouldn't grant be considered as a ddl statement?

[Sqlalchemy-tickets] Issue #4238: Docs are not strongly worded enough for many-to-many with extra columns (zzzeek/sqlalchemy)

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

New issue 4238: Docs are not strongly worded enough for many-to-many with extra columns
https://bitbucket.org/zzzeek/sqlalchemy/issues/4238/docs-are-not-strongly-worded-enough-for

chrismacklin:

When using the association object pattern with a table with columns besides the two bridging foreign keys, the docs only gently state that association_proxy is needed.  I think this needs to be called out much more loudly and clearly in the docs, as using the secondary kwarg with an association table with extra mapped columns can cause silent corruption of the result set.  As it stands, there's just one sentence in the docs that mentions in an almost offhand fashion that if there are extra columns, you need to use association_proxy:

http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-object
"The association object pattern is a variant on many-to-many: it’s used when your association table contains additional columns beyond those which are foreign keys to the left and right tables."

This makes it sounds like it provides something like a nice convenience, rather than being strictly required.

It would be even better if the secondary kwarg could perform some validation and refuse to map an association table that doesn't meet the requirement that the only columns on the table should be the FKs participating in the association.

In my case, I had an association table with an integer primary key ID and a pair of FKs forming the association (I'll call them foo_id and bar_id), associating zero-to-many instances of Bar with a single Foo, allowing duplicate association of the same Bar multiple times to a single Foo.  Suppose I have a single instance of Foo (my_foo), with two rows in the association table to a single instance of Bar (my_bar).

If my_foo.bars is declared with association_proxy, this holds:
assert my_foo.bars == [my_bar, my_bar]

If my_foo.bars is declared using relationship with secondary, I end up with the corrupt result set:
assert my_foo.bars == [my_bar]
No warning or error is emitted when creating this mapping.

This seems like a pretty big footgun to me, and I think the docs need to be MUCH more clear on this.

[Sqlalchemy-tickets] Issue #4237: Couldn't reflect postgres table. PG 10 (zzzeek/sqlalchemy)

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

New issue 4237: Couldn't reflect postgres table. PG 10
https://bitbucket.org/zzzeek/sqlalchemy/issues/4237/couldnt-reflect-postgres-table-pg-10

alsenchenko:

Postgresql 10 has introduced new relkind 'p' for a partitioned table.
Sqlalchemy selects  'r' , 'v', 'm', 'f' tables.

[Sqlalchemy-tickets] Issue #4236: Got an error when query an object have enum (zzzeek/sqlalchemy)

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

New issue 4236: Got an error when query an object have enum
https://bitbucket.org/zzzeek/sqlalchemy/issues/4236/got-an-error-when-query-an-object-have

XKVNN:

I tried to use the code in the documentation:


```
#!python

import enum

Base = declarative_base()


class MyEnum(enum.Enum):
    one = 1
    two = 2
    three = 3


t = Table(
    'data', Base.metadata,
    Column('value', Enum(MyEnum))
)


Base.metadata.drop_all(engine_fb, checkfirst=True)
Base.metadata.create_all(engine_fb, checkfirst=True)

connection = engine_fb.connect()
connection.execute(t.insert(), {"value": MyEnum.two})
```

All working fine and data inserted into db

```
#!log

2018-04-12 18:25:32,654 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE data (
	value ENUM('one','two','three')
)


2018-04-12 18:25:32,655 INFO sqlalchemy.engine.base.Engine ()
2018-04-12 18:25:32,673 INFO sqlalchemy.engine.base.Engine COMMIT
2018-04-12 18:25:32,673 INFO sqlalchemy.engine.base.Engine INSERT INTO data (value) VALUES (%s)
2018-04-12 18:25:32,674 INFO sqlalchemy.engine.base.Engine ('two',)
2018-04-12 18:25:32,675 INFO sqlalchemy.engine.base.Engine COMMIT
```

But when execute a query to get an object, I got an error:

```
#!python

LookupError: "b'two'" is not among the defined enum values
```

[Sqlalchemy-tickets] Issue #4235: sequence support for SQL server (zzzeek/sqlalchemy)

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

New issue 4235: sequence support for SQL server
https://bitbucket.org/zzzeek/sqlalchemy/issues/4235/sequence-support-for-sql-server

Michael Bayer:

SQL Server supports this:  https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql


an initial gerrit that needs lots of work:

https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/724/


this can be targeted at 1.3 if we have resources.

[Sqlalchemy-tickets] Issue #4234: Foreign key constraints not reflected from SQL Server Database (zzzeek/sqlalchemy)

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

New issue 4234: Foreign key constraints not reflected from SQL Server Database
https://bitbucket.org/zzzeek/sqlalchemy/issues/4234/foreign-key-constraints-not-reflected-from

Dave Hirschfeld:

Assuming a SQL Server database with two schemas `TEST1` and `TEST2` the below MVCE demonstrates that the foreign key constraint isn't correctly reflected from the database:
```python
import sqlalchemy as sa

engine = sa.create_engine(
    f"mssql://{server}/{database}?driver=ODBC+Driver+13+for+SQL+Server"
)

metadata = sa.MetaData()

table1 = sa.Table(
    'TrueFalse', metadata,
    sa.Column('id', sa.SmallInteger, primary_key=True),
    sa.Column('name', sa.String(5), nullable=False),
    schema='TEST2',
)

table2 = sa.Table(
    'TestTable', metadata,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('true_false', sa.SmallInteger, sa.ForeignKey(table1.c.id)),
    schema='TEST1',
)

metadata.create_all(engine)

table3 = sa.Table('TestTable', sa.MetaData(bind=engine), schema='TEST1', autoload=True)
```
```python
In [10]: table2.foreign_keys
Out[10]: {ForeignKey('TEST2.TrueFalse.id')}

In [11]: table3.foreign_keys
Out[11]: set()          <---------- SHOULD BE THE SAME AS TABLE2!!!
```
The FK constraint is created correctly in the database.

[Sqlalchemy-tickets] Issue #4233: params={} is being passed to pymysql but it expects None (zzzeek/sqlalchemy)

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

New issue 4233: params={} is being passed to pymysql but it expects None
https://bitbucket.org/zzzeek/sqlalchemy/issues/4233/params-is-being-passed-to-pymysql-but-it

Abdeali Kothari:

I was using pandas+sqlalchemy_pymysql to query from MySQL. It looks like when I try to read sql or even use sqlalchemy engine to query it gives me an error when I use a % in my query.

Using Python 3.6, sqlalchemy 1.2.1


```
#!python

qry = """
SELECT DATE_FORMAT(fetchDate, '%Y-%m') as frequency
FROM portfolionotesdailyperformance
LIMIT 10;
"""
import sqlalchemy
import settings
engine = sqlalchemy.create_engine(settings.mariadb_connection)
engine.execute(qry)
```

It looks like the `params` being passed to the cursor is {} but pymysql expects it to be a `None` for it to NOT try using python string formatting.

Reference:
https://github.com/PyMySQL/PyMySQL/blob/9c1b0d80995587cab39b56ea3b48cc25cb63289d/pymysql/cursors.py#L144

If anything other than None is passed, it does a % formatting in python.
This causes issues here as the %Y is getting converted to Y and then it gives an error:




```
#!python

ValueError                                Traceback (most recent call last)
<ipython-input-2-52648731c651> in <module>()
      7 import settings
      8 engine = sqlalchemy.create_engine(settings.mariadb_connection)
----> 9 engine.execute(qry)

~/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py in execute(self, statement, *multiparams, **params)
   2073
   2074         connection = self.contextual_connect(close_with_result=True)
-> 2075         return connection.execute(statement, *multiparams, **params)
   2076
   2077     def scalar(self, statement, *multiparams, **params):

~/venv/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

~/venv/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:

~/venv/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:

~/venv/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:

~/venv/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:

~/venv/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(

~/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    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):

~/venv/lib/python3.6/site-packages/pymysql/cursors.py in execute(self, query, args)
    162             pass
--> 163         query = self.mogrify(query, args)
    164
    165         result = self._query(query)

~/venv/lib/python3.6/site-packages/pymysql/cursors.py in mogrify(self, query, args)
    141
    142         if args is not None:
--> 143             query = query % self._escape_args(args, conn)
    144
    145         return query

ValueError: unsupported format character 'Y' (0x59) at index 33
```

[Sqlalchemy-tickets] Issue #4232: Another case of illegal sorting in Python 3 (zzzeek/sqlalchemy)

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

New issue 4232: Another case of illegal sorting in Python 3
https://bitbucket.org/zzzeek/sqlalchemy/issues/4232/another-case-of-illegal-sorting-in-python

Chris Wilson:

Similar to #2228, but this one occurs when removing multiple objects from a session (deleting from the database) in a single commit, and the primary key contains an object which has no sort order defined, such as an Enum.

If there are multiple persistent objects to be deleted, then `_sort_states` in `persistence.py` wants to sort them, and it uses the identity key to do so. But if it contains non-comparable objects, this will fail.

I'm not sure exactly why we want to "sort the states" here. But if the sort order is arbitrary then we could just sort by ID (or not sort them at all?).

I've seen this on Postgres 10 and SQLite (demo below).

```
#!python

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

import enum
class MyEnum(enum.Enum):
    one = 1
    two = 2
    three = 3
    
Base = declarative_base()

class A(Base):
    __tablename__ = 'a'

    id = Column(Enum(MyEnum), primary_key=True)
    data = Column(Integer)

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

s = Session(e)

a1 = A(id=MyEnum.one)
s.add(a1)
s.commit()

a2 = A(id=MyEnum.two)
s.add(a2)
s.commit()

s.delete(a1)
s.delete(a2)
s.commit()
```

The resulting error is:

```
File untitled1.py, line 45, in : s.commit() 
File sqlalchemy-1.1.14-py3.6-win-amd64.egg\sqlalchemy\orm\session.py, line 906, in commit : self.transaction.commit() 
File sqlalchemy-1.1.14-py3.6-win-amd64.egg\sqlalchemy\orm\session.py, line 461, in commit : self._prepare_impl() 
File sqlalchemy-1.1.14-py3.6-win-amd64.egg\sqlalchemy\orm\session.py, line 441, in _prepare_impl : self.session.flush() 
File sqlalchemy-1.1.14-py3.6-win-amd64.egg\sqlalchemy\orm\session.py, line 2177, in flush : self._flush(objects) 
File sqlalchemy-1.1.14-py3.6-win-amd64.egg\sqlalchemy\orm\session.py, line 2297, in _flush : transaction.rollback(_capture_exception=True) 
File sqlalchemy-1.1.14-py3.6-win-amd64.egg\sqlalchemy\util\langhelpers.py, line 66, in __exit__ : compat.reraise(exc_type, exc_value, exc_tb) 
File sqlalchemy-1.1.14-py3.6-win-amd64.egg\sqlalchemy\util\compat.py, line 187, in reraise : raise value 
File sqlalchemy-1.1.14-py3.6-win-amd64.egg\sqlalchemy\orm\session.py, line 2261, in _flush : flush_context.execute() 
File sqlalchemy-1.1.14-py3.6-win-amd64.egg\sqlalchemy\orm\unitofwork.py, line 389, in execute : rec.execute(self) 
File sqlalchemy-1.1.14-py3.6-win-amd64.egg\sqlalchemy\orm\unitofwork.py, line 577, in execute : uow 
File sqlalchemy-1.1.14-py3.6-win-amd64.egg\sqlalchemy\orm\persistence.py, line 243, in delete_obj : uowtransaction)) 
File sqlalchemy-1.1.14-py3.6-win-amd64.egg\sqlalchemy\orm\persistence.py, line 357, in _organize_states_for_delete : states): 
File sqlalchemy-1.1.14-py3.6-win-amd64.egg\sqlalchemy\orm\persistence.py, line 1108, in _connections_for_states : for state in _sort_states(states): 
File sqlalchemy-1.1.14-py3.6-win-amd64.egg\sqlalchemy\orm\persistence.py, line 1130, in _sort_states : sorted(persistent, key=lambda q: q.key[1]) 
TypeError: '<' not supported between instances of 'MyEnum' and 'MyEnum'
```

[Sqlalchemy-tickets] Issue #4231: Insert with inline=True on Oracle database raises error (zzzeek/sqlalchemy)

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

New issue 4231: Insert with inline=True on Oracle database raises error
https://bitbucket.org/zzzeek/sqlalchemy/issues/4231/insert-with-inline-true-on-oracle-database

Alistair Watson:

Hi, the code below fails to print the raw sql insert statement. When setting the insert inline to False, the code works but does not replace the id bind value (as expected). Adding **kw to the parameters of visit_sequence on line 770 of dialects/oracle/base.py appears to make it work as expected. SQLAlchemy version 1.2

```
from sqlalchemy import create_engine, MetaData, Column, Integer,Sequence
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects import oracle

def dump(sql):
    print(str(sql.compile(dialect=oracle.dialect(), compile_kwargs={"literal_binds": True})))

engine = create_engine('oracle+cx_oracle://test_schema:Welcome1@localhost:1521/xe', strategy='mock',executor=dump)
Base = declarative_base(metadata=MetaData())

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, Sequence(name="test_sequence"), primary_key=True)
    data = Column(Integer)

engine.execute(Child.__table__.insert(inline=True).values({'data':2}))
```

and the stacktrace:
```
Traceback (most recent call last):
  File "print_raw_sql_test.py", line 16, in <module>
    engine.execute(Child.__table__.insert(inline=True).values({'data':2}))
  File "print_raw_sql_test.py", line 6, in dump
    print(str(sql.compile(dialect=oracle.dialect(), compile_kwargs={"literal_binds": True})))
  File "<string>", line 1, in <lambda>
  File "/home/aw/env3/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 442, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/home/aw/env3/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 448, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/home/aw/env3/lib/python3.6/site-packages/sqlalchemy/dialects/oracle/base.py", line 674, in __init__
    super(OracleCompiler, self).__init__(*args, **kwargs)
  File "/home/aw/env3/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 453, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/home/aw/env3/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 219, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/home/aw/env3/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 245, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/home/aw/env3/lib/python3.6/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/home/aw/env3/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 2034, in visit_insert
    self, insert_stmt, crud.ISINSERT, **kw)
  File "/home/aw/env3/lib/python3.6/site-packages/sqlalchemy/sql/crud.py", line 57, in _setup_crud_params
    return _get_crud_params(compiler, stmt, **kw)
  File "/home/aw/env3/lib/python3.6/site-packages/sqlalchemy/sql/crud.py", line 137, in _get_crud_params
    _col_bind_name, check_columns, values, kw)
  File "/home/aw/env3/lib/python3.6/site-packages/sqlalchemy/sql/crud.py", line 290, in _scan_cols
    values, kw)
  File "/home/aw/env3/lib/python3.6/site-packages/sqlalchemy/sql/crud.py", line 480, in _append_param_insert_hasdefault
    proc = compiler.process(c.default, **kw)
  File "/home/aw/env3/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 245, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/home/aw/env3/lib/python3.6/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
TypeError: visit_sequence() got an unexpected keyword argument 'literal_binds'
```

[Sqlalchemy-tickets] Issue #4230: build CTE round trip tests, add mariadb 10.2 (zzzeek/sqlalchemy)

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

New issue 4230: build CTE round trip tests, add mariadb 10.2
https://bitbucket.org/zzzeek/sqlalchemy/issues/4230/build-cte-round-trip-tests-add-mariadb-102

Michael Bayer:

in https://bitbucket.org/zzzeek/sqlalchemy/pull-requests/4/mariadb-102-includes-cte-with-recursive/diff we can see there aren't actually any round trip tests for CTEs, probably should have some. MariaDB 10.2 now supports them also.

[Sqlalchemy-tickets] Issue #4229: PostgreSQL DATERANGE(): comparison with None does not generate expected "IS NOT NULL" (zzzeek/sqlalchemy)

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

New issue 4229: PostgreSQL DATERANGE(): comparison with None does not generate expected "IS NOT NULL"
https://bitbucket.org/zzzeek/sqlalchemy/issues/4229/postgresql-daterange-comparison-with-none

Lele Gaifax:

The following script:

```
#!python

import sqlalchemy as sa
import sqlalchemy.orm as orm
import sqlalchemy.types as types
import sqlalchemy.dialects.postgresql as sapg


md = sa.MetaData()


testtable = sa.Table(
    'testtable', md,
    sa.Column('id', types.Integer(),
              primary_key=True,
              nullable=False),
    sa.Column('name', types.Unicode(192),
              nullable=False),
    sa.Column('visibility', sapg.DATERANGE())
)


class Base:
    def __init__(self, **kwargs):
        for k in kwargs:
            setattr(self, k, kwargs[k])


class TestTable(Base):
    pass


orm.mapper(TestTable, testtable)


e = sa.create_engine('postgresql://localhost/test', echo=True)
md.create_all(e)


s = orm.Session(e)

o1 = TestTable(id=1, name='foo')
s.add(o1)
s.commit()

q = s.query(TestTable).filter(TestTable.name != None)
print(str(q))
q = s.query(TestTable).filter(TestTable.name == None)
print(str(q))

q = s.query(TestTable).filter(TestTable.visibility != None)
print(str(q))
q = s.query(TestTable).filter(TestTable.visibility == None)
print(str(q))
```

prints:
```
SELECT testtable.id AS testtable_id, testtable.name AS testtable_name, testtable.visibility AS testtable_visibility 
FROM testtable 
WHERE testtable.name IS NOT NULL
SELECT testtable.id AS testtable_id, testtable.name AS testtable_name, testtable.visibility AS testtable_visibility 
FROM testtable 
WHERE testtable.name IS NULL
SELECT testtable.id AS testtable_id, testtable.name AS testtable_name, testtable.visibility AS testtable_visibility 
FROM testtable 
WHERE testtable.visibility <> %(visibility_1)s
SELECT testtable.id AS testtable_id, testtable.name AS testtable_name, testtable.visibility AS testtable_visibility 
FROM testtable 
WHERE testtable.visibility IS NULL
```

Am I missing something or is this unexpected?

[Sqlalchemy-tickets] Issue #4228: Regression in lazy loading strategy for sharded setups (zzzeek/sqlalchemy)

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

New issue 4228: Regression in lazy loading strategy for sharded setups
https://bitbucket.org/zzzeek/sqlalchemy/issues/4228/regression-in-lazy-loading-strategy-for

Rishi Sharma:

SQLAlchemy >= 1.2

In sharded setups, the lazy loading strategy no longer fetches instances available in the identity map; instead, emits a query.

Test case can found found [here](https://github.com/rishi-freshbooks/sqlalchemy/blob/d1618ade1392e17c1028c65e177990e152715d6a/test/ext/test_horizontal_shard.py#L377-L418)

[Sqlalchemy-tickets] Issue #4227: SQL server / pyodbc allows non-int tokens in version tuple which fails on comparison (zzzeek/sqlalchemy)

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

New issue 4227: SQL server / pyodbc allows non-int tokens in version tuple which fails on comparison
https://bitbucket.org/zzzeek/sqlalchemy/issues/4227/sql-server-pyodbc-allows-non-int-tokens-in

Michael Bayer:

e.g. stack trace like:


```
#!python

    return once_fn(*arg, **kw)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\engine\strategies.py", line 183, in first_connect
    dialect.initialize(c)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1931, in initialize
    super(MSDialect, self).initialize(connection)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\engine\default.py", line 267, in initialize
    self._get_default_schema_name(connection)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1958, in _get_default_schema_name
    if self.server_version_info < MS_2005_VERSION:
TypeError: '<' not supported between instances of 'str' and 'int'
```

we should not be allowing non-int tokens in the version string at all:


```
#!diff

diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py
index 14e8ae838..30b8b8b50 100644
--- a/lib/sqlalchemy/dialects/mssql/pyodbc.py
+++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py
@@ -291,7 +291,7 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect):
                 try:
                     version.append(int(n))
                 except ValueError:
-                    version.append(n)
+                    pass
             return tuple(version)
 
     def is_disconnect(self, e, connection, cursor):

```

however, need to figure out what version string this user is getting as we still need to locate the numeric tokens.