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

[Sqlalchemy-tickets] Issue #3306: Splitting ORM and Core into seperate packages (zzzeek/sqlalchemy)

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

New issue 3306: Splitting ORM and Core into seperate packages
https://bitbucket.org/zzzeek/sqlalchemy/issue/3306/splitting-orm-and-core-into-seperate

nyov:

Notice: This is a knee-jerk proposal, without much forthought about possibility of implementation.

I feel it would be nice, if at all possible, if SQLAlchemy could have a more obvious split into the abstraction layer / Core and ORM parts, at least providing two seperate packages and maybe a slightly different name for the core part even; having the ORM package depend on the core, obviously.

The reason is that I have found myself using SQLAlchemy Core with alternative strategies, most notably the Alchimi project for using the core abstraction layer with twisted and shunning the ORM, which is so problematic with these programming models.

I think projects like this could benefit from a dedicated Core package, and vice versa.
I'm not saying the code parts should deviate apart, not at all, both packages could build from the same source repository.

But more importantly, while the official documentation makes a distinct split between ORM and Core, people casually equate SQLAlchemy with ORM only throughout third party documentation:
When looking for solutions to problems, ideas, or code examples, *with the restriction of not using the ORM*, it's easy to get frustrated when opening up yet another blog post or code snippet going with an ORM solution.

That is of course inevitable when both parts share the same name and one is somewhat "hidden" in the guts of the other. There is just no good way to search the web for solutions with e.g. "SQLAlchemy prepared queries no ORM". All you'll get is "SQLAlchemy ORM" pages ;)

Because of this, some people will not even know that SQLAlchemy is useable as anything else than just an ORM, I know I didn't when first reading about it.
That might also explain why the great abstraction layer alone isn't so well known and represented on the web.

All of which is why I feel having a distinct *name* and *package* for the Core part (under the same SQLAlchemy umbrella) could be a good strategy for the project, if such a refactoring can be achieved.

Thanks for reading. Cheers

[Sqlalchemy-tickets] Issue #3305: Backend-agnostic GUID breaks session tracking (zzzeek/sqlalchemy)

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

New issue 3305: Backend-agnostic GUID breaks session tracking
https://bitbucket.org/zzzeek/sqlalchemy/issue/3305/backend-agnostic-guid-breaks-session

Wichert Akkerman:

I have a PostgreSQL database which uses a UUID column as primary key. To access this via SQLAlchemy and still allow testing with SQLite I am using the [http://docs.sqlalchemy.org/en/rel_0_9/core/custom_types.html#backend-agnostic-guid-type](Backend-agnostic GUID type) from the documentation. With this model I notice something strange: cached objects merged into the session still trigger SELECTs when they are accessed. I have attached an example which shows this: it sets up two tables with a simple parent/child relationship, creates some data, pickles and unpickles that to simulate a cache and finally access child objects through the relationship.

If I use an Integer type for the primary keys this works as expected and no SELECTs are generated. Using the GUID type I see the following:

```
Accessing children. This should not trigger any SQL statements

2015-02-10 16:06:53,997 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-02-10 16:06:53,998 INFO sqlalchemy.engine.base.Engine SELECT child.id AS child_id, child.parent_id AS child_parent_id, child.name AS child_name 
FROM child 
WHERE ? = child.parent_id
2015-02-10 16:06:53,998 INFO sqlalchemy.engine.base.Engine ('7de83a91-1b08-4d8e-808b-cc9d91fe2491',)
[u'child-1', u'child-2', u'child-3']
2015-02-10 16:06:53,998 INFO sqlalchemy.engine.base.Engine ROLLBACK
```

[Sqlalchemy-tickets] Issue #3304: Add Query.get_all to query by list of IDs (zzzeek/sqlalchemy)

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

New issue 3304: Add Query.get_all to query by list of IDs
https://bitbucket.org/zzzeek/sqlalchemy/issue/3304/add-queryget_all-to-query-by-list-of-ids

Marian Sigler:

Inspiration for this: http://stackoverflow.com/a/21267811/196244

Sometimes one has a list of IDs and needs to fetch the associated objects. There's two ways to do that:

    session.query(Record).filter(Record.id.in_(seq)).all()

    map(session.query(Record).get, seq)

Which one is better depends on whether most of the objects are already in the session or not. I assume it is trivial for SQLAlchemy to find out if this is the case, and choose the appropriate method.

Thus I propose adding a method `Query.get_all` that takes a list of IDs and returns the corresponding objects, guessing the better method to use.
(Bonus: to allow functions using it to either accept a list of ids or of objects, allow the objects as input, too)

[Sqlalchemy-tickets] Issue #3303: Add a 'changed' event for sqlalchemy.ext.mutable attributes (zzzeek/sqlalchemy)

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

New issue 3303: Add a 'changed' event for sqlalchemy.ext.mutable attributes
https://bitbucket.org/zzzeek/sqlalchemy/issue/3303/add-a-changed-event-for

zoomorph:

Listening for changes to a scalar attribute can be done via the 'set' event:
```
#!python
def validate_phone(target, value, oldvalue, initiator):
    """Strip non-numeric characters from a phone number"""

    return re.sub(r'(?![0-9])', '', value)

# setup listener on UserContact.phone attribute, instructing
# it to use the return value
listen(UserContact.phone, 'set', validate_phone, retval=True)
```

When Mutable attributes are set, they also emit the 'set' event. However, they do not emit any events when they are merely changed.

A new event such as 'changed' should be added to cover this case. It would work like so:
```
#!python
def favorites_changed(target, value, oldvalue, initiator):
    logger.info("Favorites was set or changed")
    # do something

# setup listeners on UserContact.favorites attribute (a MutableList for example)
listen(UserContact.favorites, 'set', favorites_changed)
listen(UserContact.favorites, 'changed', favorites_changed)

u = session.query(UserContact).first()
u.favorites = [1, 2, 3]  # emits 'set'
u.favorites.append(4)  # should emit 'changed'
u.favorites[-1] = 5  # should emit 'changed'
```

Related to issue #3297

[Sqlalchemy-tickets] Issue #3302: reset isolation level fails on invalided connection (zzzeek/sqlalchemy)

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

New issue 3302: reset isolation level fails on invalided connection
https://bitbucket.org/zzzeek/sqlalchemy/issue/3302/reset-isolation-level-fails-on-invalided

Mike Bayer:


```
#!python

>>> from sqlalchemy import create_engine
>>> e = create_engine("sqlite://")  
>>> c1 = e.connect()
>>> c2 = c1.execution_options(isolation_level='SERIALIZABLE')
>>> c2.invalidate()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 482, in invalidate
    self._root.__connection.invalidate(exception)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/pool.py", line 759, in invalidate
    self._checkin()
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/pool.py", line 682, in _checkin
    self._pool, None, self._echo, fairy=self)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/pool.py", line 580, in _finalize_fairy
    connection_record.checkin()
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/pool.py", line 466, in checkin
    finalizer(connection)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/default.py", line 454, in reset_isolation_level
    self.set_isolation_level(dbapi_conn, self.default_isolation_level)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/dialects/sqlite/base.py", line 868, in set_isolation_level
    cursor = connection.cursor()
AttributeError: 'NoneType' object has no attribute 'cursor'
>>> 

```

[Sqlalchemy-tickets] Issue #3301: session._update_impl adds the item to identity map even if error thrown (zzzeek/sqlalchemy)

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

New issue 3301: session._update_impl adds the item to identity map even if error thrown
https://bitbucket.org/zzzeek/sqlalchemy/issue/3301/session_update_impl-adds-the-item-to

Mike Bayer:


```
#!python

from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, session

Base = declarative_base()

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

s1 = Session()
s2 = Session()
u1 = User(id=1, name='u1')
session.make_transient_to_detached(u1)  # shorthand for actually persisting it
s1.add(u1)

try:
    s2.add(u1)
except:
    assert not s2.identity_map.keys()
```

patch:


```
#!diff

diff --git a/lib/sqlalchemy/orm/session.py b/lib/sqlalchemy/orm/session.py
index 0e272dc..2b26831 100644
--- a/lib/sqlalchemy/orm/session.py
+++ b/lib/sqlalchemy/orm/session.py
@@ -1795,7 +1795,7 @@ class Session(_SessionClassMethods):
                 "function to send this object back to the transient state." %
                 state_str(state)
             )
-        self._before_attach(state)
+        self._before_attach(state, check_identity_map=False)
         self._deleted.pop(state, None)
         if discard_existing:
             self.identity_map.replace(state)
@@ -1875,13 +1875,12 @@ class Session(_SessionClassMethods):
         self._attach(state, include_before=True)
         state._load_pending = True
 
-    def _before_attach(self, state):
+    def _before_attach(self, state, check_identity_map=True):
         if state.session_id != self.hash_key and \
                 self.dispatch.before_attach:
             self.dispatch.before_attach(self, state.obj())
 
-    def _attach(self, state, include_before=False):
-        if state.key and \
+        if check_identity_map and state.key and \
             state.key in self.identity_map and \
                 not self.identity_map.contains_state(state):
             raise sa_exc.InvalidRequestError(
@@ -1897,10 +1896,11 @@ class Session(_SessionClassMethods):
                 "(this is '%s')" % (state_str(state),
                                     state.session_id, self.hash_key))
 
+    def _attach(self, state, include_before=False):
+
         if state.session_id != self.hash_key:
-            if include_before and \
-                    self.dispatch.before_attach:
-                self.dispatch.before_attach(self, state.obj())
+            if include_before:
+                self._before_attach(state)
             state.session_id = self.hash_key
             if state.modified and state._strong_obj is None:
                 state._strong_obj = state.obj()

```


Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3300: create_lazy_clause() is only turning a target col into a bind on the first occurrence (zzzeek/sqlalchemy)

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

New issue 3300: create_lazy_clause() is only turning a target col into a bind on the first occurrence
https://bitbucket.org/zzzeek/sqlalchemy/issue/3300/create_lazy_clause-is-only-turning-a

Mike Bayer:

the logic commented out below has the effect that if a "local" column appears more than once in the clause, it only gets turned to a bound param once.  need to determine the rationale for this logic and get it to be more specific to the case where it is needed.

```
#!diff


diff --git a/lib/sqlalchemy/orm/relationships.py b/lib/sqlalchemy/orm/relationships.py
index df2250a..9a5e1ba 100644
--- a/lib/sqlalchemy/orm/relationships.py
+++ b/lib/sqlalchemy/orm/relationships.py
@@ -2709,10 +2709,10 @@ class JoinCondition(object):
         def col_to_bind(col):
             if (reverse_direction and col in lookup) or \
                     (not reverse_direction and "local" in col._annotations):
-                if col in lookup:
-                    for tobind, equated in lookup[col]:
-                        if equated in binds:
-                            return None
+                #if col in lookup:
+                #    for tobind, equated in lookup[col]:
+                #        if equated in binds:
+                #            return None
                 if col not in binds:
                     binds[col] = sql.bindparam(
                         None, None, type_=col.type, unique=True)

```

this script illustrates three different join conditions, the third one is pretty simple, which all illustrate the same thing happening:


```
#!python

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

Base = declarative_base()


# NOT (:param_1 != a.attr OR a.attr IS NULL OR a.attr IS NULL)
# OR a.attr IS NULL AND a.attr IS NULL
notdistinct_1 = lambda a, b: ~(
    (a != b) | (a == None) |
    (b == None)) | ((a == None) & (b == None))

# :param_1 != a.attr OR a.attr IS NULL OR a.attr IS NULL
notdistinct_2 = lambda a, b: (
    (a != b) | (a == None) | (b == None))

# :param_1 = a.attr OR a.attr = a.attr
notdistinct_3 = lambda a, b: ((a == b) | (b == a))


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    attr = Column(Integer)
    collection = relationship(
        "A",
        viewonly=True,
        primaryjoin=lambda: and_(
            notdistinct_3(A.attr, foreign(remote(A.attr)))
        )
    )

configure_mappers()

print A.collection.property.strategy._lazywhere


```



Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3299: schematype constraints don't use naming convention if it doesnt include "constraint_name" (zzzeek/sqlalchemy)

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

New issue 3299: schematype constraints don't use naming convention if it doesnt include "constraint_name"
https://bitbucket.org/zzzeek/sqlalchemy/issue/3299/schematype-constraints-dont-use-naming

Mike Bayer:

this is a little limiting.  due to #3067, the constraint convention below will not even be consulted:


```
#!python

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

m1 = MetaData(naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"})
Base = declarative_base(metadata=m1)

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    foob = Column(BOOLEAN)

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


```

this change needs to be made in order for it to be seen:

```
#!diff


diff --git a/lib/sqlalchemy/sql/naming.py b/lib/sqlalchemy/sql/naming.py
index 9e57418..21bf05d 100644
--- a/lib/sqlalchemy/sql/naming.py
+++ b/lib/sqlalchemy/sql/naming.py
@@ -114,8 +114,9 @@ def _constraint_name_for_table(const, table):
     if isinstance(const.name, conv):
         return const.name
     elif convention is not None and (
-        const.name is None or not isinstance(const.name, conv) and
-            "constraint_name" in convention
+        const.name is None or (
+            not isinstance(const.name, conv) or
+            "constraint_name" in convention)
     ):
         return conv(
             convention % ConventionDict(const, table,

```

there's no reason CheckConstraint can't have a .columns collection so that the columns_0_name convention can be useful.

I dont know that we want to change this for 0.9.

[Sqlalchemy-tickets] Issue #3298: When using "Overriding Reflected Columns" feature, foreign key constraints fails when (auto-)generating relationship properties (zzzeek/sqlalchemy)

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

New issue 3298: When using "Overriding Reflected Columns" feature, foreign key constraints fails when (auto-)generating relationship properties
https://bitbucket.org/zzzeek/sqlalchemy/issue/3298/when-using-overriding-reflected-columns

Armin Gruner:

Hi,

if I change some property names distinct from the table column names according to

http://docs.sqlalchemy.org/en/rel_0_9/core/reflection.html?highlight=column%20reflect#sqlalchemy.engine.reflection.Inspector.get_columns,

I get an error later when preparing the automap base:

```
#!python

@event.listens_for(Table, 'column_reflect')
def receive_column_reflect(inspector, table, column_info):
  name = column_info["name"]
  column_info["key"] = name.lower()

def setup():
  Base.metadata.reflect(engine, extend_existing=True, autoload_replace=False)
  Base.prepare(engine)
```

NoReferencedColumnError: Could not initialize target column for ForeignKey 'ABSTRACT_FORWARD_DESTINATION.ID' on table 'TRUNK': table 'ABSTRACT_FORWARD_DESTINATION' has no column named 'ID'

If I disable the code inside the column reflection listener, SQLAlchemy 0.9.8 properly reflects all tables, creates object classes and relations.

[Sqlalchemy-tickets] Issue #3297: Custom MutableSet (similar to MutableDict) not pickling (zzzeek/sqlalchemy)

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

New issue 3297: Custom MutableSet (similar to MutableDict) not pickling
https://bitbucket.org/zzzeek/sqlalchemy/issue/3297/custom-mutableset-similar-to-mutabledict

Eoghan Murray:

I've created a MutableSet class, closely following the MutableDict class in ext.mutable.

Unfortunately it doesn't pickle correctly and I can't figure out what the problem is.

```
#!python

from sqlalchemy import *
from sqlalchemy.ext.mutable import Mutable, MutableDict
test_table = Table('test_table, metadata,
    Column('test_pk', String(40), primary_key=True),
    Column('as_dict', MutableDict.as_mutable(JsonType)),
    Column('as_set', MutableSet.as_mutable(JsonType))
```


 my error message after using pickle.dumps on an object that contains the MutableSet column.
Note the 'remove' function mentioned in the error is to do with  _parents WeakRef rather than MutableSet.remove)


