Create a random playlist

General
1

Hi… after messing around with mopidy for a while I wanted to implement the “50 random tracks” idea from Clementine which I really liked. So I wrote a little cron job. It is hacky and clunky and would be better in something like python but for a quick sketch someone might find it useful. I should point out that I am using mopidy-local.

put into /etc/cron.daily/

#!/bin/sh
# select 100 tracks at random and create a playlist

num=100
playlist="/var/lib/mopidy/m3u/random${num}.m3u8"
echo "#EXTM3U" > ${playlist}
for uri in $(/usr/bin/sqlite3 /var/lib/mopidy/local/library.db "SELECT uri FROM track ORDER BY RANDOM() LIMIT $num;"); do
	name=$(/usr/bin/sqlite3 /var/lib/mopidy/local/library.db "SELECT name FROM track WHERE uri = \"$uri\";")
	echo "#EXTINF:-1,$name"
	echo "$uri"
done >> ${playlist}
/usr/bin/chown mopidy:audio ${playlist}

If anyone wants to suggest improvements, let me know!

G

1 Like
2

Hey thanks for your little script. Using it on my mopidy setup. Small modification I had to do was to use /bin/chown instead of /usr/bin/chown (debian/raspbian on rasperrypi).

3

huh. ok. That script was hacked together on a server running debian 10.7. It is standard there to link /bin → /usr/bin:

lrwxrwxrwx 1 root root 7 Jun 21  2020 /bin -> usr/bin
4

I’m not happy about the “randomness” of the SQLite ORDER_BY_RANDOM function. On a largish database (5k tracks) it is noticeably not very random! I’ve hacked a bit of python to do the same thing. I am aware that the code is horrible but this is my first bit of python so hacked together by brute force. I’ve not had chance to compare the randomnesss but would hope that python inbuilt modules should be an improvement on the SQLite.

#!/usr/bin/python3

import sqlite3
from sqlite3 import Error
import random

def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by the db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
    except Error as e:
        print(e)

    return conn

def select_all_tracks(conn):
    """
    Query all rows in the tasks table
    :param conn: the Connection object
    :return:
    """
    cur = conn.cursor()
    cur.execute("SELECT uri FROM tracks")

    rows = cur.fetchall()
    return rows

def select_foo_of_track(conn,uri,column):
    """
    Get foo from track uri
    :conn: the connection object
    :param uri: uri of track
    :return: foo of track
    """
    cur = conn.cursor()
    query = "SELECT " + column + ' FROM tracks WHERE uri = "' + uri + '"'
    cur.execute( query )
    #fetchone returns a tuple!
    foo = cur.fetchone()
    return foo

def main():
    database = r"/home/qqs43472/.local/share/mopidy/local-sqlite/library.db"
    num = 10

    # create a database connection
    conn = create_connection(database)
    with conn:
        tracks = select_all_tracks(conn)
        s = random.sample(tracks, k=num)
        print("#EXTM3U\n")
        for i in range(len(s)):
            # need to convert single item tuple uri to a string
            track_uri = ''.join(s[i])
            # not sure why I need to unpack these list elements!
            title = select_foo_of_track(conn, track_uri, "name")
            artist = select_foo_of_track(conn, track_uri, "artist_name")
            length = select_foo_of_track(conn, track_uri, "length")
            print("#EXTINF:%s, %s - %s" % (*length,*artist,*title))
            print(*s[i],"\n")


if __name__ == '__main__':
    main()

I would be very happy to hear suggestions about how to improve the style. I still haven’t got my head around everything coming back as a tuple for instance.