Connecting to an Azure database using SQLAlchemy in Python

There are 2 issues with your connection string:

1. As per the SQLAlchemy documentation: The delimeters must be URL escaped when using a pass-through exact pyodbc string.

2. And you do not specify the sql driver name either.

You can use the code below, which works fine at my side:

import pyodbcfrom sqlalchemy import create_engineimport urllibparams = urllib.parse.quote_plus \ # urllib.parse.quote_plus for python 3(r'Driver={ODBC Driver 13 for SQL Server};Server=tcp:yourDBServerName.database.windows.net,1433;Database=dbname;Uid=username@dbserverName;Pwd=xxx;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;')conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)engine_azure = create_engine(conn_str,echo=True)print('connection is ok')print(engine_azure.table_names())

Test result:

And for the connection string, you can get it by going to azure portal -> your database -> connection strings(select the ODBC in this case):

This is what i use in Python3:

params = urllib.parse.quote_plus(    'Driver=%s;' % driver +    'Server=tcp:%s,1433;' % server +    'Database=%s;' % database +    'Uid=%s;' % username +    'Pwd={%s};' % password +    'Encrypt=yes;' +    'TrustServerCertificate=no;' +    'Connection Timeout=30;')conn_str = 'mssql+pyodbc:///?odbc_connect=' + paramsengine = create_engine(conn_str)

Python3 snippet that I am using with ODBC Driver 17 for SQL Server. Cost me some time to figure it all out, especially the driver version and params.

import urllibfrom sqlalchemy import create_enginedriver = "{ODBC Driver 17 for SQL Server}"server = "<server-name>.database.windows.net"database = "<db-name>"user = "<db-user>"password = "<db-password>"conn = f"""Driver={driver};Server=tcp:{server},1433;Database={database};Uid={user};Pwd={password};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"""params = urllib.parse.quote_plus(conn)conn_str = 'mssql+pyodbc:///?autocommit=true&odbc_connect={}'.format(params)engine = create_engine(conn_str, echo=True)engine.execute("SELECT 1")

Furthermore, I needed to install the following drivers/tools on macOS:

brew install msodbcsql17 mssql-tools