Python: Generic/Templated getters
<p>I have a code that simply fetches a user/s from a database</p>

<pre><code>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)
</code></pre>

<p>Now, what I want to do is to make the getters generic like so:</p>

<pre><code>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?'''
</code></pre>

<p>So, instead of calling something like this:</p>

<pre><code>u = users()
u.get_user_by_user_id(1)
u.get_user_by_username('foo')
u.get_users_by_role_and_company('Admin', 'bar')
</code></pre>

<p>I would just call the generic function like so:</p>

<pre><code>u = users()
u.get_user(user_id=1)
u.get_user(username='foo')
u.get_user(role='Admin', company='bar')
</code></pre>

<hr>

<p>So far, this was what I could think of:</p>

<pre><code>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)
</code></pre>

<p>Is there any way that I could use the ORM style to create the statement?</p>

<p>Try something like:</p>

<pre><code>def get_user(self, **kwargs):
    if 'user_id' in kwargs:
        (...)
    elif  'username' in kwargs:
        (...)
    elif all(a in ['role','company'] for a in kwargs):
        (...)
    else:
        (...)
</code></pre>

python python-3.x sqlalchemy metaprogramming