使用 PyODBC 将参数传递给存储过程

发布于 2024-12-10 22:53:55 字数 1131 浏览 1 评论 0原文

我正在使用 pyODBC 连接到 SQL Server 2005 Express 数据库。我在 SQLServer Express 中创建了一个存储过程，它接受 2 个字符串参数，例如stored_proc(inpu1, input2)，这些参数的类型为日期时间。我已经使用 Management Studio 测试了存储过程，它确实返回了适当的结果。但是，当我尝试从 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): 

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()

导致问题的行是

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

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

原文

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

分享到QQ

分享到微博