Can't perform query from databases library's connection pool with async Flask

Question

I want to use Flask[async] to query data asynchronously using the databases library. I want to query data from PostgreSQL using a connection from a connection pool. In order to create a single connection pool for my Flask application, like what's described here, I put the database connection code in its own module.

db.py

from databases import Database
from datetime import datetime

class Postgres:
    @classmethod
    async def create_pool(cls):
        self = Postgres()
        connection_url = 'postgresql+asyncpg://USER:PASSWORD@HOST:POST/DATABASE'
        self.database = Database(connection_url, min_size=1, max_size=5)

        if not self.database.is_connected:
            await self.database.connect()        
            print(f'Connected to database at {datetime.now()}')

        return self

main.py

from flask import Flask, render_template
from db import Postgres
import asyncio
from flask_wtf import FlaskForm
from wtforms.fields import StringField, SubmitField
from wtforms.validators import DataRequired, Length

async def create_app():
    app = Flask(__name__)
    app.db = await Postgres.create_pool()
    return app

app = asyncio.run(create_app())

class ItemForm(FlaskForm):
    item_id = StringField("Item ID:", validators=[DataRequired(), Length(7, 9)])
    submit = SubmitField('Search')
   
async def get_item(item_id):
    query = """select item_name from items where item_id = :item_id;"""
    # app.db.database.is_connected --> True
    async with app.db.database.transaction(): # AttributeError: 'NoneType' object has no attribute 'send'
        item = await app.db.database.fetch_all(query, values={'item_id': item_id})
    return item

@app.route('/', methods=['GET', 'POST'])
async def home():
    form = ItemForm()

    if form.validate_on_submit():
        item = await get_item(form.item_id.data)
        return render_template('home.html', form=form, item=item)
    
    return render_template('home.html', form=form)

if __name__ == "__main__":
    asyncio.run(app.run('0.0.0.0', port=8080, debug=True))

This prints Connected to database at 2023-07-12 14:42:46.601021 and the connected still exists before the database transaction, but I get the error:

AttributeError: 'NoneType' object has no attribute 'send'

Why do I get this error, and how can I make this work?

Edit:

I've removed the line async with app.db.database.transaction(): since transactions are for writing to the database. That leaves me with the error asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress.

I then removed everything so I just have

# top of script
database = Databases(URL)

# inside get_item()
await database.connect()
await database.fetch_all(...)

The query runs, but there is no connection pool now...

score 0 · Accepted Answer · answered Jul 13 '23 at 13:12

I ended up migrating from Flask to Quart. From the Flask docs,

Flask’s async support is less performant than async-first frameworks due to the way it is implemented. If you have a mainly async codebase it would make sense to consider Quart. Quart is a reimplementation of Flask based on the ASGI standard instead of WSGI. This allows it to handle many concurrent requests, long running requests, and websockets without requiring multiple worker processes or threads.

Quart provides support for running code before application start-up, and after application-shutdown. Creating and closing a connection pool can be done with two decorators.

import asyncpg
from quart import Quart

app = Quart(__name__)

@app.before_serving
async def create_db_pool():
    app.db_pool = await asyncpg.create_pool(connection_url)

@app.after_serving
async def create_db_pool():
    await app.db_pool.close()

In my coroutines, I can run queries with,

async with app.db_pool.acquire() as connection:
        rows = await connection.fetch(sql_string, param1)

Side Note: Bootstrap-Flask and WTForms work with Quart! These are two big reasons why I'm using it over FastAPI/Starlette.

See also [Quart-DB](https://quart-db.readthedocs.io) for a managed pool for Quart. — pgjones, Jul 15 '23 at 16:23
Thanks @pgjones, I really love the documentation you have for Quart :) — scrollout, Jul 17 '23 at 17:44
@pgjones on the page https://quart-db.readthedocs.io/en/latest/discussion/per_connection.html, do you say `g.connection` is convenient because you don't need to write `async with app.db_pool.acquire() as connection: ...`? I don't see the benefits of having 1 new connection per request. — scrollout, Jul 18 '23 at 20:40
Best to ask that as an issue/discussion on the Quart-DB repo - it is off topic for here. — pgjones, Jul 19 '23 at 21:31

Can't perform query from databases library's connection pool with async Flask

1 Answers1