/ blog

: Aaron Sparks | : 2019-12-27 | : python, odbc, pyodbc, freetds, databases, sql server, azure


Python
Screenshot of one of the HTML/Jinja templates for this website. (Photo credit - Aaron Sparks)

I've been working on a consulting project that required ODBC connectivity between some Python code and a SQL Server database hosted in Azure. The Microsoft instructions for installing the driver made this seem easy and supported. Unfortunately, I had a bunch of trouble that required a work-around.

Specifically, I kept getting a "segmentation fault error" when trying to connect with the Microsoft driver (msodbcsql17). Apparently, this has happened to a few others, but their issue was resolved with upgrading to the latest version (v17 at the time of drafting this post). That didn't resolve the issue for me and my problem wasn't intermittent. I fiddled around with driver versions, unixODBC, and a host of other potential fixes. None of them worked. Eventually, I landed on using FreeTDS instead of the Microsoft driver.

FreeTDS setup
I found these instructions for installing and configuring the FreeTDS driver. My implementation was slightly different than the instructions. Similar to the instructions, I installed unixodbc and freetds via Homebrew with brew install unixodbc freetds.

And then I made one slight change to the /usr/local/etc/freetds.conf file. My TDS protocol version is slightly different (i.e. 7.4 vs 7.3) because 7.4 was recommended for Azure SQL Server databases. I then followed the remainder of the steps for odbcinst.ini and odbc.ini files.

Python implementation
After you get FreeTDS set up, you'll want to test it out. I wrote a quick test connector on the Python-side that uses pyodbc and SQLAlchemy. Here is the sample code:

import pandas as pd
import pyodbc
from sqlalchemy import create_engine
import urllib

# SQL Server vars
sql_server_db_username = 'yourdbuser'
sql_server_db_password = 'yourpassword'
sql_server_db_name = 'yourdb'
sql_server_server_name = 'yourserver'

params = urllib.parse.quote_plus("DRIVER={FreeTDS};SERVER=" + sql_server_server_name + ",1433;DATABASE=" + \
    sql_server_db_name + ";UID=" + sql_server_db_username + ";PWD=" + sql_server_db_password+';TDS_VERSION="7.4"')
db_connection_string = "mssql+pyodbc:///?odbc_connect={}".format(params)
engine = create_engine(db_connection_string)
query = 'SELECT * FROM yourtable;'
df = pd.read_sql(query, connection)
print(df)



Hope this helps you connect quickly!


Like the blog post? Spread the word!
         

about aaron

Foo
Here's a brief rundown on the author of this blog and contact info if you want to reach out.