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

[Sqlalchemy-tickets] Issue #4376: Can't determine horizontal shard for many-to-many relationship (zzzeek/sqlalchemy)

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

New issue 4376: Can't determine horizontal shard for many-to-many relationship
https://bitbucket.org/zzzeek/sqlalchemy/issues/4376/cant-determine-horizontal-shard-for-many

Hannele Kormano:

I'm running into trouble updating a many-to-many relationship with horizontal shards. It looks like there are cases where SQLAlchemy doesn't pass an instance nor a query to the shard_chooser function, so I'm unsure what assumptions we should be making. It looks like a reasonable thing to do might be to return a dummy value in those cases as there shouldn't actually be any action -- it does also look like the shard_chooser is called separately for each kind of many-to-many update, but I'd want to make sure there aren't other reasons we should throw an error in this case. 

Reproducible in at least SQLAlchemy 1.2, with MySQL 5.6

One consistent way to reproduce this issue is to add to an empty collection -- by definition there are no deletes to process. In the tests below, I've set up the shard_mapper to throw an error in those cases where neither an instance nor a query is passed in.

    from sqlalchemy import *
    from sqlalchemy.orm import *
    from sqlalchemy.ext.horizontal_shard import ShardedSession
    from sqlalchemy.testing import fixtures
    from sqlalchemy import testing


    class UpdateManyToManyShardedTest(fixtures.DeclarativeMappedTest):
        @classmethod
        def setup_classes(cls):
            Base = cls.DeclarativeBasic

            class Book(Base):
                __tablename__ = 'book'
                id = Column(Integer, primary_key=True)
                authors = relationship(
                    'Author', secondary='book_author', back_populates='books')

            class BookAuthor(Base):
                __tablename__ = 'book_author'
                authorid = Column(ForeignKey('author.id'), primary_key=True)
                bookid = Column(ForeignKey('book.id'), primary_key=True)

            class Author(Base):
                __tablename__ = 'author'
                id = Column(Integer, primary_key=True)
                books = relationship(
                    'Book', secondary='book_author', back_populates='authors')

        def test_update_many_to_many_sharded(self):
            session = ShardedSession(
                shards={"test": testing.db},
                shard_chooser=self.shard_chooser,
                id_chooser=lambda *args: None,
                query_chooser=lambda *args: ['test']
            )

            Book, Author = self.classes("Book", "Author")
            book = Book()
            book.authors.append(Author())

            session.add(book)
            session.commit()

        def test_update_many_to_many_sharded__save_junction_table_directly(self):
            session = ShardedSession(
                shards={"test": testing.db},
                shard_chooser=self.shard_chooser,
                id_chooser=lambda *args: None,
                query_chooser=lambda *args: ['test']
            )

            Book, Author, BookAuthor = self.classes("Book", "Author", "BookAuthor")

            book = Book()
            author = Author()

            session.add(book)
            session.add(author)
            session.commit()

            book_author = BookAuthor()
            book_author.bookid = book.id
            book_author.authorid = author.id

            session.add(book_author)
            session.commit()

        def shard_chooser(self, mapper, instance, clause=None):
            if not instance and not clause:
                raise Exception('Cannot determine shard')

            return 'test'

Both tests end up with a similar stack trace:

    ======================================================================
    ERROR: test.orm.test_sharded_m2m.UpdateManyToManyShardedTest.test_update_many_to_many_sharded__save_junction_table_directly
    ----------------------------------------------------------------------
    Traceback (most recent call last):
      File "/Users/hkormano/Library/Python/2.7/lib/python/site-packages/nose/case.py", line 197, in runTest
        self.test(*self.arg)
      File "/Users/hkormano/Projects/sqlalchemy/test/orm/test_sharded_m2m.py", line 60, in test_update_many_to_many_sharded__save_junction_table_directly
        session.commit()
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 954, in commit
        self.transaction.commit()
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 467, in commit
        self._prepare_impl()
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 447, in _prepare_impl
        self.session.flush()
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2313, in flush
        self._flush(objects)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2440, in _flush
        transaction.rollback(_capture_exception=True)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/util/langhelpers.py", line 66, in __exit__
        compat.reraise(exc_type, exc_value, exc_tb)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2404, in _flush
        flush_context.execute()
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 398, in execute
        rec.execute(self)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 512, in execute
        self.dependency_processor.process_deletes(uow, states)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/dependency.py", line 1025, in process_deletes
        secondary_update, secondary_delete)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/dependency.py", line 1096, in _run_crud
        connection = uowcommit.transaction.connection(self.mapper)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 293, in connection
        bind = self.session.get_bind(bindkey, **kwargs)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/ext/horizontal_shard.py", line 237, in get_bind
        mapper, instance, clause=clause)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/ext/horizontal_shard.py", line 215, in _choose_shard_and_assign
        shard_id = self.shard_chooser(mapper, instance, **kw)
      File "/Users/hkormano/Projects/sqlalchemy/test/orm/test_sharded_m2m.py", line 71, in shard_chooser
        raise Exception('Cannot determine shard')
    Exception: Cannot determine shard

[Sqlalchemy-tickets] Issue #4375: changes in git are making requirements.txt in docs/build not work (zzzeek/sqlalchemy)

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

New issue 4375: changes in git are making requirements.txt in docs/build not work
https://bitbucket.org/zzzeek/sqlalchemy/issues/4375/changes-in-git-are-making-requirementstxt

Michael Bayer:

with git version 2.17.2 that "HEAD" indicator is broken:


```
#!python

$ .venv/bin/pip install -r requirements.txt 
Collecting changelog from git+https://bitbucket.org/zzzeek/changelog.git@HEAD#egg=changelog (from -r requirements.txt (line 1))
  Cloning https://bitbucket.org/zzzeek/changelog.git (to revision HEAD) to /tmp/pip-install-xopc8e4h/changelog
fatal: 'HEAD' is not a valid branch name.
Command "git checkout -b HEAD --track origin/HEAD" failed with error code 128 in /tmp/pip-install-xopc8e4h/changelog

```

this has to be updated back to SQLAlchemy 0.9 and additonall needs to go into dogpile, mako, alembic, they all have the same non-working format

[Sqlalchemy-tickets] Issue #4374: Feature Request: Issue warning when mapper discovers a member that is a function. (zzzeek/sqlalchemy)

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

New issue 4374: Feature Request: Issue warning when mapper discovers a member that is a function.
https://bitbucket.org/zzzeek/sqlalchemy/issues/4374/feature-request-issue-warning-when-mapper

Dave Scotese:

My code required a capital C in "Column" but I used a lower-case c, and this resulted in my database table NOT containing the column I defined.  Ultimately, this appeared as "sqlalchemy.exc.NoForeignKeysError" because the column that didn't get put into my table is the foreign key.  The code that reference the missing column may have created a problem that wasn't reported in the Werkzeug Traceback, or it may have been reported in a way that I didn't see.  Here is the end of it:

    sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Resource.locale - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

