Antwort: MySQLDB - generating "...not in (1, 2,

Discussion in 'Python' started by Holger Joukl, Feb 23, 2004.

  1. Holger Joukl

    Holger Joukl Guest

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





    |---------+--------------------------------------------------->
    | | |
    | | |
    | | |
    | | |
    | | (Richard Shea) |
    | | Gesendet von: |
    | | python-list-bounces+holger.joukl=|
    | | rg |
    | | |
    | | |
    | | 23/02/2004 10:52 |
    | | |
    |---------+--------------------------------------------------->
    >------------------------------------------------------------------------------------------------------------------|

    | |
    | An: |
    | 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.
    Holger Joukl, Feb 23, 2004
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Richard Shea
    Replies:
    4
    Views:
    432
  2. Holger Joukl
    Replies:
    0
    Views:
    307
    Holger Joukl
    Feb 23, 2004
  3. Marc Boeren
    Replies:
    2
    Views:
    266
    Steve Zatz
    Feb 23, 2004
  4. Holger Joukl
    Replies:
    0
    Views:
    285
    Holger Joukl
    Feb 26, 2004
  5. Roland Puntaier
    Replies:
    0
    Views:
    1,186
    Roland Puntaier
    May 15, 2007
Loading...

Share This Page