python - 在Connection.execute sqlalchemy中运行UPDATE SET参数化sql语句

Question

我想在 flask_sqlalchemy 中运行参数化的 mysql UPDATE SET 语句。由于我不知道应该更新哪些列，我编写了一个辅助函数来帮助编写语句。

我的模型

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement= True)
    username = db.Column(db.String(80), nullable=False)
    email = db.Column(db.String(120), nullable=False)

    def __repr__(self):
        return '<User %r>' % self.username

辅助函数

def run_sql(params):
    # e.g: params = {'username': "testing", "email": "testing@testing.ts", "id": 1}
    id = params.pop("id")
    # params = {'username': "testing", "email": "testing@testing.ts"}
    sets = list(map(lambda col: f"{col}=:{col}", params.keys()))
    # sets = ["username=:username", "email=:email"]
    sets = ", ".join(sets)
    # sets = "username=:username, email=:email"
    params["id"] = id
    # params = {'username': "testing", "email": "testing@testing.ts", "id": 1}
    sql_statement = f"""UPDATE User SET {sets} WHERE id=:id LIMIT 1"""
    # sql_statement = UPDATE User SET username=:username, email=:email WHERE id=:id LIMIT 1
    return  sql_statement

调用辅助函数

if __name__ == "__main__":
    conn = engine.connect()
    params = {'username': "testing", "email": "testing@testing.ts", "id": 1}
    sql_statement = run_sql(params)
    conn.execute(sql_statement, params)

运行前面的代码会产生以下异常

"sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':username, email=:email WHERE id=:id LIMIT 1' at line 1") [SQL: 'UPDATE User SET username=:username, email=:email WHERE id=:id LIMIT 1'] [parameters: {'username': 'testing', 'email': 'testing@testing.ts', 'id': 1}] (Background on this error at: http://sqlalche.me/e/f405)"

SQL 语句在我看来很好，所以参数。我错过了什么吗？

Answer 1

绑定(bind)参数不是 MySQL 样式，并且当您将纯文本传递给 engine.execute() 时，SQLAlchemy 在执行查询之前不会将方言应用于查询。

试试这个:

engine.execute("SELECT :val", {"val": 1})  # will fail, same as your query

...然后是:

engine.execute("SELECT %(val)s", {"val": 1})  # will execute

用 text() 包装查询将使 SQLAlchemy 处理正确的绑定(bind)样式:

from sqlalchemy import text  # or you can use db.text w/ flask-sqlalchemy
engine.execute(text("SELECT :val"), {"val": 1})

另一件需要注意的事情是，SQLAlchemy 会自动为您处理 UPDATE 查询的构造，尊重参数字典中的值，例如:

id_ = 1
params = {'username': "testing", "email": "testing@testing.ts"}
User.__table__.update().values(params).where(id=id_)
# UPDATE user SET username=%(username)s, email=%(email)s WHERE user.id = %(id_1)s

params = {'username': "testing"}
User.__table__.update().values(params).where(id=id_)
# UPDATE user SET username=%(username)s WHERE user.id = %(id_1)s

params = {'username': "testing", "unexpected": "value"}
User.__table__.update().values(params).where(id=id_)
# sqlalchemy.exc.CompileError: Unconsumed column names: unexpected