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)