SQL Server CRUD Tutorials in Python: A Step-by-Step Guide

Microsoft SQL Server is a robust and widely-used relational database management system (RDBMS). In this tutorial, we’ll explore how to perform CRUD (Create, Read, Update, Delete) operations using SQL Server in a Python application. We’ll cover each step and provide practical examples with detailed explanations to help you get started.

Prerequisites

Before we begin, make sure you have the following prerequisites:

SQL Server: SQL Server should be installed and running. You can download it from the official Microsoft SQL Server website.
Python: Ensure you have Python installed on your system. You can download Python from the official Python website.
pyodbc: Install the pyodbc package, which is a Python library for connecting to databases using ODBC (Open Database Connectivity). You can install it using pip:
```
pip install pyodbc
```

Step 1: Connecting to SQL Server

To use SQL Server in a Python application, establish a connection to the database.

import pyodbc

# Create a connection
try:
    connection = pyodbc.connect(
        "Driver={SQL Server};"
        "Server=your_server_name;"
        "Database=your_database_name;"
        "UID=your_username;"
        "PWD=your_password;"
    )

    cursor = connection.cursor()
    print("Connected to SQL Server")

except pyodbc.Error as e:
    print(f"Error: {e}")

Replace "your_server_name", "your_database_name", "your_username", and "your_password" with your SQL Server credentials and connection details.

Step 2: Creating a Table

Let’s create a simple users table to demonstrate CRUD operations.

try:
    create_table_query = """
    CREATE TABLE IF NOT EXISTS users (
        id INT PRIMARY KEY,
        username VARCHAR(50) NOT NULL,
        email VARCHAR(100) NOT NULL
    )
    """

    cursor.execute(create_table_query)
    connection.commit()
    print("Table 'users' created successfully")

except pyodbc.Error as e:
    print(f"Error: {e}")

Step 3: Inserting Data

Now, let’s insert a new user into the users table.

try:
    insert_query = "INSERT INTO users (id, username, email) VALUES (?, ?, ?)"
    user_data = (1, "john_doe", "[email protected]")

    cursor.execute(insert_query, user_data)
    connection.commit()
    print("Data inserted successfully")

except pyodbc.Error as e:
    print(f"Error: {e}")

Step 4: Querying Data

Retrieve data from the users table.

try:
    select_query = "SELECT * FROM users"

    cursor.execute(select_query)

    for row in cursor.fetchall():
        print(f"ID: {row.id}, Username: {row.username}, Email: {row.email}")

except pyodbc.Error as e:
    print(f"Error: {e}")

Step 5: Updating Data

Update a user’s email in the users table.

try:
    update_query = "UPDATE users SET email = ? WHERE username = ?"
    user_data = ("[email protected]", "john_doe")

    cursor.execute(update_query, user_data)
    connection.commit()
    print("Data updated successfully")

except pyodbc.Error as e:
    print(f"Error: {e}")

Step 6: Deleting Data

Delete a user from the users table.

try:
    delete_query = "DELETE FROM users WHERE username = ?"
    user_data = ("john_doe",)

    cursor.execute(delete_query, user_data)
    connection.commit()
    print("Data deleted successfully")

except pyodbc.Error as e:
    print(f"Error: {e}")

Step 7: Error Handling and Cleanup

Proper error handling is crucial when working with databases. Close the SQL Server connection when done.

finally:
    if 'cursor' in locals():
        cursor.close()

    if 'connection' in locals():
        connection.close()
        print("Connection closed")

Conclusion

In this tutorial, we’ve covered the basics of performing CRUD operations using SQL Server in a Python application. You’ve learned how to connect to SQL Server, create a table, insert data, query data, update records, and delete records. These fundamental skills will serve as a solid foundation for building more complex database-driven applications with SQL Server and Python.