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

in #utopian-io6 years ago (edited)

steem-python.png

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:

Screenshot 2018-07-05 02.21.30.png

Curriculum

Set up:
Filtering
Voting
Posting
Constructing
Rewards
Transfers
Account Analysis

The code for this tutorial can be found on GitHub!

This tutorial was written by @juliank.

Sort:  

This is a very good tutorial.I'm not lost and it's very well explained.Every command has explanation which is a good point.
My Suggestions :

  • Maybe you can add your tutorial is made for linux/bash ? (apt-get)
  • Maybe add a command to find server IP ?
  • Maybe you can make the curriculum shorter (I don't know how, but it's very long ^^)

I saw you're a regular utopian author. It's very good and i want to see the next part !
Your tutorials are originals, and usefuls. This give more details than another tutorials.
You give somme screenshot and I think images are evry important in a post. So if you can add more images, do it !

Hey @zonguin
Here's a tip for your valuable feedback! @Utopian-io loves and incentivises informative comments.

Contributing on Utopian
Learn how to contribute on our website.

Want to chat? Join us on Discord https://discord.gg/h52nFrV.

Vote for Utopian Witness!

Than you for your tips, I will work on implementing them.

Loading...

Thank you for your contribution.

It is very interesting to keep all transfers in a database. Then you can do a lot of analysis of the bot data.

Your contribution has been evaluated according to Utopian policies and guidelines, as well as a predefined set of questions pertaining to the category.

To view those questions and the relevant answers related to your post, click here.


Need help? Write a ticket on https://support.utopian.io/.
Chat with us on Discord.
[utopian-moderator]

Oh you understand what is coming next ;p

It is very interesting to keep all transfers in a database. Then you can do a lot of analysis of the bot data.

Hey @steempytutorials
Thanks for contributing on Utopian.
We’re already looking forward to your next contribution!

Want to chat? Join us on Discord https://discord.gg/h52nFrV.

Vote for Utopian Witness!