Centennial College

SCHOOL OF ENGINEERING TECHNOLOGY AND APPLIED SCIENCE

Information and Communication Engineering Technology
ETEC224 – SYSTEM INTEGRATION

Lab 5 – Python & MySQL

STUDENT NAME:
STUDENT ID:

Lab Objective:

 Install a database management software (MySQL)

 Execute SQL commands at the MySQL command prompt
 Create Tables in MySQL
 Run Queries in MySQL
 Access MySQL database via Python scripts

MySQL is an open-source relational database management system. Its name is a combination of "My", the
name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language.

MySQL is a relational database management system based on SQL – Structured Query Language. The
application is used for a wide range of purposes, including data warehousing, e-commerce, and logging
applications. The most common use for mySQL however, is for the purpose of a web database.

Procedure:

1. Connect your Raspberry Pi and power on.

2. Switch to one of your virtual terminals (or start a terminal within the graphical environment).

3. Inside your home directory, create a new folder call lab5.

4. Change directory into the lab5 folder.

In this lab, you are going to explore working with a database. MySQL is an open-source relational
database management system that is free and commonly used in the Linux community. You will be
installing MySQL on your Raspberry Pi. Through the MySQL client, you will be learning how to create,
edit, and query your MySQL database via the command-line interface. To begin, we must install
MySQL using the apt-get utility. Apt-get is a command-line package management tool that helps to
keep track and install your various software.
Centennial College ETEC224 – System Integration 1
 5. Ensure your Raspberry PI has internet access (via Ethernet or WiFi)

6. Update the software package information on your Linux system by running the
following command:

pi@raspberrypi ~/lab5$ sudo apt update

Centennial College ETEC224 – System Integration 2

 7. Upgrade the software packages on your Linux system by running the following command:

pi@raspberrypi ~/lab5$ sudo apt upgrade

8. Install MySQL server by running the following command:

pi@raspberrypi ~/lab5$ sudo apt install mariadb-server

9. Log into MySQL server as the root user by running the following command:

pi@raspberrypi ~/lab5$ sudo mysql -u root

10. When prompted enter the password specified during installation (should be ‘sql’ if you followed
the instructions)

Centennial College ETEC224 – System Integration 3

You should now be presented with the MySQL command prompt (mysql>) and should be able to run
SQL commands. The mysql command starts a MySQL command-line client in which you can
communicate with the running MySQL server. SQL (pronounced like the word ‘sequel’) is a standard
language for communicating with Database Management Systems.

11. At the MySQL command prompt, type the following command and record the result in the space
below:

mysql> show databases;

Note: all SQL statements terminate with a semicolon. If you forget to use one and hit enter, the prompt
will change to ‘->’ indicating that you are still continuing from your previous SQL command.

Note: The MySQL server is capable of managing multiple database at the same time. We will now
create a new database for the purpose of this lab.

12. At the MySQL command prompt, type the following SQL command to create a new database
named ‘lab5’:

mysql> create database lab5;

It is not prudent to continue operating using the root user account. We will now create a new MySQL
user and grant this user privilege to access our lab5 database.

13. At the MySQL command prompt, type the following command to create a user named ‘sid’ with
a password secret:

mysql> create user ‘sid’@localhost identified by ‘secret’;

14. At the MySQL command prompt, type the following command to grant access privilege for the
‘lab5’ database for the user ‘sid’:

mysql> grant all privileges on lab5.* to ‘sid’@’localhost’ with grant option;

15. At the MySQL command prompt, type the following command to exit MySQL client and end the
root user session.

mysql> quit;

Centennial College ETEC224 – System Integration 4

 16. Log into MySQL server as the ‘sid’ user by running the following command:

pi@raspberrypi ~/lab5$ mysql –u sid -p

17. When prompted enter the password for the sid user (should be ‘secret’ if you followed the
instructions)

When logged into mysql as the user sid, you do not have the same permission as when logged in as the
root user. The root user has the ability to create and modify databases, create and modify new users within the
mysql environment. On the other hand, the sid user has been granted privilege only for the lab5 database
(refer to step 17).

18. At the MySQL command prompt, type the following command to show the databases being
managed by the MySQL server.

mysql> show databases;

Do you see the lab5 database?

19. At the MySQL command prompt, type the following command to indicate that you will be
using the lab5 database.

mysql> use lab5;

Note: it was possible in step 19 to login at the user sid and indicate that you would be using the
lab5 database at the same time as follows (DO NOT EXECUTE THIS COMMAND AS YOU
ARE ALREADY LOGGED IN):

pi@raspberrypi ~/lab5$ mysql --user=sid --password=secret lab5

20. At the MySQL command prompt, type the following command to verify that you have are
using the lab5 database.

mysql> select database();

+ +
| database() |
+ +
| lab5 |
+ +
1 row in set (0.01 sec)

