updatepl.py

#!/usr/bin/python3
import pymysql, argparse, sys

parser = argparse.ArgumentParser()
parser.add_argument('playlist_name')
parser.add_argument('playlist_file')
parser.add_argument('-c', '--config', default='/home/zenith/ampache/config/ampache.cfg.php')
parser.add_argument('-q', '--quiet', action='store_true')
args = parser.parse_args()
playlist = args.playlist_name
playlist_path = args.playlist_file

def log(text):
    if not args.quiet:
        print(text, file=sys.stderr)

try:
    try:

        #TODO get db values from ampache cfg file instead of hardcoding
        connection = pymysql.connect(
            host='localhost',
            user='ampache',
            password='ampasswd',
            db='ampache',
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
    except pymysql.err.OperationalError as exc:
        sys.exit('Unable to connect to database\nReason: {0}'.format(exc.args[1]))

    log('Connected to the database')

    try:
        with open(playlist_path) as playlist_file:
            songs = set(line.strip() for line in playlist_file)
    except IOError as exc:
        sys.exit('Unable to open playlist file\nReason: {0}'.format(exc.args[1]))

    log('Read playlist file\nBeginning update operation')

    with connection.cursor() as cursor:
        cursor.execute('SELECT id FROM playlist WHERE name=%s', (playlist,))
        playlist_row = cursor.fetchone()
        if not playlist_row:
            sys.exit('No such playlist in database!')
        playlist_id = playlist_row['id']
        #TODO allow create new playlist instead of overwriting old one via cmdline arg

        cursor.execute('DELETE FROM playlist_data WHERE playlist=%s', (playlist_id,))

        track = 1
        for song in songs:
            cursor.execute('SELECT id FROM song WHERE file=%s', (song,))
            song_row = cursor.fetchone()
            if not song_row:
                continue
            cursor.execute('INSERT INTO playlist_data (id, playlist, object_id, object_type, track) VALUES (NULL, %s, %s, "song", %s)', (playlist_id, song_row['id'], track))
            track += 1

    connection.commit()
    log('Database successfully updated. Playlist \'{0}\' now contains {1} songs'.format(playlist, track-1))

except Exception as exc:
    sys.exit('An unknown error occured')
finally:
    connection.close()