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

[Sqlalchemy-tickets] Issue #3205: Support Table Reflection For Materialized Views (zzzeek/sqlalchemy)

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

New issue 3205: Support Table Reflection For Materialized Views
https://bitbucket.org/zzzeek/sqlalchemy/issue/3205/support-table-reflection-for-materialized

cieplak:

Table reflection works in Postgres for regular views, but when reflecting materialized views, a sqlalchemy.exc.NoSuchTableError exception is raised.

```
from sqlalchemy import Table
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Executable, ClauseElement


class CreateView(Executable, ClauseElement):
    def __init__(self, name, select):
        self.name = name
        self.select = select


@compiles(CreateView)
def visit_create_view(element, compiler, **kw):
    return "CREATE MATERIALIZED VIEW %s AS %s" % (
         element.name,
         compiler.process(element.select, literal_binds=True)
         )


from sqlalchemy import MetaData, Column, Integer
from sqlalchemy.engine import create_engine


uri = 'postgresql://postgres@localhost/postgres'
engine = create_engine(uri)
metadata = MetaData(engine)
t = Table('t',
          metadata,
          Column('id', Integer, primary_key=True),
          Column('number', Integer))
t.create()
engine.execute(t.insert().values(id=1, number=3))
engine.execute(t.insert().values(id=9, number=-3))

createview = CreateView('viewname', t.select().where(t.c.id>5))
engine.execute(createview)

v = Table('viewname', metadata, autoload=True)

for r in engine.execute(v.select()):
    print r
```

[Sqlalchemy-tickets] Issue #3204: temporary table reflection (zzzeek/sqlalchemy)

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

New issue 3204: temporary table reflection
https://bitbucket.org/zzzeek/sqlalchemy/issue/3204/temporary-table-reflection

Mike Bayer:

all the way back in 5320a47a, SQLite gained the ability to reflect temp tables.  But it seems like at least PG and MySQL don't at all.  this is totally inconsistent.

[Sqlalchemy-tickets] Issue #3203: sqlite get_unique_constraints() does not work on temporary tables (zzzeek/sqlalchemy)

[Johannes E. <iss...@bi...>]

New issue 3203: sqlite get_unique_constraints() does not work on temporary tables
https://bitbucket.org/zzzeek/sqlalchemy/issue/3203/sqlite-get_unique_constraints-does-not

Johannes Erdfelt:

When attempting to use get_unique_constraints() on a temporary table in sqlite, it will fail with an exception similar to this:

Traceback (most recent call last):
  File "test_sqlite_temporary.py", line 28, in <module>
    print insp.get_unique_constraints('test_temp_reflection')
  File "/home/johannes/openstack/sqlalchemy/lib/sqlalchemy/engine/reflection.py", line 410, in get_unique_constraints
    self.bind, table_name, schema, info_cache=self.info_cache, **kw)
  File "<string>", line 2, in get_unique_constraints
  File "/home/johannes/openstack/sqlalchemy/lib/sqlalchemy/engine/reflection.py", line 54, in cache
    ret = fn(self, con, *args, **kw)
  File "/home/johannes/openstack/sqlalchemy/lib/sqlalchemy/dialects/sqlite/base.py", line 1109, in get_unique_constraints
    table_data = c.fetchone()[0]

The attached script shows that a non-temporary table works fine, but a temporary table fails.

The bug is caused by the fact the sqlite implementation of get_unique_constraints() uses sqlite_master only, whereas code like get_indexes() does a union between sqlite_master and sqlite_temp_master. sqlite_temp_master being the version of sqlite_master that contains information on temporary tables.

[Sqlalchemy-tickets] Issue #3202: UnboundExecutionError for joint table subclasses when binds is constructed from list of tables (zzzeek/sqlalchemy)

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

New issue 3202: UnboundExecutionError for joint table subclasses when binds is constructed from list of tables
https://bitbucket.org/zzzeek/sqlalchemy/issue/3202/unboundexecutionerror-for-joint-table

ods:

The list of tables is the only list of mapped data that automatically collected in `MetaData` object. So it's the simplest way to construct session with multiple connections where separate connection is used for each metadata. `Session.get_bind()` does look up based on `mapper.mapped_table`. This works for simple mapped classes, but doesn't work for classes mapped to join like subclasses with joined table inheritance.

Test case:

```python
from sqlalchemy import Column, Integer, ForeignKey, create_engine, orm
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = 'A'
    id = Column(Integer, primary_key=True)
    type = Column(Integer, nullable=False)
    __mapper_args__ = {'polymorphic_on': type}

class B(A):
    __tablename__ = 'B'
    id = Column(ForeignKey(A.id), primary_key=True)
    __mapper_args__ = {'polymorphic_identity': 1}

engine = create_engine('sqlite://')
binds = {table: engine for table in Base.metadata.sorted_tables}
db = orm.sessionmaker(binds=binds)()

print db.get_bind(A) # Engine(sqlite://)
print db.get_bind(B) # sqlalchemy.exc.UnboundExecutionError: Could not locate a bind configured on mapper Mapper|B|B or this Session
```

Should we iterate through tables used in `Join` object `mapper.mapped_table` refers to?

[Sqlalchemy-tickets] Issue #3201: named=True raises "unexpected argument" for Engine events (zzzeek/sqlalchemy)

[Elmer de L. <iss...@bi...>]

New issue 3201: named=True raises "unexpected argument" for Engine events
https://bitbucket.org/zzzeek/sqlalchemy/issue/3201/named-true-raises-unexpected-argument-for

Elmer de Looff:

The [events documentation](http://docs.sqlalchemy.org/en/rel_0_9/core/event.html#named-argument-styles) notes that since 0.9, the listens_for event decorator accepts a keyword `named`. With this, event arguments are provided as a dict. This works fine for the example of the Pool event, but fails for Engine events (for the current 0.9.7 release).

A testcase to reproduce this and traceback are included as attachments. The relevant code is also included below:

```
#!python
from sqlalchemy.event import listens_for
from sqlalchemy.engine import Engine

@listens_for(Engine, 'before_execute', named=True)
def intercept_execute(conn, **kw):
    print conn.engine.url
```

[Sqlalchemy-tickets] Issue #3200: check for autocommit=True as create_engine() options, what's going on here exactly? (zzzeek/sqlalchemy)

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

New issue 3200: check for autocommit=True as create_engine() options, what's going on here exactly?
https://bitbucket.org/zzzeek/sqlalchemy/issue/3200/check-for-autocommit-true-as-create_engine

Mike Bayer:

need to understand why using execution_options() method works and the built in dict does not


```
#!python

from sqlalchemy.engine import create_engine

e = create_engine("mysql://scott:tiger@localhost/test", execution_options={"autocommit": True})

c = e.connect()

```



```
#!

 File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/strategies.py", line 165, in first_connect
    dialect.initialize(c)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/dialects/mysql/base.py", line 2431, in initialize
    self._detect_ansiquotes(connection)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/dialects/mysql/base.py", line 2697, in _detect_ansiquotes
    connection.execute("SHOW VARIABLES LIKE 'sql_mode'"),
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 721, in execute
    return self._execute_text(object, multiparams, params)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 870, in _execute_text
    statement, parameters
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 997, in _execute_context
    self._commit_impl(autocommit=True)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 530, in _commit_impl
    self.connection._reset_agent is self.__transaction:
AttributeError: 'Connection' object has no attribute '_reset_agent'

```

[Sqlalchemy-tickets] Issue #3199: assert dispatch_reg[owner_ref] == listen_ref (zzzeek/sqlalchemy)

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

New issue 3199: assert dispatch_reg[owner_ref] == listen_ref
https://bitbucket.org/zzzeek/sqlalchemy/issue/3199/assert-dispatch_reg-owner_ref-listen_ref

Leonardo Rossi:

I'm using Invenio 2.0 and try to replace old version of SQLAlchemy 0.8.7 with the last 0.9.7.
The utility to automaticaly create the db works (inveniomanage database recreate --yes-i-know).

But when I start tests with: python setup.py test

It return me a error:

    test_fisrt_blueprint (invenio.testsuite.test_ext_template.TemplateLoaderCase) ... --------------------------------------------------------------------------------
    ERROR in wrappers [/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/ext/logging/wrappers.py:310]:
    
    --------------------------------------------------------------------------------
    Traceback (most recent call last):
      File "/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/ext/legacy/__init__.py", line 124, in __call__
        response = self.app.full_dispatch_request()
      File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/flask/app.py", line 1470, in full_dispatch_request
        self.try_trigger_before_first_request_functions()
      File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/flask/app.py", line 1497, in try_trigger_before_first_request_functions
        func()
      File "/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/modules/messages/views.py", line 264, in invoke_email_alert_register
        email_alert_register()
      File "/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/modules/messages/models.py", line 202, in email_alert_register
        event.listen(MsgMESSAGE, 'after_insert', email_alert)
      File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/api.py", line 63, in listen
        _event_key(target, identifier, fn).listen(*args, **kw)
      File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/registry.py", line 187, in listen
        self.dispatch_target.dispatch._listen(self, *args, **kw)
      File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/orm/events.py", line 547, in _listen
        event_key.base_listen(**kw)
      File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/registry.py", line 226, in base_listen
        for_modify(target.dispatch).append(self, propagate)
      File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/attr.py", line 328, in append
        event_key.append_to_list(self, self.listeners)
      File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/registry.py", line 237, in append_to_list
        _stored_in_collection(self, owner)
      File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/registry.py", line 74, in _stored_in_collection
        assert dispatch_reg[owner_ref] == listen_ref
    AssertionError

In /home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/modules/messages/views.py (row 264)

    

```
#!python

# Registration of email_alert invoked from blueprint
# in order to use before_app_first_request.
# Reading config CFG_WEBMESSAGE_EMAIL_ALERT
# required app context.
@blueprint.before_app_first_request
def invoke_email_alert_register():
    email_alert_register()
```


In /home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/modules/messages/models.py (row 202)


```
#!python

# Registration of email_alert invoked from blueprint
# in order to use before_app_first_request.
# Reading config CFG_WEBMESSAGE_EMAIL_ALERT
# required app context.
def email_alert_register():
    if cfg['CFG_WEBMESSAGE_EMAIL_ALERT']:
        from sqlalchemy import event
        # Register after insert callback.
        event.listen(MsgMESSAGE, 'after_insert', email_alert)
```


Someone can help me? :)
(Sorry, this is my first issues and I don't know how to set "kind", etc... T_T)

Installed:

*     -e git+https://github.com/mitsuhiko/flask-sqlalchemy@c7eccba63314f3ea77e2c6217d3d3c8b0d2552fd#egg=Flask_SQLAlchemy-2.0
*     MySQL-python==1.2.5
*     SQLAlchemy==0.9.7
*     SQLAlchemy-Utils==0.23.5

[Sqlalchemy-tickets] Issue #3198: SyntaxError in test suite with Python 3.2 (zzzeek/sqlalchemy)

[Arfrever F. T. A. <iss...@bi...>]

New issue 3198: SyntaxError in test suite with Python 3.2
https://bitbucket.org/zzzeek/sqlalchemy/issue/3198/syntaxerror-in-test-suite-with-python-32

Arfrever Frehtes Taifersar Arahesis:

This problem was introduced in SQLAlchemy 0.9.7.

```
$ python3.2 sqla_nose.py
...
======================================================================
ERROR: Failure: SyntaxError (invalid syntax (test_metadata.py, line 2708))
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib64/python3.2/site-packages/nose/case.py", line 133, in run
    self.beforeTest(result)
  File "/usr/lib64/python3.2/site-packages/nose/case.py", line 75, in beforeTest
    beforeTest(self.test)
  File "/usr/lib64/python3.2/site-packages/nose/proxy.py", line 117, in beforeTest
    self.plugins.beforeTest(self.test)
  File "/usr/lib64/python3.2/site-packages/nose/plugins/manager.py", line 99, in __call__
    return self.call(*arg, **kw)
  File "/usr/lib64/python3.2/site-packages/nose/plugins/manager.py", line 167, in simple
    result = meth(*arg, **kw)
  File "lib/sqlalchemy/testing/plugin/noseplugin.py", line 86, in beforeTest
    test.test.cls.__module__,
AttributeError: 'Failure' object has no attribute 'cls'

----------------------------------------------------------------------
Ran 5933 tests in 270.246s

FAILED (SKIP=225, errors=1)
```

I attach patch.
Tests with this patch still pass with Python 2.6 and 2.7.

[Sqlalchemy-tickets] Issue #3197: "named" is not accepted for attribute events (zzzeek/sqlalchemy)

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

New issue 3197: "named" is not accepted for attribute events
https://bitbucket.org/zzzeek/sqlalchemy/issue/3197/named-is-not-accepted-for-attribute-events

Mike Bayer:


```
#!diff

diff --git a/lib/sqlalchemy/orm/events.py b/lib/sqlalchemy/orm/events.py
index c50a7b0..bd0b48f 100644
--- a/lib/sqlalchemy/orm/events.py
+++ b/lib/sqlalchemy/orm/events.py
@@ -1490,7 +1490,7 @@ class AttributeEvents(event.Events):
     @classmethod
     def _listen(cls, event_key, active_history=False,
                 raw=False, retval=False,
-                propagate=False):
+                propagate=False, **kw):
 
         target, identifier, fn = \
             event_key.dispatch_target, event_key.identifier, event_key.fn
@@ -1509,14 +1509,14 @@ class AttributeEvents(event.Events):
                     return fn(target, value, *arg)
             event_key = event_key.with_wrapper(wrap)
 
-        event_key.base_listen(propagate=propagate)
+        event_key.base_listen(propagate=propagate, **kw)
 
         if propagate:
             manager = instrumentation.manager_of_class(target.class_)
 
             for mgr in manager.subclass_managers(True):
                 event_key.with_dispatch_target(
-                    mgr[target.key]).base_listen(propagate=True)
+                    mgr[target.key]).base_listen(propagate=True, **kw)
 
     def append(self, target, value, initiator):
         """Receive a collection append event.
```


```
#!python


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

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    bs = relationship("B")


class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    a_id = Column(Integer, ForeignKey('a.id'))

e = create_engine("sqlite://", echo=True)

sess = Session(e)


@event.listens_for(A.bs, 'set', named=True)
def update_challenge_points(target, **kw):
    pass
```

[Sqlalchemy-tickets] Issue #3196: coverage is busted again, port the upcoming alembic approach over (zzzeek/sqlalchemy)

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

New issue 3196: coverage is busted again, port the upcoming alembic approach over
https://bitbucket.org/zzzeek/sqlalchemy/issue/3196/coverage-is-busted-again-port-the-upcoming

Mike Bayer:

at some point, when building the pytest suite the behavior that coverage started up before anything else went away.   It seems that conftest importing sqlalchemy/testing is all that needs to happen to ruin it as apparently conftest is pulled in before coverage starts.  I seriously thought I tested the crap out of this when i first did pytest but apparently I'm wrong.

upcoming in alembic is an old-is-new-again "bootstrap" solution, hacky like the old one but generalized a little more nicely to nose and pytest.

[Sqlalchemy-tickets] Issue #3195: CAST explodes with recursion limit (zzzeek/sqlalchemy)

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

New issue 3195: CAST explodes with recursion limit
https://bitbucket.org/zzzeek/sqlalchemy/issue/3195/cast-explodes-with-recursion-limit

squeaky:

You are probably meant to always pass one of the classes from sqlalchemy as type parameter. Anyway if you do what I did it it shouldn't explode with recursion limit I guess.

```
>>> import sqlalchemy
>>> sqlalchemy.__version__
'0.9.7'
>>> sqlalchemy.cast(1, 'text')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/data/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 542, in __repr__
    friendly = getattr(self, 'description', None)
  File "/data/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 655, in __getattr__
    return getattr(self.comparator, key)
  File "/data/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 655, in __getattr__
    return getattr(self.comparator, key)
  File "/data/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 655, in __getattr__
    return getattr(self.comparator, key)
  File "/data/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 655, in __getattr__
    return getattr(self.comparator, key)
  File "/data/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 655, in __getattr__
    return getattr(self.comparator, key)
  File "/data/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 655, in __getattr__
    return getattr(self.comparator, key)
  File "/data/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 655, in __getattr__
    return getattr(self.comparator, key)
  File "/data/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 655, in __getattr__
    return getattr(self.comparator, key)
  File "/data/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 655, in __getattr__
    return getattr(self.comparator, key)
  File "/data/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 655, in __getattr__
...
File "/data/venv/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 725, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
RuntimeError: maximum recursion depth exceeded
```

[Sqlalchemy-tickets] Issue #3194: misleading warning when "non column elements" in selfref but we have remote side (zzzeek/sqlalchemy)

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

New issue 3194: misleading warning when "non column elements" in selfref but we have remote side
https://bitbucket.org/zzzeek/sqlalchemy/issue/3194/misleading-warning-when-non-column

Mike Bayer:

this warns "SAWarning: Non-simple column elements in primary join condition for property Foo.us - consider using remote() annotations to mark the remote side.".   we need to qualify the warning for remote_side actually present.

```
#!python

from sqlalchemy import Table, Column, Integer, ForeignKeyConstraint, \
    MetaData, and_, func
from sqlalchemy.orm import mapper, relationship, configure_mappers

m = MetaData()


composite_selfref = Table(
    'composite_selfref', m,
    Column('id', Integer, primary_key=True),
    Column('group_id', Integer, primary_key=True),
    Column('parent_id', Integer),
    ForeignKeyConstraint(
        ['parent_id', 'group_id'],
        ['composite_selfref.id', 'composite_selfref.group_id']
    )
)


class Foo(object):
    pass


mapper(Foo, composite_selfref, properties={
    "us": relationship(
        Foo,
        primaryjoin=and_(
            composite_selfref.c.group_id ==
            func.foo(composite_selfref.c.group_id),
            composite_selfref.c.parent_id ==
            composite_selfref.c.id
        ),
        remote_side=set([composite_selfref.c.parent_id]),
    )
})


configure_mappers()

```

[Sqlalchemy-tickets] Issue #3193: CompoundSelect not supporting CTEs with inserts (zzzeek/sqlalchemy)

[Konsta V. <iss...@bi...>]

New issue 3193: CompoundSelect not supporting CTEs with inserts
https://bitbucket.org/zzzeek/sqlalchemy/issue/3193/compoundselect-not-supporting-ctes-with

Konsta Vesterinen:

I'm trying to do atomic SELECT or INSERT as explained here: http://stackoverflow.com/questions/6722344/select-or-insert-a-row-in-one-command/6722460#6722460

I'v been able to construct the query, however when I try to execute this query it gives me: 

AttributeError: 'CompoundSelect' object has no attribute '_returning'

Here is a minimal test case for reproducing the issue:
```
#!python

import sqlalchemy as sa
from sqlalchemy import create_engine
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import CTE, exists

Model = declarative_base()


dns = 'postgres://postgres@localhost/my_test_db'

engine = create_engine(dns)
# engine.echo = True
connection = engine.connect()


class MyModel(Model):
    __tablename__ = 'my_model'

    id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
    key = sa.Column(sa.Unicode(255), nullable=False)


sa.orm.configure_mappers()
Model.metadata.create_all(connection)

table = MyModel.__table__
value = 'some_key'

select = (
    sa.select(['*'], from_obj=table)
    .where(table.c.key == value)
)
insert = CTE(
    table.insert()
    .from_select(
        [table.c.key],
        sa.select([sa.text(value)])
        .where(~ exists(select))
    )
    .returning(sa.text('*')),
    name='new_row'
)
query = sa.select(['*'], from_obj=insert).union(select)
print connection.execute(query).fetchall()
```

[Sqlalchemy-tickets] Issue #3192: Support MultiRow Inserts (zzzeek/sqlalchemy)

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

New issue 3192: Support MultiRow Inserts
https://bitbucket.org/zzzeek/sqlalchemy/issue/3192/support-multirow-inserts

cieplak:

As far as I can tell, SQLAlchemy Core does not render multirow insert statements such as the following, even when the underlying database supports multirow inserts:
```
#!sql
INSERT INTO mytable (col1, col2, col3) VALUES
    (1, 2, "abc"),
    (2, 4, "xyz"),
    (3, 5, "aaa"),
    (4, 7, "bbb");

```
Postgres has had multiple row inserts since 8.2 and sqlite since  3.7.11.

```
#!python
from sqlalchemy import Column, Integer, MetaData, Table, Unicode, create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

engine = create_engine('sqlite://')

session = scoped_session(sessionmaker(bind=engine))

metadata = MetaData()

table = Table(
    'table', metadata,
    Column('id', Integer, primary_key=True),
    Column('value', Unicode),
)

table.create(bind=engine)

items = [(1, 'a'), (2, 'b'), (3, 'c')]
values = [{'id': a, 'value': b} for a, b in items]

# Ideally this would render a single INSERT statement, rather than three
session.execute(table.insert(), values)

```

[Sqlalchemy-tickets] Issue #3191: Sorting order is messed up with ordering_list (zzzeek/sqlalchemy)

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

New issue 3191: Sorting order is messed up with ordering_list
https://bitbucket.org/zzzeek/sqlalchemy/issue/3191/sorting-order-is-messed-up-with

Aleksandr Osipov:

Sorting order is messed up if we replace existing list with new one with some items removed.
This happens in the function orm.collections.bulk_replace, when removing items missing in new collection from old one in order to fire events.

Calling .remove method of ordering_list cause reordering of items in old collection, but this also affects ordering in new collection.

Changing two loops in orm.collections.bulk_replace seems to solve the problems.

[Sqlalchemy-tickets] Issue #3190: transactional state isn't carried over with connection._branch() (zzzeek/sqlalchemy)

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

New issue 3190: transactional state isn't carried over with connection._branch()
https://bitbucket.org/zzzeek/sqlalchemy/issue/3190/transactional-state-isnt-carried-over-with

Mike Bayer:

eg. the branched connection doesn't take on __transaction.  we should probably fix this.

[Sqlalchemy-tickets] Issue #3189: Combining association proxy and ordering list does not work (zzzeek/sqlalchemy)

[Wichert A. <iss...@bi...>]

New issue 3189: Combining association proxy and ordering list does not work
https://bitbucket.org/zzzeek/sqlalchemy/issue/3189/combining-association-proxy-and-ordering

Wichert Akkerman:

I want to create an ordered many-to-many relationship, where I only really care about the order from one side of the relationship. I tried to model that by creating a relationship using `ordering_list`, and adding an `association_proxy` on top of that. That works fine when adding items to the list, but fails when trying to remove items from the list.

I have attached a small python script that reproduces the error. This is the essential part of it:

```python
class FolderImage(BaseObject):
    __tablename__ = 'folder_image'

    image_id = schema.Column(types.Integer(),
        schema.ForeignKey('image.id'), primary_key=True)
    folder_id = schema.Column(types.Integer(),
        schema.ForeignKey('folder.id'), primary_key=True)
    position = schema.Column(types.Integer())

    def __init__(self, image=None, **kw):
        BaseObject.__init__(self, image_id=image.id, **kw)


class Image(BaseObject):
    __tablename__ = 'image'
    id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)


class Folder(BaseObject):
    __tablename__ = 'folder'

    id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
    _images = orm.relationship('FolderImage', order_by=[FolderImage.position],
        collection_class=ordering_list('position'))
    images = association_proxy('_images', 'image')

img = Image()
folder = Folder(images=[img])
session.add(folder)
session.flush()
folder.images = [img]
session.flush()
```

Running this results in:

```
sqlalchemy.exc.IntegrityError: (IntegrityError) NOT NULL constraint failed: folder_image.image_id u'INSERT INTO folder_image (folder_id, position) VALUES (?, ?)' (1, 0)
```


I am wondering if this is a regression since I apparently managed to do this a couple of years ago (see [this post](https://groups.google.com/forum/#!topic/sqlalchemy/S4_8PeRBNJw).

[Sqlalchemy-tickets] Issue #3188: regression in 0.9's "order by collapse" logic (zzzeek/sqlalchemy)

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

New issue 3188: regression in 0.9's "order by collapse" logic
https://bitbucket.org/zzzeek/sqlalchemy/issue/3188/regression-in-09s-order-by-collapse-logic

Mike Bayer:

seems to be non-deterministic


```
#!python

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

Base = declarative_base()

class C(Base):
    __tablename__ = 'c'

    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))
    b_id = Column(ForeignKey('b.id'))

class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)

B.b = column_property(
        select([func.max(C.id)]).where(C.b_id == B.id).correlate(B)
    )


b1 = aliased(B)

sess = Session()

#print sess.query(B, b1).order_by(B.b)
print sess.query(B, b1).order_by(b1.b)

```

output, based on if you uncomment that one query, for the *second* query, is:

```
#!sql


SELECT b.id AS b_id, (SELECT max(c.id) AS max_1 
FROM c 
WHERE c.b_id = b.id) AS anon_1, b_1.id AS b_1_id, (SELECT max(c.id) AS max_2 
FROM c 
WHERE c.b_id = b_1.id) AS anon_2 
FROM b, b AS b_1 ORDER BY anon_1
```

e.g. it is ordering by the wrong expression.

there's a lot going on with order_by right now so im not sure this can be in 0.9, will need to see if something can be backported.

[Sqlalchemy-tickets] Issue #3187: Eager loading not working when entity in identity_map (zzzeek/sqlalchemy)

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

New issue 3187: Eager loading not working when entity in identity_map
https://bitbucket.org/zzzeek/sqlalchemy/issue/3187/eager-loading-not-working-when-entity-in

Aidan Kane:

I'm seeing slightly surprising behaviour around loading options when an entity is already in the session identity_map. I've attached a self-contained test case so you can more easily see what I mean.

Depending on how you query for an entity (get() vs .filter().one()) you get different loading behaviour if the entity is already in the identity_map.

In the simplest case:

```
#!python

x = session.query(A).get(1)

# the loading options are ignored here
x = session.query(A).options(...loading_options...).get(1)

# while they're honoured here
x = session.query(A).options(...loading_options...).filter(A.id==1).one()

```

To run my test case:
```
#!python

import loading_behaviour_bug
loading_behaviour_bug.setup()

# subquery loading works
loading_behaviour_bug.test_expected1()

# subquery loading works
loading_behaviour_bug.test_expected2()

# subquery loading doesn't work
loading_behaviour_bug.test_unexpected()

```

I suspect this might be expected behaviour but I couldn't find anything about it in the docs.

Whenever you call get() it's coming from the identity_map, and it may have different children than those in the db (because another bit of code has made a change / dirtied the object) - though I would have thought that would be ok because it's in the same transaction. 

My issue is that depending on what code has run up to this point I can't use .get() because I can't trust the eager loading to run (my current workaround is to use the filter().one() pattern in critical bits of code). It might that I just need a little more management around the scope of the unit-or-work but you can see how easy it would be to get this wrong (especially in a web application where the recommendation is to tie the unit-of-work to the entire request).

Then again, I may have just completely missed something because it seems implausible to me that I could find anything resembling a bug in SQLA :)

[Sqlalchemy-tickets] Issue #3186: add special logic to suit MySQLs "sort of native boolean" thing (zzzeek/sqlalchemy)

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

New issue 3186: add special logic to suit MySQLs "sort of native boolean" thing
https://bitbucket.org/zzzeek/sqlalchemy/issue/3186/add-special-logic-to-suit-mysqls-sort-of

Mike Bayer:

we can turn on the supports_native_boolean flag in MySQL and most things work, because it has names "BOOL", "true", "false" as aliases for tinyint, 1 and 0, plus "is" works.   But of course not everything works, we get back 1s and 0s for some things so it doesnt support "native_boolean" fully.   We need to provide workarounds for it.

[Sqlalchemy-tickets] Issue #3185: More than one level of abstract concrete base classes does not work (zzzeek/sqlalchemy)

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

New issue 3185: More than one level of abstract concrete base classes does not work
https://bitbucket.org/zzzeek/sqlalchemy/issue/3185/more-than-one-level-of-abstract-concrete

Alex Grönholm:

The following code fails with AttributeError: 'NoneType' object has no attribute 'concrete':
```
#!python

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

Base = declarative_base()


class Document(Base, AbstractConcreteBase):
    doctype = Column(Unicode, nullable=False)


class ContactDocument(Document):
    __abstract__ = True

    send_method = Column('sendmethod', Unicode)


class ActualDocument(ContactDocument):
    __tablename__ = 'actual_documents'
    __mapper_args__ = {'concrete': True, 'polymorphic_identity': 'actual'}

    id = Column(Integer, primary_key=True)

configure_mappers()
```

This is caused by the __declare_first__() in AbstractConcreteBase not checking if the target subclass has a mapper or not. Attached is the patch I was given to remedy the issue.

[Sqlalchemy-tickets] Issue #3184: sqlite does not reflect unique constraints (zzzeek/sqlalchemy)

[Johannes E. <iss...@bi...>]

New issue 3184: sqlite does not reflect unique constraints
https://bitbucket.org/zzzeek/sqlalchemy/issue/3184/sqlite-does-not-reflect-unique-constraints

Johannes Erdfelt:

sqlite treats unique indexes and unique constraints as distinct entities. However, only indexes are reflected and unique constraints aren't.

This is different than mysql (which aliases unique constraints to a unique index) and postgresql (which has distinct unique indexes and unique constraints).

The attached script shows the problem.

[Sqlalchemy-tickets] Issue #3183: 'IS (NOT) DISTINCT FROM' operator missing (zzzeek/sqlalchemy)

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

New issue 3183: 'IS (NOT) DISTINCT FROM' operator missing
https://bitbucket.org/zzzeek/sqlalchemy/issue/3183/is-not-distinct-from-operator-missing

tehasdf:

It seems there's no support for the (postgresql, but I think it's sql 1999) 'IS DISTINCT FROM' and 'IS NOT DISTINCT FROM' operators.

See:

http://www.postgresql.org/docs/9.4/static/functions-comparison.html
https://wiki.postgresql.org/wiki/Is_distinct_from

[Sqlalchemy-tickets] Issue #3182: remove "SQL Server" pyodbc driver name default (zzzeek/sqlalchemy)

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

New issue 3182: remove "SQL Server" pyodbc driver name default
https://bitbucket.org/zzzeek/sqlalchemy/issue/3182/remove-sql-server-pyodbc-driver-name

Mike Bayer:

this is in mssql/pyodbc.  it is only used for DSN-less connections, and should be removed; it apparently isn't the best "default", and if someone is specifying hostname and not DSN in any case, they need to set this up themselves.

[Sqlalchemy-tickets] Issue #3181: MetaData.reflect with "only" keyword fails due to wrong capitalization [Oracle] (zzzeek/sqlalchemy)

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

New issue 3181: MetaData.reflect with "only" keyword fails due to wrong capitalization [Oracle]
https://bitbucket.org/zzzeek/sqlalchemy/issue/3181/metadatareflect-with-only-keyword-fails

Ivan Smirnov:

```sql
CREATE TABLE "FOO"."BAR" (
...
)
```

This works as expected:
```python
>>> engine.has_table('BAR', schema='FOO')
True
>>> engine.has_table('bar', schema='FOO')
True
```

However this doesn't:
```python
>>> meta.reflect(schema='FOO', only=['BAR'])
InvalidRequestError: Could not reflect: requested table(s) not available in oracle+cx_oracle://***:***@*** schema 'FOO': (BAR)
```
which only seems to works if names in `only` are manually lowercased:
```python
>>> meta.reflect(schema='FOO', only=['bar'])
```


Responsible: zzzeek