Product and service reviews are conducted independently by our editorial team, but we sometimes make money when you click on links. Learn more.
 

How to Query Microsoft SQL Server from Linux

How to Query Microsoft SQL Server from Linux
By

System administrators must manage a heterogeneous environment full of different OS flavors, configurations and so on. Find out one way that will allow you to perform any number of T-SQL queries to a SQL Server machine.

In a perfect system administrator's world, everything would be the same. All servers would be identical, run the same workloads on the same configurations and perform equally. If only wishing made it so. System administrators must manage a heterogeneous environment full of different OS flavors, configurations and so on. This couldn't be more evident in the need to perform SQL queries from a Linux machine.

Read on to find out one way that will allow you to perform any number of T-SQL queries to a SQL Server machine. I'll be using Ubuntu 16.04, and I will be querying a SQL Server 2012 R2 machine. However, the same technique should apply to other Linux flavors and SQL Server versions.

MORE: SQL Server 2016 Certifications Roll Into Beta

To get started, we're going to need to install a few prerequisites. We're going to be querying SQL Server from Python, so we'll need a prerequisites Python module called PyODBC. This module allows you to query any number of ODBC databases. To get this installed, we'll use pip (the Python package manager).
sudo pip install pyodbc

If this does not work, you might not have pip installed. To install pip:
sudo easy_install pip

Next, we'll need to create a Python script. I'm going to call this one sql_server.py. To create a Python script, we'll first create a blank file.
touch sql_server.py

Then, using your editor of choice, add the following lines:
#!/usr/bin/python
import pyodbc

Then tells the Python interpreter that this is a Python script and we're going to use the pyodbc module. Save this and then execute the script.
python sql_server.py

If this runs without error, the pyodc module has been installed successfully. Next, we'll need to add the code to execute a test query. To do this, I'll be using an ODBC string. The ODBC string is picky about what's included. It took some time to figure out how to make this work but here's what mine looks like.

Copy it, try it out and see if it works. If not, it will at least be a good starting point to craft an ODBC string that will work with your database.

We'll pass this ODBC string as an argument to the connect() method that's included with the pyodbc module.
conn = pyodbc.connect('DRIVER={FreeTDS};SERVER=server.domain.local;PORT=1433;UID=DOMAIN\user;PWD=mypassword;DATABASE=mydatabasename;UseNTLMv2=yes;TDS_Version=8.0;Trusted_Domain=domain.local;')

Most of the ODBC string is evident, but one important fact is the double backslash for the UID. Always ensure that they're two backslashes than one there. Also, some of the options I'm using are optional. Feel free to add or remove them as you see fit.

Next, we need to create a cursor object that will allow us to pass a T-SQL statement to. This is done with the cursor() method.
cursor = conn.cursor()

Now we have an object with an execute() method that can be used to pass any T-SQL statement we'd like into.
cursor.execute("SELECT * FROM Table")
rows = cursor.fetchall()

This creates a rows variable containing the resulting dataset. Now's the point to decide what to do with the dataset. You might send the results to a CSV file, put the results into another database or simply write the contents to the console. Below, I'm printing the results to the console if the dataset was populated.

if rows:
    print(rows)

You can see that if just printed out to the console, the dataset isn't too pretty. At this point, it's up to you to decide how to format or parse the data from the database.