mixxx-db-pathswap.py

#!/usr/bin/env python
import sqlite3
from os import path

""" 

Script to update the library base path in the mixxx database.
useful for keeping crates and playlists sycned across computers with
different base paths for the music collection. 

Simply copy the database file from one computer to another, update
the IN_DIR and OUT_DIR paths, then run the script to replace all
the paths for your music to use OUT_DIR as the new base path 

Copyright (C) 2016 Tim Rae <perceptualchaos2@gmail.com>

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.

"""

# Current path set for music folder in the mixxx database
IN_DIR = r'/media/tim/Data1/Music/'
# New path to set for music folder in the mixxx database
OUT_DIR = path.join(path.expanduser('~'), 'Music/')

# Connect to DB and fetch the relevant columns in track_locations table
db = sqlite3.connect(path.join(path.expanduser('~'), '.mixxx/mixxxdb.sqlite'))
c = db.execute('select id, location, filename, directory from track_locations')
ids,locs,fns,dirs = zip(*(c.fetchall()))

# Replace the directories with the new base path
dirs = tuple([x.replace(IN_DIR, OUT_DIR) for x in dirs])

# Save changes to the database
def dir_iter():
    for i in xrange(len(ids)):
        yield (path.join(dirs[i], fns[i]), dirs[i], ids[i])
db.executemany('update track_locations SET location = ?, directory = ? where id = ?', dir_iter())
db.commit()
db.close()