Ruan Bekker's Blog

From a Curious mind to Posts on Github

Using the Python Sys Library to Read Data From Stdin

Using Python’s sys library to read data from stdin.

In this basic example we will strip our input, delimited by the comma character, add it to a list, and print it out

Python: Read Data from Standard Input

1
2
3
4
5
6
7
8
9
10
11
12
13
import sys
import json

mylist = []

data_input = sys.stdin.read()
destroy_newline = data_input.replace('\n', '')
mylist = destroy_newline.split(', ')

print("Stripping each word and adding it to 'mylist'")
print("Found: {} words in 'mylist'".format(len(mylist)))
for x in mylist:
    print("Word: {}".format(x))

We will echo three words and pipe it into our python script:

1
2
3
4
5
6
$ echo "one, two, three" | python basic-stdin.py
Stripping each word and adding it to 'mylist'
Found: 3 words in 'mylist'
Word: one
Word: two
Word: three

Setup a Postfix Relay Server That Uses SES to Relay Outbound Mail

We will setup a Postfix Relay Servcer which our clients will use to send out mail, the Postfix server will use Amazon’s SES Service to send out mail, which we will configure as a relay host in Postfix.

Setup EC2 Instance to Relay through AWS SES:

Install Postfix and SASL:

1
$ apt install postfix mailutils libsasl2-2 sasl2-bin libsasl2-modules -y

Section we need to configure in /etc/nginx/main.cf:

1
2
3
4
5
6
relayhost = [email-smtp.eu-west-1.amazonaws.com]:587
smtp_use_tls = yes
smtp_sasl_auth_enable = yes
smtp_sasl_security_options =
smtp_sasl_password_maps = hash:/etc/postfix/sasl_passwd
smtp_tls_CAfile = /etc/ssl/certs/ca-certificates.crt

Populate SASL Passwd:

1
2
$ cat /etc/postfix/sasl_passwd
[email-smtp.eu-west-1.amazonaws.com]:587    AKIAABCDEFGHIJKLM:SomeRandomSecretString

Postmap the changes:

1
$ postmap /etc/postfix/sasl_passwd

Restart Postfix:

1
$ sudo /etc/init.d/postfix restart

Test the Mail Flow:

1
2
3
4
$ echo test | mail -r ruan@ruanbekker.com -s 'ses test mail ' ruan@ruanbekker.com && tail -f /var/log/mail.log

Jul 18 11:29:06 ip-10-1-4-250 postfix/smtp[5056]: 9FDCB469AA: to=<ruan@ruanbekker.com>, relay=email-smtp.eu-west-1.amazonaws.com[52.10.20.30]:587, delay=0.29, delays=0.02/0.03/0.12/0.13, dsn=2.0.0, status=sent (250 Ok 0234567d557572f2-76f56252-0a00-4d94-af87-38bd213914d2-000000)
Jul 18 11:29:06 ip-10-1-4-250 postfix/qmgr[4392]: 9FDCB469AA: removed

If your output looks more or less like the snippet from above, your mail should be working fine.

Nginx Reverse Proxy for Elasticsearch and Kibana 5 on AWS

As up untill today, there’s currently no VPC Support for Amazon’s Elasticsearch Service.

So for scenarios where you would like to allow private network traffic to Elasticsearch is impossible straight out of the box as Amazon’s Elasticsearch Services, only sees Public Internet Traffic.

We will setup 2 configs, one for Kibana and one for Elasticsearch, each one having its own FQDN:

  • Kibana: http://kibana.domain.com
  • Elasticsearch: http://elasticsearch.domain.com

Workaround:

There’s a couple of workarounds, which includes:

  • Nginx Reverse Proxy
  • NAT Gateway
  • Allow IAM Users/Roles

Today we will tackle the Nginx Reverse Proxy Route.

The benefit of this, would be to associate an EIP to the Nginx EC2 Instnace, then whitelist your EIP with Elasticsearch, so the only traffic that will be accepted will be the traffic that is coming from the Nginx Instance. We will also apply an additional layer of security, in this case we will use HTTP Basic Authentication, then also authorize network sources on a Security Group level.

Installing Nginx:

In this case I am using Ubuntu 16.04, so we will need to install nginx and apache2-utils for creating the Basic HTTP Auth accounts.

1
2
$ apt update && apt upgrade -y
$ apt install nginx apache2-utils -y

