SQL connection with Python

Ray_Minds — Tue, 17 Sep 2024 09:47:48 GMT

Using DB objects from Microsoft SQL Server DB in Python

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)

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.

For more information and insights, feel free to visit my blog https://www.rayminds.com/post/sql-connection-with-python
. I regularly share updates across different communities, so stay tuned for more content!

Re: SQL connection with Python

Anonymous — Wed, 18 Sep 2024 01:24:08 GMT

Hi @Ray_Minds ,

Thank you for sharing this content, it is very helpful to our community.

We are sure your post will make more people aware of how to build connections for SQL Server in Python.

You may consider accepting my reply as a solution, which will allow more people to see your post and thus help the community even more.

Thanks again for your contribution to the community.

I wish you a wonderful day.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

topic SQL connection with Python in Data Engineering

SQL connection with Python

Re: SQL connection with Python