In this lab you will learn how to connect to a relational database, place queries, and retrieve the data through a Jupyter notebook. You will also learn how to transfer the data from a Microsoft Excel spreadsheet to a SQLite database.
The data you are provided contains various measurements for ping, upload, and download speed made in different Local Authorities around England. At the moment, the data is stored in a .csv file, but you will create a database and populate it with the measurement.
We are going to use SQLite to connect to the database. SQLite is a library that implements a self-contained transactional SQL database engine that eliminates the need for an SQL server. A simple SQLite utility called
sqlite3 can be installed in the Raspberry Pi. SQLite greatly simplifies the SQL database process.
We are also going to use
csvkit, a suite of tools designed to convert various databases to the .csv (comma separated values) format.
We will need
csvkit to complete this lab. The commands below can be entered and executed directly on a Raspberry Pi terminal. You can get access to the terminal by clicking New > Terminal in the Home Tab in your web browser. The Home Tab is the tab that contains the list of labs you used to launch this lab.
Note: It is recommended to place the terminal tab side-by-side with this tab. This allows you to easily switch from the lab to the terminal window.
Below, the first command updates and synchronizes the Pi's package list with the repository server. This is to ensure the latest versions of packages are used. After the package list is up to date, the second command installs
sqlite3, an SQLite package for the Pi that contains the
Note: Since the Raspberry Pi has to contact the package respository servers to download and install the packages, these commands may take a while to finish executing.
Run the cell by either clicking the play icon to the left of the code cell or by selecting the cell pressing <SHIFT+ENTER>. Running a code cell executes the contained commands in your Raspberry Pi.
Note: In a Jupyter notebook, lines starting with an exclamation mark (
!) are commands that will be executed by the Raspberry Pi's terminal.
# apt-get update
# apt-get -y install sqlite3
After the installation process finishes, install
csvkit by running the code cell below:
Note: The installation may take a while.
# pip install csvkit
Now your Raspberry Pi has
csvkit installed and ready to be used.
csvkit are installed, we can start using them to create a database and its tables.
A table is a set of information presented in a matrix of rows and columns. A database is a collection of tables.
Consider the table below: