flask postgresql ERROR: syntax error at or near "

Question

i'm new to flask and dev in general this error is so subjective i can't google even after seeing couple of posts on stackoverflow i couldnt figure it out everything correct and literally i'm pulling my hair

@app.route("/bookPage/<all>" , methods=["GET" , "POST"])
def bookPage(all):
    global all2
    if request.method == "POST":

        #variables
        list1 = ['101','milk','3','jack','2018-09-27 16:33:58.553349' ]

        db.execute("INSERT INTO review_table (id, review, rate , user , time_t) VALUES (:id, :review, :rate , :user , :time_t)" , {"id" : list1[0], "review" : list1[1], "rate": list1[2] ,"user":list1[3] , "time_t" :list1[4] })
        db.commit()
        print( f"review has been added of value : ,{review} \n ,and rate of :  , { rate_i} ,\n , by user : , { user_i }, \n, and Id : , {id_i}" )
    else:

        print("\n\n",all,"\n\n")
        all1 = all.split(",")
        all2 =[a.strip("()").replace("'","") for a in all1]




    return render_template("bookPage.html",all1=all2)

everytime i excute it i get this error ( ERROR: syntax error at or near "user" ) he points on the "user" in

"INSERT INTO review_table (id, review, rate , user , time_t)

everything correct i'm nw but i have done the db execute many times

table in db is empty postgresql of columns

Column Type Comment

id integer
review character varying NULL
rate integer NULL
user character varying NULL
time_t time without time zone NULL

please let me know why is this keep happening i've changed user input with constant string "test" i got the same error ... i think the error is misleading

The error is exact: your column list contains the key word `user`. If you wish to use identifiers that collide with key words, you will have to use quoted identifiers. — Ilja Everilä, Sep 27 '18 at 17:28

score 0 · Answer 1 · answered Sep 27 '18 at 16:05

0

You need to convert the values to the right type before sending them to the database.

    user_i = ''
    rate_i = request.form.get("rate", type=int)
    user_i = session['user']
    time_i = datetime.now()
    id_i = int(all2[0])

    db.execute("INSERT INTO review_table (id, review, rate , user , time_t) VALUES (?, ?, ? , ? , ?)" , (id_i, review_i, rate_i , user_i , time_i ))

answered Sep 27 '18 at 16:05

Mehdi

278
4
11

id_i = int(all2[0]) , rate_i = request.form.get("rate", type=int) , i replaced both but it didnt work , same error - ERROR: syntax error at or near "user" --- what i should convert time_i to ? – RXRO Sep 27 '18 at 16:17
you need to convert time too. to utc datetime. – Mehdi Sep 27 '18 at 17:31

Viktor Ilienko · Accepted Answer · 2018-09-27T17:29:39.673

Let your database fill the field with time and date, add to your CREAT TABLE this:

time_t time without time zone DEFAULT current_timestamp

And in your INSERT do not use time_t:

    db.execute("INSERT INTO review_table (id, review, rate , user ) VALUES (:id, :review, :rate , :user )" , {"id" : list1[0], "review" : list1[1], "rate": list1[2] ,"user":list1[3] })

You can find other variants of this solution: Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine

flask postgresql ERROR: syntax error at or near "

2 Answers2