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

[Sqlalchemy-tickets] Issue #4071: test suites that make new engines for each test will blow up the LRU cache (zzzeek/sqlalchemy)

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

New issue 4071: test suites that make new engines for each test will blow up the LRU cache
https://bitbucket.org/zzzeek/sqlalchemy/issues/4071/test-suites-that-make-new-engines-for-each

Michael Bayer:

firstly, that those dialects aren't garbage collected as quickly, and then that we emit a warning, test suites that raise on warning will blow up.  this is currently illustrated in the keystone test suite.   pdb and listing out keys:

```
#!python


(<sqlalchemy.dialects.sqlite.pysqlite.SQLiteDialect_pysqlite object at 0x7f4d4725d290>, <sqlalchemy.sql.dml.Insert object at 0x7f4d481f6fd0>, ('description', 'domain_id', 'enabled', 'extra', 'id', 'is_domain', 'name', 'parent_id'), 0, False)
(<sqlalchemy.dialects.sqlite.pysqlite.SQLiteDialect_pysqlite object at 0x7f4d475b61d0>, <sqlalchemy.sql.dml.Insert object at 0x7f4d481f6fd0>, ('description', 'domain_id', 'enabled', 'extra', 'id', 'is_domain', 'name', 'parent_id'), 0, False)
(<sqlalchemy.dialects.sqlite.pysqlite.SQLiteDialect_pysqlite object at 0x7f4d47048550>, <sqlalchemy.sql.dml.Insert object at 0x7f4d481f6fd0>, ('description', 'domain_id', 'enabled', 'extra', 'id', 'is_domain', 'name', 'parent_id'), 0, False)
(<sqlalchemy.dialects.sqlite.pysqlite.SQLiteDialect_pysqlite object at 0x7f4d473bd990>, <sqlalchemy.sql.dml.Insert object at 0x7f4d481f6fd0>, ('description', 'domain_id', 'enabled', 'extra', 'id', 'is_domain', 'name', 'parent_id'), 0, False)
(<sqlalchemy.dialects.sqlite.pysqlite.SQLiteDialect_pysqlite object at 0x7f4d473b9cd0>, <sqlalchemy.sql.dml.Insert object at 0x7f4d481f6fd0>, ('description', 'domain_id', 'enabled', 'extra', 'id', 'is_domain', 'name', 'parent_id'), 0, False)
(<sqlalchemy.dialects.sqlite.pysqlite.SQLiteDialect_pysqlite object at 0x7f4d470acf10>, <sqlalchemy.sql.dml.Insert object at 0x7f4d481f6fd0>, ('description', 'domain_id', 'enabled', 'extra', 'id', 'is_domain', 'name', 'parent_id'), 0, False)
```

I'm pretty relieved this is all it is, was super worried the cache was failing in some weirder way.   it might be time to restructure how the compiled cache works, it needs to be mapper local and dialect local which means we probably need another elaborate weakref scheme like the event system has.

[Sqlalchemy-tickets] Issue #4070: tuple_ returns string whereas I would expect tuple (zzzeek/sqlalchemy)

[dima-starosud <iss...@bi...>]

New issue 4070: tuple_ returns string whereas I would expect tuple
https://bitbucket.org/zzzeek/sqlalchemy/issues/4070/tuple_-returns-string-whereas-i-would

dima-starosud:

Hello team

I am observing following behavior.
For me it looks incorrect. I would expect `tuple_` to parse SQL result before returning to application code.

```
#!python

In [22]: db.query(tuple_(1,None,tuple_('Hello', 42),3,'')).scalar()
Out[22]: '(1,,"(Hello,42)",3,"")'
```

[Sqlalchemy-tickets] Issue #4069: session.merge() can miss object gc'ed after checking in identity map (zzzeek/sqlalchemy)

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

New issue 4069: session.merge() can miss object gc'ed after checking in identity map
https://bitbucket.org/zzzeek/sqlalchemy/issues/4069/sessionmerge-can-miss-object-gced-after

Michael Bayer:

sample trace:

```
#!


   user = session.merge(cachedUser, load=False)
 File "/venv/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 157, in do
   return getattr(self.registry(), name)(*args, **kwargs)
 File "/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1849, in merge
   _resolve_conflict_map=_resolve_conflict_map)
 File "/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1875, in _merge
   merged = self.identity_map[key]
 File "/venv/lib/python2.7/site-packages/sqlalchemy/orm/identity.py", line 89, in __getitem__
   state = self._dict[key]
KeyError: <snip>
```

moved from #4030

[Sqlalchemy-tickets] Issue #4068: Chained joinedload causes duplicate sqla objects, when run with pypy (zzzeek/sqlalchemy)

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

New issue 4068: Chained joinedload causes duplicate sqla objects, when run with pypy
https://bitbucket.org/zzzeek/sqlalchemy/issues/4068/chained-joinedload-causes-duplicate-sqla

Ashley Chaloner:

This bug appears when running in pypy but not cpython. It appears when using sqlite or postgres backends (these are the only two I've tested with). The bug does not cause a stack trace, but results in duplicate database rows being returned in non-identical sqlalchemy objects.

## Versions

* pypy: Python 2.7.13 (5.8.0+dfsg-2~ppa2~ubuntu16.04, Jun 17 2017, 18:50:19) [PyPy 5.8.0 with GCC 5.4.0 20160609]
* cffi==1.10.1
* greenlet==0.4.12
* readline==6.2.4.1
* six==1.10.0
* SQLAlchemy==1.1.13
* SQLAlchemy-Utils==0.32.16

## bash script for venv setup

```bash
virtualenv -p /usr/bin/pypy ~/test-venv
source ~/test-venv/bin/activate
pip install \
    cffi==1.10.1 \
    greenlet==0.4.12 \
    readline==6.2.4.1 \
    six==1.10.0 \
    SQLAlchemy==1.1.13 \
    SQLAlchemy-Utils==0.32.16
```

## Python script to reproduce bug

```python
import uuid

from sqlalchemy import (
    Column,
    ForeignKey,
    Index,
    Table,
    create_engine
)
from sqlalchemy_utils import UUIDType
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import joinedload, relationship, sessionmaker

Base = declarative_base()


class Base2(Base):

    __abstract__ = True
    attrlist = ["id"]

    # for easier debugging
    def __repr__(self):
        attrs = ["%s:%s" % (a, getattr(self, a)) for a in self.attrlist]
        return "<%s.%s object at 0x%016x: %s>" % (
            self.__class__.__module__, self.__class__.__name__,
            id(self), ", ".join(attrs))


class Scene(Base2):

    __tablename__ = "scene"

    id = Column(UUIDType(binary=False), primary_key=True)


class ActScenes(Base2):

    __tablename__ = "act_scenes"
    attrlist = ["id", "act_id", "scene_id"]

    id = Column(UUIDType(binary=False), primary_key=True)

    act_id = Column(
        UUIDType(binary=False),
        ForeignKey("act.id"),
        nullable=False)

    scene_id = Column(
        UUIDType(binary=False),
        ForeignKey("scene.id"),
        nullable=False)
    scene = relationship(Scene, lazy="joined")


class Act(Base2):

    __tablename__ = "act"

    id = Column(UUIDType(binary=False), primary_key=True)

    scenes = relationship(ActScenes)


acts = Table(
    "acts", Base.metadata,
    Column("play_id", UUIDType(binary=False), ForeignKey("play.id")),
    Column("act_id", UUIDType(binary=False), ForeignKey("act.id"))
)
Index("ix_acts", acts.c.play_id, acts.c.act_id, unique=True)


class Play(Base2):

    __tablename__ = "play"

    id = Column(UUIDType(binary=False), primary_key=True)

    acts = relationship("Act", secondary=acts)


def run_test(i):
    """Run one test, and return True if something failed."""

    engine = create_engine('sqlite:///:memory:')
    asessionmaker = sessionmaker()
    asessionmaker.configure(bind=engine)
    Base.metadata.create_all(engine)
    session = asessionmaker()

    play1 = Play(id=uuid.uuid1())
    act = Act(id=uuid.uuid1())
    act.scenes = [
        ActScenes(
            id=uuid.uuid1(),
            act_id=act.id,
            scene=Scene(id=uuid.uuid1())
        )
        for _ in xrange(1000)
    ]
    play1.acts.append(act)

    scenecounts1 = len(play1.acts[0].scenes)

    session.add(play1)
    session.commit()

    # Comment out this block and watch the bug vanish.
    session.query(Play).options(
        joinedload(Play.acts).joinedload(Act.scenes)
    ).filter_by(id=play1.id).first()
    # End block.

    scenecounts1_again = len(play1.acts[0].scenes)
    if scenecounts1 != scenecounts1_again:
        print "Iteration {} failed".format(i)

        seen_scene_ids = set()
        for scene in act.scenes:
            if scene.id in seen_scene_ids:
                # import pdb; pdb.set_trace()
                print "Duplicate scene.id spotted: {}".format(scene.id)
                scenes_with_id = [s for s in act.scenes if s.id == scene.id]
                print "Scenes with this id:\n{}".format(
                    "\n".join(repr(s) for s in scenes_with_id))
            else:
                seen_scene_ids.add(scene.id)

    return scenecounts1 != scenecounts1_again


if __name__ == "__main__":
    N = 25
    results = [run_test(i) for i in xrange(N)]
    print "{} out of {} failed.".format(sum(results), N)
```

## Sample output

```
(test-venv) user@host:~$ pypy test_chained_joinedload.py
Iteration 1 failed
Duplicate scene.id spotted: 13a9490c-9175-11e7-995c-48e244f4ed2b
Scenes with this id:
<__main__.ActScenes object at 0x00000000035887c8: id:13a9490c-9175-11e7-995c-48e244f4ed2b, act_id:13a946b5-9175-11e7-995c-48e244f4ed2b, scene_id:13a9490d-9175-11e7-995c-48e244f4ed2b>
<__main__.ActScenes object at 0x00000000034abf68: id:13a9490c-9175-11e7-995c-48e244f4ed2b, act_id:13a946b5-9175-11e7-995c-48e244f4ed2b, scene_id:13a9490d-9175-11e7-995c-48e244f4ed2b>
```

[Sqlalchemy-tickets] Issue #4066: feature request- extend core DBAPIError exceptions with connection/engine information if available (zzzeek/sqlalchemy)

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

New issue 4066: feature request- extend core DBAPIError exceptions with connection/engine information if available
https://bitbucket.org/zzzeek/sqlalchemy/issues/4066/feature-request-extend-core-dbapierror

jvanasco:

in a trivial example, given a session with a database that does not contain `foo`...

    try:
        sess.execute("SELECT foo;")
    except Exception as e:
        print "Exception!", e
        pdb.set_trace()

`e` will be a `sqlalchemy.exc.ProgrammingError` (under Postgres and others, which inherits from `DBAPIError`).

The current design of `DBAPIError` makes it hard to tell what connection caused the error -- which is important in multi-connection environments (and probably connection pools).  

The may be raw connection info via the underlying connections's cursor (`e.orig.cursor.connection` works for postgres via pyscopg2), but this request is to make information about the SqlAlchemy Connection object on the DBAPIError .

[Sqlalchemy-tickets] Issue #4065: mysqlclient added internalerror for the interfaceerror issue (zzzeek/sqlalchemy)

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

New issue 4065: mysqlclient added internalerror for the interfaceerror issue
https://bitbucket.org/zzzeek/sqlalchemy/issues/4065/mysqlclient-added-internalerror-for-the

Michael Bayer:

https://github.com/PyMySQL/mysqlclient-python/issues/202

means we need this for reconnect tests to pass:

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 5b01b2c1f..e8cfcbae4 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1785,7 +1785,7 @@ class MySQLDialect(default.DefaultDialect):
                           self.dbapi.ProgrammingError)):
             return self._extract_error_code(e) in \
                 (2006, 2013, 2014, 2045, 2055)
-        elif isinstance(e, self.dbapi.InterfaceError):
+        elif isinstance(e, (self.dbapi.InterfaceError, self.dbapi.InternalError)):
             # if underlying connection is closed,
             # this is the error you get
             return "(0, '')" in str(e)

might as well backport to 1.0.

[Sqlalchemy-tickets] Issue #4064: Infinity numbers in oracle database produce an exception in cx_oracle.py (_detect_decimal) (zzzeek/sqlalchemy)

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

New issue 4064: Infinity numbers in oracle database produce an exception in cx_oracle.py (_detect_decimal)
https://bitbucket.org/zzzeek/sqlalchemy/issues/4064/infinity-numbers-in-oracle-database

Oleg Talalov:

Customer's database contains BINARY_FLOAT_INFINITY value in one column in few rows.

During requsting rows in fetch, function _detect_decimal called with value '~' and raise exception:
ValueError: invalid literal for int() with base 10: '~'

I use cx_orcale 5.3 and 6.0

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

[Sqlalchemy-tickets] Issue #4063: result from operation on `array_agg() @> []` assumed to be array (zzzeek/sqlalchemy)

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

New issue 4063: result from operation on `array_agg() @> []` assumed to be array
https://bitbucket.org/zzzeek/sqlalchemy/issues/4063/result-from-operation-on-array_agg-assumed

Thijs Damsma:

Since sqlalchemy 1.2 the following code will throw a `TypeError: 'bool' object is not iterable`

I believe this is because the result is assumed to be an array. 

This is an example, where I use `array_agg` and `@>` as a poor-mans pivot table:

    from sqlalchemy import Column, Table, ForeignKey, Integer, create_engine, func
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.orm import relationship
    from sqlalchemy.orm.session import sessionmaker

    engine = create_engine('postgresql://postgres:abc@localhost:5432/test')
    Session = sessionmaker(engine)

    Base = declarative_base()


    association_table = Table(
        'association', Base.metadata,
        Column('left_id', Integer, ForeignKey('left.id')),
        Column('right_id', Integer, ForeignKey('right.id'))
    )


    class Parent(Base):
        __tablename__ = 'left'
        id = Column(Integer, primary_key=True)
        children = relationship(
            "Child",
            secondary=association_table,
            back_populates="parents")


    class Child(Base):
        __tablename__ = 'right'
        id = Column(Integer, primary_key=True)
        parents = relationship(
            "Parent",
            secondary=association_table,
            back_populates="children")

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

    session = Session()

    children = [Child(), Child(), Child()]
    p1 = Parent()
    p1.children = [children[0], children[2]]

    p2 = Parent()
    p2.children = [children[1], children[2]]

    session.add_all([p1, p2])

    session.commit()

    q = session.query(
        Parent.id,
        func.array_agg(Child.id).op('@>')([children[0].id]).label('has_child_0'),
        func.array_agg(Child.id).op('@>')([children[1].id]).label('has_child_1'),
        func.array_agg(Child.id).op('@>')([children[2].id]).label('has_child_2'),
    ).\
        outerjoin(association_table, Child).\
        group_by(Parent.id)

    print(q.all())

Expected result is 

    [(1, True, False, True), (2, False, True, True)]

[Sqlalchemy-tickets] Issue #4062: pyodbc can't do rowcount with OUTPUT inserted.name (zzzeek/sqlalchemy)

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

New issue 4062: pyodbc can't do rowcount with OUTPUT inserted.name
https://bitbucket.org/zzzeek/sqlalchemy/issues/4062/pyodbc-cant-do-rowcount-with-output

Michael Bayer:

```
#!python

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

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'
    name = Column(String(50), primary_key=True)

e = create_engine("mssql+pyodbc://scott:tiger^5HHH@mssql2017:1433/test?driver=ODBC+Driver+13+for+SQL+Server", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = Session(e)

a1 = A(name='a1')
s.add(a1)

s.commit()

a1.name = A.name + '2'
s.commit()
```


this hits return_defaults() and then fails on rowcount.  need a new dialect flag.

[Sqlalchemy-tickets] Issue #4061: SQL Server BIT is native boolean (zzzeek/sqlalchemy)

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

New issue 4061: SQL Server BIT is native boolean
https://bitbucket.org/zzzeek/sqlalchemy/issues/4061/sql-server-bit-is-native-boolean

Michael Bayer:

we should not be creating a constraint for Boolean on SQL Server and we should support native boolean rules.   The BIT type only stores 1 or 0 and converts any other integer into a 1:

```
#!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(Boolean(create_constraint=False))

#e = create_engine("mssql+pymssql://scott:tiger^5HHH@mssql2017:1433/test", echo=True)
e = create_engine("mssql+pyodbc://scott:tiger^5HHH@mssql2017:1433/test?driver=FreeTDS", echo=True)

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

conn = e.connect()
conn.execute("INSERT INTO a (data) VALUES (1)")
conn.execute("INSERT INTO a (data) VALUES (15)")

if e.dialect.driver == 'pymssql':
    conn.execute("INSERT INTO a (data) VALUES (%(data)s)", {"data": True})
    conn.execute("INSERT INTO a (data) VALUES (%(data)s)", {"data": 25})
    conn.execute("INSERT INTO a (data) VALUES (%(data)s)", {"data": 1})
else:
    conn.execute("INSERT INTO a (data) VALUES (?)", [True])
    conn.execute("INSERT INTO a (data) VALUES (?)", [25])
    conn.execute("INSERT INTO a (data) VALUES (?)", [1])


print e.execute(select([A.data])).fetchall()
```

[Sqlalchemy-tickets] Issue #4060: sql server self-referential FK repeated in multple schemas doubles columns up (zzzeek/sqlalchemy)

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

New issue 4060: sql server self-referential FK repeated in multple schemas doubles columns up
https://bitbucket.org/zzzeek/sqlalchemy/issues/4060/sql-server-self-referential-fk-repeated-in

Michael Bayer:

```
#!python

from sqlalchemy import *

e = create_engine("mssql+pymssql://scott:tiger^5HHH@mssql2017:1433/test", echo=True)

metadata = MetaData()

users = Table(
    'users', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('parent_user_id', Integer,
         ForeignKey('users.user_id',
                       name='user_id_fk')),
)

users = Table(
    'users', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('parent_user_id', Integer,
         ForeignKey('test_schema.users.user_id',
                       name='user_id_fk')),
    schema="test_schema",
)

metadata.create_all(e)

insp = inspect(e)

print insp.get_foreign_keys("users", schema="test_schema")
```

output:


```
#!python

[{'referred_table': u'users', 'referred_columns': [u'user_id', u'user_id'], 'referred_schema': u'scott', 'name': u'user_id_fk', 'constrained_columns': [u'parent_user_id', u'parent_user_id']}]

```

this has to do with the join of key_constraints to itself, needs to include table_schema.

[Sqlalchemy-tickets] Issue #4059: SQL Server reflects an implicit "heap" index (zzzeek/sqlalchemy)

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

New issue 4059: SQL Server reflects an implicit "heap" index
https://bitbucket.org/zzzeek/sqlalchemy/issues/4059/sql-server-reflects-an-implicit-heap-index

Michael Bayer:

not sure if this is SQL server 2017 only, or 2016 forward, have not seen this with earlier testing:

```
#!python


from sqlalchemy import *

e = create_engine("mssql+pymssql://scott:tiger^5HHH@mssql2017:1433/test", echo=True)


c = e.connect()
c.execute("drop table if exists foo")
c.execute("""
    create table foo (
        d1 integer,
        d2 integer
    )
""")
c.execute("create index food1 on foo(d1)")

insp = inspect(e)
print insp.get_indexes("foo")
```

output:


```
#!python

[{'unique': False, 'name': None, 'column_names': []}, {'unique': False, 'name': u'food1', 'column_names': [u'd1']}]

```

that name=None is a heap index that is implicit, see https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql

[Sqlalchemy-tickets] Issue #4058: implement autocommit isolation for sql server pyodbc + pymssql (zzzeek/sqlalchemy)

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

New issue 4058: implement autocommit isolation for sql server pyodbc + pymssql
https://bitbucket.org/zzzeek/sqlalchemy/issues/4058/implement-autocommit-isolation-for-sql

Michael Bayer:

[Sqlalchemy-tickets] Issue #4057: pymssql percent signs need to not be doubled (zzzeek/sqlalchemy)

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

New issue 4057: pymssql percent signs need to not be doubled
https://bitbucket.org/zzzeek/sqlalchemy/issues/4057/pymssql-percent-signs-need-to-not-be

Michael Bayer:

```
#!python

from sqlalchemy import *

e = create_engine("mssql+pymssql://scott:tiger^5HHH@mssql2017:1433/test", echo=True)

c = e.connect()
c.execute("drop table if exists foo")
c.execute("""
    create table foo (
        d1 integer,
        d2 integer
    )
""")

c.execute(text("""
    insert into foo (d1, d2) values (:d1, 29 % 10)
"""), {"d1": 10})

print c.execute("select * from foo").fetchall()
```


```
#!python

sqlalchemy.exc.ProgrammingError: (pymssql.ProgrammingError) (102, "Incorrect syntax near '10'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n") [SQL: '\n    insert into foo (d1, d2) values (%(d1)s, 29 %% 10)\n'] [parameters: {'d1': 10}]

```

[Sqlalchemy-tickets] Issue #4056: Merge doesn't create all nested objects (zzzeek/sqlalchemy)

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

New issue 4056: Merge doesn't create all nested objects
https://bitbucket.org/zzzeek/sqlalchemy/issues/4056/merge-doesnt-create-all-nested-objects

Haim Raitsev:

When trying to update an object with nested objects, some of nested objects are already exists in the db and some are new. merge will handle correctly the existing nested objects (will update them) but will only insert the last one of the nested newly created objects.

This is the code we ran with the following input:

modal:
```
#!python
class Order(Base):
    __tablename__ = 'orders'
    id = Column(Integer, primary_key=True)
    status = Column(Enum(OrderStatus), nullable=False)
    suppression_files = relationship('File', lazy='joined', cascade="all, delete-orphan")

class File(Base):
    __tablename__ = 'files'
    id = Column(Integer, primary_key=True)
    order_id = Column(Integer, ForeignKey('orders.id'), nullable=False)
    file_name = Column(String, nullable=False)
    file_url = Column(String, nullable=False)
```
The object we are trying to merge:

```
#!python
id = {int} 59
status = {OrderStatus} OrderStatus.draft
suppression_files = {InstrumentedList} [
{File} 
file_name = {str} 'test'
file_url = {str} 'https://docs.sqlalchemy.org/en/rel_1_1/contents.html'
id = {NoneType} None
order_id = {NoneType} None, 
{File} 
file_name = {str} 'test1'
file_url = {str} 'https://docs.sqlalchemy.org/en/rel_1_1/contents.html'
id = {NoneType} None
order_id = {NoneType} None]
```

Expected:
All 2 new files in the array('suppression_files') should be created

Actual:
Only the last file (named 'test1') will be inserted to the db, the first file is ignored.

Database: PostgresSQL 9.6
SQLAlchemy:1.1.13

[Sqlalchemy-tickets] Issue #4055: merge does not follow primary key with default value (zzzeek/sqlalchemy)

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

New issue 4055: merge does not follow primary key with default value
https://bitbucket.org/zzzeek/sqlalchemy/issues/4055/merge-does-not-follow-primary-key-with

pkyosx:

merge will try to select first using primary key; however, a primary key with default value does not take effect on merge.

I have run the following example under sqlalchemy-1.1.13.

```
#!python

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

Base = declarative_base()

engine = create_engine('sqlite:///:memory:', echo=True)

class User(Base):
	__tablename__ = 'users'
	family_id = Column(Integer, primary_key=True, autoincrement=False)
	member_id = Column(Integer, primary_key=True, autoincrement=False, default=1)
	name = Column(String)

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)

session = Session()

john = User(family_id=1, name="john")
peter = User(family_id=1, name="peter")

session.merge(john)
session.commit()

session.merge(peter)

# a select does not issued for peter because missing a member_id in constructor
# however, member_id has default value setting...
# it supposed to take default value into account.

# A error like the following will happen.
# sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: users.family_id, users.member_id [SQL: u'INSERT INTO users (family_id, member_id, name) VALUES (?, ?, ?)'] [parameters: (1, 1, 'peter')]
session.commit()
```

[Sqlalchemy-tickets] Issue #4054: percent sign escaping in literal binds (zzzeek/sqlalchemy)

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

