Exception: Too many SQL Variables

[nazariyv]

Environment information

• OS: linux

• Python Version: 3.10.6

• ape and plugin versions: installed ape off this commit: 52919ca

• Contents of your ape-config.yaml:

geth:
  ethereum:
    mainnet:
      uri: http://erigon.dappnode:8545

plugins:
  - name: ens
  - name: etherscan
  - name: addressbook

What went wrong?

Cache does not work when the number of values to write to db is too large

• what command you ran
• the code that caused the failure (see this link for help with formatting code)

#!/usr/bin/env python
from ape import networks, Contract

context = networks.parse_network_choice('ethereum:mainnet:geth')
context.__enter__()
kongs = '0xef0182dc0574cd5874494a120750fd222fdb909a'
contract = Contract(kongs)
contract_transfer = [log.dict() for log in contract.Transfer]

• full output of the error you received

INFO: Connecting to existing Erigon node at 'http://erigon.dappnode:8545'.
Traceback (most recent call last):
  File "/home/shredder/.cache/pypoetry/virtualenvs/testing-rWIBWXE2-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/home/shredder/.cache/pypoetry/virtualenvs/testing-rWIBWXE2-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: too many SQL variables

The above exception was the direct cause of the following exception:

[...]
  File "/home/shredder/.cache/pypoetry/virtualenvs/testing-rWIBWXE2-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) too many SQL variables
[SQL: INSERT OR IGNORE INTO contract_events (event_name, contract_address, event_arguments, transaction_hash, block_number, block_hash, log_index, transaction_index) VALUES (?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?),  [...]

How can it be fixed?

Not an SQL expert, but it seems to me that it does not like the fact that we are inserting a lot of values. The issue is probably in the fact that we are passing so many (?, ?, ?, ...). Need to read the SQLite docs, but I am sure this is easily fixable.

[nazariyv]

According to this, you only need one set of question marks.

Source: https://docs.python.org/3/library/sqlite3.html

[nazariyv]

event_arguments is a dict itself, however, so might need flattening. I will test.

[nazariyv]

Any reason to prefer SQL vs NoSQL? I think because the data here will be primarily objects and nested objects, a NoSQL would be preferable. For example, MongoDB.

[fubuloubu]

Any reason to prefer SQL vs NoSQL? I think because the data here will be primarily objects and nested objects, a NoSQL would be preferable. For example, MongoDB.

sqlitedb has very easy support within SQLAlchemy. If there is a similarly easy document structure database that works well with Pydantic, I could entertain using that as well. We already have some caching support for contract deployments so it would be great to unify these cache structures for easier management and querying capabilities.