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>But you did all the hard work.. It's just a matter of combining the functions you created, initializing the input variables and throwing some <code>if</code> statements in the mix.</p>

<pre><code>def get_user(self, user_id=0, username='', role='', company=''):
    if user_id:
        stmt = self.users.select().where(self.users.c.user_id == user_id)
        return self.connection.execute(stmt)
    elif username:
        stmt = self.users.select().where(self.users.c.username == username)
        return self.connection.execute(stmt)
    elif role and company:
        stmt = self.users.select().where(self.users.c.role == role).where(self.users.c.company == company)
        return self.connection.execute(stmt)
    else:
        print('Not adequate information given. Please enter "ID" or "USERNAME", or "ROLE"&amp;"COMPANY"')
        return
</code></pre>

<p>Note that <code>user_id</code> has been initialized to <code>0</code> so that it has a boolean of <code>False</code>. If a 0 id is possible, set it directly to <code>False</code> instead. So, since the input cannot be 'random', is there a reason you want to do it with <code>**kwargs</code>?</p>

<hr>

<p>Alternatively, if the numberof combinations are too many to code, i would go a different route (SQL-injection-valnurable script incoming!) and that is the following:</p>

<pre><code>def get_user(self, query):
    form_query = 'SELECT user FROM {} WHERE {}'.format(table_name, query)
    # now execute it and return whatever it is you want returned
</code></pre>

<p>You are no longer passing variables to the function but rather a string which will appended to the query and executed.
<strong>Needless to say you have to be very careful with that.</strong> </p>

python python-3.x sqlalchemy metaprogramming