How to Update Records in SQL Server using Python

Python / April 8, 2019

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:

How to Update Records in SQL Server using 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:

Update Records in SQL Server using Python