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.
