Reading from SQL Server with params: pandas (or pyodbc) not functioning properly

If you use prepared statements in your SQL, you can't put multiple values for one placeholder/parameter/bind variable!

Beside this you can use placeholders/parameters/bind variables only in place of literals, you can't use it for part of SQL statement which is not a literal.

In your case you tried to put ( and ) which is part of SQL, but not a literal as parameters.

Using parameters/prepared statements/bind variable will also protect you from some SQL injections.

that said, try to change your code as follows:

change

INSERT INTO @adults VALUES ?

to

INSERT INTO @adults VALUES (?)

and

df = pd.read_sql(sql=sql_, con=cnxn, params=['(30)'])

to

df = pd.read_sql(sql=sql_, con=cnxn, params=['30'])

UPDATE:

you can prepare your SQL this way:

In [9]: vals = [20,30,40]In [32]: valsOut[32]: [20, 30, 40]In [33]: ' (?)' * len(vals)Out[33]: ' (?) (?) (?)'

then:

In [14]: sql_ = """DECLARE @adults table (Id int)   ....: INSERT INTO @adults VALUES {}   ....:   ....: SELECT [date],   ....: """In [15]: sql_.format(' (?)' * len(vals))Out[15]: 'DECLARE @adults table (Id int)\nINSERT INTO @adults VALUES (?) (?) (?)\n\nSELECT [date],\n'

Pay attention at generated (?) (?) (?)

and finally call your SQL:

df = pd.read_sql(sql=sql_.format(' (?)' * len(vals)), con=cnxn, params=vals)