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

[Richard Shea]

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.

 

[Peter Otten]

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 ?

'(1, 2, 3, 99)'

 

[vincent wehren]

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

 

[Richard Shea]

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.

 

[bobb]

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