```
#!python
  File "/usr/lib/python2.7/pickle.py", line 748, in save_global
    (obj, module, name))
PicklingError: Can't pickle <function remove at 0xb48a041c>: it's not found as weakref.remove
```

And the MutableSet class (and JsonType supporting class - included for completeness)

```
#!python

class MutableSet(Mutable, set):
    @classmethod
    def coerce(cls, key, value):
        "Convert plain sets to MutableSet."

        if not isinstance(value, MutableSet):
            if isinstance(value, set):
                return MutableSet(value)
            elif isinstance(value, dict):
                return MutableDict(value)
            # this call will raise ValueError
            return Mutable.coerce(key, value)
        else:
            return value
    def __getstate__(self):
        return set(self)
    def __setstate__(self, state):
        self.update(state)
    def update(self, *args):
        set.update(self, *args, **kwargs)
        self.changed()
    def intersection_update(self, *args):
        set.intersection_update(self, *args)
        self.changed()
    def difference_update(self, *args):
        set.difference_update(self, *args)
        self.changed()
    def symmetric_difference_update(self, *args):
        set.symmetric_difference_update(self, *args)
        self.changed()
    def add(self, elem):
        set.add(self, elem)
        self.changed()
    def remove(self, elem):
        set.remove(self, elem)
        self.changed()
    def discard(self, elem):
        set.discard(self, elem)
        self.changed()
    def pop(self):
        set.pop(self)
        self.changed()
    def clear(self):
        set.clear(self)
        self.changed()
```


