Mac: Connection failed, cannot find data source or driver

[lazarillo]

Converting from pyodbc to turbodbc

I just stumbled upon turbodbc. I am always looking for something to clean up the challenging mess that is connecting to DBs.

Driver File

I have code that already works using pyodbc. Here is my .ini file:

❯ cat /usr/local/etc/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.17.dylib
UsageCount=1

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.13.dylib
UsageCount=1

Using pyodbc

Using pyodbc -- or sqlalchemy via pyodbc -- I can already successfully call this either with a string, like:

>>> from pyodbc import connect
>>> connection = connect(connect_str)
>>> cur = connection.cursor()
>>> cur.execute("SELECT TOP 10 AccountKey, AccountNumber FROM [dim].[Account]")
<pyodbc.Cursor object at 0x7fa3e171f630>
>>> cur.fetchall()
[('?', '?'), ('2751', '2751'), ('3263', '3263'), ('1230', '1230'), ('3264', '3264'), ('0110', '0110'), ('3340', '3340'), ('2522', '2522'), ('1611', '1611'), ('0591', '0591')]

where connect_str is:

>>> connect_str = (
...     "Driver={ODBC Driver 17 for SQL Server};SERVER=tcp:"
...     f"{server},1433;Database={db};Uid={user};Pwd={pwd};Encrypt=yes;"
...     "TrustServerCertificate=no;Connection Timeout=30;"
... )

or using the key-value pairs:

>>> connection = connect(
...     "Driver={ODBC Driver 17 for SQL Server}",
...     server=server,
...     database=db,
...     uid=user,
...     pwd=pwd
... )

Trying with turbodbc

But when I try to do the exact same commands with turbodbc, it fails. First, with the string:

>>> turboconnection = turbocon(connect_str)
Traceback (most recent call last):
  File "/usr/local/Caskroom/miniconda/base/envs/dli/lib/python3.8/site-packages/turbodbc/exceptions.py", line 41, in wrapper
    return f(*args, **kwds)
  File "/usr/local/Caskroom/miniconda/base/envs/dli/lib/python3.8/site-packages/turbodbc/connect.py", line 38, in connect
    connection = Connection(intern_connect(connection_string,
turbodbc_intern.Error: ODBC error
state: IM012
native error code: 0
message: [unixODBC][Driver Manager]DRIVER keyword syntax error

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/Caskroom/miniconda/base/envs/dli/lib/python3.8/site-packages/turbodbc/exceptions.py", line 43, in wrapper
    raise DatabaseError(str(e))
turbodbc.exceptions.DatabaseError: ODBC error
state: IM012
native error code: 0
message: [unixODBC][Driver Manager]DRIVER keyword syntax error

Next, using the key-value pairs:

>>> turboconnection = turbocon(host=server, user=user, password=pwd, database=db)
Traceback (most recent call last):
  File "/usr/local/Caskroom/miniconda/base/envs/dli/lib/python3.8/site-packages/turbodbc/exceptions.py", line 41, in wrapper
    return f(*args, **kwds)
  File "/usr/local/Caskroom/miniconda/base/envs/dli/lib/python3.8/site-packages/turbodbc/connect.py", line 38, in connect
    connection = Connection(intern_connect(connection_string,
turbodbc_intern.Error: ODBC error
state: IM002
native error code: 0
message: [unixODBC][Driver Manager]Data source name not found and no default driver specified

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/Caskroom/miniconda/base/envs/dli/lib/python3.8/site-packages/turbodbc/exceptions.py", line 43, in wrapper
    raise DatabaseError(str(e))
turbodbc.exceptions.DatabaseError: ODBC error
state: IM002
native error code: 0
message: [unixODBC][Driver Manager]Data source name not found and no default driver specified

Next Steps

Because my code already works on pyodbc, and it is code in production, I will probably not switch to turbodbc. But if it is helpful to turbodbc, I am happy to work together to solve it and end up with a better DB connection. :)

I can try to help debug it, if I can be given some guidance. I am fairly strong in Python, but clueless to much of this DB connector world.

[xhochy]

These ODBC problems are really hard to debug as ODBC itself doesn't provide any indicators in the error message.

Can you try running the turbodbc code in an environment where you have manually set ODBCSYSINI=/usr/local/etc? It could be that turbodbc and pyodbc are looking into different paths.

[mk0417]

I had a similar issue. Thanks @xhochy, and I can build the connection successfully after I set the path.