CODEWITHSUNDEEP
pythonsqlalchemyargs
Anna
Anna

Reputation: 974

How to dynamically use select with SQLAlchemy?

I am trying to create a function which can filter a sql table using SQLAlchemy, with optional parameters.

The function is

def fetch_new_requests(status, request_type, request_subtype, engine, id_r=None):
        table = Table('Sample_Table', metadata, autoload=True,
                           autoload_with=engine)
        
        query = session.query(load_requests).filter_by(status = status, 
                                                      request_type = request_type, 
                                                      request_subtype = request_subtype, 
                                                      id_r = id_r)
        return pd.read_sql((query).statement,session.bind)

But it returns every time an empty table if I do not define id_r variable

I have googled but cannot find a woraround

The I have tried to use **kwargs, but it is not what I really need, I mean here I have to explicitly define column names and again come to the issue with id_r

def fetch_new_requests(**kwargs):
    
    for x in kwargs.values():
        query = session.query(load_requests).filter_by(status=x)
    return pd.read_sql((query).statement,session.bind)

My ideal result

def fetch_new_requests(any column names, values of the columns):
    
    for x in kwargs.values():
        query = session.query(load_requests).filter_by(column_name=column_value)
    return pd.read_sql((query).statement,session.bind)

In theorie I can use 2 lists or a dict but if there is another solution would be happy to hear

Upvotes: 0

Views: 1558

Answers (1)

c8999c 3f964f64
c8999c 3f964f64

Reputation: 1627

I can only give you an answer for SQLAlchemy core syntax, but it works with a dict! It has the column names in its keys and their required values, in the values.

table = Table('Sample_Table', metadata, autoload=True,
                       autoload_with=engine)
query = table.select()
where_dict = {"status": 1, "request_type": "something"}
for k, v in where_dict.items():
    query = query.where(getattr(table.c, k) == v)

just for completeness: here's the syntax to select only specific fields (your question kinda sounds like you're also looking for this):

query = table.select().with_only_columns(select_columns)  # select_columns is a list

Upvotes: 1

Related Questions