Using Python and Azure Functions to send data from Azure SQL Database

Drew Skwiers-Koballa

When building applications on Azure SQL, one of the most flexible ways to send data from your database to other systems is to use Azure Functions. Azure Functions are serverless functions that can be triggered by a variety of events, including HTTP requests, timers, and Azure SQL Database changes. In this article, we will discuss how to send data from an Azure SQL Database to an FTP server and API endpoints using Azure Functions. The complete sample code for this article is available on GitHub.

Get data from Azure SQL Database in Azure Functions

With Azure SQL bindings for Azure Functions we can easily retrieve data from an Azure SQL Database in an Azure Function, leaving the boilerplate code of connecting to the database and executing queries to the Azure Functions runtime. When our function needs to operate on a schedule, such as every morning, we can use the timer trigger to start the Azure Function. Python Azure Functions are composed of a function.json file and an __init__.py file. The function.json file is where we define the function trigger and input/output bindings and the Python code is based in the __init__.py file. Querying Azure SQL Database with an Azure Function is as simple as adding an input binding to the function.json file:

{
    "name": "products",
    "type": "sql",
    "direction": "in",
    "commandText": "SELECT [ProductID],[Name],[ProductModel],[Description] FROM [SalesLT].[vProductAndDescription]",
    "commandType": "Text",
    "connectionStringSetting": "SqlConnectionString"
}

def main(everyDayAt5AM: func.TimerRequest, products: func.SqlRowList) -> None:
    logging.info('Python timer trigger function started')
    # convert the SQL data to JSON in memory
    rows = list(map(lambda r: json.loads(r.to_json()), products))

    # get the API endpoint from app settings
    api_url = os.environ['API_URL']

    # send the data to the API
    response = requests.post(api_url, json=rows)
    # check for 2xx status code
    if response.status_code // 100 != 2:
        logging.error(f"API response: {response.status_code} {response.reason}")
    else:
        logging.info(f"API response: {response.status_code} {response.reason}")

def main(everyDayAt5AM: func.TimerRequest, products: func.SqlRowList) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')
    filename = "products.txt"
    filesize = 0

    # convert the SQL data to comma separated text
    product_list = pandas.DataFrame(products)
    product_csv = product_list.to_csv(index=False)

    datatosend = io.BytesIO(product_csv.encode('utf-8'))

    # get FTP connection details from app settings
    FTP_HOST = os.environ['FTP_HOST']
    FTP_USER = os.environ['FTP_USER']
    FTP_PASS = os.environ['FTP_PASS']

    # connect to the FTP server
    try:
        with ftplib.FTP(FTP_HOST, FTP_USER, FTP_PASS, encoding="utf-8") as ftp:
            logging.info(ftp.getwelcome())
            # use FTP's STOR command to upload the data
            ftp.storbinary(f"STOR {filename}", datatosend)
            filesize = ftp.size(filename)
            ftp.quit()
    except Exception as e:
        logging.error(e)

    logging.info(f"File {filename} uploaded to FTP server. Size: {filesize} bytes")

Drew Skwiers-Koballa Senior Program Manager

Follow