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
endI 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
endI 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_ratingPutting it all together..
Git clone this repo from GitHub:
git clone git@github.com:EricLondon/Ruby-import-iTunes-to-MySQL.gitFrom 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.xmlInstalled required Ruby gems:
bundle installExecute.
# you'll need to update your local database credentials first
./main.rbExample 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