How To: Connect to MS-SQL From Python 2 Inside Linux

Recently I have been working on a machine learning project that requires Python 2.x and we needed to connect to an MS-SQL server to pull and push data.

My initial thinking was to use pyodbc which is a Python based ODBC bridge. It is open-source (on GitHub), easy to install and use.

I am running Ubuntu on Windows which is freely available on the Microsoft Store at https://www.microsoft.com/en-us/p/ubuntu/9nblggh4msv6 . All of the instructions below will work for any Ubuntu 18.1x though, you don’t have to be running it on Windows.

Before you install pyodbc though, you’ll want to make sure you have already installed the Microsoft ODBC driver. Luckily Microsoft has a published article with explicit instructions for installing the driver on Debian, RedHat, SUSE, Ubuntu and even MacOS. You can find the detailed instructions here:
https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

Here is the exact code I used for my Ubuntu 18.04.2 OS

sudo su 
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
sudo ACCEPT_EULA=Y apt-get install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
sudo apt-get install unixodbc-dev

After you’ve installed your Microsoft ODBC drive you can then just run a pip install for ODBC:

pip install pyodbc

Now that the driver has been installed, and pyodbc has been installed you can easily create a new connection from Python into your MS-SQL server like this:

conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
'Server=[SERVER];'
'Database=[DATATBASE_NAME];'
'Uid=[USERNAME];'
'Pwd=[PASSWORD];', autocommit = True)

You will need to replace:
[SERVER] text with server URL or IP address of your server
[DATABASE_NAME] text with your database name
[USERNAME] text with the username to connect to your database with
[PASSWORD] text with the password to connect to your database with

So you may end up with something like this:

conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
'Server=mysqlserver.database.windows.net;'
'Database=ConocoProductionDB;'
'Uid=bsmith;'
'Pwd=TetPp3CMGE4zvR7B;', autocommit = True)