Python MS SQL Server CRUD Operations: A Comprehensive Guide

Estimated read time 3 min read

Performing CRUD operations (Create, Read, Update, Delete) in a Microsoft SQL Server database using Python is a crucial skill for developing data-driven applications. In this guide, we’ll walk through each CRUD operation step by step, using the pyodbc library for database interactions.

1. Setting Up MS SQL Server Database:

  • Begin by setting up a Microsoft SQL Server database and creating a table for our CRUD operations. For this example, let’s create a simple employees table.
   CREATE TABLE employees (
       name NVARCHAR(255),
       position NVARCHAR(255)

2. Installing the SQL Server Connector:

  • Install the pyodbc library using pip.
   pip install pyodbc

3. Configuring ODBC Data Source:

  • Before connecting to the SQL Server database, configure an ODBC (Open Database Connectivity) data source. This involves setting up a System DSN (Data Source Name) that points to your SQL Server instance. Follow the steps provided by Microsoft for your operating system.

4. Connecting to the SQL Server Database:

  • Create a Python script to connect to your SQL Server database.
   import pyodbc

   # Replace 'your_dsn', 'your_username', and 'your_password' with your actual database credentials
   conn = pyodbc.connect(

   cursor = conn.cursor()

5. Create Operation:

  • Write a function to add a new employee to the employees table.
   def create_employee(name, position):
       query = "INSERT INTO employees (name, position) VALUES (?, ?)"
       values = (name, position)

       cursor.execute(query, values)

6. Read Operation:

  • Retrieve employees from the employees table.
   def read_employees():
       query = "SELECT * FROM employees"

       result = cursor.fetchall()

       for employee in result:

7. Update Operation:

  • Update the position of a specific employee.
   def update_employee_position(employee_id, new_position):
       query = "UPDATE employees SET position = ? WHERE id = ?"
       values = (new_position, employee_id)

       cursor.execute(query, values)

8. Delete Operation:

  • Delete an employee from the employees table.
   def delete_employee(employee_id):
       query = "DELETE FROM employees WHERE id = ?"
       values = (employee_id,)

       cursor.execute(query, values)

9. Using the CRUD Operations:

  • Utilize these functions to perform CRUD operations.
   # Create a new employee
   create_employee('John Doe', 'Software Engineer')

   # Read all employees

   # Update the position of employee with ID 1
   update_employee_position(1, 'Senior Software Engineer')

   # Read all employees after the update

   # Delete employee with ID 2

   # Read all employees after the deletion

10. Closing the Database Connection:
– After performing CRUD operations, it’s crucial to close the database connection.



  • This guide provides a comprehensive foundation for implementing CRUD operations in a Microsoft SQL Server database using Python. Adjust the functions as needed for your specific use case. Additionally, consider using an ORM (Object-Relational Mapping) library like SQLAlchemy for more complex applications.

Related Articles