Configure Nginx:

Our main config: /etc/nginx/nginx.conf:

/etc/nginx/nginx.conf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
user www-data;
worker_processes auto;
pid /run/nginx.pid;
error_log /var/log/nginx/error.log;

events {
  worker_connections 1024;
}

http {

  # Basic Settings
  sendfile on;
  tcp_nopush on;
  tcp_nodelay on;
  keepalive_timeout 65;
  types_hash_max_size 2048;
  server_names_hash_bucket_size 128;

  include /etc/nginx/mime.types;
  default_type application/octet-stream;

  # Logging Settings
        log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                      '$status $body_bytes_sent "$http_referer" '
                      '"$http_user_agent" "$http_x_forwarded_for"';

  access_log /var/log/nginx/access.log main;

  # Gzip Settings
  gzip on;
  gzip_disable "msie6";

  # Elasticsearch and Kibana Configs
  include /etc/nginx/conf.d/elasticsearch.conf;
  include /etc/nginx/conf.d/kibana.conf;
}

Our /etc/nginx/conf.d/elasticsearch.conf configuration:

/etc/nginx/conf.d/elasticsearch.conf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
server {

  listen 80;
  server_name elasticsearch.domain.com;

  # error logging
  error_log /var/log/nginx/elasticsearch_error.log;

  # authentication: elasticsearch
  auth_basic "Elasticsearch Auth";
  auth_basic_user_file /etc/nginx/.secrets_elasticsearch;

  location / {

    proxy_http_version 1.1;
    proxy_set_header Host https://search-elasticsearch-name.eu-west-1.es.amazonaws.com;
    proxy_set_header X-Real-IP <ELASTIC-IP>;
    proxy_set_header Connection "Keep-Alive";
    proxy_set_header Proxy-Connection "Keep-Alive";
    proxy_set_header Authorization "";

    proxy_pass https://search-elasticsearch-name.eu-west-1.es.amazonaws.com/;
    proxy_redirect https://search-elasticsearch-name.eu-west-1.es.amazonaws.com/ http://<ELASTIC-IP>/;

  }

  # ELB Health Checks
  location /status {
    root /usr/share/nginx/html/;
  }

}

Our /etc/nginx/conf.d/kibana.conf configuration:

/etc/nginx/conf.d/kibana.conf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
server {

  listen 80;
  server_name kibana.domain.com;

  # error logging
  error_log /var/log/nginx/kibana_error.log;

  # authentication: kibana
  auth_basic "Kibana Auth";
  auth_basic_user_file /etc/nginx/.secrets_kibana;

  location / {

    proxy_http_version 1.1;
    proxy_set_header Host https://search.elasticsearch-name.eu-west-1.es.amazonaws.com;
    proxy_set_header X-Real-IP <ELASTIC-IP>;
    proxy_set_header Connection "Keep-Alive";
    proxy_set_header Proxy-Connection "Keep-Alive";
    proxy_set_header Authorization "";

    proxy_pass https://search.elasticsearch-name.eu-west-1.es.amazonaws.com/_plugin/kibana/;
    proxy_redirect https://search.elasticsearch-name.eu-west-1.es.amazonaws.com/_plugin/kibana/ http://<ELASTIC-IP>/kibana/;

  }

      location ~ (/app/kibana|/app/timelion|/bundles|/es_admin|/plugins|/api|/ui|/elasticsearch) {
         proxy_pass              https://search.elasticsearch-name.eu-west-1.es.amazonaws.com;
         proxy_set_header        Host $host;
         proxy_set_header        X-Real-IP $remote_addr;
         proxy_set_header        X-Forwarded-For $proxy_add_x_forwarded_for;
         proxy_set_header        X-Forwarded-Proto $scheme;
         proxy_set_header        X-Forwarded-Host $http_host;
         proxy_set_header      Authorization  "";
    }
}

Once you have replaced the elasticsearch endpoint and your EPI values, we can go ahead and create the auth accounts.

Create User Accounts for HTTP Basic Auth

Create the 2 accounts for authentication on kibana and elasticsearch:

1
2
$ htpasswd -c /etc/nginx/.secrets_elasticsearch elasticsearch-admin
$ htpasswd -c /etc/nginx/.secrets_kibana kibana-admin

Restart Nginx:

Restart and enable Nginx on boot:

1
2
$ systemctl enable nginx
$ systemctl restart nginx

