Re: [sqlalchemy] Re: all
So which is the best practice? "for row in query" or "for row in query.all()" On Thu, Jul 12, 2012 at 8:54 PM, Michael Bayer wrote: > > On Jul 11, 2012, at 8:27 PM, Gunnlaugur Briem wrote: > > Hi burgiduroy, > > On Wednesday, 11 July 2012 15:24:59 UTC, burgiduroy wrote: >> >> Are there any performance difference between the two? >> >> for row in query_object.all(): >>do_something() >> >> AND >> >> for row in query_object: >>do_something() >> > > The first fetches and processes all rows in one go before executing the > loop body. The second streams them in. > > How much that really matters depends on the DBAPI (it may fetch all rows > in one go anyway), and maybe on how heavy the object mapping is ... and on > whether you end up exiting your loop early (and so avoid processing the > rest unnecessarily). > > > Well, assuming this is an ORM Query, the Query object internally processes > all the rows before yielding them regardless, unless you used the > yield_per() method to configure that it should process them in batches. > Unfortunately the Query can't unconditionally yield rows at a time due to > the fact that the same mapped object can be present multiple times across > many rows, as a result of explicit joins as well as loads of related > collections, and mapped objects are mutable. > > So normally there's no real difference in memory usage between iter(query) > and query.all(), and none for performance except for the small overhead of > the extra list(self) that query.all() uses. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Custom in-query-only rendering for TypeDecorator types?
> > oh right. yeah call compiler.visit_column(element, **kw). > Perfect... that did the trick, thanks!!! Since the end result isn't much more than this example: http://docs.sqlalchemy.org/en/rel_0_7/core/compiler.html#synopsis it may be worth updating it to replace 'element.name' with 'compiler.visit_column(element, **kw)', since it may help future people. Russ -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/bdCRfquAUd4J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Custom in-query-only rendering for TypeDecorator types?
oh right. yeah call compiler.visit_column(element, **kw). On Jul 12, 2012, at 5:59 PM, Russ wrote: > On Thursday, July 12, 2012 5:42:12 PM UTC-4, Michael Bayer wrote: > let the compiler do it: > > elementName = compiler.process(element, **kw) > > That causes an infinite loop since it tries to compile DTColumn itself. > > I've tried stuff like super(DTColumn, element).compile(), but that doesn't > help (and was a guess anyway). > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/sqlalchemy/-/2eZs0B2dct0J. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Custom in-query-only rendering for TypeDecorator types?
On Thursday, July 12, 2012 5:42:12 PM UTC-4, Michael Bayer wrote: > > let the compiler do it: > > elementName = compiler.process(element, **kw) > That causes an infinite loop since it tries to compile DTColumn itself. I've tried stuff like super(DTColumn, element).compile(), but that doesn't help (and was a guess anyway). -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/2eZs0B2dct0J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Custom in-query-only rendering for TypeDecorator types?
> > However... element.table.name doesn't seem like it can be used directly. > It occasionally comes up with strings like "%(79508240 log_event)s", which > clearly is getting substituted in the guts somewhere when I don't do this. > To clarify, I've just determined that this is specifically happening when type(element.table) == sa.sql.expression.Alias. I'm still stumped, but that is more info, anyway. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/jcnVOxkGPaIJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Custom in-query-only rendering for TypeDecorator types?
On Jul 12, 2012, at 5:15 PM, Russ wrote:
>
> I've got it mostly working using this method, but have hit a few roadblocks.
> It was pretty easy to intercept Column definitions that were given the
> UTCEnforcedDateTime type and, with this done, then use DTColumn wrapper type
> instead with an associated compiler something like this:
>
> class DTColumn(sa.Column):
> pass
>
> @compiles(DTColumn)
> def compile_DTColumn(element, compiler, **kw):
> tz = UTCEnforcedDateTime.local_tz
> elementName = '"%s"."%s"' % (element.table.name, element.name)
> ret = "%s AT TIME ZONE '%s'" % (elementName, str(tz))
> return ret
>
> This does what I want (great!)... but this isn't quite right, yet.
>
> At first I followed the example [1] for @compiles usage with ColumnClause
> classes. This indicates to use element.name. This worked for many queries,
> until I ran into a query where the column name became ambiguous and I needed
> to specify the table name as well. Digging around the 'element' properties,
> I then added in element.table.name (as per the code above) to handle this.
> This worked for many more queries.
>
> However... element.table.name doesn't seem like it can be used directly. It
> occasionally comes up with strings like "%(79508240 log_event)s", which
> clearly is getting substituted in the guts somewhere when I don't do this.
>
> What is the proper way to get the fully qualified element name?
let the compiler do it:
elementName = compiler.process(element, **kw)
that will give you the SQL for the column as it would be rendered anyway.
But, this might give you problems specifically if the element is a _Label(), in
which case the expression is "element.element", so you might have to poke to
see if that's what you've got, then substitute that for a _Label() that is
around your SQL expression.
something like:
if isinstance(element, _Label):
my_elem = literal_column("%s AT TIME ZONE ..." %
compiler.process(element.element)).label(element.name)
return compiler.process(my_elem)
else:
ret = "%s AT TIME ZONE" % compiler.process(element)
the above isn't totally accurate but something along those lines.I could be
off here too, I'd need to screw with it (this is why I said it might be
tricky). There might be some recipe approach I could come up with here that
works more generically.
> Also, I put in the wrapping quotes on column name since I needed to for a few
> columns, but is there a "proper" way to do escape this in SQLAlchemy? I
> expect this is dialect-specific, since it only seems to get done when needed
> when left alone.
yeah all of that should be handled by the compiler.
>
> I'd also look into why the in-python TZ conversion is so slow, it seems a
> little suspect that it is 5x slower than the rest of the entire operation
> overall. I'd check things like, the result processor isn't being called
> repeatedly for the same value, stuff like that.
>
> I was also quite surprised, but it is legit. On re-profile just now it is
> not 80% of my operation, but more like 40%. See the light green part to the
> left of this runsnakerun output:
> http://static.inky.ws/image/2379/image.jpg
>
> There is some other stuff happening there (which only increase the
> tz-conversion percentage when considered), but the upshot of it all is that I
> do want to ditch the process_result_value overhead for this particular query,
> and learn more about the SQLAlchemy internals in the process. :)
>
> Russ
>
> [1] http://docs.sqlalchemy.org/en/rel_0_7/core/compiler.html#synopsis
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/sqlalchemy/-/udDuAdJqGzQJ.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Custom in-query-only rendering for TypeDecorator types?
On Thursday, July 12, 2012 3:12:26 PM UTC-4, Michael Bayer wrote: > > There is no functionality right now that allows special SQL to be > automatically associated with a type, as associated with an enclosing > column expression, at query time. There's a ticket to begin making this > kind of thing possible which is http://www.sqlalchemy.org/trac/ticket/1534. > When using @compiles for a type, the only time that compilation takes > effect is when the type itself is rendered, not its containing column - > that means, only CREATE TABLE and CAST expressions. > On Thursday, July 12, 2012 3:12:26 PM UTC-4, Michael Bayer wrote: > > There is no functionality right now that allows special SQL to be > automatically associated with a type, as associated with an enclosing > column expression, at query time. There's a ticket to begin making this > kind of thing possible which is http://www.sqlalchemy.org/trac/ticket/1534. > When using @compiles for a type, the only time that compilation takes > effect is when the type itself is rendered, not its containing column - > that means, only CREATE TABLE and CAST expressions. > I now understand this more now, thanks. Am I right in understanding that, after the initial type is passed to the Column constructor, there is no real connection between the ColumnClause specified in table definition and the column type (TypeEngine)? There might be some ways to use @compiles around Column expressions which > check the type too, but this might be a little tricky (or not). > I've got it mostly working using this method, but have hit a few roadblocks. It was pretty easy to intercept Column definitions that were given the UTCEnforcedDateTime type and, with this done, then use DTColumn wrapper type instead with an associated compiler something like this: class DTColumn(sa.Column): pass @compiles(DTColumn) def compile_DTColumn(element, compiler, **kw): tz = UTCEnforcedDateTime.local_tz elementName = '"%s"."%s"' % (element.table.name, element.name) ret = "%s AT TIME ZONE '%s'" % (elementName, str(tz)) return ret This does what I want (great!)... but this isn't quite right, yet. At first I followed the example [1] for @compiles usage with ColumnClause classes. This indicates to use element.name. This worked for many queries, until I ran into a query where the column name became ambiguous and I needed to specify the table name as well. Digging around the 'element' properties, I then added in element.table.name (as per the code above) to handle this. This worked for many more queries. However... element.table.name doesn't seem like it can be used directly. It occasionally comes up with strings like "%(79508240 log_event)s", which clearly is getting substituted in the guts somewhere when I don't do this. What is the proper way to get the fully qualified element name? Also, I put in the wrapping quotes on column name since I needed to for a few columns, but is there a "proper" way to do escape this in SQLAlchemy? I expect this is dialect-specific, since it only seems to get done when needed when left alone. I'd also look into why the in-python TZ conversion is so slow, it seems a > little suspect that it is 5x slower than the rest of the entire operation > overall. I'd check things like, the result processor isn't being called > repeatedly for the same value, stuff like that. I was also quite surprised, but it is legit. On re-profile just now it is not 80% of my operation, but more like 40%. See the light green part to the left of this runsnakerun output: http://static.inky.ws/image/2379/image.jpg There is some other stuff happening there (which only increase the tz-conversion percentage when considered), but the upshot of it all is that I do want to ditch the process_result_value overhead for this particular query, and learn more about the SQLAlchemy internals in the process. :) Russ [1] http://docs.sqlalchemy.org/en/rel_0_7/core/compiler.html#synopsis -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/udDuAdJqGzQJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Custom in-query-only rendering for TypeDecorator types?
On Jul 12, 2012, at 2:58 PM, Russ wrote: > > I'm trying to figure out how to change this so that, rather than converting > in python, the conversion happens at the database layer using AT TIME ZONE > syntax. I've tried using @compiles as follows: > > #THIS CODE DOES NOT WORK... > @compiles(UTCEnforcedDateTime) > def compile_UTCEnforcedDateTime(element, compiler, **kwargs): > tz = UTCEnforcedDateTime.local_tz > if tz: > ret = "%s AT TIMEZONE '%s'" % (element.name, str(tz)) > else: > ret = compiler.process(element) #NOT RIGHT - what is? > return ret > > However, this is not right for a few reasons... There is no functionality right now that allows special SQL to be automatically associated with a type, as associated with an enclosing column expression, at query time. There's a ticket to begin making this kind of thing possible which is http://www.sqlalchemy.org/trac/ticket/1534. When using @compiles for a type, the only time that compilation takes effect is when the type itself is rendered, not its containing column - that means, only CREATE TABLE and CAST expressions. Your best option here is to compose that SQL expression explicitly when you emit a query. There's many ways to make this happen depending on if you're dealing with Core only or ORM. There might be some ways to use @compiles around Column expressions which check the type too, but this might be a little tricky (or not). I'd also look into why the in-python TZ conversion is so slow, it seems a little suspect that it is 5x slower than the rest of the entire operation overall. I'd check things like, the result processor isn't being called repeatedly for the same value, stuff like that. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Custom in-query-only rendering for TypeDecorator types?
I currently define a custom column type for ensuring that dates stored to
the DB are offset-aware UTC, and convert to to the appropriate timezone on
retrieval, using a class something like this:
import pytz
import sqlalchemy as sa
class UTCEnforcedDateTime(sa.types.TypeDecorator):
"""DateTime type that ensures that stored datetime objects are
offset-aware UTC. Values retrieved from the database are converted to
the
local_tz (if class var is set).
"""
impl = sa.types.DateTime
local_tz = None #eg: pytz.timezone("EST")
def process_bind_param(self, value, engine):
if (value is not None) and (value.tzinfo != UTC):
raise ValueError("only offset-aware UTC datetimes are allowed.")
return value
def process_result_value(self, value, engine):
if value and self.local_tz:
#Make the DB value UTC right away (because it is!)...
utcDT = UTC.localize(value)
value = utcDT.astimezone(self.local_tz)
return value
###
This has worked extremely well to date, but I'm now finding that the
process_result_value way of doing things is (relatively) slow for large
numbers of records because the conversion happens in python after the data
is retrieved. ie: process_result_value is called for each appropriate
value in the resulting records. For example, a profile run showing 600 ms
total SQLAlchemy query time, with ~500 ms spent in process_result_value
doing tz conversions (!!).
I'm trying to figure out how to change this so that, rather than converting
in python, the conversion happens at the database layer using AT TIME ZONE
syntax. I've tried using @compiles as follows:
#THIS CODE DOES NOT WORK...
@compiles(UTCEnforcedDateTime)
def compile_UTCEnforcedDateTime(element, compiler, **kwargs):
tz = UTCEnforcedDateTime.local_tz
if tz:
ret = "%s AT TIMEZONE '%s'" % (element.name, str(tz))
else:
ret = compiler.process(element) #NOT RIGHT - what is?
return ret
However, this is not right for a few reasons...
The first problem is that the special compilation is executed when the
table is created that has this column type. I only want the special
compilation when the compilation is for rendering a query, but I can't
figure out how to determine this. The docs state
that teh compiler argument "can be inspected for any information about the
in-progress compilation, including compiler.dialect, compiler.statement",
but I can't figure it out.
The second problem is that I'm not certain how to pass through to the
default compilation that would have been done without my interception. Is
there a generic passthrough? compiler.visit_type_decorator seems
promising, but I really want "carry_on_like_I_never_intercepted_this()".
Or should I call visit_datetime?
How do I fix this?
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/AZ0K4D5PaFoJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: all
On Jul 11, 2012, at 8:27 PM, Gunnlaugur Briem wrote: > Hi burgiduroy, > > On Wednesday, 11 July 2012 15:24:59 UTC, burgiduroy wrote: > Are there any performance difference between the two? > > for row in query_object.all(): >do_something() > > AND > > for row in query_object: >do_something() > > The first fetches and processes all rows in one go before executing the loop > body. The second streams them in. > > How much that really matters depends on the DBAPI (it may fetch all rows in > one go anyway), and maybe on how heavy the object mapping is ... and on > whether you end up exiting your loop early (and so avoid processing the rest > unnecessarily). Well, assuming this is an ORM Query, the Query object internally processes all the rows before yielding them regardless, unless you used the yield_per() method to configure that it should process them in batches. Unfortunately the Query can't unconditionally yield rows at a time due to the fact that the same mapped object can be present multiple times across many rows, as a result of explicit joins as well as loads of related collections, and mapped objects are mutable. So normally there's no real difference in memory usage between iter(query) and query.all(), and none for performance except for the small overhead of the extra list(self) that query.all() uses. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
