Re: [Python-sybase-misc] How to set Sybase sets/tuples from Python?

[Nick E. <ne...@gm...>]

That should of course be:
cursor.execute(query % str(types))

On Tue, Aug 18, 2009 at 10:37 AM, Nick Edds <ne...@gm...> wrote:

> I was under the impression that repeat use of a query was for performance
> given this in the documentation of execute:
>
> The prepared dynamic SQL will be reused by the cursor if the same SQL is
> passed in the sql argument. This is most effective for algorithms where
> the same operation is used, but different parameters are bound to it (many
> times).
>
> If the parameters are not being used for performance though, how is your
> solution superior to the naive:
> query = 'select foo from bar where baz in %s'
> types = ('A', 'B', 'C')
> cursor.execute(query % types)
>
> which is more transparent.
>
> If the performance gains are of significance from repeat use of a query,
> your solution has the advantage that the cursor will reuse the prepared
> dynamic SQL for the 2nd+ time using any particular number of items.
>
> Nick
>
>
>
> On Tue, Aug 18, 2009 at 10:27 AM, Deron Meranda <der...@gm...>wrote:
>
>> On Tue, Aug 18, 2009 at 11:19 AM, Nick Edds<ne...@gm...> wrote:
>> > But what if later you want to run the query with items = ['A', 'B', 'C',
>> > 'D']?
>> >
>> > Then you're going to need to construct a new query because the original
>> > query can only support 3 items., so you lose the performance gain of
>> making
>> > a query that takes parameters. I can't really see a better solution
>> though.
>>
>> Constructing the query and args dict using the list comprehension
>> methods I showed will equally work for any sized list of items (>=1)
>> without changing any python code.
>>
>> As far as I'm aware the substitution mechanism in the DBI is not
>> there for performance; but is primarily there to make it easier to use,
>> to facilitate automatic type conversions, and to help prevent mistakes
>> in escaping and quoting SQL literals.
>>
>> I suspect any performance loss or gain will be negligible.
>> --
>> Deron Meranda
>>
>
>