Login Form in Python GUI with MYSQL and MSSQL Connection

In python, if we want to Design Login Form with database authentication with MYSQL or MSSQL then we have to follow the following process with step by step guide. 

Here we will learn How to Design Login Form in Python GUI with MYSQL connection in Python or SQL Server Connection in Python. 

First we have to Create table For authentication with following field Name:

Login_tbl
usrname	passwrd
sko	tech

Same type of table we create in MSSQL. 

After that we will use following tkinter widget:

Name	Tkinter Wiidget
Bannerlabel	Label
UserLabel	Label
PassLabel	Label
UserTxt	Entry
PassTxt	Entry
LoginBtn	Button

After that we will proceed with following points:

1. Login Form Design in Python GUI with MYSQL Connection
2. Login Form Design in Python GUI with MSSQL Connection

Now we will first Start with MYSQL connection then MS SQL Server Connection.

(1). Login Form Design in Python GUI with MYSQL Connection :

Now, first we will import ‘mysql.connector’ for connection. Then write code for establish connection with MYSQl after that write code for query like following way:

import mysql.connector as mysqlconnector
import tkinter as mytk
from tkinter import *
import tkinter.messagebox as mymessagebox

MyLoginForm = mytk.Tk()
MyLoginForm.title('Login Form with MYSQL Connection')
#Set Form Size
MyLoginForm.geometry("380x200")

def ClicktoLogin():
    #MYSQL ConnectionString
    mydb = mysqlconnector.connect(host="xxx.xxx.xxx.xx", user="sko", password="mypassword", database="skotechlearn")
    mycursor = mydb.cursor(dictionary=True)
    mycursor.execute("SELECT * FROM login_tbl where usrname = '"+ UserTxt.get() +"' and passwrd = '"+ PassTxt.get() +"';")
    myresult = mycursor.fetchone()
    if myresult==None:
       mymessagebox.showerror("Error", "Invalid User Name And Password")

    else:
       mymessagebox.showinfo("Success", "Successfully Login")
            
    mydb.close()
    mycursor.close()

# Set Tkinter Widget Size Location and Style
Bannerlabel = Label(MyLoginForm, text = "Login Form......", width=40, bg= 'yellow')
Bannerlabel.place(x=20, y=20)

UserLabel = Label(MyLoginForm, text = "User Name:", width=10)
UserLabel.place(x=20, y=60)

UserTxt = Entry(MyLoginForm,  width=27, relief="flat")
UserTxt.place(x=120, y=60)

#Set Focus on User Entrybox in Tkinter
UserTxt.focus()

PassLabel = Label(MyLoginForm, text = "Password :", width=10)
PassLabel.place(x=20, y=90)

PassTxt = Entry(MyLoginForm,  width=27, relief="flat")
PassTxt.place(x=120, y=90)

#Set Password Char in Entry Widget
PassTxt.config(show="*");

LoginBtn = Button(MyLoginForm, text ="Login", command = ClicktoLogin, relief="groove", fg='blue')
LoginBtn.place(x=280, y=140)

MyLoginForm.configure(background='#54596d')
MyLoginForm.mainloop()

When we execute this code. We will see the output looks like bellow.

 

As you can see we have changed Form, Label color for better look. After successful Login there will be a message displayed like above figure.
Now, Lets come to another point with MSSQL Connection in Python.

(2). Login Form Design in Python GUI with MSSQL Connection :

For MSSQL Connection first we install pymssql.

pip install pymssql

or

pip3 install pymssql

After that we will import pymssql in python code.

 
For Import and for MSSQL Connection String in Python, we will write following code.

 
MSSQL Server Connection in Python

Connection String Code:

# pymssql.connect(MSSQLServerIP, UserName, Password, Database)
pymssql.connect("xxx.xxx.xxx.xx", "myusername", "myPassword", "MyDatabase")

Let’s see how can we connect MSSQL Server with Python Tkinter Login Form?

import pymssql as MSSQLCnn
import tkinter as mytk
from tkinter import *
import tkinter.messagebox as mymessagebox

