How to Give Column Name Dynamically from String Variable in SQL Alchemy Filter

How to give column name dynamically from string variable in sql alchemy filter?

Just use getattr standard python library function to get an attribute by name:

col_name = 'subject'
db_session.query(Notice).filter(getattr(Notice, col_name).like("%" + query + "%"))

How do you pass a variable as a column name in sqlalchemy?

Use getattr

.order_by(desc(getattr(test_result, request.form['desc'])))

SQLAlchemy with_entities with dynamic inputs

I had this same challenge and this is how I solved it.
columns is just a CSV string eg "field1,field2,field3"
Could be further condensed but this is enough for readability.

def pick_Columns(modelClass, columns):
  if len(columns) == 0:
    return None

  colArray = columns.split(',')
  modelObjects = [eval(f'{modelClass.__name__}.{col}') for col in colArray]
  return modelObjects

def Get_Data(**kwargs):
  retVal = kwargs['db'].query(m_DataModel)

  if kwargs.get('columns', None) is not None:
    colObj = pick_Columns(m_DataModel, kwargs['columns'])
    retVal = retVal.with_entities(*colObj)
    retVal = [row._asdict() for row in retVal.all()] # with_entities returns a tuple instead of dict. Remove this line if that's what you want

  else:
    retVal = retVal.all()

  return retVal

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

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.

Turning a string into variable or object, Python

It seems like you could use getattr:

getattr(Match, objective)

Call table values dynamically with sqlalchemy

In Python getattr() is used for getting attributes based on variables. As an addition, you don't need to query the whole model object and all its attributes, if all you want is just one:

def get_value(self, table, key):
    # Note the comma after value. It unpacks the 1-tuples.
    for value, in self.session.query(getattr(table, key)):
        print(value)

---

Related Topics