PostGIS/SQLite DateTime

[Basil Veerman]

Hi,

I've been struggling for a while trying to create a mapping that works with both PostGIS and SQLite databases when dealing with DateTime.

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.

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'...)

Overriding type compilation seems to be exactly what I need, however, as per the example:

@compiles(BINARY, "sqlite")

def compile_binary_sqlite(type_, compiler, **kw): 

    return "BLOB"

Changing to:

@compiles(DateTime, "sqlite")

def compile_datetime_sqlite(type_, complier, **kw):

    return "SQLiteDateTime"

Never runs as checked with an assert=False.

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?

Thanks,

Basil

[Michael Bayer]

On Jul 12, 2013, at 5:53 PM, Basil Veerman <bvee...@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.

[Basil Veerman]

Here is a short example that illustrates the original error:

Create Test Database:

$ sqlite3 testing.sqlite

SQLite version 3.7.13 2012-06-11 02:05:22

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> BEGIN TRANSACTION;

sqlite> CREATE TABLE 'obs_raw' ('obs_raw_id' INTEGER PRIMARY KEY, 'obs_time' TIMESTAMP, 'mod_time' TIMESTAMP, 'datum' FLOAT);

sqlite> INSERT INTO "obs_raw" VALUES(32568805,'2004-05-18T00:00:00','2011-08-29T12:13:18',21.0);

sqlite> INSERT INTO "obs_raw" VALUES(32568806,'2004-05-19T00:00:00','2011-08-29T12:13:18',19.3);

sqlite> INSERT INTO "obs_raw" VALUES(32568807,'2004-05-20T00:00:00','2011-08-29T12:13:18',20.8);

sqlite> INSERT INTO "obs_raw" VALUES(32568808,'2004-05-21T00:00:00','2011-08-29T12:13:18',17.8);

sqlite> INSERT INTO "obs_raw" VALUES(32568809,'2004-05-22T00:00:00','2011-08-29T12:13:18',19.4);

sqlite> COMMIT;

sqlite> .exit

Basic python test:

from sqlalchemy import Column, BigInteger, Float, String, DateTime

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Obs(Base):

    __tablename__ = 'obs_raw'

    id = Column('obs_raw_id', BigInteger, primary_key=True)

    time = Column('obs_time', DateTime(timezone=True))

    datum = Column(Float)

from sqlalchemy.orm import sessionmaker

from sqlalchemy import create_engine

engine = create_engine('sqlite+pysqlite:///testing.sqlite')

Session = sessionmaker(bind=engine)

session = Session()

for ob in session.query(Obs.time):

    print ob

Results when run:

  File "test_datetime.py", line 19, in <module>

    for ob in session.query(Obs.time):

  File "/home/bveerman/.local/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 75, in instances

    labels) for row in fetch]

  File "/home/bveerman/.local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3157, in proc

    return row[column]

ValueError: Couldn't parse datetime string: u'2004-05-18T00:00:00'

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/ZuH7W1qeSsQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Michael Bayer]

Right, DateTime + with_variant() + sqlite.DATETIME with a custom storage format and regexp.  *However*.  There's an unfortunate case that the storage format/regexp arguments, introduced in 0.8.0, are not actually working fully, and I've just committed the fix.  So you'll have to use 0.8.3 for now, which is not released you can get it via https://bitbucket.org/zzzeek/sqlalchemy/get/rel_0_8.tar.gz .

Example:

from sqlalchemy import Column, BigInteger, Float, String, DateTime, Integer

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.dialects import sqlite

import re

Base = declarative_base()

# needs SQLAlchemy 0.8.3 to work correctly

sqlite_date = DateTime(timezone=False).with_variant(

                sqlite.DATETIME(

                storage_format="%(year)04d-%(month)02d-%(day)02dT%(hour)02d:%(minute)02d:%(second)02d",

                regexp=r"(\d+)-(\d+)-(\d+)T(\d+):(\d+):(\d+)",

            ), "sqlite")

class Obs(Base):

    __tablename__ = 'obs_raw'

    id = Column('obs_raw_id', BigInteger, primary_key=True)

    time = Column('obs_time', sqlite_date)

    datum = Column(Float)

from sqlalchemy.orm import sessionmaker

from sqlalchemy import create_engine

engine = create_engine('sqlite:///test.db', echo=True)

Session = sessionmaker(bind=engine)

session = Session()

for ob in session.query(Obs.time):

    print ob

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+...@googlegroups.com.

[Basil Veerman]

Hi Michael,

Thanks for your help.  Using 0.8.3 and the type variant passes our tests.

Do you have any (even rough) estimate as to when 0.8.3 will be released to PyPI?

Thanks,

Basil

[Michael Bayer]

well we try to get releases out every 4-6 weeks but sometimes it takes longer.    though this issue was a surprise and does lean things towards releasing sooner.