search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.88k stars 562 forks source link

Error in connecting databricks with pyodbc #1223

Closed Atul997 closed 1 year ago

Atul997 commented 1 year ago

I am following the below link to configure data bricks with SQL - configure data bricks

I am getting the following error -

pyodbc.OperationalError: ('08001', '[08001] [Simba][ODBC] (10380) Unable to establish connection with data source. Missing settings: {[Host]} (10380) (SQLDriverConnect)')

Python version - Python 3.8.10 Pyodbc - 4.0.39 driver- ODBC

Output for odbcinst -j

unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/xxxx/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

/etc/odbc.ini looks like

ODBC Data Sources]
SQL_Warehouse=Simba Spark ODBC Driver
Databricks=Databricks ODBC Connector

[SQL_Warehouse]
Driver=/opt/simba/spark/lib/64/libsparkodbc_sb64.so
Host=xxxxxx.cloud.databricks.com
Port=443
HTTPPath=sql/protocolv1/o/6444514236792964/1111-145008-asps751
ThriftTransport=2
SSL=1
AuthMech=3
UID=xxxxxxxxx
PWD=xxxxxxxxx

/etc/odbcinst.ini looks like

ODBC Drivers]
Simba SQL Server ODBC Driver=Installed

[Simba Spark ODBC Driver]
Driver=/opt/simba/spark/lib/64/libsparkodbc_sb64.so
v-chojas commented 1 year ago

Does isql -v -k '<your connection string here>' work to connect?

Atul997 commented 1 year ago

What should I put at connection string and run ?

v-chojas commented 1 year ago

The same connection string that you would give pyODBC.

Atul997 commented 1 year ago

This shows me following error [S1000][Simba][ODBC] (11560) Unable to locate SQLGetPrivateProfileString function: [Simba][DSI] An error occurred while attempting to retrieve the error message for key 'LibsLoadErr' with message parameters ['"", "/usr/local/Cellar/unixodbc/2.3.9/lib/libodbcinst.dylib"'] and component ID 3: Message not found in file "/opt/simba/spark/ErrorMessages/en-US/ODBCMessages.xml" [ISQL]ERROR: Could not SQLDriverConnect

v-chojas commented 1 year ago

You'll need to get the ODBC driver working before you can use pyODBC. That error message suggests it's related to unixODBC, so try a newer version of it, and if that doesn't work, you can ask here: https://github.com/lurcher/unixODBC/issues

In any case this doesn't appear to be a pyODBC issue.

gordthompson commented 1 year ago

odbcinst -j reports

unixODBC 2.3.6

but the error from isql shows the path

/usr/local/Cellar/unixodbc/2.3.9/lib/libodbcinst.dylib

so it looks like the unixODBC setup is incorrect.