SQLAlchemy and RAW SQL: List as an input

Question

I am using Flask-SQLAlchemy, and I am having some trouble with the IN clause in the SQL syntax. The data I want my IN clause to "read" is a list with some data, for example

args = [1, 2, 3]

here is how my code looks like.

connection = db.session.connection()
raw_sql = text("""
        SELECT
          *
        FROM 
         table
        WHERE data IN :list
        """)
query = connection.engine.execute(raw_sql, {'list' : args})

I have tried giving inserting tuples and list to the args parameter, but nothing have worked. I am either getting:

Python 'tuple' cannot be converted to a MySQL type with args = tuple([1, 2, 3])
Python 'list' cannot be converted to a MySQL type, when using args = [1, 2, 3]

how do you read from a list with SQLAlchemy and using RAW SQL and parameters as an input?

Does this answer your question? [How can I bind a list to a parameter in a custom query in sqlalchemy?](https://stackoverflow.com/questions/13190392/how-can-i-bind-a-list-to-a-parameter-in-a-custom-query-in-sqlalchemy) — johnDanger, May 07 '20 at 19:00

score 8 · Accepted Answer · edited Jul 27 '20 at 13:45

8

On python 3.7:

import sqlalchemy

args = [1, 2, 3]
raw_sql = "SELECT * FROM table WHERE data IN :values" 
query = sqlalchemy.text(raw_sql).bindparams(values=tuple(args))
conn.engine.execute(query)

edited Jul 27 '20 at 13:45

nichoio

6,289
4
26
33

answered Mar 13 '20 at 19:34

mateus_pd

96
1
3

Jivan · Answer 2 · 2014-12-26T15:06:25.543

0

You should try this method:

args = [1, 2, 3]
raw_sql = "SELECT * FROM table WHERE data IN %s" 
params = [(args,)] # the comma allows to convert the list into a tuple
conn.engine.execute(raw_sql, params)

edited Dec 26 '14 at 15:06

answered Dec 26 '14 at 12:26

Jivan

21,522
15
80
131

Hello @Jivan, thanks for the answer, but I am afraid that converting it to a string won't work. I am getting the MySQL error code: `1064`. Even if I try the SQL syntax in MySQL Workbench. I am getting the same error. It's not allowing me to write IN `"(1, 2, 3)"` or IN `'(1, 2, 3)'` and if I remove the str method I am still getting the same error! _it i also underlined in MySQL Workbench that the "" is an SQL syntax error_ – Sigils Dec 26 '14 at 14:39
@Sigils ok I found another method. Just edited my answer to reflect that. – Jivan Dec 26 '14 at 15:07
I am sorry but that did neither work. I am really glad that you are taking your time and trying to help me. But the error I am getting now is `AttributeError: 'list' object has no attribute 'keys'`. I am using python 3.4 if that matters? And yes I did write exactly how you did! Perhaps I should write it with ORM instead of RAW SQL? – Sigils Dec 26 '14 at 15:25
@Sigils at which line exactly are you getting your `AttributeError`? – Jivan Dec 26 '14 at 15:34
I am using WSGI application to show the errors. Not getting any line errors. But the last traceback error was `keys = distilled_params[0].keys()` builtin code for SQLAlchemy – Sigils Dec 26 '14 at 15:43

SQLAlchemy and RAW SQL: List as an input

2 Answers2