New issue 4054: percent sign escaping in literal binds
https://bitbucket.org/zzzeek/sqlalchemy/issues/4054/percent-sign-escaping-in-literal-binds

Michael Bayer:

```
#!python

from sqlalchemy import *

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

s = select([literal("hi % there")])


compiled = s.compile(
    dialect=e.dialect,
    compile_kwargs=dict(literal_binds=True)
)

assert e.scalar(str(compiled)) == "hi % there"

```


related to #4052 as that makes use of literals.  in addition, comment support should be consistent about its use of literal binds vs. other ad-hoc systems

[Sqlalchemy-tickets] Issue #4053: support RANGE BETWEEN x FOLLOWING and y FOLLOWING (zzzeek/sqlalchemy)

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

New issue 4053: support RANGE BETWEEN x FOLLOWING and y FOLLOWING
https://bitbucket.org/zzzeek/sqlalchemy/issues/4053/support-range-between-x-following-and-y

Michael Bayer:

apparently you can have both sides of the range be FOLLOWING (or PRECEDING I would assume).

[Sqlalchemy-tickets] Issue #4051: sqlalchemy.engine.reflection.Inspector.get_columns : documentation error for SQLAlchemy 1.2.0b1 (zzzeek/sqlalchemy)

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

New issue 4051: sqlalchemy.engine.reflection.Inspector.get_columns : documentation error for SQLAlchemy 1.2.0b1
https://bitbucket.org/zzzeek/sqlalchemy/issues/4051/sqlalchemyenginereflectioninspectorget_col

Rudolf Cardinal:

In SQLAlchemy 1.2.0b1, it's possible for `sqlalchemy.engine.reflection.Inspector.get_columns` to return the following dict as one of its results: 

    {
        'type': VARCHAR(length=255),
        'nullable': False,
        'default': None,
        'name': 'device_id',
        'comment': 'Source tablet device ID'
    }

The docs at http://docs.sqlalchemy.org/en/latest/core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_columns seem to be out of date for this in that (a) `attrs` is documented as part of the returned dictionary, but is absent in this real example; (b) `comment` is new and undocumented.

[Sqlalchemy-tickets] Issue #4050: issue #3934 regressed the behavior of exception + rollback failed (zzzeek/sqlalchemy)

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

New issue 4050: issue #3934 regressed the behavior of exception + rollback failed
https://bitbucket.org/zzzeek/sqlalchemy/issues/4050/issue-3934-regressed-the-behavior-of

Michael Bayer:

oslo.db test_cause_for_failed_flush_plus_no_savepoint, still working up local behavioral change

due to #3934

[Sqlalchemy-tickets] Issue #4049: cache key error w/ undefer group on top of defaultload, 1.2 regression w/ lazyload caching (zzzeek/sqlalchemy)

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

New issue 4049: cache key error w/ undefer group on top of defaultload, 1.2 regression w/ lazyload caching
https://bitbucket.org/zzzeek/sqlalchemy/issues/4049/cache-key-error-w-undefer-group-on-top-of

Michael Bayer:

related to #4048, we can't even get to the lazyload for this one

```
#!python


    def test_undefer_group_from_relationship_lazyload(self):
        users, Order, User, orders = \
            (self.tables.users,
             self.classes.Order,
             self.classes.User,
             self.tables.orders)

        mapper(User, users, properties=dict(
            orders=relationship(Order, order_by=orders.c.id)))
        mapper(
            Order, orders, properties=util.OrderedDict([
                ('userident', deferred(orders.c.user_id, group='primary')),
                ('description', deferred(orders.c.description,
                 group='primary')),
                ('opened', deferred(orders.c.isopen, group='primary'))
            ])
        )

        sess = create_session()
        q = sess.query(User).filter(User.id == 7).options(
            defaultload(User.orders).undefer_group('primary')
        )

        def go():
            result = q.all()
            o2 = result[0].orders[1]
            eq_(o2.opened, 1)
            eq_(o2.userident, 7)
            eq_(o2.description, 'order 3')
        self.assert_sql_count(testing.db, go, 1)

```

trace:

```
#!


Traceback (most recent call last):
  File "/home/classic/dev/sqlalchemy/test/orm/test_deferred.py", line 419, in test_undefer_group_from_relationship_lazyload
    self.assert_sql_count(testing.db, go, 1)
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/assertions.py", line 520, in assert_sql_count
    db, callable_, assertsql.CountStatements(count))
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/assertions.py", line 500, in assert_sql_execution
    result = callable_()
  File "/home/classic/dev/sqlalchemy/test/orm/test_deferred.py", line 415, in go
    o2 = result[0].orders[1]
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/orm/attributes.py", line 242, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/orm/attributes.py", line 603, in get
    value = self.callable_(state, passive)
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/orm/strategies.py", line 623, in _load_for_state
    return self._emit_lazyload(session, state, ident_key, passive)
  File "<string>", line 1, in <lambda>
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/orm/strategies.py", line 714, in _emit_lazyload
    state.load_options, effective_path
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/ext/baked.py", line 178, in _add_lazyload_options
    cache_key = opt._generate_cache_key(cache_path)
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/orm/strategy_options.py", line 389, in _generate_cache_key
    c_key = opt._generate_cache_key(path)
  File "/home/classic/dev/sqlalchemy/test/../lib/sqlalchemy/orm/strategy_options.py", line 113, in _generate_cache_key
    obj.strategy +
TypeError: can only concatenate tuple (not "NoneType") to tuple

```

