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

[vincent wehren]

"Richard Shea" <richardshea at fastmail.fm> schrieb im Newsbeitrag
news:282f826a.0402230152.253315ca at posting.google.com...
> 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.