How to fetch data from an Azure SQL Database directly within Azure ML real-time endpoint scoring script?

Question

I have an Azure Machine Learning real-time endpoint whose score.py successfully loads my registered models (via cloudpickle) and runs inference. I now need to pull data from an Azure SQL Database at runtime—ideally without installing any ODBC drivers in the scoring container.

What are the recommended approaches to connect to Azure SQL and retrieve rows as a Pandas DataFrame from within score.py? or any way to communicate the data between sql database and the scoring script

Could someone share step-by-step instructions or sample code snippets? Any tips on avoiding ODBC driver dependencies would be especially helpful.

Answer 1

To speak with SQL Server you need to use the TDS protocol. To use that protocol you need to use a driver of some sort. I believe the most common solution in the world of Python is to use an ODBC driver for SQL Server and then a Python module on top of that.

But you could also have a driver written in Python that speaks TDS directly to SQL Server. A quick googling led me to https://github.com/denisenkom/pytds, which seems to promise exactly this. I will need to add disclaimer that I don't know how good or bad pytds is, but you can give it a try.