HOWTO · Python

How to Connect to a Microsoft SQL Server Using Python and Pyodbc

Learn how to connect to a Microsoft SQL Server using Python and pyodbc

On this page

Use pyodbc to connect a Python application to Microsoft SQL Server through ODBC. Install both the Python package and a compatible SQL Server ODBC driver, then pass the server, database, driver, and authentication settings to pyodbc.connect(). This guide uses direct SQL; an ORM is another option when you want database tables represented as Python objects.

Install pyodbc and the SQL Server ODBC driver

Install pyodbc into the same Python environment that runs your application, for example with python -m pip install pyodbc. This installs the Python interface, but it does not install Microsoft’s ODBC driver. Install a SQL Server ODBC driver separately on the machine or container where the program runs, and make sure its name matches the installed driver exactly. A common driver name is ODBC Driver 18 for SQL Server; use the name available on your system.

Before connecting, collect the server address and database name. A server can be a host name, an address, or a host and instance name such as db.example.net\SQLEXPRESS; hosted deployments may also require a port. Your administrator may need to grant the account access to the selected database. The SQL Server connection information is independent of Python’s working directory, but the driver must be available to the operating system’s ODBC manager.

Connect with a driver and trusted authentication

For a Windows environment configured for integrated authentication, use a driver-based connection string with Trusted_Connection=yes. Replace the driver, server, and database values with those for your installation. The following small program builds and displays the string so you can confirm how its fields are joined; it does not contact a database.

Run this example:

driver = "ODBC Driver 18 for SQL Server"
server = "db.example.net"
database = "Sales"
connection_string = (
    f"Driver={{{driver}}};"
    f"Server={server};"
    f"Database={database};"
    "Trusted_Connection=yes;"
)
print(connection_string)
Driver={ODBC Driver 18 for SQL Server};Server=db.example.net;Database=Sales;Trusted_Connection=yes;

To open the connection in your application, pass that string to pyodbc.connect(connection_string). Integrated authentication uses the identity available to the process and depends on the operating system, driver, and SQL Server configuration; it is not the same as supplying a SQL Server user name and password. For TLS configuration, follow your organization’s certificate policy. Driver versions may use encryption defaults that differ, so do not disable certificate checks just to make a connection succeed.

Connect through a DSN with credentials

A Data Source Name (DSN) is an ODBC configuration created on the host. It can hold shared server and driver settings, leaving the application to provide the DSN name and, when required, credentials. The connection string form is DSN=SalesReadOnly;UID=app_reader;PWD=<secret>;. The DSN must exist in the same environment and be visible to the Python process; a DSN configured for an interactive user may not be available to a service account or container.

Supply credentials through a secret manager or environment-specific configuration instead of committing them to source control. If your secret contains special characters, use the quoting rules supported by ODBC connection strings or configure the credential separately; otherwise a semicolon may be interpreted as a field separator. Avoid printing the completed connection string because it can expose the password in logs. Use the same pyodbc.connect(...) call with the DSN string, and select either integrated or SQL authentication according to how the DSN and server are configured.

Execute a query and close resources

After connecting, create a cursor, execute SQL, and fetch or iterate over the returned rows. Use parameter markers for values supplied by users or application data. With pyodbc, ? is the positional marker; passing values separately keeps them distinct from the SQL text. Parameters are for data values, not table or column names. If an identifier must vary, select it from a fixed allowlist and quote it according to SQL Server rules.

A typical application flow is connection = pyodbc.connect(connection_string), followed by cursor = connection.cursor() and cursor.execute("SELECT name FROM dbo.Customers WHERE customer_id = ?", customer_id). Iterate over cursor to process rows, or call fetchall() when the result is small enough to hold in memory. For large result sets, process rows incrementally. The query assumes that the named table and column exist in the selected database; replace them with your schema.

After connection = pyodbc.connect(connection_string) succeeds, keep cursor work inside a try block and close the connection in its finally clause: try: ... finally: connection.close(). For cursor cleanup, use with connection.cursor() as cursor: inside that block; leaving the cursor context closes the cursor. A pyodbc connection context such as with connection: manages transaction scope: it commits on normal exit and rolls back when an exception escapes, but it does not close the connection. You can combine that transaction context with the cursor context inside the try, then call connection.close() in finally. This closes the connection after query processing on both normal and exceptional exits, as long as the connection was opened successfully. If you change data, decide explicitly where the transaction should commit. This usage pattern requires a configured SQL Server, driver, and schema; it has not been run as part of this article.

Diagnose common connection errors

If pyodbc reports that a driver cannot be found, check that the ODBC driver is installed in the program’s runtime environment and that the Driver={...} value matches its registered name, including spaces and version. If the server cannot be reached, verify DNS or host spelling, port and instance settings, firewall access, and whether SQL Server accepts network connections. A timeout generally points to connectivity or server load; increasing it cannot fix an incorrect destination.

For login failures, confirm which identity the connection actually uses. Trusted authentication uses the process identity, while UID and PWD request SQL credentials; mixing these approaches can send the wrong identity. Then check database access and the database name. For certificate or encryption errors, confirm the server certificate chain and the driver’s encryption settings with your administrator rather than weakening verification. Finally, inspect the connection string for missing semicolons, unmatched braces around driver names, accidental whitespace in keys, or unescaped special characters. Keep diagnostic logs useful but redact passwords and other secrets.

For driver and connection-string details, see the pyodbc SQL Server connection guidance and the official pyodbc documentation. For a related example of reading query results, see How to Extract Elements From a Database Using fetchall() in Python.