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

[Sqlalchemy-tickets] Issue #4019: Unable to access outside tables within subqueries in FROM (zzzeek/sqlalchemy)

[Volker Diels-G. <iss...@bi...>]

New issue 4019: Unable to access outside tables within subqueries in FROM
https://bitbucket.org/zzzeek/sqlalchemy/issues/4019/unable-to-access-outside-tables-within

Volker Diels-Grabsch:

Wrong SQL code is produced when a sub query appears within a FROM clause and tries to access an outside table.

Usually a sub query is able to access outside tables. However, when a sub query appears within a FROM clause, it is not able to access outside tables. Instead an unwanted additional instance of the table is added to the sub query.

For example, consider two tables `a` and `b` with a sub query `e`. When sticking these together to `query`, the outer table `a` should be used in the sub query `e`:

```
#!python

from sqlalchemy import *

a = table('a', column('w'), column('x'))
b = table('b', column('y'), column('z'))

e = select([b.c.y]).where(a.c.x == b.c.z).alias('e')

query = select([
    a.c.w,
    e.c.y.label('f'),
])

print(str(query))
```

This produces the following wrong SQL code with an unwanted inner select from `a`:


```
#!sql

SELECT a.w, e.y AS f
FROM a, (SELECT b.y AS y
FROM b, a  -- ERROR
WHERE a.x = b.z) AS e
```

Expected SQL code:

```
#!sql

SELECT a.w, e.y AS f
FROM a, (SELECT b.y AS y
FROM b  -- OK
WHERE a.x = b.z) AS e
```

For comparison, when putting the sub select into the SELECT clause rather than the FROM clause, everything works fine:

```
#!python

from sqlalchemy import *

a = table('a', column('w'), column('x'))
b = table('b', column('y'), column('z'))

e = select([b.c.y]).where(a.c.x == b.c.z).alias('e')

query = select([
    a.c.w,
    e.as_scalar().label('f'),  # Difference to first example
])

print(str(query))
```

This produces correct SQL code without the unwanted inner select from `a`:

```
#!sql

SELECT a.w, (SELECT b.y 
FROM b  -- OK 
WHERE a.x = b.z) AS v 
FROM a
```

Of course, in our more complex production code, the sub select cannot be easily moved from the FROM to the SELECT clause. The example above is just the minimal code I came up with that reproduces the issue.

For the sake of completeness, this code is intended to run on PostgreSQL >= 9.2. However, the dialect doesn't change anything in this bug report. In all examples, `str(query)` produces the same SQL code as `str(query.compile(dialect=postgresql.dialect()))`.

[Sqlalchemy-tickets] Issue #4018: maintaining float type affinity (zzzeek/sqlalchemy)

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

New issue 4018: maintaining float type affinity
https://bitbucket.org/zzzeek/sqlalchemy/issues/4018/maintaining-float-type-affinity

Michael Bayer:

continuing from #4017

two issues are 1. the "asdecimal"-ness of an expression being maintained, which goes beyond Float and 2. Float having it's own affinity



```
#!python

from sqlalchemy import Float, Integer, literal
expr = literal(5, Integer) * literal(4.5, Float)
assert expr.type.asdecimal is False
assert expr.type is Float

```

option are to give Float its own _type_affinity, but then that only works for Float and not Numeric with asdecimal=False type affinity and also that would be the single example of two levels of _type_affinity Numeric/Float so not sure I like that.

I think we should use something other than _type_affinity in _DateAffinity._adapt_expression that can deliver more specificity, and leave _type_affinity alone since this is not as much about Python datatypes.

also why are the logic used for numeric -> numeric type adaptations encapsulated in _DateAffinity, that just looks bad, rename that to something.

[Sqlalchemy-tickets] Issue #4017: float should coerce to Float (zzzeek/sqlalchemy)

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

New issue 4017: float should coerce to Float
https://bitbucket.org/zzzeek/sqlalchemy/issues/4017/float-should-coerce-to-float

Michael Bayer:

it seems to be ancient history that we slowly added support for Decimal, but never changed the coercion of plain "float" to be Float, not Numeric w/ decimals:

