Python SQL Syntax

Question

I am having issues with the following:

db.execute('INSERT INTO "users" ("id", "username", "hash") VALUES (NULL, username =: username, password =: password)', username = request.form.get("username"), password = generate_password_hash(request.form.get("password")))

I was able to get it to work with the following:

username = request.form.get("username") password = generate_password_hash(request.form.get("password"))

db.execute('INSERT INTO "users" ("id", "username", "hash") VALUES (NULL, "{0}", "{1}")'.format(username, password))

However, .format() is not recommended and I would like to make it work with proper syntax

Answer 1

EDIT:

But wait there's more! That is not valid syntax for an insert with bound parameters. The VALUES clause should only the supply the names of the bound parameters, the args following the query will supply the values for those parameters. So really what should work is:

INSERT INTO "users" ("id", "username", "hash") 
VALUES (NULL, :username, :password)',
 username = request.form.get("username"), 
 password = generate_password_hash(request.form.get("password"))

---

---

I think there is an easy fix to the original sql. You were not clear why it "didn't work", but it looks like extra white space.

INSERT INTO "users" ("id", "username", "hash") 
VALUES (NULL, username =: username, password =: password)',
 username = request.form.get("username"), 
 password = generate_password_hash(request.form.get("password"))

In the VALUES clause, : username should be :username (no space following the :). Ditto password.

Assuming "id" is an integer primary key (and/or autoincrement), there is no need to include it in the insert columns (or VALUES clause).