Python Forum

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,

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

Error:
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

what datatype are On and Off columns in your database table? If you execute the above statement in the DB console does it work?

---

Also your column name On is problem, as ON is reserved word in MySQL

INSERT INTO Monday (Program, OnState, Off) VALUES (2,'0:00:00','0:00:00')

should work. Note that I changed the column name, as well added single quotes around time values

Finally - I think your code can be simplified substantially

(Oct-24-2017, 02:33 PM)buran Wrote: [ -> ]what datatype are On and Off columns in your database table? If you execute the above statement in the DB console does it work?

---

Also your column name On is problem, as ON is reserved word in MySQL

INSERT INTO Monday (Program, OnState, Off) VALUES (2,'0:00:00','0:00:00')

should work. Note that I changed the column name, as well added single quotes around time values 

Dude, the amount of beer I owe you is incredible, changed column name and worked first time. Thank you.

(Oct-24-2017, 02:41 PM)buran Wrote: [ -> ]Finally - I think your code can be simplified substantially

I know, I just wanted to get the base features working then I'll go back and evalute it.
Many thanks again