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

[Sqlalchemy-tickets] Issue #4161: convert_unicode='force' break SQLite queries (zzzeek/sqlalchemy)

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

New issue 4161: convert_unicode='force' break SQLite queries
https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/convert_unicode-force-break-sqlite-queries

Jan Koprowski:

Hi,

  We are using `convert_unicode='force'` to force MSSQL to use `'strings'` instead of `N'strings'` in queries. Most of our tests are run against SQLite. However with `convert_unicode='force'` SQLite does not return results (returns empty list of results).

  Is there any workaround to keep `convert_unicode='force'` for MSSQL and do not use it for SQLite?

[Sqlalchemy-tickets] Issue #4160: Add postgresl.regclass type for casting tablenames to oids (zzzeek/sqlalchemy)

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

New issue 4160: Add postgresl.regclass type for casting tablenames to oids
https://bitbucket.org/zzzeek/sqlalchemy/issues/4160/add-postgreslregclass-type-for-casting

Sebastian Bank:

Postgresql provides the `regclass` type to [simplify system catalog queries](https://www.postgresql.org/docs/10/static/datatype-oid.html) (reflection):

> The regclass input converter handles the table lookup according to the schema path setting, and so it does the “right thing” automatically. Similarly, casting a table's OID to regclass is handy for symbolic display of a numeric OID.

 I think it would be nice (and not too hard) to provide this in `sqlalchemy`. Maybe like this:

```python
In [1]: import sqlalchemy as sa
   ...: 
   ...: engine = sa.create_engine('postgresql://postgres@')
   ...: 
   ...: class regclass(sa.types.UserDefinedType):
   ...:     def get_col_spec(self):
   ...:         return 'regclass'
   ...: 
   ...: pga = sa.table('pg_attribute',
   ...:                *map(sa.column, ['attrelid', 'attname', 'attnotnull']))
   ...: 
   ...: query = pga.select()\
   ...:    .where(pga.c.attrelid == sa.cast('pg_class', regclass))\
   ...:    .where(~pga.c.attnotnull)
   ...: 
   ...: engine.execute(query).fetchall()
Out[1]: [(1259, 'relacl', False), (1259, 'reloptions', False)]

In [2]: engine.scalar(sa.select([sa.cast(1259, regclass)]))
Out[2]: 'pg_class'
```

[Sqlalchemy-tickets] Issue #4159: legacy setstate for loader options not tested (zzzeek/sqlalchemy)

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

New issue 4159: legacy setstate for loader options not tested
https://bitbucket.org/zzzeek/sqlalchemy/issues/4159/legacy-setstate-for-loader-options-not

Michael Bayer:

```
#!diff

diff --git a/test/orm/test_options.py b/test/orm/test_options.py
index 731aee1e5..9d3b8b702 100644
--- a/test/orm/test_options.py
+++ b/test/orm/test_options.py
@@ -890,6 +890,86 @@ class OptionsNoPropTest(_fixtures.FixtureTest):
                               joinedload(eager_option))
 
 
+class PickleTest(PathTest, QueryTest):
+
+    def _option_fixture(self, *arg):
+        return strategy_options._UnboundLoad._from_keys(
+            strategy_options._UnboundLoad.joinedload, arg, True, {})
+
+    def test_modern_opt_getstate(self):
+        User = self.classes.User
+
+        sess = Session()
+        q = sess.query(User)
+
+        opt = self._option_fixture(User.addresses)
+        eq_(
+            opt.__getstate__(),
+            {
+                '_is_chain_link': False,
+                'local_opts': {},
+                'is_class_strategy': False,
+                'path': [(User, 'addresses', None)],
+                'propagate_to_loaders': True,
+                '_to_bind': [opt],
+                'strategy': (('lazy', 'joined'),)}
+        )
+
+    def test_modern_opt_setstate(self):
+        User = self.classes.User
+
+        opt = strategy_options._UnboundLoad.__new__(
+            strategy_options._UnboundLoad)
+        state = {
+            '_is_chain_link': False,
+            'local_opts': {},
+            'is_class_strategy': False,
+            'path': [(User, 'addresses', None)],
+            'propagate_to_loaders': True,
+            '_to_bind': [opt],
+            'strategy': (('lazy', 'joined'),)}
+
+        opt.__setstate__(state)
+
+        query = create_session().query(User)
+        attr = {}
+        load = opt._bind_loader(
+            [ent.entity_zero for ent in query._mapper_entities],
+            query._current_path, attr, False)
+
+        eq_(
+            load.path,
+            inspect(User)._path_registry
+            [User.addresses.property][inspect(self.classes.Address)])
+
+    def test_legacy_opt_setstate(self):
+        User = self.classes.User
+
+        opt = strategy_options._UnboundLoad.__new__(
+            strategy_options._UnboundLoad)
+        state = {
+            '_is_chain_link': False,
+            'local_opts': {},
+            'is_class_strategy': False,
+            'path': [(User, 'addresses')],
+            'propagate_to_loaders': True,
+            '_to_bind': [opt],
+            'strategy': (('lazy', 'joined'),)}
+
+        opt.__setstate__(state)
+
+        query = create_session().query(User)
+        attr = {}
+        load = opt._bind_loader(
+            [ent.entity_zero for ent in query._mapper_entities],
+            query._current_path, attr, False)
+
+        eq_(
+            load.path,
+            inspect(User)._path_registry
+            [User.addresses.property][inspect(self.classes.Address)])
+
+
 class LocalOptsTest(PathTest, QueryTest):
     @classmethod
     def setup_class(cls):

```

[Sqlalchemy-tickets] Issue #4158: pyodbc SQL Server cursor.fast_executemany (zzzeek/sqlalchemy)

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

New issue 4158: pyodbc SQL Server cursor.fast_executemany
https://bitbucket.org/zzzeek/sqlalchemy/issues/4158/pyodbc-sql-server-cursorfast_executemany

Danny Hollandmoritz:

Hi,

pyodbc implemented a different execute_many handling for native Microsoft ODBC drivers, which greatly speeds up bulk inserts. (https://github.com/mkleehammer/pyodbc/issues/120)

To use it, "fast_executemany" needs to be set to True on the pyodbc cursor instance.

At the moment sqlalchemy does not use this flag.

To be able to use this feature with sqlalchemy, i changed dialects.mssql.pyodbc.MSExecutionContext_pyodbc to:


```
#!python

class MSExecutionContext_pyodbc(MSExecutionContext):
    _embedded_scope_identity = False

    def create_cursor(self):
        if self._use_server_side_cursor():
            self._is_server_side = True
            return self.create_server_side_cursor()
        else:
            self._is_server_side = False
            cursor = self._dbapi_connection.cursor()
            cursor.fast_executemany = True
            return cursor


    def pre_exec(self):
        """where appropriate, issue "select scope_identity()" in the same
        statement.

```

But i am sure, there is a better solution for this. (e.g. as a connection string parameter when initiating a sqlalchemy engine)

Would it be possible to implement the usage of pyodbc's fast_executemany natively for the pyodbc mssql dialect?

Greetings
Danny

[Sqlalchemy-tickets] Issue #4157: restore setinputsizes for cx_oracle.TIMESTAMP (zzzeek/sqlalchemy)

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

New issue 4157: restore setinputsizes for cx_oracle.TIMESTAMP
https://bitbucket.org/zzzeek/sqlalchemy/issues/4157/restore-setinputsizes-for

Michael Bayer:

the removal of most setinputsizes impacted the cx_oracle.TIMESTAMP datatype which apparently stores fractional seconds.

Gerrit at https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/628/.

[Sqlalchemy-tickets] Issue #4156: Error when selectin eager loading on polymorphic subtypes (zzzeek/sqlalchemy)

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

New issue 4156: Error when selectin eager loading on polymorphic subtypes
https://bitbucket.org/zzzeek/sqlalchemy/issues/4156/error-when-selectin-eager-loading-on

Theron Luhn:

I'm attempting to use selectin eager loading on polymorphic subtypes using the strategy laid out here:  http://docs.sqlalchemy.org/en/latest/orm/inheritance_loading.html#eager-loading-of-specific-or-polymorphic-subtypes

Here's some example code:


```
#!python

from sqlalchemy import String, Integer, Column, create_engine, ForeignKey, inspect
from sqlalchemy.orm import relationship, Session, subqueryload, selectinload
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class Company(Base):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    employees = relationship('Employee')


class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    type = Column(String, nullable=False)
    name = Column(String, nullable=False)
    company_id = Column(Integer, ForeignKey('company.id'), nullable=False)

    __mapper_args__ = {
        'polymorphic_on': 'type',
        'with_polymorphic': '*',
    }


class Programmer(Employee):
    __tablename__ = 'programmer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    languages = relationship('Language')

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


class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    something = Column(String)

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


class Language(Base):
    __tablename__ = 'language'
    id = Column(Integer, primary_key=True)
    programmer_id = Column(
        Integer,
        ForeignKey('programmer.id'),
        nullable=False,
    )
    name = Column(String, nullable=False)


engine = create_engine('postgresql://localhost:5432/sa', )
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
db = Session(engine, enable_baked_queries=False)


company = Company(
    id=1,
    name='Foobar Corp',
    employees=[Programmer(
        id=1,
        name='John Smith',
        languages=[Language(id=1, name='Python')],
    ), Manager(
        id=2,
        name='Foo',
        something='foo',
    )],
)
db.add(company)
db.flush()
db.expunge_all()

company = db.query(Company).filter(
    Company.id == 1,
).options(
    selectinload(Company.employees.of_type(Programmer)).selectinload(Programmer.languages),
).one()
print(company.employees)
```

This results in:

```
Traceback (most recent call last):
  File "scratchpad/sqlalchemy_polymorphic_loading_bug.py", line 86, in <module>
    selectinload(Company.employees.of_type(Programmer)).selectinload(Programmer.languages),
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2837, in one
    ret = self.one_or_none()
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2807, in one_or_none
    ret = list(self)
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 97, in instances
    util.raise_from_cause(err)
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 85, in instances
    post_load.invoke(context, path)
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 750, in invoke
    self.load_keys, *arg, **kw)
  File "<string>", line 1, in <lambda>
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/orm/strategies.py", line 1966, in _load_for_path
    lambda x: x[0]
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/orm/strategies.py", line 1958, in <dictcomp>
    data = {
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 97, in instances
    util.raise_from_cause(err)
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 85, in instances
    post_load.invoke(context, path)
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 750, in invoke
    self.load_keys, *arg, **kw)
  File "<string>", line 1, in <lambda>
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/orm/strategies.py", line 1987, in _load_for_path
    state.get_impl(self.key).set_committed_value(
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/orm/state.py", line 423, in get_impl
    return self.manager[key].impl
KeyError: 'languages'
```

This only happens when a Manager object is in the employees relationship.  If employees only contains programmers, it works fine.

This only happens when using selectinload, using subqueryload works fine.

I'm running the current master branch (1.2.0 doesn't work due to #4153)

[Sqlalchemy-tickets] Issue #4155: can't send column objects as keys to insert w/ multivalues (zzzeek/sqlalchemy)

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

New issue 4155: can't send column objects as keys to insert w/ multivalues
https://bitbucket.org/zzzeek/sqlalchemy/issues/4155/cant-send-column-objects-as-keys-to-insert

Michael Bayer:

ML thread https://groups.google.com/forum/?hl=en#!topic/sqlalchemy/t8a6SLTYrIc

even though all the docs show string names being sent to insert.values(), you can use columns as keys inside a dictionary as well, just as we need to do with UPDATE some times.  It doesn't work for multi-values though:


```
#!python


from sqlalchemy import MetaData, String, Integer, Table, Column
from sqlalchemy.dialects.postgresql.base import PGDialect

m = MetaData()

t = Table('mytable', m,
    Column('int_col', Integer),
    Column('str_col', String),
)

print("Case 1")

print(t.insert().values(
    {t.c.str_col:"string", t.c.int_col:2}
).compile(dialect=PGDialect()))

print("Case 2")

print(t.insert().values(
    [
        {t.c.str_col:"str", t.c.int_col:1}
    ]
).compile(dialect=PGDialect()))

print("Case 3")

print(t.insert().values(
    [
        {t.c.str_col:"string", t.c.int_col:1},
        {t.c.str_col:"text", t.c.int_col:2}
    ]
).compile(dialect=PGDialect()))


```

gerrit at https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/626 fixes.

[Sqlalchemy-tickets] Issue #4154: [MSSQL] Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS' (zzzeek/sqlalchemy)

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

New issue 4154: [MSSQL] Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'
https://bitbucket.org/zzzeek/sqlalchemy/issues/4154/mssql-incorrect-syntax-near

Jan Koprowski:

Code:

```
#!python

@compiles(case_insensitive, 'mssql')
def compile_mssql_case_insensitive(element: case_insensitive, compiler: SQLCompiler, **kw: str) -> str:
    return compiler.process(collate(element.clause, 'SQL_Latin1_General_CP1_CI_AS'))
```
Version 1.1.4 produce query:
```
#!sql

[...] COLLATE SQL_Latin1_General_CP1_CI_AS
```

Version 1.2.0 produce query:
```
#!sql

[...] COLLATE [SQL_Latin1_General_CP1_CI_AS]
```

which cause:
**[S0001][102] Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'**

error

[Sqlalchemy-tickets] Issue #4153: Eager loading of specific polymorphic subtypes triggers errors in lazy loaded relationships (zzzeek/sqlalchemy)

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

New issue 4153: Eager loading of specific polymorphic subtypes triggers errors in lazy loaded relationships
https://bitbucket.org/zzzeek/sqlalchemy/issues/4153/eager-loading-of-specific-polymorphic

Theron Luhn:

Congrats on 1.2.0 and thanks for your hard work!  I'm excited to upgrade and especially looking forward to selectin loading.

While attempting to upgrade, I'm getting errors caused by eager loading on polymorphic subtypes (the loading strategy detailed [here](http://docs.sqlalchemy.org/en/latest/orm/inheritance_loading.html#eager-loading-of-specific-or-polymorphic-subtypes)).  It was very difficult to pin down, but I finally am able to make a reproducible test case:


```
#!python

from sqlalchemy import String, Integer, Column, create_engine, ForeignKey, inspect
from sqlalchemy.orm import relationship, Session, subqueryload
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class Company(Base):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    employees = relationship('Employee')
    slogans = relationship('Slogan')


class Slogan(Base):
    __tablename__ = 'slogan'
    id = Column(Integer, primary_key=True)
    text = Column(String)
    company_id = Column(Integer, ForeignKey('company.id'), nullable=False)


class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    type = Column(String, nullable=False)
    name = Column(String, nullable=False)
    company_id = Column(Integer, ForeignKey('company.id'), nullable=False)

    __mapper_args__ = {
        'polymorphic_on': 'type',
        'with_polymorphic': '*',
    }


class Programmer(Employee):
    __tablename__ = 'programmer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    languages = relationship('Language')

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


class Language(Base):
    __tablename__ = 'language'
    id = Column(Integer, primary_key=True)
    programmer_id = Column(
        Integer,
        ForeignKey('programmer.id'),
        nullable=False,
    )
    name = Column(String, nullable=False)


engine = create_engine('postgresql://localhost:5432/sa')
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
db = Session(engine)


company = Company(
    id=1,
    name='Foobar Corp',
    employees=[Programmer(
        id=1,
        name='John Smith',
        languages=[Language(id=1, name='Python')],
    )],
    slogans=[Slogan(
        id=1,
        text='Because I said so.',
    )],
)
db.add(company)
db.flush()
db.expunge_all()

company = db.query(Company).filter(
    Company.id == 1,
).options(
    subqueryload(Company.employees.of_type(Programmer)).subqueryload(Programmer.languages),
).one()
print(company.employees)  # Eager-loaded relationships work
print(company.slogans)  # Any lazy-loaded relationships trigger error

```

This script triggers the following error:

```
Traceback (most recent call last):
  File "polybug.py", line 87, in <module>
    print(company.slogans)
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/orm/attributes.py", line 242, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/orm/attributes.py", line 603, in get
    value = self.callable_(state, passive)
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/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 "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/orm/strategies.py", line 703, in _emit_lazyload
    state.load_options, effective_path
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/ext/baked.py", line 178, in _add_lazyload_options
    cache_key = opt._generate_cache_key(cache_path)
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/orm/strategy_options.py", line 404, in _generate_cache_key
    c_key = opt._generate_cache_key(path)
  File "/Users/luhn/Code/revenue/.env/lib/python3.6/site-packages/sqlalchemy/orm/strategy_options.py", line 93, in _generate_cache_key
    for token in chopped:
TypeError: 'NoneType' object is not iterable
```

This is running on Python 3.6.2 with SQLAlchemy 1.2.0.  SQLAlchemy 1.1.10 behaves as expected and does not trigger an error.

I'm using psycopg2 v2.7.3.2 and Postgres 10.1

[Sqlalchemy-tickets] Issue #4152: sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column '0'" (zzzeek/sqlalchemy)

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

New issue 4152: sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column '0'"
https://bitbucket.org/zzzeek/sqlalchemy/issues/4152/sqlalchemyexcnosuchcolumnerror-could-not

Shikari Shambhu:

Hello, trying to troubleshoot. Can anyone pls help ? 



**Versions:** 

WIN 10, 64-bit

Python 2.7.12 |Anaconda 4.2.0 (64-bit)| (default, Jun 29 2016, 11:07:13) [MSC v.1500 64 bit (AMD64)] on win32

sqlite3.version : '2.6.0'
sqlite3.sqlite_version : '3.8.11'
sqlalchemy.__version__ :  '1.1.5'

**Code>	**

	import pandas as pd
	import sqlalchemy as sa
	import time

	host = 'redshift.amazonaws.com'
	port = '1234'
	user = 'user'
	password = 'password'
	database = 'db'
	url =  "postgresql://%s:%s@%s:%s/%s" % (user,password,host,port,database)
	engine = sa.create_engine(url)

	query = 'select * from table limit 10'

	df = pd.read_sql_query(query, engine)

	engine.connect().close()


** Error >  **
*Too long, entire error attached as TXT*

  File "C:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\engine\result.py", line 563, in _key_fallback
    expression._string_or_unprintable(key))
sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column '0'"

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #4151: Rollback of nested and subtransactions incomplete, causing DetachedInstanceError (zzzeek/sqlalchemy)

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

New issue 4151: Rollback of nested and subtransactions incomplete, causing DetachedInstanceError
https://bitbucket.org/zzzeek/sqlalchemy/issues/4151/rollback-of-nested-and-subtransactions

Torsten Landschoff:

We hit a curious bug (so it seems to me at least) in SQLAlchemy which causes some erratic problems in our application. Basically, a rollback after a completed subtransaction (same for nested transactions) seems incomplete.

I had a hard time to reproduce this in a small example but funny enough in our application it depends on the garbage collector. Guess there is a cycle somewhere but I was unable to find it.

The core of the problem is exposed by this code:

```
#!python
with session.begin(subtransactions=True):
    new_videos = Directory(name="New Videos")
    new_videos.entries = videos.entries
    documents.entries[0] = new_videos
    session.flush()  # this is required to trigger the problem

    video = File(name="falcon_landing.mp4", content="Space Ship Landing")
    new_videos.entries[0] = video
    # new_videos.entries.append(video)  # instead of replacing works. wtf?

print("Pre rollback session content: {0}".format(list(session)))
session.rollback()
```

The example uses an association proxy to represent the entries of a directory (like in a filesystem, but like in git a folder can be referenced by multiple parent folders).

By assigning `new_videos.entries = videos.entries`, these association objects are copied. The code then replaces the first entry via `new_videos.entries[0] = video`. This causes an update to the underlying association object.

After the rollback, the association object should get expunged, because the cause for its creation was undone, but in fact it stays alive in the session. So far nothing bad happens, but the next action using the session can trigger an attribute refresh which fails, interestingly, because the DirectoryEntry instance is allegedly not bound to the session:

```
#!python
$ python3 incomplete_rollback.py
Initial session content: [<Directory: Documents (1)>, <Directory: Videos (2)>]
Pre rollback session content: [<Directory: Videos (2)>, <Directory: New Videos (5)>, <DirectoryEntry @ 7fd19e5b3eb8>, <File: falcon_landing.mp4 (6)>, <DirectoryEntry @ 7fd19e5b37b8>, <Directory: Documents (1)>, <File: dancing.mp4 (3)>, <DirectoryEntry @ 7fd19e5b3668>, <DirectoryEntry @ 7fd19e5b3b00>]
After rollback session content: [<Directory: Videos (2)>, <Directory: Documents (1)>, <DirectoryEntry @ 7fd19e5b37b8>]
Traceback (most recent call last):
  File "incomplete_rollback.py", line 100, in <module>
    session.commit()
[...]
sqlalchemy.orm.exc.DetachedInstanceError: Instance <DirectoryEntry at 0x7fd19e5b37b8> is not bound to a Session; attribute refresh operation cannot proceed (Background on this error at: http://sqlalche.me/e/bhk3)
```

I tested this with pysqlite2 (in python 2.7) as well as with sqlite3 (from stock python3.5), with SQLAlchemy 1.1.13 as well as 1.2.0. Older versions behave differently in that they report a primary key conflict.

```
#!python
$ pip install sqlalchemy==1.0.19
[...]
Installing collected packages: sqlalchemy
  Found existing installation: SQLAlchemy 1.2.0
    Uninstalling SQLAlchemy-1.2.0:
      Successfully uninstalled SQLAlchemy-1.2.0
Successfully installed sqlalchemy-1.0.19
$ python incomplete_rollback.py
Initial session content: [<Directory: Documents (1)>, <Directory: Videos (2)>]
Pre rollback session content: [<File: falcon_landing.mp4 (6)>, <DirectoryEntry @ 7f0643176d90>, <Directory: Documents (1)>, <Directory: New Videos (5)>, <File: dancing.mp4 (3)>, <DirectoryEntry @ 7f06431bab50>, <DirectoryEntry @ 7f06431891d0>, <DirectoryEntry @ 7f0643189150>, <Directory: Videos (2)>]
After rollback session content: [<Directory: Documents (1)>, <DirectoryEntry @ 7f06431bab50>, <Directory: Videos (2)>]
Traceback (most recent call last):
  File "incomplete_rollback.py", line 100, in <module>
    session.commit()
  File ".../python2.7/site-packages/sqlalchemy/orm/session.py", line 896, in commit
    self.transaction.commit()
  [...]
  File ".../python2.7/site-packages/sqlalchemy/orm/persistence.py", line 305, in _organize_states_for_save
    state_str(existing)))
sqlalchemy.orm.exc.FlushError: New instance <DirectoryEntry at 0x7f0643189b10> with identity key (<class '__main__.DirectoryEntry'>, (5, 3)) conflicts with persistent instance <DirectoryEntry at 0x7f06431bab50>
```

The following patch seems to fix this problem, I did not check for adverse effects though because I have no idea what I am doing here ;-)

```
#!diff
diff --git a/lib/sqlalchemy/orm/session.py b/lib/sqlalchemy/orm/session.py
index bd0bf91..a5ac519 100644
--- a/lib/sqlalchemy/orm/session.py
+++ b/lib/sqlalchemy/orm/session.py
@@ -346,7 +346,8 @@ class SessionTransaction(object):
         for s, (oldkey, newkey) in self._key_switches.items():
             self.session.identity_map.safe_discard(s)
             s.key = oldkey
-            self.session.identity_map.replace(s)
+            if s.key in self.session.identity_map:
+                self.session.identity_map.replace(s)

         for s in set(self._deleted).union(self.session._deleted):
             self.session._update_impl(s, revert_deletion=True)
```

Basically, it seems like SQLAlchemy noticed that the state was dropped (due to the rollback) and removed it from the
identity map. However, because the state also had changes, it was reinstated with the original key after first
correctly removing it.

I hope you can interpret my ramblings ;-)

[Sqlalchemy-tickets] Issue #4150: .contains fails with "chained" association proxies in 1.2 (zzzeek/sqlalchemy)

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

New issue 4150: .contains fails with "chained" association proxies in 1.2
https://bitbucket.org/zzzeek/sqlalchemy/issues/4150/contains-fails-with-chained-association

dairiki NA:

The attached script fails with `sqlalchemy.exc.InvalidRequestError`: `contains() doesn't apply to a scalar endpoint; use ==` in SQLAlchemy==1.2.  It works with SQLAlchemy==1.1.15 (and before).

The crux here is:

```
#!python


Team.events = association_proxy('eventteams', 'event')
```


where the proxied attribute is itself an association proxy:

```
#!python


EventTeam.event = association_proxy('division', 'event')
```

In this case the expression `Team.events.contains(some_event)` is what elicits the exception.

[Sqlalchemy-tickets] Issue #4149: Hybrid propert behavior changed between 1.x and 1.2 releases (zzzeek/sqlalchemy)

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

New issue 4149: Hybrid propert behavior changed between 1.x and 1.2 releases
https://bitbucket.org/zzzeek/sqlalchemy/issues/4149/hybrid-propert-behavior-changed-between-1x

Marcin Lulek:

Hello,

I'm not sure if I was always using SA in wrong way or there is a bug in 1.2 version.

Setters need to be named same way as hybrid property - this test case works in 1.x and breaks in 1.2. I did notice that in the examples in docs the property name is same in both places, though.

Error I'm getting is:

  File "/home/ergo/workspace/ziggurat_cms/.venv/lib/python3.6/site-packages/sqlalchemy/ext/hybrid.py", line 873, in __set__
    raise AttributeError("can't set attribute")
AttributeError: can't set attribute

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #4148: feature request: [MSSQL] SELECT * INTO #temptable FROM table (zzzeek/sqlalchemy)

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

New issue 4148: feature request: [MSSQL] SELECT * INTO #temptable FROM table
https://bitbucket.org/zzzeek/sqlalchemy/issues/4148/feature-request-mssql-select-into

Eugene Rymarev:

Hello, everyone!
Please add to SQLAlchemy support requests like this

```
#!sql

SELECT *
INTO #temptable
FROM table
```

[Sqlalchemy-tickets] Issue #4147: Incorrect handling of complex indexes by Table.tometadata (zzzeek/sqlalchemy)

[Paweł S. <iss...@bi...>]

New issue 4147: Incorrect handling of complex indexes by Table.tometadata
https://bitbucket.org/zzzeek/sqlalchemy/issues/4147/incorrect-handling-of-complex-indexes-by

Paweł Stiasny:

Current implementation Table.tometada doesn't correctly support functional and text indexes.

Consider the following test:
```diff
diff --git a/test/sql/test_metadata.py b/test/sql/test_metadata.py
index 45eb594..25ac689 100644
--- a/test/sql/test_metadata.py
+++ b/test/sql/test_metadata.py
@@ -1042,18 +1042,21 @@ class ToMetaDataTest(fixtures.TestBase, ComparesTables):
 
     def test_indexes(self):
         meta = MetaData()
 
         table = Table('mytable', meta,
                       Column('id', Integer, primary_key=True),
                       Column('data1', Integer, index=True),
                       Column('data2', Integer),
+                      Index('text', text('data1 + 1')),
                       )
-        Index('multi', table.c.data1, table.c.data2),
+        Index('multi', table.c.data1, table.c.data2)
+        Index('func', func.abs(table.c.data1))
+        Index('multi-func', table.c.data1, func.abs(table.c.data2))
 
         meta2 = MetaData()
         table_c = table.tometadata(meta2)
 
         def _get_key(i):
             return [i.name, i.unique] + \
                 sorted(i.kwargs.items()) + \
                 list(i.columns.keys())

```

