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.

Dunes · Accepted Answer · 2022-12-08 17:08:32Z

6

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}',
        ],
    ]
)

answered Dec 8, 2022 at 17:08

Dunes

42.1k7 gold badges86 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

EdGaere Over a year ago

Nice. This saved me hours of head banging... Thanks

Je Je Over a year ago

once stored in Postgres12, I get all double quotes " preceded by escape \, is this normal behaviour?

Collectives™ on Stack Overflow

Insert json data in table with Asyncpg using "executemany"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related