python - pyodbc 调用带有参数名称的存储过程;数据类型问题
问题描述
运行 cursor_insert.execute(sql,params) 时,我不断收到“[SQL 错误] 将数据类型 nvarchar 转换为浮点数时出错”,即使我的源和目标数据库表将数据定义为浮点数、nvarchar 和 nvarchar 以及我的存储过程.
是否将我的参数设置为一个名为“params”的新变量,从而导致数据类型发生这种变化?如果是这样,我该如何解决它?(在阅读一些 Python 文档时,它不应该改变数据类型,对吗?)
# Create cursor associated with connection
cursor=conn.cursor()
cursor_select = conn.cursor()
cursor_insert = conn.cursor()
if conn:
print('***** Connected to DCPWDBS289 *****')
select_str="SELECT TOP 5 Incident_ID,Incident_Type,Priority FROM
incidents_all WHERE incidents_all.Status NOT IN ('Closed','Resolved')"
cursor_select.execute(select_str)
while True:
row = cursor_select.fetchone()
if not row:
break
print(' Row: ', row)
IncIncident_ID = row[0] # Float
IncIncident_Type = row[1] # Str
IncPriority = row[2] # Str
sql = """EXEC ITSM.dbo.ITSM_LOAD @IncIncident_ID=?,
@IncIncident_Type=?,@IncPriority=?"""
params = ('IncIncident_ID','IncIncident_Type','IncPriority')
cursor_insert.execute(sql,params)
del cursor_insert
cursor.commit()
conn.close()
解决方案
您没有传递parameter
值,而是传递了字符串文字,试试这个:
# Create cursor associated with connection
cursor=conn.cursor()
cursor_select = conn.cursor()
cursor_insert = conn.cursor()
if conn:
print('***** Connected to DCPWDBS289 *****')
select_str="SELECT TOP 5 Incident_ID,Incident_Type,Priority FROM
incidents_all WHERE incidents_all.Status NOT IN ('Closed','Resolved')"
cursor_select.execute(select_str)
while True:
row = cursor_select.fetchone()
if not row:
break
print(' Row: ', row)
IncIncident_ID = row[0] # Float
IncIncident_Type = row[1] # Str
IncPriority = row[2] # Str
sql = """EXEC ITSM.dbo.ITSM_LOAD @IncIncident_ID=?,
@IncIncident_Type=?,@IncPriority=?"""
params = (IncIncident_ID, IncIncident_Type, IncPriority)
cursor_insert.execute(sql,params)
del cursor_insert
cursor.commit()
conn.close()
推荐阅读
- r - 如何使用带有 R 的“每个”函数命名众多列?
- javascript - React Apollo graphql 浓缩器不工作
- r - 通过R中的字符串匹配重命名数据框列
- python - 如何修复重复键值违反唯一约束并在 django 管理面板中自定义“添加用户”部分?
- sum - Prisma - 总和 - 超过 350'000 行
- python - 如何找到拆分数组的其他值的组均值?
- crystal-reports - 水晶报表的开始日期和结束日期
- javascript - 三元运算符未按预期工作
- angular - HTTP 响应没有被解析为字符串数组
- android - 如何连接套接字服务器?