mpdsqlsync.py, a Python script for synchronizing your MPD music collection to a MySQL backend

mpdsqlsync.py

############################################################################
# MPD2Mysql
#
# Synchronize your MPD music collection
# to a MySQL backend
#
# Options:
#  -b <dbhost> [default: localhost]
#  -P <dbport> [default: 3306]
#  -d <dbname> [default: mpd]
#  -u <dbuser> [default: root]
#  -w <dbpassword> [default: root]
#  -m <mpdhost> [default: localhost]
#  -p <mpdport> [default: 6600]
#
# Copyright 2013, Fabio "BlackLight" Manganiello, <blacklight86@gmail.com>
# Released under Apache License 2.0
############################################################################


#!/usr/bin/python

import re
import sys
import MySQLdb
from mpd import MPDClient
from getopt import getopt

def execute(dbconn, dbcursor, statement):
	try:
		dbcursor.execute(statement)
		dbconn.commit()
	except Exception as err:
		print("Warning in executing statement [%s]: [%s]" % (statement, err))
		dbconn.rollback()

dbname = 'mpd'
dbhost = 'localhost'
dbport = 3306
dbuser = 'root'
dbpass = 'root'
mpdhost = 'localhost'
mpdport = 6600

optlist, args = getopt(sys.argv[1:], 'hb:P:d:m:p:u:w:')

for opt, arg in optlist:
	if opt == '-b':
		dbhost = arg
	elif opt == '-P':
		dbport = arg
	elif opt == '-d':
		dbname = arg
	elif opt == '-m':
		mpdhost = arg
	elif opt == '-p':
		mpdport = arg
	elif opt == '-u':
		dbuser = arg
	elif opt == '-w':
		dbpass = arg
	elif opt == '-h':
		print("Usage: python %s [-b <dbhost>] [-P <dbport>] [-d <dbname>] [-u <dbuser>] [-w <dbpassword>] [-m <mpdhost>] [-p <mpdport>]" % argv[0])
		sys.exit(0)

client = MPDClient()
client.connect('localhost', 6600)
dbconn = MySQLdb.connect (host=dbhost, user=dbuser, passwd=dbpass, db=dbname, charset='utf8')
dbcursor = dbconn.cursor()

execute(dbconn, dbcursor, "DROP TABLE IF EXISTS music")
execute(dbconn, dbcursor, """
	CREATE TABLE music(
		id integer primary key auto_increment,
		artist varchar(767),
		title varchar(767),
		album varchar(767),
		date varchar(32),
		genre varchar(32),
		track varchar(8)
	)""")

songs = client.list("title")
numsongs = 0

for song in songs:
	artists = client.list('artist', 'title', song)
	artist = artists[0] if artists and len(artists) > 0 else ''

	albums = client.list('album', 'title', song)
	album = albums[0] if albums and len(albums) > 0 else ''

	dates = client.list('date', 'title', song)
	date = dates[0] if dates and len(dates) > 0 else ''

	genres = client.list('genre', 'title', song)
	genre = genres[0] if genres and len(genres) > 0 else ''

	tracks = client.list('track', 'title', song)
	track = tracks[0] if tracks and len(tracks) > 0 else 0

	statement = """INSERT INTO music(artist, title, album, date, genre, track) VALUES('%s', '%s', '%s', '%s', '%s', '%s')""" % \
			(artist.replace("'", "\\'"),
			song.replace("'", "\\'"),
			album.replace("'", "\\'"),
			date.replace("'", "\\'"),
			genre.replace("'", "\\'"),
			track)
	
	try:
		dbcursor.execute(statement)
	except Exception as err:
		print("Warning in executing statement [%s]: [%s]" % (statement, err))

	numsongs += 1
	# print("Processed %d/%d songs" % (numsongs, len(songs)))

try:
	dbconn.commit()
except Exception as err:
	print("Warning in executing statement [%s]: [%s]" % (statement, err))

dbcursor.close()
dbconn.close()

client.close()
client.disconnect()

I know this is a decade old, but just looking for interesting mpd related scripts. What use case was this for?

@bonelifer honestly I can't even remember - quite likely it was just a dump of some test code that I needed to work on across devices. A more complete overview of how to synchronize and persist mpd listening activities is here.