Connecting to an Azure database using SQLAlchemy in Python

Question

I am trying to connect to an Azure database using SQLAlchemy in Python.

My code is the following:

engine_azure = \
create_engine('mssql+pyodbc://{Server admin login}:{password}@{Server name}.database.windows.net:1433/{AdventureWorksLT}', echo=True)

I get the following message:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\connectors\pyodbc.py:92: SAWarning: No driver name specified; this is expected by PyODBC when using DSN-less connections
  "No driver name specified; "

Then I run the following code:

print(engine_azure.table_names())

I get the following message:

DBAPIError: (pyodbc.Error) ('01S00', '[01S00] [Microsoft][ODBC Driver Manager] Invalid connection string attribute (0) (SQLDriverConnect)')

Answer 1

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 pyodbc
from sqlalchemy import create_engine
import urllib

params = 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):