[Sqlalchemy-tickets] Issue #4048: undefer_group() from relationship path has no effect (zzzeek/sqlalchemy)

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

New issue 4048: undefer_group() from relationship path has no effect
https://bitbucket.org/zzzeek/sqlalchemy/issues/4048/undefer_group-from-relationship-path-has

Michael Bayer:

in 1.1.  in 1.2 the lazy load also fails entirely:


```
#!python

    def test_undefer_group_from_relationship(self):
        users, items, order_items, Order, Item, User, orders = \
            (self.tables.users,
             self.tables.items,
             self.tables.order_items,
             self.classes.Order,
             self.classes.Item,
             self.classes.User,
             self.tables.orders)

        mapper(Item, items, properties=dict(
            description=deferred(items.c.description)))
        mapper(User, users, properties=dict(
            orders=relationship(Order, order_by=orders.c.id)))
        mapper(
            Order, orders, properties=util.OrderedDict([
                ('items', relationship(Item, secondary=order_items)),
                ('userident', deferred(orders.c.user_id, group='primary')),
                ('description', deferred(orders.c.description,
                 group='primary')),
                ('opened', deferred(orders.c.isopen, group='secondary'))
            ])
        )

        sess = create_session()
        q = sess.query(User).filter(User.id == 7).options(
            defaultload(User.orders).undefer_group('primary')
        )

        def go():
            result = q.all()
            o2 = result[0].orders[1]
            eq_(o2.opened, 1)
            eq_(o2.userident, 7)
            eq_(o2.description, 'order 3')
        self.assert_sql_count(testing.db, go, 1)

```

[Sqlalchemy-tickets] Issue #4047: mariadb 10.2 CHECK constraints don't allow NULL to pass, need to revise BOOLEAN, ENUM (zzzeek/sqlalchemy)

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

New issue 4047: mariadb 10.2 CHECK constraints don't allow NULL to pass, need to revise BOOLEAN, ENUM
https://bitbucket.org/zzzeek/sqlalchemy/issues/4047/mariadb-102-check-constraints-dont-allow

Michael Bayer:

will seek reporting this upstream as this likely breaks the SQL standard:


```
#!sql

CREATE TABLE ck_cons_test (
    -> b1 INT NULL, 
    -> b2 INT NULL,
    -> CHECK (b1 IN (0, 1)), 
    -> CHECK (b2 IN (0, 1, NULL))
    -> );
Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> INSERT INTO ck_cons_test (b1, b2) VALUES (1, 0);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> 
MariaDB [test]> INSERT INTO ck_cons_test (b1, b2) VALUES (1, NULL);
ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_2` failed for `test`.`ck_cons_test`
MariaDB [test]> 
MariaDB [test]> INSERT INTO ck_cons_test (b1, b2) VALUES (NULL, 0);
ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_1` failed for `test`.`ck_cons_test`

```

[Sqlalchemy-tickets] Issue #4046: AttributeError: 'Connection' object has no attribute '_Connection__connection' (zzzeek/sqlalchemy)

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

New issue 4046: AttributeError: 'Connection' object has no attribute '_Connection__connection'
https://bitbucket.org/zzzeek/sqlalchemy/issues/4046/attributeerror-connection-object-has-no

Daniel Birnstiel:

I am currently receiving the exception mentioned in the title on a production deployment of a flask application using SQLAlchemy.

