In Record complex data coming as string

[mrinalkamboj]

• asyncpg version: 0.20.1
• PostgreSQL version: "version" (using select version() "PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit"
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?: Using installed version on a GCP server not Saas
• Python version: 3.8.2
• Platform: CentOS8
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: Yes
• If you built asyncpg locally, which version of Cython did you use?: NA
• Can the issue be reproduced under both asyncio and uvloop?: Yes

Following is my asyncpg code for fetching data:

 conn = await asyncpg.connect(
                user,
                password,
                database,
                host,
                port,
            )
            records = await conn.fetch(query)
            values = [dict(record) for record in records]
            return values

record result that is received contains a complex object in the form of string as follows:

'Affinity':'{"Quantity" : 0, "Revenue" : 0, "Margin" : 0, "Adjustable_Quantity" : 0, "Adjustable_Revenue" : 0, "Adjustable_Margin" : 0}'

Due to which there's extra parsing required to convert data from string to object. Is it possible to not get the data as string by default, like other adapters psycopg2 provide. Please recommend a workaround otherwise this needs lots of code addition to parse each and every object. I would ideally expect caller to directly consume json objects instead of parse and consume

[elprans]

Perhaps you're looking for this: https://magicstack.github.io/asyncpg/current/usage.html#example-automatic-json-conversion

[mrinalkamboj]

Perhaps you're looking for this: https://magicstack.github.io/asyncpg/current/usage.html#example-automatic-json-conversion

thanks for the reply, this example is related to supplying the codec for non-standard data type like json and how to translate that directly from db, My issue is with regular fetch and how it is translated into the json using standard python utility, where for a key, which contains valid json object as value, it is translated into a string, which means extra processing / parsing is required at the receiving end to reconvert, which could have been avoided. Same issue is not seen with aiopg

[elprans]

which means extra processing / parsing is required at the receiving end to reconvert, which could have been avoided.

There is no way to avoid JSON parsing. JSON is always transmitted as a string at the protocol level. aiopg and psycopg2 chose to decode JSON values by default: https://www.psycopg.org/docs/extras.html#json-adaptation. In asyncpg JSON values are not decoded by default and the documentation link I provided shows how to tell asyncpg to decode them.

[SKY-ALIN]

You could specify json decoder when asyncpg creates connection:

import json


async def get_conn():
    async with pool.acquire() as conn:
        await conn.set_type_codec('json', encoder=json.dumps, decoder=json.loads, schema='pg_catalog')
        yield conn