IN condition with parameters using sqlalchemy

I'm need some help for making a query with SQLalchemy and SQL server using a simple web form.

Form fields are datefrom, dateto, and name so any user can filter the query base on their preferences.

datefrom, dateto are selected from a datepicker and they can be left blank to wider the query.

name is a multiselect item list: you can select one, two or none. If none is selected I want to query all the names in the database (more than 20).

SQL query file is external and load in the script.

python script:

params = {'date_from': date_from,
              'date_to': date_to,
              'names': names
         }

    with engine.connect() as con:
        with open(query_file, 'r') as query:
            sql = sqlalchemy.text(query.read())
            for key in params.keys():
                if type(params[key]) == list:
                    sql = sql.bindparams(bindparam(key, expanding=True))
            rs = con.execute(sql, params)
            results = pd.DataFrame(rs.fetchall(), columns=query_columns)

  

sql script:

 WHERE (
    (date >= :datefrom OR :datefrom IS NULL)
    AND
    (date <= :dateto OR :dateto IS NULL)
    )
    AND (user IN :name OR :name IS NULL)

The problem is with the IN condition. We have 3 cases.

CASE 1. :name = ['Mark']

CASE 2. :name = None (SQLalchemy change this to NULL)

CASE 3. :name = ['Mark','John']

The problem with this solution is with the 3rd case as SQLalchemy bind the query to (?,?) IS NULL which generates an error.

• I tried to solve this issue somehow in the SQL query without success. Using coalesce, CAST. I also use a flag and tried to use the order of precedence in the SQL statements to short something.

• Another try was to convert the list to a string and use that as parameter.

• The only that I didn't try yet is adding this WHERE clauses dynamically base of the user input and avoid checking things in the SQL query. Something like:

  sql += " AND user IN :name"

Thanks!