- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-17-2025 07:51 AM - edited 03-17-2025 07:55 AM
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-17-2025 09:11 AM
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}"
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-27-2025 03:21 AM
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()
