CODEWITHSUNDEEP
pythonsql-serverfreetds
jcrudy
jcrudy

Reputation: 4061

Error using mssql+pyodbc via sqlalchemy to connect to MS SQL Server from Linux

I have a Python application that runs on a Linux machine and connects to an MS SQL Server database running in a Windows Server inside a virtual machine. I have my reasons. My application makes some large queries and processes a lot of data. It mostly works fine, but every so often the program just crashes with the following:

python: read.c:207: tds_get_string: Assertion `dest_size >= (size_t) string_len' failed.

I'm not sure where to start with this. There is no Python stack trace. Rather, the program just prints the above and terminates. If this were a normal exception I could catch it and deal with it.

My sqlalchemy connection string is:

mssql+pyodbc://username:password@localhost:1433/database?driver=FreeTDS

If it's important, tsql -C gives the following output:

Compile-time settings (established with the "configure" script)

                        Version: freetds v0.91
         freetds.conf directory: /etc/freetds
 MS db-lib source compatibility: no
    Sybase binary compatibility: yes
                  Thread safety: yes
                  iconv library: yes
                    TDS version: 4.2
                          iODBC: no
                       unixodbc: yes
          SSPI "trusted" logins: no
                       Kerberos: yes

My guess is that my problem is the result of a bug in freetds. I installed freetds and unixodbc through apt-get on my Linux Mint machine. I installed pyodbc via pip, and I'm using anaconda. I've been experimenting with installing various different versions of freetds, unixodbc, and pyodbc from source. So far, I haven't found a combination that works at all. However, I don't really know what I'm doing. I'm looking for explanations, ideas, or workarounds. Or, of course, simple and complete solutions.

Upvotes: 4

Views: 9306

Answers (1)

jcrudy
jcrudy

Reputation: 4061

I found a workaround: don't use freetds. It turns out there is a microsoft odbc driver available for linux. I followed the instructions here to install it. I had to upgrade my kernel, among other things, to make it work. Once installed, I looked in my odbcinst.ini file and there was a new entry, "ODBC Driver 13 for SQL Server". I changed my sqlalchemy connections string to match it:

mssql+pyodbc://username:password@localhost:1433/database?driver=ODBC+Driver+13+for+SQL+Server

and now everything seems to be working. The microsoft driver feels significantly slower than freetds (I haven't actually timed it), but I'm not experiencing any of the errors I was before.

Upvotes: 11

Related Questions