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 On Friday, 12 July 2013 18:56:14 UTC-7, Michael Bayer wrote: > > 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]<javascript:>> > 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]<javascript:> > > wrote: > >> >> On Jul 12, 2013, at 5:53 PM, Basil Veerman <[email protected] <javascript:>> >> 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] <javascript:>. >> To post to this group, send email to [email protected]<javascript:> >> . >> 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] <javascript:>. > To post to this group, send email to [email protected]<javascript:> > . > 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.
