Inserting NULL values with pymssql

[Steve Holden]

Jayson Barley wrote:
> I am attempting to insert NULL values into a database. I have tried to 
> do this in multiple ways without any success, see below, and haven't 
> been able to find anything through Google to help out. I am hoping that 
> I am just overlooking something or that it is a rookie mistake. Below is 
> a test I came up with to prove this. I am on Windows XP using Python 2.5 
> and pymssql-0.8.0-py2.5.
> 
> CREATE TABLE [dbo].[Test](
>  [test] [varchar](50) NULL,
>  [inttest] [int] NULL
> ) ON [PRIMARY]
>  
> 1.
> import pymssql
>  
> TestDB = 
> pymssql.connect(host='Test',user='test',password='test',database='test')
> cursor = TestDB.cursor()
> query = """INSERT INTO test.dbo.test VALUES (?, ?);"""
> cursor.execute(query,('',''))
> Returns
>  
> Traceback (most recent call last):
>   File "C:\Test\db_info\test.py", line 6, in <module>
>     cursor.execute(query,('',''))
>   File "C:\Python25\lib\site-packages\pymssql.py", line 126, in execute
>     self.executemany(operation, (params,))
>   File "C:\Python25\lib\site-packages\pymssql.py", line 152, in executemany
>     raise DatabaseError, "internal error: %s" % self.__source.errmsg()
> pymssql.DatabaseError: internal error: None
>  
> 2.
> import pymssql
>  
> TestDB = 
> pymssql.connect(host='Test',user='test',password='test',database='test')
> cursor = TestDB.cursor()
> query = """INSERT INTO test.dbo.test VALUES (?, ?);"""
> cursor.execute(query,('',None))
>  
> Returns
>  
> Traceback (most recent call last):
>   File "C:\Test\db_info\test.py", line 8, in <module>
>     cursor.execute(query,('',None))
>   File "C:\Python25\lib\site-packages\pymssql.py", line 126, in execute
>     self.executemany(operation, (params,))
>   File "C:\Python25\lib\site-packages\pymssql.py", line 152, in executemany
>     raise DatabaseError, "internal error: %s" % self.__source.errmsg()
> pymssql.DatabaseError: internal error: None
>  
> 3.
> import pymssql
>  
> TestDB = 
> pymssql.connect(host='Test',user='test',password='test',database='test')
> cursor = TestDB.cursor()
> query = """INSERT INTO test.dbo.test VALUES (?, ?);"""
> cursor.execute(query,('','NULL'))
> Returns
>  
> Traceback (most recent call last):
>   File "C:\Test\db_info\test.py", line 6, in <module>
>     cursor.execute(query,('','NULL'))
>   File "C:\Python25\lib\site-packages\pymssql.py", line 126, in execute
>     self.executemany(operation, (params,))
>   File "C:\Python25\lib\site-packages\pymssql.py", line 152, in executemany
>     raise DatabaseError, "internal error: %s" % self.__source.errmsg()
> pymssql.DatabaseError: internal error: None
>  
> I am wondering what I am missing that is preventing me from inserting a 
> NULL. I can perform the INSERT in Server Management Studio the problem 
> only exists in the Python script. If anyone can point me to a resource 
> that I may have missed or a flaw in my script I would really appreciate it.
>  
What you want is two NULL data values as the second argument to execute():

cursor.execute(query,(None, None))

> I also forgot to mention that this...
>  
> import pymssql
>  
> TestDB = pymssql.connect(host='Test',user='test',password='test',database='test')
> cursor = TestDB.cursor()
> query = """INSERT INTO test.dbo.test (test) VALUES ('%s');"""
> cursor.execute(query,(None))
>  
> works. While
>  
That's a very naughty database module. It should complain, since you 
didn't provide a tuple as the second argument to execute().

> import pymssql
>  
> TestDB = pymssql.connect(host='Test',user='test',password='test',database='test')
> cursor = TestDB.cursor()
> query = """INSERT INTO test.dbo.test (inttest) VALUES ('%d');"""
> cursor.execute(query,(None))
>  
> doesn't work and returns
>  
> Traceback (most recent call last):
>   File "C:\Test\db_info\test.py", line 6, in <module>
>     cursor.execute(query,(None))
>   File "C:\Python25\lib\site-packages\pymssql.py", line 126, in execute
>     self.executemany(operation, (params,))
>   File "C:\Python25\lib\site-packages\pymssql.py", line 152, in executemany
>     raise DatabaseError, "internal error: %s" % self.__source.errmsg()
> pymssql.DatabaseError: internal error: SQL Server message 245, severity 16, state 1, line 1:
> Conversion failed when converting the varchar value '%d' to data type int.
> DB-Lib error message 10007, severity 5:
> General SQL Server error: Check messages from the SQL Server.

I don't know what pymssql's paramstyle is, but I suspect it requires a 
"%s" far all parameters regardless of their data type. That's SQL Server 
complaining about the %d, pymssql having failed to recognize it as a 
parameter marker and passed it through to the engine.

regards
  Steve
-- 
Steve Holden        +1 571 484 6266   +1 800 494 3119
Holden Web LLC              http://www.holdenweb.com/