3 Ways of Integrating Python With SQL Server Every Data Professional Should Know About
Tags: python, sql, database, datascience
Published: 2022-08-11 06:32:00

## The elephant in the room
Unlike newer projects in the database space, Microsoft's SQL Server database management system hardly requires a lengthy introduction. Since the release of SQL Server 1.0 in 1989 [1], it has continued to be one of the most widely used database management systems and came in 3rd in DB-Engine's August 2022 ranking [2]. While being established has its benefits, it also poses certain challenges when it comes to effectively integrating SQL Server with modern data analytics workflows. 

This article aims to shed some light on three useful ways of connecting SQL Server with notebook-based analytics workflows in Python. While you could also use the `pyodbc` package for any of these, the examples below use [Deepnote](deepnote.com) data notebooks since they allow for SQL editing with auto-complete through SQL blocks & in-notebook schemas.

## 1. Execute Python scripts on SQL Server as stored procedures
The first and potentially most obvious way to bring SQL Server workflows closer to your analytics team's setup is to run some Python code on SQL Server. If you're using SQL Server 2017 and later [3], you're in luck! SQL Server lets you run Python scripts as stored procedures, and all that you need to do to unlock this capability is to specify the `@language` input. Let's take a look at an example:

```sql
EXECUTE Sp_execute_external_script
  @language = N'Python',
  @script = N' a = 1 
               b = 2
               c = a/b 
               d = a*b 
               print(c, d) 
```

While this example is nice to help grasp the general idea, it's not really a candidate for any mission-critical workflows. However, SQL Server can actually do way more than the above. In the example below, I'm running a query that uses Python variables as inputs (passed via JinjaSQL) for a Python script and returns the query result as Pandas DataFrames. How cool is that?

![Combining Python & SQL in a Deepnote notebook](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2ghoxjflbptcmg9kdknf.png)

## 2️. Mix-and-match database tables and Pandas DataFrames
There might come a time when you have to work with data that is scattered across your SQL Server database, Pandas DataFrames, or local files like CSVs. Instead of having to bulk insert all your CSVs into your database, what if there was a way to reference both Pandas DataFrames (and, therefore, CSVs) as if they were tables in your database?
Imagine you're a data analyst at an e-commerce company and have a baseline product table in your SQL Server database that looks something like this:

```sql
SELECT NAME,
       color,
       weight,
       listprice
FROM   [SalesLT].[product]
```
![SQL Server table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/thy1g1sk9h8h11310kfa.png)

Since your company is planning to run a marketing campaign with discounts, the head of marketing sent you a CSV called `discounts.csv` with the discounts for each product and asked you to run some analyses for products with a planned discount of more than 15%. The CSV looks something like this:

![Sample CSV](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nqfpicicfp1m4kefqt7x.png)

After reading the CSV into a Pandas DataFrame, you'd now like to use it in your SQL Server query. Using **JinjaSQL**, you can do the following to filter for products to include in your analysis based on your manager's request:

```sql
SELECT NAME,
       color,
       weight,
       listprice
FROM   [SalesLT].[Product]
WHERE  productid IN {{ 
                        pd.read_csv('discounts.csv') \
                        .query('Discount > 0.15')['ProductID'] 
                        | inclause
                    }}
```

## 3️. Leveraging T-SQL-specific functions in your analytics workflows
Thanks to its 33-year-long history, SQL Server comes with several useful functions out-of-the-box that are not supported in the same way in e.g. PostgreSQL or MySQL [4]. Some of them, like the `PIVOT()` and `UNPIVOT()` functions allow you to perform table manipulations in a manner that is very readable for analysts familiar with SQL and won't cause the machine you're running on to overheat. Let's run through an example.

Assume that we're back in our fictional e-commerce company. This time, however, we need to transform our database table by pivoting it and computing the mean product list price of each color type for the product categories 5, 6, 7, and 12. The image below shows our database table on the left and the desired target state on the right.

![Table transformation](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nhjsx5b7f3hoksnrmqdt.png)

To pivot the sample Product table using T-SQL as illustrated in the image above, you could run the following query:

```sql
SELECT Color, [5] AS Cat5, [6] AS Cat6, [7] AS Cat7, [12] AS Cat12
FROM   
(SELECT ListPrice, Color, ProductCategoryID  
FROM [SalesLT].[Product]) p  
PIVOT  
(  
AVG (ListPrice)  
FOR ProductCategoryID IN  
( [5], [6], [7], [12] )  
) AS pvt  
WHERE Color IS NOT NULL;
```
 

The neat thing about this query is that it's very readable for anyone familiar with SQL and, thus, more accessible across analytics teams in your organization. To really bring this point home, take a look at the Pandas code to produce a similar result:

```python
pd.pivot_table(product_df, 
                    values='ListPrice', index='Color',
                    columns=['ProductCategoryID'], aggfunc=np.mean, fill_value=np.nan) \
                    .reset_index()[["Color", 5, 6, 7, 12]]
```

While performing more or less the same actions, this code is harder to understand for someone not familiar with Python, Pandas, and NumPy.

## Summary
Age is, as the saying goes, just a number. With SQL and Python workflows becoming increasingly intertwined every day, knowing how to effectively integrate the two becomes more and more important. However, as demonstrated in the examples above, it is important to remember that this process is very much bidirectional  -  Python-heavy workflows can benefit from some nifty SQL tricks as much as SQL workflows can benefit from Python.

---

*Sources:*
[1] "Microsoft SQL Server." Wikipedia, 28 July 2022. Wikipedia, https://en.wikipedia.org/w/index.php?title=Microsoft_SQL_Server&oldid=1100983318.
[2] "DB-Engines Ranking." DB-Engines, https://db-engines.com/en/ranking. Accessed 3 Aug. 2022.
[3] WilliamDAssafMSFT. Quickstart: Run Python Scripts - SQL Machine Learning. https://docs.microsoft.com/en-us/sql/machine-learning/tutorials/quickstart-python-create-script. Accessed 4 Aug. 2022.
[4] SQL Feature Comparison. https://www.sql-workbench.eu/dbms_comparison.html. Accessed 3 Aug. 2022.