By Jacob Diamond-Reivich — Oct 6, 2025

How to Connect Python to Microsoft SQL Server: Complete Guide

Microsoft SQL Server is a powerful relational database management system widely used in enterprise environments. Connecting Python to SQL Server allows developers to build robust data-driven applications combining Python's flexibility with SQL Server's enterprise features. This comprehensive guide covers everything you need to know about Python-SQL Server connectivity.

Why Use SQL Server with Python?

SQL Server combined with Python offers powerful capabilities:

Seamless integration with Microsoft ecosystem
Excellent Windows environment support
Advanced security and encryption features
High availability and disaster recovery
Powerful T-SQL language for complex operations
Integration Services (SSIS) for ETL
Analysis Services for business intelligence
Strong support for JSON and XML data

Prerequisites

Before connecting Python to SQL Server, ensure you have:

Python 3.6 or higher installed
SQL Server installed (Express, Standard, or Enterprise)
ODBC Driver for SQL Server installed
Database credentials (server, database, username, password)
Basic understanding of Python and T-SQL

Installing pyodbc

The most popular library for connecting Python to SQL Server is pyodbc:

bash

pip install pyodbc

Alternatively, you can use pymssql:

bash

pip install pymssql

We'll primarily use pyodbc as it's more feature-rich and widely adopted.

Installing SQL Server ODBC Driver

Download and install the Microsoft ODBC Driver for SQL Server from Microsoft's website. Check installed drivers:

python

import pyodbc

# List available ODBC drivers
drivers = pyodbc.drivers()
print("Available ODBC drivers:")
for driver in drivers:
    print(f"  - {driver}")

Basic Connection to SQL Server

Let's start with a simple connection:

python

import pyodbc

# Connection parameters
server = 'localhost'
database = 'TestDB'
username = 'sa'
password = 'YourPassword123'

# Create connection string
connection_string = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password}'

# Establish connection
connection = pyodbc.connect(connection_string)

print("Connected to SQL Server successfully!")

# Close connection
connection.close()

Windows Authentication

For Windows Authentication (trusted connection):

python

import pyodbc

server = 'localhost'
database = 'TestDB'

# Connection string for Windows Authentication
connection_string = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server};DATABASE={database};Trusted_Connection=yes'

connection = pyodbc.connect(connection_string)

print("Connected using Windows Authentication!")

connection.close()

Connection with Error Handling

python

import pyodbc

def create_sql_connection():
    try:
        connection_string = (
            'DRIVER={ODBC Driver 17 for SQL Server};'
            'SERVER=localhost;'
            'DATABASE=TestDB;'
            'UID=sa;'
            'PWD=YourPassword123'
        )
        
        connection = pyodbc.connect(connection_string)
        
        print("Successfully connected to SQL Server")
        
        # Get SQL Server version
        cursor = connection.cursor()
        cursor.execute("SELECT @@VERSION")
        version = cursor.fetchone()[0]
        print(f"SQL Server version: {version[:50]}...")
        cursor.close()
        
        return connection
        
    except pyodbc.Error as e:
        print(f"Database error: {e}")
        return None
    except Exception as e:
        print(f"Error: {e}")
        return None

# Usage
conn = create_sql_connection()
if conn:
    conn.close()

Creating Tables

python

import pyodbc

connection = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=localhost;'
    'DATABASE=TestDB;'
    'UID=sa;'
    'PWD=YourPassword123'
)

cursor = connection.cursor()

# Create employees table
create_table_sql = """
CREATE TABLE employees (
    id INT IDENTITY(1,1) PRIMARY KEY,
    first_name NVARCHAR(50) NOT NULL,
    last_name NVARCHAR(50) NOT NULL,
    email NVARCHAR(100) UNIQUE,
    department NVARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE DEFAULT GETDATE()
)
"""

try:
    cursor.execute(create_table_sql)
    connection.commit()
    print("Table created successfully!")
except pyodbc.Error as e:
    print(f"Error creating table: {e}")

cursor.close()
connection.close()

Inserting Data

Insert Single Record

python

import pyodbc
from datetime import date

connection = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=localhost;'
    'DATABASE=TestDB;'
    'UID=sa;'
    'PWD=YourPassword123'
)

cursor = connection.cursor()

# Insert single employee
insert_sql = """
INSERT INTO employees (first_name, last_name, email, department, salary, hire_date)
VALUES (?, ?, ?, ?, ?, ?)
"""

employee_data = ('John', 'Doe', 'john.doe@company.com', 'Engineering', 75000.00, date.today())

cursor.execute(insert_sql, employee_data)
connection.commit()

# Get the inserted ID
cursor.execute("SELECT @@IDENTITY")
new_id = cursor.fetchone()[0]
print(f"Employee inserted with ID: {int(new_id)}")

cursor.close()
connection.close()

SQL Server uses ? as placeholders for parameterized queries.

Insert Multiple Records

python

import pyodbc

connection = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=localhost;'
    'DATABASE=TestDB;'
    'UID=sa;'
    'PWD=YourPassword123'
)

cursor = connection.cursor()

insert_sql = """
INSERT INTO employees (first_name, last_name, email, department, salary)
VALUES (?, ?, ?, ?, ?)
"""

employees_data = [
    ('Jane', 'Smith', 'jane@company.com', 'Marketing', 68000.00),
    ('Bob', 'Johnson', 'bob@company.com', 'Sales', 72000.00),
    ('Alice', 'Williams', 'alice@company.com', 'Engineering', 80000.00),
    ('Charlie', 'Brown', 'charlie@company.com', 'HR', 62000.00)
]

cursor.executemany(insert_sql, employees_data)
connection.commit()

print(f"{cursor.rowcount} employees inserted successfully!")

cursor.close()
connection.close()

Querying Data

Fetch All Records

python

import pyodbc

connection = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=localhost;'
    'DATABASE=TestDB;'
    'UID=sa;'
    'PWD=YourPassword123'
)

cursor = connection.cursor()

# Select all employees
select_sql = "SELECT id, first_name, last_name, department, salary FROM employees"
cursor.execute(select_sql)

rows = cursor.fetchall()

print(f"Total employees: {len(rows)}\n")

for row in rows:
    print(f"ID: {row.id}, Name: {row.first_name} {row.last_name}, Dept: {row.department}, Salary: ${row.salary}")

cursor.close()
connection.close()

Fetch with Conditions

python

import pyodbc

connection = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=localhost;'
    'DATABASE=TestDB;'
    'UID=sa;'
    'PWD=YourPassword123'
)

cursor = connection.cursor()

# Find employees in Engineering department
select_sql = "SELECT * FROM employees WHERE department = ?"
cursor.execute(select_sql, 'Engineering')

engineers = cursor.fetchall()

print("Engineering Department:")
for emp in engineers:
    print(f"{emp.first_name} {emp.last_name} - ${emp.salary}")

cursor.close()
connection.close()

Using Row Dictionary

python

import pyodbc

connection = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=localhost;'
    'DATABASE=TestDB;'
    'UID=sa;'
    'PWD=YourPassword123'
)

cursor = connection.cursor()

# Query with dictionary-like access
select_sql = "SELECT id, first_name, last_name, salary FROM employees WHERE salary > ?"
cursor.execute(select_sql, 70000)

# Get column names
columns = [column[0] for column in cursor.description]

# Create dictionaries
employees = []
for row in cursor.fetchall():
    employee = dict(zip(columns, row))
    employees.append(employee)

for emp in employees:
    print(f"{emp['first_name']} {emp['last_name']} - ${emp['salary']}")

cursor.close()
connection.close()

Updating Records

python

import pyodbc

connection = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=localhost;'
    'DATABASE=TestDB;'
    'UID=sa;'
    'PWD=YourPassword123'
)

cursor = connection.cursor()

# Update employee salary
update_sql = "UPDATE employees SET salary = ? WHERE id = ?"
cursor.execute(update_sql, 85000.00, 1)
connection.commit()

print(f"{cursor.rowcount} record(s) updated")

# Update multiple employees
update_sql = "UPDATE employees SET salary = salary * 1.10 WHERE department = ?"
cursor.execute(update_sql, 'Engineering')
connection.commit()

print(f"{cursor.rowcount} Engineering employees received raises")

cursor.close()
connection.close()

Deleting Records

python

import pyodbc

connection = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=localhost;'
    'DATABASE=TestDB;'
    'UID=sa;'
    'PWD=YourPassword123'
)

cursor = connection.cursor()

# Delete employee by ID
delete_sql = "DELETE FROM employees WHERE id = ?"
cursor.execute(delete_sql, 10)
connection.commit()

print(f"{cursor.rowcount} record(s) deleted")

cursor.close()
connection.close()

Working with Stored Procedures

python

import pyodbc

connection = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=localhost;'
    'DATABASE=TestDB;'
    'UID=sa;'
    'PWD=YourPassword123'
)

cursor = connection.cursor()

# Create stored procedure
proc_sql = """
CREATE PROCEDURE GetEmployeesByDepartment
    @DeptName NVARCHAR(50)
AS
BEGIN
    SELECT * FROM employees
    WHERE department = @DeptName
    ORDER BY last_name
END
"""

try:
    cursor.execute(proc_sql)
    connection.commit()
    print("Stored procedure created!")
except pyodbc.Error as e:
    print(f"Error: {e}")

# Call stored procedure
cursor.execute("EXEC GetEmployeesByDepartment ?", 'Engineering')

