python-sybase-misc

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

[Skip M. <sk...@po...>]

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
sk...@po...

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

[stan l. <st...@ya...>]

--- Skip Montanaro <sk...@po...> wrote:
> 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
> sk...@po...
> _______________________________________________
> Python-sybase mailing list
> Pyt...@ww...
>
https://www.object-craft.com.au/cgi-bin/mailman/listinfo/python-sybase
> 


Hi Skip,

I don't have a solution to your problem, just a couple
of suggestions.  

a.  Is it possible to get an error code from the
cursor?  It should be an attribute you can print.
b.  Can it be that the date routine is not being
called until after the main query has run?  You could
check this by declaring variables and setting them
with the date call before you run the actual query. 
If the dates are unassigned while the query runs, you
would get no results.

Just some thoughts.

Stan



		
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail 

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

[Skip M. <sk...@po...>]

    >> I have a fairly straightforward query:
    ...
    >> that fails (returns no result rows) from Python. 

    stan> a.  Is it possible to get an error code from the
    stan> cursor?  It should be an attribute you can print.

I don't see anything like an error attribute and cursor objects done have
__getattr__ methods so it can't be a dynamically retrieved thing.  I also
checked the cursor's _fetcher attribute and the connection for error-like
attributes but didn't see anything.

    stan> b.  Can it be that the date routine is not being
    stan> called until after the main query has run?  You could
    stan> check this by declaring variables and setting them
    stan> with the date call before you run the actual query. 
    stan> If the dates are unassigned while the query runs, you
    stan> would get no results.

I'm not sure what you mean here.  When run, this code

    import Sybase

    q = """\
    SELECT trade_time, price
      FROM underlying_daily_fp
      WHERE symbol = @sym
        AND instrument = @inst
        AND trade_time BETWEEN @start AND @end
        AND (price_type='H' or price_type='L')
      ORDER BY trade_time DESC
    """

    args = {'@sym': 'ES',
            '@start': Sybase.Date(2004, 4, 19),
            '@end': Sybase.Date(2004, 5, 17),
            '@inst': 'F'
            }

    print [(k,type(v),v) for (k,v) in args.items()]

    conn = Sybase.Connection(...)
    c = conn.cursor()
    c.execute(q, args)
    print c.fetchall()

prints this output:

    [('@sym', <type 'str'>, 'ES'), ('@start', <type 'DateTimeType'>, 'Apr 19
    2004 12:00AM'), ('@end', <type 'DateTimeType'>, 'May 17 2004 12:00AM'),
    ('@inst', <type 'str'>, 'F')] 
    []

The DateTimeType objects are clearly created before the query executes.  In
any case, I'm not sure what you mean by "declaring variables and setting
them with the date call".  Is this something I embed in my query string?

If I twiddle the trade_time clause to be any of

        AND trade_time >= @start
        AND trade_time <= @end

or

        AND trade_time >= @start

or

        AND trade_time <= @end

the query always returns empty results.  If I remove the trade_time clause
altogether the query succeeds, but of course the date range of the returned
results is incorrect.

Thx,

-- 
Skip Montanaro
sk...@po...

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

[Dave C. <dj...@ob...>]

Skip Montanaro wrote:
>     >> I have a fairly straightforward query:
>     ...
>     >> that fails (returns no result rows) from Python. 
> 
>     stan> a.  Is it possible to get an error code from the
>     stan> cursor?  It should be an attribute you can print.
> 
> I don't see anything like an error attribute and cursor objects done have
> __getattr__ methods so it can't be a dynamically retrieved thing.  I also
> checked the cursor's _fetcher attribute and the connection for error-like
> attributes but didn't see anything.
> 
>     stan> b.  Can it be that the date routine is not being
>     stan> called until after the main query has run?  You could
>     stan> check this by declaring variables and setting them
>     stan> with the date call before you run the actual query. 
>     stan> If the dates are unassigned while the query runs, you
>     stan> would get no results.
> 
> I'm not sure what you mean here.  When run, this code
> 
>     import Sybase
> 
>     q = """\
>     SELECT trade_time, price
>       FROM underlying_daily_fp
>       WHERE symbol = @sym
>         AND instrument = @inst
>         AND trade_time BETWEEN @start AND @end
>         AND (price_type='H' or price_type='L')
>       ORDER BY trade_time DESC
>     """
> 
>     args = {'@sym': 'ES',
>             '@start': Sybase.Date(2004, 4, 19),
>             '@end': Sybase.Date(2004, 5, 17),
>             '@inst': 'F'
>             }
> 
>     print [(k,type(v),v) for (k,v) in args.items()]
> 
>     conn = Sybase.Connection(...)
>     c = conn.cursor()
>     c.execute(q, args)
>     print c.fetchall()
> 
> prints this output:
> 
>     [('@sym', <type 'str'>, 'ES'), ('@start', <type 'DateTimeType'>, 'Apr 19
>     2004 12:00AM'), ('@end', <type 'DateTimeType'>, 'May 17 2004 12:00AM'),
>     ('@inst', <type 'str'>, 'F')] 
>     []
> 
> The DateTimeType objects are clearly created before the query executes.  In
> any case, I'm not sure what you mean by "declaring variables and setting
> them with the date call".  Is this something I embed in my query string?
> 
> If I twiddle the trade_time clause to be any of
> 
>         AND trade_time >= @start
>         AND trade_time <= @end
> 
> or
> 
>         AND trade_time >= @start
> 
> or
> 
>         AND trade_time <= @end
> 
> the query always returns empty results.  If I remove the trade_time clause
> altogether the query succeeds, but of course the date range of the returned
> results is incorrect.

Can you try doing something like the following:

import Sybase

Sybase.set_debug(open('debug.log', 'w'))
    :
conn = Sybase.Connection(..., debug=1)
    :

Then the module will produce quite a bit of debugging output in the log 
file.  I hope that some of the output in the log file will shed some 
light on what is going on.

- Dave

-- 
http://www.object-craft.com.au

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

[Skip M. <sk...@po...>]

    Dave> Can you try doing something like the following:

    Dave> import Sybase

    Dave> Sybase.set_debug(open('debug.log', 'w'))
    Dave>     :
    Dave> conn = Sybase.Connection(..., debug=1)
    Dave>     :

Dave,

Thanks for the response.  Alas, there is no debug arg to the Connection
class constructor.  (I'm using 0.36.)  With just the set_debug() call
nothing turns up in debug.log.  Is the debug argument something that's only
available from CVS?

Skip

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

[Dave C. <dj...@ob...>]

Skip Montanaro wrote:
>     Dave> Can you try doing something like the following:
> 
>     Dave> import Sybase
> 
>     Dave> Sybase.set_debug(open('debug.log', 'w'))
>     Dave>     :
>     Dave> conn = Sybase.Connection(..., debug=1)
>     Dave>     :
> 
> Dave,
> 
> Thanks for the response.  Alas, there is no debug arg to the Connection
> class constructor.  (I'm using 0.36.)  With just the set_debug() call
> nothing turns up in debug.log.  Is the debug argument something that's only
> available from CVS?

Ooops.  I was confusing the Sybase module with another one of my 
modules...  Should have checked the code.  Try this:

import Sybase

Sybase._ctx.debug = 1
Sybase.set_debug(open('debug.log', 'w'))
     :

- Dave

-- 
http://www.object-craft.com.au

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

[Skip M. <sk...@po...>]

    Dave> Ooops.  I was confusing the Sybase module with another one of my 
    Dave> modules...  Should have checked the code.  Try this:

    Dave> import Sybase

    Dave> Sybase._ctx.debug = 1
    Dave> Sybase.set_debug(open('debug.log', 'w'))
    Dave>      :

Thanks, that worked much better.  Attached is the debug.log file.  I don't
see anything obvious, but I'm not really sure what I'm looking at either...

Skip