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()

Could you give a small input/output sample to help illustrate your error? — Enthus3d, Sep 24 '19 at 21:08
The ids are posted from a form on the index.html. 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. Not sure if this answers your question — user12115445, Sep 25 '19 at 00:24
@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() — user12115445, Sep 25 '19 at 00:30
You can edit your question and add these in for future answerers. The '>' symbol will display them as input/output. — Enthus3d, Sep 25 '19 at 00:34
Possible duplicate of [How can I bind a list to a parameter in a custom query in sqlalchemy?](https://stackoverflow.com/questions/13190392/how-can-i-bind-a-list-to-a-parameter-in-a-custom-query-in-sqlalchemy) — Gord Thompson, Sep 26 '19 at 16:28

score 1 · Accepted Answer · answered Sep 26 '19 at 23:48

Below is the solution. Make sure to

add - from sqlalchemy import bindparam

remove - brackets from the query

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

How to fix data binding issue with SqlAlchemy

1 Answers1