between_ and Date problem (MySQL 5.0)

[Former user]

It seems that something wrong with between_ function and Date type. There is code to reproduce:

tt = Table("test", metadata,
             Column("id", Integer, primary_key = True)
            ,Column("date", Date )
            )

metadata.drop_all()
metadata.create_all()
tt.insert({"date":date(2006,6,6)}).execute()
tt.insert({"date":date(2006,6,7)}).execute()
tt.insert({"date":date(2006,6,8)}).execute()

s = tt.select(between_(tt.c.date,date(2006,6,1),date(2006,7,1)))

print s
for row in s.execute():
    print row

print "---"

s = tt.select(and_(tt.c.date>=date(2006,6,1),tt.c.date<=date(2006,7,1)))

print s
for row in s.execute():
    print row

print "---"

output:

SELECT test.id, test.date
FROM test
WHERE test.date BETWEEN 2006-06-01 AND 2006-07-01
---
SELECT test.id, test.date
FROM test
WHERE test.date >= %s AND test.date <= %s
(1L, datetime.date(2006, 6, 6))
(2L, datetime.date(2006, 6, 7))
(3L, datetime.date(2006, 6, 8))
---

Note on BETWEEN 2006-06-01 AND 2006-07-01 : dates should be inside quotes, but they aren't.

[Mike Bayer]

fixed in changeset:1604

[Mike Bayer]

I should add that you should call this function off the column directly:

s = tt.select(tt.c.date.between(date(2006,6,1),date(2006,7,1)))

let me see if i can make the standalone between_ smarter about that...

[Mike Bayer]

ok i fixed the standlaone function in changeset:1605 but column.between(val1, val2) is favored, since operations on columns should typically be called off the columns themselves (theres no other column operation that has a standalone function).