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

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 .

This post is syndicated from https://devblogs.microsoft.com/azure-sql/using-python-and-azure-functions-to-send-data-from-azure-sql-database/

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 solution 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"
}

Once we have the input binding defined, we can use the parameter products in our function code to access the data returned by the query. The products parameter is a list of SqlRow objects, which are similar to Python dictionaries.

The Azure SQL input bindings for Azure Functions can run any SQL query, including stored procedures. The commandText property is where we define the SQL query to run. In the example above, we’re selecting four columns from the view SalesLT.vProductAndDescription. The connectionStringSetting property is where we define the name of the app setting that contains the connection string to the Azure SQL Database. Additional examples are available which show using additional features, including parameters and executing SQL stored procedures.

Throughout the sample we have several values in the Azure Functions application settings, including the Azure SQL connection string, the API endpoint URL, and the FTP server login information. Keeping this sort of sensitive information out of code is a best practice that you’ll want to follow.

Sending data to an API endpoint

To send data to an API endpoint, we will use the requests library for it’s simplicity and the built-in json library. With the requests library, we can easily send a POST request to an API endpoint with the data we want to send. The SQL input binding sends data as a list of SqlRow objects, which are similar to Python dictionaries. We can use the json library to serialize the data into a JSON string, which is the format that most APIs expect.

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}")

In our Azure Function we check the API response status code to make sure the request was successful. If the status code is not in the 2xx range, we log an error. If the status code is in the 2xx range, we log a success message. By logging an error, we can monitor the Azure Functions logs to see if there are any issues with calling the API endpoint.

That’s it! Those ~10 lines of Python are all we need to run a query against our Azure SQL Database and send that data to the endpoint we set in the Azure Functions app settings.

Sending data to an FTP server

While we formatted the data as JSON to send to an API endpoint, we may want to send our data to an FTP server as a CSV file. By using a package like pandas, we can quickly convert the data to a comma-separated format.

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)

Python has a built-in library, ftplib, that can interact with FTP servers. After retrieving the FTP server information from the app settings, we can connect to the FTP server and upload the data. Instead of writing the data to a local file before uploading to the FTP server, we can use the BytesIO class to handle the binary data to memory.

    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")

Wrapping up

With Azure Functions we have a low-overhead and flexible way to build application components and the Azure SQL bindings make it easy to retrieve data from Azure SQL Database. In this article, we took a brief look at an approach to sending data from an Azure SQL Database to an FTP server and API endpoints with Python in Azure Functions. If you’d like to dive into this sample further, the code is available on GitHub .