How to Connect Python to SQL Server using pyodbc

Need to connect Python to SQL Server using pyodbc?

If so, I’ll show you the steps to establish this type of connection using a simple example.

The Example to be Used

To start, let’s review an example, where:

  • The Server Name is: DORON\SQLEXPRESS
  • The Database Name is: TestDB
  • The Table Name is: dbo.Person
  • The Table dbo.Person contains the following data:
NameAgeCity
Jade20London
Mary119NY
Martin25London
Rob35Geneva
Maria42Paris
Jon28Toronto

Steps to Connect Python to SQL Server using pyodbc

Step 1: Install pyodbc

First, you’ll need to install the pyodbc package that will be used to connect Python with SQL Server.

You can use the PIP install method to install the pyodbc package:

 

Install the pyodbc package

Step 2: Retrieve the server name

Now retrieve your server name.

One way that you can get your server name is by opening SQL Server. As you open your SQL Server, the Connect to Server box will appear on your screen, where the server name will be displayed.

In my case, the server name is: DORON\SQLEXPRESS

 

SQL Server - Server Name

Step 3: Obtain the database name

Next, you’ll need to obtain the database name in which your desired table is stored.

You can find the database name under the Object Explorer menu (underneath the Databases section) which is located on the left-hand side of your SQL Server.

In our example, the database name is: TestDB

 

SQL Server - Database Name

Step 4: Get the table name

Now you’ll need to get the name of your desired table.

The name of your table would also be located under the Object Explorer menu (underneath the Tables section).

Here, the name of the table is: dbo.Person

 

SQL Server - Table Name

 

The following data will be displayed in SQL Server when running a simple SELECT query using the dbo.Person table.

This is also the data that we’ll retrieve once we connect our Python to SQL Server using pyodbc.

 

Select query

Step 5: Connect Python to SQL Server

And for the final part, open your Python IDLE and fill the server name, database and table information.

Here is the structure of the code that you may use in Python:

 

import pyodbc 
cnxn = pyodbc.connect('Driver={SQL Server};'
                      'Server=server_name;'
                      'Database=db_name;'
                      'Trusted_Connection=yes;')

cursor = cnxn.cursor()
cursor.execute('SELECT * FROM db_name.Table')

for row in cursor:
    print(row)

 

And this is how the code would look like in Python using our example:

 

How to Connect Python to SQL Server using pyodbc

 

Press F5 to run the code in Python.

You’ll notice that the results printed in Python match with the information that was displayed when we ran the query in SQL Server:

 

Python - run code

Conclusion and Additional Resources

We have seen how to connect Python to SQL Server. Once you established such a connection between Python and SQL Server, you can definitely use SQL in Python to manage your data.

You can also use Python to insert values into SQL Server table.

If you want to learn more about the different types of connections between Python and other database applications, you may check the following posts:

For further information about the pyodbc package, please visit the pyodbc documentation.