how can i convert the json/jsonb(in posrgresql) to dict when i use fetch_all to get rows,thanks

[jianmaikj]

[qyloxe]

Totally hacky and ugly - but - hey, it works!

instead of this:

result = await database.fetch_all(query, values)

you can write this (for jsonb type):

    async with database.connection() as connection:
        await connection.raw_connection._con.set_type_codec('jsonb',encoder=json.dumps,decoder=json.loads,schema='pg_catalog')
        result = await connection.fetch_all(query, values)

[florianwns]

I spent 2 hours figuring out why I got a str instead of a dict.
Thx for the advice

[mecampbellsoup]

Should this project provide a way to configure asyncpg's connection options via Database(...)? For non-ORM users like me almost all my DB queries will return JSON.

[florianwns]

On https://docs.sqlalchemy.org/en/14/dialects/postgresql.html, we can read this

By default asyncpg does not decode the json and jsonb types and returns them as strings. SQLAlchemy sets default type decoder for json and jsonb types using the python builtin json.loads function. The json implementation used can be changed by setting the attribute json_deserializer when creating the engine with create_engine() or create_async_engine().

Maybe we can adopt an option like json_deserializer from databases library !!

[florianwns]

An other solution => "postgresql+aiopg" ;)

[qyloxe]

well, asyncpg is MUCH MUCH more performant than aiopg. As in MUCH MUCH more. It uses binary postgres protocol, so the decision of using asyncpg is good. Check benchmarks.
We only need just some more options for setting those connection/query parameters and everything will be ok. With every postgres revision there is more and more reasons to use jsonb technology.

[florianwns]

Yes i know that.
Indeed, I haven't found a better solution than yours to implement this yet.
maybe, the best would be for asyncpg to decode jsonb directly, because there is a lot of encapsulation.

[jmtaysom]

What I am using is middleware based on @qyloxe suggestion so that all my requests have json processed correctly

@app.middleware("http")
async def get_connection(request: Request, call_next):
    async with db.database.connection() as connection:
        await connection.raw_connection._con.set_type_codec(
            'jsonb',
            encoder=json.dumps,
            decoder=json.loads,
            schema='pg_catalog'
        )
        response = await call_next(request)
    return response