Python has been increasing in popularity over the years and has slowly become synonymous with both data science and DevOps. Python has an easy-to-learn syntax and an awesome toolbox that makes it extremely versatile.
Before you start leveraging Python tools such as Dash, NumPy, and pandas, you need to be able to retrieve your data from SQL Server. Python has a few different options for achieving this goal, and we are going to focus on one—SQLAlchemy.
Getting Set Up
If you do not already have Python installed, please use one of the following links to install it for your operating system:
Once you have Python installed, you will leverage the built-in package manager called pip to install SQLAlchemy. You will pass the –user flag to pip to make the installation local to your user account.
You now have SQLAlchemy installed on your system for use, but you do not have a SQL Server driver. A SQL Server driver is what Python will use to communicate with SQL Server. Use the following code to install that driver.
Now you need to build the connection string to your SQL Server. The pattern used for the connection string will look like the following code; insert your information as desired. If you want to try using Windows Authentication instead to build the connection string, that should work, too. In the example below, I am using an Azure SQL Database with AdventureWorks sample data.
You finally have all the ingredients to create a connection to a SQL Server database and execute a query. You will do this interactively in a Python console window and then create a Python script.
Our First Query via the Python REPL
Next, open your terminal and type Python.
You are now inside the Python REPL, and you can start writing Python directly in this session. You will create an engine, and then connect to the SQL Server database and execute a query that will retrieve the first ten items from the product category table.
Now that you have worked through what is needed to retrieve data, you can turn that into a Python script that creates a base on which you can create a more sophisticated solution.
Creating the Python Script
In my example, I create a new file called query.py and open that file to start editing it with the code below. I included comments with the sections that need additional context.
Now, this Python script can be executed in the terminal.
Next Steps
Now that you have the foundation in place to access SQL Server from Python, you can start exploring tools such as pandas, Dash, or Jupyter Notebooks. And there are plenty of other neat tools available in Python after you finish exploring those.
Thanks for reading,
Jamie
Jamie (@phillipsj73) is a Senior Cloud Engineer at SentryOne and working remotely in East Tennessee. He has been working with .NET since 2007 after discovering .NET development in graduate school. His Geology degree has given him an appreciation for large systems and processes which has created keen interest in Solutions Architecture, DevOps, and the Cloud. During the day he work on Windows, but at night he is all about Linux.
