<center>![steem-python.png](https://res.cloudinary.com/hpiynhbhq/image/upload/v1515886103/kmzfcpvtzuwhvqhgpyjp.png)</center>

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](https://steemit.com/utopian-io/@steempytutorials/part-23-retrieve-and-process-full-blocks-from-the-steem-blockchain) 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](https://github.com/amosbastian/steempy-tutorials/tree/master/part_26). 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](https://steemit.com/utopian-io/@amosbastian/how-to-install-steem-python-the-official-steem-library-for-python) 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](https://cdn.steemitimages.com/DQmX46eaUjaz4L7JRuWaHQq6gRGbnaBNSnzdUKrJcJT1j7P/Screenshot%202018-07-05%2002.21.30.png)

#### Curriculum
##### Set up:
- [Part 0: How To Install Steem-python, The Official Steem Library For Python](https://steemit.com/utopian-io/@amosbastian/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](https://steemit.com/utopian-io/@steempytutorials/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](https://steemit.com/utopian-io/@steempytutorials/part-2-how-to-stream-and-filter-the-blockchain-using-steem-python)
- [Part 6: How To Automatically Reply To Mentions Using Steem-Python](https://steemit.com/utopian-io/@steempytutorials/part-6-how-to-automatically-reply-to-mentions-using-steem-python)
- [Part 23: Part 23: Retrieve And Process Full Blocks From The Steem Blockchain](https://steemit.com/utopian-io/@steempytutorials/part-23-retrieve-and-process-full-blocks-from-the-steem-blockchain)
- [Part 24: An In Dept Look At Steem Operation Types Part I](https://steemit.com/utopian-io/@steempytutorials/part-24-an-in-dept-look-at-steem-operation-types-part-i)
##### Voting
- [Part 3: Creating A Dynamic Autovoter That Runs 24/7](https://steemit.com/utopian-io/@steempytutorials/part-3-creating-a-dynamic-upvote-bot-that-runs-24-7-first-weekly-challenge-3-steem-prize-pool)
- [Part 4: How To Follow A Voting Trail Using Steem-Python](https://steemit.com/utopian-io/@steempytutorials/part-4-how-to-follow-a-voting-trail-using-steem-python)
- [Part 8: How To Create Your Own Upvote Bot Using Steem-Python](https://steemit.com/utopian-io/@steempytutorials/part-8-how-to-create-your-own-upvote-bot-using-steem-python)
- [Part 25: Create A Bidbot With Steem-Python](https://steemit.com/utopian-io/@steempytutorials/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](https://steemit.com/utopian-io/@steempytutorials/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](https://steemit.com/utopian-io/@steempytutorials/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](https://steemit.com/utopian-io/@steempytutorials/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](https://steemit.com/utopian-io/@steempytutorials/how-to-calculate-a-post-s-total-rewards-using-steem-python)
- [Part 12: How To Estimate Curation Rewards Using Steem-Python](https://steemit.com/utopian-io/@steempytutorials/part-12-how-to-estimate-curation-rewards)
- [Part 14: How To Estimate All Rewards In Last N Days Using Steem-Python](https://steemit.com/utopian-io/@steempytutorials/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](https://steemit.com/utopian-io/@steempytutorials/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](https://steemit.com/utopian-io/@steempytutorials/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](https://steemit.com/utopian-io/@steempytutorials/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](https://steemit.com/utopian-io/@steempytutorials/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](https://steemit.com/utopian-io/@steempytutorials/part-18-how-to-analyse-an-account-s-resteemers)
---

The code for this tutorial can be found on [GitHub](https://github.com/amosbastian/steempy-tutorials/tree/master/part_26)!

This tutorial was written by @juliank.