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

[Sqlalchemy-tickets] Issue #3994: Azure SQL Datawarehouse error (zzzeek/sqlalchemy)

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

New issue 3994: Azure SQL Datawarehouse error
https://bitbucket.org/zzzeek/sqlalchemy/issues/3994/azure-sql-datawarehouse-error

James Bashforth:

I am receiving the following error when connecting to Azure SQL DW.  On further investigation it seems the the management view below has a slightly different name in Azure SQL DW. "sys.dm_pdw_exec_sessions" not sure if this is the cause of the issue, but if I run the following query on the SQL DW it returns results

SELECT login_name ,COUNT(session_id) AS session_count   
FROM sys.dm_pdw_exec_sessions   
GROUP BY login_name;  

Error Message
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Catalog view 'dm_exec_sessions' is not supported in this version. (104385) (SQLExecDirectW)")

[Sqlalchemy-tickets] Issue #3993: Session.begin() returns a context manager (zzzeek/sqlalchemy)

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

New issue 3993: Session.begin() returns a context manager
https://bitbucket.org/zzzeek/sqlalchemy/issues/3993/sessionbegin-returns-a-context-manager

Felix Mühlbauer:

In the example code of [autocommit](http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html?highlight=begin#autocommit-mode) the return value of Session.begin() is used as a context manager. This is not described in the docs for [Session.begin()](http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.begin)

[Sqlalchemy-tickets] Issue #3992: synchronize_session='fetch' should, you know, 'fetch' the actual data (zzzeek/sqlalchemy)

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

New issue 3992: synchronize_session='fetch' should, you know, 'fetch' the actual data
https://bitbucket.org/zzzeek/sqlalchemy/issues/3992/synchronize_session-fetch-should-you-know

Michael Bayer:

synchronize_session='fetch' currently runs the SELECT and then expires the objects.   If we're running a SELECT, we should be fetching the actual keys that changed and populating, not expiring.    perhaps we'd call this "eager_fetch" in case we're worried about side effects, but I think just switching "fetch" is fine, the term "fetch" implies something's being fetched.

[Sqlalchemy-tickets] Issue #3991: session.refresh() / load_on_ident() still hardwired to "deprecated" with_lockmode() (zzzeek/sqlalchemy)

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

New issue 3991: session.refresh() / load_on_ident() still hardwired to "deprecated" with_lockmode()
https://bitbucket.org/zzzeek/sqlalchemy/issues/3991/sessionrefresh-load_on_ident-still

Michael Bayer:

we deprecated with_lockmode() in 0.9 but session.refresh() still codes to it.   linking refresh() to with_for_update() might be awkward due to the flags, probably have a dictionary passed in using kw argument with_for_update.   

however, awkward to say `session.refresh(obj, with_for_update={})` and that means FOR UPDATE since empty dict implies boolean false.

[Sqlalchemy-tickets] Issue #3990: strange circular dependency error with only one object being changed (zzzeek/sqlalchemy)

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

New issue 3990: strange circular dependency error with only one object being changed
https://bitbucket.org/zzzeek/sqlalchemy/issues/3990/strange-circular-dependency-error-with

Adrian:

I have the following code: https://gist.github.com/ThiefMaster/9cea43683142912e5ffb0fa3b5d8d0a9

It fails with a circular dependency error even though there is only a change to `p` (however, both `p` and `gal` show up in
`sess.dirty` when I print it right before the commit, but `is_modified` only returns True for `p`).

It also works fine if I uncomment the expire call - but of course that's an awful workaround. `post_update=True` on the `gallery`
relationship also does the job but it seems like an SQLAlchemy bug to me since there's only one object with changes which can be flushed/committed just fine if I do
a manual expire...

[Sqlalchemy-tickets] Issue #3989: MetaData.naming_convention support for composite indexes/foreign keys (zzzeek/sqlalchemy)

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

New issue 3989: MetaData.naming_convention support for composite indexes/foreign keys
https://bitbucket.org/zzzeek/sqlalchemy/issues/3989/metadatanaming_convention-support-for

whereswalden:

Currently, [MetaData.naming_convention](http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.MetaData.params.naming_convention) does not support composite indexes or foreign keys, so those must be named explicitly. It'd be nice to be able to provide a template for those to be automatically named as well.

It would likely be necessary for users to define a function to provide the name, since the constraint/index could include any number of columns, but a fairly simple example could be provided in the documentation that simply `','.join()`s the column names.

[Sqlalchemy-tickets] Issue #3988: incorrect join with orm.with_polymorphic() and lazy='joined' properties (zzzeek/sqlalchemy)

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

New issue 3988: incorrect join with orm.with_polymorphic() and lazy='joined' properties
https://bitbucket.org/zzzeek/sqlalchemy/issues/3988/incorrect-join-with-ormwith_polymorphic

Julien Cigar:

Please see the test case, the error is pretty clear I think :)

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3987: Select duplicates itself when using column() on its own column (zzzeek/sqlalchemy)

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

New issue 3987: Select duplicates itself when using column() on its own column
https://bitbucket.org/zzzeek/sqlalchemy/issues/3987/select-duplicates-itself-when-using-column

dataradiant:

I suspect `Select.column()` is intended to be passed columns from a `table` rather than itself, but if that's the case a note in the documentation would helpful.  My actual usage was a lot more complicated than the example and this took a while to figure out.


```
#!python

from sqlalchemy import select, table, column, func

t1 = table("t1", column("c1"))
s1 = select([t1])

s2 = s1.column(func.min(s1.c.c1))

#  >>> str(s2)
#  'SELECT t1.c1, min(c1) AS min_1 \nFROM t1, (SELECT t1.c1 AS c1 \nFROM t1)'

s3 = s1.column(func.min(t1.c.c1))

#  Note that using the column from the table rather than the Select works fine
#  >>> str(s3)
#  'SELECT t1.c1, min(t1.c1) AS min_1 \nFROM t1'
```

[Sqlalchemy-tickets] Issue #3986: cascading delete-orphan relationships disrupted by joined table inheritance (zzzeek/sqlalchemy)

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

New issue 3986: cascading delete-orphan relationships disrupted by joined table inheritance
https://bitbucket.org/zzzeek/sqlalchemy/issues/3986/cascading-delete-orphan-relationships

Theron Luhn:

This is difficult to explain, so let me just show you:

```
#!python

from sqlalchemy import String, Integer, Column, create_engine, ForeignKey
from sqlalchemy.orm import relationship, Session
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', cascade='all,delete-orphan')


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', cascade='all,delete-orphan')

    __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')],
    )],
)
db.add(company)
db.flush()
company.employees = []
db.flush()
```

What I expect to happen is the second flush would perform roughly the following SQL:

* `delete language where programmer_id=1`
* `delete programmer where id = 1`
* `delete employee where id = 1`

Instead, I get the following error:

```
sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) update or delete on table "programmer" violates foreign key constraint "language_programmer_id_fkey" on table "language"
DETAIL:  Key (id)=(1) is still referenced from table "language".
 [SQL: 'DELETE FROM programmer WHERE programmer.id = %(id)s'] [parameters: {'id': 1}]
 ```

I've played around with various configurations, and it seems to be a very specific case that fails:

* Removing `delete-orphan` from `Programmer.languages` and making `language.id` nullable works fine.
* Performing `db.delete(programmer)` instead of `company.employees = []` works fine.

[Sqlalchemy-tickets] Issue #3985: Cross platform Merge statement (zzzeek/sqlalchemy)

[Михаил Д. <iss...@bi...>]

New issue 3985: Cross platform Merge statement
https://bitbucket.org/zzzeek/sqlalchemy/issues/3985/cross-platform-merge-statement

Михаил Доронин:

I would like to provide cross-platform Merge statement. I've this this [issue](https://bitbucket.org/zzzeek/sqlalchemy/issues/960/implement-merge-and-or-pg-on-conflict-and)

I think there should be both dialect specific variants as well as the lowest common denominator between them. The reasons are

* It's in sql 2003 standard and many databases support actual standard (okay, maybe they not so popular for sqlalchemy users)
* It is now possible to implement lowest common denominator implementation in mysql, sqlite  (maybe not that performant, but who uses sqlite for performance? And user can be warned about that in docs) and postgresql.
* I believe that lowest common denominator is the actual most useful (and used) use case. Which is "insert values or update existing ones". Kind of like `{**d1, **d2}` in python3.

```
/* postgresql */
INSERT INTO table (a, b)
    VALUES (1, 2), (2, 3)
    ON CONFLICT (a) DO UPDATE SET dname = EXCLUDED.dname;

/* mysql */
INSERT INTO table (a, b)
    VALUES (1, 2), (2, 3)
    ON DUPLICATE KEY a=VALUES(a) UPDATE

/* sqlite */
INSERT OR REPLACE INTO tabel (a, b) 
    VALUES
    (1, COALESCE((SELECT role FROM Employee WHERE id = 1), 2)),
    (2, COALESCE((SELECT role FROM Employee WHERE id = 2), 3))
```
possible sqlalchemy interface `insert(table, [(1, 2), (2, 3)]).merge(table.c.a)`

I would be very happy to provide implementation if you can provide guidance on how to do that. I'm trying to understand your code, but aside from understanding that there is `Insert` class in `sql/dlm.py` and there are probably implementations of it in all dialects I haven't been able to figure out much.

Maybe you can show how to make that for postgres since it already has `on_conflict` in dialect. And then I can use that as example to add this to sqlite, sqlalchemy and maybe other dialects.

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3984: deferred column_property gets set to "expired" state on flush, unconditionally loads on unexpire (zzzeek/sqlalchemy)

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

New issue 3984: deferred column_property gets set to "expired" state on flush, unconditionally loads on unexpire
https://bitbucket.org/zzzeek/sqlalchemy/issues/3984/deferred-column_property-gets-set-to

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)
    a = Column(Integer)
    b = Column(Integer)

    aplusb = column_property(
        a + b, deferred=True
    )

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

s = Session(e)
s.add(A(a=1, b=2))
s.commit()

a1 = s.query(A).first()
assert 'aplusb' not in a1.__dict__
a1.b = 5
s.flush()
s.commit()
a1.b
assert 'aplusb' not in a1.__dict__



```

[Sqlalchemy-tickets] Issue #3983: Pass non dialect keywords into custom compile. (zzzeek/sqlalchemy)

[Михаил Д. <iss...@bi...>]

New issue 3983: Pass non dialect keywords into custom compile.
https://bitbucket.org/zzzeek/sqlalchemy/issues/3983/pass-non-dialect-keywords-into-custom

Михаил Доронин:

I want to create a merge statement that would work both in mysql and sqlite. For that I need to be able pass keys for `on duplicate key`, but it seems that you've forbidden to pass any non dialect keywords to statements.

To be able to use sqlite in tests and mysql in production is the goal here, so polluting the code with conditionals to check what db I'm using and passing `mysql_keys` isn't acceptable.

[Sqlalchemy-tickets] Issue #3982: Create and check schema on ORM (zzzeek/sqlalchemy)

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

New issue 3982: Create and check schema on ORM
https://bitbucket.org/zzzeek/sqlalchemy/issues/3982/create-and-check-schema-on-orm

Dmitry Tyutryumov:

Hi guys, i need you help.

How i can create a schema on ORM, for few tables, 

* MetaData doesn't work when schema doesn't exist. 

* event.listen(DeclBase.metadata, 'before_create', CreateSchema('schema')) i see an error if schema is exist, but i don't know how to check schema on ORM


Will be appreciate for help

[Sqlalchemy-tickets] Issue #3981: When using with_variant() using custom type containing bind_expression and col_expression, bind_expression and col_expression are never called (zzzeek/sqlalchemy)

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

New issue 3981: When using with_variant() using custom type containing bind_expression and col_expression, bind_expression and col_expression are never called
https://bitbucket.org/zzzeek/sqlalchemy/issues/3981/when-using-with_variant-using-custom-type

Martin Webb:

When using `with_variant()` to specify a custom column (decorated type) for the dialect variant, implementations of `bind_expression` and `col_expression` are never called.

Attached file `test.py` contains a minimal example showing this in action.

Expected behaviour is that the INSERT query fails as it uses functions unavailable in SQLite, however `bind_expression` and `col_expression` are never called to make the SQL-level changes to apply these functions.

Obviously I'm not expecting the INSERT queries to actually work in SQLite because those functions don't exist, but I've written the example this way to demonstrate that the SQL-level overrides are never getting called.

The real world use case for this is that I'm trying to have a variant type apply for MySQL that uses `COMPRESS()` and `UNCOMPRESS()` while for local testing with SQLite the data is stored unchanged.

SQLAlchemy version: 1.1.9

Reproduced in:
- Python 2.7.10 on Mac OS X 10.11.6
- Python 2.7.3 on Debian Wheezy

[Sqlalchemy-tickets] Issue #3980: TypeError: issubclass() arg 1 must be a class (zzzeek/sqlalchemy)

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

New issue 3980: TypeError: issubclass() arg 1 must be a class
https://bitbucket.org/zzzeek/sqlalchemy/issues/3980/typeerror-issubclass-arg-1-must-be-a-class

Anthony Oduor:

In implementing a multi-tenant system using PostreSQL schemas... When A new customer signs-up I create a new schema when they activate their account. Here is the code for the function that creates the schema and even put's in a dummy record.


```
#!python
settings = request.registry.settings
engine = get_engine(settings)

Session = sessionmaker(engine, expire_on_commit=False)
Session.configure(bind=engine)
session = Session()

session.begin(subtransactions=True)
try:
    engine.execute(CreateSchema(tenant_id))

    metadata = MetaData(schema=tenant_id)
    declared_base = declarative_base(bind=engine, name='NewTenantBase', metadata=metadata)

    NewTenantBase = automap_base(declarative_base=declared_base)
    NewTenantBase.metadata.schema = tenant_id
    # NewTenantBase.metadata.reflect(bind=engine)

    NewTenantBase.prepare(engine, reflect=False)
    configure_mappers()

    class MyBooks(BooksMixin, NewTenantBase):
        __tablename__ = 'mybooks'

    # mapped classes are ready
    MyBooks = NewTenantBase.classes.mybooks

    NewTenantBase.metadata.create_all()

    try:
        # set the search path
        session.execute("SET search_path TO %s" % tenant_id)

        session.add(MyBooks(name='Fiction', description='You know what fiction are'))
        session.flush()
        session.execute("commit")
    except Exception as e:
        traceback.print_exc()
        session.rollback()

    session.commit()
except Exception as e:
    traceback.print_exc()
    session.rollback()
```

This using Pyramid-Websauna framework. The first signup goes well and everything is setup properly in the new schema without an error. The problem is subsequent signups don't go as well. I get the error below which happens after the new schema has been created. 

```
#!python

TypeError('issubclass() arg 1 must be a class',)
Traceback (most recent call last):
  File "/opt/mapylons/myproject/myapp/myapp/tasks.py", line 80, in create_schema
    NewTenantBase.prepare(engine, reflect=False)
  File "/opt/mapylons/myproject/venv3.5/lib/python3.5/site-packages/sqlalchemy/ext/automap.py", line 787, in prepare
    generate_relationship)
  File "/opt/mapylons/myproject/venv3.5/lib/python3.5/site-packages/sqlalchemy/ext/automap.py", line 894, in _relationships_for_fks
    local_cls, referred_cls):
TypeError: issubclass() arg 1 must be a class
```

The interesting bit is that is when I restart the server between each signup, they will all go well and the customer schema setup will go on without a hitch. How can I make this multi-tenancy work properly without literally restarting `pserve` after each customer signup? ...which would be quite a hack job

[Sqlalchemy-tickets] Issue #3979: Sequence doesn't inherit schema from metadata (zzzeek/sqlalchemy)

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

New issue 3979: Sequence doesn't inherit schema from metadata
https://bitbucket.org/zzzeek/sqlalchemy/issues/3979/sequence-doesnt-inherit-schema-from

Alistair Watson:

The code below emits:

```
#!python

CREATE SEQUENCE test_sequence
```
when according to the documentation it should get it's default schema value from the metadata and it should emit:

```
#!python

CREATE SEQUENCE test_schema.test_sequence
```

It's not really an issue if you are connecting as the user/schema you want to own the sequence - unfortunately I need to create all tables and database objects as a different user. Providing the schema parameter to each sequence directly does solve the issue.


```
#!python

from sqlalchemy import create_engine, MetaData, Column, Integer,Sequence
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('oracle+cx_oracle://test_schema:Welcome1@localhost:1521/xe', echo=True)
metadata = MetaData(schema='test_schema')
Base = declarative_base(metadata=metadata)

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, Sequence(name="test_sequence"), primary_key=True)
    data = Column(Integer)
    
Base.metadata.drop_all(engine, checkfirst = True)    
Base.metadata.create_all(engine)
```

[Sqlalchemy-tickets] Issue #3978: inspect relationships fails on many to many tables with schema set (zzzeek/sqlalchemy)

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

New issue 3978: inspect relationships fails on many to many tables with schema set
https://bitbucket.org/zzzeek/sqlalchemy/issues/3978/inspect-relationships-fails-on-many-to

Alistair Watson:

The following code fails to print the relationships when I set the schema argument of Metadata and when inspecting a table with a many to many relationship. If the schema argument is removed or the tables have a different relationship such as one to many then the relationship attribute works. I am using the latest version of SQLAlchemy (1.1.9) and Python 3.4.5. I also tried it using an old version of SQLAlchemy (0.9.10) and the problem exists there too.

```
#!python

from sqlalchemy import MetaData, inspect, Column, ForeignKey, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

metadata = MetaData(schema='test_schema')
Base = declarative_base(metadata=metadata)

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

class Secondary(Base):
    __tablename__ = 'secondary'
    fk_parent = Column(Integer, ForeignKey('parent.id'), primary_key=True)
    fk_child = Column(Integer, ForeignKey('child.id'), primary_key=True)
    
class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent = relationship("Parent",back_populates="children",secondary="secondary")
    
insp = inspect(Child)

for a in insp.relationships:
    print(a)
    

```

and the stack trace


```
#!python

Traceback (most recent call last):
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/ext/declarative/clsregistry.py", line 281, in __call__
    x = eval(self.arg, globals(), self._dict)
  File "<string>", line 1, in <module>
NameError: name 'secondary' is not defined

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "schema_test.py", line 25, in <module>
    for a in insp.relationships:
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 764, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/orm/mapper.py", line 2265, in relationships
    return self._filter_properties(properties.RelationshipProperty)
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/orm/mapper.py", line 2282, in _filter_properties
    configure_mappers()
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/orm/mapper.py", line 2866, in configure_mappers
    mapper._post_configure_properties()
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/orm/mapper.py", line 1765, in _post_configure_properties
    prop.init()
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/orm/interfaces.py", line 184, in init
    self.do_init()
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/orm/relationships.py", line 1652, in do_init
    self._process_dependent_arguments()
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/orm/relationships.py", line 1677, in _process_dependent_arguments
    setattr(self, attr, attr_value())
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/ext/declarative/clsregistry.py", line 292, in __call__
    (self.prop.parent, self.arg, n.args[0], self.cls)
sqlalchemy.exc.InvalidRequestError: When initializing mapper Mapper|Child|child, expression 'secondary' failed to locate a name ("name 'secondary' is not defined"). If this is a class name, consider adding this relationship() to the <class '__main__.Child'> class after both dependent classes have been defined.
```

[Sqlalchemy-tickets] Issue #3977: Using "configure" on scoped_session does not bind engine properly (zzzeek/sqlalchemy)

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

New issue 3977: Using "configure" on scoped_session does not bind engine properly
https://bitbucket.org/zzzeek/sqlalchemy/issues/3977/using-configure-on-scoped_session-does-not

Adam Olsen:

Using SQLAlchemy 1.1.9 with Python 2.7.13 on OS X

Using `scoped_session.configure` to change the engine doesn't bind the engine properly.  For instance, if you do something like this:

```
#!python

session = orm.scoped_session(orm.sessionmaker())
engine = create_engine('sqlite://')
session.configure(bind=engine)
session.close_all()

engine = create_engine('postgresql://someuser:somepass@localhost:5432/db',
    json_serializer=serialize, json_deserializer=deserialize)
session.configure(bind=engine)

print session.bind  # will output `Engine(sqlite://)`
```

It results in errors such as the one you see below (if you start with sqlite, but then switch to postgres and try to query tables).

If you're wondering WHY we would want to do this, it is for testing.  Our test suite tries to make sure that everything works ok with both sqlite and postgresql.  We can't just create a new instance of the scoped_session, as it will break the factories and various other places that call for a session at load time.

```
#!python

Traceback (most recent call last):
  File "/vagrant/apps/skedup/tests/base.py", line 28, in setUp
    db.session.commit()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 157, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 874, in commit
    self.transaction.commit()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 459, in commit
    self._assert_active(prepared_ok=True)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 276, in _assert_active
    % self._rollback_exception
InvalidRequestError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (exceptions.AttributeError) 'SQLiteDialect_pysqlite' object has no attribute '_json_serializer'
```

Doing something like this totally fixes it (manually assigning the engine to `session.bind`), but it seems like it shouldn't be required:

```
#!python

session = orm.scoped_session(orm.sessionmaker())
engine = create_engine('sqlite://')
session.configure(bind=engine)
session.close_all()

engine = create_engine('postgresql://someuser:somepass@localhost:5432/db',
    json_serializer=serialize, json_deserializer=deserialize)
session.configure(bind=engine)
session.bind = engine  # <---- this is the key to making it work

print session.bind  # will output `Engine(postgresql://someuser:somepass@localhost:5432/db)`
```

[Sqlalchemy-tickets] Issue #3976: Built-in way to convert an ORM object (i.e. result of an ORM query) to a Python dictionary (zzzeek/sqlalchemy)

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

New issue 3976: Built-in way to convert an ORM object (i.e. result of an ORM query) to a Python dictionary
https://bitbucket.org/zzzeek/sqlalchemy/issues/3976/built-in-way-to-convert-an-orm-object-ie

Markus Meskanen:

I suggest adding something that allows us to convert ORM objects directly into dictionaries like this:
```
#!python
class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(Text, required=True)
    birth_year = Column(Integer)

user = session.query(User).filter(User.birth_year==1996).one()
return dict(user)
```
This would not only allow extremely easy integration with JSON APIs, but sometimes it's better to handle these objects as dicts for speed etc.

Some things to consider:

- Recursion; what to do with relationships? Should one-to-many convert to a dict of lists? What if all of those many have more "sub" lists?
- Method of converting:
  1. Support `dict(obj)`, integrates neatly into Python and this way it supports more than just `dict()`, like custom mapping classes
  2. Add a `to_dict()` method to the declarative base class. I like this the *least*, because an object shouldn't know how to convert itself into everything in the world
  3. A custom function like `sqlalchemy.orm.to_dict(obj)`. Imo better than #2, and it doesn't clutter the class either.
- Ignore fields, i.e. "convert this object to a dict, but ignore `id` column"

[Sqlalchemy-tickets] Issue #3975: cx_oracle fails because of "b" in version string (zzzeek/sqlalchemy)

[zak k <iss...@bi...>]

New issue 3975: cx_oracle fails because of "b" in version string
https://bitbucket.org/zzzeek/sqlalchemy/issues/3975/cx_oracle-fails-because-of-b-in-version

zak k:

6.0b1 causes a ValueError near line 706 in
sqlalchemy\dialects\oracle\cx_oracle.py

        if hasattr(self.dbapi, 'version'):
            verlist = list()
            for version_part in self.dbapi.version.split('.'):
                version_part = version_part.split('b')[0]
                verlist.append(int(version_part))
            self.cx_oracle_ver = tuple(verlist)
        else:
            self.cx_oracle_ver = (0, 0, 0)

was my fix for this but you may have a better way

[Sqlalchemy-tickets] Issue #3974: Inconsistent session state after raising exception in after_commit event (zzzeek/sqlalchemy)

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

New issue 3974: Inconsistent session state after raising exception in after_commit event
https://bitbucket.org/zzzeek/sqlalchemy/issues/3974/inconsistent-session-state-after-raising

Oleg Lomaka:

If you raise an exception in after_commit event handler, then session.is_active is False, but if you try to call session.begin(), you will get an error: "This session is in 'committed' state; no further SQL can be emitted within this transaction."

session.begin() will work only after manually calling session.close(). I am not sure is it ok to call close in this case. And not sure you consider this as actually a bug, but apparently the session is in some inconsistent state after raising exception in after_commit event handler.

```
#!python

@event.listens_for(session, 'after_commit')
def handler(session):
    raise Exception

try:
    with session.begin():
        pass
except Exception as e:
    pass

session.begin() # raises "This session is in 'committed' state; no further SQL can be emitted within this transaction."
```

[Sqlalchemy-tickets] Issue #3973: xml datatype not supported for MS SQL Server (zzzeek/sqlalchemy)

[David J. <iss...@bi...>]

New issue 3973: xml datatype not supported for MS SQL Server
https://bitbucket.org/zzzeek/sqlalchemy/issues/3973/xml-datatype-not-supported-for-ms-sql

David Johansen:

This error is output:
```
/usr/src/app/connectrunner/venv/lib/python3.6/site-packages/sqlalchemy/dialects/mssql/base.py:1926: SAWarning: Did not recognize type 'xml' of column 'WatchXMLData'
(type, name))
Can't generate DDL for NullType(); did you forget to specify a type on this Column?
```

It appears that it should be mapped to `LONGVARCHAR` based on the documentation:
https://docs.microsoft.com/en-us/sql/t-sql/xml/xml-transact-sql
https://documentation.progress.com/output/DataDirect/DataDirectCloud/index.html#page/queries/microsoft-sql-server-data-types.html

[Sqlalchemy-tickets] Issue #3972: SystemError: <built-in function utf_8_decode> returned a result with an error set (zzzeek/sqlalchemy)

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

New issue 3972: SystemError: <built-in function utf_8_decode> returned a result with an error set
https://bitbucket.org/zzzeek/sqlalchemy/issues/3972/systemerror-returned-a-result-with-an

Alan Hamlett:

Getting this error when a `billiard.exceptions.SoftTimeLimitExceeded` exception occurs during a select query:

```
billiard.exceptions.SoftTimeLimitExceeded:
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "/app/myapp/amqp.py", line 161, in decorated
    return fn(*args, **kwargs)
  File "/app/myapp/tasks.py", line 1399, in cache_user_summary
    dt=current,
  File "/app/myapp/utils.py", line 983, in get_summary
    use_cache=use_cache)
  File "/app/myapp/utils.py", line 601, in get_combined_durations
    durations = get_durations(user=user, start=start, end=end, use_cache=use_cache)
  File "/app/myapp/utils.py", line 713, in get_durations
    heartbeats.extend(query.all())
  File "/app/venv/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2703, in all
    return list(self)
  File "/app/venv/lib/python3.5/site-packages/sqlalchemy/orm/loading.py", line 90, in instances
    util.raise_from_cause(err)
  File "/app/venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/app/venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/app/venv/lib/python3.5/site-packages/sqlalchemy/orm/loading.py", line 67, in instances
    fetch = cursor.fetchmany(query._yield_per)
  File "/app/venv/lib/python3.5/site-packages/sqlalchemy/engine/result.py", line 1154, in fetchmany
    self.cursor, self.context)
  File "/app/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1405, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/app/venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/app/venv/lib/python3.5/site-packages/sqlalchemy/engine/result.py", line 1147, in fetchmany
    l = self.process_rows(self._fetchmany_impl(size))
  File "/app/venv/lib/python3.5/site-packages/sqlalchemy/engine/result.py", line 1306, in _fetchmany_impl
    row = self._fetchone_impl()
  File "/app/venv/lib/python3.5/site-packages/sqlalchemy/engine/result.py", line 1296, in _fetchone_impl
    self.__buffer_rows()
  File "/app/venv/lib/python3.5/site-packages/sqlalchemy/engine/result.py", line 1283, in __buffer_rows
    self.__rowbuffer = collections.deque(self.cursor.fetchmany(size))
  File "/app/venv/lib/python3.5/encodings/utf_8.py", line 16, in decode
    return codecs.utf_8_decode(input, errors, True)
