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
On Jul 12, 2013, at 8:56 PM, Basil Veerman <[email protected]> wrote:
> 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'
>
>
>
> On Fri, Jul 12, 2013 at 4:50 PM, Michael Bayer <[email protected]>
> wrote:
>
> On Jul 12, 2013, at 5:53 PM, Basil Veerman <[email protected]> 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 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
> [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
