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.
