How to Update Records in SQL Server using Python

In this tutorial, you’ll see the steps to update records in SQL Server using Python.

To start, here is a template that you may use to update records in SQL Server:

UPDATE table_name
SET column_1 = value_1, column_2 = value_2, ...
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 Table

If you haven’t already done so, create a database and table in SQL Server.

For demonstration purposes, let’s assume that the:

Server name is: RON\SQLEXPRESS
Database name is: test_database
Table name is: products
The ‘products’ table contains the following data:

product_id	product_name	price
1	Laptop	1100
2	Printer	200
3	Keyboard	80
4	Monitor	450
5	Tablet	300

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=database_name;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute('SELECT * FROM table_name')

for i in cursor:
    print(i)

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, here is the code to connect Python to SQL Server:

import pyodbc 

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=RON\SQLEXPRESS;'
                      'Database=test_database;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute('SELECT * FROM products')

for i in cursor:
    print(i)

Run the code in Python (adjusted to your server, database and table information), and you’ll see the following result:

(1, 'Laptop', 1100)
(2, 'Printer', 200)
(3, 'Keyboard', 80)
(4, 'Monitor', 450)
(5, 'Tablet', 300)

Step 3: Update the Records in SQL Server using Python

After you connected Python and SQL Server, you’ll be able to update the records in SQL Server using Python.

Here is the template that you may apply in Python to update the records:

UPDATE table_name
SET column_1 = value_1, column_2 = value_2, ...
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 the price of the ‘Tablet’ product (where the product_id is 5) from 300 to 350. Therefore, the updated record should look like this:

product_id	product_name	price
5	Tablet	350

In that case, you may apply the code below in order to update the record in SQL Server using Python:

import pyodbc 

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=RON\SQLEXPRESS;'
                      'Database=test_database;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()

cursor.execute('''
                UPDATE products
                SET price = 350
                WHERE product_id = 5
                ''')

conn.commit()

Don’t forget to add conn.commit() at the bottom of the code to ensure that the changes would get implemented.

Step 4: Check that the record was updated

For this final step, you may want to check that the record was updated in SQL Server. To do so, simply run the following query in SQL Sever:

SELECT * FROM products

You’ll now see the updated record:

product_id	product_name	price
1	Laptop	1100
2	Printer	200
3	Keyboard	80
4	Monitor	450
5	Tablet	350

You may also want to check the following guides to learn how to: