pymssql query

[m.biddiscombe@gmail.com]

Hi,

I'm trying to use pymssql to execute a stored procedure. Currently, I
have an Excel spreadsheet that uses VBA in this manner:

Private Function CreateNewParrot (connDb As ADODB.Connectio n) As Long
Dim objCommand As ADODB.Command
Dim iParrot As Long
Dim bSuccess As Boolean

Set objCommand = CreateObject("A DODB.Command")
objCommand.Acti veConnection = connDb
objCommand.Comm andText = "create_new "
objCommand.Comm andType = adCmdStoredProc
objCommand.Para meters.Refresh
On Error Resume Next
Err.Clear
objCommand.Exec ute
bSuccess = (Err.Number = 0)
On Error GoTo 0
If (bSuccess) Then
If (IsNull(objComm and("@parrot")) ) Then
iParrot = 0
Else
iParrot = CLng(objCommand ("@parrot"))
End If
Else
iParrot = 0
End If
Set objCommand = Nothing
CreateNewParrot = iParrot
End Function

My attempts to translate this into a python script using pymssql have
so far been fruitless. Here is what I have tried:

>>import pymssql
>>con = pymssql.connect (host='blah', user='blah', password='blah' , database='blah' )
>>cur = con.cursor()
>>command = 'exec create_new_parr ot'
>>cur.execute(c ommand, '@parrot')

Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "C:\Program Files\Python\li b\site-packages\pymssq l.py", line
127, in execute
self.executeman y(operation, (params,))
File "C:\Program Files\Python\li b\site-packages\pymssq l.py", line
153, in executemany
raise DatabaseError, "internal error: %s" % self.__source.e rrmsg()
pymssql.Databas eError: internal error: SQL Server message 8114,
severity 16, state 5, procedure create_new_parr ot, line 0:
Error converting data type nvarchar to int.
DB-Lib error message 10007, severity 5:
General SQL Server error: Check messages from the SQL Server.

Any ideas?

Thanks.

<M>

[Tim Golden]

Re: pymssql query

m.biddiscombe@g mail.com wrote:

Hi,
>
I'm trying to use pymssql to execute a stored procedure. Currently, I
have an Excel spreadsheet that uses VBA in this manner:
>
Private Function CreateNewParrot (connDb As ADODB.Connectio n) As Long
Dim objCommand As ADODB.Command
Dim iParrot As Long
Dim bSuccess As Boolean
>
Set objCommand = CreateObject("A DODB.Command")
objCommand.Acti veConnection = connDb
objCommand.Comm andText = "create_new "
objCommand.Comm andType = adCmdStoredProc
objCommand.Para meters.Refresh
On Error Resume Next
Err.Clear
objCommand.Exec ute
bSuccess = (Err.Number = 0)
On Error GoTo 0
If (bSuccess) Then
If (IsNull(objComm and("@parrot")) ) Then
iParrot = 0
Else
iParrot = CLng(objCommand ("@parrot"))
End If
Else
iParrot = 0
End If
Set objCommand = Nothing
CreateNewParrot = iParrot
End Function

Depending on what you're after, why not transliterate
it into Python?

import win32com.client
command = win32com.client .Dispatch ("ADODB.Command ")

etc.

My attempts to translate this into a python script using pymssql have
so far been fruitless. Here is what I have tried:

>>>import pymssql
>>>con = pymssql.connect (host='blah', user='blah', password='blah' , database='blah' )
>>>cur = con.cursor()
>>>command = 'exec create_new_parr ot'
>>>cur.execute( command, '@parrot')

Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "C:\Program Files\Python\li b\site-packages\pymssq l.py", line
127, in execute
self.executeman y(operation, (params,))
File "C:\Program Files\Python\li b\site-packages\pymssq l.py", line
153, in executemany
raise DatabaseError, "internal error: %s" % self.__source.e rrmsg()
pymssql.Databas eError: internal error: SQL Server message 8114,
severity 16, state 5, procedure create_new_parr ot, line 0:
Error converting data type nvarchar to int.
DB-Lib error message 10007, severity 5:
General SQL Server error: Check messages from the SQL Server.

Well, I'm not connected to a SQL Server here (so this
is untested) but I don't believe pymssql handles
stored procedure calls differently from any other
SQL. Which is a problem here because, as far as I
can make out from your code above, @parrot is an
output parameter for the create_new stored proc.
Is that right? If it's an input-only param, then
just do the usual:

import pymssql
db = pymssql.connect (...)
q = db.cursor ()
q.execute (
"EXECUTE create_new @parrot = %s",
["parrot-thing"]
)

I'm not aware of any of the MSSQL dbapi
modules which allow for output parameters
in stored procedures. pyodbc (one of the most
recent entrants) tantalisingly offers a .callproc
but then comments "not yet supported". If the ADO
approach works, I'd use that if I were you!

TJG