Once your Nginx Service is running, you should be able to access Kibana and Elasticsearch using the credentials that you created.

Resources:

AWS: IAM S3 Policy for Cyberduck to Allow Listing Buckets and Access to One Bucket

When using Cyberduck to access S3, and a account has restrictive policies, you may find error Listing Directory: / failed.

If you have restrictive IAM Policies in your account, this may be due to the fact that S3:ListMyBuckets is not allowed.

In this post we want to allow a user to list all buckets, so that Cyberduck can do the initial list after configuration / launch, and we would like to give the user access to their designated bucket.

Creating the IAM Policy:

We will create this IAM Policy and associate the policy to the user’s account:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "Stmt1480515305000",
            "Effect": "Allow",
            "Action": [
                "s3:ListAllMyBuckets",
                "s3:GetBucketLocation"
            ],
            "Resource": [
                "arn:aws:s3:::*"
            ]
        },
        {
            "Sid": "Stmt1480515305002",
            "Effect": "Allow",
            "Action": [
                "s3:List*",
                "s3:GetObject",
                "s3:PutObject",
                "s3:DeleteObject"
            ],
            "Resource": [
                "arn:aws:s3:::allowed-bucket",
                "arn:aws:s3:::allowed-bucket/*"
            ]
        }
    ]
}

So here we should be able to list the buckets:

1
2
3
4
5
6
$ aws --profile cyberduck s3 ls /
2017-06-08 08:27:01 allowed-bucket
2017-05-21 13:39:21 private-bucket
2016-12-21 08:23:45 confidential-bucket
2017-08-10 14:18:19 test-bucket
2016-08-03 12:38:29 datalake-bucket

Able to list inside the bucket, as well as Get, Put etc.

1
2
$ aws --profile cyberduck s3 ls allowed-bucket/
                           PRE data/

Unable to list the buckets content which is expected, as we did not mention in the policy:

1
2
3
$ aws --profile cyberduck s3 ls confidential-bucket/

An error occurred (AccessDenied) when calling the ListObjects operation: Access Denied

Resources:

Using Python for Image Analysis With Amazons Rekognition Service

Amazon’s Rekognition Service, which falls under their Artificial Intelligence tier, makes it easy to add image analysis to your applications.

Today we will use Rekognition to analyze an image, to determine the percentage of detection that the service analyzes. We will be using the Python SDK to do this.

Getting a Random Image:

So, I got this drunk guy on the couch, which I thought we could use to analyze.

Image Used: - http://imgur.com/a/CHnSu

Our Python Code:

Our code will use boto3 to use rekognition from Amazon Web Services, detects the image, and prints out the values.

Note that I am not specifying any credentials, as my credentials is configured in my local credential provider, where boto will pick it up from.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import boto3

BUCKET = "rekognition-bucket"
KEY = "images/image-02.jpg"

def detect_labels(bucket, key, max_labels=10, min_confidence=90, region="eu-west-1", profile_name="aws"):
    rekognition = boto3.client("rekognition")
    response = rekognition.detect_labels(
        Image={
        "S3Object": {
        "Bucket": BUCKET,
        "Name": KEY,
    }
        },
        MaxLabels=max_labels,
        MinConfidence=min_confidence,
    )
    return response['Labels']


for label in detect_labels(BUCKET, KEY):
    print("{Name} - {Confidence}%".format(**label))

Running the App:

Running our Python App, will result in the following:

1
2
3
4
5
6
7
8
9
10
11
$ python rekog.py
People - 98.9893875122%
Person - 98.9893951416%
Human - 98.9505844116%
Alcohol - 98.573425293%
Beer - 98.573425293%
Beer Bottle - 98.573425293%
Beverage - 98.573425293%
Bottle - 98.573425293%
Drink - 98.573425293%
Couch - 98.4713821411%

Resources:

Setup RocketChat on Docker Swarm

Rocket Chat, a Self Hosted Alternative, which is very similar to Slack.

We will setup a RocketChat Server which is hosted on Docker Swarm. In future posts, I will also go through the steps on working with the API, Custom Emoji’s etc.

Requirements:

RocketChat uses MongoDB as its Database, we will keep the database outside of our swarm, if you don’t already have a MongoDB Server in place, follow the Setup a 3 Node MongoDB post to get that sorted.

Another requirement is to have docker swarm running, alternatively, you can also follow RocketChat’s Documentation if you prefer setting it up elsewhere.

