MYSQL statement not working in Flask - You have an error in your SQL syntax [duplicate]

Question

my app.py file contains the MYSQL statements as well

@app.route("/stock",methods=('GET', 'POST'))
def stock():
    dat=date.today()
    # cursor = mysql.connection.cursor(dictionary=True)
    cursor.execute("SELECT * FROM stock")
    items = cursor.fetchall()
    cursor.execute("SELECT * FROM orders ORDER BY ID DESC LIMIT 1")
    orde = cursor.fetchone()
    order = int(orde["id"]+1)
    dump = json.dumps(items)
    total = 0
    if request.method == 'POST':
        if not request.form.get("IO", False) or request.form["client"]=="Choose":
            flash("Please fill in or out radio buttons and Customer","danger")
        elif request.form["IO"] == "out":
            values = ['id','name','Out']
            record = [order,request.form["client"],True]
            abc = request.form["client"]
            query = "SELECT * FROM customers WHERE name = %s" 
            cursor.execute(query,[abc])
            customers = cursor.fetchone()
            for item in items:
                foo = int(request.form[item["name"]])
                values.append(item['name'])
                record.append(foo)
                if foo>int(item["stock"]):
                    flash("not enough stock","danger")
                    return redirect(url_for('stock'))
                sto=item["stock"]-foo
                minitotal = foo*item["price"]
                total = total + minitotal
                cursor.execute("UPDATE `stock` SET stock = %s WHERE (name = %s)",[sto,item["name"]])
            today = request.form["today"]
            values.append('Price')
            values.append('date')
            record.append(total)
            record.append(today)
            trecord = tuple(record)
            vrecord = tuple(values)
            query = "INSERT INTO orders {0} VALUES {1}"
            print(vrecord)
            cursor.execute(query.format(vrecord,trecord))  
            mydb.commit()
            return redirect(url_for('bill',client=abc))
...............
     return render_template('New_Order.html',items=items,customers=customerees,suppliers = suppliers,index=My_list,dump=dump,date=dat)

the error is this :

mysql.connector.errors.ProgrammingError: 1064 (42000): 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 ''id', 'name', 'Out', 'KSA4060', 'PAA003S', 'PAA003SS', 'Lapdesk', 'ASM704PH', 'L' at line 1

There is not a length problem here: when I used a single tuple before the code worked but my values got messed up. so I added another tuple for the columns. the code stops working mid-word as you can see in the error statement. the values are equal - below are the two tuples getting used

vrecord = ('id', 'name', 'Out', 'KSA4060', 'PAA003S', 'PAA003SS', 'Lapdesk', 'ASM704PH', 'LED4060', 'ASM820', 'ASM806', 'ASM823', 'ASM857C', 'KP3780', 'KX8680', 'KX8060', 'PA3550A', 'PA3550B', 'PA3550AS', 'PA3550BS', 'ASM353', 'PA353S', 'PA353SA', 'Price', 'date')
trecord = (1015, 'Mufaddal', True, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 6050, '2022-01-22')

If you think my database schema is the problem itself please suggest any changes there as well.

The PROBLEM is that you are using .format to create your INSERT command. That does not create proper SQL syntax, as you would see if you printed the formatted query. You need to use%s substitution, just like you do in the UPDATE query above. — Tim Roberts
– Tim Roberts, Commented Jan 22, 2022 at 7:40

Tim Roberts · Accepted Answer · 2022-01-23 08:03:51Z

0

You can build the INSERT query like this:

            trecord = tuple(record)
            vrecord = tuple(values)
            fields = ','.join(vrecord)
            values = ','.join(['%s']*len(trecord))
            query = f"INSERT INTO orders ({fields}) VALUES ({values})"
            print(vrecord)
            cursor.execute(query, trecord)  
            mydb.commit()

edited Jan 23, 2022 at 8:03

answered Jan 22, 2022 at 7:42

Tim Roberts

55.3k4 gold badges28 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mufaddal Kothari Over a year ago

the same kind of error shows up : ``` mysql.connector.errors.ProgrammingError: 1064 (42000): 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 'id,name,Out,KSA4060,PAA003S,PAA003SS,Lapdesk,ASM704PH,LED4060,ASM820,ASM806,ASM8' at line 1 ```

Tim Roberts Over a year ago

I forgot the parens around both substitutions.

Mufaddal Kothari Over a year ago

I'm not exactly following. what does substitutions mean ?

Mufaddal Kothari Over a year ago

Thank a lot worked like a charm, just changes the separator a little bit: fields = ''+','.join(vrecord)+''

Tim Roberts Over a year ago

You shouldn't need that, unless your column names unwisely have special characters.

|

Collectives™ on Stack Overflow

MYSQL statement not working in Flask - You have an error in your SQL syntax [duplicate]

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related