I am working on a little project using pysqlite. It's going to be
exposed on the web, so I want to make sure I quote all incoming data
correctly. However, I've run into a brick wall trying to use parameters
to populate a query of the form "select * where col1 in ( ? )"
The naive approach doesn't work:
values=['foo', 'bar', 'baz']
sql = """select * where value in (?)"""
cu = cx.cursor()
cu.execute(sql, (values))
The code blows up because the cursor is expecting 1 arg and gets 3. I
tried joining the array members with a comma, and that didn't work.
I've also tried the equivalent with the named style, which pysqlite
also supports, but that didn't work either.
I can't find any documentation that demonstrates this kind of query.
Is there a way to do this? It seems a bit odd not to have a way to
escape this kind of query. 4 2311
saniac wrote:
I am working on a little project using pysqlite. It's going to be
exposed on the web, so I want to make sure I quote all incoming data
correctly. However, I've run into a brick wall trying to use parameters
to populate a query of the form "select * where col1 in ( ? )"
The naive approach doesn't work:
values=['foo', 'bar', 'baz']
sql = """select * where value in (?)"""
cu = cx.cursor()
cu.execute(sql, (values))
The code blows up because the cursor is expecting 1 arg and gets 3. I
tried joining the array members with a comma, and that didn't work.
I've also tried the equivalent with the named style, which pysqlite
also supports, but that didn't work either.
I can't find any documentation that demonstrates this kind of query.
Is there a way to do this? It seems a bit odd not to have a way to
escape this kind of query.
Well, you could try using a tuple whose single element is that
three-element tuple with your list if values:
cu.execute(sql, (values, ))
which I repsume is shat you really meant to do. Note, though, that not
all DB API modules will accept lists and/or tuples as data elements of
that kind, so you may be disappointed.
regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden
saniac wrote:
I am working on a little project using pysqlite. It's going to be
exposed on the web, so I want to make sure I quote all incoming data
correctly. However, I've run into a brick wall trying to use parameters
to populate a query of the form "select * where col1 in ( ? )"
The naive approach doesn't work:
values=['foo', 'bar', 'baz']
sql = """select * where value in (?)"""
cu = cx.cursor()
cu.execute(sql, (values))
The code blows up because the cursor is expecting 1 arg and gets 3.
I assume you mean 'select * from table where...'
Try this -
values=['foo', 'bar', 'baz']
sql = """select * from table where value in (?,?,?)"""
cu = cx.cursor()
cu.execute(sql, values)
It works with odbc from pywin32. I have not tried pysqlite.
If you want it to handle a variable number of values, you will have to
programmatically construct the sql statement with the appropriate
number of parameters.
HTH
Frank Millman
Frank Millman schrieb:
If you want it to handle a variable number of values, you will have to
programmatically construct the sql statement with the appropriate
number of parameters.
>>vals = (1,2,3,4,5) sql = "select * from table where value in ("+','.join("?"*len(vals))+")" print sql
'select * from table where value in (?,?,?,?,?)'
cheers
Paul
paul wrote:
Frank Millman schrieb:
If you want it to handle a variable number of values, you will have to
programmatically construct the sql statement with the appropriate
number of parameters.
Yes, I should have made it clear it was the variable part that was
hard.
>vals = (1,2,3,4,5) sql = "select * from table where value in ("+','.join("?"*len(vals))+")" print sql
'select * from table where value in (?,?,?,?,?)'
Argh, I have a scripting language and I'm not building up strings
dynamically? What an idiot.
Thanks, that's just what I needed. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: george young |
last post by:
I had developed the habit of using the neat python form:
if someinstance:
someinstance.memb()
because it seems cleaner than "if someinstance is not None".
{please no flames about "is not None"...
|
by: Mysooru |
last post by:
Hi All,
One of the ATL class.....
template <class Base>
class CComObject : public Base
{
public:
typedef Base _BaseClass;
|
by: Ian Lazarus |
last post by:
// why is the error message being generated?
// Microsoft Visual C/C++ 5.0
class Bar
{
public:
void Log(const char* p){ }
};
class Foo : public Bar
|
by: John A Grandy |
last post by:
Is there something special in XML about the name "Parameter" ... I am not
able to XPATH query for nodes named "Parameter"
<Root>
<Category CategoryID="1">
<Elements>
<Element...
|
by: Ondrej Spanel |
last post by:
The code below does not compile with .NET 2003, I get folowing error:
w:\c\Pokusy\delegTemplArg\delegTemplArg.cpp(11) : error C2993: 'float' :
illegal type for non-type template parameter 'x'
...
|
by: Niron kag |
last post by:
Hi,
I have a windows service, which is currently installed, on my local computer.
The problem is that when I look at the task manager, I see that the “Mem
Usage”, become bigger and bigger....
|
by: GS |
last post by:
If I want to find in the row of "codetable" with codeTblName like
"mypattern%" and Code like "abc%"
MyDataSet.codetable.select("codeTblName like 'mypattern%' and Code like
'abc%'")
does the...
|
by: Nemisis |
last post by:
Hi,
Is it possible to pass in an object and parameter into a function and
return it as a string.
i.e.
To make a call to the function i would put the following
Dim str as String =...
|
by: king kikapu |
last post by:
Hi to all,
i have started a month ago to seriously studying Python. I am now
looking at the databases stuff
and i want the opinion of more experienced Python programmers (than
me) at the...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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 projectplanning, coding, testing,...
|
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...
|
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...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |