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.
steem-pythonhttps://github.com/steemit/steem-python
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.
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 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 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 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:///phpmyadmin
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
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
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()
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:
The code for this tutorial can be found on GitHub!
This tutorial was written by 