MSSQL Server Connection in Python

MSSQL Server Connection in Python

10/03/19   5 minutes read     954 Naren Allam

pythonsqlbash

In General, to establish a connection and manage queries in MS SQL Server, one should have Server Management Studio installed in your system if you are using Windows whereas if your are using Linux , you can use Azure Data Studio.

There are two ways to establish connection programmatically using python:-

    - pyodbc
    - pymssql

Procedure using pyodbc:

 ▹  Install latest ODBC driver packages on Linux (Ubuntu) or Debian based as follows

BASH  Copy
                    
                      $ sudo su
$ curl 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

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

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

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

#For Ubuntu 18.10
$ curl https://packages.microsoft.com/config/ubuntu/18.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
$ ~/.bashrc<br>

# Optional: for unixODBC development headers
$ sudo apt-get install unixodbc-dev<br>
                    
                  

Source: docs.microsoft.com

 ▹  Create a virtualenv and activate (vituralenv -p python3 venv)

 ▹  Install python odbc packages (pip install pyodbc)

Once done with the installation, create a connection string and connect to MS SQL server as follows

PYTHON  Copy
                    
                      import pyodbc
server = 'tcp:nameoftheserver'
port = '1433' # Optional
username = 'name_of_the_user'
password = 'user_password'
database = 'db_name'
cnxn = pyodbc.connect(f'DRIVER={ODBC Driver 17 for SQL Server};SERVER='+\
        server+';DATABASE='+database+';UID='+username+\
        ';PWD='+ password)
cursor = cnxn.cursor()
                    
                  

  and now you can start making queries...

Conclusion:-
  I hope this post will be helpful to you. If you have any doubts regarding the above discussed topic, feel free to ask me by writing in the comment box.