Re: [sqlalchemy] from_statement, TextAsFrom and stored procedures
On Wed, Feb 5, 2014 at 7:28 PM, Michael Bayer mike...@zzzcomputing.comwrote: OK great, added some more rules in 5c188f6c1ce85eaace27f052. Awesome, thanks! My tests all passed on my end. As far as “names line up with the result set names”, I’m not sure what you mean there, the .columns() method is always matching up names. With that checkin, all the tests in your sample suite pass, so feel free to give it a check, I’d like to get this totally right for when 0.9.3 comes out. Gotcha: I thought that even querying a plain text() object would give you the right ORM objects back as long as the columns were in the right positional order. Looks like that's not the case, which is probably for the best; I think the more liberal behavior would have a large risk of causing silent bugs. As for *why* I thought that: I didn't realize until just now that ORM is designed to handle labels when they're in the specific form tablename_columnname. That's why I thought a text query with result set names in that form was being mapped by position, because I didn't know ORM was smart enough to find columns by name in that form :) I wrote one more test that failed (but I'm pretty sure it doesn't matter): I was under the impression that passing Label objects to .columns() would allow you to map *arbitrary* result set column names to ORM attributes, and that seems to not be the case (and was never the case, AFAIK). That kind of mapping would be cool, and might not even be that hard since the columns in the RowProxy ._keymap values seem to have the original ORM columns in their .proxy_sets. That said, the only reason I can think of for someone to try that is if they did something truly nuts like a join with two columns with the same name from two tables which *also* have the same name, from two different schemas, with a stored procedure, into ORM. As long as the tablename_columname form works, I think our use case is covered, so feel free to say wontfix. But if you're interested, I added the new test to my suite: https://gist.github.com/garaden/8835587 I hope I'm not harassing you too much about the TextAsFrom feature! I feel like if I asked any other ORM to be this flexible they would either laugh or cry. SQLAlchemy is the first ORM I've worked with since using Rails as an intern, and I'm spoiled now with how awesome it is :) -Matt -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] from_statement, TextAsFrom and stored procedures
Sounds great; I agree avoiding the naming convention is ideal. For my project the only reason we're using a text clause is to call a stored procedure, which definitely can't go in a subquery, so I'm not sure how well I can weigh in on the aliasing stuff. -Matt On Fri, Feb 7, 2014 at 1:43 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Feb 7, 2014, at 1:00 PM, Matt Phipps matt.the.m...@gmail.com wrote: I wrote one more test that failed (but I'm pretty sure it doesn't matter): I was under the impression that passing Label objects to .columns() would allow you to map *arbitrary* result set column names to ORM attributes, and that seems to not be the case (and was never the case, AFAIK). That kind of mapping would be cool, and might not even be that hard since the columns in the RowProxy ._keymap values seem to have the original ORM columns in their .proxy_sets. yeah I thought this would work but it requires a proxy_set change, which I’d like to make but has me nervous: class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) data = Column(String) result = sess.query(A).from_statement( text(SELECT id AS x, data AS y FROM a). columns(A.id.label(x), A.data.label(y)) ).all() I’ve added two different patches to http://www.sqlalchemy.org/trac/ticket/2932#comment:5 which is reopened. both patches work but i think the second one is more of the right idea. it works like this too but this renders the subquery, something else to think about maybe: A1 = aliased(text(SELECT id AS x, data AS y FROM a).columns(A.id.label(x), A.data.label(y))) result = sess.query(A1).all() as does this: stmt = text(SELECT id AS x, data AS y FROM a).columns(A.id.label(x), A.data.label(y)) result = sess.query(A).select_entity_from(stmt).all() That said, the only reason I can think of for someone to try that is if they did something truly nuts like a join with two columns with the same name from two tables which *also* have the same name, from two different schemas, with a stored procedure, into ORM. well I really hate enforced naming conventions so making this work would be a breakthrough way of finally getting over that, I like it. I think this can be done. also, the change greatly increases performance as the lookup in ResultProxy doesn’t need a KeyError now. So I really want to try to make it work. I’m just trying to think of, what are the implications if the text() is then transformed into an alias() and such, but I think it might be consistent with how a Table acts right now. I think its cool: stmt = select([A.id, A.data]) result = sess.query(A).from_statement(stmt).all() # works stmt = select([A.id, A.data]).alias().select() result = sess.query(A).from_statement(stmt).all() # you get the same column error I hope I'm not harassing you too much about the TextAsFrom feature! I feel like if I asked any other ORM to be this flexible they would either laugh or cry. SQLAlchemy is the first ORM I've worked with since using Rails as an intern, and I'm spoiled now with how awesome it is :) its great, this feature is going to be much better and important than how it started a few months ago. I’ve added a lot of new thoughts to that ticket. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] from_statement, TextAsFrom and stored procedures
I've been investigating this a little further and think I found some other
issues. Our data team changed the stored procedure to stop aliasing the
column names, so passing the mapped columns right into .columns() is
working (in other words, the rest of this post doesn't reflect my use case
anymore :)).
However, labels no longer work as arguments to .columns() unless I go back
to 0.9.1 logic by setting ._textual=False and .use_labels = True. Also,
passing keyword arguments to .columns() only works if the names line up
with the result set names, i.e. using the position as a key seems to be
disabled for TextAsFrom objects.
Here's a gist of the nose test suite that helped me figure out what was
working and what wasn't: https://gist.github.com/garaden/8835587
On Sun, Feb 2, 2014 at 5:42 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On Feb 2, 2014, at 4:31 PM, Matt Phipps matt.the.m...@gmail.com wrote:
def _trackable_truckload_details():
text = db.text(EXEC ODSQuery.SelectBridgeLoadBoard)
cols = [col for col in LoadBoard.__table__.c]
cols = map((lambda x: label('ODSQuery_tblLoadBoard_' + x.name, x)),
cols)
mobile_cols =
LoadMobileTracking.load_mobile_tracking_id.property.columns
mobile_cols = map((lambda x: label('LoadMobileTracking_' + x.name,
x)), cols)
cols.extend(mobile_cols)
taf = text.columns(*cols)
return db.session.query(
LoadBoard.load,
LoadBoard.orgn_stop,
LoadBoard.dest_stop,
LoadMobileTracking.load_mobile_tracking_id).from_statement(taf).all()
Actually, I'm pretty surprised it worked at all before, without the
labeling. How did it figure out which result set columns went to which ORM
object?
This is because what’s actually going on is more sophisticated than just
matching up the names. When the ORM looks for columns in a row, it uses
the actual Column object to target the column. If your class is mapped to
a table “users”, for example, it would look like this:
users = Table(‘users’, metadata, Column(‘id’, Integer), Column(‘name’,
String))
# … later
for row in conn.execute(some_orm_statement):
user_id = row[users.c.id]
user_name = row[users.c.name]
that is, we aren’t using strings at all. When the Core select() object
(or TextAsFrom in this case) is compiled for the backend, all the Column
objects it SELECTs from are put into an internal collection called the
“result_map”, which keys the result columns in several ways, including
their positional index (0, 1, 2, ..) as well as the string name the
statement knows they’ll have in the result set (e.g. the label name in this
case) to all the objects that might be used to look them up.
So using a label(), that adds another layer onto this. The label() you
create from an existing Column still refers to that Column, and we say the
Label object “proxies” the Column. if you look in mylabel.proxy_set()
you’ll see that Column.
So when we generate the result_map, we put as keys *all* of the things
that each label() is a “proxy” for, including the Column objects that are
in our mapping. its this large and awkward dictionary structure I’ve had
to stare at for many years as I often have to fix new issues that have
arisen (such as this one).
The result is generated, we link the columns in the cursor.description by
string name to the string names we know are rendered in the final compiled
construct, the result set now knows that all the Column/Label objects
corresponding to “id” are linked to that column and that’s how the lookup
proceeds.
I’m committing 2932 in a moment and I’m super really hoping I can put out
0.9.2 today but it’s easy for me to run out of time, but 0.9.2 is
definitely due.
That would be awesome! Incidentally though, would this labeling still work
once the fix is in?
all the existing mechanisms are maintained and I’ve just made some of the
matching logic a bit more liberal here, so should be fine. It’s all
committed if you want to try out the git master.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] AttributeError: 'CompositeProperty' object has no attribute 'props'
Cool, thanks!
On Mon, Feb 3, 2014 at 7:14 PM, Michael Bayer mike...@zzzcomputing.comwrote:
just call configure_mappers() for now, and the need for that step has been
removed in b069127b2d3f7b3f2c27f91cf,
http://www.sqlalchemy.org/trac/ticket/2935.
On Feb 3, 2014, at 4:12 PM, Matthew Phipps matt.the.m...@gmail.com
wrote:
Hi SQLAlchemy,
On SQLAlchemy 0.9.2, if I construct a query selecting a composite property
before constructing any other queries, I see this error:
Traceback (most recent call last):
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/nose/case.py,
line 197, in runTest
self.test(*self.arg)
File /media/psf/vagrant/test_configure_mappers.py, line 47, in
test_composite_prop_query
user_login_query = Session.query(User.login)
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py,
line 149, in do
return getattr(self.registry(), name)(*args, **kwargs)
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
line 1151, in query
return self._query_cls(entities, self, **kwargs)
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py,
line 106, in __init__
self._set_entities(entities)
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py,
line 114, in _set_entities
entity_wrapper(self, ent)
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py,
line 3338, in __init__
column = column._query_clause_element()
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py,
line 150, in _query_clause_element
return self.comparator._query_clause_element()
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/descriptor_props.py,
line 407, in _query_clause_element
return CompositeProperty.CompositeBundle(self.prop,
self.__clause_element__())
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/descriptor_props.py,
line 404, in __clause_element__
return expression.ClauseList(group=False, *self._comparable_elements)
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py,
line 689, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/descriptor_props.py,
line 419, in _comparable_elements
return self.prop._comparable_elements
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py,
line 689, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/descriptor_props.py,
line 236, in _comparable_elements
for prop in self.props
AttributeError: 'CompositeProperty' object has no attribute 'props'
I've written a nose test module that exposes this behavior. Note that no
SQL is ever actually issued (AFAIK?).
import logging
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import composite, sessionmaker, configure_mappers,
scoped_session
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:///:memory:')
session_factory = sessionmaker(bind=engine)
Session = scoped_session(session_factory)
Base = declarative_base()
logging.basicConfig()
logging.getLogger('sqlalchemy.orm').setLevel(logging.INFO)
class Login(object):
def __init__(self, name, password):
self.name = name
self.password = password
def __composite_values__(self):
return self.name, self.password
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String)
login = composite(Login, name, password)
def __repr__(self):
return User(name='%s', fullname='%s', password='%s') % (
self.name, self.fullname, self.password)
class TestConfigureMappers(object):
def tearDown(self):
Session.remove()
# This fails
def test_composite_prop_query(self):
user_login_query = Session.query(User.login)
# This works
def test_composite_prop_query_configuring_first(self):
# Either of these two lines will suffice
user_query = Session.query(User)
#configure_mappers()
user_login_query = Session.query(User.login)
user_login = user_login_query
Is this expected behavior? I figure that configure_mappers() must be
exposed publicly for a reason, but the docs say querying should be good
enough to invoke this
