473,554 Members | 2,841 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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.
Jul 18 '05 #1
4 1636
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)'
Jul 18 '05 #2

"Richard Shea" <ri*********@fa stmail.fm> schrieb im Newsbeitrag
news:28******** *************** ***@posting.goo gle.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.

Jul 18 '05 #3
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.
Jul 18 '05 #4

"Richard Shea" <ri*********@fa stmail.fm> wrote in message
news:28******** *************** ***@posting.goo gle.com...
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.

Jul 18 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1452
by: Posadas, Dennis | last post by:
I don't want to have to compile python, but I need one ready to support unicode that includes CJK. Dennis
0
1449
by: Holger Joukl | last post by:
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)' >>>
2
2025
by: pleaseSeeFooter | last post by:
Pythonagons, I am considering using (building) a client-side application, rather than a browser, for an Internet application. I am aware there are a few resourcesout there like XUL and various Java clients or classes to build one with. Has anyone developed a frame work for this sort of thing in Python? I am looking at the Windows platform...
13
2372
by: Michele Simionato | last post by:
What is the recommended way of generating HTML from Python? I know of HTMLGen and of few recipes in the Cookbook, but is there something which is more or less standard? Also, are there plans to include a module for HTML generation in the standard library? I really would like to see some standardization in this area. Michele Simionato
2
9142
by: GujuBoy | last post by:
is there a built-in function that does a "checksum" on a file...basicly counts the bytes and computes a 16-bit checksum for each given FILE. this is the like the "sum" command in unix
28
2967
by: john_sips_tea | last post by:
Just tried Ruby over the past two days. I won't bore you with the reasons I didn't like it, however one thing really struck me about it that I think we (the Python community) can learn from. Ruby has ... an issue with docs. That is to say, there are almost none. Well, actually, there are some. For example, the "PickAxe" book (google it),...
8
3336
by: Mike Meng | last post by:
Hi all, I just finished reading Learning Python 3rd ed, and am doing my first Python application, which retrieves and process text and XML documents from Web. Python helped me to write the application in a few hours, I'm very happy with its productivity. But the performance is not satisfactory. I decide to optimized it in Python before...
71
3247
by: Jack | last post by:
I understand that the standard Python distribution is considered the C-Python. Howerver, the current C-Python is really a combination of C and Python implementation. There are about 2000 Python files included in the Windows version of Python distribution. I'm not sure how much of the C-Python is implemented in C but I think the more modules...
18
2368
by: Grant Edwards | last post by:
Could whoever is responsible for the gateway that is grabbing my postings off of Usenet and e-mailing them out please fix the headers in the mail messages so that I don't get the bounce messages? While you're at it, might as well fix it for everybody else too. ;) Its a bit rude to send out mass e-mail messages with headers faked up so...
0
7521
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7802
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
6145
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5155
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3560
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3548
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2020
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1134
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
841
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.