```
#!python

class JsonType(types.TypeDecorator):
    impl = types.Unicode

    def process_bind_param(self, value, engine):
        import jsonpickle
        if isinstance(value, MutableList):
            value = [v for v in value]
        if isinstance(value, MutableDict):
            value = dict(value)
        if isinstance(value, MutableSet):
            value = set(value)
        return unicode(jsonpickle.encode(value))

    def process_result_value(self, value, engine):
        import jsonpickle
        if value:
            return jsonpickle.decode(value)
        else:
            return {}

    def copy_value(self, value):
        return deepcopy(value)

    def compare_values(self, x, y):
        return x == y

```

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3296: raise if isolation_level execution option is used when a Transaction is present; warning in 0.9 (zzzeek/sqlalchemy)

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

New issue 3296: raise if isolation_level execution option is used when a Transaction is present; warning in 0.9
https://bitbucket.org/zzzeek/sqlalchemy/issue/3296/raise-if-isolation_level-execution-option

Mike Bayer:

isolation level settings typically do not work until the next transaction, and at least in the case of Psycopg2 are destructive.  For this reason, the MySQL dialect already implements a COMMIT, and psycopg2 actually *resets* the transaction when you change the level:

```
#!python


>>> import psycopg2
>>> conn = psycopg2.connect(user='scott', dbname='test', password='tiger', host='localhost')
>>> cursor = conn.cursor()
>>> cursor.execute("create table foo (id integer)")
>>> cursor.execute("insert into foo (id) values (1)")
>>> cursor.close()
>>> from psycopg2 import extensions
>>> conn.set_isolation_level(extensions.ISOLATION_LEVEL_SERIALIZABLE)
>>> cursor = conn.cursor()
>>> cursor.execute("select * from foo")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.ProgrammingError: relation "foo" does not exist
LINE 1: select * from foo
                      ^


```

huge warning is needed and definitely an exception in 1.0.

[Sqlalchemy-tickets] Issue #3295: effectively no documentation for get_children / the visitors module (zzzeek/sqlalchemy)

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

New issue 3295: effectively no documentation for get_children / the visitors module
https://bitbucket.org/zzzeek/sqlalchemy/issue/3295/effectively-no-documentation-for

kenko:

... even though traversing parts of a query is necessary for getting horizontal sharding right (and is even exhibited in one of the examples).