I finally examined the database and discovered there was no column and traced that problem back to the bad letter case.  I explored the two identifiers (Column and column):

    >>> import sqlalchemy
    >>> from sqlalchemy import create_engine
    >>> e = create_engine('sqlite:///:memory:', echo=True)
    >>> from sqlalchemy.ext.declarative import declarative_base
    >>> Base = declarative_base()
    >>>  from sqlalchemy import Column, Integer, String
      File "<stdin>", line 1
        from sqlalchemy import Column, Integer, String
        ^
    IndentationError: unexpected indent
    >>> from sqlalchemy import Column, Integer, String
    >>> from sqlalchemy import column
    >>> column(Integer, primary_key=True)
    Traceback (most recent call last):
      File "<stdin>", line 1, in <module>
    TypeError: column() got an unexpected keyword argument 'primary_key'
    >>> column(Integer)
    <sqlalchemy.sql.elements.ColumnClause at 0x7f8acaeafac8; <class 'sqlalchemy.sql.sqltypes.Integer'>>
    >>> column
    <function column at 0x7f8acafccbf8>
    >>> Column
    <class 'sqlalchemy.sql.schema.Column'>

So it seems that when SQLAlchemy examines a mapped class, it can find a member of the class that is a function ("column") or an instance of a class ("Column"), and it is silent in both cases.  It may be useful to declare a member of a mapped class to be a function, but it seems it would be useful for SQLAlchemy to issue a warning when that happens.

[Sqlalchemy-tickets] Issue #4373: Load.load_only on polymorphic child doesn't work (zzzeek/sqlalchemy)

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

New issue 4373: Load.load_only on polymorphic child doesn't work
https://bitbucket.org/zzzeek/sqlalchemy/issues/4373/loadload_only-on-polymorphic-child-doesnt

Dmytro Starosud:

This is using SQLAlchemy==1.2.14.
Please consider following code snippet:

```
#!python
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Load, Query
from sqlalchemy.pool import NullPool

engine = create_engine('postgresql://***', poolclass=NullPool)
connection = engine.connect()
connection.begin_nested()

Base = declarative_base()


class MyEntity(Base):
    __tablename__ = 'my_entity'

    id = Column(Integer, primary_key=True)

    a = Column(String, nullable=False)
    b = Column(String, nullable=False)
    kind = Column(String, nullable=False)

    __mapper_args__ = {'polymorphic_on': kind}


Base.metadata.create_all(connection)


class MyChild(MyEntity):
    __mapper_args__ = {'polymorphic_identity': 'child_1'}


print(MyEntity)
print(Query(MyEntity).options(Load(MyEntity).load_only('a')))
print(MyChild)
print(Query(MyChild).options(Load(MyChild).load_only('a')))
```

It produces following output:


```
<class '__main__.MyEntity'>
SELECT my_entity.id AS my_entity_id, my_entity.a AS my_entity_a, my_entity.kind AS my_entity_kind
FROM my_entity
<class '__main__.MyChild'>
SELECT my_entity.id AS my_entity_id, my_entity.a AS my_entity_a, my_entity.b AS my_entity_b, my_entity.kind AS my_entity_kind
FROM my_entity
WHERE my_entity.kind IN (:kind_1)
```

Please assist. Did I misread documentation? Note unbound `load_only('a')` works well.

[Sqlalchemy-tickets] Issue #4372: deferred Column attribute in CheckConstraint (zzzeek/sqlalchemy)

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

New issue 4372: deferred Column attribute in CheckConstraint
https://bitbucket.org/zzzeek/sqlalchemy/issues/4372/deferred-column-attribute-in

Dmytro Starosud:

This is using SQLAlchemy==1.2.14.

```
#!python

from sqlalchemy import CheckConstraint, Column, DateTime, Integer, create_engine, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import deferred
from sqlalchemy.pool import NullPool

engine = create_engine('postgresql://***', poolclass=NullPool)
connection = engine.connect()
connection.begin_nested()

Base = declarative_base()


class MyEntity(Base):
    __tablename__ = 'my_entity'

    id = Column(Integer, primary_key=True)

    _internal_modified_at = deferred(Column(
        DateTime(timezone=True), nullable=False, index=True,
        server_default=func.now(), onupdate=func.now()))

    __table_args__ = (
        CheckConstraint(
            _internal_modified_at == func.now(),
            'my_entity_is_modified_now'),
    )


Base.metadata.create_all(connection)
```

Last line produces exception:


```
root@0d982765aeb0:/service# python main.py
/usr/local/lib/python3.5/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)
Traceback (most recent call last):
  File "main.py", line 29, in <module>
    Base.metadata.create_all(connection)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 4005, in create_all
    tables=tables)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1549, in _run_visitor
    **kwargs).traverse_single(element)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 757, in visit_metadata
    _is_metadata_operation=True)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 791, in visit_table
    include_foreign_key_constraints=include_foreign_key_constraints
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1003, in _execute_ddl
    if not self.schema_for_object.is_default else None)
  File "<string>", line 1, in <lambda>
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 442, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 26, in _compiler
    return dialect.ddl_compiler(dialect, self, **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 219, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 245, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 2502, in visit_create_table
    create.include_foreign_key_constraints)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 2549, in create_table_constraints
    for constraint in constraints
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 2547, in <genexpr>
    p for p in
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 2555, in <genexpr>
    not getattr(constraint, 'use_alter', False)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 245, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 93, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 2732, in visit_check_constraint
    literal_binds=True)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 245, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1078, in visit_binary
    return self._generate_generic_binary(binary, opstring, **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1111, in _generate_generic_binary
    self, eager_grouping=eager_grouping, **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1242, in visit_bindparam
    bindparam, within_columns_clause=True, **kwargs)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1275, in render_literal_bindparam
    return self.render_literal_value(value, bindparam.type)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/dialects/postgresql/base.py", line 1490, in render_literal_value
    value = super(PGCompiler, self).render_literal_value(value, type_)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1293, in render_literal_value
    "Don't know how to literal-quote value %r" % value)
NotImplementedError: Don't know how to literal-quote value <ColumnProperty at 0x7f12c9ff8dd8; _internal_modified_at>
```

[Sqlalchemy-tickets] Issue #4371: SQLAlchemy fails to ignores parameters that are inside of SQL Text comments in SQLAlchemy 1.2+? (zzzeek/sqlalchemy)

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

New issue 4371: SQLAlchemy fails to ignores parameters that are inside of SQL Text comments in SQLAlchemy 1.2+?
https://bitbucket.org/zzzeek/sqlalchemy/issues/4371/sqlalchemy-fails-to-ignores-parameters

cowbert:

Given a SQL textstring of:

```
#!python

s = sqlalchemy.sql.text("""With t as ( SELECT 'value1' v from dual union select 'value2' v 
    from dual)
    --, comment_cte as ("select 1 from dual")

        select * from t 
        --where t = :param_t
""")

```

In earlier versions of SQLAlchemy (1.1) and cx_Oracle (5.x), this can be executed by  SQLAlchemy core with:

    engine.connect().execute(s)

In SQLAlchemy 1.2+ and cxOracle 7, this will return an error reference to:  http://sqlalche.me/e/cd3x

SQLAlchemy properly ignores comments otherwise (for example, deleting the line `--where t = :param_t` will execute properly.)  Changing `--` style comments to the `/* */` style results in the same error.