Setup Rocket Chat

We will assume MongoDB is accessible via mongodb.domain.com on port 27017, with a username and password.

Creating the RocketChat service and associate it to our appnet overlay network:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
docker service create --name rocketchat \
--replicas 1 \
--network appnet \
--env DEPLOY_METHOD=docker \
--env NODE_ENV=production \
--env PORT=3000 \
--env MONGO_URL="mongodb://mongoadmin:mongopass@mongodb.domain.com:27017/rocketchat?authSource=admin" \
--env ROOT_URL=http://rocketchat.domain.com \
--env ADMIN_USERNAME=myadmin \
--env ADMIN_PASS=secret \
--env ADMIN_EMAIL=mail@domain.com \
--env Accounts_AvatarStorePath=/app/uploads \
--secret rocketchat_secret \
rocketchat/rocket.chat

View the RocketChat Service Logs

Lets monitor the docker service logs for our rocketchat service:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
$ docker service logs -f rocketchat

rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | Using GridFS for custom sounds storage
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | Using GridFS for custom emoji storage
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | ufs: temp directory created at "/tmp/ufs"
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | System startup
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | +--------------------------------------------------------------+
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | |                        SERVER RUNNING                        |
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | +--------------------------------------------------------------+
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | |                                                              |
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | |  Rocket.Chat Version: 0.58.2                                 |
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | |       NodeJS Version: 4.8.4 - x64                            |
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | |             Platform: linux                                  |
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | |         Process Port: 3000                                   |
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | |             Site URL: http://rocketchat.domain.com           |
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | |     ReplicaSet OpLog: Disabled                               |
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | |          Commit Hash: 988103d449                             |
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | |        Commit Branch: HEAD                                   |
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | |                                                              |
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | +--------------------------------------------------------------+
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | Inserting admin user:
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | Name: Administrator
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | Email: mail@domain.com
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | Username: myadmin
rocketchat.1.lnbyfjiotqpz@docker-swarm-worker-03    | Password: secret

Now you should be able to access Rocket Chat on the ROOT_URL that you have specified.

Resources:

HTTPS Termination Using LetsEncrypt With Traefik on Docker Swarm

We will setup a HTTPS Termination on Traefik for our Java Web Application using Payara Micro, that will sit behind our Traefik proxy. In this guide, I will be using GitLab’s Private Registry for pushing my Images to.

Traefik Dockerfile:

Our Traefik Dockerfile:

Traefik Dockerfile
1
2
3
4
5
FROM traefik
ADD traefik.toml .
EXPOSE 80
EXPOSE 8080
EXPOSE 443

traefik.toml

Our Traefik config: traefik.toml

traefik.toml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
defaultEntryPoints = ["http", "https"]

[web]
address = ":8080"

[entryPoints]

[entryPoints.http]
address = ":80"

[entryPoints.https]
address = ":443"

[entryPoints.https.tls]

[acme]
email = "recipient@domain.com"
storage = "acme.json"
entryPoint = "https"
onDemand = false
OnHostRule = true

[docker]
endpoint = "unix:///var/run/docker.sock"
domain = "apps.domain.com"
watch = true
exposedbydefault = false

Build the Image:

Login to GitLab’s Registry, build and push the image:

1
2
3
$ docker login registry.gitlab.com
$ docker build -t registry.gitlab.com/<user>/<repo>/traefik:latest .
$ docker push registry.gitlab.com/<user>/<repo>/traefik:latest

Traefik:

Create the Traefik Proxy Service:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$ docker service create \
--name traefik \
--constraint 'node.role==manager' \
--publish 80:80 \
--publish 443:443 \
--publish 8080:8080 \
--mount type=bind,source=/var/run/docker.sock,target=/var/run/docker.sock \
--network appnet \
--with-registry-auth registry.gitlab.com/<user>/<repo>/traefik:latest \
--docker \
--docker.swarmmode \
--docker.domain=apps.domain.com \
--docker.watch \
--logLevel=DEBUG \
--web

Java Web Application:

Our Java Web Applications Dockerfile:

Dockerfile
1
2
FROM payara/micro
COPY app.war /opt/payara/deployments/app.war

Build and Push the Image to our GitLab Registry:

1
2
$ docker build -t registry.gitlab.com/<user>/<repo>/java_web:latest .
$ docker push registry.gitlab.com/<user>/<repo>/java_web:latest

