pyodbc the sql contains 0 parameter markers but 1 parameters were supplied' 'hy000'

Question

I am using Python 3.6, pyodbc, and connect to SQL Server.

I am trying make connection to a database, then creating a query with parameters.

Here is the code:

import sys
import pyodbc

# connection parameters
nHost = 'host'
nBase = 'base'
nUser = 'user'
nPasw = 'pass'

# make connection start
def sqlconnect(nHost,nBase,nUser,nPasw):
    try:
        return pyodbc.connect('DRIVER={SQL Server};SERVER='+nHost+';DATABASE='+nBase+';UID='+nUser+';PWD='+nPasw)
        print("connection successfull")
    except:
        print ("connection failed check authorization parameters")  
con = sqlconnect(nHost,nBase,nUser,nPasw)
cursor = con.cursor()
# make connection stop

# if run WITHOUT parameters THEN everything is OK   
ask = input ('Go WITHOUT parameters y/n ?')
if ask == 'y':
    # SQL without parameters start
    res = cursor.execute('''
    SELECT * FROM TABLE 
    WHERE TABLE.TIMESTAMP BETWEEN '2017-03-01T00:00:00.000' AND '2017-03-01T01:00:00.000'
    ''')
    # SQL without parameters stop

    # print result to console start
    row = res.fetchone()
    while row:
        print (row)
        row = res.fetchone()
    # print result to console stop

# if run WITH parameters THEN ERROR
ask = input ('Go WITH parameters y/n ?') 
if ask == 'y':

    # parameters start
    STARTDATE = "'2017-03-01T00:00:00.000'"
    ENDDATE = "'2017-03-01T01:00:00.000'"
    # parameters end

    # SQL with parameters start
    res = cursor.execute('''
    SELECT * FROM TABLE 
    WHERE TABLE.TIMESTAMP BETWEEN :STARTDATE AND :ENDDATE
    ''', {"STARTDATE": STARTDATE, "ENDDATE": ENDDATE})
    # SQL with parameters stop

    # print result to console start
    row = res.fetchone()
    while row:
        print (row)
        row = res.fetchone()
    # print result to console stop

When I run the program without parameters in SQL, it works.

When I try running it with parameters, an error occurred.

Not sure if pyodbc supports named parameters. At least didn't earlier — Sami Kuhmonen, Apr 19 '17 at 09:29
Possible duplicate of [Does pyodbc support any form of named parameters?](http://stackoverflow.com/questions/32748982/does-pyodbc-support-any-form-of-named-parameters) — m13r, Apr 19 '17 at 10:04

score 35 · Answer 1 · edited May 23 '17 at 12:25

35

Parameters in an SQL statement via ODBC are positional, and marked by a ?. Thus:

# SQL with parameters start
res = cursor.execute('''
SELECT * FROM TABLE 
WHERE TABLE.TIMESTAMP BETWEEN ? AND ?
''', STARTDATE, ENDDATE)
# SQL with parameters stop

Plus, it's better to avoid passing dates as strings. Let pyodbc take care of that using Python's datetime:

from datetime import datetime
...
STARTDATE = datetime(year=2017, month=3, day=1)
ENDDATE = datetime(year=2017, month=3, day=1, hour=0, minute=0, second=1)

then just pass the parameters as above. If you prefer string parsing, see this answer.

edited May 23 '17 at 12:25

Community

1
1

answered Apr 19 '17 at 09:34

themiurge

1,619
17
21

Thank you! I think it is right way, but i have next error: pyodbc .DataError: <'22007', '[22007]' – Aleks Apr 19 '17 at 09:57
1

It's an Invalid datetime format error (see https://learn.microsoft.com/en-us/sql/odbc/reference/appendixes/appendix-a-odbc-error-codes). I thought this might happen. You should try using a `datetime` like this: `from datetime import datetime ... STARTDATE = datetime(year=2017, month=1, ...) ` – themiurge Apr 19 '17 at 10:02
Thank you very much! I will try it now! – Aleks Apr 19 '17 at 11:17

score 6 · Answer 2 · edited Jan 16 '19 at 10:16

6

If you're trying to use pd.to_sql() like me I fixed the problem by passing a parameter called chunksize.

df.to_sql("tableName", engine ,if_exists='append', chunksize=50)

hope this helps

edited Jan 16 '19 at 10:16

Harsha Biyani

7,049
9
37
61

answered Jan 16 '19 at 09:57

Kevin Ruder

61
1
5

score 4 · Answer 3 · answered Apr 20 '17 at 09:25

i tryied and have a lot of different errors: 42000, 22007, 07002 and others

The work version is bellow:

import sys
import pyodbc
import datetime

# connection parameters
nHost = 'host'
nBase = 'DBname'
nUser = 'user'
nPasw = 'pass'

# make connection start
def sqlconnect(nHost,nBase,nUser,nPasw):
    try:
        return pyodbc.connect('DRIVER={SQL Server};SERVER='+nHost+';DATABASE='+nBase+';UID='+nUser+';PWD='+nPasw)
    except:
        print ("connection failed check authorization parameters")  
con = sqlconnect(nHost,nBase,nUser,nPasw)
cursor = con.cursor()
# make connection stop

STARTDATE = '11/2/2017'
ENDDATE = '12/2/2017'
params = (STARTDATE, ENDDATE)

# SQL with parameters start
sql = ('''
SELECT * FROM TABLE 
WHERE TABLE.TIMESTAMP BETWEEN CAST(? as datetime) AND CAST(? as datetime)
''')
# SQL with parameters stop

# print result to console start
query = cursor.execute(sql, params)
row = query.fetchone()
while row:
    print (row)
    row = query.fetchone()
# print result to console stop  
say = input ('everething is ok, you can close console')

score 0 · Answer 4 · edited Sep 09 '22 at 06:33

0

I fixed this issue with code if you are using values through csv.

for i, row in read_csv_data.iterrows():

cursor.execute('INSERT INTO ' + self.schema + '.' + self.table + '(first_name, last_name, email, ssn, mobile) VALUES (?,?,?,?,?)', tuple(row))

edited Sep 09 '22 at 06:33

Ran A

746
3
7
19

answered Sep 03 '22 at 09:23

Aaditi Pawar

11

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/32636239) – Deenadhayalan Manoharan Sep 08 '22 at 09:26

score -2 · Answer 5 · answered Jul 10 '18 at 10:20

-2

I had a similar issue. Saw that downgrading the version of PyODBC to 4.0.6 and SQLAlchemy to 1.2.9 fixed the error,using Python 3.6

answered Jul 10 '18 at 10:20

Poo22

1
1

pyodbc the sql contains 0 parameter markers but 1 parameters were supplied' 'hy000'

5 Answers5

Linked