[Sqlalchemy-tickets] Issue #3294: (DatabaseError) error with no message from the libpq (zzzeek/sqlalchemy)

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

New issue 3294: (DatabaseError) error with no message from the libpq
https://bitbucket.org/zzzeek/sqlalchemy/issue/3294/databaseerror-error-with-no-message-from

christian_barra:

Hi guys, sometimes I get this error:

Exception on /auth/login [POST] Traceback (most recent call last): File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 951, in _execute_context context) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/default.py", line 436, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python3.4/dist-packages/newrelic-2.40.0.34/newrelic/hooks/database_dbapi2.py", line 22, in execute *args, **kwargs) psycopg2.DatabaseError: error with no message from the libpq The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/usr/local/lib/python3.4/dist-packages/flask/app.py", line 1817, in wsgi_app response = self.full_dispatch_request() File "/usr/local/lib/python3.4/dist-packages/flask/app.py", line 1477, in full_dispatch_request rv = self.handle_user_exception(e) File "/usr/local/lib/python3.4/dist-packages/newrelic-2.40.0.34/newrelic/hooks/framework_flask.py", line 98, in _nr_w
 rapper_Flask_handle_exception_ return wrapped(*args, **kwargs) File "/usr/local/lib/python3.4/dist-packages/flask/app.py", line 1381, in handle_user_exception reraise(exc_type, exc_value, tb) File "/usr/local/lib/python3.4/dist-packages/flask/_compat.py", line 33, in reraise raise value File "/usr/local/lib/python3.4/dist-packages/flask/app.py", line 1475, in full_dispatch_request rv = self.dispatch_request() File "/usr/local/lib/python3.4/dist-packages/flask/app.py", line 1461, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File "/usr/local/lib/python3.4/dist-packages/newrelic-2.40.0.34/newrelic/hooks/framework_flask.py", line 40, in _nr_wrapper_handler_ return wrapped(*args, **kwargs) File "./main/views.py", line 63, in auth_login user = User.query.filter_by(username=username).first() File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/query.py", line 2367, in first ret = list(self[0:1]) File "/usr/local/lib/python3.4/dist-packages/s
 qlalchemy/orm/query.py", line 2228, in __getitem__ return list(res) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/query.py", line 2438, in __iter__ return self._execute_and_instances(context) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/query.py", line 2453, in _execute_and_instances result = conn.execute(querycontext.statement, self._params) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 729, in execute return meth(self, multiparams, params) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 826, in 
_execute_clauseelement compiled_sql, distilled_params File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 958, in _execute_context context) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception exc_info File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 188, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=exc_value) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 181, in reraise raise value.with_traceback(tb) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 951, in _execute_context context) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/default.py", line 436, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python3.4/dist-packages/newrelic-2.40.0.34/newrelic/hooks/database_dbapi2.py", line 22, in execute *args, **kwargs) 

sqlalchemy.exc.DatabaseError: (DatabaseError) error with no message from the libpq 'SELECT users.id AS users_id, users.username AS users_username, users.first_name AS users_first_name, users.last_name AS users_last_name, users.password AS users_password, users.status AS users_status, users.is_staff AS users_is_staff, users.user_plan AS users_user_plan, users.last_login AS users_last_login, users.date_joined AS users_date_joined, users.reset_password_token AS users_reset_password_token \nFROM users \nWHERE users.username = %(username_1)s \n LIMIT %(param_1)s' {'param_1': 1, 'username_1': 'xx...@xx...'}

[Sqlalchemy-tickets] Issue #3293: repair testing assertsql system to deal with intermediary statements (zzzeek/sqlalchemy)

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

New issue 3293: repair testing assertsql system to deal with intermediary statements
https://bitbucket.org/zzzeek/sqlalchemy/issue/3293/repair-testing-assertsql-system-to-deal

Mike Bayer:


```
#!

==================================================================================== FAILURES ====================================================================================
_____________________________________________________________________ QueryTest.test_disable_scope_identity ______________________________________________________________________
Traceback (most recent call last):
  File "<string>", line 2, in test_disable_scope_identity
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/exclusions.py", line 95, in decorate
    return self._do(config._current, fn, *args, **kw)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/exclusions.py", line 124, in _do
    self._expect_failure(config, ex, name=fn.__name__)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/exclusions.py", line 136, in _expect_failure
    util.raise_from_cause(ex)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/exclusions.py", line 122, in _do
    return_value = fn(*args, **kw)
  File "<string>", line 2, in test_disable_scope_identity
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/util.py", line 191, in provide_metadata
    return fn(*args, **kw)
  File "/Users/classic/dev/sqlalchemy/test/dialect/mssql/test_query.py", line 250, in test_disable_scope_identity
    ExactSQL("INSERT INTO t1 DEFAULT VALUES"),
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/assertions.py", line 420, in assert_sql_execution
    asserter.assert_(*rules)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/assertsql.py", line 373, in assert_
    if not rule.consume_final():
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/testing/assertsql.py", line 50, in consume_final
    assert False, 'Rule has not been consumed'
AssertionError: Rule has not been consumed
============================================================================ short test summary info =============================================================================
FAIL test/dialect/mssql/test_query.py::QueryTest::()::test_disable_scope_identity
```