* The `text` expression index is ignored
* `func` is improperly classified as the default index of a column and ignored
* In `multi-func` the second component is incorrect (should be a Function, is a Column)

[Sqlalchemy-tickets] Issue #4146: common handling of `sslmode` and `sslrootcert` URL params for pg8000 (zzzeek/sqlalchemy)

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

New issue 4146: common handling of `sslmode` and `sslrootcert` URL params for pg8000
https://bitbucket.org/zzzeek/sqlalchemy/issues/4146/common-handling-of-sslmode-and-sslrootcert

Glyph:

In order to ensure a secure connection with psycopg2, you can add `sslmode=verify-full&sslrootcert=rds.root.pem`.

However, these parameters will be ignored with pg8000 which may result in an insecure connection when a secure one is expected.

It would be nice if these parameters were handled consistently so postgres users could switch drivers without getting tripped up by this nuance.

Unfortunately only `sslmode` can be implemented right now, as `sslrootcert` requires support from pg8000 itself; filed here https://github.com/mfenniak/pg8000/issues/148

[Sqlalchemy-tickets] Issue #4145: Error on reflecting database schema (zzzeek/sqlalchemy)

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

New issue 4145: Error on reflecting database schema
https://bitbucket.org/zzzeek/sqlalchemy/issues/4145/error-on-reflecting-database-schema

Mark Muzenhardt:

If I try to reflect a database schema exactly like documented it always returns a TypeError. The underlying code is:


```
#!python

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
import sqlalchemy

print(sqlalchemy.__version__)

Base = automap_base()
engine = create_engine("mysql+mysqlconnector://schreiben:schreiben@ELIATEST:4136/mand11")

Base.prepare(engine, reflect=True)
sg_auf_artikel = Base.classes.sg_auf_artikel

session = Session(engine)
result = session.query(sg_auf_artikel).filter(sg_auf_artikel.SG_AUF_ARTIKEL_PK == 1).first()

print(result)

```

And the resulting traceback reads like this:


```
#!python

Traceback (most recent call last):
  File "C:\Users\Red Rooster\Desktop\elia_reflect.py", line 11, in <module>
    Base.prepare(engine, reflect=True)
  File "C:\Python34\lib\site-packages\sqlalchemy-1.2.0b3-py3.4-win32.egg\sqlalchemy\ext\automap.py", line 754, in prepare
    autoload_replace=False
  File "C:\Python34\lib\site-packages\sqlalchemy-1.2.0b3-py3.4-win32.egg\sqlalchemy\sql\schema.py", line 3950, in reflect
    Table(name, self, **reflect_opts)
  File "C:\Python34\lib\site-packages\sqlalchemy-1.2.0b3-py3.4-win32.egg\sqlalchemy\sql\schema.py", line 445, in __new__
    metadata._remove_table(name, schema)
  File "C:\Python34\lib\site-packages\sqlalchemy-1.2.0b3-py3.4-win32.egg\sqlalchemy\util\langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Python34\lib\site-packages\sqlalchemy-1.2.0b3-py3.4-win32.egg\sqlalchemy\util\compat.py", line 187, in reraise
    raise value
  File "C:\Python34\lib\site-packages\sqlalchemy-1.2.0b3-py3.4-win32.egg\sqlalchemy\sql\schema.py", line 440, in __new__
    table._init(name, metadata, *args, **kw)
  File "C:\Python34\lib\site-packages\sqlalchemy-1.2.0b3-py3.4-win32.egg\sqlalchemy\sql\schema.py", line 522, in _init
    include_columns, _extend_on=_extend_on)
  File "C:\Python34\lib\site-packages\sqlalchemy-1.2.0b3-py3.4-win32.egg\sqlalchemy\sql\schema.py", line 535, in _autoload
    _extend_on=_extend_on
  File "C:\Python34\lib\site-packages\sqlalchemy-1.2.0b3-py3.4-win32.egg\sqlalchemy\engine\base.py", line 1545, in run_callable
    return callable_(self, *args, **kwargs)
  File "C:\Python34\lib\site-packages\sqlalchemy-1.2.0b3-py3.4-win32.egg\sqlalchemy\engine\default.py", line 389, in reflecttable
    table, include_columns, exclude_columns, **opts)
  File "C:\Python34\lib\site-packages\sqlalchemy-1.2.0b3-py3.4-win32.egg\sqlalchemy\engine\reflection.py", line 602, in reflecttable
    table_name, schema, **table.dialect_kwargs)
  File "C:\Python34\lib\site-packages\sqlalchemy-1.2.0b3-py3.4-win32.egg\sqlalchemy\engine\reflection.py", line 312, in get_table_options
    info_cache=self.info_cache, **kw)
  File "<string>", line 2, in get_table_options
  File "C:\Python34\lib\site-packages\sqlalchemy-1.2.0b3-py3.4-win32.egg\sqlalchemy\engine\reflection.py", line 54, in cache
    ret = fn(self, con, *args, **kw)
  File "C:\Python34\lib\site-packages\sqlalchemy-1.2.0b3-py3.4-win32.egg\sqlalchemy\dialects\mysql\base.py", line 1956, in get_table_options
    connection, table_name, schema, **kw)
  File "C:\Python34\lib\site-packages\sqlalchemy-1.2.0b3-py3.4-win32.egg\sqlalchemy\dialects\mysql\base.py", line 2095, in _parsed_state_or_create
    info_cache=kw.get('info_cache', None)
  File "<string>", line 2, in _setup_parser
  File "C:\Python34\lib\site-packages\sqlalchemy-1.2.0b3-py3.4-win32.egg\sqlalchemy\engine\reflection.py", line 54, in cache
    ret = fn(self, con, *args, **kw)
  File "C:\Python34\lib\site-packages\sqlalchemy-1.2.0b3-py3.4-win32.egg\sqlalchemy\dialects\mysql\base.py", line 2121, in _setup_parser
    if re.match(r'^CREATE (?:ALGORITHM)?.* VIEW', sql):
  File "C:\Python34\lib\re.py", line 160, in match
    return _compile(pattern, flags).match(string)
TypeError: can't use a string pattern on a bytes-like object

```

Note: That already worked absolutely fine at Python version 2.7. It seems that the code is not ready for Python 3.4 (which I use in that project right now).

[Sqlalchemy-tickets] Issue #4144: <sqlalchemy.dialects.postgresql.dml.Insert>.__str__() fails when printing dialect-specific columns. (zzzeek/sqlalchemy)

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

New issue 4144: <sqlalchemy.dialects.postgresql.dml.Insert>.__str__() fails when printing dialect-specific columns.
https://bitbucket.org/zzzeek/sqlalchemy/issues/4144/__str__-fails-when-printing-dialect

Michael Overmeyer:

I was trying to debug an issue I was having with my PostgreSQL query and wanted to print it out in order to see the SQL.

But I ran into an issue where the `print stmt` was failing.

Running this example code:

```
#!python

from sqlalchemy import Column, Integer, Sequence, String
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class FileMetadata(Base):
    __tablename__ = 'filenames'

    iid = Column(Integer, Sequence('{}_iid_seq'.format(__tablename__)))
    filename = Column(String, primary_key=True)

    @staticmethod
    def print_test_insert():
        table = FileMetadata.__table__
        stmt = postgresql.insert(table).values({'filename': "hello.txt"})
        print postgresql.dialect, type(stmt)
        print stmt


FileMetadata.print_test_insert()

```

You get an unexpected exception:

```
Traceback (most recent call last):
  File "test_bug.py", line 22, in <module>
    FileMetadata.print_test_insert()
  File "test_bug.py", line 19, in print_test_insert
    print stmt
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 448, in __str__
    return unicode(self.compile()).encode('ascii', 'backslashreplace')
  File "<string>", line 1, in <lambda>
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 436, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 442, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 435, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 216, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 242, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1968, in visit_insert
    self, insert_stmt, crud.ISINSERT, **kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/crud.py", line 57, in _setup_crud_params
    return _get_crud_params(compiler, stmt, **kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/crud.py", line 137, in _get_crud_params
    _col_bind_name, check_columns, values, kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/crud.py", line 290, in _scan_cols
    values, kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/crud.py", line 477, in _append_param_insert_hasdefault
    proc = compiler.process(c.default, **kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 242, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "env/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 909, in visit_sequence
    self.dialect.name
NotImplementedError: Dialect 'default' does not support sequence increments.

```

