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

[Sqlalchemy-tickets] Issue #3668: problem in execute query string with error lazy loading (zzzeek/sqlalchemy)

[Ramin F. C. <iss...@bi...>]

New issue 3668: problem in execute query string with error lazy loading
https://bitbucket.org/zzzeek/sqlalchemy/issues/3668/problem-in-execute-query-string-with-error

Ramin Farajpour Cami:

Hi,

when use this query first i get valid result of query but again i refresh page site i get this error:
```
Parent instance is not bound to a Session; lazy load operation of attribute 'City' cannot proceed
```

this is a my query:


```
#!python

SELECT p.ID AS 'persons_ID', p.FirstName AS 'persons_FirstName', p.LastName AS 'persons_LastName',
                            p.NationalCode AS 'persons_NationalCode', p.CityID AS 'persons_CityID', p.Mobile AS 'persons_Mobile',
                            p.Address AS 'persons_Address', cities_1.ID AS 'cities_1_ID', cities_1.Name AS 'cities_1_Name',
                            cities_1.ParentID AS 'cities_1_ParentID', cities_2.ID AS 'cities_2_ID', cities_2.Name AS 'cities_2_Name',
                            cities_2.ParentID AS 'cities_2_ParentID' , cast(@row := @row + 1 as unsigned) as 'persons_row_number'

                    FROM Persons p LEFT OUTER JOIN cities AS cities_2 ON cities_2.ID = p.CityID
                    LEFT OUTER JOIN cities AS cities_1 ON cities_1.ID = cities_2.ParentID , (select @row := 0) as init
```
execute query :

```
#!python

 lst = ses.query(Persons).options(joinedload('City')).from_statement(query).all()
```

model :

```
#!python
class Persons(Base):


        __tablename__ = 'persons'

        ID = Column(Integer, primary_key=True)
        FirstName = Column(String(50))
        LastName = Column(String(100))
        NationalCode = Column(String(10))
        CityID = Column(Integer, ForeignKey('cities.ID'))
        Mobile = Column(String(10))
        Address = Column(String(10))

        City = relationship("Cities",lazy="joined",join_depth=2, uselist=False)

        CitiesModel = Cities()

        ProvinceID = None

        HashID = None
        row_number = None


```



Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3667: Support PostgreSQL's "WITH HOLD" cursor option (zzzeek/sqlalchemy)

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

New issue 3667: Support PostgreSQL's "WITH HOLD" cursor option
https://bitbucket.org/zzzeek/sqlalchemy/issues/3667/support-postgresqls-with-hold-cursor

Scott Milliken:

http://www.postgresql.org/docs/current/static/sql-declare.html

It's currently not possible to specify the cursor option "WITH HOLD". This option is useful when you'd like to commit between reads from a cursor.

In particular, in enables one to do this:

```
cur = session.query(Foo).yield_per(1)
for foo in cur:
    print(foo)
    # maybe do some writes..
    session.commit()
```

Currently, this causes an error:
```
Traceback (most recent call last):
  File "withold_test.py", line 9, in <module>
    for q in c:
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 86, in instances
    util.raise_from_cause(err)
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 63, in instances
    fetch = cursor.fetchmany(query._yield_per)
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 997, in fetchmany
    self.cursor, self.context)
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 990, in fetchmany
    l = self.process_rows(self._fetchmany_impl(size))
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 1149, in _fetchmany_impl
    row = self._fetchone_impl()
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 1139, in _fetchone_impl
    self.__buffer_rows()
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 1126, in __buffer_rows
    self.__rowbuffer = collections.deque(self.cursor.fetchmany(size))
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) named cursor isn't valid anymore
```

I attached a patch for your review. Example:

```
cur = session.query(Foo).yield_per(1, with_hold=True)
for foo in cur:
    print(foo)
    # maybe do some writes..
    session.commit()

```

Happy to submit a pull request if you're prefer.

