Site Tools


linux:ubuntu:spotifymusictodatabase

Add played music to database

This script will write all the artists, tracks and albums played on Spotify to a MySQL database.

Database

CREATE TABLE `album` (
  `artist_id` smallint(5) NOT NULL DEFAULT '0',
  `album_id` smallint(5) NOT NULL AUTO_INCREMENT,
  `album_name` char(128) DEFAULT NULL,
  `album_cover` varchar(1000) DEFAULT NULL,
  `album_type` varchar(128) DEFAULT NULL,
  `album_releasedate` date DEFAULT NULL,
  `album_tracknumber` smallint(5) DEFAULT NULL,
  `album_spotifyurl` varchar(128) DEFAULT NULL,
  PRIMARY KEY (album_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `artist` (
  `artist_id` smallint(5) NOT NULL AUTO_INCREMENT,
  `artist_name` char(128) DEFAULT NULL,
  `artist_spotifyurl` varchar(128) DEFAULT NULL,
  PRIMARY KEY (artist_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `played` (
  `artist_id` smallint(5) NOT NULL DEFAULT '0',
  `album_id` smallint(5) NOT NULL DEFAULT '0',
  `track_id` smallint(5) NOT NULL DEFAULT '0',
  `played` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `track` (
  `artist_id` smallint(5) NOT NULL DEFAULT '0',
  `album_id` smallint(5) NOT NULL DEFAULT '0',
  `track_id` smallint(5) NOT NULL AUTO_INCREMENT,
  `track_name` char(255) DEFAULT NULL,
  `track_popularity` int(20) DEFAULT NULL,
  `track_tracknumber` smallint(5) DEFAULT NULL,
  `track_previewurl` varchar(128) DEFAULT NULL,
  `track_spotifyurl` varchar(128) DEFAULT NULL,
  PRIMARY KEY (track_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

linux/ubuntu/spotifymusictodatabase.txt · Last modified: 2021/12/29 00:53 by lunetikk