Introduction:

SQL Notebook is an exciting feature of Azure Data Studio that is gaining popularity among database administrators and developers. In this article, we will explore how to use Python SQL scripts in SQL Notebook to connect to a SQL Server instance, execute queries, and perform various operations such as select, insert, update, and delete.

Setting up Python in SQL Notebook:

To use Python in SQL Notebook, we need to change the kernel from SQL to Python 3. This can be done by selecting Python 3 from the kernel list. If Python is not installed, we can choose the option for a new Python installation, and Azure Data Studio will handle the installation process for us.

Executing SQL queries using Python:

Once Python is set up in SQL Notebook, we can write Python code to establish a database connection, execute SQL queries, and retrieve results. We can use the pyodbc module to connect to SQL Server and execute T-SQL queries. Here is an example of executing a SELECT query:

import pyodbc

# Establish a database connection
connection_string = 'DRIVER=ODBC Driver 17 For SQL Server;Server=ServerName;DATABASE=DatabaseName;Trusted_Connection=yes'
conn = pyodbc.connect(connection_string)

# Create a T-SQL string
tsql = 'SELECT * FROM TableName'

# Open cursor and execute SQL script
cursor = conn.cursor()
cursor.execute(tsql)

# Fetch and print results
rows = cursor.fetchall()
for row in rows:
    print(row)

Inserting, updating, and deleting records:

We can also use Python SQL scripts in SQL Notebook to perform data manipulation operations such as inserting, updating, and deleting records. Here is an example of inserting a record into a table:

import pyodbc

# Establish a database connection
connection_string = 'DRIVER=ODBC Driver 17 For SQL Server;Server=ServerName;DATABASE=DatabaseName;Trusted_Connection=yes'
conn = pyodbc.connect(connection_string)

# Open cursor
cursor = conn.cursor()

# Specify the insert statement with parameter values
qry = 'INSERT INTO TableName (Column1, Column2) VALUES (?, ?)'
param_values = ['Value1', 'Value2']

# Execute the insert statement
cursor.execute(qry, param_values)

# Commit the transaction
conn.commit()

# Close the cursor
cursor.close()

Conclusion:

In this article, we have explored how to use Python SQL scripts in SQL Notebook of Azure Data Studio. We have seen how to connect to a SQL Server instance, execute queries, and perform various data manipulation operations using Python. SQL Notebook provides a powerful and flexible environment for working with SQL Server databases using Python.