8

I have troubles using a simple sql statement with the operator IN through pymssql.

Here is a sample :

import pymssql
conn = pymssql.connect(server='myserver', database='mydb')
cursor = conn.cursor()
req = "SELECT * FROM t1 where id in (%s)"
cursor.execute(req,  tuple(range(1,10)))
res = cursor.fetchall()

Surprisingly only the first id is returned and I can't figure out why. Does anyone encounter the same behavior ?

Alex
  • 816
  • 5
  • 14
  • Have never used pymssql, but for SQLite, this would require use of `cursor.executemany()` – Sam Cohen-Devries Sep 03 '15 at 20:05
  • I tried it and it returns the data corresponding to the last element of my tuple. – Alex Sep 03 '15 at 20:07
  • as i am not 100% sure, i will not yet post is as an answer. it looks like you are only passing `SELECT * FROM t1 where id in (1)`. You call `execute` with the tuple but the string only has one formatter, i know that this is the way for tuples but maybe pymsql interprets it differently – Lawrence Benson Sep 03 '15 at 20:12
  • @Alex if cursor.executemany() returns the last element and cursor.execute() returns the first, this means it is querying one values at a time. You might try concatenating the tuple and passing it as a string – Sam Cohen-Devries Sep 03 '15 at 20:15

2 Answers2

4

You're trying to pass nine ID values to the query and you only have one placeholder. You can get nine placeholders by doing this:

ids = range(1,10)
placeholders = ','.join('%s' for i in ids)
req = "SELECT * FROM t1 where id in ({})".format(placeholders)
cursor.execute(req, ids)
res = cursor.fetchall()

As an aside, you don't necessarily need a tuple here. A list will work fine.

mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
  • Thank you, this works. My only concern would be if I had a lot of ids but I'm ok for now. – Alex Sep 03 '15 at 21:13
1

It looks like you are only passing SELECT * FROM t1 where id in (1). You call execute with the tuple but the string only has one formatter. To pass all values, call execute like this:

cursor.execute(req,  (tuple(range(1,10)),))

This will pass the tuple as first argument to the string to format.

EDIT: Regarding the executeone/many() thing, if you call executemany and it returns the last instead of the first id, it seems that execute will run the query 10 times as it can format the string with 10 values. The last run will then return the last id.

Lawrence Benson
  • 1,398
  • 1
  • 16
  • 33
  • 2
    Thank you Lawrence, I tried it with the execute and it returns an error telling me that there is an incorrect syntax near ','. Full error : pymssql.ProgrammingError: (102, "Incorrect syntax near ','.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n") – Alex Sep 03 '15 at 21:06
  • The syntax is correct however when I use executemany but it retrieves only the first element. – Alex Sep 03 '15 at 21:07