Python pyodbc sql 包含 0 个参数标记，但提供了 1 个参数''hy000'
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43491381/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pyodbc the sql contains 0 parameter markers but 1 parameters were supplied' 'hy000'
提问by Aleks
I am using Python 3.6, pyodbc, and connect to SQL Server.
我正在使用 Python 3.6、pyodbc，并连接到 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.
当我在 SQL 中不带参数运行程序时，它可以工作。
When I try running it with parameters, an error occurred.
当我尝试使用参数运行它时，发生了错误。
回答by themiurge
Parameters in an SQL statement via ODBC are positional, and marked by a ?. Thus:
通过 ODBC 的 SQL 语句中的参数是位置参数，并由?. 因此：
# 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:
另外，最好避免将日期作为字符串传递。让 pyodbc 使用 Python 的日期时间来处理：
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.
然后像上面一样传递参数。如果您更喜欢字符串解析，请参阅此答案。
回答by Kevin Ruder
If you're trying to use pd.to_sql()like me I fixed the problem by passing a parameter called chunksize.
如果你想像pd.to_sql()我一样使用我通过传递一个名为 chunksize 的参数来解决这个问题。
df.to_sql("tableName", engine ,if_exists='append', chunksize=50)
hope this helps
希望这可以帮助
回答by Aleks
i tryied and have a lot of different errors: 42000, 22007, 07002 and others
我尝试了很多不同的错误：42000、22007、07002 和其他
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')
回答by Poo22
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
我有一个类似的问题。看到将 PyODBC 的版本降级到 4.0.6 和 SQLAlchemy 到 1.2.9 修复了错误，使用 Python 3.6
