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
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. signature.asc Description: Message signed with OpenPGP using GPGMail
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] from_statement, TextAsFrom and stored procedures
On Feb 5, 2014, at 6:32 PM, Matt Phipps matt.the.m...@gmail.com wrote: 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 OK great, added some more rules in 5c188f6c1ce85eaace27f052. 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. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] from_statement, TextAsFrom and stored procedures
On Feb 2, 2014, at 12:08 AM, Matthew Phipps matt.the.m...@gmail.com wrote:
db.session.commit()
typemap = {'id': db.Integer, 'username': db.String, 'email': db.String,
'random_time': UTCDateTime}
taf = text.columns(**typemap)
users = db.session.query(User).from_statement(taf).all()
This results in a stack trace:
AttributeError: 'TextAsFrom' object has no attribute 'use_labels'
Looks like TextAsFrom isn't quite select-like enough for from_statement(). I
tried tacking on a taf.use_labels = True before running the query, but just
got another error:
NoSuchColumnError Traceback (most recent call last)
ipython-input-23-c694595d6ec1 in module()
1 users = db.session.query(User).from_statement(taf).all()
NoSuchColumnError: Could not locate column in row for column 'user.id'
Any ideas? Incidentally, we can use the taf object in a session.execute() and
get great results back, type processing and all. Problem is, they're just
tuples (or a ResultProxy before you fetchall or iterate over it). Any way to
convince SQLAlchemy to turn that result set into User objects, or at that
point should we just send those to User() ourselves?
so this is good you worked through both issues that I’m fixing for
http://www.sqlalchemy.org/trac/ticket/2932. So besides the “use_labels” part,
the other thing that can make it work in 0.9.1 is to link the statement to the
mapped Column objects themselves:
from sqlalchemy.sql.selectable import TextAsFrom
TextAsFrom.use_labels = True
s.query(User).from_statement(
text(select * from users order by id).\
columns(User.id, User.name)
)
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.
signature.asc
Description: Message signed with OpenPGP using GPGMail
[sqlalchemy] from_statement, TextAsFrom and stored procedures
Hi SQLAlchemy,
Our data team wants us to use a (SQL Server 2008 R2) stored procedure to
perform our major query, which is all well and good, except it's preventing
SQLAlchemy's type processing from being applied. This is on SQLAlchemy
0.9.1, using pyodbc and FreeTDS.
For example, say we are trying to map this class (using Flask-SQLAlchemy):
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
email = db.Column(db.String(120), unique=True)
random_time = db.Column(UTCDateTime)
def __init__(self, username, email):
self.username = username
self.email = email
self.random_time = datetime.now()
Using this (trivial) user-defined type:
class UTCDateTime(db.TypeDecorator):
impl = db.DateTime
def process_result_value(self, value, dialect):
print AWOGA
return value
Create the table and populate it with some values:
db.create_all()
db.session.add(User('alice', 'al...@gmail.com'))
db.session.add(User('bob', 'b...@gmail.com'))
db.session.commit()
users = db.session.query(User).all()
Two AWOOGAs are output, as expected.
Then, create a stored procedure like this:
CREATE PROCEDURE GetUser AS
SELECT
*
FROM user
GO
And query into User objects using the procedure:
db.session.add(User('charlie', 'char...@gmail.com'))
db.session.commit()
text = db.text('exec getuser')
users = db.session.query(User).from_statement(text).all()
The resulting User objects look reasonable, *but no AWGAs*, and the
strings are all bytestrings.
After looking at the docs more closely, this isn't very surprising: text()
does warn about a lack of type processing, and suggests using
text().columns() to provide a mapping (in lieu of the now-deprecated
typemap kwarg to text()). This creates a TextAsFrom object, which adds some
extra superpowers to text() including a .c attribute. Problem is,
from_statement() doesn't like it:
db.session.commit()
typemap = {'id': db.Integer, 'username': db.String, 'email': db.String,
'random_time': UTCDateTime}
taf = text.columns(**typemap)
users = db.session.query(User).from_statement(taf).all()
This results in a stack trace:
AttributeErrorTraceback (most recent call last)
ipython-input-20-c694595d6ec1 in module()
1 users = db.session.query(User).from_statement(taf).all()
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in all(self)
2278
2279
- 2280 return list(self)
2281
2282 @_generative(_no_clauseelement_condition)
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in __iter__(self)
2386
2387 def __iter__(self):
- 2388 context = self._compile_context()
2389 context.statement.use_labels = True
2390 if self._autoflush and not self._populate_existing:
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in _compile_context(self, labels)
2732
2733 def _compile_context(self, labels=True):
- 2734 context = QueryContext(self)
2735
2736 if context.statement is not None:
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in __init__(self, query)
3478 if query._statement is not None:
3479 if isinstance(query._statement, expression.SelectBase)
and \
- 3480 not query._statement.use_labels:
3481 self.statement = query._statement.apply_labels()
3482 else:
AttributeError: 'TextAsFrom' object has no attribute 'use_labels'
Looks like TextAsFrom isn't quite select-like enough for from_statement().
I tried tacking on a taf.use_labels = True before running the query, but
just got another error:
NoSuchColumnError Traceback (most recent call last)
ipython-input-23-c694595d6ec1 in module()
1 users = db.session.query(User).from_statement(taf).all()
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in all(self)
2278
2279
- 2280 return list(self)
2281
2282 @_generative(_no_clauseelement_condition)
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc
in instances(query, cursor, context)
70 process[0](row, rows)
71 elif single_entity:
--- 72 rows = [process[0](row, None) for row in fetch]
73 else:
74 rows = [util.KeyedTuple([proc(row, None) for proc in
process],
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc
in _instance(row, result)
358 identitykey = (
359 identity_class,
-- 360 tuple([row[column]
