title: Executing WHERE IN using bindparameters in Sqlalchemy/Postgres
body: <p>I try to do a <code>SELECT ... WHERE id IN (1,2,3)</code> efficiently using Sqlalchemy with Postges.</p>
  
  <p>If I do a simple select:</p>
  
  <pre><code>s.query(Model).filter(Model.id.in_([1,2,3])).all()
  </code></pre>
  
  <p>Sqlalchemy runs this query:</p>
  
  <pre><code>SELECT model.id AS model_id FROM model 
  WHERE model.id IN (%(id_1)s, %(id_2)s, %(id_3)s)
  {'id_1': 1, 'id_2': 2, 'id_3': 3}
  </code></pre>
  
  <p>When the array gets longer this is not efficient. Also this does not work with baked queries.</p>
  
  <p>Knowing that Postgres supports tuples as parameters, I tried to put in my Array/Tuple directly into the parameter section by using a bind parameter:</p>
  
  <pre><code>s.query(Model)
   .filter(Model.id.in_(bindparam('my_tuple')))
   .params(my_tuple=(1,2,3)).all()    
  </code></pre>
  
  <p>Unfortunately Sqlalchemy does not accept <code>bindparam</code> in an <code>in_</code>:</p>
  
  <pre><code>sqlalchemy.exc.InvalidRequestError: 
      in_() accepts either a list of expressions or a selectable:
          BindParameter('my_tuple', None, type_=NullType())
  </code></pre>
  
  <p>So I tried to trick Sqlalchemy somehow to accept a <code>bindparam</code>.<br>
  Extending the <code>BindParam</code> class I was able to do so:</p>
  
  <pre><code>class TupleBindParameter(BindParameter, Selectable):
      pass
  
  s.query(Model)
   .filter(Model.id.in_(TupleBindParameter('my_tuple')))
   .params(my_tuple=(1,2,3)).all()
  </code></pre>
  
  <p>Now I get what I wanted:</p>
  
  <pre><code>SELECT model.id AS model_id FROM model 
  WHERE model.id IN %(my_tuple)s
  {'my_tuple': (1, 2, 3)}
  </code></pre>
  
  <p>This solution seems somehow hacky to me. Is there an official way to get Sqlalchemy do do the same?</p>
  
  <p>--</p>
  
  <p>The setup to reproduce my examples is very simple:</p>
  
  <pre><code>Base = declarative_base()    
  class Model(Base):
      __tablename__ = 'model'
      id = Column(Integer, primary_key=True)
      def __init__(self, id): self.id = id
  
  engine = create_engine('postgres://x:x@localhost/x')    
  Base.metadata.create_all(bind=engine)
  
  Session = sessionmaker(bind=engine)
  s = Session()
  s.add_all([Model(1), Model(2), Model(4)])
  s.commit()
  </code></pre>