How to Connect Python to SQL Server Using pyodbc

How to Connect Python to SQL Server Using Pyodbc

People learning Python usually question can python connect to any database such as an SQL server? The answer is to this is yes it can! Python can connect to many different data sources and is an extremely adaptable language. It is the best language for machine learning, data analytics, and web development. There are plenty of ways to connect to an SQL server using Python, the most common method of all is by using the pyodbc package.

Connecting Python to SQL server is an incredibly easy process. We can instantly utilize python’s dynamic nature to control and create queries in SQL. When these two amazing tools are put together, they empower us to achieve new heights of performance, automation, and efficiency.

Pyodbc

pyodbc is defined as a bridge between the two technologies i.e python and SQL server. This library provides easy access to ODBC databases.

ODBC, known as Open Database Connectivity, was developed by the SQL Access group in the early ’90s. It is a standardized application programming interface (API) for accessing databases.

Connecting Python and SQL Server

A large number of libraries are available on the internet today to place a connection between Python and SQL Server. Explained below is the process of how to connect Python and SQL Server using the pyodbc library.

How to Connect Python to SQL Server Using pyodbc infographic

Steps to Connect Python to SQL Server with the help of pyodbc: 

Step 1: Install pyodbc

You should start by installing the pyodbc package, which will be used to connect Python to the SQL Server.

Step 2: Retrieve the server name

In the next step, you need to retrieve your server name. With running the following query you can find your current server name:

SELECT @@SERVERNAME

Step 3: Get the database name

Next, you need to get the database name in which your desired table is saved. You can locate the database name below the Object Explorer menu (under the Databases section), which is positioned on the left side of your SQL Server.

Step 4: Get the table name

Now, you need to get the name of your table. You will find the name of your table below the Object Explorer menu (under the Tables section).

When running a simple SELECT query, the data gets displayed in the SQL Server. Here will be the data that you’ll see once you will connect Python to SQL Server.

Step 5: Connect Python to SQL Server

Now, for the concluding part, you need to open your Python IDLE and enter the following data: server name, database, and table information. The structure of the code would be:

import pyodbc

conn = pyodbc.connect('Driver={SQL Server};'

                      'Server=server_name;'

                      'Database=database_name;'

                      'Trusted_Connection=yes;')

cursor = conn.cursor()

cursor.execute('SELECT * FROM database_name.table')

for row in cursor:

    print(row)

Run the code in Python, as per your server name, database, and table information. Now you will see that the results that were printed in Python match with the info displayed in the SQL Server.

Conclusion

The above article explains how to connect Python to SQL Server. After establishing a connection between Python and SQL Server, you are ready to use SQL in Python for managing your data. It can help you accomplish much more in your coding projects. Python can also be used to insert values into the SQL Server table.