async views w/ pyodbc

[falkben]

Came across this repo through a google search related to pyodbc. This is a neat example, thanks for putting it online.

Just a heads up: I believe you have defined async path operators but are not using "await" so your "slow" db calls will stall the event loop whenever it does anything with the database. Since pyodbc doesn't support awaiting, you should make the view's all normal "sync" functions (without "async def"). This will spawn a new thread outside the event loop which the path operator can "await" and will free up resources to do other things. Alternatively, if you can use a database/driver that supports async/await (postges I know has a driver) you could keep your path operators as is and "await" the db queries.

You can see more about this here: https://fastapi.tiangolo.com/tutorial/sql-databases/#about-def-vs-async-def and more generally here: https://fastapi.tiangolo.com/async/

[esundberg]

Yes this is true, pyodbc will cause blocking. You can't just put await if the library is not asyncio compatible. I will check to see if there is an asyncio compatible library for pyodbc. For mysql the library is call aiomysql.

[esundberg]

I think this is this library will work instead of pyodbc: https://github.com/aio-libs/aioodbc

As a side note here is an example of using aiomysql library

I also learned that you connect to a database on startup of FastAPI like this.

@app.on_event("startup")
async def startup():
    print(f'connecting to database')
    await db.connect_pool()
    print(f'connected to database')
    print(f'Checking Connection to the database')
    result = await db.query_one("SELECT 1+1 as value")
    assert result["value"] == 2, "Unable to preform simple sql query test"

Example Stored procedure call using aiomysql on another project.

@app.post("/test")
async def test(data: test_post_body):
    result = await db.callproc_one('my_stored_proc', [data.value1, data.value2])
    print(f'{result["col1"]}:{result["col2"]}')
    if result:
        return result
    else:
        return False

From my db class

async def connect_pool(self):
        self.pool = await aiomysql.create_pool(
            host=cfg.mysql["host"], 
            port=cfg.mysql["port"],
            user=cfg.mysql["user"],
            password=cfg.mysql["password"],
            db=cfg.mysql["database"], 
            loop=asyncio.get_event_loop(),
            autocommit=cfg.mysql["autocommit"])

async def callproc_one(self, stored_proc, params=[]):
        # Return a single row not in a list
        try: 
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    await cursor.callproc(stored_proc, params)
                    columns = [column[0] for column in cursor.description]
                    row = await cursor.fetchone()
                    if row:
                        ret = dict(zip(columns,row))
                    else:
                        # No results
                        ret = {}
                    return ret
        except Exception as e:
            print(e)
            return False