sqlalchemy.dialects.mssql.pyodbc

DSN Connections

A DSN connection in ODBC means that a pre-existing ODBC datasource is configured on the client machine. The application then specifies the name of this datasource, which encompasses details such as the specific ODBC driver in use as well as the network address of the database. Assuming a datasource is configured on the client, a basic DSN-based connection looks like:

engine = create_engine("mssql+pyodbc://scott:tiger@some_dsn")

Which above, will pass the following connection string to PyODBC:

DSN=some_dsn;UID=scott;PWD=tiger

If the username and password are omitted, the DSN form will also add the Trusted_Connection=yes directive to the ODBC string.

Hostname Connections

Hostname-based connections are also supported by pyodbc. These are often easier to use than a DSN and have the additional advantage that the specific database name to connect towards may be specified locally in the URL, rather than it being fixed as part of a datasource configuration.

When using a hostname connection, the driver name must also be specified in the query parameters of the URL. As these names usually have spaces in them, the name must be URL encoded which means using plus signs for spaces:

engine = create_engine("mssql+pyodbc://scott:tiger@myhost:port/databasename?driver=ODBC+Driver+17+for+SQL+Server")

Other keywords interpreted by the Pyodbc dialect to be passed to pyodbc.connect() in both the DSN and hostname cases include: odbc_autotranslate, ansi, unicode_results, autocommit, authentication. Note that in order for the dialect to recognize these keywords (including the driver keyword above) they must be all lowercase. Multiple additional keyword arguments must be separated by an ampersand (&), not a semicolon:

engine = create_engine(
    "mssql+pyodbc://scott:tiger@myhost:49242/databasename"
    "?driver=ODBC+Driver+17+for+SQL+Server"
    "&authentication=ActiveDirectoryIntegrated"
)

The equivalent URL can be constructed using _sa.engine.URL:

from sqlalchemy.engine import URL
connection_url = URL.create(
    "mssql+pyodbc",
    username="scott",
    password="tiger",
    host="myhost",
    port=49242,
    database="databasename",
    query={
        "driver": "ODBC Driver 17 for SQL Server",
        "authentication": "ActiveDirectoryIntegrated",
    },
)

Pass through exact Pyodbc string

A PyODBC connection string can also be sent in pyodbc's format directly, as specified in the PyODBC documentation, using the parameter odbc_connect. A _sa.engine.URL object can help make this easier:

from sqlalchemy.engine import URL
connection_string = "DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password"
connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})

engine = create_engine(connection_url)

Connecting to databases with access tokens

Some database servers are set up to only accept access tokens for login. For example, SQL Server allows the use of Azure Active Directory tokens to connect to databases. This requires creating a credential object using the azure-identity library. More information about the authentication step can be found in Microsoft's documentation.

After getting an engine, the credentials need to be sent to pyodbc.connect each time a connection is requested. One way to do this is to set up an event listener on the engine that adds the credential token to the dialect's connect call. This is discussed more generally in :ref:`engines_dynamic_tokens`. For SQL Server in particular, this is passed as an ODBC connection attribute with a data structure described by Microsoft.

The following code snippet will create an engine that connects to an Azure SQL database using Azure credentials:

import struct
from sqlalchemy import create_engine, event
from sqlalchemy.engine.url import URL
from azure import identity

SQL_COPT_SS_ACCESS_TOKEN = 1256  # Connection option for access tokens, as defined in msodbcsql.h
TOKEN_URL = "https://database.windows.net/"  # The token URL for any Azure SQL database

connection_string = "mssql+pyodbc://@my-server.database.windows.net/myDb?driver=ODBC+Driver+17+for+SQL+Server"

engine = create_engine(connection_string)

azure_credentials = identity.DefaultAzureCredential()

@event.listens_for(engine, "do_connect")
def provide_token(dialect, conn_rec, cargs, cparams):
    # remove the "Trusted_Connection" parameter that SQLAlchemy adds
    cargs[0] = cargs[0].replace(";Trusted_Connection=Yes", "")

    # create token credential
    raw_token = azure_credentials.get_token(TOKEN_URL).token.encode("utf-16-le")
    token_struct = struct.pack(f"<I{len(raw_token)}s", len(raw_token), raw_token)

    # apply it to keyword arguments
    cparams["attrs_before"] = {SQL_COPT_SS_ACCESS_TOKEN: token_struct}

Enable autocommit for Azure SQL Data Warehouse (DW) connections

Azure SQL Data Warehouse does not support transactions, and that can cause problems with SQLAlchemy's "autobegin" (and implicit commit/rollback) behavior. We can avoid these problems by enabling autocommit at both the pyodbc and engine levels:

connection_url = sa.engine.URL.create(
    "mssql+pyodbc",
    username="scott",
    password="tiger",
    host="dw.azure.example.com",
    database="mydb",
    query={
        "driver": "ODBC Driver 17 for SQL Server",
        "autocommit": "True",
    },
)

engine = create_engine(connection_url).execution_options(
    isolation_level="AUTOCOMMIT"
)