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: RON\SQLEXPRESS
The Database Name is: TestDB
The Table Name (with a dbo schema) is: dbo.Person
The dbo.Person table contains the following data:

Name	Age	City
Jade	20	London
Mary	119	NY
Martin	25	London
Rob	35	Geneva
Maria	42	Paris
Jon	28	Toronto

Steps to Connect Python to SQL Server using pyodbc

Step 1: Install pyodbc

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

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

Step 2: Retrieve the server name

Now retrieve your server name.

You can get your server name by opening SQL Server. You’ll then see the Connect to Server box, where the server name will be displayed.

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

Step 3: Obtain the database name

Next, 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 side of your SQL Server.

In our example, the database name is: TestDB

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

The following data will be displayed in SQL Server when you run a simple SELECT query using the dbo.Person table. This is also the data that you’ll get once you connect Python to SQL Server using pyodbc.

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 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=server_name;'
                      'Database=db_name;'
                      'Trusted_Connection=yes;')

cursor = conn.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:

Run the code in Python (adjusted to your server name, database and table information).

You’ll notice that the results that were printed in Python match with the info that was displayed in SQL Server:

Conclusion and Additional Resources

You have seen how to connect Python to SQL Server. Once you established such a connection between Python and SQL Server, you can start using 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 tutorials:

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