Python Programming for Raspberry Pi, Sams Teach Yourself in 24 Hours

Hour 21. Using Databases in Your Programming

What You’ll Learn in This Hour:

How to use a MySQL database server in your Python scripts

How to use a PostgreSQL database server in your Python scripts

One of the problems with Python scripts is persistent data. You can store all the information you want
in your program variables, but at the end of the program, they just go away. There are times when
you’d like for your Python scripts to be able to store data that you can use later. In the old days,
storing and retrieving data from a Python script required creating a file, reading data from the file,
parsing the data, and saving the data back into the file. Trying to search for data in the file meant
having to read every record in the file to look for your data. Today, with databases being all the rage,
it’s a snap to interface your Python scripts with professional-quality open-source databases.

The two most popular open-source databases used in the Linux world are MySQL and PostgreSQL,
and both are supported on the Raspberry Pi! In this hour, you’ll see how to get these databases
running on your Raspberry Pi system and then spend some time getting used to working with them
from the command line. You’ll then learn how to interact with each one by using your Python script
programs.

Working with the MySQL Database

By far the most popular database available in the Linux environment is the MySQL database. Its
popularity has grown as a part of the Linux-Apache-MySQL-PHP (LAMP) server environment, which
many Internet web servers use for hosting online stores, blogs, and applications.

The following sections describe how to install and set up a MySQL database in your Raspberry Pi
environment, how to create the necessary database objects to use in your Python scripts, and how to
write Python scripts to interact with the database.

Installing MySQL

While the MySQL database isn’t installed by default on the Raspberry Pi, installing it is a simple
process. The Raspbian Linux distribution has two packages in the software repository to support the
MySQL environment: mysql-client and mysql-server. As you can probably guess, the
mysql-server package contains the files necessary to install and run the MySQL database server.
You’ll also install the mysql-client package, which contains a command-line interface to the
database server that you can use to create the database objects for your Python programs.

To install the MySQL environment, you just use the apt-get program:

Click here to view code image

pi@raspberrypi ~ $ sudo apt-get update

pi@raspberrypi ~ $ sudo apt-get install mysql-client mysql-server

Watch Out!: The MySQL root User Account

During the process of installing the MySQL server package, the installation script