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 pymssqlworks now
- All naive me, I started with a simple:
- 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.
Pinkintello 