Create the Java Web Application on Docker Swarm, specifiying our Host, and also a PathPrefix, so that the Traefik Proxy can accept requests for the Hostname, and anything from /app/

1
2
3
4
5
6
$ docker service create \
--name java_web \
--label 'traefik.port=8080' \
--label traefik.frontend.rule="Host:apps.domain.com; PathPrefix: /app/" \
--network appnet \
--with-registry-auth registry.gitlab.com/<user>/<repo>/java_web:latest

Now we should be able to access our Web Application on https://apps.domain.com/app/

Resources:

Run Kibana on Docker Swarm With Traefik

We will create a Kibana Service on Docker Swarm, that will sit behind a Traefik Reverse Proxy.

Create the Overlay Network:

1
$ docker network create --driver overlay appnet

Create the Traefik Service:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
docker service create \
--name traefik \
--constraint 'node.role==manager' \
--publish 80:80 \
--publish 443:443 \
--publish 8080:8080 \
--mount type=bind,source=/var/run/docker.sock,target=/var/run/docker.sock \
--network appnet \
traefik:camembert \
--docker --docker.swarmmode  \
--docker.domain=apps.domain.com \
--docker.watch \
--logLevel=DEBUG \
--web

Set DNS:

Set a wildcard *.apps.domain.com to resolve to apps.domain.com, where apps.domain.com resolves to your swarm addresses

Create Kibana:

Create a Kibana Service and set the ELASTICSEARCH_URL to your External Elasticsearch Endpoint, take note that it uses port 9200 by default.

1
2
3
4
5
6
$ docker service create \
--name kibana \
--label 'traefik.port=5601' \
--network appnet \
--env KIBANA_ELASTICSEARCH_URL=elasticsearch.domain.com \
bitnami/kibana

Access Kibana:

Your Kibana endpoint will be available at: http://kibana.apps.domain.com

Resources:

Using Python to Write Data to a MySQL Database

From our previous post, we used python to read data from mysql. In this post we will be using the random library to write random data into mysql.

We will define our lists with the categorized data, and then using for loop, to write data into our mysql database:

Create The Database:

Using Python to Create the Database:

1
2
3
4
5
6
>>> conn = pdb.connect(host=db_host, user=db_username, passwd=db_password)
>>> cursor = conn.cursor()
>>> cursor.execute("CREATE DATABASE testdb1")
1L
>>> cursor.execute("CREATE TABLE testdb1.myusers(name VARCHAR(50), surname VARCHAR(50), countries VARCHAR(50), job VARCHAR(20), os VARCHAR(20), car VARCHAR(20))")
0L

Now to list our databases:

1
2
3
4
5
6
7
8
9
10
11
12
>>> cursor.execute("show databases")
12L

>>> dbs = cursor.fetchall()
>>> for x in dbs:
...     print(x)
...

('information_schema',)
('mysql',)
('performance_schema',)
('testdb1',)

Python Code to Write to MySQL

We will create a mysql_write.py file, with the following contents to define our random data that we will write to our mysql database. The config module can be found from this post.

mysql_write.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import MySQLdb as pdb
from config import credentials as secrets
import random
import datetime

db_host = secrets['mysql']['host']
db_username = secrets['mysql']['username']
db_password = secrets['mysql']['password']
db_name = secrets['mysql']['database']

for x in range(10):
    a = random.choice(names)
    b = random.choice(surnames)
    c = random.choice(countries)
    d = random.choice(job)
    e = random.choice(os)
    f = random.choice(car)

    cursor.execute("INSERT INTO myusers values('{name}', '{surname}', '{countries}', '{job}', '{os}', '{car}');".format(name=a, surname=b, countries=c, job=d, os=e, car=f))

conn.commit()
conn.close()

After running the file: python mysql_write.py we should have 10 records in our database.

Reading the Data from MySQLL

To verify that the data is in our MySQL Database, lets logon to our mysql database:

