In this post we'll see how SQLite3 allows for interaction with databases in Python, through SQL syntax by making a small program. This application "COVRI Training " allows a user to select a course from our training program in programming languages along with the training duration and an option to do project based training along with the basic training.
The GUI for application to be developed is shown below:
Let's start coding to implement the GUI of our COVRI Training application as shown above. First step as always is to import relevant libraries,the Tkinter to build the GUI and SQLite3 for interaction with the database. Tkinter and SQLite3 are libraries that come with the standard Python library.
from tkinter import *
import sqlite3 as sq
Next step will be creating root window:
window = Tk()
window.title("COVRI Training")
window.geometry('800x600+0+0')
header = Label(window, text="Available courses", font=("arial",30,"bold"), fg="goldenrod").pack()
The root window is created and assigned to ‘window’this can be any variable, sometimes I use root. The title of the root window is set to "COVRI Training"and the size of the window is set. We can input text to form a header for the GUI. This is done via the ‘header’ variable. Our header is set to "Available courses".
Once the widow is available we create our widgets which includes list boxes, labels and buttons. As per requirement the following information is needed to store a record in the database:
Select Course
Duration(months)
Want Projects(Y/N) ?
Accordingly we'll set the labels for the above mentioned information:
L1 = Label(window, text = "Select Course", font=("arial", 18)).place(x=10,y=100)
L2 = Label(window, text = "Duration(months)", font=("arial",18)).place(x=10,y=150)
L3 = Label(window, text = "Want Projects(Y/N) ?", font=("arial",18)).place(x=10,y=200)
As we have already discussed about labels I'm not covering the details but one thing to notice is that the place() method is used to set the labels in the window.
In COVRI training I have included 'Python', 'Java', 'PHP', and 'Javascript' as the languages to be taught. I'll create a dictionary to store these language options:
planguages = {'Python', 'Java', 'PHP','Javascript'}
As I prefer the user to select a language from a drop down, I create the drop down list using the ‘OptionMenu’ function. But we need a variable for the languages which will be used in the OptionMenu function thus we define it first:
lvar = StringVar(window)
lvar .set('----')
The arguments for the OptionMenu function are the root window, the variable for the languages (set above) and the dictionary planguages :
langd= OptionMenu(window, comp, *compound)
The option menu is placed next to the Select course label:
langd.place(x=220,y=105)
Now we will set the text boxes so that users can input the relevant data. Tkinter provides the ‘Entry’ function to place text boxes into the GUI. First we declare two variables:
duration = StringVar(window)
wpro = StringVar(window)
Then use them in the Entry widget.
durationT = Entry(window, textvariable=duration)
durationT.place(x=220,y=155)
wproT = Entry(window, textvariable=wpro)
wproT.place(x=220,y=205)
These widgets are placed next to their respective labels in the GUI. So far we have developed our application to accept user input. Now the next challenge is to store the date in to a database from where it can be retrieved as and when required.
We'll begin with created a database using:
con = sq.connect('mycourses.db')
This statement creates a database if it doesn't exists and then establishes a connection to it. Next we create a cursor ( details we have covered in the previous posts)
c = con.cursor()
The cursor will allow us to call on the ‘execute’ method, which will allow for SQL commands. The execute method will allow for a table to be created based off the selected compound lift in the drop down menu.To get the text from the entry boxes and store in the database we define a get():
def get():
c.execute('CREATE TABLE IF NOT EXISTS ' +lvar .get()+ ' (Duration INTEGER, PROJECTS TEXT)') #SQL syntax
c.execute('INSERT INTO ' +lvar .get()+ ' (Duration, PROJECTS) VALUES (?, ?)',(duration.get(), wpro.get())) #Insert record into database.
con.commit()
Next we create a submit button which will use this get() and allows user to store his entry in to the database:
button_1 = Button(window, text="Submit",command=get)
button_1.place(x=100,y=300)
To reset the entry fields after the submit button is clicked add this to the get()
lvar .set('----')
duration.set('')
wpro.set('')
We also prefer to keep a clear button in case the user wants to change/edit his selection. The functionality of this button will be implemented in a newly defined clear():
def clear():
lvar .set('----')
compdb.set('----')
duration.set('')
wpro.set('')
The clear button can be created as shown below:
button_2 = Button(window,text= "Clear",command=clear)
button_2.place(x=10,y=300)
The view button can be created as shown below:
button_3 = Button(window,text="View",command=record)
button_3.place(x=10,y=350)
To display the output for the view button we have created a frame. Within this frame a list box with scroll bar is created. In this list we first enter the Duration and Project labels by using:
Lb.insert(0, 'Duration Projects')
Then we fetch the data from the table using:
data = c.fetchall()
Then using a for loop we insert the data in the list box:
for row in data:
Lb.insert(1,row)
It's better to display the name of selected language above the list box for this we create another label and place above the list box:
L4 = Label(window, text = compdb.get()+ ' ',
font=("arial", 16)).place(x=400,y=100)
Similarly we display a message "Details for selected course" below the list box with the help of a new label created as shown below:
L5 = Label(window, text = "Details for selected course",
font=("arial", 16)).place(x=400,y=350)
Remember to keep the created window open using the window.mainloop().
This application is a good exercise to use whatever we've covered so far for tkinter and sqlite. Once the program is completed it should give the following output:
The complete functionality is shown in the following screenshots:
Enter a few more records for Java and submit to database. Then view the records as shown:
Once view is clicked the output changes to:
Here today's topic comes to end. Hopefully you should be able to develop this simple application and test how far you have mastered tkinter and sqlite. This application is built just to explain how to use tkinter with sqlite database and can be made much better.
Till we meet next, keep practicing and learning Python as Python is easy to learn!
The GUI for application to be developed is shown below:
Let's start coding to implement the GUI of our COVRI Training application as shown above. First step as always is to import relevant libraries,the Tkinter to build the GUI and SQLite3 for interaction with the database. Tkinter and SQLite3 are libraries that come with the standard Python library.
from tkinter import *
import sqlite3 as sq
Next step will be creating root window:
window = Tk()
window.title("COVRI Training")
window.geometry('800x600+0+0')
header = Label(window, text="Available courses", font=("arial",30,"bold"), fg="goldenrod").pack()
The root window is created and assigned to ‘window’this can be any variable, sometimes I use root. The title of the root window is set to "COVRI Training"and the size of the window is set. We can input text to form a header for the GUI. This is done via the ‘header’ variable. Our header is set to "Available courses".
Once the widow is available we create our widgets which includes list boxes, labels and buttons. As per requirement the following information is needed to store a record in the database:
Select Course
Duration(months)
Want Projects(Y/N) ?
Accordingly we'll set the labels for the above mentioned information:
L1 = Label(window, text = "Select Course", font=("arial", 18)).place(x=10,y=100)
L2 = Label(window, text = "Duration(months)", font=("arial",18)).place(x=10,y=150)
L3 = Label(window, text = "Want Projects(Y/N) ?", font=("arial",18)).place(x=10,y=200)
As we have already discussed about labels I'm not covering the details but one thing to notice is that the place() method is used to set the labels in the window.
In COVRI training I have included 'Python', 'Java', 'PHP', and 'Javascript' as the languages to be taught. I'll create a dictionary to store these language options:
planguages = {'Python', 'Java', 'PHP','Javascript'}
As I prefer the user to select a language from a drop down, I create the drop down list using the ‘OptionMenu’ function. But we need a variable for the languages which will be used in the OptionMenu function thus we define it first:
lvar = StringVar(window)
lvar .set('----')
The arguments for the OptionMenu function are the root window, the variable for the languages (set above) and the dictionary planguages :
langd= OptionMenu(window, comp, *compound)
The option menu is placed next to the Select course label:
langd.place(x=220,y=105)
Now we will set the text boxes so that users can input the relevant data. Tkinter provides the ‘Entry’ function to place text boxes into the GUI. First we declare two variables:
duration = StringVar(window)
wpro = StringVar(window)
Then use them in the Entry widget.
durationT = Entry(window, textvariable=duration)
durationT.place(x=220,y=155)
wproT = Entry(window, textvariable=wpro)
wproT.place(x=220,y=205)
These widgets are placed next to their respective labels in the GUI. So far we have developed our application to accept user input. Now the next challenge is to store the date in to a database from where it can be retrieved as and when required.
We'll begin with created a database using:
con = sq.connect('mycourses.db')
This statement creates a database if it doesn't exists and then establishes a connection to it. Next we create a cursor ( details we have covered in the previous posts)
c = con.cursor()
The cursor will allow us to call on the ‘execute’ method, which will allow for SQL commands. The execute method will allow for a table to be created based off the selected compound lift in the drop down menu.To get the text from the entry boxes and store in the database we define a get():
def get():
c.execute('CREATE TABLE IF NOT EXISTS ' +lvar .get()+ ' (Duration INTEGER, PROJECTS TEXT)') #SQL syntax
c.execute('INSERT INTO ' +lvar .get()+ ' (Duration, PROJECTS) VALUES (?, ?)',(duration.get(), wpro.get())) #Insert record into database.
con.commit()
Next we create a submit button which will use this get() and allows user to store his entry in to the database:
button_1 = Button(window, text="Submit",command=get)
button_1.place(x=100,y=300)
To reset the entry fields after the submit button is clicked add this to the get()
lvar .set('----')
duration.set('')
wpro.set('')
We also prefer to keep a clear button in case the user wants to change/edit his selection. The functionality of this button will be implemented in a newly defined clear():
def clear():
lvar .set('----')
compdb.set('----')
duration.set('')
wpro.set('')
The clear button can be created as shown below:
button_2 = Button(window,text= "Clear",command=clear)
button_2.place(x=10,y=300)
The application now can get input from a user and store in the database. Next we plan a View button which when clicked allows us to view the entries related to our programming languages.
We create a record function which implements the functionality of the view button. We first create another variable for the second dropdown list:
langdb = StringVar(window)
langdb .set('----')
def record():
c.execute('SELECT * FROM ' +langdb .get()) #Select from which ever language is selected
frame = Frame(window)
frame.place(x= 400, y = 150)
Lb = Listbox(frame, height = 8, width = 25,font=("arial", 12))
Lb.pack(side = LEFT, fill = Y)
scroll = Scrollbar(frame, orient = VERTICAL)
scroll.config(command = Lb.yview)
scroll.pack(side = RIGHT, fill = Y)
Lb.config(yscrollcommand = scroll.set)
Lb.insert(0, 'Duration, Projects')
data = c.fetchall()
for row in data:
Lb.insert(1,row) # Inserts record row by row in list box
L4 = Label(window, text = langdb .get()+ ' ',
font=("arial", 16)).place(x=400,y=100)
L5 = Label(window, text = "Details for selected course",
font=("arial", 16)).place(x=400,y=350)
con.commit()
The view button can be created as shown below:
button_3 = Button(window,text="View",command=record)
button_3.place(x=10,y=350)
To display the output for the view button we have created a frame. Within this frame a list box with scroll bar is created. In this list we first enter the Duration and Project labels by using:
Lb.insert(0, 'Duration Projects')
Then we fetch the data from the table using:
data = c.fetchall()
Then using a for loop we insert the data in the list box:
for row in data:
Lb.insert(1,row)
It's better to display the name of selected language above the list box for this we create another label and place above the list box:
L4 = Label(window, text = compdb.get()+ ' ',
font=("arial", 16)).place(x=400,y=100)
Similarly we display a message "Details for selected course" below the list box with the help of a new label created as shown below:
L5 = Label(window, text = "Details for selected course",
font=("arial", 16)).place(x=400,y=350)
Remember to keep the created window open using the window.mainloop().
This application is a good exercise to use whatever we've covered so far for tkinter and sqlite. Once the program is completed it should give the following output:
The complete functionality is shown in the following screenshots:
Enter a few more records for Java and submit to database. Then view the records as shown:
Once view is clicked the output changes to:
Here today's topic comes to end. Hopefully you should be able to develop this simple application and test how far you have mastered tkinter and sqlite. This application is built just to explain how to use tkinter with sqlite database and can be made much better.
Till we meet next, keep practicing and learning Python as Python is easy to learn!
0 comments:
Post a Comment