Python and SQL Server – Updating Data
In order to update data in an SQL Server database, the SQL ‘Update’ statement needs to be used. The following example updates the record in the ‘person’ table that was added in the example for inserting data.
Updating data follows the same pattern as inserting data. Firstly, a connection to the database is established, then the query parameters are defined as variables, which are bound in to the following SQL statement when it is executed and a confirmation message is displayed.
One of the parameters is for an ‘id’, so that only the specified record is updated, in this case, the record with an ‘id’ of five. The other parameters are for updating the ‘lastname’ and ‘title’ fields against the record.
The execution of the SQL statement is wrapped in a ‘try-catch-finally’ block to catch any errors that may arise and close the database connection at the end.
import pyodbc # Database connection variable. connect = None try: # Connect to database. connect = pyodbc.connect(Driver='SQL Server', host='localhost', database='Demo', user='DemoUN', password='DemoPW') except pyodbc.Error as e: # Confirm unsuccessful connection and stop program execution. print("Database connection unsuccessful.") quit() # Cursor to execute query. cursor = connect.cursor() # Query parameters. lastname = "Bloggs" title = "Mrs" id = 5 # SQL to update data in the person table. sqlUpdate = \ "UPDATE person \ SET lastname = ?, \ title = ? \ WHERE id = ?" try: # Execute query and commit changes. cursor.execute(sqlUpdate, (lastname, title, id)) connect.commit() # Confirm successful updating of person information. print("Person information updated successfully.") except pyodbc.Error as e: # Confirm error updating person information and stop program execution. print("Error updating person information.") quit() finally: # Close database connection. connect.close()
The contents of the ‘person’ table now looks as follows.
id | firstname | lastname | title | dob |
1 | Bob | Smith | Mr | 1980-01-20 |
2 | George | Jones | Mr | 1997-12-15 |
3 | Fred | Bloggs | Mr | 1975-05-07 |
4 | Alan | White | Mr | 1989-03-20 |
5 | Fiona | Bloggs | Mrs | 1985-05-19 |