How to pass the name of a column as a parameter in SQLAlchemy Core?

Viewed 6780

I have an sqlalchemy core bulk update query that I need to programmatically pass the name of the column that is to be updated.

The function looks as below with comments on each variable:

def update_columns(table_name, pids, column_to_update):
    '''
    1. table_name: a string denoting the name of the table to be updated
    2. pid: a list of primary ids
    3. column_to_update: a string representing the name of the column that will be flagged. Sometimes the name can be is_processed or is_active and several more other columns. I thus need to pass the name as a parameter.
    '''
    for pid in pids:
        COL_DICT_UPDATE = {}
        COL_DICT_UPDATE['b_id'] = pid
        COL_DICT_UPDATE['b_column_to_update'] = True
        COL_LIST_UPDATE.append(COL_DICT_UPDATE)

    tbl = Table(table_name, meta, autoload=True, autoload_with=Engine)
    trans = CONN.begin()
    stmt = tbl.update().where(tbl.c.id == bindparam('b_id')).values(tbl.c.column_to_update==bindparam('b_column_to_update'))
    trans.commit()

The table parameter gets accepted and works fine.

The column_to_update doesn't work when passed as a parameter. It fails with the error raise AttributeError(key) AttributeError: column_to_mark. If I however hard code the column name, the query runs.

How can I pass the name of the column_to_update for SQLAlchemy to recognize it?

EDIT: Final Script

Thanks to @Paulo, the final script looks like this:

def update_columns(table_name, pids, column_to_update):
    for pid in pids:
        COL_DICT_UPDATE = {}
        COL_DICT_UPDATE['b_id'] = pid
        COL_DICT_UPDATE['b_column_to_update'] = True
        COL_LIST_UPDATE.append(COL_DICT_UPDATE)

    tbl = Table(table_name, meta, autoload=True, autoload_with=Engine)         
    trans = CONN.begin()
    stmt = tbl.update().where(
                              tbl.c.id == bindparam('b_id')
                              ).values(**{column_to_update: bindparam('b_column_to_update')})
    CONN.execute(stmt, COL_LIST_UPDATE)
    trans.commit()
2 Answers

There is also another simple way: tbl.c[column_name_here]

Related