sqlalchemy Incompatible with Python Azure Functions

Question

Whenever I try to run a Python Azure function (timer and service bus triggers) that includes sqlalchemy, I get this error "binding mytimer has invalid non-type annotation <sqlalchemy.sql.functions._FunctionGenerator object at 0x7fc1bfd8b610>". I am using VS code to deploy the app to a docker container locally for debugging.

This happens when I add sqlalchemy to the requirements.txt and add from sqlalchemy import * in the init.py file.

fail: Host.Results[0]

Microsoft.Azure.WebJobs.Host.FunctionInvocationException: Exception while executing function: Functions.AzureFunName

 ---> Microsoft.Azure.WebJobs.Script.Workers.Rpc.RpcException: Result: Failure

Exception: FunctionLoadError: cannot load the AzureFunName function: binding mytimer has invalid non-type annotation <sqlalchemy.sql.functions._FunctionGenerator object at 0x7fc1bfd8b610>

Stack:   File "/azure-functions-host/workers/python/3.8/LINUX/X64/azure_functions_worker/dispatcher.py", line 311, in _handle__function_load_request

    self._functions.add_function(

  File "/azure-functions-host/workers/python/3.8/LINUX/X64/azure_functions_worker/functions.py", line 174, in add_function

    raise FunctionLoadError(


   at Microsoft.Azure.WebJobs.Script.Description.WorkerFunctionInvoker.InvokeCore(Object[] parameters, FunctionInvocationContext context) in /src/azure-functions-host/src/WebJobs.Script/Description/Workers/WorkerFunctionInvoker.cs:line 96

   at Microsoft.Azure.WebJobs.Script.Description.FunctionInvokerBase.Invoke(Object[] parameters) in /src/azure-functions-host/src/WebJobs.Script/Description/FunctionInvokerBase.cs:line 82

   at Microsoft.Azure.WebJobs.Host.Executors.VoidTaskMethodInvoker`2.InvokeAsync(TReflected instance, Object[] arguments) in C:\projects\azure-webjobs-sdk-rqm4t\src\Microsoft.Azure.WebJobs.Host\Executors\VoidTaskMethodInvoker.cs:line 20

   at Microsoft.Azure.WebJobs.Host.Executors.FunctionInvoker`2.InvokeAsync(Object instance, Object[] arguments) in C:\projects\azure-webjobs-sdk-rqm4t\src\Microsoft.Azure.WebJobs.Host\Executors\FunctionInvoker.cs:line 52

   at Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.InvokeWithTimeoutAsync(IFunctionInvoker invoker, ParameterHelper parameterHelper, CancellationTokenSource timeoutTokenSource, CancellationTokenSource functionCancellationTokenSource, Boolean throwOnTimeout, TimeSpan timerInterval, IFunctionInstance instance) in C:\projects\azure-webjobs-sdk-rqm4t\src\Microsoft.Azure.WebJobs.Host\Executors\FunctionExecutor.cs:line 572

   at Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithWatchersAsync(IFunctionInstanceEx instance, ParameterHelper parameterHelper, ILogger logger, CancellationTokenSource functionCancellationTokenSource) in C:\projects\azure-webjobs-sdk-rqm4t\src\Microsoft.Azure.WebJobs.Host\Executors\FunctionExecutor.cs:line 518

   at Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithLoggingAsync(IFunctionInstanceEx instance, FunctionStartedMessage message, FunctionInstanceLogEntry instanceLogEntry, ParameterHelper parameterHelper, ILogger logger, CancellationToken cancellationToken) in C:\projects\azure-webjobs-sdk-rqm4t\src\Microsoft.Azure.WebJobs.Host\Executors\FunctionExecutor.cs:line 296

   --- End of inner exception stack trace ---

   at Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithLoggingAsync(IFunctionInstanceEx instance, FunctionStartedMessage message, FunctionInstanceLogEntry instanceLogEntry, ParameterHelper parameterHelper, ILogger logger, CancellationToken cancellationToken) in C:\projects\azure-webjobs-sdk-rqm4t\src\Microsoft.Azure.WebJobs.Host\Executors\FunctionExecutor.cs:line 343

   at Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.TryExecuteAsync(IFunctionInstance functionInstance, CancellationToken cancellationToken) in C:\projects\azure-webjobs-sdk-rqm4t\src\Microsoft.Azure.WebJobs.Host\Executors\FunctionExecutor.cs:line 105

Answer 1

Did you use the following import:

from sqlalchemy import func

this collides with azure functions import:

import azure.functions as func

which is needed for:

def main(req: func.HttpRequest) -> func.HttpResponse:

We have renamed the import for azure.functions as az_func

Hope this helps!

Answer 2

Hi @CP ,

Welcome to Microsoft Q&A!

I followed the steps available here to create a sample environment but was unable to reproduce this issue. Here are the high level steps that I performed

• Included sqlalchemy in requirements.txt file.
• Build the container and ran it (providing the required AzureWebJobsStorage as an environment variable).
• The __init__.py for TimerTrigger function is as below, ref: SQLAlchemy tutorial. import datetime import logging import sqlalchemy from sqlalchemy import * import azure.functions as func def main(mytimer: func.TimerRequest) -> None: timestamp = datetime.datetime.now().isoformat()

   if mytimer.past_due:
       logging.info('The timer is past due!')
  
   version = sqlalchemy.__version__
   engine = create_engine('sqlite:///:memory:', echo=True)
  
   logging.info('Python TimerTrigger function ran at %s', timestamp)
   logging.info('sqlalchemy version = %s', version)
   logging.info('sqlalchemy engine name = %s', engine.name)
  

-It did not throw the error as mentioned above and I got the expected output as well:

info: Function.TimerTrigger.User[0]
      Python TimerTrigger function ran at 2021-09-29T13:41:00.010115
info: Function.TimerTrigger.User[0]
      sqlalchemy version = 1.4.25
info: Function.TimerTrigger.User[0]
      sqlalchemy engine name = sqlite

I was able to run the same code locally (outside container) as well without any issues. Can you please check if you are able to run this function app locally, outside of container? You may also use Visual Studio code to debug the error locally. Based on the error reported, it looks like the function.json file's binding could be incorrect for this function, causing this issue.

Please let me know if you have any questions.

Please 'Accept as answer' and ‘Upvote’ if it helped so that it can help others in the community looking for help on similar topics.