MS SQL Driver?

ihf

Is there a driver that will permit sqlalchemy to communicate with MS SQL Server? pyodbc requires c libraries so it cannot be installed. MySQL and pymysql work fine but I need to connect to MS SQL.

ihf

I found the pypyodbc driver which is pure python but when I try to import it I get this error:

import pypyodbc
Traceback (most recent call last):
File "<string>", line 1, in <module>
File "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages-2/pypyodbc.py", line 440, in <module>
raise OdbcNoLibrary('ODBC Library is not found. Is LD_LIBRARY_PATH set?')
pypyodbc.OdbcNoLibrary: 'ODBC Library is not found. Is LD_LIBRARY_PATH set?'

cvp

@ihf seems to work only on MacOS and Linux, and to be not really "pure Python"

ihf

That's too bad. It would appear there is no way to get sqlalchemy on Pythonista to connect to SQL Server.

sulcud

@ihf check this and try something similar to check if it works https://stackoverflow.com/questions/15750711/connecting-to-sql-server-2012-using-sqlalchemy-and-pyodbc

JonB

pyodbc is written in c.

I think some options would be to run a bridge program on your PC, or even something like a raspberry pi, that acts like a sql server, but just passes cursor commands/data to the mssql server.

ihf

The drivers work with MySQL but I guess no one has seen a need for a pure python driver for MS SQL.

ihf

In case anyone else has the need to use Pythonista to talk to MS SQL server, I just found and successfully tested the pure python MS SQL driver python-tds.

cvp

@ihf I don't need it but that's a good news

rushjob

@ihf, I'm hoping you can point out something simple I have wrong with my python-tds install!

I managed to clone the sqlalchemy_pytds dir from https://github.com/m32/sqlalchemy-tds.git but and I gave pyton-tds (1.11.0) installed according to pip list in stash but when I try and run the below code:

import sqlalchemy
import sqlalchemy_tds

from sqlalchemy import create_engine

conn_str = 'mssql+pytds://xxx'
engine = create_engine(conn_str)

I get the error:
The 'python-tds' distribution was not found and is required by the application.

Hope you have come across this error in your attempts to get pytds working for mssql on pythonista and can point me in the right direction!

Thanks

JonB

Restart pythonista.
Type import pytds
Do you get an error?

If so, check that you actually installed pytds to your site-packages folder.

rushjob

Thanks for the reply. If I just test the script:

import pytds

I get the same “ pkg_resources.DistributionNotFound: The 'python-tds' distribution was not found and is required by the application” error.

I do have the pytds folder in my site-packages -3 folder though.

Given the error is from the line:

    __version__ = pkg_resources.get_distribution('python-tds').version

In the init.py from pytds, I wonder if I’m missing that package somehow (if so, any ideas how I could get it?)

Thanks

JonB

Oh, I see the prob. That setup.py is sort of wonky, in that it tries to run git to get the version. I'm not exactly sure what pkg_resources is pulling from, if pip is supposed to populate some database, or what, but it doesn't work in stash.

Since this is only failing on getting the version number, you should be able to hard code it.

What happens if you just replace that line in the unit with this?

__version__ = "1.11.0"

sebastianwild

Hard coding the version number works a treat 🙏