Working with MS SQL Server in Python on Linux

Today we’re turning our attention to Python, and looking at how you can connect to a Microsoft SQL Server database running on Linux using Python. In this tutorial, we’ll look at establishing a connection and running a few sample queries.

As in our PHP and Node tutorials, we’ll assume that you’re running Ubuntu and that you’ve got most of the prerequisites installed. For Python, we’re going to use the industry-standard Python 3 syntax, at the latest version, 3.7. If you’re running a different operating system or different Python version, you may have to change a few things but the gist should be the same.

Install PyODBC

Once again, there’s very little in the way of prerequisites here. We’re using pyodbc, which you can install via pip:

pip install pyodbc

Other common libraries, such as the popular SQLAlchemy, can be used on Linux too and provide a complete ORM experience if that’s more up your alley.
Build A Connection
PyODBC is a little finnicky in how you connect to a database: you have to build the connection string yourself. I find it’s easiest to create a dictionary and use Python 3’s string formatting to do so:

import pyodbc

details = {
 'server' : 'localhost',
 'database' : 'MyDB',
 'username' : 'me',
 'password' : 'myPass'
 }

connect_string = 'DRIVER={{ODBC Driver 13 for SQL Server}};SERVER={server};PORT=1443; DATABASE={database};UID={username};PWD={password})'.format(**details)

connection = pyodbc.connect(connect_string)
print(connection)

Put that in a .py file and run it, and you should see the connection object information printed out in your terminal. If you’re getting an error, make sure you’ve correctly copied the syntax for the connection string, including the double braces around the driver name. Those tell the formatting function that the insides of the braces aren’t variable names, they’re part of the connection string itself.

Running Database Queries

After you establish a connection, pyodbc makes it easy for you to grab a cursor and begin running queries. The cursor class implements Python’s magic “state managers”, which means you can use it in a with-clause and wait on the results. This makes running queries much easier: you just put them in a with-clause and you don’t have to worry about closing the cursor.

As in our other examples, we’ll assume you’ve got a database with some basic employee data and do some simple manipulations to it:

#grab a new cursor object from the connection
cursor = connection.cursor()

tsql = "INSERT INTO ExampleSchema.Employees (Name, Site) VALUES (?,?);"
#we can use the cursor’s with-clause syntax with the values
with cursor.execute(tsql,'Donovan','Satellite Work Center'):
  print ('Successful Insertion!')

Other statements are just as simple. To read the data back out, we use the same basic structure and read out the cursor’s results from inside the with-clause:

tsql_select = "SELECT Name, Site FROM ExampleSchema.Employees;"
with cursor.execute(tsql_select):
  row = cursor.fetchone()
  while row:
    print (str(row[0]) + " works at " + str(row[1]))
    row = cursor.fetchone()

And updates and deletes are done in a similar fashion.

Python Best Practices

Because pyodbc makes extensive use of the while-clause structure, you may want to review that syntax to make sure you know when your cursors are opening and closing. In fact, the same syntax can be used for the connection itself, but having multiple nested layers of with-clauses is not very Pythonic. Instead, if you need to close the connection manually, you can use connection.close().