This issue has happened before (closed in #1246 and #2317), but appears to have resurfaced again.

Some info about my environment:

```
#!bash

% py --version
Python 3.4.3
% pip freeze | grep SQLAlchemy
SQLAlchemy==1.1.4
```

Database is an RDS instance on AWS using psycopg2==2.6.2 as a driver.

Full exception log: https://gist.github.com/Birne94/e6e7ba91d44a680ba09419f7aa9f5705 (also attached to this report)

The exception log shows two different exceptions happening:

* At first the server closes a connection, most likely due to a restart on the side of the database server. The query fails, page errors. I have a custom exception handler which will rollback the failed database session. Since this was a simple SELECT, I guess this is not needed (right?).

* Further access to the database fails with the exception mentioned in the title. As mentioned above, the failed transation was rolled back so there shouldn't be any issue with this one still existing. Again, the query was a simple select which should not start any transaction.

After restarting the server (uwsgi), everything went back to normal. Unfortunately, it was a priority to get the server up and running again, so I was unable to further investigate the issue by attaching a debugger.

I would guess that SQLAlchemy should be able to handle closed connections and be able to reconnect automatically.

If this is not the case, is there anything I can do to detect operations on an invalid connection and perform a manual reconnect?

Thanks for reading.

[Sqlalchemy-tickets] Issue #4045: cx_Oracle 6.0 is out....aaaaaand breaks stuff (again) (zzzeek/sqlalchemy)

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

New issue 4045: cx_Oracle 6.0 is out....aaaaaand breaks stuff (again)
https://bitbucket.org/zzzeek/sqlalchemy/issues/4045/cx_oracle-60-is-outaaaaaand-breaks-stuff

Michael Bayer:

test failures:


```
#!

========================================================================= FAILURES ==========================================================================
____________________________________ InvalidateDuringResultTest_oracle+cx_oracle_[11.2.0.2.0].test_invalidate_on_results ____________________________________
[gw0] linux2 -- Python 2.7.11 /home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/bin/python
Traceback (most recent call last):
  File "/home/classic/dev/sqlalchemy/test/engine/test_reconnect.py", line 1015, in test_invalidate_on_results
    _assert_invalidated(result.fetchone)
  File "/home/classic/dev/sqlalchemy/test/engine/test_reconnect.py", line 665, in _assert_invalidated
    assert False
AssertionError: assert False
------------------------------------------------------------------- Captured stderr call --------------------------------------------------------------------
/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/lib/python2.7/site-packages/sqlalchemy/testing/engines.py:189: UserWarning: ReconnectFixture couldn't close connection: DPI-1054: connection cannot be closed when open statements or LOBs exist
  "close connection: %s" % e)
----------------------------------------------------------------- Captured stderr teardown ------------------------------------------------------------------
No handlers could be found for logger "sqlalchemy.pool.QueuePool"
/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/lib/python2.7/site-packages/sqlalchemy/testing/engines.py:43: UserWarning: testing_reaper couldn't rollback/close connection: DPI-1054: connection cannot be closed when open statements or LOBs exist
  "rollback/close connection: %s" % e)
________________________________________ PrePingRealTest_oracle+cx_oracle_[11.2.0.2.0].test_pre_ping_db_is_restarted ________________________________________
[gw0] linux2 -- Python 2.7.11 /home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/bin/python
Traceback (most recent call last):
  File "/home/classic/dev/sqlalchemy/test/engine/test_reconnect.py", line 957, in test_pre_ping_db_is_restarted
    conn = engine.connect()
  File "/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2091, in connect
    return self._connection_cls(self, **kwargs)
  File "/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 90, in __init__
    if connection is not None else engine.raw_connection()
  File "/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2177, in raw_connection
    self.pool.unique_connection, _connection)
  File "/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2151, in _wrap_pool_connect
    e, dialect, self)
  File "/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1465, in _handle_dbapi_exception_noconnection
    exc_info
  File "/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2147, in _wrap_pool_connect
    return fn()
  File "/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/lib/python2.7/site-packages/sqlalchemy/pool.py", line 344, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/lib/python2.7/site-packages/sqlalchemy/pool.py", line 811, in _checkout
    result = pool._dialect.do_ping(fairy.connection)
  File "/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 471, in do_ping
    cursor.execute(self._dialect_specific_select_one)
DatabaseError: (cx_Oracle.DatabaseError) DPI-1010: not connected
__________________________________________ NumericTest_oracle+cx_oracle_[11.2.0.2.0].test_many_significant_digits ___________________________________________
[gw1] linux2 -- Python 2.7.11 /home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/bin/python
Traceback (most recent call last):
  File "/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/lib/python2.7/site-packages/sqlalchemy/testing/suite/test_types.py", line 522, in test_many_significant_digits
    numbers
  File "<string>", line 2, in _do_test
  File "/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/lib/python2.7/site-packages/sqlalchemy/testing/assertions.py", line 69, in decorate
    return fn(*args, **kw)
  File "<string>", line 2, in _do_test
  File "/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/lib/python2.7/site-packages/sqlalchemy/testing/util.py", line 196, in provide_metadata
    return fn(*args, **kw)
  File "/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/lib/python2.7/site-packages/sqlalchemy/testing/suite/test_types.py", line 348, in _do_test
    eq_(result, output)
  File "/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/lib/python2.7/site-packages/sqlalchemy/testing/assertions.py", line 222, in eq_
    assert a == b, msg or "%r != %r" % (a, b)
AssertionError: set([Decimal('319438950232418000'), Decimal('31943874831932400000'), Decimal('87673.5940696542')]) != set([Decimal('319438950232418390.273596'), Decimal('31943874831932418390.01'), Decimal('87673.594069654243')])
=================================================== 3 failed, 1488 passed, 776 skipped in 277.05 seconds ====================================================
ERROR: InvocationError: '/home/classic/dev/sqlalchemy/.tox/py27-oracle-backendonly/bin/python -m pytest -n2 --dburi oracle://scott:tiger@oracle1120/xe --write-idents oracle_idents.txt --nomemory --backend-only'
__________________________________________________________________________ summary __________________________________________________________________________
ERROR:   py27-oracle-backendonly: commands failed

```