Python client array of array binding

Integrations Client Libraries
1

I am trying to use the following query using the Python client (0.27.1) on Crate 4.8.2 and it is failing. The table looks like:

|endpoint_name           |client_id|role      |enabled|modification_date      |creation_date          |description|method|
|------------------------|---------|----------|-------|-----------------------|-----------------------|-----------|------|
|alarm_config_info_delete|0        |admin     |t      |2022-08-25 09:00:27.409|2022-08-22 09:40:01.817|/alarm     |delete|
|alarm_config_info_delete|4        |admin     |f      |2022-08-25 09:36:34.963|2022-08-25 09:36:34.963|/alarm     |delete|
|alarm_config_info_delete|0        |user      |t      |2022-08-25 09:00:27.409|2022-08-22 09:40:01.817|/alarm     |delete|
|alarm_config_info_delete|0        |viewer    |f      |2022-08-25 09:00:27.409|2022-08-22 09:40:01.817|/alarm     |delete|
|alarm_config_info_delete|0        |superadmin|t      |2022-08-25 09:00:27.451|2022-08-22 09:40:01.889|/alarm     |delete|

The table is created using the folowing sql:

CREATE TABLE IF NOT EXISTS "doc"."api_permissions" (
   "endpoint_name" TEXT NOT NULL,
   "client_id" BIGINT NOT NULL,
   "role" TEXT NOT NULL,
   "enabled" BOOLEAN DEFAULT false,
   "modification_date" TIMESTAMP WITHOUT TIME ZONE GENERATED ALWAYS AS _cast(current_timestamp, 'timestamp without time zone'),
   "creation_date" TIMESTAMP WITHOUT TIME ZONE DEFAULT _cast(current_timestamp, 'timestamp without time zone') NOT NULL,
   "description" TEXT,
   "method" TEXT,
   PRIMARY KEY ("endpoint_name", "client_id", "role")
)
CLUSTERED INTO 2 SHARDS
WITH ( column_policy = 'dynamic')

The folowing sql using the console works as expected:

select * from doc.api_permissions ap where ARRAY[description,method] = any([['/alarm', 'delete'],['/alarm', 'put']]);

But when using the following simple code in python:

sql_check = 'SELECT endpoint_name, description, method, role, enabled FROM doc.api_permissions ' \
                'WHERE ARRAY[description, method] = ANY(?)'
params_check = [['/alarm', 'delete'],['/alarm', 'put']]
    cursor.execute(sql_check, params_check)

I get an exception:

crate.client.exceptions.ProgrammingError: SQLParseException[Cannot cast value `[/alarm, delete]` to type `text_array_array`]

It seems it is converting the elements of the outer array to text. Is there any way to accomplish the original console query with python?

Best regards,

2

The params are themselves an array. And the parameter placeholder ? is of type TEXT(TEXT(ARRAY)), i.e. you have to use triple brackets [[['/alarm', 'delete'],['/alarm', 'put']]]

sql_check = 'SELECT endpoint_name, description, method, role, enabled FROM doc.api_permissions ' \
                'WHERE ARRAY[description, method] = ANY(?)'
params_check = [[['/alarm', 'delete'],['/alarm', 'put']]]
    cursor.execute(sql_check, params_check)```
3

A JOIN might be the more performant option btw. something along the line:

SELECT
    ap.endpoint_name,
    ap.description,
    ap.method,
    ap.role,
    ap.enabled
FROM doc.api_permissions ap 
  JOIN (SELECT
        UNNEST(['/alarm', '/alarm']) "description",
        UNNEST(['delete', 'put']) "method"
  ) b USING ("description","method")
1 Like
4

Oh, what a stupid mistake. Back to work is hard…
Thank you very much.

1 Like