[FIXED] I get a cryptic KeyError when I tryto use asyncpg's executemany method

Issue

I'm trying to execute this code:

result = await conn.executemany(command=query, args=records)

where query is:

INSERT INTO elexeon_time_series (subject, flow, timestamp, value, update_time, environment) VALUES ($1, $2, $3, $4, $5, $6);

and records consists of the following data:

[{'environment': 'test',
  'flow': 'test_flow',
  'subject': 'test_subject_K4oESG2YRrnUhld',
  'timestamp': Timestamp('2021-08-11 10:34:19.458810'),
  'update_time': Timestamp('2021-08-11 12:34:19.458810'),
  'value': 0.4},
 {'environment': 'test',
  'flow': 'test_flow',
  'subject': 'test_subject_K4oESG2YRrnUhld',
  'timestamp': Timestamp('2021-08-11 11:34:19.458810'),
  'update_time': Timestamp('2021-08-11 12:34:19.458810'),
  'value': 0.5}]

When I try to execute this code, I get a rather cryptic traceback. can anybody explain what it means? What am I doing wrong?

test_postgres_aio.py:60: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
..\..\eunrg_utils\database\postgres_async.py:33: in async_insert_postgres_data
    result = await conn.executemany(command=query, args=records)
C:\installs\anaconda\envs\eunrg_utils\lib\site-packages\asyncpg\connection.py:355: in executemany
    return await self._executemany(command, args, timeout)
C:\installs\anaconda\envs\eunrg_utils\lib\site-packages\asyncpg\connection.py:1677: in _executemany
    result, _ = await self._do_execute(query, executor, timeout)
C:\installs\anaconda\envs\eunrg_utils\lib\site-packages\asyncpg\connection.py:1711: in _do_execute
    result = await executor(stmt, None)
asyncpg\protocol\protocol.pyx:254: in bind_execute_many
    ???
asyncpg\protocol\coreproto.pyx:945: in asyncpg.protocol.protocol.CoreProtocol._bind_execute_many_more
    ???
asyncpg\protocol\protocol.pyx:220: in genexpr
    ???
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

>   ???
E   KeyError: 0

asyncpg\protocol\prepared_stmt.pyx:149: KeyError

Solution

You are trying to send dictionary (so kind of named parameters) to query. As far as I know, there is a problem to send name parameters in asyncpg:

https://github.com/MagicStack/asyncpg/issues/9

I personally decided not to use asyncpg and started using aiopg which have more beautiful (from my point of view) API.

Answered By - Artiom Kozyrev