试图通过sqlalchemy将数据插入数据库时出现语法错误

Question

我不熟悉python和web开发。我正在尝试执行这个SQL alchemy insert命令（我从视频中看到了它），但它给了我语法错误。我无法理解炼金术文档

@app.route("/")
def index():
    id = "Vishal"
    p = "vishal94"
    connection.execute("INSERT INTO books (username , password) VALUES(:username , :password)",{"username":id, "password":p})
    dat = connection.execute("select * from books")
    return render_template("data.html",dat = dat)


sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near ":"
LINE 1: INSERT INTO books (username , password) VALUES(:username , :...
                                                       ^

[SQL: INSERT INTO books (username , password) VALUES(:username , :password)]
[parameters: {'username': 'Vishal', 'password': 'vishal94'}]
(Background on this error at: http://sqlalche.me/e/f405)

我实际上是从cs50课程视频中复制了这个命令，但仍然不起作用

Answer 1

将"INSERT INTO books (username , password) VALUES(:username , :password)"包装在text()中

text("INSERT INTO books (username , password) VALUES(:username , :password)")

我认为:variable语法要求

Answer 2

您可以使用%(key)s为您的查询定义关键字参数

@app.route("/")
def index():
    id = "Vishal"
    p = "vishal94"
    connection.execute("INSERT INTO books (username , password) VALUES (%(username)s, %(password)s)", {"username":id, "password":p})
    dat = connection.execute("select * from books")
    return render_template("data.html",dat = dat)