Using SQL Server with Python

I am starting a brief series of posts on manipulating a SQL Server database with Python. This is a beginner level tutorial for students wanting to get started with this activity. Python provides access to a large ecosystem of data science functions above the normal SQL Query results. We will eventually work our way up to these. But first we will start with the basics: creating an ODBC connection to a SQL Server database followed by a series of posts on the standard CRUD (Create, Read, Update, Delete) functions.

These examples were done using Windows 10. To follow along you will need to have Python installed (we are using Python 3) as well as SQL Server (it should not matter too much which version so long as it is fairly recent. We are using v16). The tutorial uses a build script which we have supplied on our Google drive. This creates a sample database called bookstore. Install this and then create an ODBC data source using the Windows ODBC data sources applet. If need be, download and install the odbc driver 17 from Microsoft first.

To connect your python scripts to SQL Server you need one more component, the Python package to connect to an ODBC data source: pyodbc. You may find you already have it installed but if not you can just install pyodbc using pip in the command window.

>pip install pyodbc

With the above in place you can adjust and run the following Python script to connect to the database. Modify the server to equal <your computer name>\<SQL Server instance name>. 

import pyodbc
# assumes bookstore database has been created in sql server
def open_database():
    """ Open connection to bookstore database using ODBC"""
    cnxn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
                      'Server=HP10ALL-IN-1\SQLEXPRAS2016A1;'
                      'Database=bookstore;'
                      'Trusted_Connection=yes;')
    print('\nConnected to SQL Server with ODBC');
    return cnxn  
# main program    
cnxn = open_database()
cnxn.close()
print("Database connection closed")

I have used Windows Authentication but you can use SQL Server authentication if you prefer. You just need to supply the user name and password in lieu of Trusted_Conection=yes.

Once this is working you can proceed to the next steps which we will provide in following posts: 1) read data, 2) add records, 3) update data, 4) delete records. After that we will extend the series to include DDL - creating and dropping databases and tables, etc. We will also cover creating and using a stored procedure.