CODEWITHSUNDEEP
pythonsqlpyodbc
C-Sway
C-Sway

Reputation: 377

pyODBC statement appearing to run but not affecting table

I have the following script running;

from os import getenv
import pyodbc
import os
import sys

cnxn = pyodbc.connect('''
DRIVER={ODBC Driver 13 for SQL Server};SERVER=myServer\SQLEXPRESS;
DATABASE=myTable;UID=myID;PWD=myPassword''')

cursor = cnxn.cursor() #makes connection
cursor.execute("""

            UPDATE ShowroomCal
                SET ShowroomCal.isbusy = 'Yes'
            FROM ShowroomCal
                JOIN calendarbookings on calendarbookings.date=showroomcal.style112 AND calendarbookings.showroom=ShowroomCal.showroom_name
                WHERE CalendarBookings.date = showroomcal.style112 and ShowroomCal.Year='2018'
                """) #runs update statement

row_count = cursor.rowcount #counts afrfected rows
status_msg = "result returned. {} row(s) affected."
print(status_msg.format(row_count)) #prints affected rows

cnxn.close()

The notebook cell returns "3 row(s) affected." yet the table being updated doesn't change when viewed in SQL Express.

I have since independently run the statement from SQL Server Management in a Query Window and it instantly changed the three rows that would have been affected so I know that the data in the table exists to be changed and that the statement works - at least from a SQL standpoint.

Can someone spot any errors from a Python perspective?

Upvotes: 0

Views: 856

Answers (1)

C-Sway
C-Sway

Reputation: 377

Scratch'N'Purr provided the answer for me, thanks very much.

If anyone else is having the same issue it's the lack of 

cnxn.commit()

After the statement that is the problem. I have since tested this and it worked perfectly.

Upvotes: 2

Related Questions