Integrated Programming

and Technologies 2
Oldarico Pigon
College of Computer Studies
 IPT 2 2

Week 1
Lesson:

 Simple CRUD Console Application with MySQL and Python

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.

You can pass data, known as parameters, into a function.

A function can return data as a result.

Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon
 IPT 2 4

CRUD
Creating and Calling a Function

In Python a function is defined using the def keyword:

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.

import mysql.connector as mysql

from mysql.connector import Error

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():

 Create three inputs within a function.

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.

name = input('Enter your name:')

print('Good Morning, ' + name)

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.

The except block lets you handle the error.

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

 Insert the following code within the try keyword:

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)

 Call the insert() function to insert data.

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

print("Number of records: " + str(cursor.rowcount))

for row in result:

print("ID: {} \nFirst Name: {} \nMiddle Name: {} \nLast Name: {} \nDate Inserted: {}".format(row[0], row[1], row[2], row[3], row[4]))

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 .

 The str() function converts the specified value into a string.

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

planets = ["Earth", "Jupiter", "Saturn"]

for x in planets:
print(x)

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)

 Call the select() function to insert data.

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

print("Number of records: " + str(cursor.rowcount))

for row in result:

print("ID: {} \nFirst Name: {} \nMiddle Name: {} \nLast Name: {} \nDate Inserted: {}".format(row[0], row[1], row[2], row[3], row[4]))

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():

 Create input() for the ID that will be deleted.

ID = input("Enter Delete ID: ")

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)

 Call the select() function to insert data.

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

except Error as error:

print("Error: {}".format(error))

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

References
 https://www.tutorialspoint.com/python/index.htm
 https://www.w3schools.com/python/

Touching Hearts. Renewing Minds. Transforming Lives. PREPARED BY: Oldarico Pigon