Python mysql error

Question

I have these codes where i simply take some input and try to store these datas in the mysql database. The database is connected, however some error seem to occur while executing them. The program seems to work without using the function, so i'm assuming there's some problem in the function. I'm a python newbie, any help will be appreciated.

from tkinter import *
import mysql.connector
import mysql.connector.cursor


class Register():
def __init__(self,master):
    frame = Frame(master)
    frame.grid()

#MySql Connection
    self.host = 'localhost'
    self.database = 'python_mysql'
    self.user = 'root'
    self.password = 'sparsha'
    self.db = mysql.connector.connect(self.host,self.database,self.user,self.password)



  #Labels
    self.lbl1 = Label(frame,text = "Name")
    self.lbl3 = Label(frame,text = "Email")
    self.lbl4 = Label(frame,text = "Tolerance")

    self.lbl1.grid(row = 0, column =0, sticky = W)
    self.lbl3.grid(row = 2, column =0, sticky = W)
    self.lbl4.grid(row = 3, column =0, sticky = W)

   #Entry
    self.txt1 = Entry(frame)
    self.txt2 = Entry(frame)
    self.txt3 = Entry(frame)

    self.txt1.grid(row = 0, column =1)
    self.txt2.grid(row = 2, column =1)
    self.txt3.grid(row = 3, column =1)

    #Button
    self.btn1=Button(frame,text = "Submit",command=self.btn1submit)
    self.btn1.grid()

def btn1submit(self):
    print("Button clicked")
    user_data = ("INSERT INTO user_data(Name, Email, Tolerance) "
           "VALUES (%s, %s, %s)")
    value = (self.txt1.get(),self.txt2.get(),self.txt3.get())
    print(value)
    # Insert new data
    self.cursor = self.db.cursor()
    self.cursor.execute(user_data, value)
    self.db.commit()
    self.cursor.close()
    self.db.close()

root = Tk()
c = Register(root)
root.mainloop()

Upon execution, the errors are listed as below:

File "C:\Python34\lib\tkinter\__init__.py", line 1533, in __call__
return self.func(*args)
File "C:/Users/Sparsha/PycharmProjects/untitled/Graphical Password     Authentication/Register.py", line 50, in btn1submit
self.cursor = self.db.cursor()
File "C:\Python34\lib\site-packages\mysql\connector\connection.py", line 1383, in cursor
raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.

Answer 1

I tried your code with these improvements. It works flawlessly.

from tkinter import *
import mysql.connector

class Register(object):
    def __init__(self, master):


        frame = Frame(master)
        frame.grid()

    #MySql Connection
        self.host = 'localhost'
        self.database = 'python_mysql'
        self.user = 'root'
        self.password = 'sparsha'
        self.db = mysql.connector.connect(host=self.host,database=self.database, user=self.user, password =self.password)
        self.cursor = self.db.cursor()


      #Labels
        self.lbl1 = Label(frame,text = "Name")
        self.lbl3 = Label(frame,text = "Email")
        self.lbl4 = Label(frame,text = "Tolerance")

        self.lbl1.grid(row = 0, column =0, sticky = W)
        self.lbl3.grid(row = 2, column =0, sticky = W)
        self.lbl4.grid(row = 3, column =0, sticky = W)

       #Entry
        self.txt1 = Entry(frame)
        self.txt2 = Entry(frame)
        self.txt3 = Entry(frame)

        self.txt1.grid(row = 0, column =1)
        self.txt2.grid(row = 2, column =1)
        self.txt3.grid(row = 3, column =1)

        #Button
        self.btn1=Button(frame,text = "Submit",command=self.btn1submit)
        self.btn1.grid()



    def btn1submit(self):
        print("Button clicked")
        user_data = ("INSERT INTO user_data(Name, Email, Tolerance) "
           "VALUES (%s, %s, %s)")
        value = (self.txt1.get(),self.txt2.get(),self.txt3.get())
        print(value)
        # Insert new data

        self.cursor.execute(user_data, value)
        self.db.commit()
        self.cursor.close()
        self.db.close()
root = Tk()
c = Register(root)
root.mainloop()

Edit you also don't need the import mysql.connector.cursor

Answer 2

Try this

user_data = "INSERT INTO user_data(Name, Email, Tolerance) VALUES ({0}, {1}, {2})".format(self.txt1.get(),self.txt2.get(),self.txt3.get())

print(user_data)

Print and paste your query please

The execute should look like this

self.cursor.execute(user_data)

Something quite odd is happening do this for troubleshooting purposes

cursor = self.db.cursor()
cursor.execute("SELECT Name from user_data")
rows = cursor.fetchone()
print(rows)