import sqlite3 import plistlib library = plistlib.readPlist('iTunes-Music-Library.xml') class BasicLibraryItem(object): def get_insert_query(self): return 'INSERT INTO {} VALUES ({})'.format( self.TABLE_NAME, self.get_bind_string(self.COLUMN_COUNT)) @staticmethod def get_bind_string(count): return ('?,' * count)[:-1] @staticmethod def as_data_string(value): if not value: return None else: return value.asBase64() @staticmethod def as_boolean(value): return 1 if value else 0 @staticmethod def format_name(name): return name.lower().replace(' ', '_') @classmethod def from_plist_entry(cls, entry): self = cls() for key, value in entry.items(): name = cls.format_name(key) if not hasattr(self, name): raise Exception("No attribute named %r" % name) setattr(self, name, value) return self class Playlist(BasicLibraryItem): COLUMN_COUNT = 21 TABLE_NAME = 'playlist' def __init__(self): self.playlist_id = None self.playlist_persistent_id = None self.parent_persistent_id = None self.distinguished_kind = None self.genius_track_id = None self.name = None self.master = False self.visible = False self.all_items = False self.folder = False self.music = False self.movies = False self.tv_shows = False self.podcasts = False self.itunesu = False self.audiobooks = False self.books = False self.purchased_music = False self.smart_info = None self.smart_criteria = None self.items = [] def as_dbrow_tuple(self): return ( self.playlist_id, #int 1, # user_id self.playlist_persistent_id, self.parent_persistent_id, self.distinguished_kind, #int self.genius_track_id, #int self.name, self.as_boolean(self.master), self.as_boolean(self.visible), self.as_boolean(self.all_items), self.as_boolean(self.folder), self.as_boolean(self.music), self.as_boolean(self.movies), self.as_boolean(self.tv_shows), self.as_boolean(self.podcasts), self.as_boolean(self.itunesu), self.as_boolean(self.audiobooks), self.as_boolean(self.books), self.as_boolean(self.purchased_music), self.as_data_string(self.smart_info), self.as_data_string(self.smart_criteria) ) @classmethod def from_plist_entry(cls, entry): self = cls() for key, value in entry.items(): if key == 'Playlist Items': for item in value: self.items.append(item['Track ID']) continue name = cls.format_name(key) if not hasattr(self, name): raise Exception("No attribute named %r" % name) setattr(self, name, value) return self class Track(BasicLibraryItem): COLUMN_COUNT = 53 TABLE_NAME = 'track' def __init__(self): self.album_rating = None self.album_rating_computed = False self.comments = None self.volume_adjustment = None self.unplayed = False self.date_added = None self.date_modified = None self.disabled = False self.play_count = None self.play_date = None self.play_date_utc = None self.rating = None self.skip_count = None self.skip_date = None self.track_id = None self.persistent_id = None self.library_folder_count = None self.file_folder_count = None self.location = None self.track_type = None self.file_type = None self.artwork_count = None self.hd = False self.has_video = False self.itunesu = False self.tv_show = False self.podcast = False self.protected = False self.purchased = False self.movie = False self.music_video = False self.bpm = None self.bit_rate = None self.sample_rate = None self.size = None self.total_time = None self.kind = None self.video_height = None self.video_width = None self.sort_album = None self.sort_album_artist = None self.sort_artist = None self.sort_composer = None self.sort_name = None self.sort_series = None self.album = None self.album_artist = None self.artist = None self.clean = False self.compilation = False self.composer = None self.content_rating = None self.disc_count = None self.disc_number = None self.episode = None self.episode_order = None self.explicit = False self.genre = None self.grouping = None self.name = None self.part_of_gapless_album = False self.release_date = None self.season = None self.series = None self.track_count = None self.track_number = None self.year = None def as_dbrow_tuple(self): return ( self.track_id, self.persistent_id, self.library_folder_count, self.file_folder_count, self.location, self.track_type, self.file_type, self.artwork_count, self.as_boolean(self.hd), self.as_boolean(self.has_video), self.as_boolean(self.itunesu), self.as_boolean(self.tv_show), self.as_boolean(self.podcast), self.as_boolean(self.protected), self.as_boolean(self.purchased), self.as_boolean(self.movie), self.as_boolean(self.music_video), self.bpm, self.bit_rate, self.sample_rate, self.size, self.total_time, self.kind, self.video_height, self.video_width, self.sort_album, self.sort_album_artist, self.sort_artist, self.sort_composer, self.sort_name, self.sort_series, self.album, self.album_artist, self.artist, self.as_boolean(self.clean), self.as_boolean(self.compilation), self.composer, self.content_rating, self.disc_count, self.disc_number, self.episode, self.episode_order, self.as_boolean(self.explicit), self.genre, self.grouping, self.name, self.as_boolean(self.part_of_gapless_album), self.release_date, self.season, self.series, self.track_count, self.track_number, self.year ) def usermeta_as_dbrow_tuple(self): return ( 1, self.track_id, self.album_rating, self.as_boolean(self.album_rating_computed), self.comments, self.volume_adjustment, self.as_boolean(self.unplayed), self.date_added, self.date_modified, self.as_boolean(self.disabled), self.play_count, self.play_date, self.play_date_utc, self.rating, self.skip_count, self.skip_date ) USERMETA_COLUMN_COUNT = 16 USERMETA_TABLE_NAME = 'user_track_metadata' def get_usermeta_insert_query(self): return 'INSERT INTO {} VALUES ({})'.format( self.USERMETA_TABLE_NAME, self.get_bind_string(self.USERMETA_COLUMN_COUNT)) assert library['Major Version'] == 1, "Invalid major version" assert library['Minor Version'] == 1, "Invalid minor version" def insert_playlists(db, library): playlists = [Playlist.from_plist_entry(entry) for entry in library['Playlists']] curs = db.cursor() track_insert_query = playlists[0].get_insert_query() for playlist in playlists: curs.execute(track_insert_query, playlist.as_dbrow_tuple()) for item in playlist.items: curs.execute('INSERT INTO playlist_track VALUES (?, ?)', (playlist.playlist_id, item)) db.commit() def insert_tracks(db, library): music_folder = library['Music Folder'] tracks = [Track.from_plist_entry(entry) for entry in library['Tracks'].values()] curs = db.cursor() track_insert_query = tracks[0].get_insert_query() usermeta_insert_query = tracks[0].get_usermeta_insert_query() for track in tracks: curs.execute(track_insert_query, track.as_dbrow_tuple()) curs.execute(usermeta_insert_query, track.usermeta_as_dbrow_tuple()) db.commit() db = sqlite3.connect('iTunesLibrary.db') #insert_playlists(db, library) insert_tracks(db, library) db.close()