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 <bveer...@uvic.ca> 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+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.
