SQLAlchemy with_entities with dynamic inputs

Question

how can I provide the with_entities option with dynamic input?

At the moment I have to do this:

columns = [DataModel.col1, DataModel.col2, ...]
data = DataModel.query.order_by(DataModel.id.desc()).with_entities(*columns).first()

But what should I do, if I get the col name as query-string parameters and have to define it dynamically?

EDIT EDIT EDIT solved it this way: In my model I define the classmethod:

@classmethod
def find_by_filter(cls, attr):
    search_columns = [getattr(cls, i) for i in attr]

    return cls.query.order_by(cls.id.desc()).with_entities(*search_columns).first()

and then I can call it from my Rest API this way:

liste = ["column 1", "column2", "column3", "column4"]

data = DataModel.find_by_filter(liste)

Did you have a look at https://stackoverflow.com/questions/10251724/how-to-give-column-name-dynamically-from-string-variable-in-sql-alchemy-filter? — Ilja Everilä, Nov 19 '19 at 18:45
Possible duplicate of [How to give column name dynamically from string variable in sql alchemy filter?](https://stackoverflow.com/questions/10251724/how-to-give-column-name-dynamically-from-string-variable-in-sql-alchemy-filter) — Doobeh, Nov 19 '19 at 19:04

score 2 · Accepted Answer · answered May 12 '20 at 19:12

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

SQLAlchemy with_entities with dynamic inputs

1 Answers1