Category Archives: Software development

News from the blog 2021-09-07

  • Blocking Ip addresses in the firewall

I’ve been blocking entire ranges from Cloud Providers, as some of their Ip’s were being used to try to hack/abuse the blog.

After some time blocking individual Ip’s, I opted for being some more effective and blocking /24 (the class C for the offending Ip).

If you work for a CSP and you can’t see my blog from your range, this is the reason.

  • I have updated my two Python books.

For Python 3 simple exercises for beginners I’ve added this new content:

v.0.29 and v.0.28 Python 3 Simple Exercises for Beginners
Added a new section Games, with a first game “Guess my number”

Provided a solution for the recipe Exercise 3: Create a function that will ask a user for a number from Keyboard input, and return the result, only when the value is between the accepted ranges.

Added two new questions to the Quiz section.

Fixed a docstring in Recipes Exercise 4, referring to a String return value which it was an Integer.

Added an exercise for retrieving a JSON with your public Ip

Added a new exercise for converting bytes to kilobytes with two decimal positions.

Added a new exercise / recipe to SSH to a Server with Username and Password and execute a command, using the Paramiko library.

For Python 3 Combat Guide I have added this new content:

v.1.02 Python 3 Combat Guide

Added a new exercise / recipe to SSH to a Server with Username and Password and execute a command using Paramiko library. I added two examples executing commands uptime and df -h /

Show an alternative way to run Flask apps.

Added new interesting packages.

As long as covid is active I plan to keep the minimum price of each of my books at the minimum accepted by LeanPub which is USD $5.

I also enable bundles and enable LeanPub to make punctual discounts to make them even more affordable to humble pockets.

  • I’m going to teach an initiation to programming class, a live Zoom session of 1 hour, plus 15 minutes for questions, for Free. It will be a very basic starting class for absolute beginners.

It will be performed at a time around 19:00 Irish time, wich is 11:00 AM in Irivine, CA time, to maximize the opportunities for people to assist.

If you would like to join, write me an email:

There are not many spots available, but if there is no room for you this time I may contact you for the next time.

This version adds class StringUtils, and a set of methods to perform different useful tasks with Strings, like converting a number to the biggest unit with sense, like a large number of bytes to PB, a smaller one to TB, or GB, MB, KB, justifying strings to the right or to the left, and cap the number of chars to a specified one, etc…

I have many libraries that I’ve been building across the years, and I’m liberating them as Open Source, as soon as I have time to make sure that are compatible with Python 3.5 or superior (and with Python 2.6 when possible), and I have time to add a decent Unit Testing Code Coverage.

I try to release libraries that have no other dependencies. After that I’ll start releasing my libs that have dependencies, like to work with MySQL, SQLite, web scrapping, etc…

Have a cheap Ubuntu in your Windows or Mac with Docker

I had this idea after one my Python and Linux students with two laptops, a Mac OS X and a Windows one explained me that the Mac OS X is often taken by their daughters, and that the Windows 10 laptop has not enough memory to run PyCharm and Virtual Box fluently. She wanted to have a Linux VM to practice Linux, and do the Bash exercises.

So this article explains how to create a Ubuntu 20.04 LTS Docker Container, and execute a shell were you can practice Linux, Ubuntu, Bash, and you can use it to run Python, Apache, PHP, MySQL… as well, if you want.

You need to install Docker for Windows of for Mac:

Docker for Windows is very handy and visual

Just pay attention to your type of processor: Mac with Intel chip or Mac with apple chip.

The first thing is to create the Dockerfile.

FROM ubuntu:20.04


ARG DEBIAN_FRONTEND=noninteractive

RUN apt update && \
    apt install -y vim python3-pip &&  \
    apt install -y net-tools mc htop less strace zip gzip lynx && \
    pip3 install pytest && \
    apt-get clean

RUN echo "#!/bin/bash\nwhile [ true ]; do sleep 60; done" > /root/; chmod +x /root/

CMD ["/root/"]

So basically the file named Dockerfile contains all the blueprints for our Docker Container to be created.

You see that I all the installs and clean ups in one single line. That’s because Docker generates a layer of virtual disk per each line in the Dockerfile. The layers are persistent, so even if in the next line we delete the temporary files, the space used will not be recovered.

You see also that I generate a Bash file with an infinite loop that sleeps 60 seconds each loop and save it as /root/ This is the file that later is called with CMD, so basically when the Container is created will execute this infinite loop. Basically we give to the Container a non ending task to prevent it from running, and exiting.

Now that you have the Dockerfile is time to build the Container.

For Mac open a terminal and type this command inside the directory where you have the Dockerfile file:

sudo docker build -t cheap_ubuntu .

I called the image cheap_ubuntu but you can set the name that you prefer.

For Windows 10 open a Command Prompt with Administrative rights and then change directory (cd) to the one that has your Dockerfile file.

docker.exe build -t cheap_ubuntu .
Image being built… (some data has been covered in white)