Within your database, information is stored in tables. The lab5 database is a brand new database and
does not currently contain any tables. You will need to add a table to this database in order to save
any data.

Centennial College ETEC224 – System Integration 5

 21. At the MySQL command prompt, type the following command to create a new table named
products in the lab5 database.

mysql> create table products (

-> prodID int unsigned not null auto_increment,
-> prodCode char(3) not null default ‘’,
-> name varchar(30) not null default ‘’,
-> qty int unsigned not null default 0,
-> msrp double(10,2) not null default 0000000.00,
-> primary key(prodID)
-> );
Query OK, 0 rows affected (0.04 sec)
https://mariadb.com/kb/en/create-table/
Paste the screenshot of results here.

22. At the MySQL command prompt, type the following command to show the tables that are in the
lab5 database. You should see that lab5 currently consist of only the products table. A
database would typically consist of many tables. However, we will only create one single table
for the purpose of this lab.

mysql> show tables;

23. At the MySQL command prompt, type the following command to review the structure of the
products table.

mysql> describe products;

Record the output from this command in the space below:

Centennial College ETEC224 – System Integration 6

 24. An synonym to the describe command is the explain command. At the MySQL command
prompt, type the following command to view the output of the explain command and compare
it to the output from step 26.

Centennial College ETEC224 – System Integration 7

 mysql> explain products;

25. Now that a table has been created, it is possible to add data into the table by “inserting” records
into our table. At the MySQL command prompt, type the following command to insert a record
into the products table.

mysql> insert into products values (1234, ‘xrt’, ‘xray tube’, 20, 10000.00);

Note: you should get the message “Query OK, 1 row affected” as a result of this command.
This indicates that your new record has been inserted into the products table creating 1 new
row.

26. To verify that the insert instruction of step 28 was successful, let’s take a look at all the content
of the products table. At the MySQL command prompt, type the following command to select
all columns from the products table and display it on the screen.

mysql> select * from products;

Record the output from this command in the space below: Paste the screenshot of results here.

27. Repeat the procedure of step 28 to insert the following additional entries into the products table.

prodID prodCode name qty msrp

NULL ecg ecg pads 20000 10.00
NULL ekg ekg pads 35000 10.00
2345 eeg head pad set 200 100.00

28. At the MySQL command prompt, type the following command to select all columns from the
products table and display it on the screen. You should see all 4 records.

mysql> select * from products;

As your database gets bigger and more and more entries are added, it may be desirable to be able to
search your table for only those records that matches a certain criteria.

29. At the MySQL command prompt, type the following select command to display all columns
from the products table for only those rows with msrp equal to 10.00.

mysql> select * from products where msrp=10.00;

Centennial College ETEC224 – System Integration 8
 30. At the MySQL command prompt, type the following select command to display all columns
from the products table for only those rows with msrp equal to 10.00 and qty is greater than
30000.

mysql> select * from products where msrp=10.00 and qty>30000;

select * from products where a=5 and b>4;

Centennial College ETEC224 – System Integration 9

You have seen how the select command can be used with criteria to limit the rows returned from your
search. At this point, “select *” indicates that we would like to get all columns. It is possible to retrieve
only certain columns in our search.

31. At the MySQL command prompt, type the following select command to display only the
prodCode and qty columns from the table.

mysql> select prodCode, qty from products;

By varying the columns you select and the criterial you specify, you can limit the results return from
your search.

32. At the MySQL command prompt, type the following select command to display only the
prodCode and qty columns from the table with msrp equal to 10.00 and qty>30000.

mysql> select prodCode, qty from products where msrp=10.00 and qty>30000;

NOTE: A column (in this case msrp) can be used in the criteria even if it is not specified in the
column list for selection.

In addition to selecting certain columns, it is possible to execute a select command to generate a

count, average, maximum, minimum, or sum of a field.

33. At the MySQL command prompt, type the following select command to get the sum of the qty
field (column) for all records (rows).

mysql> select sum(qty) from products;

34. Add a criteria to the select command to limit the rows that are summed. At the MySQL
command prompt, type the following select command to get the sum of the qty field for only the
rows with msrp greater than 50.00.

mysql> select sum(qty) from products where msrp>50.00;

35. Try the following commands one at a time to see the resulting outputs:
a) mysql> select max(qty) from products;
b) mysql> select min(qty) from products;
c) mysql> select count(*) from products where qty>100;
d) mysql> select avg(msrp) from products where qty<25000;

The output of the select command is typically shown in the order in which you inserted it into the table
or sorted by the primary key (in this case the prodID). However, it is possible to sort the output using
Centennial College ETEC224 – System Integration 10
other columns in either ascending (ASC) or descending (DESC) order.

36. At the MySQL command prompt, type the following select command to see the data from the
products table sorted by the qty field in ascending order.

mysql> select * from products order by qty ASC;

Centennial College ETEC224 – System Integration 11

 37. At the MySQL command prompt, type the following select command to see only the prodID and