1
$ mysql -u root -p
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select * from testdb1.myusers;
+----------+----------+-----------+----------------+---------+---------------+
| name     | surname  | countries | job            | os      | car           |
+----------+----------+-----------+----------------+---------+---------------+
| James    | James    | New York  | Waiter         | Mac     | Volkswagen    |
| Jennifer | Smith    | New York  | Scientist      | Windows | Audi          |
| Michelle | Jacobs   | Italy     | Police Officer | Mac     | Ford          |
| Michelle | Anderson | Italy     | Waiter         | Windows | Ford          |
| Jennifer | Smith    | England   | Doctor         | Windows | Toyota        |
| Peter    | Jacobs   | England   | IT             | Windows | BMW           |
| Samantha | James    | England   | Doctor         | Mac     | Mazda         |
| Frank    | Phillips | England   | IT             | Mac     | BMW           |
| Samantha | James    | England   | Banker         | Linux   | Mercedez-Benz |
| Peter    | Anderson | Sweden    | Doctor         | Windows | BMW           |
+----------+----------+-----------+----------------+---------+---------------+

Next, lets use Python to do the same, create a file mysql_read.py with the following content:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import MySQLdb as pdb
from config import credentials as secrets

db_host = secrets['mysql']['host']
db_username = secrets['mysql']['username']
db_password = secrets['mysql']['password']
db_name = secrets['mysql']['database']

conn = pdb.connect(host=db_host, user=db_username, passwd=db_password, db=db_name)
cursor = conn.cursor()

cursor.execute("select * from myusers")
read = cursor.fetchall()

for x in read:
    print(x)

conn.close()

Running the Python file, to read the data:

1
2
3
4
5
6
7
8
9
10
11
12
$ python mysql_read.py

('James', 'James', 'New York', 'Waiter', 'Mac', 'Volkswagen')
('Jennifer', 'Smith', 'New York', 'Scientist', 'Windows', 'Audi')
('Michelle', 'Jacobs', 'Italy', 'Police Officer', 'Mac', 'Ford')
('Michelle', 'Anderson', 'Italy', 'Waiter', 'Windows', 'Ford')
('Jennifer', 'Smith', 'England', 'Doctor', 'Windows', 'Toyota')
('Peter', 'Jacobs', 'England', 'IT', 'Windows', 'BMW')
('Samantha', 'James', 'England', 'Doctor', 'Mac', 'Mazda')
('Frank', 'Phillips', 'England', 'IT', 'Mac', 'BMW')
('Samantha', 'James', 'England', 'Banker', 'Linux', 'Mercedez-Benz')
('Peter', 'Anderson', 'Sweden', 'Doctor', 'Windows', 'BMW')

Using Python to Read Data From a MySQL Database

Wanted to use Python to read some data from MySQL and stumbled upon a couple of great resources, which I noted some of my output below:

Install Dependencies:

1
2
3
$ apt install python-dev libmysqlclient-dev python-setuptools gcc
$ easy_install pip
$ pip install MySQL-python

Download Some Sample Data:

Download the world dataset for MySQL:

1
2
$ wget http://downloads.mysql.com/docs/world.sql.zip
$ unzip world.sql.zip

Create Database:

Create the Database in MySQL for the dataset that we downloaded:

1
$ mysql -u root -p -e'CREATE DATABASE world;'

Import Data:

Import the data into the world database:

1
$ mysql -u root -p world < world.sql

Create the MySQL Credentials File:

Create a config.py file and populate the credentials in a dictionary:

1
2
3
4
5
6
7
8
credentials = {
  'mysql': {
      'host': 'localhost',
      'username': 'root',
      'password': 'password',
      'database': 'world'
  }
}

Run Queries from Python:

Enter the Python interpreter and run some queries:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
>>> import MySQLdb as pdb
>>> from config import credentials as secrets

# assignments 
>>> db_host = secrets['mysql']['host']
>>> db_username = secrets['mysql']['username']
>>> db_password = secrets['mysql']['password']
>>> db_name = secrets['mysql']['database']

# create a connection to the database
>>> conn = pdb.connect(host=db_host, user=db_username, passwd=db_password, db=db_name)

# create a object for the queries we will be using
>>> cursor = conn.cursor()

# execute the query
>>> cursor.execute('select continent, name from country where continent = "Africa" limit 5')
5L

# fetch the results by assigning it to the results object:
>>> results = cursor.fetchall()

# loop and print results:
>>> for x in results:
...     print(x)
...
('Africa', 'Angola')
('Africa', 'Burundi')
('Africa', 'Benin')
('Africa', 'Burkina Faso')
('Africa', 'Botswana')

# close the connection
>>> conn.close()

Graphing Results to Plotly:

A great blogpost that shows how to use this data to graph the results to plotly

Resources: