Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > MySQLDB - generating "...not in (1,2,3)" from Python list ?

Reply
Thread Tools

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

 
 
Richard Shea
Guest
Posts: n/a
 
      02-23-2004
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.
 
Reply With Quote
 
 
 
 
Peter Otten
Guest
Posts: n/a
 
      02-23-2004
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)'


 
Reply With Quote
 
 
 
 
vincent wehren
Guest
Posts: n/a
 
      02-23-2004

"Richard Shea" <(E-Mail Removed)> schrieb im Newsbeitrag
news:(E-Mail Removed) om...
> 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.



 
Reply With Quote
 
Richard Shea
Guest
Posts: n/a
 
      02-26-2004
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.
 
Reply With Quote
 
bobb
Guest
Posts: n/a
 
      02-29-2004

"Richard Shea" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
> 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.



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Will MySQLdb, the Python shim, be supported for Python 2.6 or 3.x? John Nagle Python 4 11-19-2008 08:38 PM
Re: Gateway to python-list is generating bounce messages. Sjoerd Mullender Python 4 09-13-2008 12:54 AM
MySQLdb module generating errors amaltasb@gmail.com Python 0 04-11-2006 12:58 PM
RE: MySQLDB - generating "...not in (1,2,3)" from Python list ? Marc Boeren Python 2 02-23-2004 06:20 PM
Antwort: MySQLDB - generating "...not in (1, 2, Holger Joukl Python 0 02-23-2004 10:08 AM



Advertisments