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}"
)

[YOUKE · Accepted solution]

I was able to solve the problem! the problem was because the driver was missing and so pyodbc or sqlAlchemy can't find it. So I used the native Java API and it is working.
This is the example code:

jdbcUsername = "username"
jdbcPassword = "password"
driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbcUrl = "jdbc:sqlserver://prtc.database.windows.net:1433;database=db02"
connectionProperties = {
"user": jdbcUsername,
"password": jdbcPassword,
"driver": driverClass
}
connection = spark._jvm.java.sql.DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword)

stmt = connection.createStatement()
sql = "delete from dbo.ExampleTable where ID = 2"

stmt.execute(sql)
connection.close()