Insert json data in table with Asyncpg using "executemany"

Question

I want to insert with Asyncpg some json data in a table (2 columns: id , cluster_json). I want to use the "executemany" function to speedup the insert process.

my code:

async def main():

    conn = await asyncpg.connect('postgresql://postgres:postgres@localhost:5432/postgres')
    statement = '''INSERT INTO cluster(cluster_json) VALUES($1) '''
    await conn.executemany(statement, [{"name":"John", "age":30, "car":null},
                                       {"name":"John1", "age":31, "car":null}'])

    await conn.close()

asyncio.get_event_loop().run_until_complete(main())

but I get the following error :

asyncpg.exceptions.DataError: invalid input in executemany() argument sequence element #0: expected a sequence, got dict

I tryied to pass the dictionaries as str. also got a bug.

The error message is clear , the code the pretty similar to the one in the documentation,
expect that I want insert json data . Unfortunately, I don't see what I am missing. Does someone spot the issue / help me ? Thanks in advance.

Answer 1

You need to double nest your JSON-blob in a list. The first list is for each row you want to insert. The second list is for each argument you want to pass to the SQL statement. asyncpg doesn't try parse your SQL statement. So it doesn't know you are only using one argument. So it needs you to hold it's hand a little and give a list of all the arguments for the statement, even if that list only has one element.

await conn.executemany(
    statement,
    [   # for first execution of statement 
        [   # first argument (i.e. $1) of first execution of statement
            '{"name":"John", "age":30, "car": null}',
        ],
        # for second execution of statement 
        [   # first argument (i.e. $1) of second execution of statement
            '{"name":"John1", "age":31, "car": null}',
        ],
    ]
)