Insert data to SQL through pymysql and flask

[iainstott]

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

[buran]

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

[buran]

Finally - I think your code can be simplified substantially

[iainstott]

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.

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