Results 1 to 3 of 3

Thread: BASH to create SQL Insert statements?

  1. #1
    Join Date
    Mar 2007
    Location
    TN, US
    Beans
    607
    Distro
    Ubuntu

    BASH to create SQL Insert statements?

    I am in the process of creating a music database as part of a class project, and I am looking for a way to cut the amount of time required for data entry. All of my music is organized in folders by Artist, and then album inside my Music folder. Using tree, I was able to generate a file that shows my music like this:
    Code:
    |-- Audioslave
    |   |-- Audioslave
    |   |   |-- 01 Cochise.mp3
    |   |   |-- 02 Show Me How to Live.mp3
    |   |   |-- 03 Gasoline.mp3
    |   |   |-- 04 What You Are.mp3
    |   |   |-- 05 Like a Stone.mp3
    |   |   |-- 06 Set It Off.mp3
    |   |   |-- 07 Shadow on the Sun.mp3
    |   |   |-- 08 I Am the Highway.mp3
    |   |   |-- 09 Exploder.mp3
    |   |   |-- 10 Hypnotize.mp3
    |   |   |-- 11 Bring Em Back Alive.mp3
    |   |   |-- 12 Light My Way.mp3
    |   |   |-- 13 Getaway Car.mp3
    |   |   `-- 14 The Last Remaining Light.mp3
    |   `-- Out of Exile
    |       |-- 01 Your Time Has Come.mp3
    |       |-- 02 Out of Exile.mp3
    |       |-- 03 Be Yourself.mp3
    and so on. The file is about 1500 lines long, and manually entering the first album resulted in about 115 lines of code, resulting in about 12,000 lines of code to be entered.

    I was thinking that a BASH script could generate the SQL for me by substituting the song names and album names in the statements. The problem is that I am super rusty on my BASH, and don't know where to begin. Any advice?
    Registered Linux user 446122 , Registered Machine 352936.

  2. #2
    Join Date
    Jul 2006
    Location
    somewhere :)
    Beans
    535
    Distro
    Ubuntu 10.04 Lucid Lynx

    Re: BASH to create SQL Insert statements?

    i don't think this tree format is particularly parseable. i'd recommend going into the base directory for your music and trying something like:

    Code:
    find . -name "*.mp3" > mymusicfiles
    the resulting file should be easier to parse.
    there are 10 types of people in the world: those that understand binary and i don't know who the other F are.

  3. #3
    Join Date
    Oct 2006
    Location
    Argentina
    Beans
    584
    Distro
    Ubuntu

    Re: BASH to create SQL Insert statements?

    This example is for sqlite (I didn't test it):

    Code:
    database=your-database
    table=table
    
    find . -name "*.mp3" | (
    
    	echo "BEGIN TRANSACTION;"
    
    	while read -r line ; do
    		album=${line%/*} album=${album##*/}
    		title=$(line##*/}
    
    		echo "INSERT INTO \"$table\" VALUES('$album','$title');"
    	done
    
    	echo "COMMIT;"
    
    ) | sqlite3 $database

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •