Importing a CSV file into an Azure SQL Database is a common task for data engineers and data scientists. In this tutorial, we will learn how to import a CSV file into an Azure SQL Database using Python.
Prerequisites
Azure SQL Database instance
Python 3.x installed
Azure SQL Python library installed (
pip install azure-sql-extensions)
Steps to import CSV into Azure SQL Database using Python
- Connect to the Azure SQL Database using Python:
from azure.sql import SqlDatabase
server = 'your_server_name.database.windows.net'
database = 'your_database_name'
username = 'your_username'
password = 'your_password'
database = SqlDatabase(server, database, username, password)
2. Create a table in Azure SQL Database:
query = '''
CREATE TABLE your_table_name (
column1 datatype1 NULL/NOT NULL,
column2 datatype2 NULL/NOT NULL,
column3 datatype3 NULL/NOT NULL,
...
);
'''
database.execute(query)
3. Read the CSV file using pandas library:
import pandas as pd
data = pd.read_csv('your_csv_file.csv')
4. Insert data from the CSV file into the Azure SQL Database table:
for index, row in data.iterrows():
query = f'''
INSERT INTO your_table_name (column1, column2, column3, ...)
VALUES ({row['column1']}, {row['column2']}, {row['column3']}, ...);
'''
database.execute(query)
Conclusion
In this tutorial, we learned how to import a CSV file into an Azure SQL Database using Python. This method can be used to import large datasets into Azure SQL Database efficiently.