Rajendra Gupta
Use pandas to query data

Connect Azure SQL Database using Python from Ubuntu

March 4, 2022 by

Azure SQL Database or Managed instance is a Microsoft cloud offering for SQL Server in the managed cloud environment. Usually, we connect databases from SQL Server Management Studio, Azure Data Studio, or applications for query data.

Python is a popular programming language with web development, machine learning capabilities. It provides an object-oriented approach using the language constructs. You can use it to write fewer lines of code for performing various tasks using custom modules and libraries. Therefore, you might get a requirement to connect Azure SQL Database using Python.

This article explores how you connect Azure SQL DB using Python in the Ubuntu operating system.

Requirements

Before we start, let’s quickly check the pre-requisites for this article.

Visual Studio Code IDE latest version

VS Code

Install VS code on Ubuntu

Microsoft ODBC driver for SQL Server

You require a Microsoft ODBC driver for SQL Server on Linux. For the article, we use the latest ODBC 17 using the following steps.

Connect with the Sudo account and use the CURL utility to add the package key from the Microsoft package store.

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add –

Microsoft ODBC driver for SQL Server

On Ubuntu 20.04, download appropriate package using following script.

curl https://packages.microsoft.com/config/ubuntu/21.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

download appropriate package

Use the apt-get command to update the repository.

$ sudo apt-get update

apt-get command

Now, install the Microsoft ODBC driver 17 with the following command.

sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17

It downloads and installs the ODBC 17. If you have already downloaded it in your environment, you get the following message.

Microsoft ODBC driver 17

Active Azure Subscription

We require an active Azure subscription and configured Azure SQL Database with a sample database to query it. You also require a login credential for connecting Azure from Python.

Note: We will not cover creating Azure SQL DB in this article. If you do not know, refer to Azure articles on SQLShack ( https://www.sqlshack.com/category/azure/)

For this article, we require the Azure logical server name that you get from the Azure portal (as shown below) or Azure CLI.

Active Azure Subscription

Download Python installer

This article uses Python for connecting to SQL Server. Therefore, we need to install and configure Python 3 in Ubuntu.

First, verify that the Python is already installed or not using the command – Python – – version.

We do not have it; therefore, it says, command ‘python’ not found.

Python installer

The below command downloads and installs the Python 3.9 version. You can check the latest Python version from the https://www.python.org/

sudo apt install python3.9

Python installer continue

Progress message

Once Python is installed, verify the build version as shown below.

Python version

Install pyodbc using pip – Python package manage

Launch VS Code IDE in Ubuntu and navigate to Terminal -> New Terminal. It launches the bash terminal as shown below.

Install pyodbc

For installing pyodbc, we require pip utility in Linux. If you have it already on Ubuntu, ignore this.

sudo apt install pip

Enter Y to proceed with the pip utility installation.

the pip utility installation

Now, install the pyodbc in Ubuntu with the following script.

pip install pyodbc

pyodbc in Ubuntu

Install Python extension in Visual Studio Code

The VS code IDE provides an extension for the Python language to provide syntax highlighting, color coding, inline suggestions. Go to the VS code’s extensions (from the left-hand menu) and install the Python language extension below.

Python extension

After installing the Python extension in VS Code, it opens the page- Get started with python development.

Step 1: Install Python – It is already completed; therefore, it shows a tick for this step.

Step 2: Select a python interpreter: We installed Python 3.9.5 in earlier steps. Therefore, click on the option – select a python interpreter and select the correct Python version from the drop-down below.

Get started with python development.

It also shows the recommended Python interpreter, as shown below.

Select interpreter

Python script for querying Azure SQL Database

Launch the VS code editor and open a new file ->, and you get a message to select the IDE language.

Python script for querying Azure SQL Database

Click on hyperlink – Select a language and select Python language as shown below.

Select the language

In the editor, paste the following Python script that connects to Azure SQL Database. Save the script as testscript.py

Execute the testscript.py using the command – python3 testscript.py

I specified the top 3 clauses to return only three rows in the output for the demo purpose.

View PY script output

Let’s understand the script line by line.

  • Line 1 imports the pyodbc module in the current Python script session. This step will ensure that you can execute the Python ODBC scripts without any dependency issues.
  • Line 2 defines a variable for storing the Azure SQL Server name.
  • Line 3 stores Azure SQL Database name.
  • Line 4 and 5 stores the credentials for connecting with Azure DB. These credentials should have permission to do the specified task successfully.
  • Line 6 defines the SQL server driver as ODBC driver for SQL Server.
  • Line 8 uses the pyodbc.connect() method for connecting to Azure DB using values defined by all the parameters.
  • Line 10 defines the T-SQL script that we want to execute on the connection defined in the connection string. You can add the t-SQL statements, stored procedure, function, view in this Python script.
  • The remaining code executes the script and prints each row value on the console. The cursor.fetchone() returns a single record or none of the rows if no more rows are available.

Use Pandas data frame for querying SQL tables in Azure SQL Database

The Pandas data frame can import a CSV file into Python, create a data frame and query, and import data into tables. The benefit of the pandas module is that it provides data structure in Python similar to SQL. Therefore, you can efficiently work with SQL using it.

To use the pandas module, first, install using command – pip install pandas

Pandas data frame

It has the following three functions for reading data from SQL.

  • pandas.read_sql_table() : Use table as a parameter
  • pandas.read_sql_query(): Use SQL query as a parameter
  • pandas.read_sql(): It can take either table or query as a parameter.

The following Python script uses the pandas data frame and read_sql_query() module for executing the SQL script in Azure SQL Database. For the connection string, we specified the connection string variable conn in the pd.read_sql_query(). You can specify the query, and it returns the results of the data frame as shown below.

Use pandas to query data

Import data from CSV file using Python script

You might import data into SQL tables using the CSV files regularly. There are multiple ways to import data into SQL Server tables, such as the SSIS package, Import and Export data wizard. You can use the pandas data frame for reading and importing data as well.

For example, suppose I have a sample CSV file with the following data.

Import data from CSV file using

We can use the pandas data frame module read_csv() for reading CSV data and display it on the screen. In the columns section, we specified the columns name for data import.

Read data from CSV

Now, we need to import this CSV data into the SQL table [TestTablePy]. To execute the insert statement, we use the cursor.execute() function and use the wildcard operator for specifying the values for each column available in the CSV file.

The Python script executes successfully, and you can verify the data in the table.

Import data from CSV

The data matches the CSV data, as shown below.

View table data

Conclusion

This article used Python scripts to access Azure SQL database tables using pyodbc and pandas data frames. Further, the article read data from CSV and imported it into the SQL table. Python scripts are helpful for data exploration, analysis using various pandas modules. Therefore, the combination of the Python script for Azure databases is valuable, and I would recommend you to explore it.

Rajendra Gupta
1,006 Views