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

Question

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

Answer 1

I'm not sure if I understood what you want, and your code looks very different from what I consider idiomatic sqlalchemy (I'm not criticizing, just commenting we probably use orthogonal code styles).

If you want to pass a literal column as a parameter use:

from sqlalchemy.sql import literal_column
...
tbl.update().where(
    tbl.c.id == bindparam('b_id')
).values({
    tbl.c.column_to_update: literal_column('b_column_to_update')
})

If you want to set the right side of the expression dynamically, use:

tbl.update().where(
    tbl.c.id == bindparam('b_id')
).values({
    getattr(tbl.c, 'column_to_update'): bindparam('b_column_to_update')
})

If none of this is not what you want, comment on the answer or improve your question and I will try to help.

[update]

The values method uses named arguments like .values(column_to_update=value) where column_to_update is the actual column name, not a variable holding the column name. Example:

stmt = users.update().\
        where(users.c.id==5).\
        values(id=-5)

Note that where uses the comparison operator == while values uses the attribution operator = instead - the former uses the column object in a Boolean expression and the latter uses the column name as a keyword argument binding.

If you need it to be dynamic, use the **kwargs notation: .values(**{'column_to_update': value})

But probably you want to use the values argument instead of the values method.

Answer 2

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