Re: [sqlalchemy] Custom in-query-only rendering for TypeDecorator types?
For future reference, it is not actually a great idea to use @compiles to render with AT TIME ZONE as I did above. When done this way, SQLAlchemy renders all references to that column using this, *including* any references in a WHERE clause. eg: when looking for log events later than some date you would get: SELECT log.blah ,log.time AT TIME ZONE 'EST' -- intended use FROM log WHERE log.time AT TIME ZONE 'EST' foo -- not intended As said above, the problem here is that the custom compilation happened in both the column specification *and* the WHERE clause. This is not surprising (with hindsight), but it prevents any index on log.time from being used (unless there is an appropriate functional index). For this case, I only wanted it applied to the column spec, not the WHERE, but I don't think this is currently possible to differentiate this and compile differently in each location... or is it? I looked into compiler.statement et al to figure out the compilation context, but could not. Russ PS: For what it's worth, for this specific case in PostgreSQL, this type of functionality is better suited to appropriate use of the timestamp with time zone data type, and correct session usage of SET TIME ZONE and/or PGTZ usage. I'm currently wrestling with the use cases here instead. -- 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/-/G99byYSmjoQJ. 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 17, 2012, at 2:25 PM, Russ wrote: For future reference, it is not actually a great idea to use @compiles to render with AT TIME ZONE as I did above. When done this way, SQLAlchemy renders all references to that column using this, including any references in a WHERE clause. eg: when looking for log events later than some date you would get: SELECT log.blah ,log.time AT TIME ZONE 'EST' -- intended use FROM log WHERE log.time AT TIME ZONE 'EST' foo -- not intended As said above, the problem here is that the custom compilation happened in both the column specification and the WHERE clause. This is not surprising (with hindsight), but it prevents any index on log.time from being used (unless there is an appropriate functional index). For this case, I only wanted it applied to the column spec, not the WHERE, but I don't think this is currently possible to differentiate this and compile differently in each location... or is it? I looked into compiler.statement et al to figure out the compilation context, but could not. it is, you need to look in the **kw passed to your custom compile function for the flag within_columns_clause=True, which indicates it's rendering the columns clause. -- 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 http://goo.gl/N4igi 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] 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.
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 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?
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 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?
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?
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.
