CODEWITHSUNDEEP
sqlalchemy
zwirbeltier
zwirbeltier

Reputation: 937

sqlalchemy: How to bind a list of parameters to a literal string?

While I use proper ORM-mappers for most of my application, there is a few very complicated to implement. That's why I tried to use Literal-SQL.

Now I want to pass a list of values as parameter to that query.

A simplified example of the problem looks as follows:

ids = [1, 2, 3]
session.query('name') \
  .from_statement('SELECT name FROM users WHERE id IN(:ids)') \
  .params(ids=ids).all()

This doesn't work due to the error:

sqlalchemy.exc.InterfaceError: (InterfaceError) Error binding parameter 2 - probably unsupported type.

So how can I make that work?

Upvotes: 1

Views: 1965

Answers (1)

Penguinfrank
Penguinfrank

Reputation: 392

So it seems like sqlalchemy doesn't know what to do with the list type in params, though I suspect the error you listed was from another example based on parameter 2 being the problem.

Here is a link that addresses your listed error. For the list issue, you could try something like:

def makeSQLList(myList):
    if not myList:
        return "()"
    newString = "(" + str(myList[0])
    for i in range(1,len(myList)):
        newString += "," + str(myList[i])
    newString += ")"
    return newString

ids = [1, 2, 3]
session.query('name') \
  .from_statement(text('SELECT name FROM users WHERE id IN ' + makeSQLList(ids))) \
  .all()

Or you could extend sqlalchemy to accept whatever type you're having a problem with. Or, try posting your complicated query and maybe the community can help simplify using the ORM-mappers. Hope one of these options helps!

Upvotes: 1

Related Questions