[python-sybase] Pulling my hair out about a query...

[Skip Montanaro]

I have a fairly straightforward query:

    SELECT trade_time, price
      FROM underlying_daily_fp d, underlying_months m
      WHERE d.instrument = @inst
        AND d.symbol = @sym
        AND m.instrument=d.instrument
        AND m.symbol=d.symbol
        AND (price_type='H' or price_type='L')
        AND m.exp_year=d.exp_year
        AND m.exp_month=d.exp_month
        AND trade_time BETWEEN @start AND @end
        AND d.trade_time=m.date
        AND m.exp_num=1
      ORDER BY trade_time  DESC

with args:

    {'@sym': 'ES',
     '@start': 'Apr 19 2004 12:00AM',
     '@end': 'May 17 2004 12:00AM',
     '@inst': 'F'}

that fails (returns no result rows) from Python.  (The @start and @end
params are actually Sybase.Date() objects.)  However, the equivalent code
pasted into isql (using strings for dates) works as expected:

    1>  select trade_time, price from underlying_daily_fp d, underlying_months m
       where d.instrument='F'
       and d.symbol='ES'
       and m.instrument=d.instrument
     and m.symbol=d.symbol
     and (price_type='H' or price_type='L')
     AND m.exp_year=d.exp_year
     AND m.exp_month=d.exp_month
     AND trade_time BETWEEN 'Apr 19 2004 12:00AM' and 'May 17 2004 12:00AM'
     AND d.trade_time=m.date
     AND m.exp_num=1
     ORDER BY trade_time  DESC
    2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 
    14> go
     trade_time                 price                
     -------------------------- -------------------- 
            May 17 2004 12:00AM          1078.250000 
            May 17 2004 12:00AM          1089.250000 
            ...
            Apr 19 2004 12:00AM          1135.250000 

    (42 rows affected)
    1> 

Furthermore, if I manually expand the arguments and call the connection's or
cursor's execute() methods:

    >>> q
    "SELECT trade_time, price\n  FROM underlying_daily_fp d, underlying_months m\n  WHERE d.instrument = 'F'\n    AND d.symbol = 'ES'\n    AND m.instrument=d.instrument\n    AND m.symbol=d.symbol\n    AND (price_type='H' or price_type='L')\n    AND m.exp_year=d.exp_year\n    AND m.exp_month=d.exp_month\n    AND trade_time BETWEEN 'Apr 19 2004 12:00AM' AND 'May 17 2004 12:00AM'\n    AND d.trade_time=m.date\n    AND m.exp_num=1\n  ORDER BY trade_time  DESC"
    >>> len(db.execute(q)[0])
    42
    >>> c = db.cursor()
    >>> c.execute(q)
    >>> len(c.fetchall())
    42

it also works.

Is it date handling?  Is it possible to get the actual SQL statement
executed by the server so I can see if and where I've gone wrong?  Do other
people experience this problem (works from isql but not Python)?  The
platform is OCS 12.5 and the latest version of the Sybase module running on
Mandrake 10.

Thanks,

Skip Montanaro
skip@pobox.com