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/ -- http://mail.python.org/mailman/listinfo/python-list