SQL IN operator using pyodbc and SQL Server
I'm using pyodbc to query to an SQL Server database
import datetime
import pyodbc
conn = pyodbc.connect("Driver={SQL Server};Server='dbserver',Database='db',
TrustedConnection=Yes")
cursor = conn.cursor()
ratings = ("PG-13", "PG", "G")
st_dt = datetime(2010, 1, 1)
end_dt = datetime(2010, 12, 31)
cursor.execute("""Select title, director, producer From movies
Where rating In ? And release_dt Between ? And ?""",
ratings, str(st_dt), str(end_dt))
but am receiving the error below. Does the tuple parameter need to be handled in a different way? Is there a better way to structure this query?
('42000', "[42000] [Microsoft][ODBC SQL 开发者_开发问答Server Driver][SQL Server]Line 9:
Incorrect syntax near '@P1'. (170) (SQLExecDirectW);
[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Statement(s) could not be prepared. (8180)")
UPDATE:
I was able to get this query to work using the string formatting operator, which isn't ideal as it introduces security concerns.
import datetime
import pyodbc
conn = pyodbc.connect("Driver={SQL Server};Server='dbserver',Database='db',
TrustedConnection=Yes")
cursor = conn.cursor()
ratings = ("PG-13", "PG", "G")
st_dt = datetime(2010, 1, 1)
end_dt = datetime(2010, 12, 31)
cursor.execute("""Select title, director, producer From movies
Where rating In %s And release_dt Between '%s' And '%s'""" %
(ratings, st_dt, end_dt))
To expand on Larry's second option - dynamically creating a parameterized string, I used the following successfully:
placeholders = ",".join("?" * len(code_list))
sql = "delete from dbo.Results where RESULT_ID = ? AND CODE IN (%s)" % placeholders
params = [result_id]
params.extend(code_list)
cursor.execute(sql, params)
Gives the following SQL with the appropriate parameters:
delete from dbo.Results where RESULT_ID = ? AND CODE IN (?,?,?)
You cannot parameterize multiple values in an IN ()
clause using a single string parameter. The only way to accomplish that is:
String substitution (as you did).
Build a parameterized query in the form
IN (?, ?, . . ., ?)
and then pass in a separate parameter for each place holder. I'm not an expert at Python to ODBC but I imagine that this is particularly easy to do in a language like Python. This is safer because you get the full value of parameterization.
To expand on Larry and geographika's answers:
ratings = ('PG-13', 'PG', 'G')
st_dt = datetime(2010, 1, 1)
end_dt = datetime(2010, 12, 31)
placeholders = ', '.join('?' * len(ratings))
vars = (*ratings, st_dt, end_dt)
query = '''
select title, director, producer
from movies
where rating in (%s)
and release_dt between ? and ?
''' % placeholders
cursor.execute(query, vars)
With the placeholder, this will return a query of:
select title, director, producer
from movies
where rating in (?, ?, ?)
and release_dt between ? and ?
If you pass in ratings
, it'll attempt to fit all of its items into one ?
. However, if we pass in *ratings
, and each item in ratings
will take its place in the in()
clause. Thus, we pass the tuple (*ratings, st_dt, end_dt)
to cursor.execute()
.
The problem is your tuple. The ODBC connection is expecting a string to construct the query and you are sending a python tuple. And remember that you have to get the string quoting correct. I'm assuming that the number of ratings you will be looking for varies. There is probably a better way, but my pyodbc tends to be simple and straightforward.
Try the following:
import datetime
import pyodbc
conn = pyodbc.connect("Driver={SQL Server};Server='dbserver',Database='db',
TrustedConnection=Yes")
def List2SQLList(items):
sqllist = "%s" % "\",\"".join(items)
return sqllist
cursor = conn.cursor()
ratings = ("PG-13", "PG", "G")
st_dt = datetime(2010, 1, 1)
end_dt = datetime(2010, 12, 31)
cursor.execute("""Select title, director, producer From movies
Where rating In (?) And release_dt Between ? And ?""",
List2SQLList(ratings), str(st_dt), str(end_dt))
精彩评论