Now that you have the image built, you can create a Container based on it.

For Mac:

sudo docker run -d --name cheap_ubuntu cheap_ubuntu

For Windows (you can use docker.exe or just docker):

docker.exe run -d --name cheap_ubuntu cheap_ubuntu

Now you have Container named cheap_ubuntu based on the image cheap_ubuntu.

It’s time to execute an interactive shell and be able to play:

sudo docker exec -it cheap_ubuntu /bin/bash

For Windows:

docker.exe exec -it cheap_ubuntu /bin/bash
Our Ubuntu terminal inside Windows

Now you have an interactive shell, as root, to your cheap_ubuntu Ubuntu 20.04 LTS Container.

You’ll not be able to run the graphical interface, but you have a complete Ubuntu to learn to program in Bash and to use Linux from Command Line.

You will exit the interactive Bash session in the container with:


If you want to stop the Container:

sudo docker stop cheap_ubuntu

Or for Windows:

docker.exe stop cheap_ubuntu

If you want to see what Containers are running do:

sudo docker ps

Released Python CarlesLibs version 0.99.2

I’ve updated my pypi carleslibs package to version 0.99.2.

The addition I made to this version is StringUtils class which offer functionalities for handling amount conversions (to different units), number formatting, string formatting and align (left, right…). I added a 85% of Unit Testing Code Coverage.

Here you have some general information about how to install and how to use the package:

News of the blog 2021-08-16

  • I completed my ZFS on Ubuntu 20.04 LTS book.
    I had an error in an actual hard drive so I added a Troubleshooting section explaining how I fixed it.
  • I paused for a while the advance of my book Python: basic exercises for beginners, as my colleague Michela is translating it to Italian. She is a great Engineer and I cannot be more happy of having her help.
  • I added a new article about how to create a simple web Star Wars game using Flask.
    As always, I use Docker and a Dockerfile to automate the deployment, so you can test it without messing with your local system.
    The code is very simple and easy to understand.
mysql> UPDATE wp_options set option_value='blog.carlesmateo.local' WHERE option_name='siteurl';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

This way I set an entry in /etc/hosts and I can do all the tests I want.

  • I added a new section to the blog, is a link where you can see all the articles published, ordered by number of views.

Is in the main page, just after the recommended articles.
Here you can see the source code.

  • I removed the Categories:
    • Storage
      • ZFS
  • In favor of:
    • Hardware
      • Storage
        • ZFS
  • So the articles with Categories in the group deleted were reassigned the Categories in the second group.
  • Visually:
    • I removed some annoying lines from the Quick Selection access.
      They came from inherited CSS properties from my WordPress, long time customized, and I created new styles for this section.
    • I adjusted the line-height to avoid separation between lines being too much.
  • I added a link in the section of Other Engineering Blogs that I like, to the great site, author of many super cool WordPress plugins.

My PHP Script to see WordPress Posts and Views ordered by Views

This article shows the code for my PHP program posts_and_views.php that you can see at the bottom of in the Quick Selection bar.

Page rendered of all my Published Posts sorted by Views

So I wrote this small PHP Code to display the number of Post Views recorded in my WordPress Database, sorted by Views in descending order.

In other words, sorted from Most Viewed.

Please note: In order to track the post views I use the excellent plugin WP-PostViews by Lester ‘GaMerZ’ Chan

Here is the code for posts_and_views.php

As you can see I use wp-config.php file to get the Database Settings (username, password, database) but I don’t use the WordPress Engine, it is just a stand alone PHP. So I wrote my own function to get the slug based on the category name. I believe the slug for it is in the Database and I could have added this as a SubQuery with JOINs, which would be better, but I wanted to keep the Database workload lightweight and especially, I did not want to invest more time investigating how the get the slug.

As I tested my function I didn’t find any Category failing but I saw that I had the Category Storage repeated in two different structure tree in Categories, and I will only always link to just the first one (So I was not linkin to the slug storage-2). I fixed that Category that I found repeated but to be honest if this script was a commercial solution or an Open Source solution properly maintained rather than just a sample, I would update it to have Categories and Tags’ slugs coming from the Database.

I would probably make it work with a Cron that would generate a cached page, updated every hour or every ten minutes. I did this in other projects I worked or in my PHP Framework Catalonia Framerwork.

But honestly, the load of the page does not justify a major effort in optimizing here, in this case.

<!DOCTYPE html>
        .first {

            background-color: blue;
            padding: 12px;
        .second {
            background-color: rgba(254, 253, 252, 0.7);
            text-align: center;
            padding:20px 0;
            font-size: 20px;

        .centered {
            text-align: center;

        /* unvisited link */
        a:link {
            color: blue;
            text-decoration: none;

        /* visited link */
        a:visited {
            color: blue;
            text-decoration: none;

        /* mouse over link */
        a:hover {
            color: #00A8EF;
            text-decoration: underline;

        /* selected link */
        a:active {
            color: blue;

include "wp-config.php";

$s_site_name = "Carles Mateo's blog";
$s_site_link = "";

function get_category_slug($s_text) {
    $s_output_text = strtolower($s_text);
    $s_output_text = str_replace(" ", "-", $s_output_text);

    return $s_output_text;

<h1><a href="<?php print $s_site_link; ?>"><?php print($s_site_name); ?></a></h1>

$s_sort = "views DESC, post_date, post_title DESC";

if (array_key_exists("sort", $_GET)) {
    if ($_GET["sort"] == "date") {
        $s_sort = "post_date, views, post_title";

$s_servername = "localhost";
$s_database = DB_NAME;
$s_username = DB_USER;
$s_password = DB_PASSWORD;

// Create connection
$o_conn = new mysqli($s_servername, $s_username, $s_password, $s_database);

// Check connection
if ($o_conn->connect_error) {
    die("Connection failed: " . $o_conn->connect_error);

mysql> DESCRIBE wp_posts;
| Field                 | Type            | Null | Key | Default             | Extra          |
| ID                    | bigint unsigned | NO   | PRI | NULL                | auto_increment |
| post_author           | bigint unsigned | NO   | MUL | 0                   |                |
| post_date             | datetime        | NO   |     | 0000-00-00 00:00:00 |                |
| post_date_gmt         | datetime        | NO   |     | 0000-00-00 00:00:00 |                |
| post_content          | longtext        | NO   |     | NULL                |                |
| post_title            | text            | NO   |     | NULL                |                |
| post_excerpt          | text            | NO   |     | NULL                |                |
| post_status           | varchar(20)     | NO   |     | publish             |                |
| comment_status        | varchar(20)     | NO   |     | open                |                |
| ping_status           | varchar(20)     | NO   |     | open                |                |
| post_password         | varchar(255)    | NO   |     |                     |                |
| post_name             | varchar(200)    | NO   | MUL |                     |                |
| to_ping               | text            | NO   |     | NULL                |                |
| pinged                | text            | NO   |     | NULL                |                |
| post_modified         | datetime        | NO   |     | 0000-00-00 00:00:00 |                |
| post_modified_gmt     | datetime        | NO   |     | 0000-00-00 00:00:00 |                |
| post_content_filtered | longtext        | NO   |     | NULL                |                |
| post_parent           | bigint unsigned | NO   | MUL | 0                   |                |
| guid                  | varchar(255)    | NO   |     |                     |                |
| menu_order            | int             | NO   |     | 0                   |                |
| post_type             | varchar(20)     | NO   | MUL | post                |                |
| post_mime_type        | varchar(100)    | NO   |     |                     |                |
| comment_count         | bigint          | NO   |     | 0                   |                |

mysql> describe wp_postmeta;
| Field      | Type            | Null | Key | Default | Extra          |
| meta_id    | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| post_id    | bigint unsigned | NO   | MUL | 0       |                |
| meta_key   | varchar(255)    | YES  | MUL | NULL    |                |
| meta_value | longtext        | YES  |     | NULL    |                |

$s_sql = "SELECT DISTINCT post_title, post_content,
                 (SELECT CAST(meta_value AS SIGNED) FROM wp_postmeta WHERE wp_postmeta.meta_key = 'views' AND wp_postmeta.post_id = wp_posts.ID) AS 'views',
                 (SELECT group_concat( separator ',') 
                         FROM wp_terms
                  INNER JOIN wp_term_taxonomy 
                          ON wp_terms.term_id = wp_term_taxonomy.term_id
                  INNER JOIN wp_term_relationships wpr 
                          ON wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
                         taxonomy= 'category' AND wp_posts.ID = wpr.object_id
                ) AS 'Categories',
                (SELECT group_concat( separator ', ') 
                        FROM wp_terms
                 INNER JOIN wp_term_taxonomy 
                         ON wp_terms.term_id = wp_term_taxonomy.term_id
                 INNER JOIN wp_term_relationships wpr 
                         ON wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
                        taxonomy= 'post_tag' AND wp_posts.ID = wpr.object_id
                ) AS 'Tags',
               ID, post_name, post_date, post_modified, post_type
          FROM wp_posts
                post_type = 'post' AND post_status = 'publish'
          ORDER BY

$o_result = $o_conn->query($s_sql);

if ($o_result->num_rows > 0) {
    ?><table style="border:1px solid">
    <tr class="first"><th>Title</th><th style="min-width:100px">Views</th><th style="min-width:150px">Creation Date</th><th>Categories</th><th>Tags</th></tr>

    $i_total_views = 0;
    $i_articles = 0;

    // output data of each row
    while($row = $o_result->fetch_assoc()) {
        $s_style='style="border:1px solid"';
        $s_url = $row['post_name'];
        print("<td $s_style>");
        print('<a href="'.$s_url.'" target="_blank">');
        print('<td class="centered" '.$s_style.'>');
        print("<td $s_style>");
        print("<td $s_style>");
        $s_categories = $row["Categories"];
        $a_categories = explode (",", $s_categories);
        $s_categories_content = "";
        foreach($a_categories as $s_category) {
            $s_category_slug = "/category/".get_category_slug($s_category)."/";
            $s_categories_content = $s_categories_content .'<a href="'.$s_category_slug.'" target="_blank">';
            $s_categories_content = $s_categories_content .$s_category;
            $s_categories_content = $s_categories_content ."</a>, ";

        if (strlen($s_categories_content) > 0) {
            $s_categories_content = substr($s_categories_content, 0, -2);

        print("<td $s_style>");
        // $row["post_content"];
        $i_total_views = $i_total_views + intval($row["views"]);
        echo "</tr>";
    } ?></table><?php
    print("<strong>Total articles:</strong> ".number_format($i_articles)." <strong>Total Views:</strong> ".number_format($i_total_views));
} else {
    echo "<p>0 results</p>";


A sample Flask application

Today I bring you a game made with Python and Flask extracted from my book Python 3 Combat Guide.

It is a very simple game where you have to choose what Star wars robot you prefer.

Then an internal counter, kept in a static variable, is updated.

I display the time as well, to show the use of a in import and dynamic contents printed as well.

I added a Dockerfile and a bash script to build the Docker Image, so you can run the Docker Container without installing anything in your computer.

You can download the code from here:

Or clone the project:

git clone

Then build the image with the script I provided:

sudo ./ 

After Docker Image flask_app is built, you can run a Docker Container based on it with:

sudo docker run -d -p 5000:5000 --name flask_app flask_app

After you’re done, in order to stop the Container type:

sudo docker stop flask_app

Here is the source code of the Python file

# Author: Carles Mateo
# Creation Date: 2020-05-10 20:50 GMT+1
# Description: A simple Flask Web Application
#              Part of the samples of
#              More source code for the book at

from flask import Flask
import datetime

def get_datetime(b_milliseconds=False):
    Return the datetime with miliseconds in format YYYY-MM-DD HH:MM:SS.xxxxx
    or without milliseconds as YYYY-MM-DD HH:MM:SS
    if b_milliseconds is True:
        s_now = str(
        s_now = str("%Y-%m-%d %H:%M:%S"))

    return s_now

app = Flask(__name__)

# Those variables will keep their value as long as Flask is running
i_votes_r2d2 = 0
i_votes_bb8 = 0

def page_root():
    s_page = "<html>"
    s_page += "<title>My Web Page!</title>"
    s_page += "<body>"
    s_page += "<h1>Time now is: " + get_datetime() + "</h1>"
    s_page += """<h2>Who is more sexy?</h2>
<a href="r2d2"><img src="static/r2d2.png"></a> <a href="bb8"><img width="250" src="static/bb8.jpg"></a>"""
    s_page += "</body>"
    s_page += "</html>"

    return s_page

def page_bb8():
    global i_votes_bb8

    i_votes_bb8 = i_votes_bb8 + 1

    s_page = "<html>"
    s_page += "<title>My Web Page!</title>"
    s_page += "<body>"
    s_page += "<h1>Time now is: " + get_datetime() + "</h1>"
    s_page += """<h2>BB8 Is more sexy!</h2>
                <img width="250" src="static/bb8.jpg">"""
    s_page += "<p>I have: " + str(i_votes_bb8) + "</p>"
    s_page += "</body>"
    s_page += "</html>"

    return s_page

def page_r2d2():
    global i_votes_r2d2

    i_votes_r2d2 = i_votes_r2d2 + 1

    s_page = "<html>"
    s_page += "<title>My Web Page!</title>"
    s_page += "<body>"
    s_page += "<h1>Time now is: " + get_datetime() + "</h1>"
    s_page += """<h2>R2D2 Is more sexy!</h2>
                <img src="static/r2d2.png">"""
    s_page += "<p>I have: " + str(i_votes_r2d2) + "</p>"
    s_page += "</body>"
    s_page += "</html>"

    return s_page

if __name__ == "__main__":"", port=5000, debug=True)

As always, the naming of the variables is based on MT Notation.

The Dockerfile is very straightforward:

FROM ubuntu:20.04


ARG DEBIAN_FRONTEND=noninteractive

RUN apt update && \
    apt install -y vim python3-pip &&  pip3 install pytest && \
    apt-get clean

ENV PYTHON_COMBAT_GUIDE /var/python_combat_guide




RUN pip3 install -r $PYTHON_COMBAT_GUIDE/requirements.txt

# This is important so when executing python3 -m current directory will be added to Syspath
# Is not necessary, as we added to PYTHONPATH


# Launch our Flask Application
CMD ["/usr/bin/python3", "/var/python_combat_guide/src/"]

News from the blog 2021-07-23

  • I’ve released v. 0.99 of carleslibs package
    This package includes utilities for:
    • Files and Directories handling
    • Date/Time retrieval
    • Python version detection

You can install it with:

pip install carleslibs

The minimum requirement declared is Python 3.6, although they work with Python 3.5 and Python 2.7, as I want to drop support for no longer supported versions.

Instructions can be found in here: carleslibs page.

Video: Beginners Python: teaching to work with for, range, lists, dicts

I recorded this class where some university colleagues and few of my students joined me.

It is recorded at Full HD. Increase Youtube’s quality to make sure the video is smoothly displayed.

You can download the source code from a previous class and this one in Gitlab:

If you are looking for my technical books follow the link.

If you would like to become one of my students, or to have me as Mentor, check this section in my blog:

Classes and Mentor

A small Python + MySql + Docker program as a sample

This article can be found in my book Python Combat Guide.

I wrote this code and article in order to help my Python students to mix together Object Oriented Programming, MySql, and Docker.

You can have everything in action with only downloading the code and running the and scripts.

You can download the source code from:

and clone with:

git clone

Installing the MySql driver

We are going to use Oracle’s official MySql driver for Python.

All the documentation is here:

In order to install we will use pip.

To install it in Ubuntu:

pip install mysql-connector-python

In Mac Os X you have to use pip3 instead of pip.

However we are going to run everything from a Docker Container so the only thing you need is to have installed Docker.

If you prefer to install MySql in your computer (or Virtual Box instance) directly, skip the Docker steps.


The Dockerfile is the file that Docker uses to build the Docker Container.

Ours is like that:

FROM ubuntu:20.04


ARG DEBIAN_FRONTEND=noninteractive

RUN apt update && apt install -y python3 pip mysql-server vim mc wget curl && apt-get clean
RUN pip install mysql-connector-python


ENV FOLDER_PROJECT /var/mysql_carles



RUN chmod +x /var/mysql_carles/

CMD ["/var/mysql_carles/"]

The first line defines that we are going to use Ubuntu 20.04 (it’s a LTS version).

We install all the apt packages in a single line, as Docker works in layers, and what is used as disk space in the previous layer is not deleted even if we delete the files, so we want to run apt update, install all the packages, and clean the temporal files in one single step.

I also install some useful tools like: vim, mc, less, wget and curl.

We expose to outside the port 3306, in case you want to run the Python code from your computer, but having the MySql in the Container.

The last line executes a script that starts the MySql service, creates the table, the user, and add two rows and runs an infinite loop so the Docker does not finish. is a Bash script that builds the Docker Image for you very easily.

It stops the container and removes the previous image, so your hard drive does not fill with Docker images if you do modifications.

It checks for errors building and it also remembers you how to run and debug the Docker Container.


# Execute with sudo


printf "Stopping old image %s\n" "${s_DOCKER_IMAGE_NAME}"
sudo docker stop "${s_DOCKER_IMAGE_NAME}"

printf "Removing old image %s\n" "${s_DOCKER_IMAGE_NAME}"
sudo docker rm "${s_DOCKER_IMAGE_NAME}"

printf "Creating Docker Image %s\n" "${s_DOCKER_IMAGE_NAME}"
sudo docker build -t ${s_DOCKER_IMAGE_NAME} . --no-cache

if [ $i_EXIT_CODE -ne 0 ]; then
printf "Error. Exit code %s\n" ${i_EXIT_CODE}

echo "Ready to run ${s_DOCKER_IMAGE_NAME} Docker Container"
echo "To run type: sudo docker run -d -p 3306:3306 --name ${s_DOCKER_IMAGE_NAME} ${s_DOCKER_IMAGE_NAME}"
echo "or just use"
echo "Debug running Docker:"
echo "docker exec -it ${s_DOCKER_IMAGE_NAME} /bin/bash"

I also provide a script named that runs your Container easily, exposing the MySql port.


# Execute with sudo


docker run -d -p 3306:3306 --name ${s_DOCKER_IMAGE_NAME} ${s_DOCKER_IMAGE_NAME}

echo "Showing running Instances"
docker ps

As you saw before I named the image after blog_carlesmateo_com_mysql.

I did that so basically I wanted to make sure that the name was unique, as the deletes an image named like the name I choose, I didn’t want to use a generic name like “mysql” that may lead to you to delete the Docker Image inadvertently.

This script will run when the Docker Container is launched for the first time:


# Allow to be queried from outside
sed -i '31 s/bind-address/#bind-address/' /etc/mysql/mysql.conf.d/mysqld.cnf

service mysql start

# Create a Database, a user with password, and permissions
cd /var/mysql_carles
mysql -u root < start.sql

while [ true ]; do sleep 60; done

With sed command we modify the line 31 of the the MySQL config file so we can connect from Outside the Docker Instance (bind-address:

As you can see it executes the SQL contained in the file start.sql as root and we start MySql.

Please note: Our MySql installation has not set a password for root. It is only for Development purposes.


The SQL file that will be ran inside our Docker Container.

CREATE DATABASE carles_database;

CREATE USER ‘python’@’localhost’ IDENTIFIED BY ‘’;
GRANT ALL PRIVILEGES ON carles_database.* TO ‘python’@’localhost’;
GRANT ALL PRIVILEGES ON carles_database.* TO ‘python’@’%’;

USE carles_database;

CREATE TABLE car_queue (
i_id_car int,
s_model_code varchar(25),
s_color_code varchar(25),
s_extras varchar(100),
i_right_side int,
s_city_to_ship varchar(25)

INSERT INTO car_queue (i_id_car, s_model_code, s_color_code, s_extras, i_right_side, s_city_to_ship) VALUES (1, "GOLF2021", "BLUE7", "COND_AIR, GPS, MULTIMEDIA_V3", 0, "Barcelona");
INSERT INTO car_queue (i_id_car, s_model_code, s_color_code, s_extras, i_right_side, s_city_to_ship) VALUES (2, "GOLF2021_PLUGIN_HYBRID", "BLUEMETAL_5", "COND_AIR, GPS, MULTIMEDIA_V3, SECURITY_V5", 1, "Cork");

As you can see it creates the user “python” with the password ‘’ for access local and remote (%).

It also creates a Database named carles_database and grants all the permissions to the user “python”, for local and remote.

This is the user we will use to authenticate from out Python code.

Then we switch to use the carles_database and we create the car_queue table.

We insert two rows, as an example.

Finally the Python code that will query the Database.

import mysql.connector

if __name__ == "__main__":
    o_conn = mysql.connector.connect(user='python', password='', database='carles_database')
    o_cursor = o_conn.cursor()

    s_query = "SELECT * FROM car_queue"


    for a_row in o_cursor:


Nothing special, we open a connection to the MySql and perform a query, and parse the cursor as rows/lists.

Please note: Error control is disabled so you may see any exception.

Executing the Container

First step is to build the Container.

From the directory where you cloned the project, execute:

sudo ./

Then run the Docker Container:

sudo ./

The script also performs a docker ps command, so you can see that it’s running.

Entering the Container and running the code

Now you can enter inside the Docker Container:

docker exec -it blog_carlesmateo_com_mysql /bin/bash

Then change to the directory where I installed the sample files:

cd /var/mysql_carles

And execute the Python 3 example:


Tying together MySql and a Python Menu with Object Oriented Programming

In order to tie all together, and specially to give a consistent view to my students, to avoid showing only pieces but a complete program, and to show a bit of Objects Oriented in action I developed a small program which simulates the handling of a production queue for Volkswagen.

MySQL Library

First I created a library to handle MySQL operations.


import mysql.connector

class MySql():

    def __init__(self, s_user, s_password, s_database, s_host="", i_port=3306):
        self.s_user = s_user
        self.s_password = s_password
        self.s_database = s_database
        self.s_host = s_host
        self.i_port = i_port

        o_conn = mysql.connector.connect(host=s_host, port=i_port, user=s_user, password=s_password, database=s_database)
        self.o_conn = o_conn

    def query(self, s_query):
        a_rows = []

        o_cursor = self.o_conn.cursor()


        for a_row in o_cursor:


        return a_rows

    def insert(self, s_query):

        o_cursor = self.o_conn.cursor()

        i_inserted_row_count = o_cursor.rowcount

        # Make sure data is committed to the database

        return i_inserted_row_count

    def delete(self, s_query):

        o_cursor = self.o_conn.cursor()

        i_deleted_row_count = o_cursor.rowcount

        # Make sure data is committed to the database

        return i_deleted_row_count

    def close(self):


Basically when this class is instantiated, a new connection to the MySQL specified in the Constructor is established.

We have a method query() to send SELECT queries.

We have a insert method, to send INSERT, UPDATE queries that returns the number of rows affected.

This method ensures to perform a commit to make sure changes persist.

We have a delete method, to send DELETE Sql queries that returns the number of rows deleted.

We have a close method which closes the MySql connection.

A Data Object: CarDO

Then I’ve defined a class, to deal with Data and interactions of the cars.


class CarDO():

    def __init__(self, i_id_car=0, s_model_code="", s_color_code="", s_extras="", i_right_side=0, s_city_to_ship=""):
        self.i_id_car = i_id_car
        self.s_model_code = s_model_code
        self.s_color_code = s_color_code
        self.s_extras = s_extras
        self.i_right_side = i_right_side
        self.s_city_to_ship = s_city_to_ship

        # Sizes for render
        self.i_width_id_car = 6
        self.i_width_model_code = 25
        self.i_width_color_code = 25
        self.i_width_extras = 50
        self.i_width_side = 5
        self.i_width_city_to_ship = 15

    def print_car_info(self):
        print("Id:", self.i_id_car)
        print("Model Code:", self.s_model_code)
        print("Color Code:", self.s_color_code)
        print("Extras:", self.s_extras)
        s_side = self.get_word_for_driving_side()
        print("Drive by side:", s_side)
        print("City to ship:", self.s_city_to_ship)

    def get_word_for_driving_side(self):
        if self.i_right_side == 1:
            s_side = "Right"
            s_side = "Left"

        return s_side

    def get_car_info_for_list(self):

        s_output = str(self.i_id_car).rjust(self.i_width_id_car) + " "
        s_output += self.s_model_code.rjust(self.i_width_model_code) + " "
        s_output += self.s_color_code.rjust(self.i_width_color_code) + " "
        s_output += self.s_extras.rjust(self.i_width_extras) + " "
        s_output += self.get_word_for_driving_side().rjust(self.i_width_side) + " "
        s_output += self.get_s_city_to_ship().rjust(self.i_width_city_to_ship)

        return s_output

    def get_car_header_for_list(self):
        s_output = str("Id Car").rjust(self.i_width_id_car) + " "
        s_output += "Model Code".rjust(self.i_width_model_code) + " "
        s_output += "Color Code".rjust(self.i_width_color_code) + " "
        s_output += "Extras".rjust(self.i_width_extras) + " "
        s_output += "Drive".rjust(self.i_width_side) + " "
        s_output += "City to Ship".rjust(self.i_width_city_to_ship)

        i_total_length = self.i_width_id_car + self.i_width_model_code + self.i_width_color_code + self.i_width_extras + self.i_width_side + self.i_width_city_to_ship
        # Add the space between fields
        i_total_length = i_total_length + 5

        s_output += "\n"
        s_output += "=" * i_total_length

        return s_output

    def get_i_id_car(self):
        return self.i_id_car

    def get_s_model_code(self):
        return self.s_model_code

    def get_s_color_code(self):
        return self.s_color_code

    def get_s_extras(self):
        return self.s_extras

    def get_i_right_side(self):
        return self.i_right_side

    def get_s_city_to_ship(self):
        return self.s_city_to_ship

Initially I was going to have a CarDO Object without any logic. Only with Data.

In OOP the variables of the Instance are called Properties, and the functions Methods.

Then I decided to add some logic, so I can show what’s the typical use of the objects.

So I will use CarDO as Data Object, but also to do few functions like printing the info of a Car.

Queue Manager

Finally the main program.

We also use Object Oriented Programming, and we use Dependency Injection to inject the MySQL Instance. That’s very practical to do Unit Testing.

from lib.mysqllib import MySql
from do.cardo import CarDO

class QueueManager():

    def __init__(self, o_mysql):
        self.o_mysql = o_mysql

    def exit(self):

    def main_menu(self):
        while True:
            print("Main Menu")
            print("1. Add new car to queue")
            print("2. List all cars to queue")
            print("3. View car by Id")
            print("4. Delete car from queue by Id")
            print("0. Exit")

            s_option = input("Choose your option:")
            if s_option == "1":
            if s_option == "2":
            if s_option == "3":
            if s_option == "4":

            if s_option == "0":

    def get_all_cars(self):
        s_query = "SELECT * FROM car_queue"

        a_rows = self.o_mysql.query(s_query)
        a_o_cars = []

        for a_row in a_rows:
            i_id_car = a_row[0]
            s_model_code = a_row[1]
            s_color_code = a_row[2]
            s_extras = a_row[3]
            i_right_side = a_row[4]
            s_city_to_ship = a_row[5]

            o_car = CarDO(i_id_car=i_id_car, s_model_code=s_model_code, s_color_code=s_color_code, s_extras=s_extras, i_right_side=i_right_side, s_city_to_ship=s_city_to_ship)

        return a_o_cars

    def get_car_by_id(self, i_id_car):
        b_success = False
        o_car = None

        s_query = "SELECT * FROM car_queue WHERE i_id_car=" + str(i_id_car)

        a_rows = self.o_mysql.query(s_query)

        if len(a_rows) == 0:
            # False, None
            return b_success, o_car

        i_id_car = a_rows[0][0]
        s_model_code = a_rows[0][1]
        s_color_code = a_rows[0][2]
        s_extras = a_rows[0][3]
        i_right_side = a_rows[0][4]
        s_city_to_ship = a_rows[0][5]

        o_car = CarDO(i_id_car=i_id_car, s_model_code=s_model_code, s_color_code=s_color_code, s_extras=s_extras, i_right_side=i_right_side, s_city_to_ship=s_city_to_ship)
        b_success = True

        return b_success, o_car

    def replace_apostrophe(self, s_text):
        return s_text.replace("'", "´")

    def insert_car(self, o_car):

        s_sql = """INSERT INTO car_queue 
                                (i_id_car, s_model_code, s_color_code, s_extras, i_right_side, s_city_to_ship) 
                                (""" + str(o_car.get_i_id_car()) + ", '" + o_car.get_s_model_code() + "', '" + o_car.get_s_color_code() + "', '" + o_car.get_s_extras() + "', " + str(o_car.get_i_right_side()) + ", '" + o_car.get_s_city_to_ship() + "');"

        i_inserted_row_count = self.o_mysql.insert(s_sql)

        if i_inserted_row_count > 0:
            print("Inserted", i_inserted_row_count, " row/s")
            b_success = True
            print("It was impossible to insert the row")
            b_success = False

        return b_success

    def add_new_car(self):
        print("Add new car")

        while True:
            s_id_car = input("Enter new ID: ")
            if s_id_car == "":
                print("A numeric Id is needed")

            i_id_car = int(s_id_car)

            if i_id_car < 1:

            # Check if that id existed already
            b_success, o_car = self.get_car_by_id(i_id_car=i_id_car)
            if b_success is False:
                # Does not exist

            print("Sorry, this Id already exists")

        s_model_code = input("Enter Model Code:")
        s_color_code = input("Enter Color Code:")
        s_extras = input("Enter extras comma separated:")
        s_right_side = input("Enter R for Right side driven:")
        if s_right_side.upper() == "R":
            i_right_side = 1
            i_right_side = 0
        s_city_to_ship = input("Enter the city to ship the car:")

        # Sanitize SQL replacing apostrophe
        s_model_code = self.replace_apostrophe(s_model_code)
        s_color_code = self.replace_apostrophe(s_color_code)
        s_extras = self.replace_apostrophe(s_extras)
        s_city_to_ship = self.replace_apostrophe(s_city_to_ship)

        o_car = CarDO(i_id_car=i_id_car, s_model_code=s_model_code, s_color_code=s_color_code, s_extras=s_extras, i_right_side=i_right_side, s_city_to_ship=s_city_to_ship)
        b_success = self.insert_car(o_car)

    def see_all_cars(self):

        a_o_cars = self.get_all_cars()

        if len(a_o_cars) > 0:
            print("No cars in queue")

        for o_car in a_o_cars:


    def see_car_by_id(self, i_id_car=0):
        if i_id_car == 0:
            s_id = input("Car Id:")
            i_id_car = int(s_id)

        s_id_car = str(i_id_car)

        b_success, o_car = self.get_car_by_id(i_id_car=i_id_car)
        if b_success is False:
            print("Error, car id: " + s_id_car + " not located.")
            return False


        return True

    def delete_by_id(self):

        s_id = input("Enter Id of car to delete:")
        i_id_car = int(s_id)

        if i_id_car == 0:
            print("Invalid Id")

        # reuse see_car_by_id
        b_found = self.see_car_by_id(i_id_car=i_id_car)
        if b_found is False:

        s_delete = input("Are you sure you want to DELETE. Type Y to delete: ")
        if s_delete.upper() == "Y":
            s_sql = "DELETE FROM car_queue WHERE i_id_car=" + str(i_id_car)
            i_num = self.o_mysql.delete(s_sql)

            print(i_num, " Rows deleted")

            # if b_success is True:
            #     print("Car deleted successfully from the queue")

if __name__ == "__main__":


        o_mysql = MySql(s_user="python", s_password="", s_database="carles_database", s_host="", i_port=3306)

        o_queue_manager = QueueManager(o_mysql=o_mysql)
    except KeyboardInterrupt:
        print("Detected CTRL + C. Exiting")

This program talks to MySQL, that we have started in a Docker previously.

We have access from inside the Docker Container, or from outside.

The idea of this simple program is to use a library for dealing with MySql, and objects for dealing with the Cars. The class CarDO contributes to the render of its data in the screen.

To enter inside the Docker once you have generated it and is running, do:

docker exec -it blog_carlesmateo_com_mysql /bin/bash


cd /var/mysql_carles 


I added a file called queue_manager.php so you can see how easy is to render a HTML page with data coming from the Database, from PHP.

News from the Blog 2021-07-01

  • Google Instances’ Performance
    I’ve updated the CMIPS score for the latest Google instances vs last Amazon’s I tried and baremetals.

This is the changelog for latest version:

v. 0.99
 A whole new chapter showing sorting in Python and lambdas. (.sort() and sorted() package First)

 I show writing lambdas for Sorting, and also what makes them crash.

 Explained why Lambdas are not recommended unless you use for working with data, like for sorting or filtering out, and unless you know what you are doing. They are difficult to Debug.

 Explained about PEP8 tool to validate style.

 Explaining why we define Instance variables in the Constructor.

 Provided more samples for Flask Applications.

 Fixed code sample as the editor removed the white line spaces.

 Added more books to the bibliography

 I explain the importance of running Unit Testing as both root and as regular users.

 Explain how to run as regular user inside a Docker Container.

 Explained requirements.txt file. And how integrates PyCharm to create venv/ Virtual Environment.

 Also how it is used in Dockerfile to make sure all the dependencies are satisfied in the Docker Container.

As any project committed to saving human lives, she has all my support and admiration.