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.
