Passing nulls to stored procedure in Python

问题:

I'm using Python to call a stored procedure in my MSSQL database, but it doesn't like the "None" value passed. Is this perhaps a bug in pymssql? Or is there some way to ensure that "None" is converted to NULL? I need to be able to pass NULL.

example code:

cn = pymssql.connect(my connection info)
cur = cn.cursor()
params = range(2)
params[0] = 1
params[1] = None # this needs to pass as NULL
cur.callproc('mystoredproc', params)

回答1:

It looks like it's a known issue.

Update: I found a solution. While the pymssql module does need to be fixed, I can use the _mssql module that comes with it and call the lower level routines.

Example:

import _mssql
conn = _mssql.connect(connection info, syntax is slightly different than pymssql)
proc = conn.init_procedure('mystoredproc')
proc.bind(1, _mssql.SQLINT4, name='@firstparam')
proc.bind(None, _mssql.SQLINT4, name='@secondparam', null=True)
proc.execute()

The "null=True" setting is required to allow this parameter to accept nulls, and the "None" will get converted to a NULL.