Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

I get a cryptic KeyError when I tryto use asyncpg's executemany method #807

Closed
salimfadhleyhtp opened this issue Aug 11, 2021 · 5 comments · Fixed by #848
Closed

I get a cryptic KeyError when I tryto use asyncpg's executemany method #807

salimfadhleyhtp opened this issue Aug 11, 2021 · 5 comments · Fixed by #848

Comments

@salimfadhleyhtp
Copy link

  • asyncpg version: 0.24.0
  • PostgreSQL version: PostgreSQL 11.9 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (GCC) 7.4.0, 64-bit
  • Python version: 3.7.11
  • Platform: Windows
  • Do you use pgbouncer?: No
  • Did you install asyncpg with pip?: Yes

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

@elprans
Copy link
Member

elprans commented Aug 11, 2021

executemany expects a sequence of tuples not mappings. We ought to handle incorrect input data better, of course.

@salimfadhleyhtp
Copy link
Author

Thanks, it looks like I misunderstood the argument types. Yes, I think list of dicts, list of lists, and list of tuples should all be supported, but I know what I did wrong now.

@elprans
Copy link
Member

elprans commented Aug 13, 2021

Supporting mappings would be confusing, because the iteration is done over keys in Python. It would also be inconsistent with the handling of query arguments in Postgres/asyncpg in general as they're inherently positional.

@salimfadhleyhtp
Copy link
Author

yes, perhaps all that's needed is a clearer error message in case the user passes in the wrong type of record?

@elprans
Copy link
Member

elprans commented Aug 18, 2021

Definitely.

elprans added a commit that referenced this issue Nov 7, 2021
This adds a check that elements of sequence passed to `executemany()`
are proper sequences themselves and notes the offending sequence element
number in the exception message.  For example:

    await self.con.executemany(
        "INSERT INTO exmany (b) VALUES($1)"
        [(0,), ("bad",)],
    )

    DataError: invalid input for query argument $1 in element #1 of
               executemany() sequence: 'bad' ('str' object cannot be
	       interpreted as an integer)

Fixes: #807
elprans added a commit that referenced this issue Nov 16, 2021
This adds a check that elements of sequence passed to `executemany()`
are proper sequences themselves and notes the offending sequence element
number in the exception message.  For example:

    await self.con.executemany(
        "INSERT INTO exmany (b) VALUES($1)"
        [(0,), ("bad",)],
    )

    DataError: invalid input for query argument $1 in element #1 of
               executemany() sequence: 'bad' ('str' object cannot be
               interpreted as an integer)

Fixes: #807
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants