使用 PyODBC 将参数传递给存储过程 [英] Passing Parameters to Stored Procedures using PyODBC

问题描述

我使用 pyODBC 连接到 SQL Server 2005 express 数据库.我在 SQLServer express 中创建了一个存储过程，它采用 2 个字符串参数，例如 stored_proc(inpu1, input2) 这些参数的类型为 datetime.我已经使用管理工作室测试了存储过程，它确实返回了适当的结果.但是，当我尝试从 python(我使用 Eclipse)调用存储过程时，我收到错误消息.

<块引用>

pyodbc.DataError: ('22018', '[22018] [Microsoft][SQL Native Client] 转换规范的字符值无效 (0) (SQLExecDirectW)')2/9/2011 12:00:03 2/9/2011 12:20:03

我调用的函数如下:

def GetAlarmFrequencyTime(tstart,tend):打印 tstart，趋向参数=(tstart，tend)local_cursor=conn.cursor()local_cursor.execute("{call dbo.GetAlarmEventHistoryFrequency_TimeRange(tstart,tend)}")resultset_rows=local_cursor.fetchall()打印 "There are" , len(resultset_rows), "records"对于resultset_rows 中的single_row:打印|",single_row[0], "|",single_row[1],"|"local_cursor.close()

引起问题的那一行是

<块引用>

local_cursor.execute("{call dbo.GetAlarmEventHistoryFrequency_TimeRange(tstart,tend)}")

任何人都可以帮助我了解如何将多个参数传递给使用 pyODBC 调用的存储过程.我是否需要先转换 tstart 并倾向于日期时间格式，如果需要，如何转换?我已经验证了 strptime，但即使这样也没有成功，尽管我可能使用错了

解决方案

我终于明白了，可以正常工作而不会崩溃

<块引用>

local_cursor.execute("{call dbo.GetAlarmEventHistoryFrequency_TimeRange(tstart,tend)}")

是错误的语法.应该是

<块引用>

local_cursor.execute("{call dbo.GetAlarmEventHistoryFrequency_TimeRange(?,?)}",(tstart),(tend))

要特别注意特殊符号 {} 和参数传入的方式 (tstart),(tend) local_cursor.execute("*{*call dbo.GetAlarmEventHistoryFrequency_TimeRange*(?,?)}",(tstart),(tend)*)

I'm using pyODBC to connect to a SQL Server 2005 express database. I've created a stored procedure in SQLServer express that takes 2 string parameters e.g stored_proc(inpu1, input2) these parameters are of type datetime. I have tested the stored proc using management studio and it does return an appropriate result. However when i try to call the stored procedure from python(i'm using Eclipse) I get the error.

pyodbc.DataError: ('22018', '[22018] [Microsoft][SQL Native Client]Invalid character value for cast specification (0) (SQLExecDirectW)')2/9/2011 12:00:03 2/9/2011 12:20:03

The function i'm calling is as follows:

def GetAlarmFrequencyTime(tstart,tend): 

print tstart, tend  
arguments=(tstart,tend)
local_cursor=conn.cursor()
local_cursor.execute("{call dbo.GetAlarmEventHistoryFrequency_TimeRange(tstart,tend)}")

resultset_rows=local_cursor.fetchall()
print "There are" , len(resultset_rows), "records"
for single_row in resultset_rows:
    print "|" ,single_row[0], "|" ,single_row[1],"|" 

local_cursor.close()

The line that's causing the trouble is

local_cursor.execute("{call dbo.GetAlarmEventHistoryFrequency_TimeRange(tstart,tend)}") 

can anyone help me understand how I can pass multiple parameters to a stored procedure called using pyODBC. Do I need to convert tstart, and tend to a datetime format first, if so how? I have trued strptime, but even that's not succeeded, though i may be using it wrong

解决方案

I finally got it, to work without crashing

local_cursor.execute("{call dbo.GetAlarmEventHistoryFrequency_TimeRange(tstart,tend)}")

is the wrong syntax. It should be

local_cursor.execute("{call dbo.GetAlarmEventHistoryFrequency_TimeRange(?,?)}",(tstart),(tend))

Special attention should be made to the special symbols {} and the way the parameters are passed in (tstart),(tend) local_cursor.execute("*{*call dbo.GetAlarmEventHistoryFrequency_TimeRange*(?,?)}",(tstart),(tend)*)

这篇关于使用 PyODBC 将参数传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！