AI News, Python Function Library Reference

Python Function Library Reference

This section contains the Python reference documentation for three proprietary packages from Microsoft used for data science and machine learning on premises and at scale.

For SQL Server, packages are installed by default in the \Program files\Microsoft SQL Server*instance name*\PYTHON_SERVICES Ships in: To get the version of a Python module installed on your computer, start Python and execute the following commands: To view the embedded help for each class, use the help() command, specifying the base class of the object of interest.

If you have a background in these libraries, you might notice similarities in function names and operations, with Python versions adhering to the naming conventions of that language: First, read the introduction to each package to learn about common use case scenarios: Next, follow these tutorials for hands on experience:

Tutorials for SQL Server Machine Learning Services

This article provides a comprehensive list of the tutorials, demos, and sample applications that use machine learning features in SQL Server 2016 or SQL Server 2017.

Start here to learn how to run R or Python from T-SQL, how to use remote and local compute contexts, and how to optimize your R and Python code for a SQL production environment.

After running SQL Server setup, don't forget these important steps: For details, see this article for some common setup and configuration issues: Troubleshooting Machine Learning Services

Python in SQL Server 2017: enhanced in-database machine learning

We are excited to share the preview release of in-database analytics and machine learning with Python in SQL Server.

The addition of Python builds on the foundation laid for R Services in SQL Server 2016 and extends that mechanism to include Python support for in-database analytics and machine learning.

The standard open source CPython interpreter (version 3.5) and some Python packages commonly used for data science are downloaded and installed during SQL Server setup if you choose the Python option in the feature tree.

Users can easily install any additional open source Python package, including the modern deep learning packages like Cognitive Toolkit and TensorFlow to run in SQL Server.

Install new Python packages on SQL Server

This article describes how to install new Python packages on an instance of SQL Server 2017 Machine Learning Services.

The package installed in this example is CNTK, a framework for deep learning from Microsoft that supports customization, training, and sharing of different types of neural networks.

For example, if Machine Learning Services has been installed using defaults, and machine learning is enabled on the default instance, the path would be as follows: Open the Python command prompt associated with the instance.

As installation progresses, you can see status messages in the command prompt window: When installation is complete, you can immediately begin using the package as described in the next step.

For examples of deep learning using CNTK, see these tutorials: Python API for CNTK To use functions from the package in your script, insert the standard import <package_name>

SQL Server Machine Learning Services – Part 1: Python Basics

With the release of SQL Server 2017, Microsoft changed the name of R Services to Machine Learning Services (MLS) and added support for Python, a widely implemented programming language known for its straightforward syntax and code readability.

As with the R language, you can use Python to transform and analyze data within the context of a SQL Server database and then return the modified data to a calling application.

In subsequent articles, we’ll dig into Python’s capabilities in more depth, relying on this article as a foundation for the ones to follow.

To demonstrate how to run the sp_execute_external_script stored procedure, start with a simple example that retrieves sales data from the AdventureWorks2017 database, passes the data into a Python script, and then returns the data without modifying it:

After declaring the @pscript variable, you create the Python script, which starts by assigning the output from the T-SQL query to the df variable:

In fact, you do not even have to include the df variable and can instead simply assign the input variable to the output variable:

I’ve included the df variable here to help demonstrate how to begin working with SQL Server data within a Python script.

Assigning the source data to a separate variable allows you to modify the source data, while still maintaining the original dataset.

(For more information on the SQL Server types that Python supports, see the Microsoft document Python Libraries and Data Types.) After defining the Python script and T-SQL query, you can call the sp_execute_external_script stored procedure, passing in the necessary parameter values.

You do not have to include SQL Server data in the Python script, but if you’re going to be running Python within the SQL Server environment, it seems likely you’ll want to use SQL Server data.

As you can see, the Python script returns a basic result set with the data organized into columns and rows, just like the source data.

When using the sp_execute_external_script stored procedure to run Python scripts, you can specify names for the default variables assigned to the input and output datasets.

The following procedure call assigns the name RawData to the input variable, the name SumData to the output variable, and the names Territories and Sales to the output columns:

To rename the input variable, you must include the @input_data_1_name parameter when calling the sp_execute_external_script stored procedure, assigning the new name to that parameter.

To rename the output variable, you must include the @output_data_1_name parameter when calling the stored procedure, assigning the new name to that parameter.

When assigning column names, keep in mind that the database engine must be able to implicitly convert the returned data to that type.

Often this will not be a problem, depending on the level of precision that’s required, but it points to the fact that, whenever data is implicitly converted, you need to be aware of its potential impact.

If you’ve worked with the R language in SQL Server, you’re no doubt familiar with the concept of the data frame, a two-dimensional data structure similar to a SQL Server table.

The SQL Server data imported into a Python script is converted to a DataFrame object, and the data returned by a Python script is passed to the output variable as a DataFrame object.

simple way to confirm that you’re working with data frames is to modify the first example to return only the data type of the df variable:

To get the data type, you call the type function, specifying the df variable, and then call the print function to return the data type to the application.

This tells the Python engine to return the Territories values, ensuring that the unique Territories names are included in the results along with the aggregated sales data.

The next step is to add a calculated column to the df2 data frame that rates a territory’s performance based on the aggregated Sales values.

To add the column, you need only specify the df2 variable, followed by brackets that include the name of the new column in quotes.

Next, modify the column definition to include a second possible value, using a conditional expression to define a data range for when to apply that value:

You then repeat this process to add a third value, only this time the conditional expression indicates that, if the Sales value exceeds 10 million, the Performance value should be set to Excellent:

Of course, you could have achieved the same results with T-SQL alone, but even such a simple example helps demonstrate some of the ways that you can manipulate data to achieve various results.

Often when you’re working with Python, you’ll want to call the sp_execute_external_script stored procedure from within a user-defined stored procedure that can then be evoked from a calling application.

There’s no magic here, just a basic definition that creates the GetSalesTotals procedure which calls the sp_execute_external_script procedure and passes in the Python script and T-SQL query as we’ve seen in previous examples.

This time, when defining the stored procedure, include two input parameters, @low and @high, both defined with the INT data type.

The stored procedure now returns the results shown in the following figure which indicate that only one territory is performing poorly and only one above average.

Although this is a fairly trivial example, it demonstrates how you can dynamically interact with the Python script by passing in values when calling stored procedures in order to better control the results.

R and Python drive SQL Server 2017 into machine learning

Microsoft last week announced a wave of new features for its data platform, along with the SQL Server 2017 name and what Microsoft calls a “production quality” beta release.

(By renaming SQL Server 2016’s R Services to Machine Learning Services in SQL Server 2017, Microsoft has made clear where it’s aiming its SQL tools.) Including R and Python in SQL Server works well for both the existing SQL Server audience and for data scientists who are unlikely to have experience with T-SQL.

With R aimed at statistical analysis experts, Python is perhaps the easiest on-ramp to analytical programming for the rest of us, especially with a wide choice of relevant packages that add new data analysis features to a familiar language.

Installing the Python options adds not only a Pythion interpreter, but also several commonly used machine learning tools, including a subset of the Anaconda Python-based data science tools and Microsoft’s own RevoScalePy package.

Because it’s one of the more popular big data tool sets for Python, its inclusion lets data scientists quickly bring their existing skills (in concert with Hadoop) and code to SQL Server.

Running Python code inside SQL Server lets you take advantage of numerous Microsoft performance and scaling features, with direct access to its in-memory database features, speeding up OLAP queries.

How to build machine learning applications using R and Python in SQL Server 2017 - BRK3298

Learn how to use R and Python integration in Microsoft SQL Server 2016/2017 to build machine learning applications. Learn how to operationalize machine ...

Sumit Kumar - In database Machine Learning with Python in SQL Server

Description SQL Server 2017 has built in AI capabilities that bring Python based intelligence to where the data lives. Abstract In this session, we will take a ...

How to use R, Python, and machine learning with Microsoft SQL Server 2017(Serving AI with data)

In this session, learn how to use R and Python to perform statistical and machine learning tasks in SQL Server 2017. Also, learn how to operationalize your R or ...

machine learning in Microsoft SQL Server 2017 with Python

Machine learning services in SQL Server 2017 provides Python support for in-database machine learning, now. In this session we show the basics of how to run ...

Built-in machine learning in Microsoft SQL Server 2017 with Python

Machine learning services in SQL Server 2017 provides Python support for in-database machine learning, now. In this session we show the basics of how to run ...

machine learning in Microsoft SQL Server 2017 with Python

Machine learning services in SQL Server 2017 provides Python support for in-database machine learning, now. In this session we show the basics of how to run ...

Predictive Analytics with R Server and SQL Server 2016

They key to optimizing any business is to match demand with supply. Using Microsoft R Server and SQL Server 2016 we can forecast NYC taxi demand using ...

Advanced Analytics with R and SQL

R is the lingua franca of Analytics. SQL is the world's most popular database language. What magic can you make happen by combining the power of R and ...

Connecting SQL Server database using python library.

This tutorial is about how to connect to Microsoft SQL Server database using pyodbc library in python. This is perhaps the simplest way to connect to SQL Server ...

Utilizando Python y R desde SQL Server Machine Learning Services

Python, R y SQL son los lenguajes más populares en Data Science. A partir de SQL Server 2017, podremos utilizar los tres lenguajes desde SQL Server, ...