Generic function that accept a table and column name and returns all the primary key values that matches a filter value given

i have functions that look like this that is littered through out the code

def get_M_status(S): 
    M_id = marital.select(marital.c.marital_status_description == S).execute().fetchone()
    if M_id == None:
        print "Warning: No Marital id found for %s Marital status to Single" % S
        M_id = marital.select(marital.c.marital_status_description == "Single").execute().fetchone()       
    return M_id[0]

i was wondering i开发者_StackOverflow社区f their is a way to write a generic function where i can pass the relevant values ie: table name primary key column filter column and filter value

cheers

If the primary key is only one column, you can do something like:

getattr(table.c, pkey_col_name) == S

as the 'generic' version of marital.c.marital_status_description == S.

So, something like (please note: this is untested):

def get_row(table, col_name, val, default=None):
    col = getattr(table.c, col_name)
    row = table.select(col == S).execute().fetchone()
    if row == None:
        print "Warning: No row found for %s in %s; using %s" % (val, table, default)
        row = table.select(col == default).execute().fetchone()       
    return row[0]

If you have mapped classes, this is even easier; you can do things like:

record = session.query(Marital).get(key)

where Marital is the mapped class for the table marital, session is a sql alchemy session, key is a tuple of key columns (in order). If the key exists in the table, record will be the row found; otherwise it will be None.

The table object has a primary_key attribute that contains the columns that make up the primary key. Select that, just add the where clause and you're done:

def get_pks_by_col(tbl, col_name, col_value):
    s = select(tbl.primary_key.columns).where(tbl.columns[col_name] == col_value)
    return s.execute().fetchall()

Modify to suit your specific conditions. (len(tbl.primary_key) == 1 is guaranteed, need to pass in the connection to execute on, etc.)