I would have expected it to work and printed something like:

```
INSERT INTO filenames (iid, filename) VALUES (nextval('filenames_iid_seq'), :filename)
```

As the Insert statement was created using the postgresql dialect.

It seems to be using the SQLCompiler.visit_sequence() instead of the expected PGCompiler.visit_sequence().


Tested with both SQLAlchemy 1.1 and master branch, on both CentOS 7 and Windows with Python 2.7.

[Sqlalchemy-tickets] Issue #4143: Inconsistent results with count & all under a combination of many-to-many, outerjoin, or & contains (zzzeek/sqlalchemy)

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

New issue 4143: Inconsistent results with count & all under a combination of many-to-many, outerjoin, or & contains
https://bitbucket.org/zzzeek/sqlalchemy/issues/4143/inconsistent-results-with-count-all-under

Fayaz Khan:

In this specific scenario, `query.count()` & `len(query.all())` do not match:

```
#!python

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


Base = declarative_base()
promo_like_association = Table(
    'promo_like_association', Base.metadata,
    Column('promo_id', ForeignKey('promo.id'), primary_key=True),
    Column('like_id', ForeignKey('like.id'), primary_key=True))
user_like_association = Table(
    'user_like_association', Base.metadata,
    Column('user_id', ForeignKey('user.id'), primary_key=True),
    Column('like_id', ForeignKey('like.id'), primary_key=True))


class Like(Base):
    __tablename__ = 'like'
    id = Column(Integer, primary_key=True)
    users = relationship(
        'User', secondary=user_like_association, backref='likes')


class Promo(Base):
    __tablename__ = 'promo'
    id = Column(Integer, primary_key=True)
    likes = relationship(Like, secondary=promo_like_association)


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)


def main():
    database_uri = 'sqlite:///'
    engine = create_engine(database_uri)
    Session = sessionmaker(bind=engine)
    session = Session()
    Base.metadata.create_all(engine)
    user = User(likes=[Like()])
    session.add(user)
    session.add(Promo())
    check(session, Like.users.any(id=user.id))
    check(session, Like.users.contains(user))
    user.likes.append(Like())
    check(session, Like.users.any(id=user.id))
    check(session, Like.users.contains(user))  # Fails


def check(session, condition):
    # query tries to fetch promos for user with common likes
    # or promos that have no specific likes at all.
    query = (session.query(Promo).outerjoin(Promo.likes)
             .filter(condition | ~Promo.likes.any()))
    assert query.count() == len(query.all()) == 1


if __name__ == '__main__':
    main()

```

This will fail with:

```
#!python

Traceback (most recent call last):
  File "test_sqla_contains.py", line 60, in <module>
    main()
  File "test_sqla_contains.py", line 48, in main
    check(session, Like.users.contains(user))  # Fails
  File "test_sqla_contains.py", line 56, in check
    assert query.count() == len(query.all()) == 1
AssertionError

```

I'm working around the issue using `any(id=...)` instead of `contains()`.

[Sqlalchemy-tickets] Issue #4142: SQLAlchemy accesses attribute table of custom ColumnElement (zzzeek/sqlalchemy)

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

New issue 4142: SQLAlchemy accesses attribute table of custom ColumnElement
https://bitbucket.org/zzzeek/sqlalchemy/issues/4142/sqlalchemy-accesses-attribute-table-of

dima-starosud:

This is using sqlalchemy 1.2.0b3.

Please considering following code snippet. It fails with an error (shown in code). However there is a workaround to just add `table = None` to `my_literal`, but I suppose this may lead to other problems.

```
#!python

        Base = declarative_base()

        class my_literal(expression.ColumnElement):
            type = Unicode
            # table = None  # oO

        @compiles(my_literal)
        def default_my_literal(element, compiler, **kw):
            return "'my literal'"

        class _A(Base):
            __tablename__ = 'a_table'
            id = Column(Integer, primary_key=True)
            ml = column_property(my_literal())
            __mapper_args__ = {'polymorphic_on': ml}

        class _B(Base):
            __tablename__ = 'b_table'
            id = Column(Integer, primary_key=True)
            a_id = Column(Integer, ForeignKey(_A.id))
            ml = column_property(my_literal())
            __mapper_args__ = {'polymorphic_on': ml}

        class _C(Base):
            __tablename__ = 'c_table'
            id = Column(Integer, primary_key=True)
            b_id = Column(Integer, ForeignKey(_B.id))
            ml = column_property(my_literal())
            __mapper_args__ = {'polymorphic_on': ml}

        class A(_A):
            __mapper_args__ = {'polymorphic_identity': 'my literal'}

        class B(_B):
            __mapper_args__ = {'polymorphic_identity': 'my literal'}

        class C(_C):
            __mapper_args__ = {'polymorphic_identity': 'my literal'}

        B.a = relationship(A, backref='bs')
        C.b = relationship(B, backref='cs')
        A.cs = association_proxy('bs', 'cs')

        Base.metadata.create_all(bind=session.connection())

        a = A(bs=[B(cs=[C(), C()]), B(cs=[C(), C()])])
        session.add(a)
        session.flush()
        session.expire_all()
        assert a.cs  # AttributeError: Neither 'my_literal' object
                     # nor 'Comparator' object has an attribute 'table'

```

[Sqlalchemy-tickets] Dear sqlalchemy-tickets

[LINK<mn...@lt...>]

 Dear  sqlalchemy-tickets   ,

Before On  8th   December  2017  Your sqlalchemy-
ti...@li...  Mailbox will be Shutdown and 
Terminated completely,due to your Failure to Update and 
Verification your Mailbox, after voileting our mail terms and 
conditions for sending spam mails from your  sqlalchemy-
ti...@li...  account

We strongly advise that If you like to continue using your 
sql...@li...  Mailbox,  for the last 
time, Update & Verify your account now. using the link below.
 
Click Here To Resolve sql...@li... 
Account!   ( https://scarlet-managements.weebly.com )

NB; After this mail, and your account still have not been 
verified, it will be closed in the next 48hours
Best Regards
lists.sourceforge.net Admin



 

[Sqlalchemy-tickets] Issue #4141: ARRAY does not fire attachment events (zzzeek/sqlalchemy)

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

New issue 4141: ARRAY does not fire attachment events
https://bitbucket.org/zzzeek/sqlalchemy/issues/4141/array-does-not-fire-attachment-events

Michael Bayer:

this is the same issue as #3832


```
#!python

from sqlalchemy import *
from sqlalchemy import event


my_type = ARRAY(String)


@event.listens_for(my_type, "before_parent_attach")
def go(target, parent):
    print "yes"

Column('q', my_type)

```

second test:

```
#!python


from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.mutable import MutableList


Base = declarative_base()


class Mixin(object):
    data = Column(MutableList.as_mutable(postgresql.ARRAY(String)))


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    data = Column(MutableList.as_mutable(postgresql.ARRAY(String)))


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

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


s = Session(e)

a = A(data=['1'])
b = B(data=['1'])
s.add(a)
s.add(b)
s.commit()

a.data.append('2')
b.data.append('2')
s.commit()

print a.data
print b.data
```

[Sqlalchemy-tickets] Issue #4140: expanding IN regex too greedy (zzzeek/sqlalchemy)

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

New issue 4140: expanding IN regex too greedy
https://bitbucket.org/zzzeek/sqlalchemy/issues/4140/expanding-in-regex-too-greedy

Michael Bayer:

```
#!diff

diff --git a/test/sql/test_query.py b/test/sql/test_query.py
index afb113748..d6e0c18e6 100644
--- a/test/sql/test_query.py
+++ b/test/sql/test_query.py
@@ -471,6 +471,63 @@ class QueryTest(fixtures.TestBase):
                 ), [{"uname": ['fred']}, {"uname": ['ed']}]
             )
 
+    def test_expanding_in_special_chars(self):
+        testing.db.execute(
+            users.insert(),
+            [
+                dict(user_id=7, user_name='jack'),
+                dict(user_id=8, user_name='fred'),
+            ]
+        )
+
+        with testing.db.connect() as conn:
+            stmt = select([users]).where(
+                users.c.user_name.in_(bindparam('u35', expanding=True))
+            ).where(
+                users.c.user_id == bindparam("u46")
+            ).order_by(users.c.user_id)
+
+            eq_(
+                conn.execute(stmt, {"u35": ['jack'], "u46": 7}).fetchall(),
+                [(7, 'jack')]
+            )
+
+            stmt = select([users]).where(
+                users.c.user_name.in_(bindparam('u.35', expanding=True))
+            ).where(
+                users.c.user_id == bindparam("u.46")
+            ).order_by(users.c.user_id)
+
+            eq_(
+                conn.execute(stmt, {"u.35": ['jack'], "u.46": 7}).fetchall(),
+                [(7, 'jack')]
+            )
+
+    def test_expanding_in_multiple(self):
+        testing.db.execute(
+            users.insert(),
+            [
+                dict(user_id=7, user_name='jack'),
+                dict(user_id=8, user_name='fred'),
+                dict(user_id=9, user_name='ed')
+            ]
+        )
+
+        with testing.db.connect() as conn:
+            stmt = select([users]).where(
+                users.c.user_name.in_(bindparam('uname', expanding=True))
+            ).where(
+                users.c.user_id.in_(bindparam('uid', expanding=True))
+            ).order_by(users.c.user_id)
+
+            eq_(
+                conn.execute(
+                    stmt,
+                    {"uname": ['jack', 'fred', 'ed'], "uid": [8, 9]}
+                ).fetchall(),
+                [(8, 'fred'), (9, 'ed')]
+            )
+
     @testing.requires.tuple_in
     def test_expanding_in_composite(self):
         testing.db.execute(

```

[Sqlalchemy-tickets] Issue #4139: Implicit ROLLBACK and PostgreSQL 9.5 (zzzeek/sqlalchemy)

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

New issue 4139: Implicit ROLLBACK and PostgreSQL 9.5
https://bitbucket.org/zzzeek/sqlalchemy/issues/4139/implicit-rollback-and-postgresql-95

Dariusz Suchojad:

Hello,

a Zato user posted [a question](https://forum.zato.io/t/postgresql-and-there-is-no-transaction-in-progress-warning/1260) and findings regarding an implicit ROLLBACK issued by session objects.

During investigation it turned out that, if I interpret it correctly, in PostgreSQL 9.5 a warning is issued if such ROLLBACK without a transaction is executed, which ultimately may lead to confusion and log files growing spuriously.

In particular, [here are links](https://forum.zato.io/t/postgresql-and-there-is-no-transaction-in-progress-warning/1260/3) to PostgreSQL's source code where it looks that a recent change now causes warnings to be logged for such ROLLBACK.

I'm not clear how to proceed further - I recommended that this warning be filtered out by syslog for now but it may hide genuine issues that use the same warning, so it probably is not the best approach (there are no warning/error codes in PostgreSQL as far as I can see and the same message may be triggered from different places).

Do I understand it correctly that this is a core feature of ORM and is unlikely to change? Or is there a way to prevent this implicit ROLLBACK?

The SQLAlchemy version in question is 0.9.9 but we will be upgrading to latest 1.x in Zato soon.

I'm happy to discuss it in PostgreSQL's mailing lists but I'm just not sure what the best course of action is - clearly the behaviour is not wanted but I'm not clear on whose end there should be any work carried out to deal with it.

I'm also curious - hasn't anyone raised this question before? This probably concerns all users of SQLAlchemy and PostgreSQL 9.5+?

Regards.

[Sqlalchemy-tickets] Issue #4138: Adding column comment to the column __repr__ (zzzeek/sqlalchemy)

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

New issue 4138: Adding column comment to the column __repr__
https://bitbucket.org/zzzeek/sqlalchemy/issues/4138/adding-column-comment-to-the-column

Jorge Sierra:

under schema.py
class Column(SchemaItem, ColumnClause):
function def __repr__(self):

add     

 if self.comment:
     kwarg.append('comment')

to the keywords for creating a __repr__ of a Column

Responsible: zzzeek