LOADING

Type to search

How To Connect Python to Microsoft SQL Server

To Know more about the Different Corporate Training & Consulting Visit our website www.Instrovate.com Or Email : info@instrovate.com or WhatsApp / Call at +91 74289 52788

Data Analytics Python

How To Connect Python to Microsoft SQL Server

Share
Prerequisites To Follow this Exercise :
  1. Microsoft SQL Server Database Express Edition & Adventure Works DataWarehouse – If you Don’t have a Microsoft SQL Server express Database and want to install it in your system and also install AdventureWorks DW , Follow –https://instrovate.com/2019/05/22/download-install-free-microsoft-sql-server-install-adventureworks-database-data-warehouse/
  2. Python Installed in your System : If you are a new user to Python and want to know how to install Python via the Anaconda Distribution , You can go through the step by step Blog i have written to install Python via Anaconda Distribution & start using Jupyter Notebook : https://instrovate.com/2019/06/09/python-anaconda-distribution-how-to-download-and-install-it-and-run-the-first-python-program/

Once you have the Microsoft SQL Server Express Edition and Python Installed in your system you are Good to Go ahead and follow the below Use Case and Example.

Connecting Python with SQL Server
 If you are looking for Python Corporate Training – Instrovate Corporate Training
 If you are looking for Python Consulting Work – Instrovate Consulting Services

IN porder to connect python to Microsoft SQL Server we first need to set up ODBC Connection in system control-panel. Following are the steps for the same:

  1. Open Control Panel
  2. In the search Bar on top right corner type ODBC:

How To Connect Python to Microsoft SQL Server 29

(3) As per your System Configuration click on the link either Set up ODBC data sources (32-bit) or Set up ODBC data sources (64-bit)

(4) After clicking below screen will get displayed :

How To Connect Python to Microsoft SQL Server 30

(5) Click on the Add Button :

How To Connect Python to Microsoft SQL Server 31

(6) After clicking add following screen is displayed:

How To Connect Python to Microsoft SQL Server 32

(7) Click on the SQL Server Native Client 11.0 as indicated above

(8) Now click FInish  and then below screen will be displayed:

How To Connect Python to Microsoft SQL Server 33

(9) IN the name filed give some name to your new DataBase source.

(10) IN description try to give some description of the ODBC Data SOurce.

(11) IN the server column type in the Server name or DSN of your SQL Server Database installed on your system

How To Connect Python to Microsoft SQL Server 34

(12) After this click on Next. After which below screen will be displayed :

How To Connect Python to Microsoft SQL Server 35

(13) Click ON Next to continue to get the below screen

How To Connect Python to Microsoft SQL Server 36

(14 ) After clicking Next below screen is displayed in which click finish

How To Connect Python to Microsoft SQL Server 37

How To Connect Python to Microsoft SQL Server 38

How To Connect Python to Microsoft SQL Server 39

(15) Click Test Data SOurce which must be successful if all the details filled are correct

How To Connect Python to Microsoft SQL Server 40

(17) Click OK and then in the next screen again click OK to get the initial Set up DOne

(18) NOw in order to test the connection from python script first of all make sure that the SQL Server is up and running in your system.

(19) Below is the simple program to fetch the result of select query form the SQL Server Database:

How To Connect Python to Microsoft SQL Server 41

(20) pyodbc is a python library to establish an ODBC Connection to desired Data SOurce

(21) Following are the property needs to be set while establishing connection from python:

Driver – Name which we have added earlier while setting up ODBC COnfiguration

How To Connect Python to Microsoft SQL Server 32

 

Server – Server Name or DSN of SQL Server running on local or remote system

Database – Database Name to which connection needs to be established

Trusted_connection – By default is yes so need to be used as it as given in above

Program

(22) Running the above python code must display the content of the table queried in the program:

How To Connect Python to Microsoft SQL Server 43

Note :- The Database used here is AdventureWorks2017