How to bulk insert CSV from Azure Blob to Azure Sql using Azure Function v2 (python)

Question

How to bulk insert CSV from Azure Blob to Azure Sql using Azure Function v2 (python)

jim01011 0

I have the following code below that inserts one line into an azure sql DB. How would I modify this code to bulk insert from Azure Blob (csvs) to Azure Sql using Azure Function v2 (python)?


import azure.functions as func
import logging
from azure.functions.decorators.core import DataType
import uuid

app = func.FunctionApp()

@app.function_name(name="HttpTrigger1")
@app.route(route="hello", auth_level=func.AuthLevel.ANONYMOUS)
@app.generic_output_binding(arg_name="toDoItems", type="sql", CommandText="dbo.ToDo", ConnectionStringSetting="SqlConnectionString",
    data_type=DataType.STRING)
def test_function(req: func.HttpRequest, toDoItems: func.Out[func.SqlRow]) -> func.HttpResponse:
        item_id = str(uuid.uuid4())  # Convert UUID to string
        toDoItems.set(func.SqlRow({"Id": item_id, "title": "name", "completed": False, "url": "www"}))
        return func.HttpResponse(f"Hello John")

Vinodh247 34,661 MVP Volunteer Moderator

just wondering why do you think the below simple approach would not work? is there any reason for not using pyodbc in your code?

def main(req: func.HttpRequest, toDoItems: func.Out[func.SqlRow]) -> func.HttpResponse:
    blob_service_client = 	     BlobServiceClient(account_url="https://<STORAGE_ACCOUNT_NAME>.blob.core.windows.net", credential="<STORAGE_ACCOUNT_KEY>")
    container_client = blob_service_client.get_container_client("<CONTAINER_NAME>")
    blobs = container_client.list_blobs()
    server = "<SERVER_NAME>.database.windows.net"
    database = "<DATABASE_NAME>"
    username = "<USERNAME>"
    password = "<PASSWORD>"
    driver = "{ODBC Driver XX for SQL Server}"
    conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=XXXX;DATABASE='+database+';UID='+username+';PWD='+ password)
    cursor = conn.cursor()
    sql = "INSERT INTO <TABLE_NAME> (Id, title, completed, url) VALUES (?, ?, ?, ?)"
    for blob in blobs:
        if blob.name.endswith(".csv"):
            blob_client = container_client.get_blob_client(blob)
            blob_data = blob_client.download_blob().readall().decode('utf-8')
            csv_data = blob_data.splitlines()
            for row in csv_data:
                toDoItems.set(func.SqlRow(row.split(",")))
    conn.commit()
    conn.close()
    return func.HttpResponse(f"Data inserted successfully.")

Let me know your thoughts...

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.

SwathiDhanwada-MSFT 18,996 Reputation points Moderator

2024-04-30T04:44:52.87+00:00

@jim01011 Thanks for reaching out. Here is an article for bulk inserting in Azure SQL using python. The article summarizes the ways to import the data. Kindly check it out and let me know if that is helpful for your scenario.

Your answer

Vinodh247 34,661 Reputation points MVP Volunteer Moderator

2024-04-29T10:22:48.0666667+00:00

just wondering why do you think the below simple approach would not work? is there any reason for not using pyodbc in your code?

def main(req: func.HttpRequest, toDoItems: func.Out[func.SqlRow]) -> func.HttpResponse: blob_service_client = BlobServiceClient(account_url="https://<STORAGE_ACCOUNT_NAME>.blob.core.windows.net", credential="<STORAGE_ACCOUNT_KEY>") container_client = blob_service_client.get_container_client("<CONTAINER_NAME>") blobs = container_client.list_blobs() server = "<SERVER_NAME>.database.windows.net" database = "<DATABASE_NAME>" username = "<USERNAME>" password = "<PASSWORD>" driver = "{ODBC Driver XX for SQL Server}" conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=XXXX;DATABASE='+database+';UID='+username+';PWD='+ password) cursor = conn.cursor() sql = "INSERT INTO <TABLE_NAME> (Id, title, completed, url) VALUES (?, ?, ?, ?)" for blob in blobs: if blob.name.endswith(".csv"): blob_client = container_client.get_blob_client(blob) blob_data = blob_client.download_blob().readall().decode('utf-8') csv_data = blob_data.splitlines() for row in csv_data: toDoItems.set(func.SqlRow(row.split(","))) conn.commit() conn.close() return func.HttpResponse(f"Data inserted successfully.")

Let me know your thoughts...

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.
SwathiDhanwada-MSFT 18,996 Reputation points Moderator

2024-04-30T04:44:52.87+00:00

@jim01011 Thanks for reaching out. Here is an article for bulk inserting in Azure SQL using python. The article summarizes the ways to import the data. Kindly check it out and let me know if that is helpful for your scenario.

Share via

How to bulk insert CSV from Azure Blob to Azure Sql using Azure Function v2 (python)

Your answer