Env: Python 3.6.4, SQLAlchemy 1.2.14, cx-Oracle 7.0.0

Full stack trace:

```
#!python

Traceback (most recent call last):
  File "/usr/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1127, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 631, in _init_compiled
    self.compiled_parameters = [compiled.construct_params()]
  File "/usr/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 566, in construct_params
    % bindparam.key, code="cd3x")
sqlalchemy.exc.InvalidRequestError: A value is required for bind parameter 'param_t' (Background on this error at: http://sqlalche.me/e/cd3x)

```

[Sqlalchemy-tickets] Issue #4370: sqlite 3.25.x docs/tutorial.rst doctests fail (zzzeek/sqlalchemy)

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

New issue 4370: sqlite 3.25.x docs/tutorial.rst doctests fail
https://bitbucket.org/zzzeek/sqlalchemy/issues/4370/sqlite-325x-docs-tutorialrst-doctests-fail

Andreas Rammhold:

While bumping SQLAlchemy (& sqlite) in the NixOS Package repository (https://github.com/NixOS/nixpkgs/pull/46852) we have been seeing one particular test (`DocTest.test_core`) failing whenever we test with sqlite >= 3.25.  Succeeding the tests is a mandatory step for the package to be bumped.

SQLAlchemy version: 1.2.13

SQLite version: 2.35.3


### Steps to reproduce

After checking out https://github.com/NixOS/nixpkgs/pull/46852 and building it using `nix-build -I nixpkgs=. -A python3Package.sqlalchemy` the following error will appear after some time:

```
=================================== FAILURES ===================================
______________________________ DocTest.test_core _______________________________
Traceback (most recent call last):
  File "/build/SQLAlchemy-1.2.13/test/base/test_tutorials.py", line 86, in test_core
    self._run_doctest("core/tutorial.rst")
  File "/build/SQLAlchemy-1.2.13/test/base/test_tutorials.py", line 80, in _run_doctest
    self._run_doctest_for_content(fname, content)
  File "/build/SQLAlchemy-1.2.13/test/base/test_tutorials.py", line 69, in _run_doctest_for_content
    assert not runner.failures
AssertionError: assert not 2
 +  where 2 = <doctest.DocTestRunner object at 0x7fffeb84db70>.failures
----------------------------- Captured stdout call -----------------------------
2018-11-15 00:58:09,857 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2018-11-15 00:58:09,857 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,858 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2018-11-15 00:58:09,858 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,859 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("users")
2018-11-15 00:58:09,859 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,860 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("addresses")
2018-11-15 00:58:09,860 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,861 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE users (
	id INTEGER NOT NULL, 
	name VARCHAR, 
	fullname VARCHAR, 
	PRIMARY KEY (id)
)


2018-11-15 00:58:09,862 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,862 INFO sqlalchemy.engine.base.Engine COMMIT
2018-11-15 00:58:09,863 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE addresses (
	id INTEGER NOT NULL, 
	user_id INTEGER, 
	email_address VARCHAR NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES users (id)
)


2018-11-15 00:58:09,863 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,864 INFO sqlalchemy.engine.base.Engine COMMIT
2018-11-15 00:58:09,867 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname) VALUES (?, ?)
2018-11-15 00:58:09,868 INFO sqlalchemy.engine.base.Engine ('jack', 'Jack Jones')
2018-11-15 00:58:09,868 INFO sqlalchemy.engine.base.Engine COMMIT
2018-11-15 00:58:09,869 INFO sqlalchemy.engine.base.Engine INSERT INTO users (id, name, fullname) VALUES (?, ?, ?)
2018-11-15 00:58:09,870 INFO sqlalchemy.engine.base.Engine (2, 'wendy', 'Wendy Williams')
2018-11-15 00:58:09,870 INFO sqlalchemy.engine.base.Engine COMMIT
2018-11-15 00:58:09,880 INFO sqlalchemy.engine.base.Engine INSERT INTO addresses (user_id, email_address) VALUES (?, ?)
2018-11-15 00:58:09,881 INFO sqlalchemy.engine.base.Engine ((1, 'ja...@ya...'), (1, 'ja...@ms...'), (2, 'ww...@ww...'), (2, 'we...@ao...'))
2018-11-15 00:58:09,881 INFO sqlalchemy.engine.base.Engine COMMIT
2018-11-15 00:58:09,882 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname 
FROM users
2018-11-15 00:58:09,882 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,897 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname 
FROM users
2018-11-15 00:58:09,897 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,899 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname 
FROM users
2018-11-15 00:58:09,899 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,900 INFO sqlalchemy.engine.base.Engine SELECT users.name, users.fullname 
FROM users
2018-11-15 00:58:09,900 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,902 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address 
FROM users, addresses
2018-11-15 00:58:09,902 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,904 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address 
FROM users, addresses 
WHERE users.id = addresses.user_id
2018-11-15 00:58:09,904 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,916 INFO sqlalchemy.engine.base.Engine SELECT users.fullname || ? || addresses.email_address AS title 
FROM users, addresses 
WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
2018-11-15 00:58:09,917 INFO sqlalchemy.engine.base.Engine (', ', 'm', 'z', '%@aol.com', '%@msn.com')
2018-11-15 00:58:09,920 INFO sqlalchemy.engine.base.Engine SELECT users.fullname || ? || addresses.email_address AS title 
FROM users, addresses 
WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
2018-11-15 00:58:09,920 INFO sqlalchemy.engine.base.Engine (', ', 'm', 'z', '%@aol.com', '%@msn.com')
2018-11-15 00:58:09,922 INFO sqlalchemy.engine.base.Engine SELECT users.fullname || ', ' || addresses.email_address AS title FROM users, addresses WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
2018-11-15 00:58:09,922 INFO sqlalchemy.engine.base.Engine ('m', 'z', '%@aol.com', '%@msn.com')
2018-11-15 00:58:09,924 INFO sqlalchemy.engine.base.Engine SELECT users.id, addresses.id, users.id, users.name, addresses.email_address AS email FROM users JOIN addresses ON users.id=addresses.user_id WHERE users.id = 1
2018-11-15 00:58:09,924 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,929 INFO sqlalchemy.engine.base.Engine SELECT users.fullname || ', ' || addresses.email_address AS title 
FROM users, addresses 
WHERE users.id = addresses.user_id AND users.name BETWEEN 'm' AND 'z' AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
2018-11-15 00:58:09,929 INFO sqlalchemy.engine.base.Engine ('%@aol.com', '%@msn.com')
2018-11-15 00:58:09,932 INFO sqlalchemy.engine.base.Engine SELECT users.fullname || ? || addresses.email_address AS anon_1 
FROM users, addresses 
WHERE users.id = addresses.user_id AND users.name BETWEEN 'm' AND 'z' AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
2018-11-15 00:58:09,933 INFO sqlalchemy.engine.base.Engine (', ', '%@aol.com', '%@msn.com')
2018-11-15 00:58:09,935 INFO sqlalchemy.engine.base.Engine SELECT addresses.user_id, count(addresses.id) AS num_addresses 
FROM addresses ORDER BY num_addresses
2018-11-15 00:58:09,936 INFO sqlalchemy.engine.base.Engine ()
**********************************************************************
File "core/tutorial.rst", line 1037, in core/tutorial.rst
Failed example:
    conn.execute(stmt).fetchall()
Expected:
    SELECT addresses.user_id, count(addresses.id) AS num_addresses
    FROM addresses ORDER BY num_addresses
    ()
    [(2, 4)]
Got:
    SELECT addresses.user_id, count(addresses.id) AS num_addresses 
    FROM addresses ORDER BY num_addresses
    ()
    [(1, 4)]
2018-11-15 00:58:09,939 INFO sqlalchemy.engine.base.Engine SELECT addresses.user_id, count(addresses.id) AS num_addresses 
FROM addresses ORDER BY num_addresses DESC
2018-11-15 00:58:09,939 INFO sqlalchemy.engine.base.Engine ()
**********************************************************************
File "core/tutorial.rst", line 1054, in core/tutorial.rst
Failed example:
    conn.execute(stmt).fetchall()
Expected:
    SELECT addresses.user_id, count(addresses.id) AS num_addresses
    FROM addresses ORDER BY num_addresses DESC
    ()
    [(2, 4)]
Got:
    SELECT addresses.user_id, count(addresses.id) AS num_addresses 
    FROM addresses ORDER BY num_addresses DESC
    ()
    [(1, 4)]
```

The full log can be retrieved here:
https://gist.githubusercontent.com/andir/b4b7f1ef355899c7972bb5ead67afb0f/raw

Looking at the documentation for this doctest one of the queries that is executed origins from the following lines:

```
>>> from sqlalchemy import func
>>> stmt = select([
...         addresses.c.user_id,
...         func.count(addresses.c.id).label('num_addresses')]).\
...         order_by("num_addresses")
```

The difference in output is in the first element of the first tuple. It should represent `addresses.user_id`. Given that there is no ordering, grouping further restriction of the value of that field I'd guess that any value could be presented here. It could probably be explained as "undefined behavior"? In that case the assumption of the test is wrong and should allow for any value from the set of available values (1 or 2).

We believe this could be a bug within the testsuite.

[Sqlalchemy-tickets] Issue #4369: remove depreacted cx_Oracle connect params, let all keywords through (zzzeek/sqlalchemy)

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

New issue 4369: remove depreacted cx_Oracle connect params, let all keywords through
https://bitbucket.org/zzzeek/sqlalchemy/issues/4369/remove-depreacted-cx_oracle-connect-params

Michael Bayer:

cx_Oracle is moving very fast these days and it now allows a lot of new arguments: https://cx-oracle.readthedocs.io/en/latest/module.html#cx_Oracle.Connection

we need to at least allow connect_args to travel through generically and we should also string-coerece the ones that are listed in the above document.

Additionally, we should not be setting threaded=True.   SQLAlchemy connections are not threadsafe in any case.   Allow it as an option, make sure we document that this changed.

Remove all the unused and warned as deprecated params auto_setinputsizes, allow_twophase, "threaded" as  a dialect flag (what's that about anyway?), 

use_ansi really should not be part of connect_args, it's a dialect flag, as well as auto_convert_lobs.   as a bonus, deprecate that these are part of connect_args, but this is not critical.

[Sqlalchemy-tickets] Issue #4368: Connection reset stale while recycling pool connection (zzzeek/sqlalchemy)

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

New issue 4368: Connection reset stale while recycling pool connection
https://bitbucket.org/zzzeek/sqlalchemy/issues/4368/connection-reset-stale-while-recycling

Carlo Suriano:

I am experiencing long periods of stale on customer production environment due to recycling pool parameter. The architecture of the solution is the following:

1. Different concurrent tasks are launched on a job scheduler which has 4 workers. Each worker creates a session pool with SQLAlchemy `create_engine(<connection string>, pool_size=8, max_overflow=0, pool_recycle=3600, pool_timeout=30)`

2. Each task executes a complex workflow, which consists of SELECT queries in cascade;

3. The underlying DB is an Oracle RAC with failover and three available addresses for each of the two failover nodes;

Randomically, during the execution of a task, the pool recycler verify the overcoming of the 3600 timeout and triggers the recycling of the connection. This causes a stale that lasts about 15 minutes (sistematically) after which SQLAlchemy succeeds to close and re-open connection and then continues the execution.

This is an excerpt of the library logger:
```
[myPID]	YYYY-MM-DD 12:07:59,974	pool.py: 513	INFO	Connection <cx_Oracle.Connection to <my_connection_string>> exceeded timeout; recycling
[myPID]	YYYY-MM-DD 12:07:59,974	pool.py: 248	DEBUG	Closing connection <cx_Oracle.Connection to <my_connection_string>>
[myPID]	YYYY-MM-DD 12:23:28,294	pool.py: 255	ERROR	Exception closing connection <cx_Oracle.Connection to <my_connection_string>>
Traceback (most recent call last):
  File "myEnv/lib/python2.7/site-packages/sqlalchemy/pool.py", line 250, in _close_connection
    self._dialect.do_close(connection)
  File "myEnv/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 412, in do_close
    dbapi_connection.close()
DatabaseError: ORA-25408: can not safely replay call

[myPID]	YYYY-MM-DD 12:23:28,401	pool.py: 538	DEBUG	Created new connection <cx_Oracle.Connection to <my_connection_string>>
[myPID]	YYYY-MM-DD 12:23:28,405	base.py: 487	INFO	BEGIN (implicit)
[myPID]	YYYY-MM-DD 12:23:28,406	base.py: 912	INFO	
                    SELECT XXX
                    FROM YYY
                    WHERE ZZZ
```

Since the connection string is constant for each object of the pool, it is not possible to distinguish between the connection being handled on the logs.

A quick fix is to set the recycle param at -1, but could the stale be due to wrong management of the failover on SQLAlchemy?

[Sqlalchemy-tickets] Issue #4367: aliased join leaks into any() / has(), only when using declarative (zzzeek/sqlalchemy)

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

New issue 4367: aliased join leaks into any() / has(), only when using declarative
https://bitbucket.org/zzzeek/sqlalchemy/issues/4367/aliased-join-leaks-into-any-has-only-when

Michael Bayer:

the tests we've had for years in https://github.com/zzzeek/sqlalchemy/blob/master/test/orm/test_query.py#L2382 fails on declarative only; the columns contain the "parententity" annotation when using declarative, only "parentmapper" when not.   I have no idea what this is about.

test:

```
#!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 C(Base):
    __tablename__ = 'c'
    id = Column(Integer, primary_key=True)


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

    c = relationship("C")

s = Session()


print(
    s.query(B).join(B.c, aliased=True).filter(B.c.has(C.id == 1))
)
```


classical:


```
#!python

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

m = MetaData()

c = Table(
    'c', m,
    Column('id', Integer, primary_key=True)
)

b = Table(
    'b', m,
    Column('id', Integer, primary_key=True),
    Column('c_id', ForeignKey('c.id'))
)

class C(object):
    pass

class B(object):
    pass

mapper(B, b, properties={
    'c': relationship(C)
})

mapper(C, c)

s = Session()


print(
    s.query(B).join(B.c, aliased=True).filter(B.c.has(C.id == 1))
)

```

outputs:

```
#!python


[classic@photon2 sqlalchemy]$ python test2.py 
SELECT b.id AS b_id, b.c_id AS b_c_id 
FROM b JOIN c AS c_1 ON c_1.id = b.c_id 
WHERE EXISTS (SELECT 1 
FROM c 
WHERE c_1.id = b.c_id AND c.id = :id_1)
[classic@photon2 sqlalchemy]$ python test3.py 
SELECT b.id AS b_id, b.c_id AS b_c_id 
FROM b JOIN c AS c_1 ON c_1.id = b.c_id 
WHERE EXISTS (SELECT 1 
FROM c 
WHERE c.id = b.c_id AND c.id = :id_1)

```

[Sqlalchemy-tickets] Issue #4366: Use of .has/.any with composite secondary join (zzzeek/sqlalchemy)

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

New issue 4366: Use of .has/.any with composite secondary join
https://bitbucket.org/zzzeek/sqlalchemy/issues/4366/use-of-has-any-with-composite-secondary

Fred Ludlow:

Hi, 
Many times in the past I've started writing a sqlalchemy bug report and realised it was me! - this time I think it might just be a real bug...

I've attached a minimal example which hopefully explains it. python 2.7.15, sqla 1.2.13

I'm trying to create a relationship between a child (A) and it's great-grandparent (D), using a secondary join of the intermediate tables (B and C). 

This loads data correctly, but if I try and filter using .has (.any) on the relationship, it constructs an exists clause but doesn't apply the join condition within the secondary object (i.e. the B-C join).  As a result I think it ends up effectively doing "A inner join B full outer join C inner join D".

```python
from sqlalchemy import and_, create_engine, Integer, Column, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine("sqlite:///", echo=True)
Base = declarative_base()
Base.metadata.bind = engine
Session = sessionmaker()
session = Session()


class D(Base):
    __tablename__ = 'd'
    id = Column(Integer, primary_key=True)


class C(Base):
    __tablename__ = 'c'
    id = Column(Integer, primary_key=True)
    d_id = Column(ForeignKey(D.id))


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


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    b_id = Column(ForeignKey(B.id))
    
    d = relationship(
        'D',
        secondary="join(B, C)",
        primaryjoin="A.b_id == B.id",
        secondaryjoin="C.d_id == D.id",
        uselist=False)

    d_fixed = relationship(
        'D',
        secondary="join(B, C)",
        primaryjoin="A.b_id == B.id",
        secondaryjoin="and_(B.c_id == C.id, C.d_id == D.id)",
        uselist=False)



Base.metadata.create_all()

```
This prints:

Explicit join, correct behaviour:
```python
print session.query(A).join(A.d).filter(D.id==1)
```
```sql
SELECT a.id AS a_id, a.b_id AS a_b_id 
FROM a JOIN (b AS b_1 JOIN c AS c_1 ON c_1.id = b_1.c_id) ON a.b_id = b_1.id JOIN d ON c_1.d_id = d.id 
WHERE d.id = ?
```

Using has, missing b-c join condition
```python
print session.query(A).filter(A.d.has(D.id==1))
```
```sql
SELECT a.id AS a_id, a.b_id AS a_b_id 
FROM a 
WHERE EXISTS (SELECT 1 
FROM b, c, d 
WHERE a.b_id = b.id AND c.d_id = d.id AND d.id = ?)
```

Putting b-c join into secondaryjoin, fixes has/any query:
```python
print session.query(A).filter(A.d_fixed.has(D.id==1))
```
```sql
SELECT a.id AS a_id, a.b_id AS a_b_id 
FROM a 
WHERE EXISTS (SELECT 1 
FROM b, c, d 
WHERE a.b_id = b.id AND b.c_id = c.id AND c.d_id = d.id AND d.id = ?)
```

But the b-c join condition now appears twice when using query(A).join(A.d)
```python

```
print session.query(A).join(A.d_fixed).filter(D.id==1)
```sql
SELECT a.id AS a_id, a.b_id AS a_b_id 
FROM a JOIN (b AS b_1 JOIN c AS c_1 ON c_1.id = b_1.c_id) ON a.b_id = b_1.id JOIN d ON b_1.c_id = c_1.id AND c_1.d_id = d.id 
WHERE d.id = ?
```

I'm guessing this is related to #4349 and #4363

[Sqlalchemy-tickets] Issue #4365: dont assume _from_obj[0] when joining without a clear "left" side (zzzeek/sqlalchemy)

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

New issue 4365: dont assume _from_obj[0] when joining without a clear "left" side
https://bitbucket.org/zzzeek/sqlalchemy/issues/4365/dont-assume-_from_obj-0-when-joining

Michael Bayer:

this would allow us to fix the hack in #4363


```
#!python

    def test_join_entity_from_multiple_from_clause(self):
        """test adding joins onto multiple FROM clauses"""

        User, Address = (self.classes.User,
                         self.classes.Address)
        sess = create_session()

        q = sess.query(Address, User).join(Address.dingaling).\
            join(User.orders)

        a1 = aliased(Address)

        assert_raises_message(
            sa.exc.InvalidRequestError,
            "Can't determine which FROM clause to join from, there are "
            "multiple FROMS which can join to this entity",
            q.join, a1
        )

    def test_join_entity_w_onclause_from_multiple_from_clause(self):
        """test adding joins onto multiple FROM clauses"""

        User, Order, Address = (self.classes.User,
                                self.classes.Order,
                                self.classes.Address)
        sess = create_session()

        q = sess.query(Address, User).join(Address.dingaling).\
            join(User.orders)

        a1 = aliased(Address)

        q = q.join(a1, Order.address_id == a1.id)
        self.assert_compile(
            q,
            "< query works >"
        )

```

[Sqlalchemy-tickets] Issue #4364: Code completion in IDEs with Session objects doesn't work (zzzeek/sqlalchemy)

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

New issue 4364: Code completion in IDEs with Session objects doesn't work
https://bitbucket.org/zzzeek/sqlalchemy/issues/4364/code-completion-in-ides-with-session

Michael Rans:

If I create a Session object, I do not get code completion for it in my IDE (PyCharm) even if I add a type hint.

```
#!python

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import NullPool

class HDXBase(object):
    @declared_attr
    def __tablename__(cls):
        return '%ss' % cls.__name__.lower()

engine = create_engine('sqlite:///test.db', poolclass=NullPool, echo=False)
Session = sessionmaker(bind=engine)
Base = declarative_base(cls=HDXBase)
Base.metadata.create_all(engine)
session = Session()
```
If after entering the above I type "session." in PyCharm, there are no options given from the Session class. 

```
#!python

def get_session(db_url):
    # type: (str) -> sqlalchemy.orm.session.Session
    engine = create_engine(db_url, poolclass=NullPool, echo=False)
    Session = sessionmaker(bind=engine)
    Base = declarative_base(cls=HDXBase)
    Base.metadata.create_all(engine)
    return Session()

session = get_session('sqlite:///test.db')
```

If after entering the above I type "session." in PyCharm, there are no options given from the Session class.  Also I notice an error "Cannot find reference 'orm' in '__init__.py'"

[Sqlalchemy-tickets] Issue #4363: select from clause change when I update version from 1.2.12 to 1.2.13 (zzzeek/sqlalchemy)

[大易归真 <iss...@bi...>]

New issue 4363: select from clause change when I update version from 1.2.12 to 1.2.13
https://bitbucket.org/zzzeek/sqlalchemy/issues/4363/select-from-clause-change-when-i-update

大易归真:

My query code is:
```
funcs = aliased(Function)
mapping = db.session.query(FunctionMapping).get(1)
foreign_funcs = mapping.peers.join(ForeignFunction.functions).join(funcs)
```
When use version 1.2.12:

The SQL is

```
SELECT ... 
FROM foreign_function_set, foreign_function INNER JOIN foreign_function_map AS foreign_function_map_1 ON foreign_function.id = foreign_function_map_1.foreign_function_id INNER JOIN function ON function.id = foreign_function_map_1.function_id INNER JOIN function AS function_2 ON function.id = function_2.parent_id AND function_2.id = function.parent_id LEFT OUTER JOIN (foreign_function_map AS foreign_function_map_2 INNER JOIN function AS function_1 ON function_1.id = foreign_function_map_2.function_id) ON foreign_function.id = foreign_function_map_2.foreign_function_id
```

When I update sqlalchemy version to 1.2.13, The SQL is:

```
SELECT ...
FROM foreign_function_set INNER JOIN `function` AS function_2 ON function_2.id = `function`.id OR function_2.function_id = `function`.id OR function_2.function_group_id = `function`.id, foreign_function INNER JOIN foreign_function_map AS foreign_function_map_1 ON foreign_function.id = foreign_function_map_1.foreign_function_id INNER JOIN `function` ON `function`.id = foreign_function_map_1.function_id LEFT OUTER JOIN (foreign_function_map AS foreign_function_map_2 INNER JOIN `function` AS function_1 ON function_1.id = foreign_function_map_2.function_id) ON foreign_function.id = foreign_function_map_2.foreign_function_id
```

The program raise error `u"Unknown column 'function.id' in 'on clause'"`

I find 1.2.13 add `_from_obj ` to query, but don't understand how this attribute change SQL?


This is class and secondary defina

```
#!python

class Function(db.Model):
    id = db.Column(db.SmallInteger, autoincrement=True, primary_key=True)

    function_id = db.Column(db.SmallInteger)
    function_group_id = db.Column(db.SmallInteger)

    parent_id = db.Column(db.SmallInteger, db.ForeignKey('function.id'))
    children = db.relationship(
        'Function', lazy='dynamic', backref=db.backref(
             'parent', remote_side=[id]))


foreign_function_map = db.Table('foreign_function_map', db.Column(
    'foreign_function_id', db.SmallInteger, db.ForeignKey(
        'foreign_function.id', ondelete='CASCADE'), primary_key=True
    ), db.Column(
        'function_id', db.SmallInteger, db.ForeignKey('function.id'),
        primary_key=True
    ))


class ForeignFunction(db.Model):
    id = db.Column(db.SmallInteger, autoincrement=True, primary_key=True)
    functions = db.relationship(
        'Function', secondary=foreign_function_map, lazy='joined')


foreign_function_set = db.Table('foreign_function_set', db.Column(
    'function_mapping_id', db.SmallInteger, db.ForeignKey(
        'function_mapping.id'), primary_key=True), db.Column(
    'foreign_function_id', db.SmallInteger, db.ForeignKey(
        'foreign_function.id'), primary_key=True))


class FunctionMapping(db.Model):
    id = db.Column(db.SmallInteger, autoincrement=True, primary_key=True)
    key = db.Column(db.String(20), index=True, unique=True)
    peers = db.relationship(
        'ForeignFunction', secondary=foreign_function_set, lazy='dynamic',
        backref=db.backref('site'))
```

[Sqlalchemy-tickets] Issue #4362: add new API for IDENTITY start/increment in mssql (zzzeek/sqlalchemy)

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

New issue 4362: add new API for IDENTITY start/increment in mssql
https://bitbucket.org/zzzeek/sqlalchemy/issues/4362/add-new-api-for-identity-start-increment

Michael Bayer:

in #4235 we need Sequence to be a real object for SQL Server so we need to deprecate its current use for IDENTITIY, propose new column params mssql_identity_start, mssql_identity_increment.   need deprecation warnings for Sequence

[Sqlalchemy-tickets] Issue #4361: mysql 8.0 fix likely breaks on case insensitive (zzzeek/sqlalchemy)

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

New issue 4361: mysql 8.0 fix likely breaks on case insensitive
https://bitbucket.org/zzzeek/sqlalchemy/issues/4361/mysql-80-fix-likely-breaks-on-case

Michael Bayer:

e.g. #4344, we have this:


```
#!python

            correct_for_wrong_fk_case = connection.execute(
                sql.text("""
                    select table_schema, table_name, column_name
                    from information_schema.columns
                    where (table_schema, table_name, lower(column_name)) in
                    :table_data;
                """).bindparams(
                    sql.bindparam("table_data", expanding=True)
                ), table_data=col_tuples
            )

```

if table names/schema are stored in lower case that will fail.  user reporting this stack trace:

```
#!


INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
Traceback (most recent call last):
  File "---/venv/bin/alembic", line 11, in <module>
    load_entry_point('alembic==1.0.2', 'console_scripts', 'alembic')()
  File "---/venv/lib/python3.7/site-packages/alembic/config.py", line 502, in main
    CommandLine(prog=prog).main(argv=argv)
  File "---/venv/lib/python3.7/site-packages/alembic/config.py", line 496, in main
    self.run_cmd(cfg, options)
  File "---/venv/lib/python3.7/site-packages/alembic/config.py", line 479, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "---/venv/lib/python3.7/site-packages/alembic/command.py", line 176, in revision
    script_directory.run_env()
  File "---/venv/lib/python3.7/site-packages/alembic/script/base.py", line 427, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "---/venv/lib/python3.7/site-packages/alembic/util/pyfiles.py", line 81, in load_python_file
    module = load_module_py(module_id, path)
  File "---/venv/lib/python3.7/site-packages/alembic/util/compat.py", line 82, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 728, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "alembic/env.py", line 75, in <module>
    run_migrations_online()
  File "alembic/env.py", line 70, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "---/venv/lib/python3.7/site-packages/alembic/runtime/environment.py", line 836, in run_migrations
    self.get_context().run_migrations(**kw)
  File "---/venv/lib/python3.7/site-packages/alembic/runtime/migration.py", line 321, in run_migrations
    for step in self._migrations_fn(heads, self):
  File "---/venv/lib/python3.7/site-packages/alembic/command.py", line 156, in retrieve_migrations
    revision_context.run_autogenerate(rev, context)
  File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/api.py", line 415, in run_autogenerate
    self._run_environment(rev, migration_context, True)
  File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/api.py", line 451, in _run_environment
    autogen_context, migration_script)
  File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 22, in _populate_migration_script
    _produce_net_changes(autogen_context, upgrade_ops)
  File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 48, in _produce_net_changes
    autogen_context, upgrade_ops, schemas
  File "---/venv/lib/python3.7/site-packages/alembic/util/langhelpers.py", line 313, in go
    fn(*arg, **kw)
  File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 75, in _autogen_for_tables
    inspector, upgrade_ops, autogen_context)
  File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 137, in _compare_tables
    inspector.reflecttable(t, None)
  File "---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py", line 633, in reflecttable
    exclude_columns, _extend_on, reflection_options)
  File "---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py", line 729, in _reflect_fk
    table_name, schema, **table.dialect_kwargs)
  File "---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py", line 447, in get_foreign_keys
    **kw)
  File "<string>", line 2, in get_foreign_keys
  File "---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py", line 54, in cache
    ret = fn(self, con, *args, **kw)
  File "---/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 2081, in get_foreign_keys
    self._correct_for_mysql_bug_88718(fkeys, connection)
  File "---/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 2126, in _correct_for_mysql_bug_88718
    for col in fkey['referred_columns']
  File "---/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 2126, in <listcomp>
    for col in fkey['referred_columns']
KeyError: 'id'

```

working on reproducing with an 8.0 with lower_case_table_names set to 1

release immediately upon fix

[Sqlalchemy-tickets] Issue #4360: implement SQLite DDL level ON CONFLICT (zzzeek/sqlalchemy)

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

New issue 4360: implement SQLite DDL level ON CONFLICT
https://bitbucket.org/zzzeek/sqlalchemy/issues/4360/implement-sqlite-ddl-level-on-conflict

Michael Bayer:

moved from #4010 to represent straight ON CONFLICT, that is, https://www.sqlite.org/lang_conflict.html.

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

[Sqlalchemy-tickets] Issue #4359: very intricate expiration sequence can lead to a detached object buried in a bound parameter (zzzeek/sqlalchemy)

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

New issue 4359: very intricate expiration sequence can lead to a detached object buried in a bound parameter
https://bitbucket.org/zzzeek/sqlalchemy/issues/4359/very-intricate-expiration-sequence-can

Michael Bayer:

I've not managed to reduce this further yet, simple attempts are still failing to identify the full issue


```
#!python

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

Base = declarative_base()


class Person(Base):
    __tablename__ = "persons"

    id = Column(Integer, primary_key=True)


class Car(Base):
    __tablename__ = "cars"

    id = Column(Integer, primary_key=True)
    owner_id = Column(Integer, ForeignKey("persons.id"))
    owner = relationship("Person")


class House(Base):
    __tablename__ = "houses"

    id = Column(Integer, primary_key=True)
    owner_id = Column(Integer, ForeignKey("persons.id"))
    owner = relationship("Person")


class PersonPersonRelationship(Base):
    __tablename__ = "person_person_relationships"

    from_person_id = Column(Integer, ForeignKey("persons.id"), primary_key=True)
    from_person = relationship(
        "Person",
        foreign_keys=[from_person_id],
        backref=backref("relationships"),
    )
    to_person_id = Column(Integer, ForeignKey("persons.id"), primary_key=True)
    to_person = relationship("Person", foreign_keys=[to_person_id])


engine = create_engine("sqlite://")

Base.metadata.create_all(engine)

# Set up our data
session = Session(bind=engine)
person1 = Person(id=1)
session.add(person1)
person2 = Person(id=2)
session.add(person2)
PersonPersonRelationship(from_person=person2, to_person=person1)
car = Car(owner=person2)
session.add(car)
session.commit()
session.close()

print("------------------------------------------------------------")
# Start with a fresh session to reproduce the issue
new_session = Session(engine)


def make_foo_query(sess):
    owner = sess.query(Person).get(2)
    return House.owner == owner

# TODO: need to figure out how to reduce this mapping
# Pull the car, its owner, and the owner's related people into memory
car = new_session.query(Car).one()
relationships = car.owner.relationships

crit = make_foo_query(new_session)

for s in sorted(
    new_session.identity_map.all_states(),
    key=lambda x: id(x.obj()),
    reverse=True
):
    print("object: %s" % s.obj())
    new_session.expire(s.obj())


print(crit.left.callable())



```


```
#!python

------------------------------------------------------------
object: <__main__.Person object at 0x7fd62fd87d30>
object: <__main__.Car object at 0x7fd62fd87898>
object: <__main__.PersonPersonRelationship object at 0x7fd62fd214e0>
Traceback (most recent call last):
  File "test.py", line 84, in <module>
    print(crit.left.callable())
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/relationships.py", line 1429, in _go
    value = fn(*arg, **kw)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line 2625, in _get_state_attr_by_column
    return state.manager[prop.key].impl.get(state, dict_, passive=passive)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line 597, in get
    value = state._load_expired(state, passive)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/state.py", line 617, in _load_expired
    self.manager.deferred_scalar_loader(self, toload)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/loading.py", line 813, in load_scalar_attributes
    (state_str(state)))
sqlalchemy.orm.exc.DetachedInstanceError: Instance <Person at 0x87b640> is not bound to a Session; attribute refresh operation cannot proceed (Background on this error at: http://sqlalche.me/e/bhk3)

```

for some reason if I just do a simple A/B test and have "B" expired first, the issue does not reproduce.   so not sure what's going on yet.

[Sqlalchemy-tickets] Issue #4358: Problem with psycopg2 >=2.5.4 and JSONB field (zzzeek/sqlalchemy)

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

New issue 4358: Problem with psycopg2 >=2.5.4 and JSONB field
https://bitbucket.org/zzzeek/sqlalchemy/issues/4358/problem-with-psycopg2-254-and-jsonb-field

Manuel Holtgrewe:

When using psycopg2 >=2.5.4, JSONB fields are directly returned as Python objects and not as strings. This leads to a problem as SQLAlchemy tries to run `json.loads()` on these already decoded JSON files.

Also see here:

- https://stackoverflow.com/questions/50538699/sqlalchemy-psycopg2-jsonb-columns-are-being-returned-as-strings-instead-of-dict
- http://initd.org/psycopg/docs/extras.html#json-adaptation

I'm sorry that I do not have time to write a complete example showing the bug but I could work around it by disabling this behaviour in psycopg2 (as shown in the second link).

[Sqlalchemy-tickets] Issue #4357: alt-key primary key on bulk update UPDATES the pk as well as having it in the WHERE clause (zzzeek/sqlalchemy)

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

New issue 4357: alt-key primary key on bulk update UPDATES the pk as well as having it in the WHERE clause
https://bitbucket.org/zzzeek/sqlalchemy/issues/4357/alt-key-primary-key-on-bulk-update-updates

Michael Bayer:

continuing from #3849:

    py.test test/orm/test_bulk.py -s --log-debug=sqlalchemy.engine -k test_update_attrs


the UPDATE statement is re-SETing the pk attribute needlessly (which can fail on SQL server):

    UPDATE people_attrs SET person_id=?, name=? WHERE people_attrs.person_id = ?
    INFO:sqlalchemy.engine.base.Engine:(5, 'newname', 5)

the tests need to assert_sql

[Sqlalchemy-tickets] Issue #4356: Error after upgrading to 1.2.13 (zzzeek/sqlalchemy)

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

New issue 4356: Error after upgrading to 1.2.13
https://bitbucket.org/zzzeek/sqlalchemy/issues/4356/error-after-upgrading-to-1213

Thorsten Kampe:

"TypeError: __init__() got an unexpected keyword argument 'use_lifo'" after upgrading to 1.2.13. Reverting to 1.2.12 doesn't get rid of the error. Also uninstalling and reinstalling. Removing the sqlalchemy folder and reinstalling removes the issue-

A comparison between 1.2.12 and 1.2.13 shows that the pool directory does not exist in 1.2.13. Upgrading and uninstalling via pip does not remove the folder.

Attaching the traceback because Bitbucket's Markdown parser produces still an unreadable mess.

[Sqlalchemy-tickets] Issue #4355: Avoid unnecessary join on joinedload in custom query (zzzeek/sqlalchemy)

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

New issue 4355: Avoid unnecessary join on joinedload in custom query
https://bitbucket.org/zzzeek/sqlalchemy/issues/4355/avoid-unnecessary-join-on-joinedload-in

Valery Tikhonov:

F.e. I have 2 entities: `Event` and `Plan`. Event has 1:1 relationship with plan via some foreign key.
And I build custom query to get all events with plans for some date and group.
Also I add `joinedload` to have all plans populated in relations.
```
query = Event.query \
        .join(Plan) \
        .filter(Plan.group_id == group_id) \
        .filter(Plan.date <= to_date) \
        .filter(Plan.color_id == color_id) \
        .options(joinedload(Event.plan, innerjoin=True))
```
This gives me such sql:
```
SELECT <event & plan selector>
FROM price_change 
JOIN plans_monthly ON plans_monthly.month_unique_id = price_change.month_unique_id JOIN plans_monthly AS plans_monthly_1 ON plans_monthly_1.month_unique_id = price_change.month_unique_id 
WHERE plans_monthly.group_id = ? AND plans_monthly.date <= ? AND plans_monthly.color_id = ? AND plans_monthly.date > ?
```

As you can see - it generates second plans_monthly join (just after the first one), which can be easily avoided, as all data, I need, it in the first one.

For now the workaround is building custom query (returning just plan and event rows), then mapping them to events via `from_statement` and then applying each found plan to event via `set_committed_value`.

But it would be nice to have it all done automatically.
Thanks!

[Sqlalchemy-tickets] Issue #4354: __delete__ doesnt work for many to one relationships (zzzeek/sqlalchemy)

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

New issue 4354: __delete__ doesnt work for many to one relationships
https://bitbucket.org/zzzeek/sqlalchemy/issues/4354/__delete__-doesnt-work-for-many-to-one

Michael Bayer:

kind of a big oversight...


```
#!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)
    data = Column(String)
    bs = relationship("B", back_populates="a")


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

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

s = Session(e)

b1 = B()
a1 = A(bs=[b1])
s.add_all([a1, b1])
s.commit()

b1.a
del b1.a

s.commit()

assert b1.a is None

```

[Sqlalchemy-tickets] Issue #4353: raiseload with sql_only=False is overly aggressive (zzzeek/sqlalchemy)

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

New issue 4353: raiseload with sql_only=False is overly aggressive
https://bitbucket.org/zzzeek/sqlalchemy/issues/4353/raiseload-with-sql_only-false-is-overly

Alex Rothberg:

I believe that I am seeing a case where `raiseload` with `sql_only=False` is triggering an exception when it should not be. While setting `sql_only=True` avoids the error for reasons outlined below this is not an ideal workaround. Further, exception is being generated in a case where the look up is entirely not needed (explanation below). The stack trace is:

```
#!python

/Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:229: in __set__
    instance_dict(instance), value, None)
/Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:1079: in set
    initiator=evt)
/Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/collections.py:768: in bulk_replace
    existing_adapter.fire_remove_event(member, initiator=initiator)
/Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/collections.py:698: in fire_remove_event
    item, initiator)
/Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:962: in fire_remove_event
    fn(state, value, initiator or self._remove_token)
/Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:1255: in emit_backref_from_collection_remove_event
    passive=PASSIVE_NO_FETCH)
/Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:634: in pop
    passive=passive, check_old=value, pop=True)
/Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:800: in set
    LOAD_AGAINST_COMMITTED)
/Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:599: in get
    value = callable_(state, passive)
/Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/strategies.py:834: in __call__
    return strategy._load_for_state(state, passive)
/Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/strategies.py:589: in _load_for_state
    self._invoke_raise_load(state, passive, "raise")
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <sqlalchemy.orm.strategies.LazyLoader object at 0x1176b2f20>
state = <sqlalchemy.orm.state.InstanceState object at 0x1162462b0>, passive = 53
lazy = 'raise'
```

I believe that the problem is that an exception is thrown unconditionally here: https://github.com/zzzeek/sqlalchemy/blob/7d372da7385be6a9817a20b6b62f7c4237af7b26/lib/sqlalchemy/orm/strategies.py#L588-L589 even if this escape hatch would have been hit: https://github.com/zzzeek/sqlalchemy/blob/7d372da7385be6a9817a20b6b62f7c4237af7b26/lib/sqlalchemy/orm/strategies.py#L626-L628

I think in the cases where the load is entirely unneeded (as it is in my case), the exception should not be raised.

While a workaround is to set `sql_only=True`, I want to avoid that as it can lead to differences is results between testing and production (due to the presence of items in the identity map avoiding exceptions).

The reason I say the load us unneeded is that sqla is attempting to load the old value in a soon to be replaced backref (ie this is part of the emit).

[Sqlalchemy-tickets] Issue #4352: Single table inheritance conflict resolution breaks for primary keys (zzzeek/sqlalchemy)

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

New issue 4352: Single table inheritance conflict resolution breaks for primary keys
https://bitbucket.org/zzzeek/sqlalchemy/issues/4352/single-table-inheritance-conflict

Tom Manderson:

Single table inheritance has a conflict resolution method for columns that appear in multiple subclasses. When the column that appears in multiple subclasses is a primary key, the conflict resolution procedure is skipped and an error is thrown.

This makes sense when the primary key isn't on the base class to start with, but if it is (eg. in my use case the primary key is added with `declared_attr.cascading`) then we should allow it.

There's an [existing PR](https://github.com/zzzeek/sqlalchemy/pull/483) including the code below as a test case:

```
class TestBase(Base):
            __abstract__ = True

            @declared_attr.cascading
            def id(cls):
                col_val = None
                if TestBase not in cls.__bases__:
                    col_val = cls.__table__.c.get('id')
                if col_val is None:
                    col_val = Column(Integer, primary_key=True)
                return col_val

        class Person(TestBase):
            """single table base class"""
            __tablename__ = 'person'

        class Engineer(Person):
            """ single table inheritance, no extra cols """

        class Manager(Person):
            """ single table inheritance, no extra cols """
```