Drupal 6 Bulk Save of Taxonomy Terms with Perl

Submitted by nigel on Saturday 20th February 2010

You may be faced with the prospect of performing a batch save of taxonomy terms on your Drupal 6 installation. Using the normal interface, despite being clean and efficient, it could be an onerous task of many mouse clicks and wandering concentration resulting in errors. I was faced with such a situation so decided to quickly develop my own utility to perform a bulk save on my behalf. I decided upon Perl as the language to code in because I did not want the development time associated with user interfaces - this utility is for command line aficionados.

Taxonomy Perl1

The example I am showing is for the creation of an image_gallery of Premier League football teams. I am making the assumption that the vocabulary image_gallery already exists, and I have manually created a taxonomy term Premier League. It would be relatively easy to modify my code to supply by the vocabulary and the child of this as command line arguments. I am already taking the the name of the child as a command line argument - so look at this code if you wish to amend what I have already done.

The perl script is reproduced below - I have named it populate

# Copyright www.badzilla.co.uk
# Populate the database with terms from a given parent on the command line
#   Note - this just adds taxonomy terms and not nodes
# Usage: Pipe a text file containing a list of SBs, one per line
# Licence GPL. This program may be distributed as per the terms of GPL and all credits
# must be retained
# If you find this script useful, please consider a donation to help me fund my web presence
# and encourage me to develop more products to be placed under the terms of GPL
# To donate, go to http://www.badzilla.co.uk and click on the donation button
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
use strict;
use warnings;
use DBI;
use Getopt::Std;
sub trim($);
# config
my $dbname = 'your_db';
my $dbuser = 'your_db_user';
my $dbpwd = 'your_db_password';
my %options=();
my $parent = "";
my $db;
my $readdb;
getopts("p:", \%options);
$parent = $options{p} if defined $options{p};
if ($parent eq "") { die("usage: input_file | populate -p Parent\n"); }
# open the database connection
$db = DBI->connect("DBI:mysql:$dbname", $dbuser, $dbpwd) or die "sdpopulate: Cannot open database " . $db->errstr() . "\n";
my $parentvocab = $db->quote($parent);
$readdb = $db->prepare("SELECT tid, vid FROM `term_data` WHERE name = $parentvocab;") or die "sdpopulate: Cannot prepare database " . $db->errstr() . "\n";
if (!$readdb->execute()) { die "sdpopulate: Failed to read database " . $db->errstr() . "\n"; }
my $ref =  $readdb->fetchall_arrayref;
if (!exists($$ref[0][0]) or !exists($$ref[0][1])) {
    die "sdpopulate: Could not locate parent taxomony term\n";
my $parent_tid = $$ref[0][0];
my $parent_vid = $$ref[0][1];
while(<>) {
    my $line = trim($_);
    # check first if already exists
    my $quoteline = $db->quote($line);
    $readdb = $db->prepare("SELECT tid FROM `term_data` WHERE vid = $parent_vid AND name = $quoteline;") or die "sdpopulate: Cannot prepare database " . $db->errstr() . "\n";
    if (!$readdb->execute()) { die "sdpopulate: Failed to read database " . $db->errstr() . "\n"; }
    my $ref2 =  $readdb->fetchall_arrayref;
    if (exists($$ref2[0][0])) {
        print "Term $line already exists, not created.\n";
    } else {
        # write term_data
        my $writedb = $db->prepare("INSERT INTO `term_data` (vid, name, description, weight) VALUES (?, ?, ?, ?);");
        if (!$writedb) { die "sdpopulate: Cannot prepare database " . $db->errstr() . "\n"; }
        $writedb->execute($parent_vid, $line, "", 0);
        my $last_id = $db->last_insert_id(undef, undef, qw(a_table a_table_id));
        # write term_hierarchy
        $writedb = $db->prepare("INSERT INTO `term_hierarchy` (tid, parent) VALUES (?, ?);");
        if (!$writedb) { die "sdpopulate: Cannot prepare database " . $db->errstr() . "\n"; }
        $writedb->execute($last_id, $parent_tid);
        # write url_alias
        $parent =~ tr/ /-/;
        $line =~ tr/ /-/;
        $writedb = $db->prepare("INSERT INTO `url_alias` (src, dst) VALUES (?, ?);");
        if (!$writedb) { die "sdpopulate: Cannot prepare database " . $db->errstr() . "\n"; }
        $writedb->execute("image/tid/$last_id", "category/Image-Galleries/$parent/$line");
        print "Term $line created\n";
sub trim($)
        my $string = shift;
        $string =~ s/^\s+//;
        $string =~ s/\s+$//;
        return $string;
Make this executable with:
laptop4:/usr/local/sd # chmod u+x populate
The utility accepts piped input - so place all your taxonomy terms in a text file, one term per line - like I have done in my example.
Aston Villa     
Birmingham City 
Blackburn Rovers        
Bolton Wanderers        
Hull City       
Man City        
Man Utd 
Stoke City      
Tottenham Hotspur       
West Ham United 
Wigan Athletic  
Wolverhampton Wanderers
To run this utility, do:
laptop4:/usr/local/sd # cat football_teams | ./populate -p "Premier League"
Term Arsenal created
Term Aston-Villa created
Term Birmingham-City created
Term Blackburn-Rovers created
Term Bolton-Wanderers created
Term Burnley created
Term Chelsea created
Term Everton created
Term Fulham created
Term Hull-City created
Term Liverpool created
Term Man-City created
Term Man-Utd created
Term Portsmouth created
Term Stoke-City created
Term Sunderland created
Term Tottenham-Hotspur created
Term West-Ham-United created
Term Wigan-Athletic created
Term Wolverhampton-Wanderers created
laptop4:/usr/local/sd #
Taxonomy Perl2

Ok! No error messages so that should have worked. Check by pointing a web browser at the taxonomy listing. Hey presto! Everything ok, and if you roll the mouse over the links, you will see that the url aliases have been created correctly too!

blog terms
Drupal Drupal 6 Perl