between and DateTime problem

Originally reported by: Anonymous

It seems that there is an another problem with between function; test case is below:

tt = Table("test", metadata,
             Column("id", Integer, primary_key = True)
            ,Column("time", DateTime )
            )

metadata.drop_all()
metadata.create_all()
tt.insert({"time":datetime(2006,6,6, 1, 1, 1)}).execute()
tt.insert({"time":datetime(2006,6,7, 2, 2, 2)}).execute()
tt.insert({"time":datetime(2006,6,8, 3, 3, 3)}).execute()

a = datetime(2006, 6, 6, 0, 0, 1)
b = datetime(2006, 6, 8, 23, 59, 59)

# it works
s = tt.select(and_(tt.c.time >= a, tt.c.time <=b))

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

print "---"

# it does not
s = tt.select(tt.c.time.between(a, b))

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

print "---"

output:

SELECT test.id, test.time
FROM test
WHERE test.time >= %s AND test.time <= %s
(1L, datetime.datetime(2006, 6, 6, 1, 1, 1))
(2L, datetime.datetime(2006, 6, 7, 2, 2, 2))
(3L, datetime.datetime(2006, 6, 8, 3, 3, 3))
---
SELECT test.id, test.time
FROM test
WHERE test.time BETWEEN 2006-06-06 00%s%s AND 2006-06-08 23%s%s
Traceback (most recent call last):
  File "between2.py", line 37, in ?
    for row in s.execute():
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql.py", line 505, in execute
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql.py", line 459, in execute_using
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql.py", line 366, in execute
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 463, in execute_compiled
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 280, in execute_compiled
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 276, in proxy
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 315, in _execute_raw
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 334, in _execute
sqlalchemy.exceptions.SQLError: (ProgrammingError) not enough arguments for format string 'SELECT test.id, test.time \nFROM test \nWHERE test.time BETWEEN 2006-06-06 00%s%s AND 2006-06-08 23%s%s' [None, None](None,)

may be it concerns standalone between function too.

Bitbucket: https://bitbucket.org/zzzeek/sqlalchemy/issue/205

Original comment by Michael Bayer (Bitbucket: zzzeek, GitHub: zzzeek):

i have tested the latest trunk (1609) with sqlite, mysql, postgres and I get the correct results, heres mysql which it seems is what youre using:

SELECT test.id, test.time 
FROM test 
WHERE test.time >= %s AND test.time <= %s
[17:25:16,248](2006-06-11) [engine](engine): SELECT test.id, test.time 
FROM test 
WHERE test.time >= %s AND test.time <= %s
[17:25:16,250](2006-06-11) [engine](engine): [6, 6, 0, 0, 1), datetime.datetime(2006, 6, 8, 23, 59, 59)](datetime.datetime(2006,)
(1L, datetime.datetime(2006, 6, 6, 1, 1, 1))
(2L, datetime.datetime(2006, 6, 7, 2, 2, 2))
(3L, datetime.datetime(2006, 6, 8, 3, 3, 3))
---
SELECT test.id, test.time 
FROM test 
WHERE test.time BETWEEN %s AND %s
[17:25:16,327](2006-06-11) [engine](engine): SELECT test.id, test.time 
FROM test 
WHERE test.time BETWEEN %s AND %s
[17:25:16,329](2006-06-11) [engine](engine): [6, 6, 0, 0, 1), datetime.datetime(2006, 6, 8, 23, 59, 59)](datetime.datetime(2006,)
(1L, datetime.datetime(2006, 6, 6, 1, 1, 1))
(2L, datetime.datetime(2006, 6, 7, 2, 2, 2))
(3L, datetime.datetime(2006, 6, 8, 3, 3, 3))
---

I tested MySQL with version 0.2.2 and I get the identical results as you did, so make sure you upgrade to at least revision 1609 from the trunk.

zzzeek / sqla_issue_test

between and DateTime problem #205