By:- Bipul Kumar
Using DB objects from Microsoft SQL Server DB in Python
Overview: This document provides comprehensive details to connect the ‘SQL Server’ objects with the Python and ‘pyodbc’ library. In this article, we will be covering the installation of libraries, creating a parameter, and building connection for SQL server in Python (jupyter notebook). The following can also be used to run Python code: PyCharm, Visual Studio & Anaconda etc as per your convenience. Pre-requisites:
Install 3.0x Python on your machine.
Microsoft SQL Server ODBC driver installed.
Install the pyodbc library.
Installation:
Install the ‘pyodbc’ library in the jupyter notebook, please use the code below. # pip install pyodbc
If already installed, you will get a message as a snapshot below.
Connecting to Microsoft SQL Server Using Windows Authentication:
1. Now, we need to import the library, please use the below code. # import pyodbc
2. Create a parameter to build a connection, please use the code below.
#server_name = " server_name";
# db = “database_name”;
# driver = “{ODBC Driver 18 for SQL Server}”;
Note: - Please make sure which driver version installed on your machine.
3. Build the connection to the Microsoft SQL Server using window authentication
with the help of the above parameter, please use the code below.
# ‘conn=pyodbc.connect(
f'DRIVER={driver};'
f'SERVER={server_name};'
f'DATABASE={db};'
f'Trusted_Connection=yes;
)
4. Please make sure your connection builds successfully using the print function , by using the below code.
# print(conn)
5. Create the cursor object & make sure the cursor connection build is
successful. please use the below code.
# cursor = conn.cursor
# print(cursor)
6. Prepare and execute the query, please use the code below.
# cursor.execute(“Query Statement”).
Note:- you will be using your own query.
7. Now, we need to fetch the query and then process the result, please use the
code below.
# result = cursor.fetchall()
# print(result)
Result Output:
8. Finally, we need to close the open session for cursor & connection, please use
the code below.
# cursor.close()
# conn.close()
Summary: Connecting to Microsoft SQL Server using Python and pyodbc library provides flexibility, automation, and cross-platform compatibility, making it an excellent choice for integrating database operations into Python applications. While SSMS provides a user-friendly GUI and is fine-tuned for database management tasks with limited functionality, pyodbc allows for more flexible and automated solutions suitable for various development, data processing, and data transformation.
Informative