How to connect Python to SQL Server with pyodbc

Connecting Python to SQL Server is one of the most common tasks for anyone working with data on the Microsoft platform: it lets you read tables, run queries and bring the results straight into a pandas DataFrame. The most reliable way to do it is the pyodbc library, which builds on Microsoft's official ODBC driver. Below we show, step by step, how to set up this connection from scratch, with ready-to-use examples and the most common errors to avoid.

Prerequisites

• Python 3.9 or later installed on your system.
• Access to a SQL Server instance (local, on a VM or on Azure SQL) and its credentials.
• The Microsoft ODBC Driver 18 for SQL Server (or the latest version) installed on the operating system.
• Permission to run pip install in your Python environment.

Step 1: Install the pyodbc library

Start by installing the package in your environment. If you use virtual environments — which is recommended — activate it first and only then run the command.

pip install pyodbc pandas

The pyodbc library handles communication with SQL Server, and pandas will receive the data in tabular form. Note: the ODBC driver itself is not installed with pip — it is an operating-system component that you must download from Microsoft's website.

Step 2: Confirm the ODBC driver is available

Before connecting, make sure Python can "see" the driver. This short example lists every ODBC driver installed on the machine.

import pyodbc

for driver in pyodbc.drivers():
    print(driver)

The output should include a line such as ODBC Driver 18 for SQL Server. If the list comes back empty or without any SQL Server driver, install it before continuing — it is the number-one cause of the "Data source name not found" error.

Step 3: Create the connection to SQL Server

The connection is defined by a connection string with the driver, the server, the database and the credentials. Keep the password out of your code, for example in an environment variable, and never leave it written in the file.

import os
import pyodbc

conn_str = (
    "DRIVER={ODBC Driver 18 for SQL Server};"
    "SERVER=localhost,1433;"
    "DATABASE=Vendas;"
    "UID=leitor;"
    "PWD=" + os.environ["SQL_PWD"] + ";"
    "Encrypt=yes;"
    "TrustServerCertificate=yes;"
)

conn = pyodbc.connect(conn_str)
print(conn.getinfo(pyodbc.SQL_DBMS_NAME))

A few important points: in driver 18, Encrypt=yes is the default and a good security practice. Use TrustServerCertificate=yes only in development, with self-signed certificates; in production prefer a valid certificate and TrustServerCertificate=no. For integrated Windows authentication, replace UID and PWD with Trusted_Connection=yes.

Step 4: Run a query and read the results

With the connection open, create a cursor to run SQL commands. Always use parameters (the ?) instead of concatenating values into the query — that is what protects the application from SQL injection.

cursor = conn.cursor()

cursor.execute(
    "SELECT TOP 5 id, cliente, total FROM dbo.Encomendas WHERE total > ?",
    100,
)

for row in cursor.fetchall():
    print(row.id, row.cliente, row.total)

The fetchall() method returns every row at once; for large tables, iterate over the cursor directly or use fetchmany() to save memory.

Step 5: Bring the data into a pandas DataFrame

For analysis, the most practical option is to load the result straight into a DataFrame. The pandas read_sql function accepts the query and the pyodbc connection.

import pandas as pd

df = pd.read_sql("SELECT * FROM dbo.Encomendas", conn)
print(df.head())
print(len(df))

conn.close()

Always close the connection at the end with conn.close(), or open it inside a with block so that Python closes it automatically.

Verify the result

If everything went well, running the Step 3 script prints the DBMS name — Microsoft SQL Server — and the following steps print the first rows of the table in the terminal. To confirm the values are correct, compare them with the same query in SQL Server Management Studio or Azure Data Studio. If you get the "Login failed for user" error, check the credentials; if it is "server was not found", verify the server name, the port and whether the firewall allows the connection.

Conclusion

With just a few lines of code, Python can now read from and write to SQL Server, opening the door to automating reports, feeding machine learning models or building data pipelines. The natural next step is to parameterise the connection string per environment and try SQLAlchemy, which builds on pyodbc and simplifies writing DataFrames back to the database. Which query from your daily work would you like to automate first?