[sqlalchemy] Re: mySQL and timedelta
Noufal wrote:
Hello everyone,
I'm having some trouble with a query involving a timedelta
object with a mySQL backend (MyISAM). I have a table called Run
that has two fields like so.
sa.Column('starttime', sa.TIMESTAMP),
sa.Column('endtime', sa.TIMESTAMP)
I'm trying to find all rows that have endtime - starttime equal
to some timedelta obtained from a user. The query is constructed
like this
l = datetime.timedelta(seconds=907)
cc = ((Run.c.endtime - Run.c.starttime) == l)
t=q.select(cc)
2007-10-01 14:10:12,572 INFO sqlalchemy.engine.base.Engine.0x..4c
SELECT runs.status AS runs_status, runs.workdir AS runs_workdir,
runs.machinetype AS runs_machinetype, runs.endtime AS
runs_endtime, runs.hostname AS runs_hostname, runs.cmdline AS
runs_cmdline, runs.incremental AS runs_incremental, runs.`user`
AS runs_user, runs.starttime AS runs_starttime, runs.rid AS
runs_rid
FROM runs
WHERE (runs.endtime - runs.starttime) = %s ORDER BY runs.rid
2007-10-01 14:10:12,572 INFO sqlalchemy.engine.base.Engine.0x..4c
[datetime.timedelta(0, 907)]
The %s looks funny there.
This returns the wrong row and when I do this kind of query via my
script (which is web based), I get messages in the server error
log that look like this
/usr/lib/python2.5/site-packages/sqlalchemy/databases/mysql.py:31
3: Warning: Truncated incorrect DOUBLE value: '00:08:40'
cursor.execute(statement, parameters)
I expect the users to input values like HH:MM:SS which I split
and use to create the timedelta object.
I'd appreciate any help.
MySQL doesn't support first-class intervals or arithmetic operators
for date types that I'm aware of. The date math functions can be
used for this sort of calculation, such as:
cc = (func.timestampdiff(text('second'), Run.c.starttime,
Run.c.endtime) == 907)
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mySQL and timedelta
On Oct 1, 2007, at 4:45 AM, Noufal wrote:
Hello everyone,
I'm having some trouble with a query involving a timedelta object
with a mySQL backend (MyISAM). I have a table called Run that has two
fields like so.
sa.Column('starttime', sa.TIMESTAMP),
sa.Column('endtime', sa.TIMESTAMP)
I'm trying to find all rows that have endtime - starttime equal to
some timedelta obtained from a user. The query is constructed like
this
l = datetime.timedelta(seconds=907)
cc = ((Run.c.endtime - Run.c.starttime) == l)
t=q.select(cc)
it would help to know what version youre using. SQLAlchemy might
treat this expression differently in 0.3 as it would in 0.4.
ultimately, the issue is probably that MySQLdb doesn't understand the
timedelta datatype youre sending over.
2007-10-01 14:10:12,572 INFO sqlalchemy.engine.base.Engine.0x..4c
SELECT runs.status AS runs_status, runs.workdir AS runs_workdir,
runs.machinetype AS runs_machinetype, runs.endtime AS runs_endtime,
runs.hostname AS runs_hostname, runs.cmdline AS runs_cmdline,
runs.incremental AS runs_incremental, runs.`user` AS runs_user,
runs.starttime AS runs_starttime, runs.rid AS runs_rid
FROM runs
WHERE (runs.endtime - runs.starttime) = %s ORDER BY runs.rid
2007-10-01 14:10:12,572 INFO sqlalchemy.engine.base.Engine.0x..4c
[datetime.timedelta(0, 907)]
The %s looks funny there.
the %s is a bind parameter.
Without trying it (and also maybe Jason has some ideas on this), youd
have to send in the type of value which MySQL produces when doing
math operations with dates. from your log output it seems like it
might be retunring a double, in which case thats the kind of value
youd have to bind to the statement , instead of a python
datetime.timedelta object.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---
