python - 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 example is from pyodbc Getting Started document

First opening the database and set up a cursor

import pyodbc

# Specifying the ODBC driver, server name, database, etc. directly
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass')

# Create a cursor from the connection
cursor = cnxn.cursor()

The insert example in the document is then

# Do the insert
cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
#commit the transaction
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.