Advanced Database Operations with pyodbc

In this advanced tutorial, you will learn how to use the pyodbc module to connect to a SQL Server database from Python. The example covers the entire process from establishing a connection to creating tables and inserting data. By utilizing parameterized queries, the code ensures that the data insertion process is secure and resistant to SQL injection attacks. Additionally, the example includes comprehensive exception handling to manage various database errors effectively.

Code Example

import pyodbc

# Define the connection string
connection_string = (
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=your_server_name;'
    'DATABASE=your_database_name;'
    'UID=your_username;'
    'PWD=your_password'
)

try:
    # Establish the connection
    connection = pyodbc.connect(connection_string)
    print("Connection successful!")

    # Create a cursor from the connection
    cursor = connection.cursor()

    # Define SQL queries
    create_table_query = """
    CREATE TABLE IF NOT EXISTS Users (
        id INT PRIMARY KEY IDENTITY(1,1),
        name NVARCHAR(100),
        email NVARCHAR(100) UNIQUE,
        created_at DATETIME DEFAULT GETDATE()
    )
    """

    insert_data_query = """
    INSERT INTO Users (name, email) VALUES (?, ?)
    """

    # Execute create table query
    cursor.execute(create_table_query)
    print("Table created successfully!")

    # Insert data with parameterized queries
    users = [
        ("Alice Johnson", "alice.johnson@example.com"),
        ("Bob Smith", "bob.smith@example.com"),
        ("Carol White", "carol.white@example.com")
    ]

    for user in users:
        try:
            cursor.execute(insert_data_query, user)
            print(f"Inserted user: {user[0]}")
        except pyodbc.IntegrityError as ie:
            print(f"Failed to insert user {user[0]}: {ie}")

    # Commit the transaction
    connection.commit()
    print("Data committed successfully!")

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

finally:
    # Close the cursor and connection
    if cursor:
        cursor.close()
    if connection:
        connection.close()
    print("Connection closed.")

Explanation of the Code

1. Connection String: This includes the necessary details to connect to the SQL Server database. Replace your_server_name, your_database_name, your_username, and your_password with your actual credentials.
2. Establishing Connection: The pyodbc.connect(connection_string) establishes the connection to the SQL Server database.
3. Creating Cursor: connection.cursor() creates a cursor object which is used to execute SQL commands.
4. Defining SQL Queries:
   • create_table_query: SQL query to create the Users table if it does not exist. This table includes columns for id, name, email, and created_at.
   • insert_data_query: SQL query to insert data into the Users table using parameterized queries to prevent SQL injection.
5. Executing SQL Queries:
   • The create_table_query is executed to create the Users table.
   • A list of users is defined and iterated over. For each user, the insert_data_query is executed with the user data as parameters.
6. Exception Handling:
   • General exceptions are caught using pyodbc.Error, and specific integrity errors (like unique constraint violations) are caught using pyodbc.IntegrityError.
7. Committing Transaction: connection.commit() is used to save the changes to the database.
8. Closing Resources: The cursor and connection are closed in the finally block to ensure they are closed even if an error occurs.