MySQLDB - generating "...not in (1,2,3)" from Python list ?

Discussion in 'Python' started by Richard Shea, Feb 23, 2004.

  1. Richard Shea

    Richard Shea Guest

    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.
    Richard Shea, Feb 23, 2004
    #1
    1. Advertising

  2. Richard Shea

    Peter Otten Guest

    Richard Shea wrote:

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


    >>> numbers = (1,2,3,99)
    >>> "(%s)" % ", ".join(map(str, numbers))

    '(1, 2, 3, 99)'
    Peter Otten, Feb 23, 2004
    #2
    1. Advertising

  3. "Richard Shea" <> schrieb im Newsbeitrag
    news:...
    > 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.




    >>> sql = "select * from table where C1 not in (%s)"
    >>> params = [str(i) for i in (1,2,3)]
    >>> sql % ",".join(params)

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

    Looks solid to me.

    Vincent Wehren







    >
    > 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.
    vincent wehren, Feb 23, 2004
    #3
  4. Richard Shea

    Richard Shea Guest

    Hi - I'm sorry I haven't responded before I got a cold earlier this
    week and it's kind of knocked me sideways. Reading the replies I
    realised I had done something fundamentally wrong and I was able to
    use them as a basis for getting it to work correctly so thanks very
    much to all of you for your help.

    There is one thing about the whole business which I find a bit
    difficult - it would be nice if after you have executed the query you
    were able to actually view the query (with substituted parameters) as
    a string to ensure that your query was what it thought it was. I
    understand that mySQLdb is really a wrapper around the C API for
    MySQL. I've taken a look at that and I can't find anything like what
    I'm describing but if any of you guys do know of such a feature it
    would be useful in future to know - one of the reaons I was having
    problems this time was fully appreciating just what the query was I
    was submitting.

    I should just say before you think I'm nuts that the 'real' query was
    a good deal more complex (and had more substituted parameters) than
    the simple one which I created to ask the question I did.

    Anyway thanks again for all your help.

    regards

    richard shea.
    Richard Shea, Feb 26, 2004
    #4
  5. Richard Shea

    bobb Guest

    "Richard Shea" <> wrote in message
    news:...
    > Hi - I'm sorry I haven't responded before I got a cold earlier this
    > week and it's kind of knocked me sideways. Reading the replies I
    > realised I had done something fundamentally wrong and I was able to
    > use them as a basis for getting it to work correctly so thanks very
    > much to all of you for your help.
    >
    > There is one thing about the whole business which I find a bit
    > difficult - it would be nice if after you have executed the query you
    > were able to actually view the query (with substituted parameters) as
    > a string to ensure that your query was what it thought it was. I
    > understand that mySQLdb is really a wrapper around the C API for
    > MySQL. I've taken a look at that and I can't find anything like what
    > I'm describing but if any of you guys do know of such a feature it
    > would be useful in future to know - one of the reaons I was having
    > problems this time was fully appreciating just what the query was I
    > was submitting.
    >

    Like this?

    print "delete from " + str(t) + " where " + str(col) + " = " +str(num) +
    ";"
    delete from table where id = 1;
    ???

    > I should just say before you think I'm nuts that the 'real' query was
    > a good deal more complex (and had more substituted parameters) than
    > the simple one which I created to ask the question I did.
    >
    > Anyway thanks again for all your help.
    >
    > regards
    >
    > richard shea.
    bobb, Feb 29, 2004
    #5
    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. Holger Joukl
    Replies:
    0
    Views:
    269
    Holger Joukl
    Feb 23, 2004
  2. Marc Boeren
    Replies:
    2
    Views:
    266
    Steve Zatz
    Feb 23, 2004
  3. Replies:
    0
    Views:
    308
  4. John Salerno
    Replies:
    5
    Views:
    300
    John Salerno
    Aug 31, 2006
  5. John Nagle
    Replies:
    4
    Views:
    871
    John Nagle
    Nov 19, 2008
Loading...

Share This Page