PostgreSQL for Low Power FM Radio

Confession: I'm into terrestrial radio.

I volunteer my time doing IT projects for the local LPFM. If you want to read all about this station head on over to kvwj.org. It's pretty cool what has been done with next to zero budget.

Back in 2024 the director and chief engineer was wondering how he could get a list of the songs that have played on the air over the last four weeks. He was wanting to analyze just how often songs are playing. This station uses the free version of ZaraRadio. It gives you playback logs for every day. Going through the logs and figuring out what played is a big hassle though. Here's a small sample of what a daily log looks like:

06:32:39	start	C:\Users\KVWJ\Desktop\ye olde new gold\playback\clockwheel rotation\B medium oldie\A Well Respected Man - Kinks - 1965.mp3
06:35:16	start	C:\Users\KVWJ\Desktop\ye olde new gold\playback\clockwheel rotation\C slow oldie\It's Just A Matter Of Time - Brook Benton - 1959.mp3
06:37:41	start	C:\Users\KVWJ\Desktop\ye olde new gold\playback\clockwheel rotation\D extra slow oldie\Your Mama Said You Cried - Kenny Dino - 1961.mp3

I thought it shouldn't be too bad to write a Python script that can scrape these logs and throw them into a SQL database. I took a few hours over a week or so and got a proof of concept working. I had a script running in a loop and grabbing lines from the log that matched the pattern above. I started with a SQLite database with a flat one table structure.

The next thing I worked on was a web interface to interact with the SQLite database. I have some experience in Django, so I cobbled together a project based on django-tables2 and got the data displaying on a web page. I then threw a download button on it so that our director could have a nice csv of the data to take with him.

I eventually realized that this flat SQLite database would not be sustainable.

I then got to work restructuring the database so it wasn't basically a large Excel spreadsheet. I broke it off into artist, track, category, year, and playback tables. The rows in the playback table corresponding to the log lines above now looks like this:

id,track,date,timestamp
4244293,2511,2026-10-20,06:37:41
4244292,1659,2026-10-20,06:35:16
4244291,1207,2026-10-20,06:32:39

This makes the database much more efficient.

I was at this point fighting with Django. Randomly it would get really slow. I decided it was time to move from the SQLite database to a more powerful PostgreSQL database. I created tables with the same name in the new database and then dumped the SQLite tables and imported them into the new PostregSQL tables.

And this is just part 1 is a series of posts I will be making about this.