Part 26: Retrieving Data From The STEEM Blockchain And Storing Into A MySQL Database

This tutorial is part of a series where different aspects of programming with steem-python are explained. Links to the other tutorials can be found in the curriculum section below. This part will explain how to set up a MySQL database interface with the STEEM Blockchain. And in addition adding the PHPMyAdmin user interface.

---

What will I learn

• Install Apache
• Install MySQL
• Install PHPMyAdmin
• Install MySQL for Python
• Configure the database
• Adding transfers to the database

Requirements

• Python3.6
• steem-python
• Linux/bash

Difficulty

• basic

---

Repository

https://github.com/steemit/steem-python

Tutorial

Preface

Tutorial 23 discussed how to retrieve blocks from the STEEM Blockchain and interact with the transactions in these blocks. In certain cases its preferable to store data locally for faster accessibility. This tutorial will explain how to set up Apache, MySQL and PHPMyAdmin to store and access this data easily. The code in this tutorial will scan the blockchain for transfers in the specified block range and store these in a local MySQL database.

Setup

Download the files from Github. There 2 are files get_blocks.py which contains the interaction with the STEEM Blockchain and db.py which contains interactions with the MySQL database. This tutorial was tested on a clean ubuntu 16.04 installation with Anaconda and Steem-Python already installed. Refer to this Tutorial for more information on how to do that. It is also assumed the code is running on a remote server. get_blocks.py takes two arguments, the starting_block from which to start from and block_count to set the amount of blocks to retrieve.

Run scripts as following:
> python bidbot.py 23822234 100

Install Apache

Install apache as follows:

sudo apt-get update
sudo apt-get install apache2

Set the global ServerName by adding ServerName <server_ip_adress> to the end of the file.:

sudo nano /etc/apache2/apache2.conf

Check if done correct with:

sudo apache2ctl configtest

Output should be:

Output
Syntax OK

Restart the server for changes to take affect:

sudo systemctl restart apache2

In case of using a firewall add an exception for apache:

sudo ufw allow in "Apache Full"

Install MySQL

Install MySQL as follows. Go through the installation, you will be asked to set a root user password. The default settings are oke.:

sudo apt update
sudo apt install mysql-server

Optional but good practice. Go through the each option and select your preference:

sudo mysql_secure_installation

Install PHPMyAdmin

Install as follows, be sure to select apache2 by pressing space:

sudo apt-get update
sudo apt-get install phpmyadmin php-mbstring php-gettext

Enable the mcrypt and mbstring extensions:

sudo phpenmod mcrypt
sudo phpenmod mbstring

Restart apache:

sudo systemctl restart apache2

You can check if its working by go to the following url:

http://<server_ip_adress>/phpmyadmin

Install mysql for python

Install the Python and MySQL development headers and libraries:

sudo apt-get install python-dev libmysqlclient-dev

For python3:

sudo apt-get install python3-dev

Install mysql for python:

pip install mysqlclient

Configure the database

With everything installed the database can be set up. A new user test will be made and a database called steem will be created in which a table transfer will be made. This table will have columns for block, index, timestamp, to, from, amount and memo. The settings below are compatible with the provide code, however tweaking and adjusting for your own preferences is advised.

Login as the root user:

mysql -u root -p

Create a test account, this account is used in the code:

GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' IDENTIFIED BY 'Test!234#';

Create a new database called steem:

CREATE DATABASE steem;

Create a table tranfers which will be used to store the transfers:

