pyodbc sql 包含 0 个参数标记，但提供了 1 个参数''hy000'

本文介绍了pyodbc sql 包含 0 个参数标记，但提供了 1 个参数''hy000'的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在使用 Python 3.6、pyodbc，并连接到 SQL Server.

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.

代码如下:

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

当我在 SQL 中不带参数运行程序时，它可以工作.

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

当我尝试使用参数运行它时，出现错误.

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

推荐答案

通过 ODBC 的 SQL 语句中的参数是位置参数，并用 ? 标记.因此:

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

另外，最好避免将日期作为字符串传递.让 pyodbc 使用 Python 的日期时间来处理:

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.

这篇关于pyodbc sql 包含 0 个参数标记，但提供了 1 个参数''hy000'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

HY000