Update Records in SQL Server using Python

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

Here are the steps that you may follow.

Steps to Update Records in SQL Server using Python

Step 1: Install the Pyodbc Package

To start, install the pyodbc package using this command:

pip install pyodbc

Step 2: Connect Python to SQL Server

Then, connect Python to SQL server.

For demonstration purposes, let’s assume that the:

  • Server name is: RON-SQLEXPRESS
  • Database name is: test_database
  • Table name is: products

Where the products table contains the following data:

product_idproduct_nameprice
1Laptop1100
2Printer200
3Keyboard80
4Monitor450
5Tablet300

Here is the code to connect Python to SQL Server for our example (make sure to adjust the code to reflect your serverdatabase and table information):

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, and you’ll see the following data:

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

Step 3: Update Records in SQL Server using Python

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

For example, 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_idproduct_nameprice
5Tablet350

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, check that the record was updated by running the following query in SQL Sever:

SELECT * FROM products

You’ll now see the updated record:

product_idproduct_nameprice
1Laptop1100
2Printer200
3Keyboard80
4Monitor450
5Tablet350

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