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:

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:

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

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

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

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

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:

• Connect Python to an Oracle Database using cx_Oracle
• Connect Python to MS Access Database using pyodbc
• Connect Python to MySQL using MySQLdb

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