Inserting NULL values with pymssql

[Jayson Barley]

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 
 
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.
 
Jayson

________________________________

From: python-list-bounces+jayson.barley=webtrends.com at python.org
[mailto:python-list-bounces+jayson.barley=webtrends.com at python.org] On
Behalf Of Jayson Barley
Sent: Thursday, February 21, 2008 1:20 PM
To: python-list at python.org
Subject: Inserting NULL values with pymssql


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.
 
Jayson
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20080221/35315d57/attachment-0001.html>