Connecting to MS SQL with pymssql and openquery

Have you ever struggled to install a module in python and connect to a MS SQL database with openquery? I spent a few hours browsing through Stackoverflow andpymssql documentation, downloading and uninstalling various modules until it finally worked.

Here is what I did (if you’ve had an easier install experience to make pymssql work for you, please share):

  • Installing pymssql:
      • All naive me, I started with a simple: pip install pymssql … but it did not work. So, I actually took the time to read the pymssqldocumentation that requires an install of freetds, a module that allows open source applications (such as Python) to connect to MS SQL
      • Using Homebrew and the steps outlined here, I did brew install freetds, but it failed due to a “DBVERSION_80” error.
      • Google to my rescue, and I found another tip that suggested to use the older protocol freetds_091. So I did: brew install homebrew/versions/freetds091
    • And voila: pip install pymssql works now
  • Running a query in python with openquery:
    • The pymssql module provides connections examples here
    • However, if you used to connect to MS SQL in Excel prior to your migration to Python, you may be worried about connecting to a linked server usingopenquery.  You need not worry, you can just re-use your Excel query asopenquery is compatible with python. So below is an excerpt of what I ended up with:

Excerpt of Code:

import pymssql
boo = pymssql.connect(server='some IP address', port='some port number',
user='username', password='password',
database='database/schema name'
)
cursor = boo.cursor()
cursor.execute("""
SET QUOTED_IDENTIFIER OFF
SELECT * FROM OPENQUERY(LinkedServer, "SELECT * ....")
""")
boom = cursor.fetchall()

Voila… If you need more info on using openquery, check this microsoft tip.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s