Oct-24-2017, 01:44 PM
Wonder if someone can help as I'm going round in circles on something that should be simple.
All I am trying to do is input some data from a form in a flask web app to a database. I have the SQL server running ok because I can query and insert data elsewhere, I'm just having problems with this one.
I have tried constructing the SQL query in many, many ways up to now and either get ERROR 1064 there is an error in your syntax or TypeError: not all arguments converted during string formatting.
Latest incarnation looks like this,
Many Thanks
Iain
All I am trying to do is input some data from a form in a flask web app to a database. I have the SQL server running ok because I can query and insert data elsewhere, I'm just having problems with this one.
I have tried constructing the SQL query in many, many ways up to now and either get ERROR 1064 there is an error in your syntax or TypeError: not all arguments converted during string formatting.
Latest incarnation looks like this,
@app.route('/saveschedule', methods=['POST', 'GET']) def saveschedule(): results=[] schedDB=pymysql.connect(host=dbHOST,port=int(dbPORT),user=dbUSER,password=dbPSWD,db="CentralHeatingData") SCHEDcur=schedDB.cursor() day = request.form['day'] Prog1 = str(request.form['Program_1']) On1 = str(request.form['1_onTime']) Off1 = str(request.form['1_offTime']) Prog2 = request.form['Program_2'] On2 = request.form['2_onTime'] Off2 = request.form['2_offTime'] Prog3 = request.form['Program_3'] On3 = request.form['3_onTime'] Off3 = request.form['3_offTime'] Prog4 = request.form['Program_4'] On4 = request.form['4_onTime'] Off4 = request.form['4_offTime'] Prog5 = request.form['Program_5'] On5 = request.form['5_onTime'] Off5 = request.form['5_offTime'] Prog6 = request.form['Program_6'] On6 = request.form['6_onTime'] Off6 = request.form['6_offTime'] Prog7 = request.form['Program_7'] On7 = request.form['7_onTime'] Off7 = request.form['7_offTime'] Prog8 = request.form['Program_8'] On8 = request.form['8_onTime'] Off8 = request.form['8_offTime'] Prog9 = request.form['Program_9'] On9 = request.form['9_onTime'] Off9 = request.form['9_offTime'] HeatLogger=getLoggerState() heatingState=getHeatingState() livingRoomTemp, bedRoomTemp=getHeatingTemps() networkState=getNetworkState() delTABLE="""TRUNCATE TABLE `%s`""" % (day) #SCHEDcur.execute(delTABLE) data=[ (Prog1, On1, Off1), (Prog2, On2, Off2), (Prog3, On3, Off3), (Prog4, On4, Off4), (Prog5, On5, Off5), (Prog6, On6, Off6), (Prog7, On7, Off7), (Prog8, On8, Off8), (Prog9, On9, Off9) ] for line in data: insertDATA="INSERT INTO "+day+" (Program, On, Off) VALUES " dbDATA=(line[0],line[1],line[2]) insertDATA=str(insertDATA) print(insertDATA) SCHEDcur.execute(insertDATA, dbDATA) SHEDcur.commit() if day == "Monday": redirectURL='/monday' if day == "Tuesday": redirectURL='/tuesday' if day == "Wednesday": redirectURL='/wednesday' if day == "Thursday": redirectURL='/thursday' if day == "Friday": redirectURL='/friday' if day == "Saturday": redirectURL='/saturday' if day == "Sunday": redirectURL='/sunday' return redirect(redirectURL)but I have tried multiple different ways of constructing it like
insertDATA=("INSERT INTO "+day+" (Program, On, Off) VALUES (%s,%s,%s)" % (str(line[0]),str(line[1]),str(line[2])))On this one the print command prints the right command, I think
Output:INSERT INTO Monday (Program, On, Off) VALUES (1,0:00:00,0:00:00)
but throwsError:pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version fo
r the right syntax to use near 'On, Off) VALUES (1,0:00:00,0:00:00)' at line 1"
Really going round in circles and tearing my hair out at this point.Many Thanks
Iain