Sounds great, thanks. On Wednesday, 17 July 2013 19:35:48 UTC-7, Michael Bayer wrote: > > 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. > > > > On Jul 17, 2013, at 12:38 PM, Basil Veerman <bvee...@uvic.ca <javascript:>> > wrote: > > 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 <basilv...@gmail.com> 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 >> <mik...@zzzcomputing.com>wrote: >> >>> >>> 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. >>> >>> >>> >>> -- >>> 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. >>> >>> >>> >> >> >> -- >> 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. >> 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. >> >> >> >> >> > -- > 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 <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com<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 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.
