On Jul 12, 2013, at 5:53 PM, Basil Veerman <bveer...@uvic.ca> wrote:
> Hi,
>
> I've been struggling for a while trying to create a mapping that works with
> both PostGIS
what's a PostGIS database? do you mean a Postgresql database with spatial
extensions installed?
>
> Background: Production PostGIS database has been reduced and converted to a
> spatialite database with the same schema for offline testing purposes.
>
> Problem: PostGIS TIMESTAMP is now stored as SQLite TIMESTAMP, but effectively
> as a string. I think the main problems is that the default SQLite DateTime
> dialect storage_format includes miliseconds, our data does not.
OK the DATETIME object that's in the SQLite dialect supports customization of
this, but if your data doesnt have milliseconds, it just stores it as zero.
I'm not sure what the problem is exactly.
>
> A solution which seems to be working is to declare a TypeDecorator and set
> the Column type to it:
>
> class SQLiteDateTime(types.TypeDecorator):
> impl = types.String
>
> def process_bind_param(self, value, dialect):
> return datetime.strftime(value, '%Y-%m-%dT%H:%M:%S')
>
> def process_result_value(self, value, dialect):
> return datetime.strptime(value, '%Y-%m-%dT%H:%M:%S')
>
> This works as expected for SQLite, however does not for PostGIS (unless
> checking for dialect.name = 'sqlite'...)
I think you should be using plain old DateTime here, but if you need DateTime
with SQLite's DATETIME object specially configured, you can do this:
from sqlalchemy.dialects.sqlite import DATETIME
datetime = DateTime.with_variant(DATETIME(truncate_milliseconds=True))
if you want to stick with TypeDecorator, use load_dialect_impl():
class MyType(TypeDecorator):
# ...
def load_dialect_impl(self, dialect):
if dialect.name == 'sqlite':
return DATETIME(...)
else:
return DateTime(...)
>
> Overriding type compilation seems to be exactly what I need, however, as per
> the example:
I'm completely confused by that. type compilation only regards how the type
is rendered in a CREATE TABLE statement, it has nothing to do with how data is
marshalled into it.
>
>
> Currently these are both at the top of a mapping file which all the tables
> are declared. Am I just missing something about compile time overrides?
I really need to see a comprehensive, short example illustrating what exactly
the issue is since it's not at all clear.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