```
#!python


_type_map = {
    int: Integer(),
    float: Numeric(),
    bool: BOOLEANTYPE,
    decimal.Decimal: Numeric(),
    dt.date: Date(),
    dt.datetime: DateTime(),
    dt.time: Time(),
    dt.timedelta: Interval(),
    util.NoneType: NULLTYPE
}

```

So the more expensive Decimal is added into situations where plain float is used, and also on SQLite we of course get the warning as well as the wrong type back:

```
#!python


from sqlalchemy import create_engine, literal, select

e = create_engine("sqlite://")

print repr(e.scalar(select([literal(4.56)])))
```

```
#!


$ python test.py 
/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/sqltypes.py:596: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.
  'storage.' % (dialect.name, dialect.driver))
Decimal('4.5600000000')

```

ouch!

this is easy to fix, but may or may not have hard implications for current applications.  Consider for 1.2 if possible.

[Sqlalchemy-tickets] Issue #4016: Support UUID on pg8000 (zzzeek/sqlalchemy)

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

New issue 4016: Support UUID on pg8000
https://bitbucket.org/zzzeek/sqlalchemy/issues/4016/support-uuid-on-pg8000

Donald Stufft:

It would be really awesome if the PostgreSQL support for UUIDs worked on pg8000. It appears like this should be relatively simple to do since pg8000 supports UUIDs, the main thing that needs changed is the result processor needs to not pass an already existing UUID object into the Python uuid.UUID class, and instead just return it.

I worked around this locally by simply doing:

```
from sqlalchemy.dialects.postgresql import UUID as _UUID

class UUID(_UUID):

    def result_processor(self, dialect, coltype):
        if self.as_uuid:
            _process = super().result_processor(dialect, coltype)

            def process(value):
                if isinstance(value, uuid.UUID):
                    return value
                return _process(value)
            return process
        else:
            return None
```

Which seems to work to me, but I haven't dug into it too deeply to see if there is a reason why it is the way it is.

[Sqlalchemy-tickets] Issue #4015: Possible Memory Leak on simple operations (zzzeek/sqlalchemy)

[Luca Z. <iss...@bi...>]

New issue 4015: Possible Memory Leak on simple operations
https://bitbucket.org/zzzeek/sqlalchemy/issues/4015/possible-memory-leak-on-simple-operations

Luca Zulian:

I am currently playing around with SQLAlchemy a bit, and I found a strange behaviour about memory usage. I'm using sqlalchemy with version 1.1.10 running on python 2.7.13, macOS Sierra version 10.12.5, MySQL-python 1.2.5, mysql driver version Ver 14.14 Distrib 5.7.18, for osx10.12 (x86_64) using  EditLine wrapper


```
#!python
from contextlib import contextmanager

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

Base = declarative_base()

e = create_engine('mysql://root:root@127.0.0.1:3306/MyDb?charset=utf8')


class Currency(Base):
    __tablename__ = "currency"
    id = Column(Integer, primary_key=True)

Base.metadata.create_all(e)
Session = sessionmaker(autoflush=True, bind=e)


@contextmanager
def session_scope():
    """Provide a transactional scope around a series of operations."""
    session = Session()
    try:
        yield session
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()


def foo():
    with session_scope() as session2:
        result = session2.query(Currency).filter_by(id=1).first()
        print(result.id)


while True:
    foo()
```
It seems that the memory it's never freed, and that continuously increase.
Also I've got the same problem only opening and closing the session.


```
#!python
from contextlib import contextmanager

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

Base = declarative_base()

e = create_engine('mysql://root:root@127.0.0.1:3306/MyDb?charset=utf8')


class Currency(Base):
    __tablename__ = "currency"
    id = Column(Integer, primary_key=True)

Base.metadata.create_all(e)
Session = sessionmaker(autoflush=True, bind=e)


@contextmanager
def session_scope():
    """Provide a transactional scope around a series of operations."""
    session = Session()
    try:
        yield session
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()


def foo():
    with session_scope() as session2:
        pass


while True:
    foo()
```

[Sqlalchemy-tickets] Issue #4014: SQLA don't set FK in relationship if inheritance present (zzzeek/sqlalchemy)

[Вася Г. <iss...@bi...>]

New issue 4014: SQLA don't set FK in relationship if inheritance present
https://bitbucket.org/zzzeek/sqlalchemy/issues/4014/sqla-dont-set-fk-in-relationship-if

