SNOW-1043694: Error Inserting Record: String Formatting Conversion Error

[indrasvat]

Python version

Python 3.11.7

Operating system and processor architecture

macOS-13.4.1-arm64-arm-64bit

Installed packages

anyio==4.1.0
asn1crypto==1.5.1
certifi==2023.7.22
cffi==1.16.0
charset-normalizer==3.3.2
click==8.1.7
cryptography==41.0.5
filelock==3.13.1
h11==0.14.0
httpcore==1.0.2
httpx==0.25.2
idna==3.4
loguru==0.7.2
packaging==23.2
platformdirs==3.11.0
pycparser==2.21
PyJWT==2.8.0
pyOpenSSL==23.3.0
python-dotenv==1.0.0
pytz==2023.3.post1
PyYAML==6.0.1
requests==2.31.0
sniffio==1.3.0
snowflake-connector-python==3.5.0
sortedcontainers==2.4.0
tomlkit==0.12.3
typing_extensions==4.8.0
urllib3==1.26.18

What did you do?

The details are in the `What did you expect to see?` section.

What did you expect to see?

I encountered an error while trying to insert a record into Snowflake using the Python connector. Below are the sanitized table creation command, Python code snippet, and the error message.

Table Setup:

CREATE TABLE IF NOT EXISTS my_database.my_schema.audit_action_log (
    run_timestamp TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP,
    run_id VARCHAR(255) NOT NULL,
    action_type VARCHAR(100) NOT NULL,
    action_details VARIANT,
    success BOOLEAN NOT NULL,
    user_id VARCHAR(255),
    notes TEXT,
    workflow_id VARCHAR(255),
    PRIMARY KEY (run_id)
);

Python Code Snippet:

def insert_audit_record(self, audit_record, batch_size=1000):
    try:
        if self.connection is None:
            self._create_connection()

        with self.connection.cursor() as cursor:
            cursor.execute("BEGIN")
            import logging
            logging.getLogger('snowflake.connector').setLevel(logging.DEBUG)

            cursor.execute(self.audit_queries.get("audit_action_log"), (
                audit_record.run_id,
                audit_record.action_type,
                json.dumps(audit_record.action_details) if audit_record.action_details else None,
                str(audit_record.success),
                audit_record.user_id,
                audit_record.notes,
                audit_record.workflow_id
            ))

            # Further processing and batch insertion for campaign log...

            cursor.execute("COMMIT")
            # Logging success message...
    except Exception as e:
        if self.connection is not None:
            self.connection.cursor().execute("ROLLBACK")
        # Logging error message...
        raise
    finally:
        self.close_connection()

Insert statement (read from yaml file):

audit_action_log:
  development: &audit_action_log_query_dev
    value: |
      INSERT INTO my_database.my_schema.audit_action_log
      (
          run_id, action_type, action_details, success, user_id, notes, workflow_id
      )
      SELECT
        column1, column2, PARSE_JSON(column3), column4, column5, column6, column7
      FROM VALUES (?, ?, ?, ?, ?, ?, ?);

Error Message:

While trying to execute the insert operation, I encountered the following error related to string formatting conversion:

DEBUG    | cursor.py:_preprocess_pyformat_query:604 - binding: [INSERT INTO my_database.my_schema.audit_action_log ( run_id, action_type, action_details, success, user_id, notes, workflow_id ) SELECT column1, column2, PARSE_JSON(column3), column4, column5, column6, column7 FROM VALUES (?, ?, ?, ?, ?, ?, ?);] with input=[('ff7fba27-49a6-4d81-b39b-de9cfa5204f4', 'abc', '{"se_id": 1, "st_type": "aaa", "target_updates": [{"cid": 19748636879, "bid": 10913452999, "target": 0}]}', 'True', '[email protected]', 'insert_audit_record', '')], processed=[...]

ERROR    | custom_logger:insert_audit_record:130 - Error inserting audit record, transaction rolled back: not all arguments converted during string formatting

  File "path/to/venv/lib/python3.11/site-packages/snowflake/connector/cursor.py", line 616, in _preprocess_pyformat_query
    query = command % processed_params
TypeError: not all arguments converted during string formatting

Could you help me understand why this string formatting error is occurring and how to resolve it?

[sfc-gh-aling]

hey @kodeninja ,thanks for reaching out.
you're using qmark style parameter in your insertion, by default the connector user pyformat style parameter.
to make it work, you just need to set paramstyle to "qmark" like the follow before you run your code:

snowflake.connector.paramstyle = "qmark"

(for more information about the paramstyle, it could be found in the python db api v2: https://peps.python.org/pep-0249/#paramstyle)

[indrasvat]

Thanks @sfc-gh-aling! That totally worked 🙂