MyLoginForm = mytk.Tk()
MyLoginForm.title('Login Form with MSSQL Server Connection')
MyLoginForm.geometry("380x200")

def ClicktoLogin():
    #MSSQL Server ConnectionString
    MSSQLdb = MSSQLCnn.connect("xxx.xxx.xxx.xx", "SQLServerUser", "SQlServerPassword", "SQLServerDatabase")
    mySQLcursor = MSSQLdb.cursor()
    mySQLcursor.execute("SELECT * FROM login_tbl where usrname = '"+ UserTxt.get() +"' and passwrd = '"+ PassTxt.get() +"';")
    mySQLresult = mySQLcursor.fetchone()
    if mySQLresult==None:
       mymessagebox.showerror("Error", "Invalid User Name And Password")

    else:
       mymessagebox.showinfo("Success", "Successfully Login")
            
    MSSQLdb.close()
    mySQLcursor.close()

Bannerlabel = Label(MyLoginForm, text = "MSSQL Server Login Form......", width=40, bg= '#81907E', fg='white')
Bannerlabel.place(x=20, y=20)

UserLabel = Label(MyLoginForm, text = "User Name:", width=10, bg='#FFE6D8')
UserLabel.place(x=20, y=60)

UserTxt = Entry(MyLoginForm,  width=27, relief="flat")
UserTxt.place(x=120, y=60)

#Focus of User Entrybox in Tkinter
UserTxt.focus()

PassLabel = Label(MyLoginForm, text = "Password :", width=10, bg='#FFE6D8')
PassLabel.place(x=20, y=90)

PassTxt = Entry(MyLoginForm,  width=27, relief="flat")
PassTxt.place(x=120, y=90)

#Set Entry box Password Char in Tkinter
PassTxt.config(show="*");

LoginBtn = Button(MyLoginForm, text ="Login", command = ClicktoLogin, relief="groove", fg='blue')
LoginBtn.place(x=280, y=140)

MyLoginForm.configure(background='#FFE6D8')
MyLoginForm.mainloop()

After the execution of this code in python, the following output will present.

This way you can easily Create or Design Login Form in Python GUI with Tkinter and Connect Login Form with MSSQL in Python Or Connect Login Form with MYSQL in Python.

MySQL Connection in Python with MYSQL Select Query in Python

When we want to connect MYSQL Database with application then we have to first consider about MYSQL Connection Driver and MySQL ConnectionString. So here we will describe MYSQL Database Connection in Python or MYSQL connection in Python with database in three ways. 

1. Using Pyodbc in MYSQL Connection
2. Using mysql.connector for MYSQL Connection
3. Using pymysql for MYSQL Connection

Fist thing we have to know that if we want to connect any type of Database organization, then we have to use Driver from particular database organization like MYSQL. So, Lets Start from pyodbc in python.

(1). Using Pyodbc in MYSQL Connection :

For using pyodbc, you have to install pyodbc through pip. Now, lets know the following connection string of pyodbc.

pyodbc.connect("DRIVER={Driver_Name}; User=Server_User; Password=server_Password; Server=localhost/server IP; Database=DatabaseName;")

So, Write following code to show database table’s record in following way:

import pyodbc 

mysqlcn = pyodbc.connect("DRIVER={MySQL ODBC 8.0 ANSI Driver}; User=sko; Password=xxxxxx; Server=xxx.xxx.xxx.xx; Database=mydatabase; ")
dbcursor = mysqlcn.cursor() 
dbcursor.execute("SELECT * FROM TestTable") 
myfield = dbcursor.fetchone() 
# Loop to fetch one by one data.
while myfield:
      # Print partuculat column data from table
print (myfield[0]) 
myfield = dbcursor.fetchone()

#Now Close cursor and connection
dbcursor.close()
mysqlcn.close() 

In this code we use “MySQL ODBC 8.0 ANSI Driver” in pyodbc. 

This will show following output:

Show MySQL Data in Listbox in Python

If you want to show data or you can use MYSQL Select Query in Python to show data in Listbox then following code will be help in you coding.

Show Database Record in Listbox:

import tkinter as mytk
from tkinter import *
import pyodbc 

MytkForm = mytk.Tk()
MytkForm.title('MySQL with pyodbc')
MytkForm.geometry("250x100")

# Create Listbox
MyTklist = mytk.Listbox(MytkForm)
#Clear Listbox
MyTklist.delete(0,END)
mysqlcn = pyodbc.connect("DRIVER={MySQL ODBC 8.0 ANSI Driver}; User=sko; Password=xxxxxx; Server=xxx.xxx.xxx.xx; Database=mydatabase;")
dbcursor = mysqlcn.cursor() 
#MySQL Select Query in Python
dbcursor.execute("SELECT * FROM TestTable") 
myfield = dbcursor.fetchone() 
while myfield:
    # Add Field data in Listbox
    MyTklist.insert(END,myfield[0]) 
    myfield = dbcursor.fetchone()

dbcursor.close()
mysqlcn.close()
MyTklist.pack()

MytkForm.mainloop()

OUTPUT: 

(2). Using mysql.connector for MYSQL Connection

In this point we will learn how to use mysql.connector for database connection and also learn how to List MYSQL data through column name in python. 

Mysql.connector ConnectionString in Python

mysql.connector.connect(host="XXX.XXX.XXX.XX", user="Database_User", password="Database_Password", database="DatabaseName")

Now Lets, see how to show data?

import mysql.connector
#ConnectionString
mydb = mysql.connector.connect(host="XXX.XXX.XXX.XX", user="sko", password="mypassword", database="mydatabase")
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM TestTable")
myresult = mycursor.fetchall()
for mycol in myresult:
   # Get Second Column's row List
   print(mycol[1])

mydb.close()
mycursor.close() 

OUTPUT:

In this given output, you can see, we show only second column data through “mycol[1]” this code. 

MySQL Column Value show through Column Name in Python in Listbox: 

As you saw before, we are showing data through Column Number, but if we have more columns and want to show particular column value then we have to determine Column Name. 

So, through following way you can use to show column value through Column Name. You have to add following code in cursor method.

mycursor = mydb.cursor(dictionary=True)

Lets see with full details:

import mysql.connector
import tkinter as mytk
from tkinter import *

MytkForm2 = mytk.Tk()
MytkForm2.title('MySQL with mysql.connector')
MytkForm2.geometry("270x120")

MyListData= mytk.Listbox(MytkForm2)
mydb = mysql.connector.connect(host="XXX.XXX.XXX.XX", user="sko", password="mypassword", database="mydatabase")

#Fetch Particular Column Value through Column name with using dictionary
mycursor = mydb.cursor(dictionary=True)

mycursor.execute("SELECT * FROM TestTable")
myresult = mycursor.fetchall()
for MyColName in myresult:
    MyListData.insert(END, MyColName ["MyDetails"]) 
mydb.close()
mycursor.close() 
MyListData.pack()

MytkForm2.mainloop()

Now, This will Show output according to Column Name.

(3). Using Pymysql for MYSQL Connection

You can use another connector in python that's call pymysql. First you have to install pymysql. After that you have to use following connection string.

pymysql.connect(host="XXX.XXX.XXX.XX", user="MySQLUsername", password="MYSQLPassword", database="MYSQLDatabase")

Then write following code to show data in Python.

import pymysql

# database connectionstring
connmysql = pymysql.connect(host="XXX.XXX.XXX.XX", user="sko", password="mypassword", database="mydatabase")
cursordb = connmysql.cursor()

# execute your query through execute().
cursordb.execute("SELECT * FROM TestTable")

# get all row through fetchall().
data = cursordb.fetchall()
for colnm in data:
    print(colnm[0])

# close connection
connmysql.close()

OUTPUT:

Conclusion: You can use any one of these method. You will find some performance differences. But you will find exact result using any connector in python.

So, this way you can Connect MYSQL in Python with step by step process.