How can i access Azure Sql Database with Python function App?

Question

I need to create connection with Azure Sql Database(Azure-Server:1) with Azure function which is hosted in Azure-Server:2. Basically both accounts are different but i need to fetch some data from Azure Sql Database which is hosted in (Azure-Server:1).

Is it even possible?

I tried:

import pandas as pd
import pyodbc
from sqlalchemy import create_engine

server = 'server.database.windows.net'
database = 'A'
username = 'B'
password = '###' 

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
driver = 'ODBC Driver 17 for SQL Server'
DATABASE_CONNECTION = f'mssql://{username}:{password}@{server}/{database}?driver={driver}'
engine = create_engine(DATABASE_CONNECTION,fast_executemany = True)
connection = engine.connect()

The above code is working for within same Azure server Sql DB, when i am creating connection through Azure Function but not working for other Azure linked azure database?

Answer 1

The way you follow the python Azure function to connect azure Database is correct.

conn =  "DRIVER={ODBC Driver 17 for SQL Server};SERVER=IP_ADDRESS;DATABASE=DataLake;UID=USERID;PWD=PASSWORD"
quotedConnection = quote_plus(conn)
db_con = 'mssql+pyodbc:///?odbc_connect={}'.format(quotedConnection )
engine = create_engine(db_con)


@event.listens_for(engine, 'before_cursor_execute')
def receive_before_cursor_execute(conn, cursor, statement, params, context, executemany):
    if executemany:
        cursor.fast_executemany = True

If you want to call the two different Database connections, you can make use of different calls. So that we can avoid the timeout issue.

For that either you can use One by one call or two different calls of the database connection.

Refer here to use different calls to connect databases.