Sqlalchemy pass parameter in session.query

Question

Im trying to insert the table name and the column name as parameters from dictionary

for key in columns_mapping:
    query = session.query(columns_mapping[key]['TABLE'].columns_mapping[key]['Name']).distinct()
    df = pd.read_sql(query.statement, query.session.bind)
    df = clean_format_table(df, columns_mapping[key]['Name'])
    exist_connectors = df[columns_mapping[key]['Name']]
    new_query = session.query(ReportCyclePopulation.columns_mapping[key]['Name']).distinct(). \
        filter(ReportCyclePopulation.columns_mapping[key]['Name'].notin_(tuple(exist_connectors)))
    df_new = pd.read_sql(new_query.statement, new_query.session.bind)
    df_new = clean_format_table(df_new, 'location_from')
    key = columns_mapping[key]['FORMAT'](df_new,columns_mapping[key]['Name'], columns_mapping[key]['COLUMN'])
    session.bulk_insert_mappings(columns_mapping[key]['TABLE'], key.to_dict(orient="records"))
    session.commit()

but I getting this error- exec(code_obj, self.user_global_ns, self.user_ns) File "", line 2, in query = session.query(columns_mapping[key]['TABLE'].columns_mapping[key]['Name']).distinct() AttributeError: 'str' object has no attribute 'columns_mapping'

how I could pass the values of the dictionary to the query

walid barakeh walid barakeh · Accepted Answer · 2020-07-01T07:55:14

It was my fault to use a string in the dictionary not the class for example :

    columns_mapping = {'loads': {
            'TABLE': LoadConnector,
            'NAME':'loads',
            'FORMAT': load_connectors_table,
            'COLUMN':'load_connector',
        },

and in the query I used this

    for key in columns_mapping:
        query = session.query(getattr(columns_mapping[key]['NEWTABLE'], columns_mapping[key]['NAME'])).distinct()
        df = pd.read_sql(query.statement, query.session.bind)
        df = clean_format_table(df, columns_mapping[key]['NAME'])

and its works as expected hope you could enjoy this answer

Sqlalchemy pass parameter in session.query

1 Answers