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.

@YMartin I think that is just an indentation issue in the posting. Both "functions" are clearly intended to be methods of the class. @user - what happens if you get rid of the import mysql.connector.cursor line? — Alan Hoover
– Alan Hoover, Commented Jul 31, 2015 at 13:45
@AlanHoover removing that line actually gave the very same error I had posted earlier. No changes — user5177957
– user5177957, Commented Jul 31, 2015 at 13:49
I don't know if it will help, but did you try working with some other MySQL database driver like MySQLdb ? — FunkySayu
– FunkySayu, Commented Jul 31, 2015 at 14:10
do you intend to close the connection after the button is pressed once?? — Digvijayad
– Digvijayad, Commented Jul 31, 2015 at 14:20

Digvijayad · Accepted Answer · 2015-07-31 15:08:49Z

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

edited Jul 31, 2015 at 15:08

answered Jul 31, 2015 at 15:01

Digvijayad

5346 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

FirebladeDan Over a year ago

Ah! creation of cursor in class creation. Good stuff!

Digvijayad Over a year ago

aye aye... that and the use of keyword arguments. Was getting error without them.

FirebladeDan Over a year ago

What keyword arguments do you speak of? I'm just curious. host=self.host? OMG! that's right. I'm curious how he was ever able to connect tho! Weird.

Digvijayad Over a year ago

yes, without them I was getting this error. TypeError: __init__() takes 1 positional argument but 5 were given . I think they are called keyword arguments, not sure about the exact term.

user5177957 Over a year ago

wow, thanks! This actually helped. Also, @FirebladeDan thanks for your input on this!

|

FirebladeDan · Accepted Answer · 2015-07-31 15:01:02Z

0

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)

edited Jul 31, 2015 at 15:01

answered Jul 31, 2015 at 14:32

FirebladeDan

1,0696 silver badges14 bronze badges

4 Comments

user5177957 Over a year ago

Program is taking the input, but is not storing it into the database.

user5177957 Over a year ago

Database is connected. I tried the same program without classes and functions and it worked like a charm. It took my input and then stored it in the database. So, no problem with the connection.

FirebladeDan Over a year ago

Ok that's good. can you do print(self.txt1.get()). What is it showing?

user5177957 Over a year ago

print(self.txt1.get()) displays the first entry value. I don't understand the problem here. It keeps displaying the same error with self.cursor = self.db.cursor() and if i remove this line and the three lines below it, no error is displayed.

Collectives™ on Stack Overflow

Python mysql error

2 Answers 2

6 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related