Hi,
Since I am quite happy with the result, I just want to share a very little project I realized.
Task: Randomly play audiobook from youtube mediathek.
Thanks to Kolja and his awesome ytube music player and adonno manual to build a standalone tagreader (which brought my solder skills to the limit 
) ,
i already implemented a tag reader jukebox for the kids, where they can choose out of a few
labeled rfc cards what youtube album (audio book) they want to hear.
Now I just received two xiaomi magic cubes which I decided to give to the kids to control stuff
in their rooms like lights and audio.
For shaking the cube I wanted to randomly play an audio book out of each kids mediathek or better out
of its pile of rfc tags (as a matter of fact they are all in my accounts mediathek - they don’t
have their own yet).
Realization:
Since all the youtube media-ids are already in the automation file for the tag reader, i first thought of
using a python script to read the lines where media ids are listed and choose one randomly as return.
Trying that I realized I need to import random. As far as i know import is not possible in scripts
running with python_script integration.
Installing pyscript made my system (docker pi3+) very slow. Don’t know why.
Anyway, I decided to skip python and choose another approach using sql, which was in my mind from the beginning.
Therefore I added another table to my mariadb homeassistant db.
There I added all media-ids, media-type and title from my jukebox.yaml and added flags for each family
member to assign it to them.
In configuration.yaml I added a sql sensor for each family member.
It uses the rand() function which works with mysql and relatives.
The scan_interval is high, because i don’t need automatic refreshes only triggered by automation.
Like:
- platform: sql
db_url: !secret recorder_db_url
scan_interval: 360000
queries:
- name: random_album_kid1
query: "SELECT * FROM yt_albums WHERE kid1 = 'X' ORDER BY RAND() LIMIT 1;"
column: "yt_id"
In the automation.yaml i added an action which first updates the sensor and then start playback
on the relevant ytube mediaplay and cast device. triggerd by shaking the magic cube.
- service: homeassistant.update_entity
target:
entity_id:
- sensor.random_album_kid1
- service: media_player.play_media
data:
entity_id: '{{ ytube_player_entitiy_id }}'
media_content_id: '{{ states.sensor.random_album_kid1.state }}'
media_content_type: '{{ states.sensor.random_album_kid1.attributes.typ }}'
The entity in home assistant:
That’s it. Hope sharing this helps anyone to achieve their goal
Probably there are other maybe easier ways to realize a task like this. Nevertheless i am
satisfied with the result and it was quick and easy to implement.
Regards
Christian