Connecting to SQL on Databricks Using SQLAlchemy or pyodbc

[YOUKE]

On Databricks, when I try to connect to SQL using SQLAlchemy or pyodbc to run delete queries on a specific table, I get this error: (pyodbc.Error) ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)".

"""

import sqlalchemy
import urllib
server_ = "<server>"
username = "<usrname>"
password = "pwd"
SQLCred = f"UID="+username+";PWD="+password+";"
params = urllib.parse.quote_plus("DRIVER={ODBC Driver 17 for SQL Server};SERVER="+server_+";DATABASE=db_test;" + SQLCred + "Trusted_Connection=Yes;")
engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
conn = engine.connect()

"""

Does anyone have any idea why this error is generated and how it could be fixed?

[MariuszK]

This code will not work. You can try this:

import os
from sqlalchemy import create_engine

access_token = os.getenv("DATABRICKS_TOKEN")
server_hostname = os.getenv("DATABRICKS_SERVER_HOSTNAME")
http_path = os.getenv("DATABRICKS_HTTP_PATH")
catalog = os.getenv("DATABRICKS_CATALOG")
schema = os.getenv("DATABRICKS_SCHEMA")

engine = create_engine(
url = f"databricks://token:{access_token}@{server_hostname}?" +
f"http_path={http_path}&catalog={catalog}&schema={schema}"
)