[python-sybase] Pulling my hair out about a query...
Dave Cole
djc at object-craft.com.au
Wed, 23 Jun 2004 09:48:46 +1000
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