Python Forum
Insert data to SQL through pymysql and flask
Thread Rating:
  • 2 Vote(s) - 3.5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Insert data to SQL through pymysql and flask
#1
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
Reply
#2
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
Reply
#3
Finally - I think your code can be simplified substantially
Reply
#4
(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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How can users store data temporarily in flask app? darktitan 6 2,859 Mar-21-2022, 06:38 PM
Last Post: darktitan
  Creating tables with pymysql using Xampp newbie1 0 2,077 Jul-15-2020, 11:10 AM
Last Post: newbie1
  how to save the data from MySQL to CSV in Flask farah97 4 2,899 Jan-03-2020, 03:02 AM
Last Post: farah97
  Using flask to add data to sqlite3 table with PRIMARY KEY catafest 1 3,705 Sep-09-2019, 07:00 AM
Last Post: buran
  Flask data with dynamic updates from panadas parthi1705 0 2,065 Jun-19-2019, 09:59 AM
Last Post: parthi1705
  pymysql.err.InterfaceError nikos 1 3,000 Feb-24-2019, 02:07 PM
Last Post: nikos
  Issue with bottle-pymysql nikos 13 5,360 Feb-23-2019, 11:15 AM
Last Post: nikos
  unable to import pymysql IMuriel 3 7,292 Jan-08-2019, 08:56 PM
Last Post: IMuriel
  flask requests display data from api on webpage with javacript pascale 0 2,743 Oct-25-2018, 08:30 PM
Last Post: pascale
  Paginate json data in flask or jinja2 soli1994 1 8,249 Jun-28-2018, 06:00 PM
Last Post: gontajones

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020