python-sybase-misc

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

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

Suppose I have this query:

    select foo from bar where baz in ("A", "B", "C")

and want to parameterize the where clause.  I've tried this:

    query = 'select foo from bar where baz in "@type"'
    args = {
        "@type": ("A", "B", "C"),
    }

but I get a TypeError (unsupported parameter type) when I try to execute the
query.

I manually tried creating DataBuf objects from various container types but
they all failed:

     >>> sybase.DataBuf(1)
    <DataBufType object at 0x8366728>
    >>> sybase.DataBuf(())
    Traceback (most recent call last):
      File "<stdin>", line 1, in ?
    TypeError: unsupported parameter type
    >>> sybase.DataBuf([])
    Traceback (most recent call last):
      File "<stdin>", line 1, in ?
    TypeError: unsupported parameter type
    >>> sybase.DataBuf(set())
    Traceback (most recent call last):
      File "<stdin>", line 1, in ?
    TypeError: unsupported parameter type

We are using an old version of the Sybase module (0.36) so this might have
been something previously unsupported but which is supported in a later
version, however I thought I would ask what the proper syntax for this is.

Thx,

-- 
Skip Montanaro - sk...@po... - http://www.smontanaro.net/
    Getting old sucks, but it beats dying young

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

[Deron M. <der...@gm...>]

On Tue, Aug 18, 2009 at 10:21 AM, <sk...@po...> wrote:
> Suppose I have this query:
>
>    select foo from bar where baz in ("A", "B", "C")
>
> and want to parameterize the where clause.  I've tried this:
>
>    query = 'select foo from bar where baz in "@type"'
>    args = {
>        "@type": ("A", "B", "C"),
>    }
>
> but I get a TypeError (unsupported parameter type) when I try to execute the
> query.

It's just a little more manual work on constructing your SQL.  You
have to make each item in the set a separate substitution, rather
than trying to use just one substitution with a tuple.

Say you have a list or set of items you want to match.  Then
try something like:

    items = ["A", "B", "C"]
    qitems = ", ".join( [ "@item%d" % n for n in range(len(items)) ] )
    query = "select foo from bar where baz in (" + qitems + ")
    args = dict( [ ("@item%d" % n, v) for n, v in enumerate(items) ] )

-- 
Deron Meranda

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

[Deron M. <der...@gm...>]

On Tue, Aug 18, 2009 at 10:51 AM, Deron Meranda<der...@gm...> wrote:
>    items = ["A", "B", "C"]
>    qitems = ", ".join( [ "@item%d" % n for n in range(len(items)) ] )
>    query = "select foo from bar where baz in (" + qitems + ")
>    args = dict( [ ("@item%d" % n, v) for n, v in enumerate(items) ] )

Oops, I dropped a missing quote character on the query= line above.

Oh, what the complicated-looking list comprehensions give you is
SQL which looks like:

   select foo from bar where baz in (@item0, @item1, @item2)

and an argument dictionary that is like:

   {'@item0': 'A', '@item1': 'B', '@item2': 'C'}


Though it will automatically handle any number of members
in the items list.
-- 
Deron Meranda

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

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

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.

Nick

On Tue, Aug 18, 2009 at 9:57 AM, Deron Meranda <der...@gm...>wrote:

> On Tue, Aug 18, 2009 at 10:51 AM, Deron Meranda<der...@gm...>
> wrote:
> >    items = ["A", "B", "C"]
> >    qitems = ", ".join( [ "@item%d" % n for n in range(len(items)) ] )
> >    query = "select foo from bar where baz in (" + qitems + ")
> >    args = dict( [ ("@item%d" % n, v) for n, v in enumerate(items) ] )
>
> Oops, I dropped a missing quote character on the query= line above.
>
> Oh, what the complicated-looking list comprehensions give you is
> SQL which looks like:
>
>   select foo from bar where baz in (@item0, @item1, @item2)
>
> and an argument dictionary that is like:
>
>   {'@item0': 'A', '@item1': 'B', '@item2': 'C'}
>
>
> Though it will automatically handle any number of members
> in the items list.
> --
> Deron Meranda
>
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus
> on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Python-sybase-misc mailing list
> Pyt...@li...
> https://lists.sourceforge.net/lists/listinfo/python-sybase-misc
>

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

[Deron M. <der...@gm...>]

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

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

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

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
>

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
>>
>
>

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

[Deron M. <der...@gm...>]

> 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).

Well, this is technically a property of the specific database you're using.

I don't know that much really about Sybase or SQL Server.
In Oracle this is true, although the newest releases have made this
performance penalty much less.  In MySQL there's no practical
difference.

However, still, from my examples we have:

  SQL = "select foo from bar where baz in (@item0, @item1, @item2)"
  ARGS = {'@item0': 'A', '@item1': 'B', '@item2': 'C'}

That's still using substitution, which for those databases which
matter, will allow the prepared statement to be reused (for any
set of values with the same number of items).

True, if you had another query that had four items rather than three
that may require another prepare.  But it's still much better than
using embedded literals in which case you could never reuse the
same statement even for ('A','B','C') and ('X','Y','Z').

Though I'm not sure that any database supports substitutions on more
than individual (literal) values anyway; e.g., you can't substitute an
entire set for an IN clause.  (Any Sybase experts which to chime in?)


>> 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 % str(types))

For this example either way is fine.  However what you're doing
is building the SQL with embedded string literals.  What I
showed still used the substitution mechanism.

True, the string formatting is way is clearer, but it's also
quite fragile.  What if you have a one-tuple:
   types = ('A',).
Then you get the invalid SQL:
   .... IN ('A',)
or if types was say something other than a tuple, say
   types = ['A','B','C']
or
   types = set(['A','B','C'])


You also don't get the advantage of correct escaping.  What if
you had types = ('A\'s', 'B') -- then it would break (a possible
SQL injection attack vector).

You also don't get the type conversions; e.g.,
  types = ('A', None, u'Z\u2012', datetime.date(2009,9,18))
etc....


>> 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.

Well, we really need a Sybase expert to weigh in here, which is
not me.  I can tell you that on Oracle it may make some difference,
but probably not much, especially on newer versions.  On MySQL its
a wash.
-- 
Deron Meranda