Purpose of the article: To guide the reader through the process of seamless integration of Excel data into an Azure SQL database using Python within the Visual Studio code environment.
Intended Audience: Users of Python and Azure SQL Database
Tools and Technology: Azure and Visual Studio code
Keywords: Azure SQL database, Python, Excel data and Visual Studio code.
Introduction:
Azure SQL Database is a fully managed, cloud-based relational database offered by Microsoft Azure. It provides a platform for storing and managing structured data, allowing organizations to easily build, scale, and maintain their applications and data. Azure SQL Database offers the same capabilities as an on-premises SQL Server while providing the benefits of cloud-based infrastructure, including high availability, scalability, security, and automated maintenance.
Creating SQL Database in Azure:
Step 1: Access the Azure Portal
- Navigate to the Azure portal using the link https://portal.azure.com/.
Step 2: Initiate Database Creation
- In the Azure portal, navigate to “Create a Resource” and search for “SQL Database.”
- Select “SQL Database” from the list and click “Create.”
Step 3: Database Configuration
- Configure the following details:
- Subscription: Choose your Azure subscription
- Resource group: Create a new one or use an existing resource group
- Database name: Pick a unique name for the database
- Server: Create a new server or an existing one
Step 4: Server Setup
- Choose an appropriate name for the server and proceed to set up a login and password, allowing the server administrator the necessary credentials to execute queries effectively.
Step 5: Network Configuration
- Activate the client’s IP address within the networking settings to facilitate seamless communication and interaction with specified network configurations. In the networking settings, enable the client’s IP address.
Step 6: Review and Create
- Review all the provided details to ensure accuracy before clicking “Create” to initiate the SQL database creation.
Step 7: Database Deployment
- After successful validation, click “Create” to begin the deployment process. This will result in the creation of an SQL database.
Step 8: Firewall Configuration
- In the overview section, click “Set Firewall networks,” add the client’s address, and save the changes.
Step 9: Query editor access
- Navigate to the query editor and log in using credentials for your SQL server.
Step 10: Table Creation
- Once logged into the server, create a new query, and define a table with a specific name and column names corresponding to those used to connect the Excel file data.
Step 11: Initial Table Status
- Upon creating the table using the SELECT statement, it will initially appear empty when queried for its data.
Connecting Visual Studio Code:
Step 12: Python Code for Integration
- In this step, you will find the Python code required to establish a connection between the data in an Excel file and an Azure SQL database.
Step 13: Local Execution
- Here, you will see the output when running the code locally.
Step 14: Database Verification
- Finally, verify the output in the Azure SQL database. Querying the table will confirm that the Excel data is successfully connected and operational in Azure SQL.
Author Bio:
Mulluru Harika
Associate Software Engineer-Data Science-Analytics
With 2.5 years of experience in Python, I've applied my skills in practical settings, particularly in using machine learning regression models. I'm also proficient in working with Azure ML, function apps, web apps, and virtual machines in Azure SQL.
