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 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.

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

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 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.

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 
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:

 

How to Connect Python to SQL Server using pyodbc

 

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

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

Python - run code

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.