Posts: 7
Threads: 2
Joined: Nov 2019
Hello, As the subject of this message implies, the following code:
#imports
from tkinter import *
from tkinter import messagebox as ms
import mysql.connector as mysql
mydb = mysql.connect(
host="localhost",
user="james",
passwd="simplepassword",
database="testdb"
)
#main Class
class main:
def __init__(self,master):
# Window
self.master = master
# Some Usefull variables
self.username = StringVar()
self.password = StringVar()
self.n_username = StringVar()
self.n_password = StringVar()
#Create Widgets
self.widgets()
#Login Function
def login(self):
#Establish Connection
cursor = mydb.cursor()
#Find user If there is any take proper action
find_user = ("SELECT * FROM user WHERE username = ? and password = ?")
#find_user = ('SELECT username = ? and password = ? FROM testdb.user')
cursor.execute("SELECT [(self.username.get()),(self.password.get())]")
result = cursor.fetchall()
if result:
self.logf.pack_forget()
self.head['text'] = self.username.get() + '\n Loged In'
self.head['pady'] = 150
else:
ms.showerror('Oops!','Username Not Found.')
def new_user(self):
#Establish Connection
cursor = mydb.cursor()
#Find Existing username if any take proper action
find_user = ('SELECT * FROM user WHERE username = ?')
cursor.execute(find_user,[(self.username.get())])
if cursor.fetchall():
ms.showerror('Error!','Username Taken Try a Diffrent One.')
else:
ms.showinfo('Success!','Account Created!')
self.log()
#Create New Account
insert = 'INSERT INTO user(username,password) VALUES(?,?)'
cursor.execute(insert,[(self.n_username.get()),(self.n_password.get())])
cursor.commit()
#Frame Packing Methords
def log(self):
self.username.set('')
self.password.set('')
self.crf.pack_forget()
self.head['text'] = 'LOGIN'
self.logf.pack()
def cr(self):
self.n_username.set('')
self.n_password.set('')
self.logf.pack_forget()
self.head['text'] = 'Create Account'
self.crf.pack()
#Draw Widgets
def widgets(self):
self.head = Label(self.master,text = 'LOGIN',font = ('',35),pady = 10)
self.head.pack()
self.logf = Frame(self.master,padx =10,pady = 10)
Label(self.logf,text = 'Username: ',font = ('',20),pady=5,padx=5).grid(sticky = W)
Entry(self.logf,textvariable = self.username,bd = 5,font = ('',15)).grid(row=0,column=1)
Label(self.logf,text = 'Password: ',font = ('',20),pady=5,padx=5).grid(sticky = W)
Entry(self.logf,textvariable = self.password,bd = 5,font = ('',15),show = '*').grid(row=1,column=1)
Button(self.logf,text = ' Login ',bd = 3 ,font = ('',15),padx=5,pady=5,command=self.login).grid()
Button(self.logf,text = ' Create Account ',bd = 3 ,font = ('',15),padx=5,pady=5,command=self.cr).grid(row=2,column=1)
self.logf.pack()
self.crf = Frame(self.master,padx =10,pady = 10)
Label(self.crf,text = 'Username: ',font = ('',20),pady=5,padx=5).grid(sticky = W)
Entry(self.crf,textvariable = self.n_username,bd = 5,font = ('',15)).grid(row=0,column=1)
Label(self.crf,text = 'Password: ',font = ('',20),pady=5,padx=5).grid(sticky = W)
Entry(self.crf,textvariable = self.n_password,bd = 5,font = ('',15),show = '*').grid(row=1,column=1)
Button(self.crf,text = 'Create Account',bd = 3 ,font = ('',15),padx=5,pady=5,command=self.new_user).grid()
Button(self.crf,text = 'Go to Login',bd = 3 ,font = ('',15),padx=5,pady=5,command=self.log).grid(row=2,column=1)
#create window and application object
root = Tk()
root.title("Login Form")
main(root)
root.mainloop()Generates: mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[(self.username.get()),(self.password.get())]' at line 1
after I enter username & password
What am I missing here ?
I'm using Visual Studio Code 1.40.1 on a windows 10 with mysql server 8.0 along with Python 3.8 installed.
Posts: 8,090
Threads: 154
Joined: Sep 2016
Nov-22-2019, 11:21 AM
(This post was last modified: Nov-22-2019, 11:22 AM by buran.)
first thing that is not OK
cursor.execute("SELECT [(self.username.get()),(self.password.get())]")
Posts: 7
Threads: 2
Joined: Nov 2019
I understand that. However, when trying to create a new user, I get:
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement error
Which makes me think that either the connector is not doing its job or incorrect syntax is used.
Posts: 8,090
Threads: 154
Joined: Sep 2016
Nov-22-2019, 03:14 PM
(This post was last modified: Nov-22-2019, 03:15 PM by buran.)
I would try to replace lines 54-55 with
insert = 'INSERT INTO user (username, password) VALUES (%s, %s)'
cursor.execute(insert, (self.n_username.get(), self.n_password.get())) https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html
Posts: 7
Threads: 2
Joined: Nov 2019
Then I get: mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1
Posts: 8,090
Threads: 154
Joined: Sep 2016
Nov-22-2019, 05:27 PM
(This post was last modified: Nov-22-2019, 05:28 PM by buran.)
please, post full code and explain what you do when you get the error
You are using nonprepared statements, so you should not use qmark parametrization, i.e. replace ? placeholders with %s.
quote from the docs:
Quote:The parameters found in the tuple or dictionary params are bound to the variables in the operation. Specify variables using %s or %(name)s parameter style (that is, using format or pyformat style).
OR alternatively make your statements prepared:
Quote:Prepared statements executed with MySQLCursorPrepared can use the format (%s) or qmark (?) parameterization style. This differs from nonprepared statements executed with MySQLCursor, which can use the format or pyformat parameterization style.
Posts: 7
Threads: 2
Joined: Nov 2019
Revised code is as follows:
#imports
#don't forget to dos>pip install mysql-connector-python
from tkinter import *
from tkinter import messagebox as ms
import mysql.connector as mysql
mydb = mysql.connect(
host="localhost",
user="goktan",
passwd="nopassword64",
database="testdb"
)
#main Class
class main:
def __init__(self,master):
# Window
self.master = master
# Some Usefull variables
self.username = StringVar()
self.password = StringVar()
self.n_username = StringVar()
self.n_password = StringVar()
#Create Widgets
self.widgets()
#Login Function
def login(self):
#Establish Connection
cursor = mydb.cursor()
#Find user If there is any take proper action
find_user = ("SELECT * FROM user WHERE username = ? and password = ?")
#find_user = ('SELECT username = ? and password = ? FROM testdb.user')
adi = (self.username.get())
sifre = (self.password.get())
cursor.execute("SELECT (self.username.get()),(self.password.get())]")
result = cursor.fetchall()
if result:
self.logf.pack_forget()
self.head['text'] = self.username.get() + '\n Loged In'
self.head['pady'] = 150
else:
ms.showerror('Oops!','Username Not Found.')
def new_user(self):
#Establish Connection
cursor = mydb.cursor()
#Find Existing username if any take proper action
find_user = ("SELECT * FROM user WHERE username = ?")
cursor.execute(find_user,(self.username.get()))
if cursor.fetchall():
ms.showerror('Error!','Username Taken Try a Diffrent One.')
else:
ms.showinfo('Success!','Account Created!')
self.log()
#Create New Account
insert = 'INSERT INTO user(username, password) VALUES(%s, %s)'
cursor.execute(insert, (self.n_username.get(), self.n_password.get()))
cursor.commit()
#Frame Packing Methords
def log(self):
self.username.set('')
self.password.set('')
self.crf.pack_forget()
self.head['text'] = 'LOGIN'
self.logf.pack()
def cr(self):
self.n_username.set('')
self.n_password.set('')
self.logf.pack_forget()
self.head['text'] = 'Create Account'
self.crf.pack()
#Draw Widgets
def widgets(self):
self.head = Label(self.master,text = 'LOGIN',font = ('',35),pady = 10)
self.head.pack()
self.logf = Frame(self.master,padx =10,pady = 10)
Label(self.logf,text = 'Username: ',font = ('',20),pady=5,padx=5).grid(sticky = W)
Entry(self.logf,textvariable = self.username,bd = 5,font = ('',15)).grid(row=0,column=1)
Label(self.logf,text = 'Password: ',font = ('',20),pady=5,padx=5).grid(sticky = W)
Entry(self.logf,textvariable = self.password,bd = 5,font = ('',15),show = '*').grid(row=1,column=1)
Button(self.logf,text = ' Login ',bd = 3 ,font = ('',15),padx=5,pady=5,command=self.login).grid()
Button(self.logf,text = ' Create Account ',bd = 3 ,font = ('',15),padx=5,pady=5,command=self.cr).grid(row=2,column=1)
self.logf.pack()
self.crf = Frame(self.master,padx =10,pady = 10)
Label(self.crf,text = 'Username: ',font = ('',20),pady=5,padx=5).grid(sticky = W)
Entry(self.crf,textvariable = self.n_username,bd = 5,font = ('',15)).grid(row=0,column=1)
Label(self.crf,text = 'Password: ',font = ('',20),pady=5,padx=5).grid(sticky = W)
Entry(self.crf,textvariable = self.n_password,bd = 5,font = ('',15),show = '*').grid(row=1,column=1)
Button(self.crf,text = 'Create Account',bd = 3 ,font = ('',15),padx=5,pady=5,command=self.new_user).grid()
Button(self.crf,text = 'Go to Login',bd = 3 ,font = ('',15),padx=5,pady=5,command=self.log).grid(row=2,column=1)
#create window and application object
root = Tk()
root.title("Login Form")
main(root)
root.mainloop()When I run the program, I'm greated with a login form. I select 'Create Account' and after filling-in the form, I get the following errors:
Exception in Tkinter callback
Traceback (most recent call last):
File "C:\Python\Python38\lib\tkinter\__init__.py", line 1883, in __call__
return self.func(*args)
File "c:\Users\nadir\Desktop\python\login-mysql.py", line 51, in new_user
cursor.execute(find_user,(self.username.get()))
File "C:\Python\Python38\lib\site-packages\mysql\connector\cursor.py", line 569, in execute
self._handle_result(self._connection.cmd_query(stmt))
File "C:\Python\Python38\lib\site-packages\mysql\connector\connection.py", line 590, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
File "C:\Python\Python38\lib\site-packages\mysql\connector\connection.py", line 478, in _handle_result
raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1
Posts: 8,090
Threads: 154
Joined: Sep 2016
Nov-22-2019, 06:02 PM
(This post was last modified: Nov-22-2019, 06:03 PM by buran.)
What do you not understand from my previous post that you need to replace the placeholders or make the statements prepared?
Also be careful about spaces in the sql statements, e.g. in this one
'INSERT INTO user(username, password) VALUES(%s, %s)'
there should be space between user and ( and between VALUES and (, as in my example
And you still didn't fix the statement on line 36
Posts: 7
Threads: 2
Joined: Nov 2019
Apologies as English is my Second language.
I revised the code from what I understood only to get the same error as follows:
#imports
#don't forget to dos>pip install mysql-connector-python
from tkinter import *
from tkinter import messagebox as ms
import mysql.connector as mysql
mydb = mysql.connect(
host="localhost",
user="goktan",
passwd="nopassword64",
database="testdb"
)
#main Class
class main:
def __init__(self,master):
# Window
self.master = master
# Some Usefull variables
self.username = StringVar()
self.password = StringVar()
self.n_username = StringVar()
self.n_password = StringVar()
#Create Widgets
self.widgets()
#Login Function
def login(self):
#Establish Connection
cursor = mydb.cursor()
#Find user If there is any take proper action
find_user = ("SELECT * FROM user WHERE username = %s and password = %s")
cursor.execute("SELECT (self.username.get()), (self.password.get())]")
result = cursor.fetchall()
if result:
self.logf.pack_forget()
self.head['text'] = self.username.get() + '\n Loged In'
self.head['pady'] = 150
else:
ms.showerror('Oops!','Username Not Found.')
def new_user(self):
#Establish Connection
cursor = mydb.cursor()
#Find Existing username if any take proper action
find_user = ("SELECT * FROM user WHERE username = ?")
cursor.execute(find_user, (self.username.get()))
if cursor.fetchall():
ms.showerror('Error!','Username Taken Try a Diffrent One.')
else:
ms.showinfo('Success!','Account Created!')
self.log()
#Create New Account
insert = 'INSERT INTO user (username, password) VALUES (%s, %s)'
cursor.execute(insert, (self.n_username.get(), self.n_password.get()))
cursor.commit()
#Frame Packing Methords
def log(self):
self.username.set('')
self.password.set('')
self.crf.pack_forget()
self.head['text'] = 'LOGIN'
self.logf.pack()
def cr(self):
self.n_username.set('')
self.n_password.set('')
self.logf.pack_forget()
self.head['text'] = 'Create Account'
self.crf.pack()
#Draw Widgets
def widgets(self):
self.head = Label(self.master,text = 'LOGIN',font = ('',35),pady = 10)
self.head.pack()
self.logf = Frame(self.master,padx =10,pady = 10)
Label(self.logf,text = 'Username: ',font = ('',20),pady=5,padx=5).grid(sticky = W)
Entry(self.logf,textvariable = self.username,bd = 5,font = ('',15)).grid(row=0,column=1)
Label(self.logf,text = 'Password: ',font = ('',20),pady=5,padx=5).grid(sticky = W)
Entry(self.logf,textvariable = self.password,bd = 5,font = ('',15),show = '*').grid(row=1,column=1)
Button(self.logf,text = ' Login ',bd = 3 ,font = ('',15),padx=5,pady=5,command=self.login).grid()
Button(self.logf,text = ' Create Account ',bd = 3 ,font = ('',15),padx=5,pady=5,command=self.cr).grid(row=2,column=1)
self.logf.pack()
self.crf = Frame(self.master,padx =10,pady = 10)
Label(self.crf,text = 'Username: ',font = ('',20),pady=5,padx=5).grid(sticky = W)
Entry(self.crf,textvariable = self.n_username,bd = 5,font = ('',15)).grid(row=0,column=1)
Label(self.crf,text = 'Password: ',font = ('',20),pady=5,padx=5).grid(sticky = W)
Entry(self.crf,textvariable = self.n_password,bd = 5,font = ('',15),show = '*').grid(row=1,column=1)
Button(self.crf,text = 'Create Account',bd = 3 ,font = ('',15),padx=5,pady=5,command=self.new_user).grid()
Button(self.crf,text = 'Go to Login',bd = 3 ,font = ('',15),padx=5,pady=5,command=self.log).grid(row=2,column=1)
#create window and application object
root = Tk()
root.title("Login Form")
main(root)
root.mainloop()
Posts: 8,090
Threads: 154
Joined: Sep 2016
you still have ? on line 47
and line 33 statement is still not fixed. You execute it, not the one on line 32!
|
