Migrating to Mysql from SQlite

atari400 · Nov-22-2019, 10:57 AM

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.

**buran** · (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())]")

atari400 · Nov-22-2019, 02:48 PM

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.

**buran** · (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

atari400 · Nov-22-2019, 04:58 PM

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

**buran** · (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.

atari400 · Nov-22-2019, 05:57 PM

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

**buran** · (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

atari400 · Nov-22-2019, 06:23 PM

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()

**buran** · Nov-22-2019, 06:49 PM

you still have ? on line 47
and line 33 statement is still not fixed. You execute it, not the one on line 32!

Possibly Related Threads…
Thread		Author	Replies	Views	Last Post
	Need to change connection and queries from SQLite to MySQL	Bxapocalypse1	2	1,300	Oct-20-2025, 11:35 PM Last Post: Pedroski55
	Migrating of python2 script to python3	zuri	7	7,504	Oct-05-2023, 02:40 PM Last Post: snippsat
	Mysql and mysql.connector error	lostintime	2	2,562	Oct-03-2023, 10:25 PM Last Post: lostintime
	Migrating data from oracle into postgres	python_student	1	3,949	Feb-10-2022, 09:16 PM Last Post: buran
	Mysql error message: Lost connection to MySQL server during query	tomtom	6	24,386	Feb-09-2022, 09:55 AM Last Post: ibreeden

Migrating to Mysql from SQlite

User Panel Messages

Announcements