SystemError: <built-in function utf_8_decode> returned a result with an error set
```

Using Python 3.5.2, SQLAlchemy 1.1.9 and psycopg2 2.7.1.

[Sqlalchemy-tickets] Issue #3971: Unicode Comparison Warning (zzzeek/sqlalchemy)

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

New issue 3971: Unicode Comparison Warning
https://bitbucket.org/zzzeek/sqlalchemy/issues/3971/unicode-comparison-warning

Lukas Siemon:

Getting a warning
```shell
.../env/local/lib/python2.7/site-packages/sqlalchemy/sql/type_api.py:359: 
UnicodeWarning: Unicode equal comparison failed to convert 
both arguments to Unicode - interpreting them as being unequal
  return x == y

```
Unfortunately we treat warnings as exceptions, so I'm kind of stuck here. This seems like a problem with SQLAlchemy? Any advice what best to do here?

Test Case:
```python

# coding=utf-8
import unittest
from sqlalchemy import Column, String, text, inspect
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.ext.declarative import declarative_base
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = (
    'postgres://postgres:password@localhost:5432/tmp')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)

Base = declarative_base()


class Test(db.Model):
    __tablename__ = 'test'
    id = Column(UUID, primary_key=True, nullable=False,
                server_default=text('uuid_generate_v4()'))
    name = Column(String, nullable=False)

db.create_all()


class TestClass(unittest.TestCase):

    def test_function(self):
        test = Test(name="♥ This")
        db.session.add(test)
        db.session.commit()
        db.session.expire_all()
        test = Test.query.filter_by(name="♥ This").first()
        test.name = "Still ♥ This!"
        inspected = inspect(test)
        assert getattr(inspected.attrs, 'name').history.has_changes()

```

[Sqlalchemy-tickets] Issue #3970: Index postgresql_ops ignored when func.lower(column) (zzzeek/sqlalchemy)

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

New issue 3970: Index postgresql_ops ignored when func.lower(column)
https://bitbucket.org/zzzeek/sqlalchemy/issues/3970/index-postgresql_ops-ignored-when

ponomar:

Here is code:

```
#!python

from sqlalchemy import Table, Column, Index, String, MetaData, func, create_engine

md = MetaData()
tbl = Table('t1', md, Column('col1', String()))
idx = Index('idx1', func.lower(tbl.c.col1), postgresql_ops={'col1': 'text_pattern_ops'})

e = create_engine("postgresql://postgres:postgres@localhost/test", echo=True)
with e.connect() as c:
    md.create_all(c)

```

Outputs

     Column |       Type        | Modifiers 
    --------+-------------------+-----------
     col1   | character varying | 
    
    Indexes:
        "idx1" btree (lower(col1::text))  # NO text_pattern_ops!!!

Responsible: zzzeek