[sqlalchemy] Re: Modifying results of a KeyedTuple query?
Just a quick followup... Thanks again for the help/advice. I did what you suggested, and the whole query (with bulk_update_mappings) takes .16 seconds to return a result set of 7800 records or so. That's up from ~1.2 seconds it took before I did the optimizations. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Modifying results of a KeyedTuple query?
Oops, I missed the part about the bulk update. I suppose that would work as long as it's keying on the index. The documentation doesn't make it clear how it picks the index to query on... I assume it inspects the column properties and picks the best one to use behind the scenes? Normally you'd query on the id or something. On Friday, August 10, 2018 at 4:21:32 PM UTC-5, James Couch wrote: > > I think I see what you mean. Do an inline query/update, maybe just query > by primary index for speed. I guess that won't add too much overhead, I'll > give it a shot. > > On Friday, August 10, 2018 at 1:43:51 PM UTC-5, Mike Bayer wrote: >> >> You need to copy the keyedtuples into some other data structure, like a >> dictionary, modify it, then send that data back into updates. Your best >> bet is to use the bulk update stuff once you have those dictionaries, see >> http://docs.sqlalchemy.org/en/latest/orm/session_api.html?highlight=bulk#sqlalchemy.orm.session.Session.bulk_update_mappings >> >> . >> >> >> -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Modifying results of a KeyedTuple query?
I think I see what you mean. Do an inline query/update, maybe just query by primary index for speed. I guess that won't add too much overhead, I'll give it a shot. On Friday, August 10, 2018 at 1:43:51 PM UTC-5, Mike Bayer wrote: > > You need to copy the keyedtuples into some other data structure, like a > dictionary, modify it, then send that data back into updates. Your best > bet is to use the bulk update stuff once you have those dictionaries, see > http://docs.sqlalchemy.org/en/latest/orm/session_api.html?highlight=bulk#sqlalchemy.orm.session.Session.bulk_update_mappings > > . > > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Modifying results of a KeyedTuple query?
On Friday, August 10, 2018 at 4:03:06 PM UTC-5, Jonathan Vanasco wrote: > > > A quick background on Mike's short answer... Tuples are immutable lists in > Python, and "KeyedTuple" should indicate that you can't change the values. > They're just a handy result storage object, not an ORM object mapped to a > table row. > Indeed. I was hoping there might be a way to convert the NamedTuple back into a model class without having to do a second query just to update the values. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Modifying results of a KeyedTuple query?
Hey all. Long time lurker, first time poster. I'm using sqlalchemy ORM. We have a fairly decent sized data set, and one table has a pretty large number of columns, some of them with foreignkeys. I found that limiting a query to specific columns speeds up the time it takes to come back with a result by quite a bit (like 100x faster for this one table, talking like over a second to just a few milliseconds difference). So I have a query that goes something like this: q = dbsession.query( Host.hostname, Host.platform, Host.ostype, Host.buildstatus, Host.created_on, Host.created_by, Host.modified_by, Host.jobrunid).filter(Host.buildstatus != None).all() I then pass that result through a few functions to validate/update some of the fields. Problem is, I could do this on a regular Query result (q = dbsession.query(Host).filter(Host.buildstatus != None).all()) but the results I get back from this optimized query as posted above, I can't modify those values, I get "AttributeError: can't set attribute". I've been Googling this and searching through these posts, but I guess I don't know the right terms to search on. I can't find anything in the documentation talking about how to modify the results of a KeyedTuple query. Normally I would do something like: for x in q: if pingresult(x.hostname) is False: x.buildstatus = 'removed' dbsession.commit() dbsession.close() But if q is a set of KeyedTuples, this won't work. What's the speediest way to accomplish this task? Should I just stick to querying all the columns (query(Host)...) and just take the performance hit? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
