python - 使用关键字参数作为函数输入更改 sqlite3 表中的信息，如何在 Python 中执行此操作？

Question

关键字参数是 idx、name 和密码。将索引为 3 的记录更新为名称“Brian”，该方法称为更新(idx=3，名字='布赖恩')。如果参数 idx 不存在，则该方法返回 False。也可能存在密码，并且使用给定值更新具有索引的记录对于字段。

我已经尝试过类似的操作，但得到一个 str object not callable 错误，我尝试查看这里的其他脚本，但我不断收到错误。

def update(self, **kwargs):
    ''' keyword arguments are idx, name and password.
     For example, to update the record with index 3 to
     the name ’Brian’, the method is called as
     update(idx=3, name=’Brian’). The method returns
     False if the parameter idx is absent. In addition
     to name, also the passwd may be present and the record
     with the index is updated with the given values
     for the fields. The method update returns True if the
     updates succeeded, or False otherwise.'''

    if 'idx' in kwargs:

        query = 'UPDATE players set name = ?, password = ? WHERE idx = ?' (kwargs['name'], kwargs['password'],kwargs['idx'])

        self.cr.execute(query)

        self.db.commit()

        print('records updated')

        return True

    else:

        print('records failed to update')

        return False

Answer 1

你不能像你做的那样把参数放在查询中:

query = 'UPDATE players set name = ?, password = ? WHERE idx = ?' (kwargs['name'], kwargs['password'],kwargs['idx'])

Python 会认为您正在尝试调用该字符串文字，就像它是一个函数一样。

相反，在执行查询时传递 args，因为 execute() 方法实际上是将您的值填充到 SQL 语句中。

query = 'UPDATE players set name = ?, password = ? WHERE idx = ?'
self.cr.execute(query, (kwargs['name'], kwargs['password'], kwargs['idx']))

更好的方法是使用命名占位符；然后你可以只传递 kwargs 而不必挖掘你想要的字段:

query = 'UPDATE players set name = :name, password = :password WHERE idx = :idx'
self.cr.execute(query, kwargs)

要回答您评论中的问题，您可以通过遍历字典，根据您具有值的字段动态创建查询:

assert "idx" in kwargs   # idx is a required field
query = "UPDATE players SET"
for field in kwargs:
    if field != "idx":
        query += " {f} = :{f},".format(f=field)
query = query.strip(",")   # strip off unneeded comma after last field
query += " WHERE idx = :idx"
self.cr.execute(query, kwargs)

或者作为单个语句:

query = "UPDATE players SET " + ", ".join(f + " = :" + f 
         for f in kwargs if f != "idx") + " WHERE idx = :idx"