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:

Here is the code to connect Python to SQL Server for our example (make sure to adjust the code to reflect your server, database 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:

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:

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

• Delete Records in SQL Server using Python
• Insert Values into SQL Server Table using Python