Search code examples
pythonazuresqlalchemyodbcpyodbc

SQLAlchemy unable to execute query python

I have to conencto an Azure SQL database using Managed Identity. The connection is successful. The code I am using is:

self.logging.info("Connecting to database - connection string: {conn_str}".format(conn_str=self.conn_str))
credential = ManagedIdentityCredential(client_id = 'xxxxxxxxxxxxx') # TODO parametrize this client id, get from azurekeyvault
database_token = credential.get_token("https://database.windows.net/.default")
tokenstruct = self._bytes2mswin_bstr(database_token.token.encode())
                
self.engine = create_engine(self.conn_str,connect_args={'attrs_before': {self.SQL_COPT_SS_ACCESS_TOKEN:tokenstruct}}) 
self.logging.info("Connection successful")

I see in the logs that connection is successful: enter image description here

But when executing a query using read_sql:

result_df = pd.read_sql(query, con=self.engine)
self.logging.info('Query execution successfull, returned data dimensions: {shape}'.format(shape=result_df.shape))

it gives an error that: enter image description here

versions:

Name: **SQLAlchemy**
Version: 1.4.46
Summary: Database Abstraction Library
Home-page: https://www.sqlalchemy.org
Author: Mike Bayer
Author-email: mike_mp@zzzcomputing.com
License: MIT
Location: /anaconda/envs/localpy38/lib/python3.8/site-packages
Requires: greenlet
Required-by: 

Name: **pandas**
Version: 1.5.2
Summary: Powerful data structures for data analysis, time series, and statistics
Home-page: https://pandas.pydata.org
Author: The Pandas Development Team
Author-email: pandas-dev@python.org
License: BSD-3-Clause
Location: /anaconda/envs/localpy38/lib/python3.8/site-packages
Requires: numpy, python-dateutil, pytz

Name: **pyodbc**
Version: 4.0.35
Summary: DB API Module for ODBC
Home-page: https://github.com/mkleehammer/pyodbc
Author: 
Author-email: 
License: MIT
Location: /anaconda/envs/localpy38/lib/python3.8/site-packages
Requires: 

**ODBC drivers**

unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/azureuser/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

I am confused, how can it create connection first and then complain about user when executing the query. Also, The solution was working but my environment crashed and I had to create a new one. I dnt remember the versions in the previous environments.

Solution

  • There seemed to be some problem with string formatting. Also the token was not being passed from aqlalchemy to odbc.

    Followed the solution here: Pass Azure AD Token for Azure SQL DB in pre-connection arguments to SQLAlchemy create_engine()

    driver = "Driver={ODBC Driver 17 for SQL Server}"
server = ";SERVER={0}".format(environ.get('server'))
database = ";DATABASE={0}".format(environ.get('database'))
connString = driver + server + database
params = urllib.parse.quote(connString)
db = create_engine("mssql+pyodbc:///?odbc_connect={0}".format(params), 
connect_args={'attrs_before': {SQL_COPT_SS_ACCESS_TOKEN:tokenstruct}})