pymssql query

[Tim Golden]

m.biddiscombe at gmail.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.Connection) As Long
>   Dim objCommand As ADODB.Command
>   Dim iParrot As Long
>   Dim bSuccess As Boolean
> 
>   Set objCommand = CreateObject("ADODB.Command")
>   objCommand.ActiveConnection = connDb
>   objCommand.CommandText = "create_new"
>   objCommand.CommandType = adCmdStoredProc
>   objCommand.Parameters.Refresh
>   On Error Resume Next
>   Err.Clear
>   objCommand.Execute
>   bSuccess = (Err.Number = 0)
>   On Error GoTo 0
>   If (bSuccess) Then
>     If (IsNull(objCommand("@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_parrot'
>>>> cur.execute(command, '@parrot')
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
>   File "C:\Program Files\Python\lib\site-packages\pymssql.py", line
> 127, in execute
>     self.executemany(operation, (params,))
>   File "C:\Program Files\Python\lib\site-packages\pymssql.py", line
> 153, in executemany
>     raise DatabaseError, "internal error: %s" % self.__source.errmsg()
> pymssql.DatabaseError: internal error: SQL Server message 8114,
> severity 16, state 5, procedure create_new_parrot, 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