Antwort: MySQLDB - generating "...not in (1, 2, 3)" from Python list ? ['LBBW': checked]

[Holger Joukl]

You could use the str() builtin, returning the string representation of the
list object:

>>> params = (1, 2, 3)
>>> "select * from T where C1 not in %s" % str(params)
'select * from T where C1 not in (1, 2, 3)'
>>>




|---------+--------------------------------------------------->
|         |                                                   |
|         |                                                   |
|         |                                                   |
|         |                                                   |
|         |  richardshea at fastmail.fm (Richard Shea)           |
|         |  Gesendet von:                                    |
|         |  python-list-bounces+holger.joukl=lbbw.de at python.o|
|         |  rg                                               |
|         |                                                   |
|         |                                                   |
|         |  23/02/2004 10:52                                 |
|         |                                                   |
|---------+--------------------------------------------------->
  >------------------------------------------------------------------------------------------------------------------|
  |                                                                                                                  |
  |        An:      python-list at python.org                                                                           |
  |        Kopie:                                                                                                    |
  |        Thema:   MySQLDB - generating "...not in (1,2,3)" from Python list ? ['LBBW': checked]                    |
  >------------------------------------------------------------------------------------------------------------------|




Hi - I've writing a Python script which has a query which looks like
this ...

select * from T where C1 not in (1,2,3)

... C1 is a numeric column so elements of (1,2,3) must not be quoted
like this ('1','2','3') and of course they must not be quoted like
this ('1,2,3').

I'm using 'scanf' style substitution into the SQL, eg ...

cursor.execute("select * from T where C1 not in (%s)",params).


My problem is that the values that need to appear in the bracket are
held in a Python list. At first I thought this was great - just use
'join' with ',' as the second arg but of course join is expecting a
list of strings and if you str() the contents of the list you end up
with ('1','2','3').

Then I tried rolling my own string concatenation but then you end up
with a string or ('1,2,3') which the SQL doesn't like.

So in summary - would anyone be kind enough to tell me, given that I'm
using scanf style SQL subbing, how I can substitute in a comma
delimited list of integers without quotes being put around things to
upset the SQL ?

thanks

richard shea.
--
 http://mail.python.org/mailman/listinfo/python-list



Der Inhalt dieser E-Mail ist vertraulich. Falls Sie nicht der angegebene
Empfänger sind oder falls diese E-Mail irrtümlich an Sie adressiert wurde,
verständigen Sie bitte den Absender sofort und löschen Sie die E-Mail
sodann. Das unerlaubte Kopieren sowie die unbefugte Übermittlung sind nicht
gestattet. Die Sicherheit von Übermittlungen per E-Mail kann nicht
garantiert werden. Falls Sie eine Bestätigung wünschen, fordern Sie bitte
den Inhalt der E-Mail als Hardcopy an.

The contents of this  e-mail are confidential. If you are not the named
addressee or if this transmission has been addressed to you in error,
please notify the sender immediately and then delete this e-mail.  Any
unauthorized copying and transmission is forbidden. E-Mail transmission
cannot be guaranteed to be secure. If verification is required, please
request a hard copy version.