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. 4 1637
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)'
"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.
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" <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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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
|
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)'
>>>
|
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...
|
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
|
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
| |
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),...
|
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...
|
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...
|
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...
|
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. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
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...
|
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
| |
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...
| | |