pyodbc insert into sql

Question

I use a MS SQL express db. I can connect and fetch data. But inserting data does not work:

cursor.execute("insert into [mydb].[dbo].[ConvertToolLog] ([Message]) values('test')")

I get no error but nothing is inserted into the table. Directly after I fetch the data the inserted row is fetched. But nothing is saved.

In MS SQL Server Management Studio the insertion does work.

Answer 1

You need to commit the data. Each SQL command is in a transaction and the transaction must be committed to write the transaction to the SQL Server so that it can be read by other SQL commands.

Under MS SQL Server Management Studio the default is to allow auto-commit which means each SQL command immediately works and you cannot rollback.

The insert example in the pyodbc Getting Started document is

cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
cnxn.commit()

or better using parameters

cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
cnxn.commit()

As the document says

Note the calls to cnxn.commit(). You must call commit or your changes will be lost! When the connection is closed, any pending changes will be rolled back. This makes error recovery very easy, but you must remember to call commit.