How to select the MIN() of an ORM attribute and a literal datetime?

How do I get the MIN() of a datetime column and a literal datetime in SQL Alchemy v0.6.4? I want to clamp a datetime result to a specific range.

I've been using sqlalchemy.func.min(column, literal_datetime). This seems to work fine in SQLite but not at all with MySQL, which no doubt means I'm going about this wrong. The error from MySQL is:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to开发者_JAVA百科 your MySQL server version for the right syntax to use near ' '2011-06-14 12:30:00') AS min_1 \nFROM example' at line 1") 'SELECT min(example.my_datetime, %s) AS min_1 \nFROM example' (datetime.datetime(2011, 6, 14, 12, 30),)

• How can I clamp datetime results in a portable way?
• (Do I really have to do it in my application?)

Here's what I've been using to explore the problem - works fine as presented here (using a memory-based SQLite DB), doesn't work with MySQL:

#!/usr/bin/env python

import random
from datetime import datetime

import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base

orm_base = declarative_base()

class MyClass( orm_base ):
    __tablename__ = "example"
    pri_key  = sa.Column(sa.Integer(), primary_key=True)
    my_datetime  = sa.Column(sa.DateTime(), index=True)

engine = sa.create_engine("sqlite:///:memory:", echo=True)

Session = orm.sessionmaker( bind=engine )

orm_base.metadata.bind = engine
orm_base.metadata.create_all()

# Create test-data

session = Session()

random.seed(1234567980)
for i in range(100):
    month = random.randint(1, 12)
    day = random.randint(1, 28)
    hour = random.randint(0, 23)
    minute = random.randint(0, 59)

    my_instance = MyClass()
    my_instance.my_datetime = datetime(2011, month, day, hour, minute)

    session.add( my_instance )

session.commit()
session.close()

# Problem starts here

session = Session()
literal_datetime = datetime(2011, 06, 14, 12, 30)
print session.query( sa.func.min(MyClass.my_datetime) ).one()  # OK
print session.query( sa.func.min(MyClass.my_datetime, literal_datetime) ).all()  # Error in MySQL

I'm not an SQL expert but database portability is important to me, so any tips on how to avoid pitfalls like this in the future are welcome.

---

Use sqlalchemy.sql.expression.case expression, which is supported on both mySql and SQLite.

The min/max functions of SQLite support non-aggregate operation on multiple values. This is not supported on mySql though.