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
- Navigate to https://code.visualstudio.com/ in Ubuntu, download the Debian release and install it.
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 –
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
Use the apt-get command to update the repository.
$ sudo apt-get update
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.
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.
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.
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
Once Python is installed, verify the build version as shown below.
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.
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.
Now, install the pyodbc in Ubuntu with the following script.
pip install pyodbc
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.
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.
It also shows the recommended Python interpreter, as shown below.
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.
Click on hyperlink – Select a language and select Python language as shown below.
In the editor, paste the following Python script that connects to Azure SQL Database. Save the script as testscript.py
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
import pyodbc server = 'azuredemosqldemo.database.windows.net' database = 'azuredemodatabase' username = 'sqladmin' password = '********' driver= '{ODBC Driver 17 for SQL Server}' with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn: with conn.cursor() as cursor: cursor.execute("SELECT TOP 3 ProductID, Name, ProductNumber FROM [SalesLT].[Product]") row = cursor.fetchone() while row: print (str(row[0]) + " " + str(row[1])) row = cursor.fetchone() |
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.
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
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
import pyodbc import pandas as pd server = 'azuredemosqldemo.database.windows.net' database = 'azuredemodatabase' username = 'sqladmin' password = '********' driver= '{ODBC Driver 17 for SQL Server}' with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn: df=pd.read_sql_query('Select Top 5 ProductID, Name, ProductNumber from SalesLT.Product order by productid', conn) print(df) |
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.
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.
|
1 2 3 4 5 6 7 8 9 |
import pandas as pd data = pd.read_csv (r'sampledata.csv') df = pd.DataFrame(data, columns= ['Country','ID','EmpName']) print(df) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
import pyodbc import pandas as pd data = pd.read_csv (r'sampledata.csv') df = pd.DataFrame(data, columns= ['Country','ID','EmpName']) server = 'azuredemosqldemo.database.windows.net' database = 'azuredemodatabase' username = 'sqladmin' password = '********’ driver= '{ODBC Driver 17 for SQL Server}' with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn: cursor= conn.cursor() # Create Table # Insert DataFrame to Table for row in df.itertuples(): cursor.execute(''' INSERT INTO TestTablePy (Country, ID, EmpName) VALUES (?,?,?) ''', row.Country, row.ID, row.EmpName ) conn.commit() |
The Python script executes successfully, and you can verify the data in the table.
The data matches the CSV data, as shown below.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023