I've updated to psycopg2 2.2.1, working now. Sorry for wasting your time! On Dec 20, 6:01 pm, ellonweb <[email protected]> wrote: > >>> print session.query(Updates.timestamp - td).first() > > 2010-12-20 17:44:45,757 INFO sqlalchemy.engine.base.Engine.0x...dad0 > BEGIN (implicit) > 2010-12-20 17:44:45,766 INFO sqlalchemy.engine.base.Engine.0x...dad0 > SELECT updates.timestamp - %(timestamp_1)s AS anon_1 > FROM updates > LIMIT 1 OFFSET 0 > 2010-12-20 17:44:45,779 INFO sqlalchemy.engine.base.Engine.0x...dad0 > {'timestamp_1': datetime.timedelta(0, 60)} > Traceback (most recent call last): > ... > > I'm using psycopg2 2.0.14, I'm starting to think that's the cause? > Slightly off-topic but do you know if the binary distributions of > psycopg2 built against pg9 will work fine on 8.4? (This is the reason > I'm using such an old version) > > Here's the full traceback if it's > useful:http://paste.pound-python.org/show/740/ > > On Dec 20, 5:24 pm, Michael Bayer <[email protected]> wrote: > > > On Dec 20, 2010, at 11:58 AM, ellonweb wrote: > > > > FYI, I'm using 0.6.5 and postgre8.4/psycopg2. > > > Passing in timedeltas didn't work, though I notice the opposite works: > > > I can pass in a datetime to compare to a db-datetime and SQLA gives me > > > a timedelta back, but I can't compare a timedelta with a db-datetime > > > to get a datetime back: > > > > With a declarative table Updates, containing a column timestamp, > > > roughly like this: > > > class Updates(Base): > > > __tablename__ = 'updates' > > > timestamp = Column(DateTime, default=current_timestamp()) > > > can't reproduce, psycopg2 2.2.2: > > > from sqlalchemy import * > > from sqlalchemy.orm import * > > from sqlalchemy.ext.declarative import declarative_base > > from datetime import * > > > Base = declarative_base() > > > engine = create_engine('postgresql://scott:ti...@localhost/test', echo=True) > > > class Updates(Base): > > __tablename__ = 'updates' > > id = Column(Integer, primary_key=True) > > timestamp = Column(DateTime, default=func.current_timestamp()) > > > Base.metadata.create_all(engine) > > > session = Session(engine) > > session.add_all([Updates(), Updates(), Updates()]) > > session.commit() > > > td=timedelta(minutes=1) > > print Updates.timestamp - td > > > print session.query(Updates.timestamp - td).first() > > > output: > > > 2010-12-20 12:22:04,686 INFO sqlalchemy.engine.base.Engine.0x...7510 select > > version() > > 2010-12-20 12:22:04,686 INFO sqlalchemy.engine.base.Engine.0x...7510 {} > > 2010-12-20 12:22:04,688 INFO sqlalchemy.engine.base.Engine.0x...7510 select > > current_schema() > > 2010-12-20 12:22:04,688 INFO sqlalchemy.engine.base.Engine.0x...7510 {} > > 2010-12-20 12:22:04,690 INFO sqlalchemy.engine.base.Engine.0x...7510 select > > relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where > > n.nspname=current_schema() and lower(relname)=%(name)s > > 2010-12-20 12:22:04,690 INFO sqlalchemy.engine.base.Engine.0x...7510 > > {'name': u'updates'} > > 2010-12-20 12:22:04,731 INFO sqlalchemy.engine.base.Engine.0x...7510 BEGIN > > (implicit) > > 2010-12-20 12:22:04,731 INFO sqlalchemy.engine.base.Engine.0x...7510 INSERT > > INTO updates (timestamp) VALUES (CURRENT_TIMESTAMP) RETURNING updates.id > > 2010-12-20 12:22:04,732 INFO sqlalchemy.engine.base.Engine.0x...7510 {} > > 2010-12-20 12:22:04,759 INFO sqlalchemy.engine.base.Engine.0x...7510 INSERT > > INTO updates (timestamp) VALUES (CURRENT_TIMESTAMP) RETURNING updates.id > > 2010-12-20 12:22:04,759 INFO sqlalchemy.engine.base.Engine.0x...7510 {} > > 2010-12-20 12:22:04,760 INFO sqlalchemy.engine.base.Engine.0x...7510 INSERT > > INTO updates (timestamp) VALUES (CURRENT_TIMESTAMP) RETURNING updates.id > > 2010-12-20 12:22:04,760 INFO sqlalchemy.engine.base.Engine.0x...7510 {} > > 2010-12-20 12:22:04,761 INFO sqlalchemy.engine.base.Engine.0x...7510 COMMIT > > updates.timestamp - :timestamp_1 > > 2010-12-20 12:22:04,763 INFO sqlalchemy.engine.base.Engine.0x...7510 BEGIN > > (implicit) > > 2010-12-20 12:22:04,764 INFO sqlalchemy.engine.base.Engine.0x...7510 SELECT > > updates.timestamp - %(timestamp_1)s AS anon_1 > > FROM updates > > LIMIT 1 OFFSET 0 > > 2010-12-20 12:22:04,764 INFO sqlalchemy.engine.base.Engine.0x...7510 > > {'timestamp_1': datetime.timedelta(0, 60)} > > (datetime.datetime(2010, 12, 20, 12, 21, 4, 732161),) > > > >>>> td=timedelta(minutes=1) > > >>>> td > > > datetime.timedelta(0, 60) > > >>>> Updates.timestamp - td > > > <sqlalchemy.sql.expression._BinaryExpression object at 0x033D5150> > > >>>> print Updates.timestamp - td > > > updates.timestamp - %(timestamp_1)s > > >>>> session.query(Updates.timestamp - td).first() > > > > sqlalchemy.exc.DataError: (DataError) invalid input syntax for type > > > timestamp: "0 days 60.000000 seconds" > > > LINE 1: SELECT updates.timestamp - '0 days 60.000000 seconds' AS > > > ano... > > > > On Dec 20, 3:02 pm, Michael Bayer <[email protected]> wrote: > > >> On Dec 20, 2010, at 8:22 AM, ellonweb wrote: > > > >>> If I have an integer column I can easily select the column minus one: > > >>> session.query(mytable.column - 1) > > > >>> If I want to select a datetime column minus one minute, there doesn't > > >>> seem to be an easy way to do it. > > >>> I would have expected to be to do something like: > > >>> session.query(mytable.column - datetime.timedelta(minutes=1)) > > > >>> The only way I've been able to do what I want is like this: > > >>> session.query(mytable.column - cast('60', Interval)) > > >>> Is this the best way to do this or have I missed something? > > > >> This depends on the DBAPI and database backend in use. For example, if > > >> you use psycopg2 with postgresql, you can pretty much pass in timedeltas > > >> and datetimes and date arithmetic is fully possible (SQLAlchemy 0.6 > > >> needed). With other backends such as SQLIte and SQL Server, you > > >> typically need to use the built-in functions of those backends to coerce > > >> timedeltas into integer values and/or use the comparison functions > > >> provided by that backend. > > > >> Some modicum of platform-neutrality can be achieved if you use the > > >> @compiles extension to build higher level date functions that do what > > >> you need, such as the date comparison function below I use for PG/MSSQL: > > > >> from sqlalchemy import expression, Integer > > >> from sqlalchemy.ext.compiler import compiles > > > >> class datediff(expression.FunctionElement): > > >> type = Integer() > > >> name = 'datediff' > > > >> @compiles(datediff, 'postgresql') > > >> def _pg_datediff(element, compiler, **kw): > > >> return "(%s::date - %s::date)" % ( > > >> compiler.process(element.clauses.clauses[1]), > > >> compiler.process(element.clauses.clauses[0]), > > >> ) > > > >> @compiles(datediff, 'mssql') > > >> def _ms_datediff(element, compiler, **kw): > > >> return "DATEDIFF(day, %s, %s)" % ( > > >> compiler.process(element.clauses.clauses[0], **kw), > > >> compiler.process(element.clauses.clauses[1], **kw), > > >> ) > > > >>> Thanks > > > >>> -- > > >>> You received this message because you are subscribed to the Google > > >>> Groups "sqlalchemy" group. > > >>> To post to this group, send email to [email protected]. > > >>> To unsubscribe from this group, send email to > > >>> [email protected]. > > >>> For more options, visit this group > > >>> athttp://groups.google.com/group/sqlalchemy?hl=en. > > > > -- > > > You received this message because you are subscribed to the Google Groups > > > "sqlalchemy" group. > > > To post to this group, send email to [email protected]. > > > To unsubscribe from this group, send email to > > > [email protected]. > > > For more options, visit this group > > > athttp://groups.google.com/group/sqlalchemy?hl=en.
-- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
