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? — johnDanger
– johnDanger, Commented May 7, 2020 at 19:00

nichoio · Accepted Answer · 2020-07-27 13:45:53Z

9

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, 2020 at 13:45

nichoio

7,9074 gold badges32 silver badges44 bronze badges

answered Mar 13, 2020 at 19:34

mateus_pd

1061 silver badge3 bronze badges

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

Comments

Jivan · Accepted Answer · 2014-12-26 15:06:25Z

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, 2014 at 15:06

answered Dec 26, 2014 at 12:26

Jivan

23.4k16 gold badges92 silver badges144 bronze badges

5 Comments

Sigils Over a year ago

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

Jivan Over a year ago

@Sigils ok I found another method. Just edited my answer to reflect that.

Sigils Over a year ago

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?

Jivan Over a year ago

@Sigils at which line exactly are you getting your AttributeError?

Sigils Over a year ago

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

Collectives™ on Stack Overflow

SQLAlchemy and RAW SQL: List as an input

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related