Rajendra Gupta
Launch SQL Notebook

Use Python SQL scripts in SQL Notebooks of Azure Data Studio

December 25, 2019 by

This article explores the Python SQL scripts in SQL Notebook of Azure Data Studio.

Introduction

SQL Notebook is an exciting feature of Azure Data Studio. It is gaining popularity among database administrators and developers. You should explore the following articles before going through this article:

Let’s create a new notebook for this article. Connect to a SQL instance in Azure Data Studio.

Right-click on a SQL instance and from the context menu choose New Notebook:

Launch SQL Notebook

It launches SQL Notebook, as shown below. By default, it launches SQL kernel for executing T-SQL queries for SQL Server.

SQL kernel

In the kernel list, we see following kernels apart from SQL:

  • PySpark: We can use this for writing Python code using spark compute from a cluster
  • Spark Scala and Spark R: We can use scala code using spark compute from a cluster
  • Python 3: We can use Python code for connecting with SQL Server and execute queries
  • PowerShell: We can write PowerShell code using PowerShell kernel

You might think – Why should we worry about the Python programming language? If yes, go through this article: Why would a SQL Server DBA be interested in Python?

SQLShack article

You should also explore the Python articles and be familiar with the Python queries.

Let’s change the kernel from SQL to Python 3 in SQL Notebook. Once we change the selection to Python 3, it gives the following option for configuring Python for Notebooks:

configuring Python for Notebooks

We get two options for Python installation:

  • New Python installation: If we do not have an existing Python installation, we can choose this option, and Azure Data Studio does Python installation for us. It takes some time for downloading and installs Python. You can see an information message as well in the middle of the Python configuration page
  • Use existing Python installation: If we have an existing Python on the server, we can browse to Python directory and use existing installation

Let’s choose the default option New Python installation and click on the Install button at the bottom. It logs the installation in the task window on Azure Data Studio. We can see that the Python installer size is 144.21 MB. You should have an active internet connection for downloading the software:

New python installation

It downloads the required package and starts the installation for Notebooks. It also shows the commands for installation of Python kernel:

installation of Python kernel

It installs the Python and starts notebook Python kernel:

starts notebook Python kernel

You can see kernel: Python 3 in SQL Notebook after installation:

Manage packages

We can also see Attach to is localhost for the Python3 kernel. We use this for local Python development:

attach to option in notbook

Click on Manage Packages, and you can see a list of installed Pip packages:

list of installed packags

We can search for any specific Pip package as well. Click on Add new and search for specific Pip module:

In the following screenshot, we search for “idna” Pip package. In the result, it gives the package summary and version information:

check existing package

Let’s search for Python SQL driver (pyodbc) module and install it for the Notebook:

Python SQL driver (pyodbc) module

Now, we will use Python ODBC for connecting to SQL Server and query tables.

Execute SQL query using Python in SQL Notebook

First, click on Text and write a heading for the query:

add a text

SQL Notebook uses Markdown language formatting. You also get a preview of the text, as shown below.

Markdown language formatting

We can format the text in an h2 heading by adding the ## symbol in front of the text:

Add an H2 heading

Now we will add Python code in this notebook. We require the following information to write the query:

  • SQL instance details
  • Database name
  • T-SQL for retrieving records from a table
  • ODBC driver information

Click on Windows Start and type “odbc”. Open ODBC data sources (64-bit):

ODBC data source

It opens the following ODBC Data Source Administrator (64-bit):

System DSN

Under the System DSN tab, click on Add. It opens the available ODBC drivers. Note down the name of latest ODBC driver – ODBC Driver 17 for SQL Server:

ODBC driver for SQL Server

Cancel it, and it closes the ODBC data source window.

Step 1: Import pyodbc Python module

Step 2: Establish a database connection using windows authentication

Step 3: Create a T-SQL string

Step 4: Make a connection using the string from step 1 with Pyodbc

Step 5: Open cursor and execute SQL script

Step 6: Print results in comma-separated format

Let’s combine all pieces together and paste into SQL Notebook. To add the code, after the text, click on Add code:

Add code in SQL notebook

In the code area, paste the following Python SQL script:

Execute Python SQL Scripts

Click on the play icon for executing this Python code and view the results:

Click on play icon for executing Python

Executing a long query (multiline) using Python SQL script in Azure Data Studio

In the previous section, we used a single line query in a single quote. We cannot execute the long query in the single quotes.

We get the following error message if we use a long query in a single quote:

Executing a multiple line query

We can use the long SQL statement using the triple-quote string format. We require textwrap module for using triple-quote. SQL Server ignores all-white spaces along with tab and newlines:

Script execution

Insert data into SQL tables with Python SQL script for SQL Notebook

Suppose we have the following test table and we want to insert data using Python SQL:

Let’s copy and paste the following code in the Notebook for inserting one record:

Execute this code, and it returns the number of rows inserted into the table:

Number of rows insertion message

In this query, we followed the below steps:

Step 1: Import pyodbc Python module

Step 2: Establish a database connection using windows authentication

Step 3: Open the cursor

Step 4: Specify the insert statement with wildcard operators and specified values using param_values.

Step 5: Execute the insert statement with parameters specified

Step 6: Print a message for the number of rows inserted using cursor.rowcount and print function.

Step 6: Commit the insert statement and close the cursor

We need to commit the record that we inserted in an earlier step. We should not skip this step else, SQL Server will block the other sessions trying to retrieve records from this table. It is similar to a commit transaction statement in SQL Server.

We inserted single records in the above query. We might want to insert multiple records at a time. We can use the above query with some changes. Previously we use cursor.execute the function for data insertion. We use a cursor.executemany function for inserting multiple records.

In the below query, we insert three records in the tempdata table:

We can see the data in the tempdata table, as shown below:

Verify result

Update records using Python SQL script in SQL table

In this section, we will use an update statement in the Azure Data Studio Notebook using the Python SQL.

We can specify an update statement similar to an insert statement:

Update statement in Azure Data Studio

Let me highlight the changes in the following query compared to an insert query.

Specify the update statement with requirement parameters using a wildcard operator (?):

In the param_value, we must specify the values for the wildcard operator(?) in the correct order:

Execute the Python SQL script in Notebook and verify the changes:

Verify results

Delete records using Python SQL script in SQL table

Let’s delete a record from the SQL table in the Notebook of Azure Data Studio. You can easily understand the following code as it looks similar to an update statement. We replaced the update with a delete statement. We use the wildcard operator in the where condition:

delete statement

Verify that record with ID=1 does not exist in the tempdata table:

Verify deleted record

Conclusion

In this article, we explored Python SQL script in SQL Notebook of Azure Data Studio. We can use Python for Select, Insert, Update and Delete statements as well. You should explore Python language for SQL Server using the SQLShack articles.

Rajendra Gupta
3,899 Views