Filter objects within two seconds of one another using SQLAlchemy

Question

Hello: I have two tables with a column 'date'. One holds (name, date) and the other holds (date, p1, p2). Given a name, I want to use the date in table 1 to query p1 and p2 from table two; the match should happen if date in table one is within two seconds of date in table two.

How can you accomplish this using SQLAlchemy?

I've tried (unsuccessfully) to use the between operator and with a clause like:

td = datetime.timedelta(seconds=2)
q = session.query(table1, table2).filter(table1.name=='my_name').\
    filter(between(table1.date, table2.date - td, table2.date + td))

Any thoughts?

Edit: I've managed to solve the problem using the following approach:

from sqlalchemy.sql import between
import datetime
# [all other relevant imports]

td = datetime.timedelta(seconds=2)
t1_entry = session.query(table_1).filter(table_1.name == 'the_name').first()
if t1_entry is not None:
 tmin = t1_entry.date - td
 tmax = t1_entry.date + td
 t2_entry = session.query(table_2).filter(between(table_2.date, tmin, tmax)).first()
 return (t1_entry, t2_entry)
return None

So the comparison can be done, but I'm not sure the approach is efficient.

Answer 1

Let me first explain why what you tried doesn't work. SQLAlchemy is just a convenient way to write SQL queries, all the querying is nonetheless happening on the remote side. SQLAlchemy columns are special objects whose __eq__, __gt__ etc methods are overwritten to return not True or False, but other special objects, which remember what was the object they were compared to and can generate appropriate SQL statements later. The same is for adding etc: The custom __add__, __sub__ method does not return a number or a concatenated string but also such an object, that generates an sql statement. You can compare / add them to strings, integers etc, other columns, select statements, mysql function calls etc, but not to special python objects like timedeltas. (Simplified, and probably technically not 100% correct ;) )

So what you can do is:

• Make the values in the database integers, eg unix timestamps. That way your between query will work (with 2 instead of the delta)
• Use database-side functions to convert the datetime format date to a unix timestamp and then do the comparison.

UPDATE: I've played around a little with that, and somehow it does work, there even is an Interval data type. However at least here it does not work properly:

MySQL:

>>> db.session.execute(db.select([User.date_joined, User.date_joined + timedelta(seconds=2)], limit=1)).fetchall()
[(datetime.datetime(2009, 7, 10, 20, 47, 33), 20090710204733.0)]
>>> db.session.execute(db.select([User.date_joined, User.date_joined + 2], limit=1)).fetchall()
[(datetime.datetime(2009, 7, 10, 20, 47, 33), 20090710204735.0)]
>>> db.session.execute(db.select([User.date_joined+0, User.date_joined + 2], limit=1)).fetchall()
[(20090710204733.0, 20090710204735.0)]

SQLite:

>>> db.session.execute(db.select([User.date_joined, User.date_joined + timedelta(seconds=2)], limit=1)).fetchall()
TypeError: expected string or buffer
>>> db.session.execute(db.select([User.date_joined, User.date_joined + 2], limit=1)).fetchall()
[(datetime.datetime(2010, 5, 28, 23, 8, 22, 476708), 2012)]
>>> db.session.execute(db.select([User.date_joined+0, User.date_joined + 2], limit=1)).fetchall()
[(2010, 2012)]

I don't know why the first one fails on MySQL and why it returns floats. The SQLite errors seem to happen because SQLite does not have a DATETIME data type and SQLAlchemy stores it as a string.

You'll have to play around with that a little, maybe you'll find a way that works - but I think to stay really dbms independent the integer method will be the only feasible way.

Answer 2

After some time with this question open, the approach so far is the one I've come up with:

from sqlalchemy.sql import between
import datetime
# [all other relevant imports]

td = datetime.timedelta(seconds=2)
t1_entry = session.query(table_1).filter(table_1.name == 'the_name').first()
if t1_entry is not None:
 tmin = t1_entry.date - td
 tmax = t1_entry.date + td
 t2_entry = session.query(table_2).filter(between(table_2.date, tmin, tmax)).first()
 return (t1_entry, t2_entry)
return None

If you have a better idea, I will accept your answer.