Mico Mico - 3 months ago 9
Python Question

Python: Generic/Templated getters

I have a code that simply fetches a user/s from a database

class users:
def __init__(self):
self.engine = create_engine("mysql+pymysql://root:password@127.0.0.1/my_database")
self.connection = self.engine.connect()
self.meta = MetaData(bind=self.connection)
self.users = Table('users', self.meta, autoload = true)

def get_user_by_user_id(self, user_id):
stmt = self.users.select().where(self.users.c.user_id == user_id)
return self.connection.execute(stmt)

def get_user_by_username(self, username):
stmt = self.users.select().where(self.users.c.username == username)
return self.connection.execute(stmt)

def get_users_by_role_and_company(self, role, company)
stmt = self.users.select().where(self.users.c.role == role).where(self.users.c.company == company)
return self.connection.execute(stmt)


Now, what I want to do is to make the getters generic like so:

class users:
def __init__(self):
self.engine = create_engine("mysql+pymysql://root:password@127.0.0.1/my_database")
self.connection = self.engine.connect()
self.meta = MetaData(bind=self.connection)
self.users = Table('users', self.meta, autoload = true)

def get_user(self, **kwargs):
'''How do I make this generic function?'''


So, instead of calling something like this:

u = users()
u.get_user_by_user_id(1)
u.get_user_by_username('foo')
u.get_users_by_role_and_company('Admin', 'bar')


I would just call the generic function like so:

u = users()
u.get_user(user_id=1)
u.get_user(username='foo')
u.get_user(role='Admin', company='bar')





So far, this was what I could think of:

def get_user(**kwargs):
where_clause = ''
for key, value in kwargs.items():
where_clause += '{} == {} AND '.format(key, value)
where_clause = where_clause[:-5] # remove final AND
stmt = "SELECT * FROM {tablename} WHERE {where_clause};".format(tablename='users', where_clause=where_clause)
return self.connection.execute(stmt)


Is there any way that I could use the ORM style to create the statement?

Answer

Fully generalized, so any combination of legal field names is accepted as long as a field of that name exists in the table. The magic is in getattr, which allows us to look up the field we're interested in dynamically (and raises AttributeError if called with a non-existent field name):

def get_user(self, **kwargs):
    # Basic query
    stmt = self.users.select()

    # Query objects can be refined piecemeal, so we just loop and
    # add new clauses, assigning back to stmt to build up the query
    for field, value in kwargs.items():
        stmt = stmt.where(getattr(self.users.c, field) == value)

    return self.connection.execute(stmt)
Comments