CREATE TABLE `steem`.`transfers` ( `id` INT NOT NULL AUTO_INCREMENT , `block` INT NOT NULL , `index` INT NOT NULL , `timestamp` TIMESTAMP NOT NULL , `to` TEXT NOT NULL , `from` TEXT NOT NULL , `amount` TEXT NOT NULL , `memo` TEXT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

Set the character set of this table to work together with emojis:

ALTER TABLE transfers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

Exit mysql:

\q

Adding transfers to the database

All transactions in each block are checked to be of the type transfer, if so they are processed to be stored into the database.

for transaction in block['transactions']:
    if transaction['operations'][0][0] == self.tag:
        self.process_transaction(index,
                                 block,
                                 transaction['operations']
                                 [0][1])

All the data wanted for storage is retrieved from the block and transaction and inserted into the database.

def process_transaction(self, index, block, operation):
    date = block['timestamp']
    to = operation['to']
    user = operation['from']
    amount = operation['amount']
    memo = operation['memo']

    db.insert_selection(self.block, index, date, to, user, amount, memo)

The query specifies the table to use and which variables will be stored.

def insert_selection(block, index, timestamp, to, user, amount, memo):

    query = "INSERT INTO `transfers` (`block`, `index`, `timestamp`,`to`, `from`, `amount`, `memo`)" \
            " VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}');".format(block, index, timestamp, to, user, amount, memo)

This part is where user credentials are set and the database itself. localhost refers to storing the data locally.

    try:
        db = MySQLdb.connect(host="localhost",
                             user="test",
                             passwd="Test!234#",
                             db="steem")

Since STEEM allows for emojis to be used it is important to set everything to utf8mb4.

        db.set_character_set('utf8mb4')

        cur = db.cursor()
        cur.execute('SET NAMES utf8mb4;')
        cur.execute('SET CHARACTER SET utf8mb4;')
        cur.execute('SET character_set_connection=utf8mb4;')
        cur.execute(query)

        db.commit()

    except Exception as e:
        print('Error:', e)

    finally:
        cur.close()
        db.close()

Running the script

In case LC_ALL is not set:

export LC_ALL="en_US.UTF-8"

With everything set up all is left is running the code. Doing so will start retrieving the blocks and look for transfers in each block. Each transfer is then stored in the local database.

python get_blocks.py 23892389 100
Booted
Connected to: https://api.steemit.com

Block:  23892389

Block:  23892390

Block:  23892391

Block:  23892392

.
.
.
.
Block:  23892488

Now head to http://<server_ip_adress>/phpmyadmin and it should look something like this:

Curriculum

Set up:

• Part 0: How To Install Steem-python, The Official Steem Library For Python
• Part 1: How To Configure The Steempy CLI Wallet And Upvote An Article With Steem-Python

Filtering

• Part 2: How To Stream And Filter The Blockchain Using Steem-Python
• Part 6: How To Automatically Reply To Mentions Using Steem-Python
• Part 23: Part 23: Retrieve And Process Full Blocks From The Steem Blockchain
• Part 24: An In Dept Look At Steem Operation Types Part I

Voting

• Part 3: Creating A Dynamic Autovoter That Runs 24/7
• Part 4: How To Follow A Voting Trail Using Steem-Python
• Part 8: How To Create Your Own Upvote Bot Using Steem-Python
• Part 25: Create A Bidbot With Steem-Python

Posting

• Part 5: Post An Article Directly To The Steem Blockchain And Automatically Buy Upvotes From Upvote Bots
• Part 7: How To Schedule Posts And Manually Upvote Posts For A Variable Voting Weight With Steem-Python

Constructing

• Part 10: Use Urls To Retrieve Post Data And Construct A Dynamic Post With Steem-Python

Rewards

• Part 9: How To Calculate A Post's Total Rewards Using Steem-Python
• Part 12: How To Estimate Curation Rewards Using Steem-Python
• Part 14: How To Estimate All Rewards In Last N Days Using Steem-Python

Transfers

• Part 11: How To Build A List Of Transfers And Broadcast These In One Transaction With Steem-Python
• Part 13: Upvote Posts In Batches Based On Current Voting Power With Steem-Python

Account Analysis

• Part 15: How To Check If An Account Is Following Back And Retrieve Mutual Followers/Following Between Two Accounts
• Part 16: How To Analyse A User's Vote History In A Specific Time Period Using Steem-Python
• Part 18: How To Analyse An Account's Resteemers Using Steem-Python

---

The code for this tutorial can be found on GitHub!

This tutorial was written by @juliank.