Python Integration with SQL Server

This quick start introduces the basic general concepts behind how to build your first workflow by using python with SQL. In this quick start, we will build an app with a connection string, and in the second approach, we will integrate Python with SQL Server. We’ll install the required packages and SQL Server features that are required to run the python script in SQL Server.

Initially, only R service was available as a feature till SQL 2016. Microsoft included python support in SQL 2017 and added Python as an in-database component, which can be used for deep data analysis and in the implementation of machine learning models in the database itself. Then Microsoft renamed R Services to Machine Learning Services, and R and Python are two options under this feature.

SQL Server Machine Learning Services:- Machine Learning Services is a feature in SQL Server that gives the ability to run Python and R scripts with relational data. You can use open-source packages and frameworks, and the Microsoft Python and R packages for predictive analytics and machine learning. The scripts are executed in-database without moving data outside SQL Server or over the network. Once the installation of the features is done, most common packages are installed with Machine Learning service, like revoscalepy, microsoftml etc.

Now I will start with a simple program that will extract data from the database using the connection string. This program needs a .json file that will contain weekdays.

JSON data should appear like below:
{

“Monday”: 1,

“Tuesday”: 2,

“Wednesday”: 3,

“Thursday”: 4,

“Friday”: 5,

“Saturday”: 6,

“Sunday”: 7

}

Also, I need a data table that will have one varchar type column

CREATE TABLE DaysOfWeek(

[DayOfWeek] [varchar](10) NOT NULL

) ON [PRIMARY]

insert into DaysOfWeek(DayOfWeek)values(‘Monday’),(‘Tuesday’),(‘Sunday’),(‘Monday’),(‘Tuesday’),(‘Wednesday’),(‘Thursday’),(‘Friday’),(‘Saturday’),(‘Friday’),(‘Monday’),(‘Sunday’)

Now, our purpose is to map the days. So I will write a code that will read JSON data and also extract the data from the table then will map JSON and data table using the Pandas Python library.