we need a system that can log "real" statements as well as the "intermediary" ones like sequences and stuff, and can assert both or ignore the "intermediary" ones.   the rules / observations here need to be a lot smarter.  try to get this for 1.0.

[Sqlalchemy-tickets] Issue #3292: Support native Python 3.4 enums with enum type (zzzeek/sqlalchemy)

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

New issue 3292: Support native Python 3.4 enums with enum type
https://bitbucket.org/zzzeek/sqlalchemy/issue/3292/support-native-python-34-enums-with-enum

Veeti Paananen:

It would be nice to have built-in integration between the SQLAlchemy enum type and standard Python enums.

[Sqlalchemy-tickets] Issue #3291: Problem using binary type with foreign key relationship between models (zzzeek/sqlalchemy)

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

New issue 3291: Problem using binary type with foreign key relationship between models
https://bitbucket.org/zzzeek/sqlalchemy/issue/3291/problem-using-binary-type-with-foreign-key

David Czarnecki:

Running into a problem trying to use a binary type with a foreign key relationship between models. This stems from trying to use the UUIDType from SQLAlchemy-Utils and choosing the binary form, which uses a BINARY(16) type for the UUID column. This is using MariaDB. Relevant software and library versions are included at the end.

Here are the create table scripts:


```
#!mysql

MariaDB [flask_accounts_development]> show create table account\G;
*************************** 1. row ***************************
       Table: account
Create Table: CREATE TABLE `account` (
  `id` binary(16) NOT NULL,
  `username` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: No query specified
```

```
#!mysql
MariaDB [flask_accounts_development]> show create table address\G;
*************************** 1. row ***************************
       Table: address
Create Table: CREATE TABLE `address` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` binary(16) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `account_id` (`account_id`),
  CONSTRAINT `address_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: No query specified
```

Account and Address model definitions:

```
#!python
from app import db
from sqlalchemy_utils import UUIDType

class Account(db.Model):
    id = db.Column(UUIDType, primary_key=True)
    username = db.Column(db.String(64), unique=True, nullable=False)
    addresses = db.relationship('Address', backref=db.backref('account'), cascade='all, delete-orphan')

    def __init__(self, username):
        self.username = username

    def __repr__(self):
        return '<Account id=%r name=%r>' % (self.id, self.username)

class Address(db.Model):
    id          = db.Column(db.Integer, primary_key=True)
    account_id  = db.Column(UUIDType, db.ForeignKey('account.id'), nullable=False)

    def __repr__(self):
        return '<Address id=%r account_id=%r>' % (self.id, self.account_id.hex)
```

Code to exercise the issue:

```
#!python
from app.accounts.models import Address
from app import db
from app.accounts.models import Account
import uuid

account = Account(username='david')
account.id = uuid.uuid4()

db.session.add(account)
db.session.commit()

address = Address(account=account)

db.session.add(address)
db.session.commit()

account.addresses
```

Running this from a Flask shell, I get the following error trying to get the addresses for an account:

```
#!python
In [12]: account.addresses
/Users/dczarnecki/.virtualenvs/ba-flask-migrate-test/lib/python2.7/site-packages/sqlalchemy/engine/default.py:436: Warning: Invalid utf8 character string: 'DEF4C0'
  cursor.execute(statement, parameters)
Out[12]: []
```

The relationship is empty this way. I can however navigation the relationship from address to account.

```
#!python
In [11]: address.account
Out[11]: <Account id=UUID('5b608608-d29b-4f8e-bfa4-a80640ffaff3') name=u'david'>
```

Any thoughts on setting up the relationship correctly or where the issue might be going from account to addresses? 

Software:

MariaDB: mysql --version
mysql  Ver 15.1 Distrib 10.0.15-MariaDB, for osx10.10 (x86_64) using readline 5.1

Flask-SQLAlchemy: 2.0

SQLAlchemy-Utils: 0.26.6

MySQL-python: 1.2.5

[Sqlalchemy-tickets] Issue #3290: ORM class attributes that are mapped to database table: provide as built-in method. Generally, provide ORM class-level info. (zzzeek/sqlalchemy)

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

New issue 3290: ORM class attributes that are mapped to database table: provide as built-in method.  Generally, provide ORM class-level info.
https://bitbucket.org/zzzeek/sqlalchemy/issue/3290/orm-class-attributes-that-are-mapped-to

taibit:

I wanted a list of ORM class attributes that are mapped to db columns.

I found the built-in (private) function _orm_columns() in the source code file /lib/sqlalchemy/orm/base.py 
which worked when my ORM class attributes were not re-named.

But when I made an ORM attribute different from the db column, the _orm_columns() no longer worked.

