Python SQL Server: Update

Summary: in this tutorial, you will learn how to update data in an SQL Server table from a Python program.

This tutorial begins where the bulk copy tutorial left off.

Updating data in a table #

Step 1. Create a new update.py file within the project directory.

Step 2. Define a function update_customer_email that updates the customer’s email:

import logging
from connect import create_connection

def update_customer_email(customer_id: int, email: str) -> bool:
    # Connect to the SQL Server
    conn = create_connection()
    if conn is None:
        return False

    # Update the customer email
    with (conn, conn.cursor() as cursor):
        cursor.execute(
            "UPDATE Customers SET Email = %s WHERE CustomerID = %s",
            (email, customer_id),
        )
        conn.commit()
        logging.info(f'{cursor.rowcount} rows updated successfully.')
        return True if cursor.rowcount == 1 else False

How it works.

First, import the built-in logging module for logging:

import logging

Second, import the create_connection function from the connect module:

from connect import create_connection

Third, define a function update_customer_email that accepts a customer ID (customer_id) that identifies the customer to update and the new email of the customer (email):

def update_customer_email(customer_id: int, email: str) -> bool:

The function returns true if it successfully updates the customer’s email or false otherwise.

Fourth, create a connection to the SQL Server and return false if the connection fails:

conn = create_connection()
if conn is None:
    return False

Fifth, manage the Connection and Cursor objects in the with statement to avoid manually closing them:

with (conn, conn.cursor() as cursor):

Sixth, create a Cursor object and execute the UPDATE statement that updates the email of a customer by ID:

cursor.execute(
    "UPDATE Customers SET Email = %s WHERE CustomerID = %s",
    (email, customer_id),
)

Notice that we use the UPDATE statement with parameterized values to prevent SQL injection attacks.

Seventh, call the commit() method of the Connection object to save the change to the database permanently:

conn.commit()

Finally, log the number of rows updated by accessing the rowcount property of the Cursor object and return True to indicate that the update was successful or False otherwise:

logging.info(f'{cursor.rowcount} rows updated successfully.')
return True if cursor.rowcount == 1 else False

Step 3. Modify the main.py module to use the update_customer_email function:

import logging, sys
from update import update_customer_email

# config logging to console
logging.basicConfig(
    stream=sys.stdout, 
    encoding='utf-8', 
    format='%(levelname)s:%(message)s',
    level=logging.DEBUG
)

# update customer email
update_customer_email(1, '[email protected]')

How it works (we’ll focus on the update part).

First, import update_customer_email from the update module:

from update import update_customer_email

Second, call the update_customer_email function to update the email of customer ID 1 to '[email protected]':

update_customer_email(1, '[email protected]')

Step 4. Launch SQL Server Management Studio (SSMS), connect to the SQL Server, and execute the following query to retrieve data from customer ID 1:

SELECT * 
FROM customers
WHERE customerID = 1;

Output:

Step 5. Open your terminal and execute the following command to run the main.py file:

python main.py

Output:

INFO:1 rows updated successfully

Step 6. Execute the following query again to verify the update:

SELECT * 
FROM customers
WHERE customerID = 1;

Output:

Download the project source code #

Download the project source code

Summary #

Use the execute() method of a Cursor object to execute an UPDATE statement to update data in an SQL Server table.

Was this tutorial helpful?