0

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"])}

?
LoicM
  • 1,786
  • 16
  • 37

1 Answers1

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"])}
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828