Вася Гайкин:

I want build pseudo file system on DB tables and make several classes. But assign to **item.parent** attribute has no effect. It work if write **item.parent_id = parent.id**.

*(in attanchment simple example project)*

```
#!python

class Item( Base ):
    name = Column( String, nullable=False )

    parent_id = Column( Integer,
                        ForeignKey( 'Item.id',
                                    onupdate='cascade',
                                    ondelete='cascade',
                                    deferrable=True ),
                        nullable=False )

    parent = relationship( 'Folder',
                           foreign_keys=parent_id,
                           uselist=False,
                           post_update=True )

    _type = Column( String, nullable=False )
    __mapper_args__ = { 'polymorphic_on': _type }


class File( Item ):
    id = Column( Integer,
                 ForeignKey( 'Item.id',
                             onupdate='cascade',
                             ondelete='cascade' ),
                 primary_key=True )

    data = Column( LargeBinary, nullable=False )
    mime = Column( String, nullable=False )

    preview_id = Column( Integer,
                         ForeignKey( 'File.id',
                                     onupdate='cascade',
                                     ondelete='cascade' ) )

    preview = relationship( 'File', foreign_keys=preview_id, uselist=False )

    __mapper_args__ = { 'polymorphic_identity': 'file' }
```

**part of  utils.py**

```
#!python

def load_file( path, parent ):
    data = open( path, 'rb' ).read()
    name = os.path.basename( path )

    file = File()
    file.name = name
    file.data = data
    file.mime = 'image/jpeg'

    file.parent_id = parent.id  # <--- OK
    # file.parent  = parent # <--- FAIL

    session.add( file )
    session.commit()

    return file
```

**part of run.py**

```
#!python

    main_file = load_file( '/tmp/example_2.jpg', usr )
    preview = load_file( '/tmp/example_3.jpg', bin_ )

    main_file.preview = preview  # <--- OK

    link = Link()
    link.target = root
    link.name = main_file.name
    # link.parent_id = root.id  # <--- OK
    link.parent = root  # <--- FAIL
    session.add( link )

    session.commit()
```

[Sqlalchemy-tickets] Issue #4013: [idea] support __len__ on AppenderQuery to unify usage with non lazy=dynamic loaded collections (zzzeek/sqlalchemy)

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

New issue 4013: [idea] support __len__ on AppenderQuery to unify usage with non lazy=dynamic loaded collections
https://bitbucket.org/zzzeek/sqlalchemy/issues/4013/idea-support-__len__-on-appenderquery-to

dwt:

Hi there,

during a refactoring from lazy=False to lazy=dynamic on some of our relationships, it would have made our life quite a bit easier if the attribute wouldn't have just supported iteration and ranged queries, but also __len__ (to emit a count() query).

Is that something you would consider? It shouldn't be too hard to implement.

Our use case is, that we have some template mixins, that are quite generic and would like to consume both lazy=dynamic as well as regular relations.

[Sqlalchemy-tickets] Issue #4012: AttributeError in WithinGroup (zzzeek/sqlalchemy)

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

New issue 4012: AttributeError in WithinGroup
https://bitbucket.org/zzzeek/sqlalchemy/issues/4012/attributeerror-in-withingroup

Scott Milliken:

Commit 7c4512c did a refactor, renaming the `WithinGroup` constructor argument from `func` to `element`. It looks like a reference to `func` in `WithinGroup.get_children` didn't get updated.

Noticed because it's causing an `AttributeError` when trying to use `func.percentile_cont`.

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #4011: joined subclass to 2 level subquery load fails on join (zzzeek/sqlalchemy)

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

New issue 4011: joined subclass to 2 level subquery load fails on join
https://bitbucket.org/zzzeek/sqlalchemy/issues/4011/joined-subclass-to-2-level-subquery-load

Michael Bayer:

Currently cannot be tested on master due to #3963.

under 1.1, the third query is losing join context

