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:
- Open Control Panel
- In the search Bar on top right corner type ODBC:
(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 :
(5) Click on the Add Button :
(6) After clicking add following screen is displayed:
(7) Click on the SQL Server Native Client 11.0 as indicated above
(8) Now click FInish and then below screen will be displayed:
(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
(12) After this click on Next. After which below screen will be displayed :
(13) Click ON Next to continue to get the below screen
(14 ) After clicking Next below screen is displayed in which click finish
(15) Click Test Data SOurce which must be successful if all the details filled are correct
(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:
(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
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:
Note :- The Database used here is AdventureWorks2017
