In this tutorial, I’ll show you the steps to update records in SQL Server using Python.
To start, here is a template that you can use to update records in SQL Server:
UPDATE Database_Name.Table_Name SET Column1_Name = value1, Column2_Name = value2,... WHERE condition
In the next section, you’ll see how to use this template in practice.
Steps to Update Records in SQL Server using Python
Step 1: Create a Database and/or Table
If you haven’t already done so, create a database and/or table in SQL Server.
For example, I created a test database, where:
- The Server Name is: RON\SQLEXPRESS
- The Database Name is: TestDB
- The Table Name is: dbo.Person
- The Table dbo.Person contains the following data:
Name | Age | City |
Jade | 20 | London |
Mary | 119 | NY |
Martin | 25 | London |
Rob | 35 | Geneva |
Maria | 42 | Paris |
Jon | 28 | Toronto |
Step 2: Connect Python to SQL Server
You can use the following template to connect Python to SQL Server:
import pyodbc conn = pyodbc.connect('Driver={SQL Server};' 'Server=server_name;' 'Database=db_name;' 'Trusted_Connection=yes;') cursor = conn.cursor() cursor.execute('SELECT * FROM db_name.Table') for row in cursor: print(row)
You may refer to the following guide that explains the full steps to connect Python to SQL Server using the pyodbc package.
In the context of our example, this is the code that I used to connect Python to SQL Server:
import pyodbc conn = pyodbc.connect('Driver={SQL Server};' 'Server=RON\SQLEXPRESS;' 'Database=TestDB;' 'Trusted_Connection=yes;') cursor = conn.cursor() cursor.execute('SELECT * FROM TestDB.dbo.Person') for row in cursor: print(row)
And this is the result that I got in Python:
As you can observe, the information in Python matches with the information in the dbo.Person table in SQL Server.
Step 3: Update Records in SQL Server using Python
After you established a connection between Python and SQL Server, you’ll be able to update records in SQL Server using Python.
This is the template that you can apply in Python to update the records:
UPDATE Database_Name.Table_Name SET Column1_Name = value1, Column2_Name = value2,... WHERE condition
Let’s say that your goal is to update one of the records in SQL Server.
More specifically, let’s say that you’d like to update Jon’s Age from ‘28’ to ‘29,’ as well as the city to ‘Montreal.’ Therefore, the updated record should look like this:
Name | Age | City |
Jon | 29 | Montreal |
Here is the code that I used to update the record in SQL Server using Python:
import pyodbc conn=pyodbc.connect('Driver={SQL Server};' 'Server=RON\SQLEXPRESS;' 'Database=TestDB;' 'Trusted_Connection=yes;') cursor = conn.cursor() cursor.execute('SELECT * FROM TestDB.dbo.Person') cursor.execute(''' UPDATE TestDB.dbo.Person SET Age = 29,City = 'Montreal' WHERE Name = 'Jon' ''') conn.commit()
Don’t forget to add conn.commit() at the end of the code, to ensure that the changes would get implemented.
Step 4: Check that the record got updated
For this final step, you may want to check that the record got updated in SQL Server. To do so, simply run the following query in SQL Sever:
SELECT * FROM TestDB.dbo.Person
You’ll now see the updated record: