I am trying to connect a database using DSN, when I setup the database it did not asked for any username and password, instead i provided the DSN name and selected the security authentication as "Azure Active Directory Integrated Authentication".
I can connect like this
conn = pyodbc.connect(DSN='EAP') # EAP is my DSN name, and it works fine
df = pd.read_sql(myquery, conn)
conn.close()
# this query does not fail, just gives the warnings.
# I would like to use engine method, instead of this connection method.
warning:
pandas only support SQLAlchemy connectable(engine/connection)
ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2
objects are not tested, please consider using SQLAlchemy
Question: How to get the same query using engine, instead of conn?
My Attempts
import sqlalchemy
from sqlalchemy.engine import URL
from sqlalchemy import create_engine
#conn_str = "DRIVER={ODBC Driver 17 for SQL Server};DSN=EAP"
conn_str = "DRIVER={msodbcsql17.dll};DSN='EAP'"
conn_url = URL.create("mssql+pyodbc", query={"odbc_connect": conn_str})
engine = create_engine(conn_url)
# engine = sqlalchemy.create_engine('mysql+pyodbc://EAP')
parent = pd.read_sql(myquery, engine)
Gives ERROR: InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') (Background on this error at: https://sqlalche.me/e/14/rvf5)
References:
- There are literally infinite materials similar to this question, but none of them could solve my case.
- https://docs.sqlalchemy.org/en/14/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pyodbc
- Pandas to_sql not working with SQL Alchemy connection
- sqlalchemy, specify database name with DSN