full positional mapping w/ ORM / corresponding compat for TextAsFrom

[Mike Bayer]

tons of use cases for TextAsFrom that should be intuitive that don't work. When we make a TextAsFrom with a positional set of columns, those columns should be welded to it. The statement should be able to work in any ORM context flawlessly, no reliance on names matching up should be needed as we do not target on name anymore:

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)
    bs = relationship("B")


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

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

s = Session(e)
s.add_all([
    A(bs=[B(), B()]),
    A(bs=[B(), B()])
])
s.commit()

b1 = aliased(B)

# works
sql = "select a.id, ba.id as bid, ba.a_id from "\
    "a left outer join b as ba on a.id=ba.a_id"

# fails.  why?
# sql = "select a.id as aid, ba.id as bid, ba.a_id from "\
#    "a left outer join b as ba on a.id=ba.a_id"

# fails. why?
# sql = "select a.id as aid, ba.id, ba.a_id from "\
#    "a left outer join b as ba on a.id=ba.a_id"


# are we relying upon names somehow?  we should be able to
# be 100% positional now
t = text(sql).columns(A.id, b1.id, b1.a_id)

q = s.query(A).from_statement(t).options(contains_eager(A.bs, alias=b1))

for a in q:
    print a.id
    print a, a.bs

# forget about if we try a1 = aliased(A) also...

I've added docs in 7d268d4bcb5e6205d05ac and 4f51fa947ffa0cadeab7ad7dc that we may even have to dial back for versions that don't have this feature.

[Mike Bayer]

this patch does it. the controversial part is if users are using text().columns() with Column objects in an unordered way. need to decide if we should switch that here or add a new method. i think adding a new method is going to make this all even less intuitive.

[Mike Bayer]

Issue #3156 was marked as a duplicate of this issue.

[Mike Bayer]

initial version of the patch w/ many improvements is in b7f64dbef4eb3c375b7ff0664e7c246364a8afdb.

[Mike Bayer]

• A deep improvement to the recently added :meth:.TextClause.columns method, and its interaction with result-row processing, now allows the columns passed to the method to be positionally matched with the result columns in the statement, rather than matching on name alone. The advantage to this includes that when linking a textual SQL statement to an ORM or Core table model, no system of labeling or de-duping of common column names needs to occur, which also means there's no need to worry about how label names match to ORM columns and so-forth. In addition, the :class:.ResultProxy has been further enhanced to map column and string keys to a row with greater precision in some cases. fixes #3501
• reorganize the initialization of ResultMetaData for readability and complexity; use the name "cursor_description", define the task of "merging" cursor_description with compiled column information as its own function, and also define "name extraction" as a separate task.
• fully change the name we use in the "ambiguous column" error to be the actual name that was ambiguous, modify the C ext also

→ <<cset 1f7a1f777d8f>>