How to fix data binding issue with SqlAlchemy

Question

I am using Python 3.6 with Flask. I am trying to connect to Amazon Redshift database using SqlAlchemy. The query has IN operation in the Where clause and the values for it are sent by binding it to the query. For some reason it does not work? It does not error out, but does not fetch any results. If I hard code the values in the query, it works fine.

I have tried a few suggested options but no luck - 1. binding the values as a list or just as comma separated string 2. removing the brackets in the query 3. changing the code to stmt = text(file.read()) stmt = stmt.bindparams(search = ids) df = pd.read_sql_query(stmt, connection)

    dtv_script.txt
    Select * from tbl1 where id IN (:search)


    def get_dt(id_list):

        engine = create_engine('postgresql://xxxxxxxxxx')

        connection = engine.connect()

        ids = list(id_list.split(","))

        #dtv_script.txt has the sql
        file = open('dtv_script.txt')
        sql = text(file.read())

        df = pd.read_sql_query(sql, connection, params={'search' : ids})

        connection.close()
        return df

The ids are posted from a form on the index.html. Sample ids = 2011592,2021593,2033591. The flask route page captures it in the get_dt() function and returns the dataframe back to the results.html page for display

@app.route('/result', methods=['POST']) 
def result(): 
    if request.method == 'POST': 
        id_list = request.form.get('ids') 
        df_dt = dofri.get_dt(id_list) 
        return render_template('result.html', **locals()) 
    else: 
        flash('There was some error. Check the logs') 
        return index() 

Answer 1

Below is the solution. Make sure to

1. add - from sqlalchemy import bindparam
2. remove - brackets from the query
3. add - expanding=True

dtv_script.txt
        Select * from tbl1 where id IN :search

def get_dt(id_list):

    engine = create_engine('postgresql://xxxxxxxxxx')
    connection = engine.connect()

    ids = list(id_list.split(","))

    #dtv_script.txt has the sql
    file = open('dtv_script.txt')


    sql = file.read()


    t = text(sql)
    t = t.bindparams(bindparam('search', expanding=True))
    df = pd.read_sql_query(t, connection, params={'search' : ids })
    connection.close()
    return df