Better Python error handling message when (header=true) is not passed during `COPY FROM` (hashtag2737)

[vikramsubramanian]

Consider a case where we're trying to insert LivesIn relations to an edge table:(:Person)-[:LivesIn]->(:City)

The data for the lives_in.csv is like this:

person_id,city_id
1,2
2,1

The following command is correct, and loads in the data when considering the header row.

COPY LivesIn FROM 'lives_in.csv' (header=true);

However, if the user forgets to specify the (header=true)clause, it will fail as expected, but the error message is not helpful:

Traceback (most recent call last):
  File "/code/kuzu-debug/load_data.py", line 59, in <module>
    conn.execute(load_lives_in_edges)
  File "/code/kuzu-debug/.venv/lib/python3.11/site-packages/kuzu/connection.py", line 80, in execute
    _query_result = self._connection.execute(
                    ^^^^^^^^^^^^^^^^^^^^^^^^^
RuntimeError: Conversion exception: Cast failed. person_id is not in INT64 range.

The above error message makes it seem like the data is incorrect, rather than something wrong with the way we specified it in the edge insertion query.

A better error message would be:

RuntimeError: Conversion exception: Cast failed. Expected INT64, got "person_id"

Stating it this way clearly states what the parser expects, and what it found, making user experience during debugging much better.

)

[mayil-ai[bot]]

Summary: Improving error message for missing (header=true) clause in COPY FROM command in Python.

Possible Solution

Based on the provided information, the issue seems to be related to the COPY command not being executed with the header=true option, which is causing a cast failure when trying to interpret the first row of the CSV file as data instead of as headers. To resolve this issue:

• Modify the COPY command execution in the load_lives_in_edges function or wherever it is being called to include the header=true option.
• Update the error handling for the COPY command to provide a more informative error message when the header is missing or when there is a type casting issue due to incorrect CSV formatting.
• Ensure that the execute method in connection.py can handle and propagate exceptions correctly, possibly by enhancing the error message to include suggestions for common issues like missing headers.

Here is a concise solution:

• Locate the load_lives_in_edges function in the codebase.
• Update the COPY command string to include the header=true option:

  copy_command = "COPY LivesIn FROM 'lives_in.csv' (header=true);"
  conn.execute(copy_command)

• In connection.py, enhance the error handling for the COPY command to check for the presence of a header and provide a more descriptive error message if the header is missing or if there is a type casting issue. For example:

  class Connection:
    def execute(self, command: str) -> None:
        try:
            # Existing implementation...
        except SomeSpecificException as e:
            if "Cast failed" in str(e):
                raise RuntimeError("Conversion exception: Cast failed. Ensure the CSV file includes a header or that the data types are correct.") from e
            else:
                raise

• Test the changes to ensure that the COPY command now works with the CSV file and that the error message is more informative when an issue occurs.

Code snippets to check

• tools/python_api/test/test_exception.py

  This file contains tests for exception handling in the Python API, which is relevant to improving error messages.

• test/copy/e2e_copy_transaction_test.cpp

  This file contains tests for the COPY command, which is directly related to the issue of handling headers during data import.