Inserting through pymssql but no rows appear in th

Question

Im quite new to python and im trying to write a script that puts values into a SQL database.

its a simple 2 column table that looks like this:

CREATE TABLE [dbo].[pythonInsertTest](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [value] [varchar](50) NULL

I tried doing a select query from python and that worked! So the connection or anything of that sort is not the problem. but when i'm inserting into the table with the following code:

import pymssql
conn = pymssql.connect(server='XXXX', user='XXXX', password='XXXX', database='XXXX')
cursor = conn.cursor()
cursor.execute("insert into pythonInsertTest(value) OUTPUT INSERTED.ID VALUES('test')")
row = cursor.fetchone()
while row:
    print "Inserted Product ID : " +str(row[0])
    row = cursor.fetchone()

the response is:

$? && exit 1
Inserted Product ID : 20
Exit status: 0

However if i look in my sql manager and select all the rows in said table, the row i just added is not in there... But when i manually insert a row trough an SQL query in the manager its added.

Thing to note that it did skip the ID the id that was "inserted" trough my python script.

Anyone seen this before or knows what to do?

Answer 1

Ha, I've banged my head on this a few times as well. As far as I can tell, you are missing a commit statement. As per this example, add a conn.commit(), and hopefully you will be golden.