[FEATURE] PyODBC documentation / driver support for sqlalchemy for python projects

[justmike1]

Is your feature request related to a problem?
I want to use the odbc plugin of opensearch but either it is not possible in python projects or the documentation are not concise when it comes to it because all I see are PowerBI or Tableau or OS installation docs.

What solution would you like?
I want a simple PyODBC connection string, same as how JDBC works...

What alternatives have you considered?
I have tried elasticsearch-dbapi but it isn't maintainable.
Nothing maintainable exists.

Do you have any additional context?
None.

[Swiddis]

When I was last messing with this, the issue was that while the Python ecosystem predominantly uses unixODBC for ODBC connections, our ODBC driver uses iODBC. I don't think they're generally interchangeable, so it'd take some refactoring work to change our system to use unixODBC. I vaguely recall some issues on the Python side regarding iODBC support, e.g. mkleehammer/pyodbc#681, but I'm not sure it's an easy fix for them either and unixODBC is more widely used, so this might be a thorn for a while.

More info on the differences at What are the functional differences between iODBC and unixODBC? (stackexchange)

[Swiddis]

For what it's worth I tried digging up some old test code for the SQL ODBC driver with PyODBC, I verified the driver is installed correctly (and works via iODBC) but it doesn't seem to be usable via unixODBC.

import pyodbc # 5.2.0

assert "OpenSearch SQL" in pyodbc.drivers()
pyodbc.connect("Driver={OpenSearch SQL};DSN=OS-Localhost", encoding="utf-8")

$ python3 connect.py
Traceback (most recent call last):
  File "/Users/sawiddis/code/temp/odbc-tester/connect.py", line 4, in <module>
    pyodbc.connect("Driver={OpenSearch SQL};DSN=OS-Localhost", encoding="utf-8")
pyodbc.InterfaceError: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')