Q: Is there a way to use this with ORMs, such as SQLAlchemy?

[max-sixty]

This looks awesome.

Is it as simple as swapping out a connection string for our existing pyodbc string?
connection_string = 'mssql+pyodbc:///?odbc_connect=%s' % connect_str
We're currently using a FreeTDS driver, would we change that?

Thanks

[MathMagique]

Hi!

Thanks for the question. SQLAlchemy support has not landed yet, so it is not that easy yet.

BUT: My colleague @bjoernmeier already put some work in that. He has even created a pull request for exasol/sqlalchemy-exasol#50, a project which adds a dialect for the Exasolution database. It is probably not as general as one would wish, but it is a start.

[MathMagique]

You would definitely keep your existing FreeTDS driver, though.

[MathMagique]

Short update: The pull request I mentioned above has been accepted.

[dirkjonker]

I'll try creating a SQLAlchemy dialect for mssql + turbodbc, should be fairly trivial, (except perhaps for unicode data).

[MathMagique]

@dirkjonker That would be really awesome! The unicode stuff needs fixing on the turbodbc side, obviously. You could have a look at the codebase of https://github.com/blue-yonder/sqlalchemy_exasol for inspiration. I am not really familiar with that code base, but I suspect a little cargo culting in there ;-).

If you encounter turbodbc issues, please keep reporting them :-)

[dirkjonker]

Status update on the MSSQL + turbodbc dialect. It works in certain situations but there are still some weird cases with "invalid cursor state" errors and situations where SQLAlchemy is calling rollback without an active transaction, which also raises errors. Those can probably be solved in the dialect, but I'll report if it's actually turbodbc that is causing problems.

As soon as I have something that works reasonably well I'll release an alpha version under MIT license, should be somewhere this week.

[MathMagique]

That sounds great!

[dirkjonker]

Took me a bit longer than expected (priorities, learning C, etc.) but I have created a very simple SQLAlchemy dialect for turbodbc + SQL Server.

https://github.com/dirkjonker/sqlalchemy-turbodbc

Feel free to fork/clone/report bugs/create pull requests/etc.

[MathMagique]

Excellent news @dirkjonker! Good job!

If you want, you could create a pull request to include a small SQLAlchemy section in the Readme that mentions your efforts and those at https://github.com/blue-yonder/sqlalchemy_exasol. Otherwise I will just add the note myself :-).

[dirkjonker]

@MathMagique of course! Here you go: #66

[MathMagique]

Since the answer to this question is now part of the Readme, and there is initial support for MSSQL with SQLAlchemy, I'll mark this question as resolved.