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

[Sqlalchemy-tickets] Issue #3819: Enum creation doesn't respect metadata schema setting (zzzeek/sqlalchemy)

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

New issue 3819: Enum creation doesn't respect metadata schema setting
https://bitbucket.org/zzzeek/sqlalchemy/issues/3819/enum-creation-doesnt-respect-metadata

tomkcook:

Sample code:


```
#!python

from sqlalchemy import create_engine, Column, Enum, Integer, event
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.schema import DDL

db = declarative_base()
db.metadata.schema = 'test'

engine = create_engine('postgresql://tkcook:tkcook@localhost/tkcook')

import enum

event.listen(db.metadata, 'before_create', DDL('create schema if not exists test'))

class Phase(enum.Enum):
    one = 'one'
    two = 'two'

class Test(db):
    __tablename__ = 'test_t'
    id = Column(Integer, primary_key=True)
    phase = Column(Enum(Phase, name='phase'))

db.metadata.create_all(engine)

t = Test(id = 1, phase = Phase.one)
session = sessionmaker(bind=engine)()
session.add(t)
session.commit()

```

Expected result: Schema `test` contains a table called `test_t` and a type called `phase`.

Actual result: Schema `test` contains a table called `test_t` but the type `phase` is created in schema `public`.

[Sqlalchemy-tickets] Issue #3818: naming convention should not fire off on reflected constraints; Alembic will also need adjustment (zzzeek/sqlalchemy)

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

New issue 3818: naming convention should not fire off on reflected constraints; Alembic will also need adjustment
https://bitbucket.org/zzzeek/sqlalchemy/issues/3818/naming-convention-should-not-fire-off-on

Michael Bayer:

per #3817, alembic has a brittle reliance on naming conventions being applied at reflection time.  We'd like to set up https://gerrit.sqlalchemy.org/#/c/209/ so that this isn't automatic but Alembic will also need to work around this.  

[Sqlalchemy-tickets] Issue #3817: Explicit naming convention error on upgrade to 1.1 on alembic migrate (zzzeek/sqlalchemy)

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

New issue 3817: Explicit naming convention error on upgrade to 1.1 on alembic migrate
https://bitbucket.org/zzzeek/sqlalchemy/issues/3817/explicit-naming-convention-error-on

Dave McDaniel:

After today's update to 1.1 I am now getting a migration error with the following traceback:

```bash
  File "/home/dave/Code/edgar/alembic/versions/c3337510cab5_added_signing_table.py", line 20, in upgrade
    globals()["upgrade_%s" % engine_name]()
  File "/home/dave/Code/edgar/alembic/versions/c3337510cab5_added_signing_table.py", line 51, in upgrade_main_db
    ['signing_id'], ['id'])
  File "/usr/lib/python3.4/contextlib.py", line 66, in __exit__
    next(self.gen)
  File "/usr/lib/python3.4/site-packages/alembic/operations/base.py", line 299, in batch_alter_table
    impl.flush()
  File "/usr/lib/python3.4/site-packages/alembic/operations/batch.py", line 73, in flush
    *self.reflect_args, **self.reflect_kwargs)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 436, in __new__
    metadata._remove_table(name, schema)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value
  File "/usr/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 431, in __new__
    table._init(name, metadata, *args, **kw)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 507, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 519, in _autoload
    self, include_columns, exclude_columns
  File "/usr/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1528, in run_callable
    return callable_(self, *args, **kwargs)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 364, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/engine/reflection.py", line 605, in reflecttable
    exclude_columns, reflection_options)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/engine/reflection.py", line 727, in _reflect_fk
    **reflection_options
  File "/usr/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 436, in __new__
    metadata._remove_table(name, schema)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value
  File "/usr/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 431, in __new__
    table._init(name, metadata, *args, **kw)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 507, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 519, in _autoload
    self, include_columns, exclude_columns
  File "/usr/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1528, in run_callable
    return callable_(self, *args, **kwargs)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 364, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/engine/reflection.py", line 617, in reflecttable
    include_columns, exclude_columns, reflection_options)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/engine/reflection.py", line 833, in _reflect_check_constraints
    sa_schema.CheckConstraint(**const_d))
  File "/usr/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 688, in append_constraint
    constraint._set_parent_with_dispatch(self)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/sql/base.py", line 434, in _set_parent_with_dispatch
    self.dispatch.after_parent_attach(self, parent)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/event/attr.py", line 218, in __call__
    fn(*args, **kw)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/sql/naming.py", line 144, in _constraint_name
    newname = _constraint_name_for_table(const, table)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/sql/naming.py", line 124, in _constraint_name_for_table
    metadata.naming_convention)
  File "/usr/lib/python3.4/site-packages/sqlalchemy/sql/naming.py", line 79, in __getitem__
    return getattr(self, '_key_%s' % key)()
  File "/usr/lib/python3.4/site-packages/sqlalchemy/sql/naming.py", line 43, in _key_constraint_name
    "Naming convention including "
sqlalchemy.exc.InvalidRequestError: Naming convention including %(constraint_name)s token requires that constraint is explicitly named.
```

The odd thing is that the name is explicitly set in the  `create_foreign_key` call (line 51 as identified in the traceback), which is below:

```python
    with op.batch_alter_table('experiment', naming_convention=convention,
                              schema=None) as batch_op:
        batch_op.add_column(sa.Column('signing_id', sa.Integer(), nullable=True))
        batch_op.create_foreign_key(op.f('fk_experiment_signing_id_signing'),
                                    'signing',
                                    ['signing_id'], ['id'])
```

convention is defined as:

```python
convention = {
    "ix": 'ix_%(column_0_label)s',
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s"
}
```

Based on alembic docs, the first postional argument to `create_foreign_key` is the `constraint_name`, so I don't understand the traceback message that it is not being defined, as it is in the call via that first positional argument.

[Sqlalchemy-tickets] Issue #3816: consider rollup of __clause_element__() onto ClauseElement (zzzeek/sqlalchemy)

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

New issue 3816: consider rollup of __clause_element__() onto ClauseElement
https://bitbucket.org/zzzeek/sqlalchemy/issues/3816/consider-rollup-of-__clause_element__-onto

Michael Bayer:

tried to do this for #3802 late in 1.1 but it's too critical if someone is doing a while loop, and here's at least one example:  https://github.com/albertov/py-mailing/blob/a81d71f35b3c04de75cca48eb82b0b85bbaaf915/mailing/models/util.py#L45.  there's no reason that person would have done that besides copying our code.   additionally, if we put `__clause_element__()` onto ClauseElement, there's a lot of internal checks that should also be optimized.   

other more far-out tricks would be:

1. return a copy of the object that raises some assertion on `__clause_element__()`.  though this is expensive

2. deprecate `__clause_element__()` and replace with some totally other method.   But we'd need almost-forever backwards compat for calling upon `__clause_element__` also in this case.  It sort of suggests all the places where we do `hasattr(obj, '__clause_element__')` should become some other kind of single call that perhaps we can put into cutils.   Detecting `__clause_element__` itself would then raise some kind of deprecation warning.

[Sqlalchemy-tickets] Issue #3815: session autoflush is not working (zzzeek/sqlalchemy)

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

New issue 3815: session autoflush is not working
https://bitbucket.org/zzzeek/sqlalchemy/issues/3815/session-autoflush-is-not-working

miso:

I am working with **SQLAlchemy** and **Flask-SQLAlchemy** and when I add an object to the session, the session isn't auto flushed. When I add an object to the session, and `autoflush` is set to `True`, it supposed to send a INSERT operation in the outgoing transaction, right?

After I started getting this problem I made a clean virtual environment with  **`SQLAlchemy (1.0.15)`** and a basic Flask application without **Flask-SQLAlchemy**. I created an engine and a basic session passing `autoflush=True` to `sessionmaker`. When I call **`session.add(instance)`**, again, the session isn't flushed automatically. I am unable to get an `id`. If I echo to the terminal, no database operation seems to happen.

**`session.autoflush`** is returning True. But I have to call **`session.flush()`** manually in order to get an `id` and actually send the INSERT statement.

I am missing something? About how autoflush is supposed to work?

[Sqlalchemy-tickets] Issue #3814: don't rely on odbc SQL_DBMS_VER for SQL server (zzzeek/sqlalchemy)

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

New issue 3814: don't rely on odbc SQL_DBMS_VER for SQL server
https://bitbucket.org/zzzeek/sqlalchemy/issues/3814/dont-rely-on-odbc-sql_dbms_ver-for-sql

Michael Bayer:

I'm on freetds 0.95 and again getting a garbage '95.12.255' value for SQL Server version, no matter what I set TDS_VERSION towards.  Suggest constructing a lookup table based on the values in https://msdn.microsoft.com/en-us/library/ms174396.aspx.

[Sqlalchemy-tickets] Issue #3813: 'on conflict ... do update ...' where clause and implicit returning (zzzeek/sqlalchemy)

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

New issue 3813: 'on conflict ... do update ...' where clause and implicit returning
https://bitbucket.org/zzzeek/sqlalchemy/issues/3813/on-conflict-do-update-where-clause-and

Pawel:

When using Postgres 9.5 INSERT ... ON CONFLICT ... DO UPDATE ... I get an error:

```
#!python
  File ".../sqlalchemy/orm/scoping.py", line 157, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File ".../sqlalchemy/orm/session.py", line 1044, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File ".../sqlalchemy/engine/base.py", line 947, in execute
    return meth(self, multiparams, params)
  File ".../sqlalchemy/sql/elements.py", line 262, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File ".../sqlalchemy/engine/base.py", line 1055, in _execute_clauseelement
    compiled_sql, distilled_params
  File ".../sqlalchemy/engine/base.py", line 1204, in _execute_context
    result = context._setup_crud_result_proxy()
  File ".../sqlalchemy/engine/default.py", line 862, in _setup_crud_result_proxy
    self._setup_ins_pk_from_implicit_returning(row)
  File ".../sqlalchemy/engine/default.py", line 927, in _setup_ins_pk_from_implicit_returning
    for col in table.primary_key
TypeError: 'NoneType' object has no attribute '__getitem__'
```

I have hard time recreating issue with test (sorry I'm a bit new to the project), but i think I know what conditions cause this error.

1. INSERT must give a CONFLICT
2. ON UPDATE must have a WHERE with condition that does not allow for update
3. There should be only one data set for the INSERT VALUES (so that the implicit returning is `True` 

so sth like this should give an error:

```
#!python            
conn.execute(users.insert(), dict(id=1, name='name1'))
i = insert(users)
i = i.on_conflict_do_update(
  index_elements=[users.c.id],
  set_=dict(name=i.excluded.name),
  where=(i.excluded.name == 'other_name''))
)

conn.execute(u, [dict(id=1, name='name2')])
```

This test passes but `_implicit_returning` here is `False`. In my code I use session and `implicit_returning` is `True` (is it because of session?)  

Then in file: `sqlalchemy/engine/default.py:862` function: `_setup_crud_result_proxy`
we go inside both `if`'s and `row = result.fetchone()` sets row to `None` because of the `where` clause I think (nothing will be inserted or updated). Then the call `self._setup_ins_pk_from_implicit_returning(row)` with `row == None` give an error.
 
```
#!python
if self.isinsert:
  if self._is_implicit_returning:  # when implicit_returning is True!
    # row == None
    row = result.fetchone()
    self.returned_defaults = row
    # if row == None we get an error in this method!
    self._setup_ins_pk_from_implicit_returning(row)
    result._soft_close(_autoclose_connection=False)
    result._metadata = None
  elif not self._is_explicit_returning:
    result._soft_close(_autoclose_connection=False)
    result._metadata = None

```

[Sqlalchemy-tickets] Issue #3812: add raise_on_sql feature (zzzeek/sqlalchemy)

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

New issue 3812: add raise_on_sql feature
https://bitbucket.org/zzzeek/sqlalchemy/issues/3812/add-raise_on_sql-feature

Michael Bayer:

not sure if we want this.  raise_on_sql means, lazyload if the load means pulling from the identity map or checking for NULL elements in the join condition, otherwise if we are definitely emitting SQL then raise.   POC at https://gerrit.sqlalchemy.org/#/c/203/.   It also includes some changes to the strategy system that should be a good idea, in that we can pass through the strategy_keys to the strategy object itself and allow them to function as options.

[Sqlalchemy-tickets] Issue #3811: Eager load does not work as expected under special circumstances (zzzeek/sqlalchemy)

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

New issue 3811: Eager load does not work as expected under special circumstances
https://bitbucket.org/zzzeek/sqlalchemy/issues/3811/eager-load-does-not-work-as-expected-under

ddzialak:

My postgresql database keep huge tree thus I have to use yield_per and I want to use eager load to make sure there is no separated query for parent. Unfortunately eager load does not work if parent will be returned in next `yield_per` portion (see attached script).

Attached script is reproducing that bug with `SQLAlchemy==1.1.0b3`
I've used that version as I've though that issue is fixed by: https://bitbucket.org/zzzeek/sqlalchemy/issues/3431/object-eager-loaded-on-scalar-relationship 

Currectly output is:
```
delete from
create_all
SELECT parent_1.id AS parent_1_id, parent_1.name AS parent_1_name, parent_1.parent_id AS parent_1_parent_id, parent.id AS parent_id_1, parent.name AS parent_name, parent.parent_id AS parent_parent_id 
FROM parent LEFT OUTER JOIN parent AS parent_1 ON parent_1.id = parent.parent_id
Parent(11, name) has parent parent 33 ------- ERR: 'Parent.parent' is not available due to lazy='raise'
Parent(22, p2) has parent None ------- WITH: None
Parent(33, p3) has parent parent 22 ------- WITH: Parent(22, p2) has parent None
```

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3810: Engine creation issue when attempting to use an Azure Data Warehouse DB using mssql+pyodbc (zzzeek/sqlalchemy)

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

New issue 3810: Engine creation issue when attempting to use an Azure Data Warehouse DB using mssql+pyodbc
https://bitbucket.org/zzzeek/sqlalchemy/issues/3810/engine-creation-issue-when-attempting-to

Felipe:

Whenever trying to create an engine object using the mssql dialect and pyodbc driver, an error will be raised indicating the database_principal_id() does not exist.

According to Microsoft Documentation, this is true for their data warehouse, but is still supported in normal Azure Databases. (https://msdn.microsoft.com/en-us/library/ms187319.aspx)

With my limited knowledge, I applied a workaround but I am not completely sure the impact it might have in other connections.

Edited the **base.py** module to have the following:

Line 532:

```
#!python

   MS_AZURE_VERSION = (12,)
```


Line 1654:
    
```
#!python

def _get_default_schema_name(self, connection):
        if self.server_version_info < MS_2005_VERSION:
            return self.schema_name
        elif self.server_version_info >= MS_AZURE_VERSION:
            query = sql.text("""
                SELECT user_name()
            """)
        else:
            query = sql.text("""
                SELECT default_schema_name FROM
                sys.database_principals
                WHERE principal_id=database_principal_id()
            """)
        default_schema_name = connection.scalar(query)
        if default_schema_name is not None:
            return util.text_type(default_schema_name)
        else:
            return self.schema_name
```

[Sqlalchemy-tickets] Issue #3809: Unescaped single quote in server_default (for MySQL at least) (zzzeek/sqlalchemy)

[alex-businessoptics <iss...@bi...>]

New issue 3809: Unescaped single quote in server_default (for MySQL at least)
https://bitbucket.org/zzzeek/sqlalchemy/issues/3809/unescaped-single-quote-in-server_default

alex-businessoptics:

The following code:

```
#!python

from sqlalchemy import create_engine, Table, Column, String, MetaData
from sqlalchemy.sql.ddl import CreateTable

engine = create_engine('mysql://businessoptics:bus...@my...v/data')
print CreateTable(Table('mytable',
                        MetaData(),
                        Column('mycolumn',
                               String(length=100),
                               server_default="a'b"))
                  ).compile(engine)
```

produces this invalid output:

```
#!sql

CREATE TABLE mytable (
	mycolumn VARCHAR(100) DEFAULT 'a'b'
)
```

In this case it's a SQL syntax error, but it could also lead to SQL injection.

[Sqlalchemy-tickets] Issue #3808: persistent_to_deleted does not accommodate for garbage collected object (zzzeek/sqlalchemy)

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

New issue 3808: persistent_to_deleted does not accommodate for garbage collected object
https://bitbucket.org/zzzeek/sqlalchemy/issues/3808/persistent_to_deleted-does-not-accommodate

Michael Bayer:


```
#!diff

diff --git a/test/orm/test_events.py b/test/orm/test_events.py
index ab61077..0a11c12 100644
--- a/test/orm/test_events.py
+++ b/test/orm/test_events.py
@@ -1872,6 +1872,31 @@ class SessionLifecycleEventsTest(_RemoveListeners, _fixtures.FixtureTest):
             ]
         )
 
+    def test_persistent_to_deleted_del(self):
+        sess, User, start_events = self._fixture()
+
+        u1 = User(name='u1')
+        sess.add(u1)
+        sess.flush()
+
+        listener = start_events()
+
+        @event.listens_for(sess, "persistent_to_deleted")
+        def persistent_to_deleted(session, instance):
+            assert instance is not None
+
+        sess.delete(u1)
+        del u1
+
+        sess.flush()
+
+        eq_(
+            listener.mock_calls,
+            [
+                call.persistent_to_deleted(sess, None),  # should we send state here?
+            ]
+        )
+
     def test_detached_to_persistent_via_cascaded_delete(self):
         sess, User, Address, start_events = self._fixture(include_address=True)

```

this is critical for 1.1 because the API is broken.  either we don't call the event or we send the state (And should we change the signature so that we know if state is there or not? propose send both object and state).

fails because the object is gone.  the event signature here is broken, we can't always send the object along.  there might be some other events that have this also, the object would have to be persistent first and not be subject to strong_ref.  persistent_to_transient for example seems to have a strong ref.

[Sqlalchemy-tickets] Issue #3807: table qualfification in PG on conflict (zzzeek/sqlalchemy)

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

New issue 3807: table qualfification in PG on conflict
https://bitbucket.org/zzzeek/sqlalchemy/issues/3807/table-qualfification-in-pg-on-conflict

Michael Bayer:


```
#!diff

diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 88110ba..7bc5c5c 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -1258,6 +1258,22 @@ class InsertOnConflictTest(fixtures.TestBase, AssertsCompiledSQL):
                             "WHERE name != %(name_1)s "
                             "AND description != %(description_2)s")
 
+    def test_do_update_add_whereclause_references_excluded(self):
+        i = insert(
+            self.table1, values=dict(name='foo'))
+        i = i.on_conflict_do_update(
+            constraint=self.excl_constr_anon,
+            set_=dict(name=i.excluded.name),
+            where=(
+                (self.table1.c.name != i.excluded.name))
+        )
+        self.assert_compile(i,
+                            'INSERT INTO mytable (name) VALUES '
+                            "(%(name)s) ON CONFLICT (name, description) "
+                            "WHERE mytable.description != %(description_1)s "
+                            'DO UPDATE SET name = excluded.name '
+                            "WHERE mytable.name != excluded.name")
+
     def test_quote_raw_string_col(self):
         t = table('t', column("FancyName"), column("other name"))

```

[Sqlalchemy-tickets] Issue #3806: Postgres code generation is wrong for -> operator (HSTORE column access) pre-psql-9.5 (zzzeek/sqlalchemy)

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

New issue 3806: Postgres code generation is wrong for -> operator (HSTORE column access) pre-psql-9.5
https://bitbucket.org/zzzeek/sqlalchemy/issues/3806/postgres-code-generation-is-wrong-for

lincolnq:

On sqlalchemy 1.0.15, the below test code outputs "(test -> %(test_1)s) IS NOT NULL".

On sqlalchemy 1.1.0b3, it outputs "test -> %(test_1)s IS NOT NULL" -- note the omitted parentheses. 

The latter code is correct in Postgres 9.5 and above where the IS NOT NULL operator has lower precedence than ->, but below 9.5, the IS NOT NULL operator's precedence is higher than the -> operator but sqlalchemy still thinks it is lower.

```
#!python

from sqlalchemy import Column
from sqlalchemy.dialects import postgresql

col = Column(postgresql.HSTORE, name='test', primary_key=True)
print(str((col['key'] != None).compile(dialect=postgresql.dialect())))
```

[Sqlalchemy-tickets] Issue #3805: 1.1.0b3 Core PostgreSQL INSERT CTE rollback bug with engine.execute and default reset_on_return (zzzeek/sqlalchemy)

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

New issue 3805: 1.1.0b3 Core PostgreSQL INSERT CTE rollback bug with engine.execute and default reset_on_return
https://bitbucket.org/zzzeek/sqlalchemy/issues/3805/110b3-core-postgresql-insert-cte-rollback

Christopher Wilson:

I'm using the new insert CTEs to implement a select or insert in one statement using the engine.execute() approach. The default reset_on_return works fine for separate select() followed by an insert() as the insert transaction is COMMITted. However, using an insert CTE, the transaction is ROLLBACKed whenever the connection is returned to the pool.


MWE: using PostgreSQL 9.3.


```
#!python

import sqlalchemy as sa

metadata = sa.MetaData()

exTable = sa.Table('the_table_name',metadata,
        sa.Column('id', sa.Integer, primary_key = True),
        sa.Column('text', sa.TEXT, unique=True),
        )

theText = "some text here"
returningCols = [exTable.c.id]

engine = sa.create_engine(connStr)
lookup = exTable.select().with_only_columns(returningCols).where(exTable.c.text==theText)
lookupCTE = lookup.cte('selq')
insertCTE = exTable.insert().returning(*returningCols).from_select([exTable.c.text],sa.select([sa.literal(theText)]).where(~sa.exists(sa.select([sa.text('*')]).select_from(lookupCTE)))).cte('insq')

selParams = [sa.text('*')]
lookupSel = sa.select(selParams).select_from(lookupCTE)
lookupIns = sa.select(selParams).select_from(insertCTE)
query = lookupSel.union_all(lookupIns)
idres = engine.execute(query)
theId = None
for item in idres:
  print item
  theId = item['id']

idLookup = engine.execute(lookup)
print ("Should find ID: %d..." % (theId))
for item in idLookup:
  print "item found: %d" % item['id']
```
prints:
(1,)
Should find ID: 1...


Setting pool_reset_on_return = 'commit':
(2,)
Should find ID: 2...
item found: 2

This leads to confusing behavior: 
* if the data is present, the correct ID is returned, 
* if the data is not present, then the data is inserted, the corresponding ID returned, and then that ID is invalid as the transaction is rolled back at the end of the execute() call. 

Yes, I can arbitrarily use the pool_reset_on_return='commit' option for the engine, but the expected behavior would be a COMMIT transaction on the use of INSERT, UPDATE, or DELETE CTEs, since that is the behavior of the separate INSERT command with the default *pool_reset_on_return* behavior. 

[Sqlalchemy-tickets] Issue #3804: "Invalid utf8 character string" warning on insert into binary-type columns with MySQL 5.6 (zzzeek/sqlalchemy)

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

New issue 3804: "Invalid utf8 character string" warning on insert into binary-type columns with MySQL 5.6
https://bitbucket.org/zzzeek/sqlalchemy/issues/3804/invalid-utf8-character-string-warning-on

Andrew Mason:

I have encountered this issue trying to insert a binary UUID (i.e. uuid.uuid4().bytes) into both varbinary(16). I have also encountered this issue trying to insert binary data into a mediumblob column, so I suspect the issue is affecting all binary-type columns.

Note that MySQL 5.5 happily executes the insert, but MySQL 5.6 (and likely later versions) spew warnings.

This django user has submitted an issue against MySQL [here](https://bugs.mysql.com/bug.php?id=79317). The purported solution is instead of doing
```
#!sql

    INSERT INTO `user` (uuid)
    VALUES (%s)
```

to do
```
#!sql
    INSERT INTO `user` (uuid)
    VALUES (_binary %s)
```

which will cause MySQL to treat the data as binary rather than utf8-encoded data.

The problem here is that MySQL is trying to validate the value being populated as utf8, and if it's not valid utf8, is throwing the warning. One might argue that this doesn't need to be done, since it's populating a binary column type, but since we're passing a string (and not casting it as a _binary), MySQL stands by it's warning.

I am using the python mysqldb for my DBAPI, SQLAlchemy 1.0.12, and (attempting to, at least, as noted above) MySQL 5.6.

Attached is a traceback where the issue occurs.

[Sqlalchemy-tickets] Issue #3803: DBAPI connections go invalid on KeyboardInterrupt, GreenletExit e.g BaseException but not Exception, simple invalidate() fixes. (zzzeek/sqlalchemy)

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

New issue 3803: DBAPI connections go invalid on KeyboardInterrupt, GreenletExit e.g BaseException but not Exception, simple invalidate() fixes.
https://bitbucket.org/zzzeek/sqlalchemy/issues/3803/dbapi-connections-go-invalid-on

Michael Bayer:

case test3.py and test5.py illustrate pymysql connections being corrupted both by GreenletExit and KeyboardInterrupt.  The program loop has many issues with "commands out of sync" for various reasons, but at the very least because when the error occurs, our context managers as well as pool logic wants to do a rollback(), such as Transaction.__exit__, which is already called for all BaseException subclasses, not just Exception, and this blows up because the connection is already corrupted; this occurs even if we use NullPool because we are still in the handling of the context.

Similar tests against pymysql alone (test2.py, test4.py) illustrate that largely the same kind of connection corruption can be shown against C-based mysqlclient with the KeyboardInterrupt example, so this is not just a pure Python greenlet thing.

_handle_dbapi_error() already handles Exception, adding support for BaseException and special "connection only, don't invalidate the pool" logic as in attached patch allow test3 / test5 to be run with no errors at all except normal interception of the KeyboardInterrupt / GreenletExit.

[Sqlalchemy-tickets] Issue #3802: SQL expression in ORM update only look for ClauseElement, not __clause_element__() (zzzeek/sqlalchemy)

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

New issue 3802: SQL expression in ORM update only look for ClauseElement, not __clause_element__()
https://bitbucket.org/zzzeek/sqlalchemy/issues/3802/sql-expression-in-orm-update-only-look-for

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'
    id = Column(Integer, primary_key=True)
    not_pk = Column(Integer)

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

a1 = A(not_pk=1)
s = Session(e)

s.add(a1)
s.commit()

# works
a1.not_pk = A.not_pk.__clause_element__()
s.commit()

# fails
a1.not_pk = A.not_pk
s.commit()
```

[Sqlalchemy-tickets] Issue #3801: can't use SQL expression for PK col (zzzeek/sqlalchemy)

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

New issue 3801: can't use SQL expression for PK col
https://bitbucket.org/zzzeek/sqlalchemy/issues/3801/cant-use-sql-expression-for-pk-col

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'
    id = Column(Integer, primary_key=True)
    not_pk = Column(Integer)

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

a1 = A(not_pk=1)
s = Session(e)

s.add(a1)
s.commit()

# works
a1.not_pk = A.not_pk + 1
s.commit()

# fails
a1.id = A.id + 1
s.commit()
```

[Sqlalchemy-tickets] Issue #3800: NoSuchColumnError when using a sql statement as the polymorphic_on and loading a relationship via joinedload (zzzeek/sqlalchemy)

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

New issue 3800: NoSuchColumnError when using a sql statement as the polymorphic_on and loading a relationship via joinedload
https://bitbucket.org/zzzeek/sqlalchemy/issues/3800/nosuchcolumnerror-when-using-a-sql

sjhewitt:

Test Case
```
#!python

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

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'

    id = Column(types.Integer, primary_key=True)
    discriminator = Column(types.String(50), nullable=False)
    child_id = Column(types.Integer)
    child = relationship('A', primaryjoin='foreign(A.child_id) == remote(A.id)')

    __mapper_args__ = {
        'polymorphic_identity': 'a',
        "polymorphic_on": case([
            (discriminator == "a", "a"),
        ], else_="b"),
    }


class B(A):
    __mapper_args__ = {
        'polymorphic_identity': 'b'
    }


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

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

session.add_all([
    A(id=1, discriminator='a'),
    A(id=2, discriminator='b', child_id=1),
    A(id=3, discriminator='c', child_id=1),
])
session.commit()


session.query(A).options(joinedload('child')).all()
```

Error:
```
2016-09-19 15:03:31,801 INFO sqlalchemy.engine.base.Engine SELECT a.id AS a_id, a.discriminator AS a_discriminator, a.child_id AS a_child_id, CASE WHEN (a.discriminator = ?) THEN ? ELSE ? END AS _sa_polymorphic_on, a_1.id AS a_1_id, a_1.discriminator AS a_1_discriminator, a_1.child_id AS a_1_child_id, CASE WHEN (a_1.discriminator = ?) THEN ? ELSE ? END AS anon_1
FROM a LEFT OUTER JOIN a AS a_1 ON a.child_id = a_1.id
2016-09-19 15:03:31,801 INFO sqlalchemy.engine.base.Engine ('a', 'a', 'b', 'a', 'a', 'b')
2016-09-19 15:03:31,801 DEBUG sqlalchemy.engine.base.Engine Col ('a_id', 'a_discriminator', 'a_child_id', '_sa_polymorphic_on', 'a_1_id', 'a_1_discriminator', 'a_1_child_id', 'anon_1')
2016-09-19 15:03:31,802 DEBUG sqlalchemy.engine.base.Engine Row (1, u'a', None, u'a', None, None, None, u'b')
2016-09-19 15:03:31,802 DEBUG sqlalchemy.engine.base.Engine Row (2, u'b', 1, u'b', 1, u'a', None, u'a')
2016-09-19 15:03:31,802 DEBUG sqlalchemy.engine.base.Engine Row (3, u'c', 1, u'b', 1, u'a', None, u'a')
Traceback (most recent call last):
  File "sqla_test.py", line 45, in <module>
    session.query(A).options(joinedload('child')).all()
  File "env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2613, in all
    return list(self)
  File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 86, in instances
    util.raise_from_cause(err)
  File "env/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 71, in instances
    rows = [proc(row) for row in fetch]
  File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 597, in polymorphic_instance
    return instance_fn(row)
  File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 428, in _instance
    loaded_instance, populate_existing, populators)
  File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 497, in _populate_full
    populator(state, dict_, row)
  File "env/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 1586, in load_scalar_from_joined_new_row
    dict_[key] = _instance(row)
  File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 592, in polymorphic_instance
    discriminator = row[polymorphic_on]
  File "env/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 408, in _key_fallback
    expression._string_or_unprintable(key))
sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'CASE WHEN (a_1.discriminator = :param_1) THEN :param_2 ELSE :param_3 END'"
```

It seems that the `_sa_polymorphic_on` column for the joined relationship gets named `anon1` and then cannot be found in the row

[Sqlalchemy-tickets] Issue #3799: equal and not-equal operators should not be associative (zzzeek/sqlalchemy)

[John P. <iss...@bi...>]

New issue 3799: equal and not-equal operators should not be associative
https://bitbucket.org/zzzeek/sqlalchemy/issues/3799/equal-and-not-equal-operators-should-not

John Passaro:

Example
```
#!python

from sqlalchemy import literal
eq_stmt = (literal(1) == 1) == (literal(1) == 0)
ne_stmt = (literal(1) != 1) != (literal(1) != 0)
print eq_stmt; print ne_stmt
```
output:
```
:param_1 = :param_2 = :param_3 = :param_4
:param_1 != :param_2 != :param_3 != :param_4
```
Expect:
```
(:param_1 = :param_2) = (:param_3 = :param_4)
(:param_1 != :param_2) != (:param_3 != :param_4)
```

See PR (including test case) at https://gerrit.sqlalchemy.org/#/c/190/, and discussion on original PR at https://github.com/zzzeek/sqlalchemy/pull/308.

Not high priority as far as the reporter is concerned, it is easy to work around.

[Sqlalchemy-tickets] Issue #3798: _add_bind() not covered / doesn't import exception (zzzeek/sqlalchemy)

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

New issue 3798: _add_bind() not covered / doesn't import exception
https://bitbucket.org/zzzeek/sqlalchemy/issues/3798/_add_bind-not-covered-doesnt-import

Michael Bayer:


```
#!python

from sqlalchemy.orm import Session

s = Session()

s.bind_mapper("boom", "Foo")


python test.py
Traceback (most recent call last):
  File "test.py", line 5, in <module>
    s.bind_mapper("boom", "Foo")
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 1150, in bind_mapper
    self._add_bind(mapper, bind)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 1125, in _add_bind
    raise exc.ArgumentError(
AttributeError: 'module' object has no attribute 'ArgumentError'

```

[Sqlalchemy-tickets] Issue #3797: Plain Column on Single Table Subclass Pollutes `foreign_keys` when Multi Table Ancestor Present (zzzeek/sqlalchemy)

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

New issue 3797: Plain Column on Single Table Subclass Pollutes `foreign_keys` when Multi Table Ancestor Present
https://bitbucket.org/zzzeek/sqlalchemy/issues/3797/plain-column-on-single-table-subclass

Daniel Rocco:

Hi all. Thanks so much for SQLAlchemy, which is a joy to use and a solid foundation for our software.

Using declarative in a mixed single and multi table inheritance scenario can lead to the following error:


```
#!python

Traceback (most recent call last):
  File "sqla_declarative_mixed_multi_single_simpler.py", line 126, in <module>
    print session.query(BaseUser).count()
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1260, in query
    return self._query_cls(entities, self, **kwargs)
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 110, in __init__
    self._set_entities(entities)
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 120, in _set_entities
    self._set_entity_selectables(self._entities)
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 150, in _set_entity_selectables
    ent.setup_entity(*d[entity])
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3446, in setup_entity
    self._with_polymorphic = ext_info.with_polymorphic_mappers
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 754, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1891, in _with_polymorphic_mappers
    configure_mappers()
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 2768, in configure_mappers
    mapper._post_configure_properties()
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1708, in _post_configure_properties
    prop.init()
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py", line 183, in init
    self.do_init()
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1629, in do_init
    self._setup_join_conditions()
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1704, in _setup_join_conditions
    can_be_synced_fn=self._columns_are_mapped
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1972, in __init__
    self._determine_joins()
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 2055, in _determine_joins
    consider_as_foreign_keys=consider_as_foreign_keys
  File "<string>", line 2, in join_condition
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 849, in _join_condition
    a, a_subset, b, consider_as_foreign_keys)
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 882, in _joincond_scan_left_right
    key=lambda fk: fk.parent._creation_order):
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 882, in <lambda>
    key=lambda fk: fk.parent._creation_order):
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 738, in __getattr__
    key)
AttributeError: Neither 'Column' object nor 'Comparator' object has an attribute 'parent'

```

The example hierarchy exhibiting this behavior consists of 4 model classes:

* `User` extends `BaseUser` (multi table)
* A `User` has a collection of one or more `Thing` objects
* `SubUser` extends `User` (single table), adding a single column


```
#!python

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


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

Base = declarative_base()


class BaseUser(Base):
    __tablename__ = 'root'

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

    __mapper_args__ = {
        'polymorphic_on': row_type,
        'polymorphic_identity': 'baseuser'
    }


class User(BaseUser):
    __tablename__ = 'user'

    __mapper_args__ = {
        'polymorphic_identity': 'user'
    }

    baseuser_id = Column(Integer, ForeignKey('root.id'), primary_key=True)


class Thing(Base):
    __tablename__ = 'thing'

    id = Column(Integer, primary_key=True)

    owner_id = Column(Integer, ForeignKey('user.baseuser_id'))
    owner = relationship('User', backref='things')


class SubUser(User):
    __mapper_args__ = {
        'polymorphic_identity': 'subuser'
    }

    sub_user_custom_thing = Column(Integer)


Base.metadata.create_all(engine)

print session.query(BaseUser).count()

```


>From the traceback we see that the failure originates with this loop expression (`selectable.py` line 880):

    for fk in sorted(
            b.foreign_keys,
            key=lambda fk: fk.parent._creation_order):

Digging further, `b` turns out to be the `child_selectable` used when setting
up the join condition for `Thing.owner` (`relationships.py` line 1690);
`b.foreign_keys` contains:

    set([ForeignKey('root.id'), Column('sub_user_custom_thing', Integer(), table=<user>)])

So the plain integer column `sub_user_custom_thing` has somehow ended up in the
`User` table's set of foreign keys, which leads to the symptom above.

There are several ways to work around this issue:

* specify a `primaryjoin` on the relationship `Thing.owner`, which bypasses the
  automatic construction of the join condition

* convert `SubUser` to multi table inheritance

* redefine the `Thing.owner` relationship target to `BaseUser`

* trigger class instrumentation _after_ the definition of `User` and `Thing`
  but _before_ the definition of `SubUser`. This allows the construction of the
  `Thing.owner` relationship to succeed by forcing it to happen before `User`'s
  list of foreign keys gets corrupted.

Tested with SQLAlchemy 1.0.15, 1.1.0b3, and a master checkout on 2016-09-14.


```
#!

$ lsb_release -a ; echo ; python --version ; echo ; pip list
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.1 LTS
Release:        16.04
Codename:       xenial

Python 2.7.8

pip (8.1.2)
setuptools (27.2.0)
SQLAlchemy (1.0.15)
wheel (0.30.0a0)

```

[Sqlalchemy-tickets] Issue #3796: deprecate and remove old "scope=None" logic for scoped_session() (zzzeek/sqlalchemy)

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

New issue 3796: deprecate and remove old "scope=None" logic for scoped_session()
https://bitbucket.org/zzzeek/sqlalchemy/issues/3796/deprecate-and-remove-old-scope-none-logic

Michael Bayer:

some old cruft that should be removed in https://github.com/zzzeek/sqlalchemy/pull/307/files within scoped_session call(), source is very old commit b8b51fe4379936fe142c875ea0f17da14a12c27d.  nevertheless, this is non-underscored so someone might be using it.

[Sqlalchemy-tickets] Issue #3795: This Session's transaction has been rolled back by a nested rollback() call. (zzzeek/sqlalchemy)

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

New issue 3795: This Session's transaction has been rolled back by a nested rollback() call.
https://bitbucket.org/zzzeek/sqlalchemy/issues/3795/this-sessions-transaction-has-been-rolled

masell2:

Hello.
I have a error regarding subtransactions used in the flush method.
given the example code i get a stack trace.
The subtransaction in example code would be equivalent to the handling in sqlalchemy.orm.session.Session._flush in case of a exception.


```
#!python


#!/usr/bin/env python
# -*- coding: utf8 -*-

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session

MYSQL_DSN = 'mysql+pymysql://root:root@127.0.0.1/test?charset=utf8'
db_engine = create_engine(
    MYSQL_DSN,
    encoding='utf-8',
    pool_size=10,
    max_overflow=50,
    pool_recycle=600,
    pool_reset_on_return='rollback'
)
session = scoped_session(sessionmaker(db_engine))()

# Begin a subtransaction, like in _flush: https://bitbucket.org/zzzeek/sqlalchemy/src/c3d582d87b5bd404d380c5707f35b7a20c9828fc/lib/sqlalchemy/orm/session.py?at=master&fileviewer=file-view-default#session.py-2142
subtrans = session.begin(subtransactions=True)
# Rollback the transaction, like in _flush in case of exception. https://bitbucket.org/zzzeek/sqlalchemy/src/c3d582d87b5bd404d380c5707f35b7a20c9828fc/lib/sqlalchemy/orm/session.py?at=master&fileviewer=file-view-default#session.py-2183
subtrans.rollback()

# session is unusable unless i do another rollback call manually.
with session.begin_nested():
    pass


```

```python
Traceback (most recent call last):
  File "trans.py", line 24, in <module>
    with session.begin_nested():
  File "/vagrant/sqltest/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 745, in begin_nested
    return self.begin(nested=True)
  File "/vagrant/sqltest/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 725, in begin
    nested=nested)
  File "/vagrant/sqltest/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 235, in _begin
    self._assert_active()
  File "/vagrant/sqltest/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 218, in _assert_active
    "This Session's transaction has been rolled back "
sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back by a nested rollback() call.  To begin a new transaction, issue Session.rollback() first.
```