As an aside, I'd also like to support the "SCROLL" and "NO SCROLL" options, but to be useful we'd need to add APIs to support the `direction` clause in `FETCH` and `MOVE` statements (http://www.postgresql.org/docs/current/static/plpgsql-cursors.html). Do you have an opinion about how you'd like to implement this, or should I just give it a shot?

[Sqlalchemy-tickets] Issue #3666: Insert of multiple values that contain an unknown column don't trigger an exception (zzzeek/sqlalchemy)

[Athena Y. <iss...@bi...>]

New issue 3666: Insert of multiple values that contain an unknown column don't trigger an exception
https://bitbucket.org/zzzeek/sqlalchemy/issues/3666/insert-of-multiple-values-that-contain-an

Athena Yao:

Inserting just one value triggers the CompileError 'Unconsumed column names' just fine.

Will provide patch shortly.

Test case attached. Before fix:

$ python2 unknown_columns.py
Expected fail (single value): CompileError('Unconsumed column names: unknown_column',)
Traceback (most recent call last):
  File "unknown_columns.py", line 30, in <module>
    raise RuntimeError("(multiple values): shouldn't hit here")
RuntimeError: (multiple values): shouldn't hit here

After fix:
$ python2 unknown_columns.py
Expected fail (single value): CompileError('Unconsumed column names: unknown_column',)
Expected fail (multiple values): CompileError('Unconsumed column names: unknown_column',)

[Sqlalchemy-tickets] Issue #3665: documentation language (zzzeek/sqlalchemy)

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

New issue 3665: documentation language
https://bitbucket.org/zzzeek/sqlalchemy/issues/3665/documentation-language

imbolc:

For me, as not english native speaker, the language of documentation looks much complicated in comparison with most other libs.

[Sqlalchemy-tickets] Issue #3664: un-GC'ed / un-configured mappers can be in _mapper_registry (zzzeek/sqlalchemy)

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

New issue 3664: un-GC'ed / un-configured mappers can be in _mapper_registry
https://bitbucket.org/zzzeek/sqlalchemy/issues/3664/un-gced-un-configured-mappers-can-be-in

Mike Bayer:

not sure how to detect that these mappers are defunct without just calling gc.collect(), which seems a little heavy handed:

```
#!python


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

Base = declarative_base()

atob = Table(
    'atob', Base.metadata,
    Column('aid', ForeignKey('a.id')),
    Column('bid', ForeignKey('b.id'))
)


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    bs = relationship("B", secondary=lambda: globals().get('atob'))


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

if True:
    del A
    del B
    del atob
    del Base

    Base = declarative_base()


class C(Base):
    __tablename__ = 'c'
    id = Column(Integer, primary_key=True)

configure_mappers()
```

output:

```
#!


  File "test2.py", line 37, in <module>
    configure_mappers()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line 2831, in configure_mappers
    mapper._post_configure_properties()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line 1756, in _post_configure_properties
    prop.init()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/interfaces.py", line 183, in init
    self.do_init()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/relationships.py", line 1634, in do_init
    self._setup_join_conditions()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/relationships.py", line 1709, in _setup_join_conditions
    can_be_synced_fn=self._columns_are_mapped
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/relationships.py", line 1978, in __init__
    self._determine_joins()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/relationships.py", line 2082, in _determine_joins
    "specify a 'primaryjoin' expression." % self.prop)
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship A.bs - there are no foreign keys linking these tables.  
```

specifically you'd see this kind of thing if those mappers are part of a module that was garbage collected, like an alembic module.

this patch resolves but gc.collect() doesn't behave the same across Python VMs:


```
#!diff

diff --git a/lib/sqlalchemy/orm/mapper.py b/lib/sqlalchemy/orm/mapper.py
index 2236b2f..06b2b81 100644
--- a/lib/sqlalchemy/orm/mapper.py
+++ b/lib/sqlalchemy/orm/mapper.py
@@ -2808,6 +2808,9 @@ def configure_mappers():
             if not Mapper._new_mappers:
                 return
 
+            import gc
+            gc.collect()
+
             Mapper.dispatch._for_class(Mapper).before_configured()
             # initialize properties on all mappers
             # note that _mapper_registry is unordered, which

```


there's no other indicator on the objects that they're no longer dereferenced since theyre in a cycle.

[Sqlalchemy-tickets] Issue #3663: column_property with func has static column alias (zzzeek/sqlalchemy)

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

New issue 3663: column_property with func has static column alias
https://bitbucket.org/zzzeek/sqlalchemy/issues/3663/column_property-with-func-has-static

Lukas Siemon:

I have an issue with column_property and using database specific functions. It seems like the alias is not created correctly in the query.

I'm obtaining data from a model and an alias of the model. When I use "func.hex" (or any "func" function), the column alias is named "hex" (or what the function is called) for both models and this causes a collision:
*InvalidRequestError: Ambiguous column name 'hex(CAST(label.id AS VARCHAR) || :param_1)' in result set! try 'use_labels' option on select statement.*

However, when I cast the column_property as a string (see commented section in test case) this works as expected. Is this a bug or a limitation? What would be the best way to work around this?

The test case and generated query below. I would have expected the returned columns to be named "hex_1" and "hex_2".


```
#!sql

SELECT
   hex(CAST(label.id AS VARCHAR) || :param_1) AS hex,
   hex(CAST(label_1.id AS VARCHAR) || :param_2) AS hex   
FROM
   label 
JOIN
   link 
      ON link.id = label.link_id 
JOIN
   label AS label_1 
      ON label_1.id = link.label_id
```


Test case:
```
#!python
import unittest
from sqlalchemy import (Column, Integer, String, func, ForeignKey, cast)
from sqlalchemy.orm import (column_property, relationship, aliased)
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy


class TestColumnPropertyStaticName(unittest.TestCase):

    def setUp(self):
        app = Flask(__name__)
        app.config['SQLALCHEMY_DATABASE_URI'] = ("sqlite://")
        db = SQLAlchemy(app)

        class Label(db.Model):
            __tablename__ = 'label'
            id = Column(Integer, primary_key=True)
            hex = column_property(func.hex(func.cast(id, String) + 'data'))
            # =============================
            # ====== this would work ======
            # hex = column_property(
            #     cast(func.hex(func.cast(id, String) + 'data'), String))
            # =============================
            link_id = Column(Integer, ForeignKey('link.id'))
            link = relationship("Link", foreign_keys=link_id)

        class Link(db.Model):
            __tablename__ = 'link'
            id = Column(Integer, primary_key=True)
            label_id = Column(Integer, ForeignKey('label.id'))
            label = relationship("Label", foreign_keys=label_id)

        db.drop_all()
        db.create_all()

        self.Label = Label
        self.Link = Link
        self.db = db

    def test_dynamic_bindparam(self):
        # saving the mirrored linkage
        label = self.Label()
        link = self.Link()
        label.link = link
        self.db.session.add(label)
        self.db.session.commit()
        link.label_id = label.id
        self.db.session.commit()

        # generate the query
        query = self.Label.query
        query = query.join(self.Link, self.Link.id == self.Label.link_id)
        alias = aliased(self.Label)
        query = query.join(alias, alias.id == self.Link.label_id)
        query = query.with_entities(self.Label.hex, alias.hex)
        print query

        data = query.one()
        print data

```

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3662: correlation fails with with_polymorphic selectables (zzzeek/sqlalchemy)

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

New issue 3662: correlation fails with with_polymorphic selectables
https://bitbucket.org/zzzeek/sqlalchemy/issues/3662/correlation-fails-with-with_polymorphic

Mike Bayer:

we probably need to get correlate to find the actual tables to correlate here.  two workarounds presented.  Also why doesn't query have correlate_except() ?!


```
#!python

from sqlalchemy import Column, Integer, ForeignKey, String, DateTime
from sqlalchemy import and_, func
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import arrow

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
Base = declarative_base()


class Parent(Base):
    __tablename__ = 'parent_objs'

    id = Column(Integer, primary_key=True)
    obj_type = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('user_objs.id'))
    time = Column(DateTime)

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


class Child(Parent):
    __tablename__ = 'child_objs'

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

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


class User(Base):
    __tablename__ = 'user_objs'

    id = Column(Integer, primary_key=True)


class UserEvent(Base):
    __tablename__ = 'user_events'

    id = Column(Integer, primary_key=True)
    time = Column(DateTime)
    user_id = Column(Integer, ForeignKey('user_objs.id'))


Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

session = Session()
twenty_hours_ago = arrow.utcnow().replace(hours=-20).naive
ten_hours_ago = arrow.utcnow().replace(hours=-10).naive
just_after_ten_hours_ago = arrow.utcnow().replace(hours=-10, seconds=+1).naive
five_hours_ago = arrow.utcnow().replace(hours=-5).naive
just_after_five_hours_ago = arrow.utcnow().replace(hours=-5, seconds=+1).naive
now = arrow.utcnow().naive


u = User(id=1)
session.add(u)
session.commit()

ue_1 = UserEvent(id=1, user_id=u.id, time=twenty_hours_ago)
ue_2 = UserEvent(id=2, user_id=u.id, time=ten_hours_ago)
ue_3 = UserEvent(id=3, user_id=u.id, time=five_hours_ago)
ue_4 = UserEvent(id=4, user_id=u.id, time=now)

child_1 = Child(id=1, time=just_after_ten_hours_ago, user_id=u.id)
child_2 = Child(id=2, time=just_after_five_hours_ago, user_id=u.id)


map(session.add, [
    ue_1, ue_2, ue_3, ue_4, child_1, child_2])
session.commit()


# failure one
q_with_declarative_one = (
    session.query(Parent.id, UserEvent.id)
    .join(User, Parent.user_id == User.id)
    .join(UserEvent, and_(
        UserEvent.user_id == User.id,
        UserEvent.time == session.query(
            func.max(UserEvent.time)
        ).filter(UserEvent.user_id == User.id)
         .filter(UserEvent.time <= Parent.time)
         .correlate(Parent).correlate(User)
         .as_scalar()
    ))
)

# workaround one
q_with_declarative_two = (
    session.query(Parent.id, UserEvent.id)
    .join(User, Parent.user_id == User.id)
    .join(UserEvent, and_(
        UserEvent.user_id == User.id,
        UserEvent.time == session.query(
            func.max(UserEvent.time)
        ).filter(UserEvent.user_id == User.id)
         .filter(UserEvent.time <= Parent.time)
         .as_scalar().correlate_except(UserEvent)
    ))
)

# workaround two
from sqlalchemy.orm import with_polymorphic
parent_poly = with_polymorphic(Parent, [], Parent.__table__)

q_with_declarative_three = (
    session.query(parent_poly.id, UserEvent.id)
    .join(User, parent_poly.user_id == User.id)
    .join(UserEvent, and_(
        UserEvent.user_id == User.id,
        UserEvent.time == session.query(
            func.max(UserEvent.time)
        ).filter(UserEvent.user_id == User.id)
         .filter(UserEvent.time <= parent_poly.time)
         .correlate(parent_poly).correlate(User)
         .as_scalar()
    ))
)


print q_with_declarative_one.all()
print q_with_declarative_two.all()
print q_with_declarative_three.all()
```

[Sqlalchemy-tickets] Issue #3661: Add join and outerjoin classmethods to declarative classes (zzzeek/sqlalchemy)

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

New issue 3661: Add join and outerjoin classmethods to declarative classes
https://bitbucket.org/zzzeek/sqlalchemy/issues/3661/add-join-and-outerjoin-classmethods-to

Haleemur Ali:

Given a database described via declarative syntax


```
#!python
from sqlalchemy import select, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

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

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer)
    street = Column(String)
    city = Column(String)
    postcode = Column(String)
    region = Column(String)
```
The following query written in sqlalchemy expression language is not valid:

```
#!python
stmt = select(
    [User.name, Address.city]
).select_from(User.join(Address, User.id == Address.user_id))
```
It raises `AttributeError: type object 'User' has no attribute 'join'`.

However, the same expression written using `User.__table__` is valid, i.e.

```
#!python
>>>str(User.__table__.join(Address, User.id == Address.user_id))
'users JOIN addresses ON users.id = addresses.user_id'
```
produces the desired join expression.

The `join` & `outerjoin` functions imported from `sqlalchemy.sql.expression` accept declarative classes as input and is able to produce the desired output

```
#!python
>>>from sqlalchemy.sql.expression import join, outerjoin
>>>str(join(User, Address, User.id == Address.user_id))
'users JOIN addresses ON users.id = addresses.user_id'
>>>str(outerjoin(User, Address, User.id == Address.user_id))
'users LEFT OUTER JOIN addresses ON users.id = addresses.user_id'
```

In my opinion this is a simple fix to add `join` & `outerjoin` functions as `classmethods` to declarative classes through the mapper. That way both classically declared mappings and modern declarative mappings will be consistently usable in sqlalchemy expression language.

I'd be happy to submit a PR for this change.

[Sqlalchemy-tickets] Issue #3660: to_list does not have a special case for `bytes` (zzzeek/sqlalchemy)

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

New issue 3660: to_list does not have a special case for `bytes`
https://bitbucket.org/zzzeek/sqlalchemy/issues/3660/to_list-does-not-have-a-special-case-for

Antti Haapala:

I'm using bytea (16 byte md5 digest) in PostgreSQL as a primary key. Unfortunately `to_list` which is used for example by `_get_impl` does not have a special case for `bytes` and thus `get()` complains about "Incorrect number of values in identifier to formulate primary key for query.get(); primary key columns are 'file.id'"

[Sqlalchemy-tickets] Issue #3659: 'ascii' codec can't decode byte 0xdb in position 942: ordinal not in range(128) SQLAlchemy (zzzeek/sqlalchemy)

[Ramin F. C. <iss...@bi...>]

New issue 3659: 'ascii' codec can't decode byte 0xdb in position 942: ordinal not in range(128) SQLAlchemy
https://bitbucket.org/zzzeek/sqlalchemy/issues/3659/ascii-codec-cant-decode-byte-0xdb-in

Ramin Farajpour Cami:

 I use some Arabic script in my query and Python can't decode Arabic characters to ASCII and Python 2 uses bytestrings (ASCII) strings by default, which support only Latin characters. Python 3 uses Unicode strings by default.

query :

```
#!python

query = """SELECT * FROM (
               SELECT p.ID AS 'persons_ID', 
                 p.FirstName AS 'persons_FirstName', 
                 p.LastName AS 'persons_LastName',
                 p.NationalCode AS 'persons_NationalCode', 
                 p.CityID AS 'persons_CityID', 
                 p.Mobile AS 'persons_Mobile',
                 p.Address AS 'persons_Address', 
                 cities_1.ID AS 'cities_1_ID', 
                 cities_1.Name AS 'cities_1_Name',
                 cities_1.ParentID AS 'cities_1_ParentID', 
                 cities_2.ID AS 'cities_2_ID', 
                 cities_2.Name AS 'cities_2_Name',
                 cities_2.ParentID AS 'cities_2_ParentID' , 
                 cast(@row := @row + 1 as unsigned) as 'persons_row_number'                                                                              
               FROM Persons p 
               LEFT OUTER JOIN cities AS cities_2 
               ON cities_2.ID = p.CityID 
               LEFT OUTER JOIN cities AS cities_1 
               ON cities_1.ID = cities_2.ParentID , (
                 select @row := 0) as init WHERE 1=1
                 AND p.FirstName LIKE N'{}%'""".format('رامین')
```
i use format('رامین') i get error UnicodeEncodeError from MysqlDB,


Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3658: orm load when col is missing fails to report correct exception (zzzeek/sqlalchemy)

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

New issue 3658: orm load when col is missing fails to report correct exception
https://bitbucket.org/zzzeek/sqlalchemy/issues/3658/orm-load-when-col-is-missing-fails-to

Mike Bayer:

1.0 regression, works in 0.9:


```
#!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)
    x = Column(Integer)

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

s = Session(e)
s.query(A.id, A.x).from_statement("select 1").all()
```

in 0.9:

    sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'a.id'"

in 1.0:

     File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/loading.py", line 74, in instances
     for row in fetch]
    TypeError: 'NoneType' object is not callable

