update statement under pyodbc issue-CodePudding

I am currently developing a program in python that interacts with multiple database. I am using pyodbc to connect, and execute queries. One of the database is an azure database. I noticed sometimes the sent data is not updated in the database although the program run successfully and no error was thrown. Is there any practices that i should follow to make sure this doesn't happen or is this related to my code or db connection issue? I am a beginner. Would appreciate everyone's help thank you!

Also is the .commit() line should be run after every sql run?

The program should be updating a row of data in the database based on a condition, this particular query sometimes doesn't take effect, but no error was thrown. I also executed multiple queries after that, no issue was found for the next queries. It is successfully executed.

the query is a simple query which is

UPDATE DraftVReg SET VRStatus = 'Potential Duplicate Found' WHERE RowID = ?

CodePudding user response：

I tried to reproduce your scenario on my end and was able to update the SQL row in the Azure SQL DB with Pyodbc module.

Yes, Its very necessary to use

conn.commit

to commit your changes inside a database after you perform operations such as Update or Insert inside Azure SQL DB programmatically.

1) Fetch Data with Select statement.

I was able to fetch the Table’s data successfully with Select * from ‘Tablename’ query inside pyodbc code before I try UPDATE statement.

import pyodbc
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};''SERVER=tcp:sqlservernamesql.database.windows.net,1433;''DATABASE=databasename; UID=siliconuser;PWD=Password;')
#conn.commit()
cursor = conn.cursor()
cursor.execute('Select * FROM StudentReviews')
#conn.commit()
for  i  in  cursor:
print(i)
cursor.close()
conn.close()

Result:-

enter image description here

2) UPDATE the rows require conn.commit()

Code :-

import pyodbc
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};''SERVER=tcp:siliconserversql.database.windows.net,1433;''DATABASE=silicondb; UID=userid; PWD=Password;')
cursor = conn.cursor() 
#cursor.execute('Select * FROM StudentReviews')
cursor.execute("UPDATE StudentReviews SET ReviewTime = ('7') WHERE ReviewText = ('SQL DB')")
conn.commit()
cursor.close()
conn.close()

Result:-

Update statement Executed successfully and the Table Row was updated in Azure SQL, Refer Below :-

enter image description here

3) With autocommit=true

Thank you @Gord thompson for the comment and suggestion!

Code :-

import pyodbc
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};''SERVER=tcp:siliconserversql.database.windows.net,1433;''DATABASE=silicondb; UID=username; PWD=Password;', autocommit=True) 
#conn.commit()
cursor = conn.cursor()
cursor.execute("UPDATE StudentReviews SET ReviewTime = ('8') WHERE ReviewText = ('SQL DB')")
cursor.close()
conn.close()

Results :- With autocommit=true, You do not need to add conn.commit everytime you update the SQL DB.

enter image description here