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

score 7 · Accepted Answer · edited Dec 05 '19 at 12:39

7

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.

edited Dec 05 '19 at 12:39

Ilja Everilä

50,538
7
126
127

answered Dec 19 '13 at 15:20

Paulo Scardine

73,447
11
124
153

How do I then pass the `bindparam(b_column_to_mark)` to the query since a bulk update requires this binding parameter? – lukik Dec 19 '13 at 15:42
The second option is more of what I want as I've seen `getattr` fetches the column name. However, when I run it, am getting the error `AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'items' ` – lukik Dec 19 '13 at 16:08
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. If you need it to be dynamic, use the `**kwargs` notation: `.values(**{'column_to_update': value})` – Paulo Scardine Dec 19 '13 at 16:25
Thanks for the guidance. I've edited the question to include the final code. I've used the `.values(**{'column_to_update': value})` which I really don't understand how it works but it works. Does the `**` mean it is now `python magic`? and how does it then translate to the column that we want? – lukik Dec 19 '13 at 17:13
Yes, there is some magic here but it is pretty straight forward. You can use a dictionary to pass named arguments to a function: `fn(**{'key': value})` is equivalent to `fn(key=value)` - same goes for positional arguments, `fn(*[arg1, arg2, argN])` is the same as `fn(arg1, arg2, argN)`. This can be very handy, for more fun see http://stackoverflow.com/questions/36901/what-does-double-star-and-star-do-for-python-parameters – Paulo Scardine Dec 19 '13 at 17:46

score 2 · Answer 2 · answered Nov 18 '19 at 12:06

2

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

answered Nov 18 '19 at 12:06

igolkotek

1,687
18
16

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

2 Answers2