Python:通用/模板化 getter

Question

我有一个代码，可以简单地从数据库中获取用户

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)

现在，我想做的就是使 setter/getter 变得通用，如下所示:

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?'''

所以，不要这样调用:

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

我会像这样调用通用函数:

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

<小时/>

到目前为止，这是我能想到的:

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)

有什么方法可以使用 ORM 样式来创建语句吗？

Answer 1

完全通用，因此只要表中存在该名称的字段，就可以接受任何合法字段名称的组合。神奇之处在于 getattr，它允许我们动态查找我们感兴趣的字段(如果使用不存在的字段名称调用，则会引发 AttributeError):

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)