{"id":1732,"date":"2023-02-26T17:31:56","date_gmt":"2023-02-27T01:31:56","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=1732"},"modified":"2023-05-04T14:22:50","modified_gmt":"2023-05-04T21:22:50","slug":"using-python-and-azure-functions-to-send-data-from-azure-sql-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/using-python-and-azure-functions-to-send-data-from-azure-sql-database\/","title":{"rendered":"Using Python and Azure Functions to send data from Azure SQL Database"},"content":{"rendered":"

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<\/a>. 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<\/a>.<\/p>\n

<\/p>\n

\"sending<\/a><\/h2>\n

Get data from Azure SQL Database in Azure Functions<\/h2>\n

With Azure SQL bindings for Azure Functions<\/a> 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<\/a> to start the Azure Function. Python Azure Functions are composed of a function.json<\/em> file and an __init__.py<\/em> file. The function.json<\/em> file is where we define the function trigger and input\/output bindings and the Python code is based in the __init__.py<\/em> file. Querying Azure SQL Database with an Azure Function is as simple as adding an input binding to the function.json<\/em>\u00a0file:<\/p>\n

{\r\n    \"name\": \"products\",\r\n    \"type\": \"sql\",\r\n    \"direction\": \"in\",\r\n    \"commandText\": \"SELECT [ProductID],[Name],[ProductModel],[Description] FROM [SalesLT].[vProductAndDescription]\",\r\n    \"commandType\": \"Text\",\r\n    \"connectionStringSetting\": \"SqlConnectionString\"\r\n}<\/code><\/pre>\n
\n
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.<\/div>\n
<\/div>\n
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<\/em>. The connectionStringSetting<\/em> property is where we define the name of the app setting that contains the connection string to the Azure SQL Database. Additional examples<\/a> are available which show using additional features, including parameters and executing SQL stored procedures.<\/div>\n
<\/div>\n
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.<\/div>\n
Sending data to an API endpoint<\/h2>\n
\n
To send data to an API endpoint, we will use the requests<\/em> library for it’s simplicity and the built-in json<\/em> library. With the requests<\/em>\u00a0library, we can easily send a POST<\/em>\u00a0request to an API endpoint with the data we want to send. The SQL input binding sends data as a list of SqlRow<\/em> objects, which are similar to Python dictionaries. We can use the json<\/em>\u00a0library to serialize the data into a JSON string, which is the format that most APIs expect.<\/div>\n
\n
def main(everyDayAt5AM: func.TimerRequest, products: func.SqlRowList) -> None:\r\n    logging.info('Python timer trigger function started')\r\n    # convert the SQL data to JSON in memory\r\n    rows = list(map(lambda r: json.loads(r.to_json()), products))\r\n\r\n    # get the API endpoint from app settings\r\n    api_url = os.environ['API_URL']\r\n\r\n    # send the data to the API\r\n    response = requests.post(api_url, json=rows)\r\n    # check for 2xx status code\r\n    if response.status_code \/\/ 100 != 2:\r\n        logging.error(f\"API response: {response.status_code} {response.reason}\")\r\n    else:\r\n        logging.info(f\"API response: {response.status_code} {response.reason}\")<\/code><\/pre>\n<\/div>\n<\/div>\n
\n
\n
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.<\/div>\n
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.<\/div>\n
Sending data to an FTP server<\/h2>\n
\n
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<\/em>, we can quickly convert the data to a comma-separated format.<\/div>\n
\n
def main(everyDayAt5AM: func.TimerRequest, products: func.SqlRowList) -> func.HttpResponse:\r\n    logging.info('Python HTTP trigger function processed a request.')\r\n    filename = \"products.txt\"\r\n    filesize = 0\r\n\r\n    # convert the SQL data to comma separated text\r\n    product_list = pandas.DataFrame(products)\r\n    product_csv = product_list.to_csv(index=False)<\/code><\/pre>\n<\/div>\n<\/div>\n
\n
\n
Python has a built-in library, ftplib<\/em>, 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<\/em>\u00a0class to handle the binary data to memory.<\/div>\n
\n
    datatosend = io.BytesIO(product_csv.encode('utf-8'))\r\n\r\n    # get FTP connection details from app settings\r\n    FTP_HOST = os.environ['FTP_HOST']\r\n    FTP_USER = os.environ['FTP_USER']\r\n    FTP_PASS = os.environ['FTP_PASS']\r\n\r\n    # connect to the FTP server\r\n    try:\r\n        with ftplib.FTP(FTP_HOST, FTP_USER, FTP_PASS, encoding=\"utf-8\") as ftp:\r\n            logging.info(ftp.getwelcome())\r\n            # use FTP's STOR command to upload the data\r\n            ftp.storbinary(f\"STOR {filename}\", datatosend)\r\n            filesize = ftp.size(filename)\r\n            ftp.quit()\r\n    except Exception as e:\r\n        logging.error(e)\r\n\r\n    logging.info(f\"File {filename} uploaded to FTP server. Size: {filesize} bytes\")<\/code><\/pre>\n<\/div>\n
Wrapping up<\/h2>\n
\n
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.\u00a0If you’d like to dive into this sample further, the code is available on GitHub<\/a>.<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":46830,"featured_media":1964,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1,411],"tags":[435,407],"class_list":["post-1732","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql","category-python","tag-functions","tag-python"],"acf":[],"blog_post_summary":"
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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/1732","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/46830"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=1732"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/1732\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/1964"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=1732"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=1732"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=1732"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}