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 <mike...@zzzcomputing.com>wrote:
>
> 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 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+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.
>
>
>
--
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.
