Fetch and Display data from MySQL using Python

Hello friends how are you, today in this blog i will teach you how you can fetch and display data from MySQL using Python. 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.

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

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

Example: Inserting data into table

Following is the query to insert data into table

INSERT INTO REG(NAME, CONTACT, EMAIL, GENDER, CITY, STATE) 
VALUES ('Tony', '9999925635', 'tony@gmail.com','Male', 'Nagpur', 'Maharashtra'),
 ('Sony', '8555596888', 'sony@gmail.com','Male', 'Motihari', 'Bihar'),
 ('Amisha', '8770351258', 'amisha@gmail.com','Female', 'Jabalpur', 'Madhya Pradesh'),
 ('Rocky', '9179876567', 'rocky@gmail.com','Male', 'Jabalpur', 'Madhya Pradesh')

The above query will insert 4 records simultaneously, after executing this query we have data like below in database

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 

1.Python code to display MySQL records in console

Here is the complete code to fetch data from MySQL and display in console

import mysql.connector
#connection to MySQL
my_connect = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="PythonData"
)
#end of connection 
my_conn = my_connect.cursor()
my_conn.execute("SELECT * FROM REG limit 0,10")
i=0
for user in my_conn:
    for j in range(len(user)):
        print(user[j],end=' ')
    i=i+1
    print()# line break at the end of each row

when you run the code you will get screen like below

2.Python code to display MySQL records using Tkinter

Here is the complete code to fetch data from MySQL and display in GUI window

import mysql.connector
import tkinter  as tk
from tkinter import *
record_window = tk.Tk()
#Setting title of screen
record_window.title("User Records")
#setting height and width of screen
record_window.geometry("660x350")
#connect to MySQl
my_connect = mysql.connector.connect(
  host="localhost",#server name
  user="root",#username
  password="",#password:in my case there is no password so i left blank
  database="PythonData"#database name
)
my_conn = my_connect.cursor()
#end of connection 
my_conn.execute("SELECT * FROM REG limit 0,10")
i=0
for student in my_conn:
    for j in range(len(student)):
        e = Entry(record_window, width=15, fg='blue')
        e.grid(row=i, column=j)
        e.insert(END, student[j])
    i=i+1
record_window.mainloop()

when you run the code you will get screen like below

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😊