Sqlalchemy: detect string vs. sequence of values passed to in_()

[OHeimlich]

Describe the bug
While querying an object using the in_ function on integer column and passing a string instead of list of integers, the string is parsed to a tuple instead of raising an error.

Expected behavior
Expected to exception to be raised

To Reproduce
Please try to provide a Minimal, Complete, and Verifiable example.
See also Reporting Bugs on the website, and some example issues

from sqlalchemy import create_engine

engine = create_engine('sqlite:///:memory:', echo=True)
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    password = Column(String)

    def __repr__(self):
        return f'User {self.name}'


Base.metadata.create_all(engine)
query = session.query(User).filter(User.id.in_("123456"))
print(query)

Error

SELECT users.id AS users_id, users.name AS users_name, users.password AS users_password 
FROM users 
WHERE users.id IN (?, ?, ?, ?, ?, ?)

Versions.

• OS:
• Python: 3.7
• SQLAlchemy: 1.3.12
• Database:
• DBAPI:

Additional context

Have a nice day!

[zzzeek]

hey there -

the SQL generated is exactly what you asked for, in_() is passed a sequence, each member of which becomes an element in the IN clause, and in this case you passed a string, which in Python is a sequence of characters.

Can you indicate the exact SQL you are wishing to emit to the SQLite database? thanks. (edit: oh, it should raise, yes).

[zzzeek]

this raises in 1.4 as we've rewritten the entire system by which SQL elements are constructed:

>>> from sqlalchemy import column, Integer
>>> column('q', Integer).in_("134")
Traceback (most recent call last):
...
sqlalchemy.exc.ArgumentError: IN expression list, SELECT construct, or bound parameter object expected, got '134'.
>>> 

confirmed that 1.3's parsing system (fully rewritten in 1.4) does not have this behavior.

your test program (great MCVE!) in 1.4.0beta1:

sqlalchemy.exc.ArgumentError: IN expression list, SELECT construct, or bound parameter object expected, got '123456'.

[zzzeek]

dupe of #4617

fixed in f07e050c9ce4afdeb9c