ctds adoption

[sylvainr]

Speaking of adoption (previous thread), I think a very impactful feature would be to have a SQAlchemy connector to ctds.

I have been on a quest for the perfect Python MSSQL driver for the last few months and each driver has issues:

• pymssql: No bulk insert support + stability issues reported for 1+ years (which crashes/seg-faults the python process). Pros: multi platform support in conda.
• pyodbc: No bulk insert support + not easy to setup. Some support in conda.
• pytds: Does have bulk insert support, but does not support SSL connections, which makes it unusable on Azure. Pros: pure python, easy to deploy.
• ctds: No SQLAlchemy connector. Not sure if it supports SSL either? Also, is it cross-plarform? It looks like there are some conda packages available for OS X and Linux but did not find anything for Windows.

I feel that the convenience of having a pre-compiled conda package is huge, especially when people using Python+SQL Server are frequently data scientist kind of personas, and tend to favor convenience above everything else.

A SQLAlchemy connector allows more advance users to easily swap their driver. I tried to write one for ctds but pretty much failed.

Finally, I don't think a lot of people know about ctds. Having a blog article on Zillow's tech blog (if it exists!) would definitely give it much more visibility.

[joshuahlang]

I will look into SSL support. It probably is supported since ctds is built on top of FreeTDS, which is also used by pymssql and pyodbc.

ctds is currently only officially supported on Linux and OS X. I had started working on Windows support long ago, but was side-tracked by more pressing things. I don't think much is needed to finish it, but currently I don't believe it will even compile, let alone work, on Windows.

I have never used conda, so any packages there were uploaded by someone else. It seems as though conda users will take care of this on their own?

[joshuahlang]

SSL/TLS support appears to be all handled by FreeTDS. If the Force Encryption option is enable (see the Properties dialog of the Protocols for MSSQLSERVER section of the SQL Server Configuration Manager), the following query can be used to confirm the connection is in fact encrypted:

select encrypt_option from sys.dm_exec_connections where session_id = @@SPID

Of course there isn't currently a way to force encryption in the client via ctds itself. I suppose it would be possible using the *encryption option in a freetds.conf file, though that is a bit of a hack in my opinion.

[joshuahlang]

ctds 1.5.0+ supports Windows

[joshuahlang]

I think a SQLAlchemy dialect similar to https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/dialects/mssql/pymssql.py would suffice, though I'm not sure packaging it with ctds is better than as a separate egg.

[sylvainr]

I'd think a different package is better. I tried reusing another dialect as an example but it was not that obvious to me. I am sure it's doable though.

[wikiped]

There is an implementation for pytds which perhaps might be helpful.

[joshuahlang]

I have something sort of working, though it isn't passing all of the SQLAlchemy tests as of yet...

https://github.com/joshuahlang/sqlalchemy-ctds