pymysql.err.ProgrammingError when running tkinter+pymysql script

Question

Consider this code:

from tkinter import *
import pymysql

def newc():
    def inr1():

        var1 = var.get()
        connection = pymysql.connect(host='localhost', user='root', password='', db='class')
        cursor = connection.cursor()
        q = ("create table " + str(var1) + "(Name text,regno int,attendence text)")
        cursor.execute(q)
        connection.commit()
        connection.close()

    r2.withdraw()
    r3.deiconify()
    cfr=Frame(r3,width=800,height=600).grid(row=0,column=0)
    l3=Label(cfr,text="Enter the name of the class").grid(row=0,column=0)
    var=StringVar()
    ec=Entry(cfr,textvariable=var).grid(row=0,column=1)
    eb=Button(cfr,text="Create",command=inr1).grid(row=1,column=1)
    eb1=Button(cfr,text="quit",command=quit).grid(row=1,column=2)

def insstud():
    def inr():
        classname =sec.get()
        stu=name.get()
        re1=regno.get()
        at=att.get()
        connection = pymysql.connect(host='localhost', user='root', password='', db='class')
        cursor = connection.cursor()
        q = ("insert into "+str(classname)+" values(%s,%s,%s)")
        values=[stu,re1,at]
        cursor.execute(q,values)
        connection.commit()
        connection.close()

    r2.withdraw()
    s1.deiconify()
    sf = Frame(s1, width=800, height=600).grid(row=0, column=0)
    Label(sf,text="Class").grid(row=0,column=0)
    Entry(sf,textvariable=sec).grid(row=0,column=1)
    Label(sf,text="Name").grid(row=1,column=0)
    Entry(sf, textvariable=name).grid(row=1, column=1)
    Label(sf, text="Regno").grid(row=2, column=0)
    Entry(sf, textvariable=regno).grid(row=2, column=1)
    Label(sf, text="Attendance ").grid(row=3, column=0)
    Entry(sf, textvariable=att).grid(row=3, column=1)
    Button(sf,text="Submit",command=inr).grid(row=4,column=3)
def postlogin():
    root.destroy()
    r2.deiconify()
    nf2=Frame(r2,width=800,height=600).grid(row=0,column=0)
    # /*----For Displaying class---------s-*/
    b1=Button(r2,text="Show the details of a class",command=show).grid(row=0,column=0,rowspan=3,columnspan=3)
    # /*----For creating New class----------*/
    b2=Button(r2,text="Create a new class",command=newc).grid(row=0,column=2,rowspan=3,columnspan=3)
    b3=Button(r2,text="Insert a student into a class",command=insstud).grid(row=1,column=0,rowspan=3,columnspan=3)
    b4=Button(r2,text="Exit",command=quit).grid(row=1,column=2,rowspan=3,columnspan=3)


def sel():
    s1.destroy()
    data1 = []
    sec1=sec.get()
    print(sec1)
    connection = pymysql.connect(host='localhost', user='root', password='', db='class')
    cursor = connection.cursor()
    q = ("select * from " + str(sec1))
    cursor.execute(q)
    data = cursor.fetchall()

    for row in data:

        temp = [row[0], row[1], row[2]]
        data1.append(temp)
    cursor.close()
    connection.close()
    r1 = Tk()
    nf = Frame(r1, width=10, height=10).grid(row=0, column=0)
    l1 = Label(nf, text="Name").grid(row=0, column=0, sticky=W)
    l2 = Label(nf, text="Regno").grid(row=0, column=1, sticky=NW)
    l3 = Label(nf, text="Attendance perc").grid(row=0, column=2, sticky=NW)
    nf1 = Frame(r1, width=200, height=200).grid(row=2, column=2)
    t = Text(nf1)
    for x in range(4):
        t.insert(END, data1[x])
        t.insert(END, "\n")

    t.grid(row=2, column=0)


def show():

    r2.withdraw()

    s1.deiconify()
    sf=Frame(s1,width=100,height=100).grid(row=0,column=0)
    l4=Label(sf,text="Section").grid(row=0,column=0)
    se=Entry(sf,textvariable=sec).grid(row=0,column=1,columnspan=4)
    sb=Button(sf,text="Ok",command=sel).grid(row=1,column=1)



def login():
    usr=user.get()
    pas=password.get()
    connection = pymysql.connect(host='localhost', user='root', password='', db='login')
    cursor = connection.cursor()
    q=("select username from user where username=%s")


    q1 = ("select pass from user where pass=%s")
    if cursor.execute(q,usr) and cursor.execute(q1, pas):
        postlogin()
    else:
        print("Try again")

    connection.commit()
    connection.close()
root=Tk()
user=StringVar()
password=StringVar()
sec=StringVar()
name=StringVar()
regno=StringVar()
att=StringVar()
tp=Frame(root,width=800,height=600)
tp.pack()
l1=Label(tp,text="Username")
l1.grid(row=0,column=0)
e1=Entry(tp,textvariable=user).grid(row=0,column=1,columnspan=4)

l2=Label(tp,text="Password")
l2.grid(row=1,column=0)
e2=Entry(tp,textvariable=password).grid(row=1,column=1,columnspan=4)

submit=Button(tp,text="Login",command=login).grid(row=2,column=2)

#r2 is the windows for post login screen
r2 = Tk()
r2.withdraw()
# r3 is the windows for creating a new class
r3=Tk()
r3.withdraw()
s1=Tk()
s1.withdraw()
root.mainloop()

when I run it I'll get this traceback:

C:\Users\dimon\AppData\Local\Programs\Python\Python36-32\python.exe C:/Users/dimon/PycharmProjects/untitled/1.py
Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\dimon\AppData\Local\Programs\Python\Python36-32\lib\tkinter\__init__.py", line 1702, in __call__
    return self.func(*args)
  File "C:/Users/dimon/PycharmProjects/untitled/1.py", line 34, in inr
    cursor.execute(q,values)
  File "C:\Users\dimon\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pymysql\cursors.py", line 170, in execute
    result = self._query(query)
  File "C:\Users\dimon\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pymysql\cursors.py", line 328, in _query
    conn.query(q)
  File "C:\Users\dimon\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pymysql\connections.py", line 893, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "C:\Users\dimon\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pymysql\connections.py", line 1103, in _read_query_result
    result.read()
  File "C:\Users\dimon\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pymysql\connections.py", line 1396, in read
    first_packet = self.connection._read_packet()
  File "C:\Users\dimon\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pymysql\connections.py", line 1059, in _read_packet
    packet.check_error()
  File "C:\Users\dimon\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pymysql\connections.py", line 384, in check_error
    err.raise_mysql_exception(self._data)
  File "C:\Users\dimon\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'values('','','')' at line 1")

I've got stuck at this point, could anyone help?

Welcome to Stack overflow. Please add questions in a readable manner. Also add possible input and expected output so that we can debug easily. — Mufeed, Jun 30 '18 at 15:47
Your question proves effort and that's a good thing but you still need to improve it quite a bit. Make sure you provide enough context so users that will try to help are able to reproduce your issue... That's what a [mcve](https://stackoverflow.com/help/mcve) is all about. Said otherwise, If I try to run your script on my box will I be able to get the same traceback with the information you've provided on the question? If not, then it's not a mcve and you need to edit/improve the question. — BPL, Jun 30 '18 at 15:58
@dimonfrekp dimonfrekp I've edited your question to make it a little bit more clear for users that want to give it a shot but the provided code is still not a mcve, for instance, If I try to run your script I'll get [this](https://dl.dropboxusercontent.com/s/npniisyn7v7dzvi/python_2018-06-30_18-07-43.png) but If I read your question I don't know how to get the same error than you. So make sure the question will provide enough information for users to reproduce the errors reported in your question — BPL, Jun 30 '18 at 16:09
You've posted what looks like a lot of irrelevant code. Please try to reduce the code down to a [mcve]. — Bryan Oakley, Jun 30 '18 at 16:42
I have a database named login which has a table username and pass and another database named class which contains the sections table like (a,b,c..Like in college we have sections named after alpha s right.). When i run the code to select the students from a particular section by running the entire program i get the above errors but when i run the particular code it doesnt show any error — Harish P, Jul 05 '18 at 05:15

score 1 · Answer 1 · answered Jun 30 '18 at 16:16

1

Ok. I'm going to give a shot at an answer. Your code depends on a database that no one here has access to so we can't help a whole lot, but the error you're getting is this:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'values('','','')' at line 1

This indicates that values=[stu,re1,at] is set to ['', '', ''] which evidently is incorrect. You will need to dig in to why or how stu, re1 and at are getting set to the empty string.

answered Jun 30 '18 at 16:16

TheDavidFactor

1,647
2
19
18

I have a database named login which has a table username and pass and another database named class which contains the sections table like (a,b,c..Like in college we have sections named after alpha s right.). When i run the code to select the students from a particular section by running the entire program i get the above errors but when i run the particular code it doesnt show any error – Harish P Jul 05 '18 at 05:16
It's a gigo (https://en.wikipedia.org/wiki/Garbage_in,_garbage_out) problem. It's not about the specific section of code, but about the data that's being provided to the code. at a most basic level, doing a bunch of prints of the various variables should help you narrow down where the bad data is coming from. – TheDavidFactor Jul 05 '18 at 16:18

pymysql.err.ProgrammingError when running tkinter+pymysql script

1 Answers1