>>> 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.
