I have an azure functions application(python) which connects to snowflake database(creates a dbconnection object), executes query and returns the results and it has been working fine. The only challenge is time it takes to return the query. It creates the dbconnection object everytime when it is invoked.
Question: Is it possible to pool the connection using functions app ( considering the functions app is stateless)
The short answer is YES, here is my sample code of Azure Function with HTTP trigger as below.
import logging
import azure.functions as func
def inc(n):
while True:
n += 1
yield n
count = inc(0)
def main(req: func.HttpRequest) -> func.HttpResponse:
logging.info('Python HTTP trigger function processed a request.')
return func.HttpResponse(f"Count: {count.__next__()}!")
I declared a global variable count
with the value of a generator inc(0)
outside the function main
, then I can get an increased count response as the figure below.
It's the same as my another answer for the SO thread Azure use python flask framework for function app.
So I think it's indeed to realize a connection pool in Azure Functions, such as using snowflakedb/snowflake-sqlalchemy
to create a connection pool for Snowflake database in Python outside the function main
of Azure Function, as the code below by refering to the SQLAlchemy document Connection Pooling.
import logging
import azure.functions as func
from sqlalchemy import create_engine
import sqlalchemy.pool as pool
engine = create_engine(
'snowflake://{user}:{password}@{account}/'.format(
user='<your_user_login_name>',
password='<your_password>',
account='<your_account_name>',
)
)
def getconn():
connection = engine.connect()
return connection
mypool = pool.QueuePool(getconn, max_overflow=10, pool_size=5)
def main(req: func.HttpRequest) -> func.HttpResponse:
try:
conn = mypool.connect()
# Do the code what you want, such as
# cursor = conn.cursor()
# cursor.execute("select foo")
....
finally:
conn.close()
return func.HttpResponse(f"......")
The explaination for the code above is that as below.
mypool
object will be invoked from memory following the main
function be triggered until mypool
removed from memory after idle long time.main
function after idle, a new mypool
object will be created and continous to do as above.