0

I have a small script made in Python which has two functions. The first to send data into a table. The second can read the table.

When I call the function that triggers my insert query, the data is not saved in the database.

When I insert an identical query directly into SQL Server it works fine.

So my script is good and my query is good too. Firewall systems are properly configured.

So why data is not saved ?

The primary key of my table is an IDENTITY column. When I activate my insert function, the IDENTITY column still auto increments while no data is saved.

I give you my script :

enter image description here

Here my SQL Server :

enter image description here

The SQL query for creating my table

enter image description here

I try my best to find a solution, i need your help to understand my problem.

Jean-baptiste Briois
  • 47
  • 5
  • Missing commit after insert, https://learn.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver16#insert-a-row – sushanth Sep 04 '22 at 15:01
  • Better idea is to set `autocommit=true` then you don't have to think about it. – Charlieface Sep 04 '22 at 16:07

2 Answers2

2

As a quick fix, add commit after execute:

...
cursor.execute(sql)
cursor.connection.commit()

But I would also advise you to keep as little connections as possible. In your current code you create new connection for each operation.

qaziqarta
  • 1,782
  • 1
  • 4
  • 11
1

After inserting with your cursor you need to commit your inserts with connection.commit()

As qaziqarta pointed out it is best practice to not open a new connection everytime you are trying to insert or read something from the database.

You should initialize your connection once at the beginning and close it after you are done reading/writing.

Lukas Kuhn
  • 73
  • 1
  • 6