Python & MySQL CRUD Tutorial
Python & MySQL CRUD Tutorial
and Technologies 2
Oldarico Pigon
College of Computer Studies
IPT 2 2
Week 1
Lesson:
Objectives:
Developing a simple application with create, read, update, and delete feature with Python and
MySQL.
Topics:
CRUD
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 3
CRUD
A function is a block of code which only runs when it is called.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 4
CRUD
Creating and Calling a Function
def say_myname():
print("You are Oldarico Pigon")
say_myname()
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 5
CRUD
Imports
Import using import mysql.connector as mysql statement so you can use this module's
methods to communicate with the MySQL database.
Import using from mysql.connector import Error forces you to generate an Error statement
if the compiler detects an error in your MySQL syntax.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 6
CRUD
Install package mysql-connector-python.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 7
CRUD
Make a function called "insert.“
def insert():
def insert():
fname = input("Enter First Name: ")
mname = input("Enter Middle Name: ")
lname = input("Enter Last Name: ")
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 8
CRUD
Python input() function is used to take user input. By default, it returns the user input in form
of a string.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 9
CRUD
Try Except
The try block lets you test a block of code for errors.
try:
print(name)
except:
print("The Variable is not set")
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 10
CRUD
Create a try except
try:
conn = mysql.connect(host="localhost", username="root", password="", database="db_test")
sql = "INSERT INTO tbl_account (firstname, middlename, lastname) VALUES ('{}', '{}', '{}')".format(fname, mname, lname)
cursor = conn.cursor()
cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 11
CRUD
To create a connection between the MySQL database and Python, the connect() method of
mysql.connector module is used. We pass the database details like HostName, username,
and the password in the method call, and then the method returns the connection object.
A cursor is an object which helps to execute the query and fetch the records from the database.
The commit() method lets a user permanently save all the changes made in the transaction of
a database or table.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 12
CRUD
Insert the following code within the except keyword:
except Error:
print("Error: ").format(Error)
insert()
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 13
CRUD
import mysql.connector as mysql
from mysql.connector import Error
def insert():
fname = input("Enter First Name: ")
mname = input("Enter Middle Name: ")
lname = input("Enter Last Name: ")
try:
conn = mysql.connect(host="localhost", username="root", password="", database="db_test")
sql = "INSERT INTO tbl_account (firstname, middlename, lastname) VALUES ('{}', '{}', '{}')".format(fname, mname, lname)
cursor = conn.cursor()
cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()
except Error:
print("Error: ").format(Error)
insert()
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 14
CRUD
Create a function called select().
def select():
Create try except keyword and Insert the following code within the try keyword:
try:
conn = mysql.connect(host="localhost", username="root", password="", database="db_test")
sql = "SELECT * FROM tbl_account"
cursor = conn.cursor()
cursor.execute(sql)
result = cursor.fetchall()
cursor.close()
conn.close()
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 15
CRUD
cursor.fetchall() fetches all the rows of a query result. It returns all the rows as a list of
tuples. An empty list is returned if there is no record to fetch. cursor.
cursor.rowcount returns the number of rows returned for SELECT statements, or the
number of rows affected by DML statements such as INSERT or UPDATE .
x = 10
print("This is string " + str(x))
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 16
CRUD
A for loop is used for iterating over a sequence (that is either a list, a tuple, a dictionary, a
set, or a string).
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 17
CRUD
Insert the following code within the except keyword:
except Error:
print("Error: ").format(Error)
select()
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 18
CRUD
import mysql.connector as mysql
from mysql.connector import Error
def select():
try:
conn = mysql.connect(host="localhost", username="root", password="", database="db_test")
sql = "SELECT * FROM tbl_account"
cursor = conn.cursor()
cursor.execute(sql)
result = cursor.fetchall()
cursor.close()
conn.close()
except Error:
print("Error: ").format(Error)
select()
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 19
CRUD
Create a function called delete()
def delete():
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 20
CRUD
Create try except keyword and Insert the following code within the try keyword:
try:
conn = mysql.connect(host="localhost", username="root", password="", database="db_test")
sql = "DELETE FROM tbl_account WHERE ID = {}".format(ID)
cursor = conn.cursor()
cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 21
CRUD
Insert the following code within the except keyword:
except Error:
print("Error: ").format(Error)
delete()
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 22
CRUD
import mysql.connector as mysql
from mysql.connector import Error
def delete():
ID = input("Enter Delete ID: ")
try:
conn = mysql.connect(host="localhost", username="root", password="", database="db_test")
sql = "DELETE FROM tbl_account WHERE ID = {}".format(ID)
cursor = conn.cursor()
cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()
except Error:
print("Error: ").format(Error)
delete()
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 23
CRUD
def update():
ID = input("Enter ID: ")
fname = input("Enter New First Name: ")
mname = input("Enter New Middle Name: ")
lname = input("Enter New Last Name: ")
try:
conn = mysql.connect(host="localhost", username="root", password="", database="db_test")
sql = "UPDATE tbl_account SET firstname='{}', middlename='{}', lastname='{}' WHERE ID = {}".format(fname, mname, lname, ID)
cursor = conn.cursor()
cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()
update()
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 24
Quiz 1
Directions:
Your work should be submitted as a recorded video.
Pass till the 19th of August.
50 pts
Create a program that will allow you to insert, update, delete and select your name, year, and
section.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 25
Week 2
Lesson:
Objectives:
Understanding the Tkinter GUI and widgets that we can use to create Tkinter applications, as
well as widget options.
Topics:
Tkinter GUI, Button Widget, Label Widget, and Entry Widget.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 26
Tkinter GUI
To create GUI application, we need to start by importing Tkinter modules.
root is the root window into which all other widgets go. It is called Master, that is the parameter
used to represent the parent window.
root = Tk()
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 27
Tkinter GUI
geometry() is used to set the dimensions of the Tkinter window and is used to set the position of
the main window on the user's desktop.
root.geometry
mainloop() is a method on the main window which we execute when we want to run our
application.
root.mainloop()
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 28
Tkinter GUI
Iconbitmap(bitmap) sets the icon of the window/frame widget to bitmap . The bitmap must be an
ico type, but not png or jpg type, otherwise, the image will not display as the icon.
In your project, create a new directory named “img" and save any .ico files inside.
root.iconbitmap("img/yt.ico")
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 29
Button Widget
Button widget.
The Button widget is used to add buttons in a Python application. These buttons can display
text or images that convey the purpose of the buttons. You can attach a function or a method to
a button which is called automatically when you click the button.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 30
Button Widget
Options and Description:
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 31
Button Widget
8. Height - Height of the button in text lines (for textual buttons) or pixels (for images).
9. Highlightcolor - The color of the focus highlight when the widget has focus.
11. Justify - How to show multiple text lines: LEFT to left-justify each line; CENTER to center them;
or RIGHT to right-justify.
14. Relief - Relief specifies the type of the border. Some of the values are SUNKEN, RAISED,
GROOVE, and RIDGE.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 32
Button Widget
15. Underline - Default is -1, meaning that no character of the text on the button will be underlined.
If nonnegative, the corresponding text character will be underlined.
16. State - Set this option to DISABLED to gray out the button and make it unresponsive. Has the
value ACTIVE when the mouse is over it. Default is NORMAL.
17. Width - Width of the button in letters (if displaying text) or pixels (if displaying an image).
18. Wraplength - If this value is set to a positive number, the text lines will be wrapped to fit within
this length.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 33
Button Widget
Activebackground - Background color when the button is under the cursor.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 34
Button Widget
Activeforeground - Foreground color when the button is under the cursor.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 35
Button Widget
Bd - Border width in pixels. Default is 2.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 36
Button Widget
Bg - Normal background color.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 37
Button Widget
Fg - Normal foreground (text) color.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 38
Button Widget
Font - Text font to be used for the button's label.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 39
Button Widget
Height - Height of the button in text lines (for textual buttons) or pixels (for images).
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 40
Button Widget
Justify - How to show multiple text lines: LEFT to left-justify each line; CENTER to center them;
or RIGHT to right-justify.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 41
Button Widget
Padx - Additional padding left and right of the text.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 42
Button Widget
Pady - Additional padding above and below the text.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 43
Button Widget
Relief - Relief specifies the type of the border. Some of the values are SUNKEN, RAISED,
GROOVE, and RIDGE.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 44
Button Widget
Underline - Default is -1, meaning that no character of the text on the button will be underlined.
If nonnegative, the corresponding text character will be underlined.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 45
Button Widget
State - Set this option to DISABLED to gray out the button and make it unresponsive. Has the
value ACTIVE when the mouse is over it. Default is NORMAL.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 46
Button Widget
Width - Width of the button in letters (if displaying text) or pixels (if displaying an image).
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 47
Button Widget
Wraplength - If this value is set to a positive number, the text lines will be wrapped to fit within
this length.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 48
Button Widget
Image - Image to be displayed on the button (instead of text).
images = PhotoImage(file="img/Red_button.png")
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 49
Button Widget
Command - Function or method to be called when the button is clicked.
def clickme():
lbl = Label(root, text="Hello World!")
lbl.pack()
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 50
Label Widget
Label widget.
This widget implements a display box where you can place text or images. The text displayed
by this widget can be updated at any time you want.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 51
Label Widget
Options and Description:
1. Anchor - This options controls where the text is positioned if the widget has more space than the
text needs. The default is anchor=CENTER, which centers the text in the available space.
2. Bg - The normal background color displayed behind the label and indicator.
3. Bitmap - Set this option equal to a bitmap or image object and the label will display that graphic.
5. Cursor - If you set this option to a cursor name (arrow, dot etc.), the mouse cursor will change to
that pattern when it is over the checkbutton.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 52
Label Widget
6. Font - If you are displaying text in this label (with the text or textvariable option, the font option
specifies in what font that text will be displayed.
7. Fg - If you are displaying text or a bitmap in this label, this option specifies the color of the text. If
you are displaying a bitmap, this is the color that will appear at the position of the 1-bits in the
bitmap.
9. Image - To display a static image in the label widget, set this option to an image object.
10. Justify - Specifies how multiple lines of text will be aligned with respect to each other: LEFT for
flush left, CENTER for centered (the default), or RIGHT for right-justified.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 53
Label Widget
11. Padx - Extra space added to the left and right of the text within the widget. Default is 1.
12. Pady - Extra space added above and below the text within the widget. Default is 1.
13. Relief - Specifies the appearance of a decorative border around the label. The default is FLAT; for
other values.
14. Text - To display one or more lines of text in a label widget, set this option to a string containing
the text. Internal newlines ("\n") will force a line break.
15. Textvariable - To slave the text displayed in a label widget to a control variable of class StringVar,
set this option to that variable.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 54
Label Widget
16. Underline - You can display an underline (_) below the nth letter of the text, counting from 0, by
setting this option to n. The default is underline=-1, which means no underlining.
17. Width - Width of the label in characters (not pixels!). If this option is not set, the label will be sized
to fit its contents.
18. Wraplength - You can limit the number of characters in each line by setting this option to the
desired number. The default value, 0, means that lines will be broken only at newlines.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 55
Label Widget
Anchor - This options controls where the text is positioned if the widget has more space than
the text needs. The default is anchor=CENTER, which centers the text in the available space.
You need to set the width and height of the label for it to work.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 56
Label Widget
Bitmap - Set this option equal to a bitmap or image object and the label will display that graphic.
b1 = Label(root, bitmap="error")
b2 = Label(root, bitmap="hourglass")
b3 = Label(root, bitmap="info")
b4 = Label(root, bitmap="question")
b5 = Label(root, bitmap="warning")
b1.pack()
b2.pack()
b3.pack()
b4.pack()
b5.pack()
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 57
Label Widget
Cursor - If you set this option to a cursor name (arrow, dot etc.), the mouse cursor will change to
that pattern when it is over the checkbutton.
greet = StringVar()
greet.set("Hello World")
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 58
Label Widget
Python Tkinter supports quite a number of different mouse cursors available. The exact
graphic may vary according to your operating system.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 59
Label Widget
Textvariable - To slave the text displayed in a label widget to a control variable of class
StringVar, set this option to that variable.
greet = StringVar()
greet.set("Hello World")
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 60
Entry Widget
Entry widget.
The Entry widget is used to provide the single line text-box to the user to accept a value from
the user. We can use the Entry widget to accept the text strings from the user. It can only be
used for one line of text from the user.
textbox = Entry(root)
textbox.pack()
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 61
Entry Widget
Options and Description:
1. Bg - The normal background color displayed behind the label and indicator.
3. Cursor - If you set this option to a cursor name (arrow, dot etc.), the mouse cursor will change to
that pattern when it is over the checkbutton.
5. Exportselection - By default, if you select text within an Entry widget, it is automatically exported to
the clipboard. To avoid this exportation, use exportselection=0.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 62
Entry Widget
6. Fg - The color used to render the text.
7. Highlightcolor - The color of the focus highlight when the entry has the focus.
8. Justify - If the text contains multiple lines, this option controls how the text is justified: CENTER,
LEFT, or RIGHT.
9. Relief - With the default value, relief=FLAT, the entry does not stand out from its background. You
may set this option to any of the other styles
11. Selectborderwidth - The width of the border to use around selected text. The default is one pixel.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 63
Entry Widget
12. Selectforeground - The foreground (text) color of selected text.
13. Show - Normally, the characters that the user types appear in the entry. To make a .password.
entry that echoes each character as an asterisk, set show="*".
14. State - The default is state=NORMAL, but you can use state=DISABLED to gray out the control
and make it unresponsive. If the cursor is currently over the entry, the state is ACTIVE.
15. Textvariable - In order to be able to retrieve the current text from your entry widget, you must set
this option to an instance of the StringVar class.
16. Width - The default width of a entry is determined by the size of the displayed image or text. You
can set this option to a number of characters and the entry will always have room for that many
characters.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 64
Entry Widget
Selectbackground - The background color to use displaying selected text.
Selectborderwidth - The width of the border to use around selected text. The default is one
pixel.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 65
Entry Widget
Highlightcolor - The color of the focus highlight when the entry has the focus.
The figures show what happens when the Entry is clicked and when it is not.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 66
Entry Widget
Show - Normally, the characters that the user types appear in the entry. To make a .password.
entry that echoes each character as an asterisk, set show="*".
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 67
Entry Widget
State - The default is state=NORMAL, but you can use state=DISABLED to gray out the control
and make it unresponsive. If the cursor is currently over the entry, the state is ACTIVE.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 68
Entry Widget
Textvariable - In order to be able to retrieve the current text from your entry widget, you must set
this option to an instance of the StringVar class.
x = StringVar()
x.set("Hello World")
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 69
Quiz 2
Directions:
Your work should be submitted as a recorded video.
NA
100 pts
Create a program that will allow the user to input and display your name, year, and section when
the button is clicked.
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
IPT 2 70
References
https://www.tutorialspoint.com/python/index.htm
https://www.tutorialspoint.com/python/python_gui_programming.htm
https://www.w3schools.com/python/
Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon