0

I am trying to pass a variable to a SQLAlchemy IN clause, but not sure how to do it. I have tried different ideas, but they have not worked so far.

My question is how do I pass the object vars to the SQL IN clause and get pandas to read it correctly. I have included an abridged version of the code I've been working on.

import sqlalchemy as sa
import pandas as pd

# assign variable with credentials
engine = create_engine(sa.engine.url.URL.create(drivername='',
                            username='',
                            password='',
                            host='', 
                            database='',
                            ))

# to use in statement
vars = "2241, 2242, 2242, 2242, 2242"

# sql query
data_query = sa.text(""" 
                SELECT  id, score
                FROM (
                      r.id
                      Avg(do.scorepoints)                       AS score,
                FROM wa.resp r
                JOIN wa.resp ra
                ON r.id = ra.id 
                WHERE r.org = 111
                AND ra.objid IN :vars
                """)


# run pandas to get sql query data
dataset = pd.read_sql(data_query,
                      con = engine,
                      params={'vars':vars}
                      )
GSA
  • 751
  • 8
  • 12
  • The method of doing the substitution depends on what database backend you're using. Some use the `:vars` method, some use `%(vars)s`, some use `?`. You have to know that. – Tim Roberts May 14 '21 at 00:58
  • 2
    @timroberts The SQLA `text` construct normalizes placeholders and uses the `:named` style. @gsa Which DBMS are you using? Some drivers support passing tuples as arguments to render a value list in brackets. A DBMS agnostic approach would be to use SQLA expanding bind params: https://stackoverflow.com/a/56382828/2681632 – Ilja Everilä May 14 '21 at 06:33

0 Answers0