Ruby: import iTunes to MySQL

In this blog post I thought I'd share some code I wrote to export my iTunes library to XML, import into MySQL, and run some queries against the data; primarily based on my song ratings. I use this to gain access to aggregate SQL queries, and to populate playlists for my iPhone and a site I run: DailyAlbum.us.

This code assumes you are familiar with executing Ruby scripts, or are willing to learn. If you don't have the latest version of Ruby installed, I suggest you check out RVM, and if you don't have MySQL installed, I suggest you do via brew install mysql.

First I created a module class to handle all MySQL methods. file: lib/itunes_import_database.rb

module ItunesImport

  class Database

    attr_reader :mysql

    def initialize(params={})

      params.each {|key,value| instance_variable_set("@db_#{key}", value.to_s) }

      db_init
      ensure_database
      ensure_table
    end

    def db_init
      @mysql = Mysql2::Client.new host: @db_host, username: @db_username, password: @db_password, database: @db_database
    end

    def ensure_database
      sql = "show databases like '#{@mysql.escape(@db_database)}'"
      raise "Database (#{@mysql.escape(@db_database)}) does not exist" if @mysql.query(sql).map {|r| r}.size == 0
    end

    def ensure_table

      sql = "show tables in #{@mysql.escape(@db_database)} like '#{@mysql.escape(@db_table)}'"
      results = @mysql.query(sql).first

      if results.nil?
        sql = "CREATE TABLE `#{@mysql.escape(@db_table)}` (id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT)"
        @mysql.query sql
      end
    end

    def ensure_columns(columns=[])

      # get a list of existing columns
      sql = "show columns in #{@mysql.escape(@db_table)}"
      existing_columns = @mysql.query(sql).map {|r| r['Field'] }

      # ensure columns exist
      columns.each do |column|
        unless existing_columns.include?(column)
          sql = "ALTER TABLE #{@mysql.escape(@db_table)} ADD `#{column}` VARCHAR(255)"
          @mysql.query sql
        end
      end

    end

    def insert_row(row)

      sql_columns = row.keys.map {|k| "`#{k}`"}.join(', ')
      sql_values = row.values.map {|v| "'#{@mysql.escape(v.to_s[0..254])}'"}.join(', ')

      sql = "insert into #{@mysql.escape(@db_table)} (#{sql_columns}) values (#{sql_values})"
      @mysql.query sql

    end

    def truncate_table
      sql = "select count(*) as `countX` from `#{@mysql.escape(@db_table)}`"
      return if @mysql.query(sql).first['countX'] == 0

      sql = "truncate table `#{@mysql.escape(@db_table)}`"
      @mysql.query sql
    end

    def albums_by_highest_average_rating(limit=20)

      sql = "
        select Artist, Album, Genre, Year, count(*) as `album_track_count`, avg(ifnull(Rating, 0)) as `average_rating`
        from tracks
        where `Album Artist` is not null
        group by `Album Artist`, Album
        having album_track_count > 2
        order by average_rating desc
        limit #{@mysql.escape(limit.to_s)}"

      @mysql.query(sql).map {|result| result}

    end

    def indie_albums_by_highest_average_rating(limit=20)

      sql = "
        select Artist, Album, Genre, Year, count(*) as `album_track_count`, avg(ifnull(Rating, 0)) as `average_rating`
        from tracks
        where `Album Artist` is not null and Genre like '%indie%'
        group by `Album Artist`, Album
        having album_track_count > 2
        order by average_rating desc
        limit #{@mysql.escape(limit.to_s)}"

      @mysql.query(sql).map {|result| result}

    end

  end

end

I then created the main module file to interface with the database class and provide a few private/helper methods. file: lib/itunes_import.rb

module ItunesImport

  # init database
  def self.init(db_params={})

    @db = Database.new db_params

  end

  # import XML Library into MySQL
  def self.import(itunes_file=nil, options={})

    # ensure db connection
    raise "Database connection required." if @db.nil?

    @itunes_file = itunes_file
    ensure_file

    if options.has_key?(:truncate) && options[:truncate]==true
      @db.truncate_table
    end

    load_library

    import_library
  end

  # query
  def self.albums_by_highest_average_rating(limit=20)
    puts "# Albums by highest average rating:"
    output_results_hash @db.albums_by_highest_average_rating limit
    puts "\n"
  end

  # query
  def self.indie_albums_by_highest_average_rating(limit=20)
    puts "# Indie Albums by highest average rating:"
    output_results_hash @db.indie_albums_by_highest_average_rating limit
    puts "\n"
  end

  class << self

    private

    def ensure_file
      raise "File (#{@itunes_file}) does not exist" unless File.exists?(@itunes_file)
    end

    def load_library
      @library = Plist::parse_xml @itunes_file
    end

    def import_library

      raise "Library not loaded" if @library.nil?

      @library['Tracks'].each do |key,row|
        @db.ensure_columns row.keys
        @db.insert_row row
      end

    end

    def output_results_hash(data=[])

      # max length for each column
      max_lengths = {}
      data.first.keys.each do |column|
        max_row = data.max { |a, b| a[column].to_s.length <=> b[column].to_s.length }
        max_row_column_length = max_row[column].to_s.length
        max_lengths[column] = max_row_column_length > column.length ? max_row_column_length : column.length
      end

      data.each_with_index do |row, index|

        # first row
        if index == 0
          row.each {|key, value| printf "%-#{max_lengths[key]+1}s", key }
          puts "\n"
        end

        row.each {|key, value| printf "%-#{max_lengths[key]+1}s", value }
        puts "\n"

      end

    end

  end

end

I created the main executable file to define database credentials and execute the module methods. file: main.rb

#!/usr/bin/env ruby

# include gems
require 'mysql2'
require 'plist'

# include path
$:.unshift File.dirname(__FILE__) + '/lib'

# require lib
require 'itunes_import_database'
require 'itunes_import'

# define database params
db_params = {
  host: 'localhost',
  username: 'itunes_import',
  password: 'itunes_import',
  database: 'itunes_import',
  table: 'tracks',
}

# initialize db
ItunesImport.init db_params

options = {
  truncate: true
}

# import XML library to MySQL
ItunesImport.import 'Library.xml', options

# query
ItunesImport.albums_by_highest_average_rating

# query
ItunesImport.indie_albums_by_highest_average_rating

Putting it all together..

Git clone this repo from GitHub:

git clone git@github.com:EricLondon/Ruby-import-iTunes-to-MySQL.git

From iTunes, export your library. It's easiest of you put this XML file in your cloned repo directoy.

File >> Library >> Export Library...

Save As: Library.xml

Installed required Ruby gems:

bundle install

Execute.

# you'll need to update your local database credentials first

./main.rb

Example output.

Erics-MacBook-Pro-15:itunes Eric$ ./main.rb

# Albums by highest average rating:
Artist            Album                             Genre           Year album_track_count average_rating
pink floyd        wish you were here                Classic Rock    1975 5                 96.0
led zeppelin      houses of the holy                Classic Rock    1973 8                 92.5
the doors         best of doors                     Classic Rock    1985 19                91.57894736842105
whitest boy alive dreams                            Indie           2005 10                90.0
iggy pop          the idiot                         Post Punk       1977 8                 90.0
vampire weekend   vampire weekend                   Indie           2008 11                89.0909090909091
neil young        on the beach                      Folk            1974 8                 87.5
radiohead         bends                             Alternative     1995 12                86.66666666666667
herbie hancock    thrust                            Jazz Fusion     1974 4                 85.0
suckers           suckers ep                        Indie           2009 4                 85.0
the clash         story of the clash, vol. 1 disc 1 Punk            1988 13                84.61538461538461
phish             lawnboy                           Alternative     1990 9                 84.44444444444444
eagles            their greatest hits (1971-1975)   Soft Rock       1976 10                84.0
sublime           sublime                           Ska             1996 17                83.52941176470588
nirvana           in utero                          Grunge          1993 12                83.33333333333333
radiohead         ok computer                       Alternative     1997 12                83.33333333333333
chad valley       equatorial ultravox               Indie - Electro 2012 7                 82.85714285714286
underworld        born slippy                       Electronic      1996 7                 82.85714285714286
led zeppelin      iv                                Classic Rock    1971 8                 82.5
nirvana           nevermind                         Grunge          1991 13                81.53846153846153

# Indie Albums by highest average rating:
Artist                           Album                                Genre            Year album_track_count average_rating
whitest boy alive                dreams                               Indie            2005 10                90.0
vampire weekend                  vampire weekend                      Indie            2008 11                89.0909090909091
suckers                          suckers ep                           Indie            2009 4                 85.0
chad valley                      equatorial ultravox                  Indie - Electro  2012 7                 82.85714285714286
cut copy                         rendezvous                           Indie - Electro  2001 3                 80.0
mgmt                             oracular spectacular                 Indie            2007 10                80.0
wild nothing                     evertide                             Indie - Shoegaze 2010 3                 80.0
yeah yeah yeahs                  live session (itunes exclusive) - ep Indie            2006 4                 80.0
strange talk                     strange talk ep                      Indie - Electro  2011 4                 80.0
matt & kim                       grand                                Indie            2009 11                80.0
the pains of being pure at heart higher than the stars                Indie - Shoegaze 2009 5                 80.0
arcade fire                      neon bible                           Indie            2007 11                80.0
yeah yeah yeahs                  show your bones                      Indie            2006 11                78.18181818181819
the black keys                   attack and release                   Indie            2008 11                78.18181818181819
washed out                       within and without                   Indie - Shoegaze 2011 9                 77.77777777777777
kings of leon                    youth & young manhood                Indie            2003 12                76.66666666666667
okkervil river                   the silver gymnasium                 Indie            2013 11                76.36363636363636
animal collective                merriweather post pavilion           Indie            2009 11                76.36363636363636
twin shadow                      forget                               Indie            2010 11                76.36363636363636
suckers                          candy salad                          Indie            2012 11                76.36363636363636

Source code on GitHub