Bind a list to a parameter as json array in custom SQLAlchemy query

Question

I know thanks to this website that it is possible to bind a list to a custom query parameter, however I have only seen it done with an IN clause.

What I am trying to do is to pass a list to a MySQL JSON_ARRAY, with no success at the moment

My code looks like this:

from sqlalchemy import text
query = text("INSERT into models (name, elements) VALUES (:model_name, :elements)"
session.execute(query, {"model_name": "foo", "elements": ["bar", "baz"]}

But it raises OperationalError: (pymysql.err.OperationalError) (1241, 'Operand should contain 1 column(s)') as I assume that each element of the list is identified as an indidividual column.

I tried to enclose the :elements parameters in a JSON_ARRAY(...) to no avail.

Is there a way to leverage SQLALchemy's text here, or do I need to go back to building custom strings to pass with something like

session.execute(query, {"model_name": "foo", "elements": '", "'.join(["bar", "baz"])}

?

Bill Karwin · Accepted Answer · 2022-06-10 15:29:50Z

1

I'd serialize your python array to a string in JSON format, then you can bind it as a single scalar value.

import json
session.execute(query, {"model_name": "foo", "elements": json.dumps(["bar", "baz"])}

answered Jun 10, 2022 at 15:29

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Bind a list to a parameter as json array in custom SQLAlchemy query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related