Insert data in MYSQL using Python [Tkinter Library]

Create Registration form and store data in MySQL Database.

Hello friends how are you, today in this blog i will teach you how you can insert data into MySQL Database using Python and Tkinter GUI Library. This tutorial will help you definitely if you wan to create a window application in Python or if you want to make any kind of projects or Software Applications. I am using PyCharm IDE and now i am going to describe everything step by step so just go through this post to get complete knowledge.

Registration form is one of the most used Form in Window or GUI Applications, It helps user to Register by using  name, contact, email, etc actually it depends upon the type of Registration.

Here in this post i am using name, contact, email, gender, city and state of user for Registration.

Here i have created a Registration form using Tkinter GUI library in which i have used name, contact, email, gender, city and state of user for Registration.

Now go through  this post step by step to learn completely.

Create a Database

Open your MySQL and create a Database "PythonData" if you want to create using query then copy the code below ,following is the query of creating database in MySQL 

CREATE DATABASE PythonData

Create a Table

Now create a Table[REG] inside  Database[PythonData], following is the query to create a table in MySQL database

CREATE TABLE REG (
    RID int NOT NULL AUTO_INCREMENT,
    NAME varchar(255) NOT NULL,
    CONTACT varchar(255),
    EMAIL varchar(255),
    GENDER varchar(255),
    CITY varchar(255),
    STATE varchar(255),
    PRIMARY KEY (RID)
);

After doing these we have a database and a Table without any data like below 

Syntax of Inserting data into table

Following is the syntax of inserting data into table

INSERT INTO TABLE_NAME (column1, column2,column3,...columnN)
VALUES (value1, value2, value3,...valueN);

Install mysql.connector Package or Library

If you want to manipulate data in MySQL using Python then this library is compulsory without this library python can't communicate with MySQL.

If you write and execute your code in command prompt or Python IDLE then write the below code in command prompt to install this library

pip install mysql-connector

below is the screenshot of command prompt

But i will suggest all of you to use Pycharm IDE for python because according to me it is the best IDE for Python.Now i am going to tell you how you can install this library using Pycharm

To install go to File and click on Setting

After clicking on Setting you will get a screen like below

Here in above screen you have to click on Project Interpreter and after clicking you will get a screen like below

Here in above screen you have to click on plus[+] icon as indicated by arrow, after clicking on it you will get a screen like below

here in above screen first search "mysql-connector" in search box then make sure "mysql-connector" is selected as indicated above and finally click on "Install Package" to add this library in your project, after that wait for some seconds [It depends upon your internet speed] it will display a message below.

Write Python Code

Here is the complete code for inserting data in MySQL using Python just copy and paste into your editor

from tkinter import *
from  tkinter import  ttk
#importing connection
import  mysql.connector
#establishing connection
conn = mysql.connector.connect(
   user='root', password='', host='localhost', database='pythondata')
"""
here in my case there is no password so password='' is blank
root is username
localhost is server or host name 
you can also use 127.0.0.1 in place of local host
pythondata is the name of Database
"""
#defining register function
def register():
    #getting form data
    name1=name.get()
    con1=contact.get()
    email1=email.get()
    gen1=gender.get()
    city1=city.get()
    state1=state.get()
    #applying empty validation
    if name1=='' or con1==''or email1=='' or gen1==''or city1==''or state1=='':
        message.set("fill the empty field!!!")
    else:
       # Creating a cursor object using the cursor() method
       cursor = conn.cursor()
       # Preparing SQL query to INSERT a record into the database.
       insert_stmt = (
           "INSERT INTO REG(NAME, CONTACT, EMAIL, GENDER, CITY, STATE)"
           "VALUES (%s, %s, %s, %s, %s, %s)"
       )
       if gen1==1:
        data = (name1, con1,email1,"Male",city1,state1)
       else:
        data = (name1, con1, email1, "Female", city1, state1)
       try:
           #executing the sql command
           cursor.execute(insert_stmt,data)
           #commit changes in database
           conn.commit()
       except:
           conn.rollback()
       message.set("Stored successfully")

#defining Registrationform function
def Registrationform():
    global reg_screen
    reg_screen = Tk()
    #Setting title of screen
    reg_screen.title("Registration Form")
    #setting height and width of screen
    reg_screen.geometry("350x400")
    #declaring variable
    global  message;
    global name
    global contact
    global email
    global gender
    global city
    global state
    name = StringVar()
    contact = StringVar()
    email=StringVar()
    gender=IntVar()
    city=StringVar()
    state=StringVar()
    message=StringVar()
    #Creating layout of Registration form
    Label(reg_screen,width="300", text="Please enter details below", bg="orange",fg="white").pack()
    #name Label
    Label(reg_screen, text="Name * ").place(x=20,y=40)
    #name textbox
    Entry(reg_screen, textvariable=name).place(x=90,y=42)
    #contact Label
    Label(reg_screen, text="Contact * ").place(x=20,y=80)
    #contact textbox
    Entry(reg_screen, textvariable=contact).place(x=90,y=82)

    # email Label
    Label(reg_screen, text="Email * ").place(x=20, y=120)
    # email textbox
    Entry(reg_screen, textvariable=email).place(x=90, y=122)

    # gender Label
    Label(reg_screen, text="Gender * ").place(x=20, y=160)
    # gender radiobutton
    Radiobutton(reg_screen,text="Male",variable=gender,value=1).place(x=90,y=162)
    Radiobutton(reg_screen, text="Female", variable=gender, value=2).place(x=150, y=162)

    # city Label
    Label(reg_screen, text="City * ").place(x=20, y=200)
    # city combobox
    monthchoosen = ttk.Combobox(reg_screen, width=27, textvariable=city)
    monthchoosen['values'] = (' Mumbai',
                              ' Bhopal',
                              ' Patna',
                              ' Indore',
                              ' Nagpur',
                              ' Motihari',
                              ' Pune',
                              ' Gwalior',
                              ' Jabalpur',)
    monthchoosen.current()
    monthchoosen.place(x=90,y=202)

    # state Label
    Label(reg_screen, text="State * ").place(x=20, y=240)
    # state combobox
    monthchoosen = ttk.Combobox(reg_screen, width=27, textvariable=state)
    monthchoosen['values'] = (' Madhya Pradesh',
                              ' Maharashtra',
                              ' Bihar',
                              ' Punjab',
                              ' Gujrat',
                              ' Rajsthan',)
    monthchoosen.current()
    monthchoosen.place(x=90, y=242)
    #Label for displaying login status[success/failed]
    Label(reg_screen, text="",textvariable=message).place(x=95,y=264)
    #Login button
    Button(reg_screen, text="Register", width=10, height=1, bg="orange",command=register).place(x=105,y=300)
    reg_screen.mainloop()
#calling function Registrationform
Registrationform()

when you run the code you will get screen like below

Registration Form with empty validation

I have applied a Empty Validation means if you left blank any field then you will get a message "fill the empty field!!!" after pressing button.

Registration Form with stored successfully

When you will fill all the fields and click on Register button then form data will be stored in Table[Reg] in Database[PythonData]. 

When you click on Register button you will get a message "Stored Successfully" and data will be stored in MySQL Database.

Registration Students Record

Here i am providing the screenshot of file where all the records of registered students are present.

Widgets used to create Registration Form

1.Label
2.TextBox or EditText(Entry)
3.Radiobutton
4.Combobox
5.Button

I hope this post will help you definitely, if you have any doubt then let me know by comment i will definitely fix it.

If you like my post then please share it with your friends. Thanks😊