employees = cursor.fetchall()

print(f"Found {len(employees)} employees in Engineering")
for emp in employees:
    print(f"{emp.first_name} {emp.last_name}")

cursor.close()
connection.close()

Working with Output Parameters

python

import pyodbc

connection = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=localhost;'
    'DATABASE=TestDB;'
    'UID=sa;'
    'PWD=YourPassword123'
)

cursor = connection.cursor()

# Create procedure with output parameter
proc_sql = """
CREATE PROCEDURE GetEmployeeCount
    @DeptName NVARCHAR(50),
    @EmpCount INT OUTPUT
AS
BEGIN
    SELECT @EmpCount = COUNT(*)
    FROM employees
    WHERE department = @DeptName
END
"""

cursor.execute(proc_sql)
connection.commit()

# Call procedure with output parameter
dept = 'Engineering'
cursor.execute(f"DECLARE @count INT; EXEC GetEmployeeCount ?, @count OUTPUT; SELECT @count", dept)

count = cursor.fetchone()[0]
print(f"Employee count in {dept}: {count}")

cursor.close()
connection.close()

Working with Transactions

python

import pyodbc

connection = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=localhost;'
    'DATABASE=TestDB;'
    'UID=sa;'
    'PWD=YourPassword123'
)

cursor = connection.cursor()

try:
    # Start transaction
    cursor.execute("BEGIN TRANSACTION")
    
    cursor.execute("UPDATE employees SET salary = salary - 5000 WHERE id = 1")
    cursor.execute("UPDATE employees SET salary = salary + 5000 WHERE id = 2")
    
    # Commit transaction
    cursor.execute("COMMIT TRANSACTION")
    connection.commit()
    print("Transaction completed successfully")
    
except pyodbc.Error as e:
    # Rollback on error
    cursor.execute("ROLLBACK TRANSACTION")
    connection.commit()
    print(f"Transaction failed: {e}")
    
finally:
    cursor.close()
    connection.close()

Using Context Managers

python

import pyodbc
from contextlib import closing

def get_connection():
    return pyodbc.connect(
        'DRIVER={ODBC Driver 17 for SQL Server};'
        'SERVER=localhost;'
        'DATABASE=TestDB;'
        'UID=sa;'
        'PWD=YourPassword123'
    )

with closing(get_connection()) as conn:
    with closing(conn.cursor()) as cursor:
        cursor.execute("SELECT COUNT(*) FROM employees")
        count = cursor.fetchone()[0]
        print(f"Total employees: {count}")

Working with JSON Data

SQL Server has excellent JSON support:

python

import pyodbc
import json

connection = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=localhost;'
    'DATABASE=TestDB;'
    'UID=sa;'
    'PWD=YourPassword123'
)

cursor = connection.cursor()

# Create table with JSON column
cursor.execute("""
    CREATE TABLE user_settings (
        user_id INT PRIMARY KEY,
        settings NVARCHAR(MAX)
    )
""")

# Insert JSON data
settings = {
    "theme": "dark",
    "language": "en",
    "notifications": True
}

cursor.execute(
    "INSERT INTO user_settings (user_id, settings) VALUES (?, ?)",
    1, json.dumps(settings)
)
connection.commit()

# Query and parse JSON
cursor.execute("SELECT settings FROM user_settings WHERE user_id = 1")
result = cursor.fetchone()[0]
user_settings = json.loads(result)

print(f"User theme: {user_settings['theme']}")

# Query JSON using SQL Server JSON functions
cursor.execute("""
    SELECT JSON_VALUE(settings, '$.theme') as theme
    FROM user_settings
    WHERE user_id = 1
""")

theme = cursor.fetchone()[0]
print(f"Theme from JSON_VALUE: {theme}")

cursor.close()
connection.close()

Connection Pooling

python

import pyodbc

class ConnectionPool:
    def __init__(self, connection_string, pool_size=5):
        self.connection_string = connection_string
        self.pool = []
        self.pool_size = pool_size
        
        for _ in range(pool_size):
            self.pool.append(pyodbc.connect(connection_string))
    
    def get_connection(self):
        if self.pool:
            return self.

Why Use SQL Server with Python?

Prerequisites

Installing pyodbc

Installing SQL Server ODBC Driver

Basic Connection to SQL Server

Windows Authentication

Connection with Error Handling

Creating Tables

Inserting Data

Insert Single Record

Insert Multiple Records

Querying Data

Fetch All Records

Fetch with Conditions

Using Row Dictionary

Updating Records

Deleting Records

Working with Stored Procedures

Working with Output Parameters

Working with Transactions

Using Context Managers

Working with JSON Data

Connection Pooling

Subscribe to Transition from Excel to Python | Mito