using array as execute parameter in dbapi

[gglegrp112]

Is it possible to send an array as a parameter for an execute method in
dbapi2 module?


I'm using adodbapi and try to perfrom the following SQL query:
select * from item where storeid in ('01', '02')

When I use direct string formating it works:

a=('01','02')
cur.execute('select * from item where storeid in %s' % a.__repr__())

but trying to use parameter does not work:

cur.execute('select * from item where storeid in ?', a)
cur.execute('select * from item where storeid in ?', (a,))
cur.execute('select * from item where storeid in ?', ([a],)

and even this does not work:
cur.execute('select * from item where storeid in ?', a.__repr__())
cur.execute('select * from item where storeid in ?',
(a.__repr__(),))

surprisingly, this get executed be returns wrong result:
cur.execute('select * from item where storeid in (\'?\'),
"','".join(a))
I guess the question mark gets escaped by the quotes the parameter is
ignored.

Regards,

Magdy Salam

 

[Dennis Lee Bieber]

Is it possible to send an array as a parameter for an execute method in
dbapi2 module?

surprisingly, this get executed be returns wrong result:
cur.execute('select * from item where storeid in (\'?\'),
"','".join(a))
I guess the question mark gets escaped by the quotes the parameter is
ignored.

Bottom up... No, your \' are not escaping the ? -- and you are
missing the closing string quote in what you have there.

What you have know is that DBAPI parameter substitution puts quotes
around non-numeric data. That is one reason why one is supposed to use
it -- it determines, on a field by field basis, if quotes (or, depending
on adapter and data type, some other delimiter) is required for the
argument being handled.

Your join operation created a string of
"01','02"

(ignore the outer ")... THEN the dbapi parameter parser saw your ?, saw
that you supplied a string data type for that field, and surround the
string with whatever string delimiter is used in that database -- I'm
presuming, from your experiments, that it is a single quote. This means
your SQL looked like

select * from item where storeid in (''01\',\'02'')

where the ' inside the string are escaped by the dbapi, it put the '
before 0 and after 2, and you had entered the ' after ( and before )

You'll need a two-step process (at least your dbapi is using ? and
not %s, no confusion of escaping %

The first line creates as many parameter markers (?) as there are terms
in the tuple a, and puts that into the placeholder (%s) of the SQL
string. Now that you have to correct number of parameter markers, you
can let the dbapi do its job of properly escaping each one into the SQL.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[gglegrp112]

This is a really neat trick.

Thank you very much.