[Sqlalchemy-tickets] Issue #3657: positional result column logic failing (zzzeek/sqlalchemy)

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

New issue 3657: positional result column logic failing
https://bitbucket.org/zzzeek/sqlalchemy/issues/3657/positional-result-column-logic-failing

Mike Bayer:

Havent fully tracked this down yet, suspect the query wrapping in mssql for LIMIT/OFFSET is causing this to happen.

very heisenbuggy, set PYTHONHASHEED=random and run lots of times against mssql:


```
#!python

import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base

#engine = sa.create_engine('mssql+pymssql://badams:password@192.168.56.101:1443/testdb', echo=True)

engine = sa.create_engine('mssql+pymssql://scott:tiger@192.168.122.232:1213/test', echo=True)

session = saorm.sessionmaker(bind=engine)()

Base = declarative_base()

class Person(Base):
    __tablename__ = 'people'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)

Base.metadata.create_all(engine)

session.query(Person).delete()

session.add(Person(name='foo'))
session.add(Person(name='bar'))

session.commit()

results = session.query(
    Person.name.label('person'),
).add_entity(
    Person
).order_by(
    Person.name
)

print results.count()
print results.limit(1).offset(1).all()


```

the stack is a lie; to really make this fail we have to raise here (and this is another issue for 1.1, why aren't we raising here? )

```
#!diff


--- a/lib/sqlalchemy/engine/result.py
+++ b/lib/sqlalchemy/engine/result.py
@@ -420,7 +427,8 @@ class ResultMetaData(object):
         if key in self._keymap:
             processor, obj, index = self._keymap[key]
         else:
-            ret = self._key_fallback(key, False)
+            # MARKMARK
+            ret = self._key_fallback(key) #, False)
             if ret is None:
                 return None
             processor, obj, index = ret

```


so ultimately the compiler._result_columns is wrong.  on a bad run its:


```
#!python

[

('person', 'person', (Column('name', String(), table=<people>), 'person', 'person'), String()), 
('people_id', 'people_id', (Column('id', Integer(), table=<people>, primary_key=True, nullable=False), 'people_id', 'people_id'), Integer()), ('people_name', 'people_name', (Column('name', String(), table=<people>), 'people_name', 'people_name'), String())

]

```

on a good run it's:

```
#!python


[

('person', 'person', (<sqlalchemy.sql.elements.Label object at 0x7f46cc3dcf10>, 'person', 'person'), String()), 
('people_id', 'people_id', (Column('id', Integer(), table=<people>, primary_key=True, nullable=False), 'people_id', 'people_id'), Integer()), ('people_name', 'people_name', (Column('name', String(), table=<people>), 'people_name', 'people_name'), String())
]

```

so our label is occasionally getting whacked from the result list, and its a dictionary ordering issue.   the LIMIT wrapping in mssql/base.py -> visit_select seems like it may be significant also.  

[Sqlalchemy-tickets] Issue #3656: Firebird dialect doesn't quote column names properly (zzzeek/sqlalchemy)

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

New issue 3656: Firebird dialect doesn't quote column names properly
https://bitbucket.org/zzzeek/sqlalchemy/issues/3656/firebird-dialect-doesnt-quote-column-names

zoomorph:

class Studio(Base):

    __tablename__ = 'studios'

    sid = Column(Integer, primary_key=True)
    value = Column(String, nullable=False)


SQLAlchemy generates "SELECT studios.sid, studios."value" FROM studios;"

This doesn't work. It appears (first time using Firebird so not too sure) that all column names must be quoted, otherwise I get an "unknown column" error.

I've temporarily worked around it by adding force=True arg to sql\compiler.py line 645  (and modifying line 2704 to actually use this argument).

[Sqlalchemy-tickets] Issue #3655: BinaryExpression wrong type (datetime instead of date) (zzzeek/sqlalchemy)

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

New issue 3655: BinaryExpression wrong type (datetime instead of date)
https://bitbucket.org/zzzeek/sqlalchemy/issues/3655/binaryexpression-wrong-type-datetime

Tom Kedem:

Suppose I have the following arrangement:
```
class MyModel(Base):
    date = Column(Date)

    @hybrid_property
    def offset_date(self):
        return self.date + datetime.timedelta(days=1)
```

The `offset_date` type is `datetime` instead of `date`. I would expect the result of the `BinaryExpression` to evaluate to the same type of the column. This is in accordance to how python evaluates the addition:
```
>>> type(datetime.date.today() + datetime.timedelta(days=1))
<class 'datetime.date'>
```

I'm using the latest master to date.

[Sqlalchemy-tickets] Issue #3654: inspectable Query (for relationships) (zzzeek/sqlalchemy)

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

New issue 3654: inspectable Query (for relationships)
https://bitbucket.org/zzzeek/sqlalchemy/issues/3654/inspectable-query-for-relationships

jvanasco:

There doesn't seem to be a (public) way to find out if a path has been registered to a query for eager loading.  (A non-public way seems to be `query.__dict__['with_options']`)

I propose an enhancement to the Query API, which allows a query to be queried for the status of eager loads.

In the simplest concept, it would just return a dict where the keys are a stringified path, and the values are the type of load (joinedload vs subqueryload vs contains_eager).  If there is no eager loading for a given relationship, then the key is absent.

    print q.registered_eagers()
    > {'foo': 'joinedload', 'foo.bar':' subqueryload', 'foo.biz': 'contains_eager'}

The use-case is when dealing with generative queries that are loading relationships several layers deep; it becomes increasingly hard to ensure you have a load-strategy for each intermediary relationship.. 

[Sqlalchemy-tickets] Issue #3653: Support docstrings on hybrid attributes (zzzeek/sqlalchemy)

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

New issue 3653: Support docstrings on hybrid attributes
https://bitbucket.org/zzzeek/sqlalchemy/issues/3653/support-docstrings-on-hybrid-attributes

David Baumgold:

Currently, it appears that there is no way to set a docstring on a hybrid attribute: even if there is a docstring on the function, the `@hybrid_property` and `@hybrid_method` decorators don't copy it over to the objects they return. There should be way to set docstrings on hybrid attributes.

My ultimate goal is to get [Sphinx's](http://www.sphinx-doc.org/) [autodoc](http://www.sphinx-doc.org/en/stable/ext/autodoc.html) extension working properly with hybrid attributes, so that I can write RST-formatted docstrings for the hybrid attributes of my data models, and generate beautiful documentation from those docstrings.

I started putting together a fix for this bug in [a GitHub pull request](https://github.com/zzzeek/sqlalchemy/pull/238), but the problem turned out to be more complicated than I expected.

[Sqlalchemy-tickets] Issue #3652: Clarify unnest example in FunctionElement.alias documentation (zzzeek/sqlalchemy)

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

New issue 3652: Clarify unnest example in FunctionElement.alias documentation
https://bitbucket.org/zzzeek/sqlalchemy/issues/3652/clarify-unnest-example-in

Sebastian Bank:

Using `func.unnest` to unpack a postgres array:

```python
from sqlalchemy import Column, Integer, Text, select, column, func
from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy.ext.declarative import declarative_base

class Post(declarative_base()):

    __tablename__ = 'post'

    id = Column(Integer, primary_key=True)
    title = Column(Text, nullable=False)
    tags = Column(ARRAY(Text), nullable=False)

query = select([Post.id, column('tag')])\
    .select_from(func.unnest(Post.tags).alias('tag'))

print(query)
```

As wanted, this gives `SELECT post.id, tag FROM post, unnest(post.tags) AS tag` (which is postgres-specific syntax, I guess).

I found the example given in [the documentation of FunctionElement.alias](https://bitbucket.org/zzzeek/sqlalchemy/src/366f97b5617af0d15cfaf594ec5ef0408c70e873/lib/sqlalchemy/sql/functions.py?at=master&fileviewer=file-view-default#functions.py-198) somewhat confusing because the SELECT-clause does not actually use the unpacked data:

```python
stmt = select([column('data')]).select_from(
    func.unnest(Table.data).alias('data_view'))
```

Wouldn't it make more sense like this (following the example above)?

```python
stmt = select([column('item')]).select_from(
    func.unnest(Table.data).alias('item'))
```

Or maybe this is not the right use of `FunctionElement.alias`?

[Sqlalchemy-tickets] Issue #3651: Got "AttributeError: uselist" when using association proxy and deriving ConcreteBase (zzzeek/sqlalchemy)

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

New issue 3651: Got "AttributeError: uselist" when using association proxy and deriving ConcreteBase
https://bitbucket.org/zzzeek/sqlalchemy/issues/3651/got-attributeerror-uselist-when-using

Brian Peterson:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "<string>", line 4, in __init__
  File "/home/brian/.virtualenvs/flask-permissions-ex/lib/python3.4/site-packages/sqlalchemy/orm/state.py", line 306, in _initialize_instance
    manager.dispatch.init_failure(self, args, kwargs)
  File "/home/brian/.virtualenvs/flask-permissions-ex/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/brian/.virtualenvs/flask-permissions-ex/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 184, in reraise
    raise value
  File "/home/brian/.virtualenvs/flask-permissions-ex/lib/python3.4/site-packages/sqlalchemy/orm/state.py", line 303, in _initialize_instance
    return manager.original_init(*mixed[1:], **kwargs)
  File "/home/brian/code/pwm/flask-permissions-ex/ex/database.py", line 28, in __init__
    UserMixin.__init__(self, roles)
  File "<string>", line 6, in __init__
  File "/home/brian/code/pwm/flask-permissions/flask_permissions/models.py", line 125, in __init__
    self.roles = [default_role]
  File "/home/brian/.virtualenvs/flask-permissions-ex/lib/python3.4/site-packages/sqlalchemy/ext/associationproxy.py", line 272, in __set__
    if self.scalar:
  File "/home/brian/.virtualenvs/flask-permissions-ex/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 754, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/home/brian/.virtualenvs/flask-permissions-ex/lib/python3.4/site-packages/sqlalchemy/ext/associationproxy.py", line 232, in scalar
    scalar = not self._get_property().uselist
  File "/home/brian/.virtualenvs/flask-permissions-ex/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 840, in __getattr__
    return self._fallback_getattr(key)
  File "/home/brian/.virtualenvs/flask-permissions-ex/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 818, in _fallback_getattr
    raise AttributeError(key)
AttributeError: uselist

[Sqlalchemy-tickets] Issue #3650: limit/offset used with a subqueryload creates unpredictable results (zzzeek/sqlalchemy)

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

New issue 3650: limit/offset used with a subqueryload creates unpredictable results
https://bitbucket.org/zzzeek/sqlalchemy/issues/3650/limit-offset-used-with-a-subqueryload

jvanasco:

This affects `subqueryload`, not `joinedload`.

It is related to https://bitbucket.org/zzzeek/sqlalchemy/issues/3064/warn-when-limit-offset-used-w-o-order-by

Assume a simple relationship of Foo to Bar:

    class Bar(Base):
        __tablename__ = 'bar'
        id = Column(Integer, primary_key=True)

    class Foo(Base):
        __tablename__ = 'foo'
        id = Column(Integer, primary_key=True)
        id_bar = Column(Integer, ForeignKey("bar.id"), nullable=False)
    
        bar = sqlalchemy.orm.relationship("Bar")

The following query will yield unpredictable results for the 'bar' relationship, even when an id_bar is present:

    foos = s.query(Foo)\
            .options(subqueryload('bar'))\
            .limit(20)\
            .offset(10)\
            .all()

The issue is that the subqueryload logic generates a selection like such (reformatted for emphasis):

         SELECT bar.* FROM
     (SELECT DISTINCT foo.id_bar AS foo_id_bar FROM foo LIMIT :limit OFFSET :offset )
          as anon_1 JOIN bar on bar.id = anon_1.foo_id_bar ORDER BY anon_1.foo_id_bar;

Because that inner select for the subquery is performing a DISTINCT, the LIMIT and OFFSET apply to the 'distinct' -- not overall -- list.

one example scenario that will fail: the offset is greater than the total number of possible values.  if every foo is assigned an id_bar of only 2 values (1, 2), paginating an offset of 2 of greater will always return NULL and no `bar` relation will be set.  

    # 1. make a bunch of Bars.  Let's do 20!
    for i in range(0, 20):
        b = Bar(id=i)
        s.add(b)
        s.flush()
        s.commit()

    # 2, okay, now make 100 foos... but only use 2 `bars`.
    for i in range(0, 100):
        if i < 50:
            id_bar = 1
        else:
            id_bar = 2
        f = Foo(id=i, id_bar=id_bar)
        s.add(f)
        f.id
        s.flush()
        s.commit()

    # run the above select , and see the results:
    for f in foos:
        print "--"
        print "examing foo.%s" % f.id
        print f.id_bar
        print f.bar

    # you'll notice that `bar` will be missing even when id_bar is set

another example scenario is when you have a lot of repetition within the dataset.  e.g. if there are fewer unique BARs than FOOs, you'll encounter errors when you paginate closer to the end.    i haven't been able to recreate a reliable dataset for this, but it happens in my production dataset when I paginate 80% through.

Then to complicate further, this sort of becomes the other issue -- there is no implicit `order_by` on the inner query unless an `order_by` is explicitly applied to the outer query.  

`order_by(Foo.id.asc())` will create this inner query:

 	(SELECT DISTINCT foo.id_bar AS foo_id_bar, foo.id AS foo_id 
	 FROM foo ORDER BY foo.id ASC
	 LIMIT ? OFFSET ?)

however, no order_by will result in the inner query

	(SELECT DISTINCT foo.id_bar AS foo_id_bar 
	 FROM foo
	 LIMIT ? OFFSET ?)

there is no reason for the db to guarantee these results will be for the same ids returned in the previous query (the non-subqueryload 'master' query that sqlalchemy just issued).

The other ticket talked about a more specific use-case with depth, but I'll address the larger and less detailed concept - in the absence of an explicit order_by, sqlalchemy appears to issue an implicit order_by on the primary key -- however does not extend that implicit order_by to the inner query.

the fixes I suggest are:

1. migrate this into 2 queries -- an inner that paginates and an outer that applies distinct (or group_by, which can be much faster for this in certain scenarios)
    - SELECT DISTINCT foo.id_bar AS foo_id_bar FROM foo LIMIT :limit OFFSET :offset
    + SELECT DISTINCT foo_id_bar FROM (SELECT foo.id_bar AS foo_id_bar FROM foo LIMIT :limit OFFSET :offset) AS foo

2. in the absence of no order_by issued with a subquery, just apply the same implicit primary-key ordering to the inner query that was used on the outer query.

[Sqlalchemy-tickets] Issue #3649: Tests fail with Python built with newer sqlite (zzzeek/sqlalchemy)

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

New issue 3649: Tests fail with Python built with newer sqlite
https://bitbucket.org/zzzeek/sqlalchemy/issues/3649/tests-fail-with-python-built-with-newer

cwebber:

Python built with sqlite 3.8.11.1 and before seems to be working fine.  Building with newer versions of sqlite, such as 3.10.0 or 3.10.2 results in failing tests:

=================================== FAILURES ===================================
__________ QueryTest_sqlite_pysqlite.test_column_accessor_sqlite_raw ___________
Traceback (most recent call last):
  File "<string>", line 2, in test_column_accessor_sqlite_raw
  File "/tmp/guix-build-python-sqlalchemy-0.9.7.drv-0/SQLAlchemy-0.9.7/test/../lib/sqlalchemy/testing/exclusions.py", line 72, in decorate
    return fn(*args, **kw)
  File "/tmp/guix-build-python-sqlalchemy-0.9.7.drv-0/SQLAlchemy-0.9.7/test/sql/test_query.py", line 898, in test_column_accessor_sqlite_raw
    assert 'user_id' not in r
AssertionError: assert 'user_id' not in (1, 'john')
_______ QueryTest_sqlite_pysqlite.test_column_accessor_sqlite_translated _______
Traceback (most recent call last):
  File "/tmp/guix-build-python-sqlalchemy-0.9.7.drv-0/SQLAlchemy-0.9.7/test/../lib/sqlalchemy/engine/result.py", line 70, in __getitem__
    processor, obj, index = self._keymap[key]
KeyError: 'query_users.user_id'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<string>", line 2, in test_column_accessor_sqlite_translated
  File "/tmp/guix-build-python-sqlalchemy-0.9.7.drv-0/SQLAlchemy-0.9.7/test/../lib/sqlalchemy/testing/exclusions.py", line 72, in decorate
    return fn(*args, **kw)
  File "/tmp/guix-build-python-sqlalchemy-0.9.7.drv-0/SQLAlchemy-0.9.7/test/sql/test_query.py", line 918, in test_column_accessor_sqlite_translated
    eq_(r['query_users.user_id'], 1)
  File "/tmp/guix-build-python-sqlalchemy-0.9.7.drv-0/SQLAlchemy-0.9.7/test/../lib/sqlalchemy/engine/result.py", line 72, in __getitem__
    processor, obj, index = self._parent._key_fallback(key)
  File "/tmp/guix-build-python-sqlalchemy-0.9.7.drv-0/SQLAlchemy-0.9.7/test/../lib/sqlalchemy/engine/result.py", line 331, in _key_fallback
    expression._string_or_unprintable(key))
sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'query_users.user_id'"
=========================== short test summary info ============================
FAIL test/sql/test_query.py::QueryTest_sqlite_pysqlite::()::test_column_accessor_sqlite_raw
FAIL test/sql/test_query.py::QueryTest_sqlite_pysqlite::()::test_column_accessor_sqlite_translated
============= 2 failed, 5803 passed, 737 skipped in 237.59 seconds =============
phase `check' failed after 241.9 seconds

[Sqlalchemy-tickets] Issue #3648: Documentation: error in SQL Expression Language tutorial(?) (zzzeek/sqlalchemy)

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

New issue 3648: Documentation: error in SQL Expression Language tutorial(?)
https://bitbucket.org/zzzeek/sqlalchemy/issues/3648/documentation-error-in-sql-expression

Joakim Nybäck:

Hi,
I followed the SQLAlchemy SQL Expression Language tutorial from the beginning. I added the code as I went along, including the code for creating the two tables in the section "**Define and Create Tables**" (section 3.1.3 in the PDF version; the PDF is for Release 1.1.0b1 and dated 13-FEB-2016). When I execute the code an exception is thrown:

"UnboundExecutionError: MetaData object is not bound to an Engine or Connection.  Execution can not proceed without a database to execute against."

The code listed in the tutorial includes this line:


```
#!python

metadata = MetaData()
```

If I change that line to:


```
#!python

metadata = MetaData(bind=engine)
```


then the code works fine.

[Sqlalchemy-tickets] Issue #3647: Merge fails when Object has had a new child object created in a one-to -many situation (zzzeek/sqlalchemy)

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

New issue 3647: Merge fails when Object has had a new child object created in a one-to -many situation
https://bitbucket.org/zzzeek/sqlalchemy/issues/3647/merge-fails-when-object-has-had-a-new

Mark Paine:

Model example:

```
#!python

class Element(Base):
    __tablename__ = 'Element'
    # Primary Key(s)
    id = Column(Integer, primary_key=True)
    properties = relationship("ElementProperty", backref="Element")

class ElementProperty(Base):
    __tablename__ = 'ElementProperty'
    # Primary Key(s)
```


Code example:

```
#!python
    def working(self):
        e = sqldb_ems.Element(id=1)
        ep = sqldb_ems.ElementProperty(name='test')
        e.properties.append(ep)
        session.add(e)
        session.commit()

    def not_working(self):
        e = sqldb_ems.Element(id=1)
        ep = sqldb_ems.ElementProperty(name='test')
        e.properties.append(ep)
        session.merge(e)
        session.commit()
```


The code which does not work, fails during a select statement looking at the child object table. The select statement looks like this:

SELECT [ElementProperty].name AS [ElementProperty_name], [ElementProperty].[element_Id] AS [ElementProperty_element_Id], [ElementProperty].type AS [ElementProperty_type], [ElementProperty].value AS [ElementProperty_value] 
FROM [ElementProperty] 
WHERE [ElementProperty].name = %(param_1)s AND [ElementProperty].[element_Id] = %(param_2)s
2016-02-12 12:41:05,073 INFO sqlalchemy.engine.base.Engine {'param_1': 'test', 'param_2': symbol('NEVER_SET')}


The param_2 is not being set. This should be the foreign key.

[Sqlalchemy-tickets] Issue #3646: MutableDict creates only on first level of JSON (zzzeek/sqlalchemy)

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

New issue 3646: MutableDict creates only on first level of JSON
https://bitbucket.org/zzzeek/sqlalchemy/issues/3646/mutabledict-creates-only-on-first-level-of

Valentin Kaplov:

If we associate MutableDict with JSON data, then MutableDict accociates with first level elements only. As result if we have on second and/or next levels mutable objects (like list, dict) and we modify them, then MutableDict will not marked as dirty/changed and modification will not take effect to database. Thus we'll lose consistency.
Example.
We have next JSON data from database:

```
#!python
{
    "key1": {"key1.2": "value1.2"},
    "key2": ["value2.1", "value2.2"],
    "key3": "value3",
}
```
If we pass the data to MutableDict, then it will create next object tree:

**Actual result:**
```
#!python

m_dict = MutableDict({
    "key1": dict({"key1.2": "value1.2"}),    # value builtin dict
    "key2": list(["value2.1", "value2.2"]),  # value builtin list
    "key3": "value3"
})

m_dict['key1']['key1.2'] = 'new_value'   # m_dict doesn't marked as changed
m_dict['key1'][0] = 'new_value'          # m_dict doesn't marked as changed
```


**Expected result:**
```
#!python

MutableDict({
    "key1": MutableDict({"key1.2": "value1.2"})      # value MutableDict asocciated with JSON
    "key2": MutableList(["value2.1", "value2.2"]),   # value MutableDict asocciated with JSON
    "key3": "value3"
})

m_dict['key1']['key1.2'] = 'new_value'   # m_dict marked as changed
m_dict['key1'][0] = 'new_value'          # m_dict marked as changed
```

**Additional information:**
To fix the problem needs to have implemented MutableList https://bitbucket.org/zzzeek/sqlalchemy/issues/3297

Need to change all builtin mutables in JSON on corresponding associated Mutables from sqlalchemy. BTW Mirantis has already implemented wrappers that fix the bug. It can be usefull https://github.com/openstack/fuel-web/blob/master/nailgun/nailgun/db/sqlalchemy/models/mutable.py

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3645: UPDATE on a Join creates duplicate tables (zzzeek/sqlalchemy)

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

New issue 3645: UPDATE on a Join creates duplicate tables
https://bitbucket.org/zzzeek/sqlalchemy/issues/3645/update-on-a-join-creates-duplicate-tables

immerrr:

It is probably unexpected that it actually works, but MySQL can do that (and StackOverflow suggests that MS SQL can, too). Here's a script to reproduce:

```
import sqlalchemy as sa

metadata = sa.MetaData()


t1 = sa.Table(
    't1', metadata,
    sa.Column('key1', sa.Integer),
    sa.Column('ref1', sa.Integer),
    sa.Column('val1', sa.Integer),
    sa.ForeignKeyConstraint(['ref1'], ['t2.key2']),
)


t2 = sa.Table(
    't2', metadata,
    sa.Column('key2', sa.Integer),
    sa.Column('val2', sa.Integer),
)

engine = sa.create_engine('mysql+mysqlconnector://')


def to_str(stmt):
    return str(stmt.compile(bind=engine,
                            compile_kwargs={'literal_binds': True}))
```

In the end I see
```
>>> to_str(
...     sa.update(t1.join(t2),
...               values={t1.c.val1: 'foo'},
...               whereclause=(t2.c.val2 == 'foobar')))
...
'UPDATE t1 INNER JOIN t2 ON t2.key2 = t1.ref1, t2 SET t1.val1=%(val1)s WHERE t2.val2 = %(val2_1)s'
```

Note, that there's a second `t2` table in the *table_references* part of the query.

This patch seems to have fixed my case, but I can't seem to run tests out of the box, so I don't know if it broke anything else:
```
index 7b506f9..fe9f20b 100644
--- a/lib/sqlalchemy/sql/dml.py
+++ b/lib/sqlalchemy/sql/dml.py
@@ -766,7 +766,7 @@ class Update(ValuesBase):
         # TODO: this could be made memoized
         # if the memoization is reset on each generative call.
         froms = []
-        seen = set([self.table])
+        seen = set(_from_objects(self.table))
 
         if self._whereclause is not None:
             for item in _from_objects(self._whereclause):
```

[Sqlalchemy-tickets] Issue #3644: text() colon-escaping behaviour (zzzeek/sqlalchemy)

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

New issue 3644: text() colon-escaping behaviour
https://bitbucket.org/zzzeek/sqlalchemy/issues/3644/text-colon-escaping-behaviour

Sebastian Bank:

Using the postgres double colon shortcut for `CAST(expression AS type)`, e.g.:
```python
import sqlalchemy as sa
engine = sa.create_engine('postgresql://postgres@/')
engine.execute('SELECT * FROM pg_attribute WHERE attrelid = %(tab)s::regclass', tab='pg_class')
```

Trying this with `text`, I think the [docs](http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.text) would suggest using `\:`s:
```python
engine.execute(sa.text('SELECT * FROM pg_attribute WHERE attrelid = :tab\:\:regclass'), tab='pg_class')
```
But this raises `ProgrammingError: (psycopg2.ProgrammingError)` (rendered as `SELECT * FROM pg_attribute WHERE attrelid = %(tab)s\::regclass`).

As expected, this also raises:
```python
engine.execute(sa.text('SELECT * FROM pg_attribute WHERE attrelid = :tab::regclass'), tab='pg_class')
```
(rendered as `SELECT * FROM pg_attribute WHERE attrelid = :tab::regclass`)

This finally works (extra space): 
```python
engine.execute(sa.text('SELECT * FROM pg_attribute WHERE attrelid = :tab ::regclass'), tab='pg_class')
```
But is this the intended way?