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

USE wb;
CREATE TABLE wbdt(
   id int, 
   countries varchar(250),
   yr year,
   gdp float,
   tourout float
);

In case you want to drop a table later on, you type in:

DROP TABLE tablename;

Now we can insert some dummy data in our newly created “wbdt” table:

INSERT INTO wbdt VALUES (0,'Country A',2016,20000,5000);

And now let’s clear the table so we can import actual data:

TRUNCATE TABLE wbdt;

To exit MySQL just type in:

quit

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:

#import the wb api
from pandas.io import wb
#get data for gdp per capita in constant dollars, and outbound turists,
#between 1995 and 2015
data = wb.download(indicator=['NY.GDP.PCAP.KD','ST.INT.DPRT'],
 country='all', start=1995, end=2015)
#keep only rows where both indicators are present
dataf=data.dropna(thresh=2)
#turn dataframe multi-index into columns
dataf.reset_index(inplace=True)

So now that our data is ready we can write it to the database:

#import sqlalchemy engine
from sqlalchemy import create_engine
#create the connection string
con=create_engine('mysql+mysqldb://wbuser:wbpwd@192.168.1.117:3306/wb',
 echo=False)
#change column names to match the ones in the db
dataf.columns = ['countries', 'yr','gdp','tourout']
#send dataframe to db table
dataf.to_sql('wbdt',con, flavor='mysql',
 if_exists='append',
 index_label='id')

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:

import pandas 
datar=pandas.read_sql('SELECT * FROM wbdt', con, index_col='id')