How to query Azure SQL database using Python async?-CodePudding

I'm stuck and can't figure out a workable way to connect asynchronously to an Azure SQL database using Python.

I've tried asyncio, pyodbc and asyncpg to no avail.

I think this is close...

import asyncio
import pyodbc

async def query_azure_sql_db():
  connection_string = 'Driver={ODBC Driver 17 for SQL Server};Server=tcp:<mySERVER>.database.windows.net,1433;Database=sqldbstockdata;Uid=<myUN>;Pwd=<myPW>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;'

  async with pyodbc.connect(connection_string) as conn:
    async with conn.cursor() as cursor:
      query = 'SELECT * FROM dbo.<myTABLE>'
      await cursor.execute(query)
      results = cursor.fetchall()
      return results

loop = asyncio.get_event_loop()
results = loop.run_until_complete(query_azure_sql_db())
print(results)

But results in this cryptic error: AttributeError: __aenter__

I'm open to other libraries.

Any help is appreciated.

CodePudding user response：

The problem is that pyodbc.connect is a function and does not implement async context manager, so why you are getting the AttributeError. I recommend using aioodbc instead of pyodbc as it provides the same functionality as async methods. You can also make it work with async with by implementing the __aenter__ and __aexit__ dunder methods.

import asyncio
import aioodbc

class AsyncPyodbc:
    def __init__(self, dsn, autocommit=False, ansi=False, timeout=0,
                 loop=None, executor=None, echo=False, after_created=None, **kwargs):
        self._connection: aioodbc.Connection = aioodbc.connect(dsn=dsn, autocommit=autocommit, ansi=ansi,
                                                               loop=loop, executor=executor, echo=echo,
                                                               timeout=timeout, after_created=after_created, **kwargs)

    async def __aenter__(self) -> aioodbc.Connection:
        return await self._connection

    async def __aexit__(self, *_):
        return await self._connection.close()

This will require an event loop and will be used as follows.

async def query_azure_sql_db():
    connection_string = (
        'Driver={ODBC Driver 17 for SQL Server};'
        'Server=tcp:<mySERVER>.database.windows.net,1433;'
        'Database=sqldbstockdata;Uid=<myUN>;Pwd=<myPW>;'
        'Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;'
    )
    loop = asyncio.get_event_loop()

    async with AsyncPyodbc(connection_string, loop=loop) as conn:
        ...