MS-SQL

Installation of MS-SQL driver — this is only required if data is on remote machine

Download MS-SQL driver as per your OS

Windows

for windows, it's easy as installing within a click. ODBC for MS-SQL is available here.

2. Install the .msi file as per your bit.

Linux — Ubuntu



curl sudo sucurl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - #Download appropriate package for the OS version

#Choose only ONE of the following, corresponding to your OS version

curl #Ubuntu 14.04curl https://packages.microsoft.com/config/ubuntu/14.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

curl #Ubuntu 16.04curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

curl #Ubuntu 18.04curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

curl #Ubuntu 18.10curl https://packages.microsoft.com/config/ubuntu/18.10/prod.list > /etc/apt/sources.list.d/mssql-release.list

curl #Ubuntu 19.04curl https://packages.microsoft.com/config/ubuntu/19.04/prod.list > /etc/apt/sources.list.d/mssql-release.list exit

sudo apt-get update

sudo ACCEPT_EULA=Y apt-get install msodbcsql17

# optional: for bcp and sqlcmd

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

# optional: for unixODBC development headers

sudo apt-get install unixodbc-dev

Linux — Debian



curl sudo sucurl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - #Download appropriate package for the OS version

#Choose only ONE of the following, corresponding to your OS version

curl #Debian 8curl https://packages.microsoft.com/config/debian/8/prod.list > /etc/apt/sources.list.d/mssql-release.list

curl #Debian 9curl https://packages.microsoft.com/config/debian/9/prod.list > /etc/apt/sources.list.d/mssql-release.list

curl #Debian 10curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list exit

sudo apt-get update

sudo ACCEPT_EULA=Y apt-get install msodbcsql17

# optional: for bcp and sqlcmd

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

# optional: for unixODBC development headers

sudo apt-get install unixodbc-dev

# optional: kerberos library for debian-slim distributions

sudo apt-get install libgssapi-krb5-2

Linux — RedHat

sudo su #Download appropriate package for the OS version

#Choose only ONE of the following, corresponding to your OS version

curl #RedHat Enterprise Server 6curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo

curl #RedHat Enterprise Server 7curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

curl #RedHat Enterprise Server 8curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo exit

sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts

sudo ACCEPT_EULA=Y yum install msodbcsql17

# optional: for bcp and sqlcmd

sudo ACCEPT_EULA=Y yum install mssql-tools

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc

source ~/.bashrc

# optional: for unixODBC development headers

sudo yum install unixODBC-devel

Mac OS



brew tap microsoft/mssql-release

brew update

brew install msodbcsql17 mssql-tools /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install )"brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release brew updatebrew install msodbcsql17 mssql-tools

Install pyodbc — ODBC package for python

install pyodbc package.

pip install pyodbc

2. To check whether the driver has installed properly, find all the drivers connected to pyodbc.

import pyodbc

pyodbc.drivers()

for MS-SQL it will result in

['ODBC Driver 17 for SQL Server']

As more drivers you will add to your system, more drivers will be added in the list.

Connect to database

For remote connection.

# enter ip address and port number of the system where the database resides.

server = 'tcp:31.288.186.65,49170'

database = 'database_name' # enter database name

username = 'user_name'

password = 'pass_word' # add appropriate driver name

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password) cursor = cnxn.cursor()

2. For Local connection (if data is in your local computer).



server = 'tcp:31.288.186.65,49170'

database = 'database_name' # enter database name cnxn = pyodbc.connect('DRIVER= {SQL Server} ;SERVER='+server+';DATABASE='+database+';Trusted_Connection=yes;') cursor = cnxn.cursor()

Query the database

you can query the database ie, select, insert, update or delete in your notebook.

your query can be directly converted to pandas DataFrame.

import pandas as pd # select command

query = ''' SELECT RecordID FROM tables''';

data = pd.read_sql(query, cnxn)

data.head()

Alternatives

Alternatively, you can use pymssql which works the same but it has been discontinued. Still, if you want to use it, you have to install by