qty columns from the products table for rows where msrp is greater than 50 sorted by the qty
field in descending order.

mysql> select prodID, qty from products where msrp>50.00 order by qty DESC;

Now that you can insert data (insert) into your database tables and search for data (select). It is time
to see how you can change the data that already in the table using the update command.

38. At the MySQL command prompt, type the following update command to change qty to 2000 for
the product with prodID of 1234.

mysql> update products set qty=2000 where prodID=1234;

The delete command can be used to remove row(s) from the database table.

39. At the MySQL command prompt, type the following delete command to remove the row where
the prodID is 1234.

mysql> delete from products where prodID=1234;

40. Use the select command to verify that the row with prodID=1234 is now gone. What command
did you use?

41. It is possible to remove multiple rows from the table at one time based on a criteria. At the
MySQL command prompt, type the following delete command to remove all rows where the qty
is greater than 30000.

mysql> delete from products where qty>30000;

42. Use the select command to verify that all rows with qty greater than 30000 is now gone.

While the delete command allows you to delete a single row, the alter command will allow you to
modify or remove an entire column of your table.

43. At the MySQL command prompt, type the following alter command to modify the name column.

mysql> alter table products modify column name varchar(40);

44. Run the describe command to verify that the products table has been modify.

mysql> describe products;

Centennial College ETEC224 – System Integration 12

 45. At the MySQL command prompt, type the following alter command to remove the name
column.

mysql> alter table products drop column name;

46. Run the describe command to verify that the products table has been modify.

mysql> describe products;

47. At the MySQL command prompt, type the following alter command to add the name column
back.

mysql> alter table products add name varchar(30);

48. Run the describe command to verify that the products table has been modify.

mysql> describe products;

While it is very helpful to learn how to work with the MySQL command prompt, understanding how to
programmatically execute SQL commands will enable you to automate the process of accessing your
database. In this part of the lab, we will explore how to use Python (which was explored in lab 5) to
access our database.

49. At the MySQL command prompt, type the following command to exit from the mysql client.

mysql> quit;

50. Install the python-mysqldb package on your Linux system by running the following command:

pi@raspberrypi ~/lab5$ sudo apt install mariadb-server python3-mysqldb

NOTE: This package contains the necessary software tools that enable your python script to
interact with your MySQL database using SQL commands.

51. In your lab5 directory, create a new file named script5_1.py

mkdir Lab5
cd Lab5
touch script5_1.py

52. We will now create a python script to connect and insert some data into the products table of
the lab5 database. Open script5_1.py file using a text editor and enter the following
program.

Centennial College ETEC224 – System Integration 13

nano script5_1.py
Do not copy

#!/usr/bin/python

import MySQLdb

db_conn = MySQLdb.connect(host=”localhost”, user=”sid”, passwd=”secret”, db=”lab5”)

interct = db_conn.cursor()
interct.execute( “insert into products values (NULL, ‘abc’, ’test’, 101, 1.99)”)
db_conn.commit()
db_conn.close()

Centennial College ETEC224 – System Integration 14

 53. Save your file and change the permission so that script5_1.py is executable.

Chmod +x script5_1.py

54. Execute the script by running the following command.

pi@raspberrypi ~/lab5$ python script5_1.py

At this point, a new entry should have been inserted into the products table of the lab5
database.
Paste the screenshot of results here.

55. In your lab5 directory, create a new file named script5_2.py

56. We will now create a python script to connect and select data from the products table of the
Lab5 database. Open script5_2.py file using a text editor and enter the following program.

Do not copy

#!/usr/bin/python
import MySQLdb

db_conn = MySQLdb.connect(host=”localhost”, user=”sid”, passwd=”secret”, db=”lab5”)

interct = db_conn.cursor()
interct.execute( “select * from products”)
for row in interct.fetchall():
print(row)

db_conn.close()

57. Save your file and change the permission so that script5_2.py is executable.

58. Execute the script by running the following command.

pi@raspberrypi ~/lab5$ python script5_2.py

At this point, you should see on the console screen the data from the products table of the lab5
database.
Paste the screenshot of results here.

We are now finished with the products table. It is possible to delete an entire table from a database by
using the drop SQL command.

59. Log into MySQL server as the ‘sid’ user once again by running the following command:

Centennial College ETEC224 – System Integration 15

 pi@raspberrypi ~/lab5$ mysql –u sid –p

60. At the MySQL command prompt, type the following command to indicate that you will be using

Centennial College ETEC224 – System Integration 16

 the lab5 database.

mysql> use lab5;

Centennial College ETEC224 – System Integration 17

 61. At the MySQL command prompt, type the following command to delete the products table
from the lab5 database.

mysql> drop table products;

62. At the MySQL command prompt, type the following command to exit MySQL client and end the
root user session.

mysql> quit;

Centennial College ETEC224 – System Integration 18