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:///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
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.