Ever tried accessing data on Azure SQL Database from a Python IDE? This article will walk you through the steps required to reach the data in Azure SQL Database tables.
Before beginning, let us set up the pre-requisites needed for the process. Below are the software and their versions used at the time of this article.
- IDE: Visual Studio Code (Version: 1.24.1 or above)
- Python Version: 3.6
- Install Python extension within VSCode and setup Environment variables as required
- SQL Server Management Studio v17.7
- Active Azure Subscription
- Databases and Tables built with data
- Database Name and Login Credentials
Let us first look at the data in Azure SQL Database using SQL Server Management Studio. The data we have used for this demo comes from the AdventureWorks dataset which is a property of Microsoft.
Product Category Table:
Now let us focus on Visual Studio code to figure out connecting to Azure SQL Database.
Since we are trying to connect to a SQL Database which is ODBC by nature. We need to import a open source library built for Python. Use the below script to import the library, which helps us to connect to any ODBC databases:
Now we have the library imported, we need to get the details for connectivity like ServerName, Db name, Login Credentials and a suitable driver for connecting to the Azure SQL Database.
To identify your Server Name and Azure SQL Database Name, you could either navigate from the Azure Portal or use SQL Server Management Studio where the Azure SQL DB is already connected to.
On Azure, follow these steps to reach the server name:
Resource Group -> All Resources -> SQL Database (Resource) -> Overview -> Server name
Azure SQL Database Resource name will be your database name
Once we are done identifying the Server and Database names, lets move on to the IDE.
Note: We are using the ODBC Driver 13 for accessing Azure SQL Database.
Now that we are all setup with the connection and ready to read the tables, let us execute a simple SQL query and verify if we are able to fetch the data.
The output terminal provides the Product Name and the Category the product belongs to:
Optional – Exporting the retrieved data to CSV file:
For further ease of readability, the rows returned from Azure SQL Data Warehouse can be written to a csv file or a text file and used for import/analysis in any other platform.
Everybody knows that python is a very strong analytical tool. Incase you have been focused to import data from Azure SQL Database or in this case, any ODBC Databases, into Data Frames and work around with it, this brief article would help you get to the data easily.
Thanks for staying tuned to Visual BI Solutions. Look forward to this space for many more articles on Microsoft Azure.
Data Source Used: AdventureWorks Database from Microsoft Corporation