Install MySQL on the Orange Pi

Install MySQL and set up a database

In this post I will show you how to install MySQL sever on the Raspberry Pi or on an Orange Pi Plus 2e. Furthermore I will show you how to read and write data from Python to a MySQL database using SQLAlchemy.

To install MySQL type in the command:

sudo apt-get install mysql-server

At some point during the installation you will be prompted to add a password for the root user:

Type in a password and remember it. Once the installation is finished you can get the version and check that the install was successful by running:

mysql --version

Next let’s log in to MySQL with:

mysql -u root -p

Type in the password. Now we can create a database. In this example I will show how to get and store data from the World Bank, so let’s call the database “wb”. Create the database with:

CREATE DATABASE wb;

Then let’s add a user to the database:

CREATE USER 'wbuser'@'localhost' IDENTIFIED BY 'wbpwd';
GRANT ALL PRIVILEGES ON wb.* TO 'wbuser'@'localhost' WITH GRANT OPTION;
CREATE USER 'wbuser'@'%' IDENTIFIED BY 'wbpwd';
GRANT ALL PRIVILEGES ON wb.* TO 'wbuser'@'%' WITH GRANT OPTION;

To drop a user just type:

DROP USER 'username'@'localhost';

Now let’s create a table for our data. We want an index column, a column for countries, one for years and two more for economic indicators (GDP per capita and number of outbound tourists).

To enable remote access from a network client to the database we need to change the MySQL configuration file. This step is only necessary if you plan to connect from a different device on the network. So open it in a text editor from it’s usual location in Debian Linux:

sudo nano /etc/mysql/my.cnf

Look for a line which contains “bind-address” and change it to the network address of your Orange or Raspberry Pi.

Now let’s download some actual data from World Bank through Python and populate the database.

Connect to MySQL from Python

We can access World Bank’s World Development Indicators data directly in Python. For more information about how to get data into Python DataFrames from different Internet sources read here. So I will download data representing the GDP per capita and number of outbound tourists for every country, between 1995 and 2005.

First you need to install SQLAlchemy and MySQLdb, you need them to connect to a database:

sudo pip install SQLAlchemy mysqldb

Then, start Python:

python

In Python, type in the following code to download data from the World Bank and store in a DataFrame:

To check if it worked, exit Python and connect to the MySQL server and run:

USE wb;
SELECT * FROM wbdt;

You will see the last rows and a row count:

Finally, if you want to retrieve data from a SQL table into a Python DataFrame, the pandas module has made it easy with the “read_sql” method. For example, using the same connection string and database as before, you can run: