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?

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?

• pyspark
• sqlalchemy
• azure-databricks
• pyodbc

• Does this answer your question? – snakecharmerb Commented Mar 17 at 16:52
• This question is similar to: Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect) while connect with sqlalchemy. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. – Starship Remembers Shadow Commented Mar 17 at 19:22
• The driver is missing, so pyodbc or SQLAlchemy can't find it. – Dileep Raj Narayan Thumula Commented Mar 19 at 8:05
• Dileep Faj Narayan Thumula, I also thought it might be that the driver doesn't exist, do you have any idea how I can intall it on databricks? – Error 404 Commented Mar 19 at 16:38

2 Answers 2

I have tried the same approach like you and I have received the same ERROR like you as shown below:

I have tried using the SCALA

Below is the code I have tried:

%scala
import java.util.Properties
import java.sql.DriverManager
val jdbcUsername = "<your username>"
val jdbcPassword = "<YOUR PWD>"
val driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
val jdbcUrl = s"jdbc:sqlserver://prtc.database.windows:1433;database=db02;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows;loginTimeout=30;"
val connectionProperties = new Properties()
connectionProperties.put("user", s"${jdbcUsername}")
connectionProperties.put("password", s"${jdbcPassword}")
connectionProperties.setProperty("Driver", driverClass)
val connection = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword)
val stmt = connection.createStatement()
val sql = "delete from File_source where FileId = 4"
stmt.execute(sql)
connection.close()

Results:

In the above code using the com.microsoft.sqlserver.jdbc.SQLServerDriver Driver to connect to the Sql server and establishing connection. Then I have tried deleting a row from the table in your case you try deleting the table.

Dileep Raj Narayan Thumula first of all, I would like to thank you for the explanation. The code with Scala works! I rewrote it in PySpark, and for the database connection part, I used the native Java API, and it worked!

jdbcUsername = "username"
jdbcPassword = "password"
driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbcUrl = "jdbc:sqlserver://prtc.database.windows: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()