How to Update Records in SQL Server using Python

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: