How to connect SqlAlchemy to Azure SQL database

Question

I am trying to connect to my Azure SQL Database by using SqlAlchemy. Currently, when I try to use the code below I get the following errors. Currently Error 1 listed below, but I have also gotten the others while trying different approaches.

Error 1 (if I leave the code as written below):

(pyodbc.Error) ('IM010', '[IM010] [Microsoft][ODBC Driver Manager] Data source name too long (0) (SQLDriverConnect)') (Background on this error at: https://sqlalche.me/e/20/dbapi) pyodbc.Error: ('IM010', '[IM010] [Microsoft][ODBC Driver Manager] Data source name too long (0) (SQLDriverConnect)')

Error 2:

pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Error 3: This happens on the 'engine = ...' line if I don't use urllib

ValueError: invalid literal for int() with base 10

What is incorrect with my code, and how do I connect to my Azure SQL Database with SqlAlchemy?

import pyodbc
import time
import GW_PDR_Queries   as qu
import pandas           as pd
import sqlalchemy
import urllib

server   = 'myserver.database.windows.net'
database = 'myDatabase'
username = 'myUsername'
password = 'myPassword'   
# driver   = '{ODBC Driver 17 for SQL Server}'
driver   = 'Driver={SQL Server}'
# connection_string = 'DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password
connection_string = driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password
# connection_string = username + '@' + server + ':' + password + '@' + server

connection_string = urllib.parse.quote_plus(connection_string)

#'DRIVER={SQL Server};SERVER=yoursqlAzureServer.database.windows.net,1433', user='yourName@yoursqlAzureServer', password='Password', database='DBName'
engine = sqlalchemy.create_engine('mssql+pyodbc://'+ connection_string)
# engine = sqlalchemy.create_engine(connection_string)

query = 'SELECT * FROM panel.panel_table'

with engine.begin() as conn:
    df = pd.read_sql_query(sql=text(query), con=conn)

Answer 1

To connect sqlalchemy to Azure SQL Database Follow below code:

pyodbc.Error: ('IM010', '[IM010] [Microsoft][ODBC Driver Manager] Data source name too long (0) (SQLDriverConnect)')

If your username or password contain @ make sure convert it in to %40

pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

To resolve above error, make sure you have proper driver installed locally.

My sample Code:

import urllib
from sqlalchemy import create_engine,text
import pandas as pd

server   = 'servername.database.windows.net'
database = 'databasename'
username = 'username'
password = 'password!'   
driver   = '{ODBC Driver 17 for SQL Server}'

conn = f"""Driver={driver};Server=tcp:{server},1433;Database={database};
Uid={username};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)

query = 'SELECT @@version'

with engine.connect() as connection:
    df = pd.read_sql_query(sql=text(query), con=connection)
print(df.to_markdown())

Here I am querying SQL version from SQL and storing it into dataframe.