Among other things lately I was working on making charts for the country of origin for jobs on UpWork.

I wanted to have country flag icons for each country so I needed to add a new field in the odesk_cmap table in the database. This new field would provide the flag code for that particular country.

This table now looks like this, with the f_name column added.

So I went over to github and looked for a set of icons I could use for these flags and found flag-icon made by Steven Skelton which fits quite nicely.

Then I've identified this place to contain the flag codes.

I've extracted that json out, it looked a bit like this

"data": [
  { "name": "Afghanistan", "aspect": 1.5, "alpha2": "AF", "alpha3": "AFG", "numeric": 4, "endonym": ["Afghanestan","\u0627\u0641\u063A\u0627\u0646\u0633\u062A\u0627\u0646"] },
  { "name": "American Samoa", "aspect": 2, "alpha2": "AS", "alpha3": "ASM", "numeric": 16, "endonym": ["Amerika S\u0101moa"] },
  { "name": "Andorra", "aspect": "10/7", "alpha2": "AD", "alpha3": "AND", "numeric": 20 },
  { "name": "Angola", "aspect": 1.5, "alpha2": "AO", "alpha3": "AGO", "numeric": 24 },
  { "name": "Anguilla", "aspect": 2, "alpha2": "AI", "alpha3": "AIA", "numeric": 660 },
  { "name": "Argentina", "aspect": 1.6, "alpha2": "AR", "alpha3": "ARG", "numeric": 32 },
  { "name": "Armenia", "aspect": 2, "alpha2": "AM", "alpha3": "ARM", "numeric": 51, "endonym": ["Hayast\u00E1n","\u0540\u0561\u0575\u0561\u057D\u057F\u0561\u0576"] },

And then I wrote some Perl to update the table in my database with these new codes

use strict;
use warnings;
use JSON;
use DBD::Pg;
use DBI;

my $j;

# Load the json with mappings between country names and flag codes(alpha2)
    # input record separator localized
    # in order to enable slurp mode
    # (the whole file is read, not just one line)
    local $/;
    open( my $fh, '<', 'data.json' );
    my $json_text   = <$fh>;
    $j = decode_json( $json_text );

my $countries = $j->{data};

my $dbh = DBI->connect("dbi:Pg:dbname=test1", "user", "password");
for my $country (@$countries) {
    # country name in flag-icons data set
    my $nameValues = $country->{name};
    $nameValues = [$nameValues] if ref($nameValues) ne 'ARRAY';

    for my $nameCountry (@$nameValues) {
        # find country in DB
        my $sth = $dbh->prepare('SELECT o_name, d_name FROM odesk_cmap WHERE o_name=?');
        my $result = $sth->fetchrow_arrayref;
        if(defined($result)) {
            # update row and place alpha2 code from flag-icons dataset in
            # (for, for Albania the alpha2 code is AL)
            print $country->{alpha2}."\n";
            my $sth = $dbh->prepare('UPDATE odesk_cmap SET f_name=? WHERE o_name=?');
            $sth->execute(lc($country->{alpha2}), lc($nameCountry));


In the above Perl code, $/ is the input record separator so I'm just localizing it in order to use slurp mode and read the entire json file.

Then I go through the entries in the file and check 1 if I can find the country in the database, and I just update the column with the right value and I'm done.

Afterwards I could actually get charts like these with the flag for each country:



PostgreSQL 9.5 comes with the UPSERT feature but I'm still on 9.4 (MySQL had this feature for a long time). The table modified here is rather small so running 2 queries for each row is not a big problem.