Subscribe Us

How to fetch data from SQLite database in python tkinter | Python SQLite3 Tutorial

Anil Singhania December 30, 2020

Hello friends how are you, Today in this post "How to fetch data from SQLite database in python tkinter" i am going to teach you how you can fetch data from  SQLite database and populate into Tkinter GUI interface. If you are a Computer Science students and want to create GUI based application using Database then this post will help you definitely. if you don't know how to use SQLite in Python and How to insert data into python first visit the below link.

Now i am going to explain everything step by step

Step 1:Create Database

Create any python file and type the following  code to create SQLite database. when you will run this code a database file with name student will be created in the current directory where your python file exists.

import  sqlite3
conn=sqlite3.connect("student.db")
print("Database created successfully")

Step 2:Create Table

Just type the following code into your python file and execute this code to create table inside your database.

import  sqlite3
conn=sqlite3.connect("student.db")
print("Database Opened successfully")
conn.execute("""
CREATE TABLE STUD_REGISTRATION(
STU_ID INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL ,
STU_NAME TEXT NOT NULL, 
STU_CONTACT TEXT,
STU_EMAIL TEXT,
STU_ROLLNO TEXT NOT NULL,
STU_BRANCH TEXT NOT NULL)
""")
print ("Table STUD_REGISTRATION created successfully")
"""
###Output###
Database Opened successfully
Table STUD_REGISTRATION created successfully
"""

Here execute() is a predefined function which is used to execute any query of SQLite. If you want to see your table in SQLite then you can use DB Browser.

Step 3:Insert data into table

Now i am going to insert data into table. By using SQL query we can insert data into tables. Following is the code to insert data into table.

import  sqlite3
conn=sqlite3.connect("student.db")
print("Database Opened successfully")

conn.execute("INSERT INTO STUD_REGISTRATION (STU_NAME,STU_CONTACT,STU_EMAIL,STU_ROLLNO,STU_BRANCH) \
      VALUES ('Raju', '9179876567', 'raju@gmail.com','MCA204', 'CA')");

conn.execute("INSERT INTO STUD_REGISTRATION (STU_ID,STU_NAME,STU_CONTACT,STU_EMAIL,STU_ROLLNO,STU_BRANCH) \
      VALUES ('Nancy', '9179785695', 'nancy@gmail.com','MCA225', 'CA')");

conn.commit()
print ("Records inserted successfully")
conn.close()
"""
###Output###
Database Opened successfully
Records inserted successfully
"""

Step 4:Select and Display data from table

Now i am going to create a GUI interface that will access the data from SQLite database and will display the records in a proper table format. Here is the complete code.

#import library
#import library for creating GUI
from tkinter import *
import tkinter.ttk as ttk
#import library for handling SQLite database
import sqlite3
#defining function for creating GUI Layout
def DisplayForm():
    #creating window
    display_screen = Tk()
    #setting width and height for window
    display_screen.geometry("800x200")
    #setting title for window
    display_screen.title("krazyprogrammer.com")
    global tree
    #creating frame
    TopViewForm = Frame(display_screen, width=600, bd=1, relief=SOLID)
    TopViewForm.pack(side=TOP, fill=X)
    LeftViewForm = Frame(display_screen, width=600)
    LeftViewForm.pack(side=LEFT, fill=Y)
    MidViewForm = Frame(display_screen, width=600)
    MidViewForm.pack(side=RIGHT)
    lbl_text = Label(TopViewForm, text="Student Records", font=('arial', 18), width=600,bg="#1C2833",fg="white")
    lbl_text.pack(fill=X)
    lbl_txtsearch = Label(LeftViewForm, text="", font=('arial', 15))
    lbl_txtsearch.pack(side=TOP, anchor=W)
    #setting scrollbar
    scrollbarx = Scrollbar(MidViewForm, orient=HORIZONTAL)
    scrollbary = Scrollbar(MidViewForm, orient=VERTICAL)
    tree = ttk.Treeview(MidViewForm,columns=("Student Id", "Name", "Contact", "Email","Rollno","Branch"),
                        selectmode="extended", height=100, yscrollcommand=scrollbary.set, xscrollcommand=scrollbarx.set)
    scrollbary.config(command=tree.yview)
    scrollbary.pack(side=RIGHT, fill=Y)
    scrollbarx.config(command=tree.xview)
    scrollbarx.pack(side=BOTTOM, fill=X)
    #setting headings for the columns
    tree.heading('Student Id', text="Student Id", anchor=W)
    tree.heading('Name', text="Name", anchor=W)
    tree.heading('Contact', text="Contact", anchor=W)
    tree.heading('Email', text="Email", anchor=W)
    tree.heading('Rollno', text="Rollno", anchor=W)
    tree.heading('Branch', text="Branch", anchor=W)
    #setting width of the columns
    tree.column('#0', stretch=NO, minwidth=0, width=0)
    tree.column('#1', stretch=NO, minwidth=0, width=100)
    tree.column('#2', stretch=NO, minwidth=0, width=150)
    tree.column('#3', stretch=NO, minwidth=0, width=80)
    tree.column('#4', stretch=NO, minwidth=0, width=120)
    tree.pack()
    DisplayData()
#defining function to access data from SQLite database
def DisplayData():
    # open databse
    conn = sqlite3.connect('student.db')
    #select query
    cursor=conn.execute("SELECT * FROM STUD_REGISTRATION")
    fetch = cursor.fetchall()
    for data in fetch:
        tree.insert('', 'end', values=(data))
    cursor.close()
    conn.close()
#calling function
DisplayForm()
#Running Application
mainloop()
 
You just type this code into your python file or you can copy this code for your personal use. When you will run this code you will get a screen like below
How to fetch data from SQLite database in python tkinter

I hope now you can fetch data from SQLite and display into Python Tkinter.


Request:-If you found this post helpful then let me know by your comment and share it with your friend. 

If you want to ask a question or want to suggest then type your question or suggestion in comment box so that we could do something new for you all. 

If you have not subscribed my website then please subscribe my website. Try to learn something new and teach something new to other. 

Thanks.😊
Tags

Post a Comment

1 Comments