Syntax error when trying to insert data into my database through sqlalchemy

Question

I am new to python and web development. I am trying to execute this SQL alchemy insert command (i have watched it from a video) but it is giving me syntax error. I am not able to understand the alchemy documentation.

@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)

I have actually copied this command from a cs50 course video still not working.

Answer 1

Wrap your "INSERT INTO books (username , password) VALUES(:username , :password)" in text().

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

I think the :variable syntax requires that

Answer 2

You can go for %(key)s to define keyword arguments for you query.

@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)