Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
import sqlite3, plistlib
tracks_cmap = {'Album' :'album',
'Album Rating' :'album_rating',
'Artist' :'artist',
'Comments' :'comments',
'Compilation' :'compilation',
'Composer' :'composer',
'Disabled' :'disabled',
'Disc Count' :'disc_count',
'Disc Number' :'disc_number',
'Genre' :'genre',
'Grouping' :'grouping',
'Location' :'location',
'Name' :'name',
'Play Count' :'play_count',
'Rating' :'rating',
'Release Date' :'release_date',
'Size' :'size',
'Sort Album' :'sort_album',
'Sort Artist' :'sort_artist',
'Sort Composer':'sort_composer',
'Sort Name' :'sort_name',
'Start Time' :'start_time',
'Stop Time' :'stop_time',
'Total Time' :'total_time',
'Track Count' :'track_count',
'Track ID' :'track_id',
'Track Number' :'track_number',
'Year' :'year'}
tracks_make = """CREATE TABLE tracks (
album TEXT,
album_rating INTEGER,
artist TEXT,
comments TEXT,
compilation INTEGER,
composer TEXT,
disabled INTEGER,
disc_count INTEGER,
disc_number INTEGER,
genre TEXT,
grouping TEXT,
location TEXT NOT NULL,
name TEXT NOT NULL,
play_count INTEGER,
rating INTEGER,
release_date TEXT,
size INTEGER,
sort_album TEXT,
sort_artist TEXT,
sort_composer TEXT,
sort_name TEXT,
start_time INTEGER,
stop_time INTEGER,
total_time INTEGER,
track_count INTEGER,
track_id INTEGER PRIMARY KEY,
track_number INTEGER,
year INTEGER
)"""
filter_tracks = set(['Podcast','Movie','Music Video'])
def track_tuple(track_d):
if filter_tracks.intersection(track_d.keys()):
# Track had an attribute matching one of the 'forbidden' values
# Never defined by iTunes if it's False
return None
if track_d.get('Protected', False):
# Track is protected, could potentially have a False value though
return None
new_track = {}
for field in tracks_cmap.keys():
new_track[tracks_cmap[field]] = track_d.get(field, None)
if not new_track['location'] or not new_track['track_id']:
# No file location or track ID recorded, useless track
return None
# Correct the booleans
new_track['compilation'] = int(not not new_track['compilation'])
new_track['disabled'] = int(not not new_track['disabled'])
# Ensure name is not None / NULL
if not new_track['name']:
new_track['name'] = ''
return tuple([x[1] for x in sorted(new_track.items(), key=lambda x: x[0])])
def refresh_tracks_table(xml_file, db_name):
conn = sqlite3.connect(db_name)
c = conn.cursor()
# See if we need to create the tracks table
_ = c.execute("select name from sqlite_master where type='table' AND name='tracks'")
if not c.fetchone():
# Table doesn't exist, needs creation
_ = c.execute(tracks_make)
conn.commit()
with open(xml_file, 'r') as itunes_xml:
reader = itunes_xml.xreadlines()
# Skip through to the beginning of the tracks.
while (reader.next() != '\t<key>Tracks</key>\n'):
pass
# Skip the dict line after
_ = reader.next()
# Read the track chunks
section_start = reader.next()
i = 0
while section_start != '\t</dict>\n':
i += 1
track_section = []
section_line = reader.next()
while section_line != '\t\t</dict>\n':
track_section.append(section_line)
section_line = reader.next()
track_section.insert(0, '<?xml version="1.0" encoding="UTF-8"?>\n<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">\n<plist version="1.0">\n')
track_section.append('</dict>\n</plist>')
track_info = track_tuple(plistlib.readPlistFromString('\n'.join(track_section)))
if track_info:
# Track passed muster, we have a nicely formatted tuple ready to insert
_ = c.execute("insert or replace into tracks values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", track_info)
if not (i % 100):
# Batch up our commits in groups of 100
conn.commit()
section_start = reader.next()
conn.commit()
refresh_tracks_table('itunes.xml', 'itunes.sqlite3')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.