Generally, there seems to be a need for info about the ORM class that is distinct from the mapping of that class to a db table.
There does not seem to be a place for that info that is provided out-of-the-box as far as I can tell.

My a solution for my particular issue that includes my code is posted on stackoverflow at: 
http://stackoverflow.com/questions/27947294/when-the-sqlalchemy-orm-class-attributes-are-different-from-database-columns-ho


BTW, is there a reason that the function  _orm_columns()  is private and not documented?
Otherwise, users struggle to come up with their own solution as in: 
http://stackoverflow.com/questions/1958219/convert-sqlalchemy-row-object-to-python-dict/24748320#24748320


On the positive side, it's nice that sqlalchemy and python provide the flexible structure for me to create a simple solution for my particular need as is documented in Mixin and Custom Base Classes http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative/mixins.html 

[Sqlalchemy-tickets] Issue #3289: Aliased join with subselect does not look at load options (zzzeek/sqlalchemy)

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

New issue 3289: Aliased join with subselect does not look at load options
https://bitbucket.org/zzzeek/sqlalchemy/issue/3289/aliased-join-with-subselect-does-not-look

malthe:

The following program outputs:
```sql
SELECT b.b_id AS b_b_id,
       b.a_id AS b_a_id,
       c.id AS c_id,
       c.c_name AS c_c_name,
       c.b_id AS c_b_id
FROM c JOIN (
       SELECT a.id AS a_id,
              a.a_name AS a_a_name,
              b.id AS b_id,
              b.b_name AS b_b_name
       FROM a JOIN b ON a.id = b.id
) AS b ON b.b_id = c.b_id
```
Note how ``b.b_name`` is in the subselect, but not used outside. It's a minor issue because it will most probably be optimized out by the database, but it still makes the query longer than it needs to be.

```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_name = Column(String)


class B(A):
    __tablename__ = 'b'

    id = Column(Integer, ForeignKey("a.id"), primary_key=True)
    b_name = Column(String)


class C(Base):
    __tablename__ = 'c'

    id = Column(Integer, primary_key=True)
    c_name = Column(String)
    b_id = Column("b_id", Integer, ForeignKey("b.id"))

    b = relationship(B)

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

s = Session(e)

b = B(id=1, a_name="foo", b_name="bar")
s.add(b)

c = C(id=1, b_id=b.id, c_name="boo")
s.add(c)

s.flush()

alias = aliased(B, name="b")
print s.query(C).join(alias).options(
    contains_eager(C.b, alias=alias).load_only(alias.id)
)
```

[Sqlalchemy-tickets] Issue #3287: Load options ignore inherited classes with alias (zzzeek/sqlalchemy)

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

New issue 3287: Load options ignore inherited classes with alias
https://bitbucket.org/zzzeek/sqlalchemy/issue/3287/load-options-ignore-inherited-classes-with

malthe:

If a query is made on an alias then the ``load_only`` option fails to exclude properties that are set on a base class of the aliased mapper.
```python
q = sess.query(alias).options(load_only("age"))
```
This is demonstrated in a test case here: https://github.com/malthe/sqlalchemy/tree/load-only-with-alias-issue

[Sqlalchemy-tickets] Issue #3286: PostgreSQL fulltext search broken when query string is not in proper tsquery format (zzzeek/sqlalchemy)

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

New issue 3286: PostgreSQL fulltext search broken when query string is not in proper tsquery format
https://bitbucket.org/zzzeek/sqlalchemy/issue/3286/postgresql-fulltext-search-broken-when

Priit Laes:

Postgres full text search with query strings that are not in proper format fails. Example from documentation:

Following SQLAlchemy code (taken from "Full Text Search" section under PostgreSQL dialect docs):

```select([sometable.c.text.match("search string")])```

should emit following SQL:

```SELECT text @@ to_tsquery('search string') FROM table```

But when executing that (using real column name and table name) ends up with:

```ERROR:  syntax error in tsquery: "search string"```

There's ```plainto_tsquery``` function that should be used instead, when query string is not in proper format:
```SELECT text @@ plainto_tsquery('search string') FROM table```

My idea would be to use plainto_tsquery with column.match and introduce column.match_tsquery which ends up emitting to_tsquery SQL.

[Sqlalchemy-tickets] Issue #3285: an exception which occurs when forming row processors in loading.py leaves the cursor open (zzzeek/sqlalchemy)

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

New issue 3285: an exception which occurs when forming row processors in loading.py leaves the cursor open
https://bitbucket.org/zzzeek/sqlalchemy/issue/3285/an-exception-which-occurs-when-forming-row

Mike Bayer:

e.g. such as this:


```
#!

test/orm/test_eager_relations.py::EagerTest::test_disable_dynamic FAILED

===================================================== FAILURES ======================================================
__________________________________________ EagerTest.test_disable_dynamic ___________________________________________
Traceback (most recent call last):
  File "/home/mbayer/dev/sqlalchemy/test/orm/test_eager_relations.py", line 433, in test_disable_dynamic
    sess.query(User).options(joinedload(User.addresses)).first()
  File "/home/mbayer/dev/sqlalchemy/test/../lib/sqlalchemy/orm/query.py", line 2447, in first
    ret = list(self[0:1])
  File "/home/mbayer/dev/sqlalchemy/test/../lib/sqlalchemy/orm/query.py", line 2283, in __getitem__
    return list(res)
  File "/home/mbayer/dev/sqlalchemy/test/../lib/sqlalchemy/orm/loading.py", line 50, in instances
    for query_entity in query._entities
  File "/home/mbayer/dev/sqlalchemy/test/../lib/sqlalchemy/orm/query.py", line 3280, in row_processor
    polymorphic_discriminator=self._polymorphic_discriminator
  File "/home/mbayer/dev/sqlalchemy/test/../lib/sqlalchemy/orm/loading.py", line 249, in instance_processor
    context, path, mapper, result, adapter, populators)
  File "/home/mbayer/dev/sqlalchemy/test/../lib/sqlalchemy/orm/interfaces.py", line 477, in create_row_processor
    mapper, result, adapter, populators)
  File "/home/mbayer/dev/sqlalchemy/test/../lib/sqlalchemy/orm/strategies.py", line 1442, in create_row_processor
    self
InvalidRequestError: 'User.addresses' does not support object population - eager loading cannot be applied.
```

in that test, if we just swallow the error, the cursor stays open, and pypy gets screwed up; even closing the connection it keeps the table locked.

[Sqlalchemy-tickets] Issue #3284: sqlalchemy-monetdb, python2 and unicode (zzzeek/sqlalchemy)

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

New issue 3284: sqlalchemy-monetdb, python2 and unicode
https://bitbucket.org/zzzeek/sqlalchemy/issue/3284/sqlalchemy-monetdb-python2-and-unicode

Gijs Molenaar:

Hi!

I'm about to release sqlalchemy-monetdb 0.9 but I have one final bug that I just don't can't manage to squash for a while now. I just don't understand where the problem lies, if it is a problem with sqlalchemy-monetdb (0.9) or python-monetdb.

problem is where you have a utf-8 encoded unicode string in a source file which is inserted into the database using sqlalchemy. Somehow the fetched results differ. Only happens with Python2, not python3. Probably it is a very stupid misunderstanding of how utf-8 encoded strings are handled somewhere. 

see test output here:

https://travis-ci.org/gijzelaerr/sqlalchemy-monetdb/jobs/44208224

If you don't have a simple suggestion then never mind, I probably need to dive into python2 and unicode handling again.

[Sqlalchemy-tickets] Issue #3283: strange behavior with MySQL empty SET (zzzeek/sqlalchemy)

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

New issue 3283: strange behavior with MySQL empty SET
https://bitbucket.org/zzzeek/sqlalchemy/issue/3283/strange-behavior-with-mysql-empty-set

yoch:

Hi all,

I want to use the MySQL SET type with sqlalchemy, but in case we have an empty set in database, sqlalchemy return an set with one element (the empty string).

Thank you

[Sqlalchemy-tickets] Issue #3282: enhance use_alter to be more forgiving, include an "auto" mode (zzzeek/sqlalchemy)

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

New issue 3282: enhance use_alter to be more forgiving, include an "auto" mode
https://bitbucket.org/zzzeek/sqlalchemy/issue/3282/enhance-use_alter-to-be-more-forgiving

Mike Bayer:

the case of a schema that's been reflected, or otherwise incurs dependencies, is too dependent on simplistic use_alter flags in order to do subsequent schema creates.  introduce an "auto" mode that figures this out more naturally at metadata create time, and just knows how to warn/error in the drop case if a name is missing.  allow use_alter to be meaningfully changed after the fact as well.

```
#!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)
    b_id = Column(ForeignKey('b.id'))
    bs = relationship("B")

class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))

from sqlalchemy.schema import AddConstraint, DropConstraint
from sqlalchemy import event


for table in Base.metadata.tables.values():

    for fkc in table.foreign_keys:
        # setting this here isn't good enough, because
        # we do the events as a fixed thing inside of set_parent().
        fkc.use_alter = True

        # we don't actually need a name for the CREATE part, just
        # the drop.
        fkc.constraint.name = table.name

        event.listen(Base.metadata, "after_create", AddConstraint(fkc.constraint))
        event.listen(Base.metadata, "before_drop", DropConstraint(fkc.constraint))

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

[Sqlalchemy-tickets] Issue #3281: aliased() not working with subquery (zzzeek/sqlalchemy)

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

New issue 3281: aliased() not working with subquery
https://bitbucket.org/zzzeek/sqlalchemy/issue/3281/aliased-not-working-with-subquery

Iurie Gheorghies:

    bla = literal_column('1', Integer)

    temp = s.query(bla.label('bla')).subquery()
    acc = aliased(Account, 'acc')
    s.query(acc, temp).join(temp, temp.c.bla == acc.id).all()

raises
AttributeError: 'str' object has no attribute 'corresponding_column'