```
#!sql


SELECT dept.id AS dept_id, owner_1.dept_id AS owner_1_dept_id 
FROM (SELECT DISTINCT milestone.owner_id AS milestone_owner_id 
FROM milestone, sprint 
WHERE sprint.id IN (?)) AS anon_1 JOIN owner AS owner_1 ON owner_1.id = anon_1.milestone_owner_id JOIN dept ON dept.id = owner_1.dept_id ORDER BY owner_1.dept_id



```


```
#!python

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

Base = declarative_base()


class Milestone(Base):
    __tablename__ = 'milestone'
    id = Column(Integer, primary_key=True)

    milestone_type = Column(String(64), nullable=False)
    owner_id = Column(
        Integer,
        ForeignKey('owner.id', name='milestone_owner_id_owner_id_fkey'))

    owner = relationship('Owner', back_populates='milestone')

    __mapper_args__ = {
        'polymorphic_on': milestone_type,
    }


class Sprint(Milestone):
    __tablename__ = 'sprint'
    id = Column(
        Integer,
        ForeignKey('milestone.id', name='sprint_id_milestone_id_fkey'),
        primary_key=True)

    __mapper_args__ = {
        'polymorphic_identity': 'sprint'
    }


class Owner(Base):
    __tablename__ = 'owner'
    id = Column(Integer, primary_key=True)

    dept_id = Column(
        Integer,
        ForeignKey('dept.id', name='owner_dept_id_dept_id_fkey'))
    dept = relationship('Dept', back_populates='owner')
    milestone = relationship('Milestone', back_populates='owner')


class Dept(Base):
    __tablename__ = 'dept'
    id = Column(Integer, primary_key=True)
    owner = relationship('Owner', back_populates='dept')


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

sess = Session(e)

d = Dept(id=1)
o = Owner(id=1)
o.dept = d
s = Sprint(id=1)
s.owner = o
sess.add(o)
sess.commit()

sess.query(Sprint).filter(Sprint.id.in_([1])).options(
    subqueryload(Sprint.owner).subqueryload(Owner.dept)).all()


```

[Sqlalchemy-tickets] Issue #4010: implement SQLite INSERT OR REPLACE variant of insert() (zzzeek/sqlalchemy)

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

New issue 4010: implement SQLite INSERT OR REPLACE variant of insert()
https://bitbucket.org/zzzeek/sqlalchemy/issues/4010/implement-sqlite-insert-or-replace-variant

Michael Bayer:

SQLIte-specific variant implemented similarly to Postgresql's ON CONFLICT, MySQL ON DUPLICATE KEY

original issue #960

[Sqlalchemy-tickets] Issue #4009: implement MySQL ON DUPLICATE KEY UPDATE (zzzeek/sqlalchemy)

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

New issue 4009: implement MySQL ON DUPLICATE KEY UPDATE
https://bitbucket.org/zzzeek/sqlalchemy/issues/4009/implement-mysql-on-duplicate-key-update

Michael Bayer:

MySQL-specific variant implemented similarly to Postgresql's ON CONFLICT

original issue #960

[Sqlalchemy-tickets] Issue #4008: Docs: responsive sphinx theme (zzzeek/sqlalchemy)

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

New issue 4008: Docs: responsive sphinx theme
https://bitbucket.org/zzzeek/sqlalchemy/issues/4008/docs-responsive-sphinx-theme

mixmastamyk:

Hi,

Been trying to read the docs on a mobile device and it is quite difficult because the sidebar takes up most of the viewport.  Happens on a narrow desktop window also, when trying to work on code and have docs open at the same time.

A few lines of responsive css could move or hide the sidebar for narrow viewports.  Alternatively there are responsive Sphinx themes, like Alabaster.

Thanks for the hard work,

[Sqlalchemy-tickets] Issue #4007: mysql 5.7 may have made SHOW VARIABLES limited (zzzeek/sqlalchemy)

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

New issue 4007: mysql 5.7 may have made SHOW VARIABLES limited
https://bitbucket.org/zzzeek/sqlalchemy/issues/4007/mysql-57-may-have-made-show-variables

Michael Bayer:

per https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_show_compatibility_56 they've changed the rules for SHOW.

At https://github.com/zzzeek/sqlalchemy/pull/369, a user is getting blank for "SHOW VARIABLES LIKE 'sql_mode'" and I think this might be what's happening.  The MySQL logic needs to be ready for no row returned here and it should emit a warning.

[Sqlalchemy-tickets] Issue #4006: Combination of Array and string with explicit collation (PostgreSQL) (zzzeek/sqlalchemy)

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

New issue 4006: Combination of Array and string with explicit collation (PostgreSQL)
https://bitbucket.org/zzzeek/sqlalchemy/issues/4006/combination-of-array-and-string-with

Rudolph Froger:

This field:

```
#!python
from sqlalchemy.dialects.postgresql import ARRAY
...

keywords = Column(ARRAY(Unicode(100, collation='en_US'), dimensions=1))
```

Gives an error when trying to create all tables:

```
#!python

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near "["
LINE 7:  keywords VARCHAR(100) COLLATE "en_US"[], 
```

This is the generated SQL:

```
#!sql
CREATE TABLE example_table (keywords VARCHAR(100) COLLATE "en_US"[]);
```

The correct SQL should be:


```
#!sql

CREATE TABLE example_table (keywords VARCHAR(100)[] COLLATE "en_US");
```

[Sqlalchemy-tickets] Issue #4005: Error determining the version of a Postgres server (zzzeek/sqlalchemy)

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

New issue 4005: Error determining the version of a Postgres server
https://bitbucket.org/zzzeek/sqlalchemy/issues/4005/error-determining-the-version-of-a

Steven Winfield:

In ```sqlalchemy.dialects.postgresql.base.PGDialect._get_server_version_info```, the server version is found by regex matching the result of

```
#!sql

select version()
```

but this doesn't match the string returned by the v10 betas that are currently available, so an AssertionError is raised. 

An example of the version string there is:
```PostgreSQL 10beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit```

However, a more robust way to check the version number - the way that the postgres devs recommend now - would be to query the value of [```server_version_num```](https://www.postgresql.org/docs/10/static/runtime-config-preset.html). This is an integer (returned as a string) of the form (major * 10000 + minor * 100 + bugfix), e.g. Postgres v9.6.2 => 90602 and 10beta1 => 100000

So ```_get_server_version_info``` could do something like:

```
#!python
v = int(connection.execute("show server_version_num").scalar())
major, v = divmod(v, 10000)
minor, bugfix = divmod(v, 100)
return (major, minor, bugfix)
```

If you prefer a select rather than a show, then
```
#!sql
select current_setting('server_version_num')
```
does the same thing.

This should work all the way back to postgres v8.2.
Not that it matters too much, but the designation above of "major", "minor", and "bugfix" is correct for [v10 onwards](https://www.postgresql.org/support/versioning/).

Cheers.

[Sqlalchemy-tickets] Issue #4004: one soft delete hook to rule them all (zzzeek/sqlalchemy)

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

New issue 4004: one soft delete hook to rule them all
https://bitbucket.org/zzzeek/sqlalchemy/issues/4004/one-soft-delete-hook-to-rule-them-all

Michael Bayer:

not really sure how this should look but goals are:

1. ORM keeps "delete" semantics completely.   session.delete(obj), relationships will use cascade='all, delete, delete-orphan' normally, so that everything looks like "DELETE"

2. the critical part is unitofwork.register_object().  that's where the "thing" happens that determines the "deletedness" of all objects.  unlike session.delete(), this is the place where it happens for all the relationship cascades also.

3. I am loathe to add a plain event inside of unitofwork.register_object().  at this point we are asking too much of users to understand internal flow.  before_flush() is right at the top, fine.  before_update() / before_delete(), right before the statement, OK.    But because of relationship operations that take place in dependency.py, before_flush() doesn't tell us about everything that will happen in the flush and we can't react to everything.

4. the hook is really something that happens specific to UOW - after all the presorts are done and before the actions proceed.  

register_object() hook still seems like the way to go.   this could also impact versioning recipes too.

see if a register_object() hook can come up with recipes that handle both soft_delete and versioning.  that could help come up with some way to explain it.

[Sqlalchemy-tickets] Issue #4003: oracle check constraint reflection (zzzeek/sqlalchemy)

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

New issue 4003: oracle check constraint reflection
https://bitbucket.org/zzzeek/sqlalchemy/issues/4003/oracle-check-constraint-reflection

Michael Bayer:

gerrit ready to go at https://gerrit.sqlalchemy.org/#/c/300/

[Sqlalchemy-tickets] Issue #4002: warn when postgresql distinct columns used on other dialect (zzzeek/sqlalchemy)

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

New issue 4002: warn when postgresql distinct columns used on other dialect
https://bitbucket.org/zzzeek/sqlalchemy/issues/4002/warn-when-postgresql-distinct-columns-used

Michael Bayer:

e.g. query.distinct(<cols>)

also add doc note.

[Sqlalchemy-tickets] Issue #4001: AttributeError using with_hint (zzzeek/sqlalchemy)

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

New issue 4001: AttributeError using with_hint
https://bitbucket.org/zzzeek/sqlalchemy/issues/4001/attributeerror-using-with_hint

R. Grout:

With SQLAlchemy 1.1.9.  

I've been trying to figure out how to get indexing hints into a MySQL query.  I've been unsuccessful, but along the way, I ran across this AttributeError.  When the dialect is set to 'mysql', the error is raised, but the hint does not appear in the query string.  The default dialect of '*' does raise the AttributeError.

Running this function:
```
def hint():
    table = sql.table('runs')

    query = sql.select([table]).with_hint('runs', 'USE INDEX(my_index)').select_from(table)
    print(query)
```
I get the following attribute error (when trying to print the query).
```
Traceback (most recent call last):
  File "sqltest.py", line 11, in <module>
    hint()
  File "sqltest.py", line 9, in hint
    print(query)
  File "lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 446, in __str__
    return str(self.compile())
  File "<string>", line 1, in <lambda>
  File "lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 436, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 442, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 435, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 216, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 242, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "lib/python3.6/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 1696, in visit_select
    hint_text, byfrom = self._setup_select_hints(select)
  File "lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 1770, in _setup_select_hints
    select._hints.items()
  File "lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 1771, in <listcomp>
    if dialect in ('*', self.dialect.name)
AttributeError: 'str' object has no attribute '_compiler_dispatch'
```

[Sqlalchemy-tickets] Issue #4000: Problems with Update and Table Inheritance (zzzeek/sqlalchemy)

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

New issue 4000: Problems with Update and Table Inheritance
https://bitbucket.org/zzzeek/sqlalchemy/issues/4000/problems-with-update-and-table-inheritance

quasiben:

## Dev Setup
 - Python 3.5
 - SQLAlchemy 1.13
 - OSX


I am a bit new to inheritance modeling for tables so it's quite possible this is not a bug but rather lack of understanding.  I've also tried scanning through past issues and I don't think I've seen this addressed though [this posting from google groups](https://groups.google.com/forum/#!topic/sqlalchemy/BKHxW-wpaak) may be relevant

## Goal

I'm trying to update a single column in the parent class (`People`) while using the child class (`Engineer`).  

### Working Code 

```python
import os
import sys

from sqlalchemy import Column, create_engine, ForeignKey, Integer, String, DateTime

from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import func
from sqlalchemy.ext.declarative import declarative_base


try:
   os.remove('test.db')
except FileNotFoundError:
   pass 

engine = create_engine('sqlite:///test.db', echo=True)
Session = sessionmaker(engine)

Base = declarative_base()


class People(Base):
    __tablename__ = 'people'
    discriminator = Column('type', String(50))
    __mapper_args__ = {'polymorphic_on': discriminator}

    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    updated = Column(DateTime, server_default=func.now(), onupdate=func.now())

class Engineer(People):
    __tablename__ = 'engineer'
    __mapper_args__ = {'polymorphic_identity': 'engineer'}
    id = Column(Integer, ForeignKey('people.id'), primary_key=True)
    kind = Column(String(100), nullable=True)

Base.metadata.create_all(engine)

session = Session()

e = Engineer()
e.name = 'Mike'
session.add(e)
session.flush()
session.commit()

# works when updating the object
e.name = "Doug"
session.add(e)
session.commit()


# works using the base class for the query
count = session.query(People).filter(
                           People.name.is_('Doug')).update({People.name: 'James'})

# fails when using the derived class
count = session.query(Engineer).filter(
                           Engineer.name.is_('James')).update({Engineer.name: 'Mary'})

session.commit()
print("Count: {}".format(count))
```

## Problem

The last update fails:

```python
session.query(Engineer).filter(
                           Engineer.name.is_('James')).update({Engineer.name: 'Mary'})

# Generated SQL
# UPDATE engineer SET name=?, updated=CURRENT_TIMESTAMP FROM people WHERE # people.name IS ?
```

As you can see the statement is trying to update rows in incorrect table, `name` is in the parent table.

I'm a little unclear about how inheritance tables should work but it seems like updates should work transparently with the derived object. Meaning, when I update `Engineer.name` querying against the `Engineer` object, SQLAlchemy should know to update the `People` table. 

I believe the the google [google groups](https://groups.google.com/forum/#!topic/sqlalchemy/BKHxW-wpaak) possibly explains why SQLAlchemy doesn't emit two update statements if I were to update `kind`.  One for the update to `kind` and another for the `onupdate` column `updated`, correct?

[Sqlalchemy-tickets] Issue #3999: merge operator precedence for all like / is / eq / comparison to 5 (zzzeek/sqlalchemy)

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

New issue 3999: merge operator precedence for all like / is / eq / comparison to 5
https://bitbucket.org/zzzeek/sqlalchemy/issues/3999/merge-operator-precedence-for-all-like-is

Michael Bayer:

e.g.:

```
#!diff


diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py
index 58f32b3..ca5a90b 100644
--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -1094,15 +1094,15 @@ _PRECEDENCE = {
     match_op: 6,
     notmatch_op: 6,
 
-    ilike_op: 6,
-    notilike_op: 6,
-    like_op: 6,
-    notlike_op: 6,
-    in_op: 6,
-    notin_op: 6,
-
-    is_: 6,
-    isnot: 6,
+    ilike_op: 5,
+    notilike_op: 5,
+    like_op: 5,
+    notlike_op: 5,
+    in_op: 5,
+    notin_op: 5,
+
+    is_: 5,
+    isnot: 5,
 
     eq: 5,
     ne: 5,

```

rationale:  MySQL, Oracle and now Postgresql all have relatively flat precedences for these operators:

(mysql):
https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/operator-precedence.html

(oracle):

https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions001.htm#i1034834

pg 9.4, less flat: https://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html#SQL-PRECEDENCE-TABLE

pg 9.5, now is more flat: https://www.postgresql.org/docs/9.5/static/sql-syntax-lexical.html#SQL-PRECEDENCE-TABLE

making these all have the same precedence means we'll just have more parenthesis.

[Sqlalchemy-tickets] Issue #3998: Problems using automap_base with DB2-s SAMPLE DB (zzzeek/sqlalchemy)

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

New issue 3998: Problems using automap_base with DB2-s SAMPLE DB
https://bitbucket.org/zzzeek/sqlalchemy/issues/3998/problems-using-automap_base-with-db2-s

Krzysztof Malinowski:

Using the official docker form IBM:

https://hub.docker.com/r/ibmcom/db2express-c/


```
#!bash

docker run -it -p 50000:50000 -e DB2INST1_PASSWORD=mypass -e LICENSE=accept ibmcom/db2express-c:latest bash

# create db
su - db2inst1
db2start
db2sampl

```

python requirements:
```
#!
sqlalchemy==1.0.17
ibm-db==2.0.5.1
ibm_db_sa==0.3.2
```
(btw. tried this with the newest versions also - same result)

OS:

```
#!
Ubuntu 14.04 64bit
```

Doing:
```
#!python
from sqlalchemy.ext.automap import automap_base
from sqlalchemy import create_engine

Base = automap_base()
engine = create_engine("db2://db2inst1:mypass@172.18.0.1:50000/SAMPLE")
Base.prepare(engine, reflect=True)

```
raises:

```
#!python

Traceback (most recent call last):
  File "db2_automap.py", line 8, in <module>
    Base.prepare(engine, reflect=True)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/automap.py", line 777, in prepare
    generate_relationship)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/automap.py", line 899, in _relationships_for_fks
    nullable = False not in set([fk.parent.nullable for fk in fks])
AttributeError: 'NoneType' object has no attribute 'nullable'
```

seems that some fk-s have incorrect *parent* and *column* attributes

[Sqlalchemy-tickets] Issue #3997: 'twophase' is an invalid keyword (zzzeek/sqlalchemy)

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

New issue 3997: 'twophase' is an invalid keyword
https://bitbucket.org/zzzeek/sqlalchemy/issues/3997/twophase-is-an-invalid-keyword

Bohuslav Hosek:

I have problem,

engine=sqlalchemy.create_engine('oracle+cx_oracle://user:password@dsn')
connection=engine.connect()

TypeError: 'twophase' is an invalid keyword argument for this function

>>> sqlalchemy.__version__
'1.1.10'
cx_Oracle.__version__ 
'6.0b1'

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3996: unincremented version id counter on superclass (zzzeek/sqlalchemy)

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

New issue 3996: unincremented version id counter on superclass
https://bitbucket.org/zzzeek/sqlalchemy/issues/3996/unincremented-version-id-counter-on

Michael Bayer:

from #3673


```
#!python

from sqlalchemy import *
from sqlalchemy import types
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base,  declared_attr
from sqlalchemy import event

from datetime import datetime

Base = declarative_base()

class Foo(Base):
    __tablename__ = 'foo'
    id = Column(types.Integer, primary_key=True)
    type = Column(types.Unicode(64), nullable=False)
    bar = Column(types.DateTime(), nullable=False)
    other = Column(types.Integer)

    __mapper_args__ = {
        'polymorphic_on': u'type',
        'polymorphic_identity': u'Foo',
        'version_id_col': bar,
        'version_id_generator': False
    }

class FooDerived(Foo):
    __tablename__ = 'foo_derived'
    id = Column(types.Integer, ForeignKey(u'foo.id'), primary_key=True)
    other2 = Column(types.Integer)

    __mapper_args__ = {
        'polymorphic_identity': u'FooDerived'
    }


e = create_engine('sqlite:///:memory:', echo='debug')
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

session = Session(e, autocommit=True, autoflush=False, expire_on_commit=False)

x = FooDerived()
x.bar = datetime(1970, 1, 1)
session.add(x)
session.flush()

x.other2 = 1
session.flush()

```

it tries to update the primary table:



```
#!

OperationalError: (sqlite3.OperationalError) near "WHERE": syntax error [SQL: u'UPDATE foo SET  WHERE foo.id = ? AND foo.bar = ?'] [parameters: (1, '1970-01-01 00:00:00.000000')]
```

docs:

```
#!


We can update our ``User`` object without incrementing the version counter
as well; the value of the counter will remain unchanged, and the UPDATE
statement will still check against the previous value.  This may be useful
for schemes where only certain classes of UPDATE are sensitive to concurrency
issues::

```

[Sqlalchemy-tickets] Issue #3995: Postgres JSON Array access fails on postgresql+pg8000 (zzzeek/sqlalchemy)

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

New issue 3995: Postgres JSON Array access fails on postgresql+pg8000
https://bitbucket.org/zzzeek/sqlalchemy/issues/3995/postgres-json-array-access-fails-on

Patrick Hayes:

I've observed that the `[]` operator to access array elements of JSON/JSONB columns with postgresql+pg8000 returns incorrect values 

Here is a minimal example that reproduces the behaviour, it should be executable as long as you have a postgres server running on port 5432 with username `username`, password `password`, and a database `testdb`

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

Base = declarative_base()
class Model(Base):
  __tablename__ = 'test'
  id = Column(BigInteger, primary_key=True)
  data = Column(JSONB, name='data_json', nullable=False)

def run_with_scheme(scheme):
  db_url = URL(
    scheme,
    username='username',
    password='password',
    host='localhost',
    port=5432,
    database='testdb',
  )
  engine = create_engine(db_url)
  Base.metadata.drop_all(engine)
  Base.metadata.create_all(engine)
  session_factory = sessionmaker(bind=engine)
  session = session_factory()
  session.add(Model(data=[1]))
  return session.query(Model.data[0]).all()

print run_with_scheme('postgresql')   # Returns [(1,)]
print run_with_scheme('postgresql+pg8000')  # Returns [(None,)]
```

Is this a sqlalchemy issue or a pg8000 issue? If this operator is not supported on pg8000 then perhaps it should be forbidden in the sqlalchemy layer, as opposed to silently returning the wrong results?