How to Connect Python to SQL Server using pyodbc

In this tutorial, you will connect Python to a Microsoft SQL Server database using pyodbc.

import pyodbc 

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=server_name;'
                      'Database=database_name;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute('SELECT * FROM table_name')

Step-by-Step Example

Step 1: Install the pyodbc Package

If you don't have pyodbc already installed, execute the following command in your terminal:

pip install pyodbc

Step 2: Get the server name

Run the following query on your SQL Server instance to get the server name:

SELECT @@SERVERNAME

The default name is:

MSSQLSERVER

Step 3: Connect Python to SQL Server

Let's say, that you have created a database named fish_db and a table named fishes with the following data:

You can then connect to the database, query the table and output its data in your terminal using the following Python script:

import pyodbc 

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=MSSQLSERVER;'
                      'Database=fish_db;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute('SELECT * FROM fishes')

for i in cursor:
    print(i)

The output of this script should look like this:

(1, 'salmon')
(2, 'pufferfish')
(3, 'shark')

Optional Step: From SQL Server to pandas DataFrame

Sometimes it is more convenient to load the data into a pandas dataframe. To achieve this, you can use the read_sql_query function as follows:

import pyodbc
import pandas as pd

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=MSSQLSERVER;'
                      'Database=fish_db;'
                      'Trusted_Connection=yes;')

df = pd.read_sql_query('SELECT * FROM products', conn)

That's it! You just learned how to connect to SQL Server using Python and pyodbc.