Connect to SQL Server using Python

~ jlmcdermott

In this post I’m going to demonstrate how you can connect to SQL Server in python, read data from a table and load it into a data frame.

Prerequisites

In order to connect to SQL Server you’ll need to ensure you have the correct ODBC Driver installed.

You’ll also want to pip install the Python packages pyodbc and pandas in order to follow the code in this article.

SQL Server Authentication

When defining your connection you can either connect using windows authentication or SQL authentication as shown below.

Windows Authentication

import pyodbc

# Windows Authentication
conn = pyodbc.connect(
    'Driver={SQL Server};'
    'Server=localhost\\instance;'
    'Database=database;'
    'Trusted_Connection=yes;')

SQL Authentication

import pyodbc

# SQL Authentication
conn = pyodbc.connect(
    'Driver={SQL Server};'
    'Server=localhost\\instance;'
    'Database=database;'
    'UID=user;'
    'PWD=password;')

Return Results from SQL

Now we can authenticate we want to return data by executing a SQL query. To do that we want to open the connection, execute the statement, return our results, and close the connection.

# open connection
cursor = conn.cursor()

# execute SQL
cursor.execute('SELECT * FROM dbo.StarWars')

# put the results into an object
result = cursor.fetchall()

# close connection
cursor.close()

# print results
print(result)

Results to a Data Frame

As you can see from my return the data isn’t in a shape that I can easily go on to work with in Python. So to resolve this I’m going to load it into a data frame but I need to make some changes. Firstly I need to get the column names from the return using the description function.

# get the columns for the result
cols = [column[0] for column in cursor.description]

Then I also want to change the outputted rows from being tuples () to lists [].

# iterate over each row and append to list
data = []
for row in result:
    # convert a tuple to a list
    rowlist = list(row)
    data.append(rowlist)

This enables me to use pandas in order to load the returned data into a data frame, like so.

import pandas as pd

# create a dataframe
df = pd.DataFrame(data, columns = cols)

Putting Everything Together

Now I have all my component parts I’m able to call SQL and put my data into a Data Frame like so.

import pyodbc
import pandas as pd

conn = pyodbc.connect(
    'Driver={SQL Server};'
    'Server=localhost\\instance;'
    'Database=database;'
    'Trusted_Connection=yes;')

# open connection
cursor = conn.cursor()
# execute SQL query
cursor.execute('SELECT * FROM dbo.StarWars')
# put the results into an object
result = cursor.fetchall()
# get the columns for the result
cols = [column[0] for column in cursor.description]

# iterate over each row and append to list
data = []
for row in result:
    # convert a tuple to a list
    rowlist = list(row)
    data.append(rowlist)

# close connection
cursor.close()

# create a dataframe
df = pd.DataFrame(data, columns = cols)

# print the dataframe
print(df)

Published by jlmcdermott