how do i store then call a mp3 from sqlite3 .db file

how do i store then call a mp3 from sqlite3 .db file - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: how do i store then call a mp3 from sqlite3 .db file (/thread-31434.html)

how do i store then call a mp3 from sqlite3 .db file - gr3yali3n - Dec-10-2020

what would be the best way to store and then call and play music files from a .db file?
i have been trying to figure out how i would store music in a file then a came across sqlite3 so i have been studying this for a week of so now. i see how to create tables and such but im wondering can this be used to store mp3s (surely it could right) but the process of calling the stored mp3 from sqlite3 .db file then playing it..

i figured that i could create a table that has (artist , album , song , path/to/song)
and the call that some how but i am not sure if this is possible my, research hasn't shown this yet.

RE: how do i store then call a mp3 from sqlite3 .db file - MrBitPythoner - Dec-10-2020

You could theorettically call a SELECT statement for the sqlite db and store the mp3 files, assuming they are accesible. Then you would play them with playsound library.

RE: how do i store then call a mp3 from sqlite3 .db file - MrBitPythoner - Dec-10-2020

https://pypi.org/project/playsound/

RE: how do i store then call a mp3 from sqlite3 .db file - snippsat - Dec-11-2020

(Dec-10-2020, 05:56 PM)gr3yali3n Wrote: i figured that i could create a table that has (artist , album , song , path/to/song)

That's is the more normal way to do it,then it use file system to storing and retrieving files.

Can store binary data like audio,video,images...ect directly in db,it's called BLOB (Binary Large Object).
It's compact in sqlite as all data is in one .db file,but can soon get large if need to store a lot of songs.
Can give a example i have for images that have,just rewrite a little to take audio.
So audio.db for these two song is 17MB.

Create

import sqlite3

conn = sqlite3.connect('audio.db')
cursor = conn.cursor()
cursor.execute("DROP TABLE IF EXISTS AUDIO")

# Creating table as per requirement
sql = "CREATE TABLE audio_table (id, name TEXT NOT NULL, audio BLOB NOT NULL, resume BLOB NOT NULL);"
cursor.execute(sql)
print("Table created successfully.....")

Insert data

import sqlite3

def to_binary(filename):
    '''Convert data to binary format'''
    with open(filename, 'rb') as file:
        blob_data = file.read()
    return blob_data

def insert_blob(emp_id, name, audio, resume_file):
    try:
        sqlite_connection = sqlite3.connect('audio.db')
        cursor = sqlite_connection.cursor()
        print("Connected to SQLite")
        sqlite_query = "INSERT INTO audio_table (id, name, audio, resume) VALUES (?, ?, ?, ?)"
        emp_audio = to_binary(audio)
        resume = to_binary(resume_file)
        # Convert data into tuple format
        data_tuple = (emp_id, name, emp_audio, resume)
        cursor.execute(sqlite_query, data_tuple)
        sqlite_connection.commit()
        print("Image and file inserted successfully as a BLOB into a table")
        cursor.close()

    except sqlite3.Error as error:
        print(f"Failed to insert blob data into sqlite table {error}")
    finally:
        if sqlite_connection:
            sqlite_connection.close()
            print("The sqlite connection is closed")

if __name__ == '__main__':
    insert_blob(1, "Adele - Set Fire To The Rain", "Adele - Set Fire To The Rain.mp3", "Adele - Set Fire To The Rain.txt")
    insert_blob(2, "Clean Bandit", "Clean Bandit.mp3", "Clean Bandit.txt")

Read data

import sqlite3

def write_file(data, filename):
    '''Convert binary data and write it on Hard Disk'''
    with open(filename, 'wb') as file:
        file.write(data)
    print(f"Stored blob data into:{filename}\n")

def read_blob(emp_id, audio_path, describe_path):
    try:
        sqlite_con = sqlite3.connect('audio.db')
        cursor = sqlite_con.cursor()
        print("Connected to SQLite")
        sql_blob_query = "SELECT * from audio_table where id = ?"
        cursor.execute(sql_blob_query, (emp_id,))
        record = cursor.fetchall()
        for row in record:
            name  = row[1]
            photo = row[2]
            resumeFile = row[3]
            print("Storing employee image and resume on disk \n")

            audio = f"{audio_path}{name}.mp3"
            describe = f"{describe_path}{name}.txt"
            write_file(photo, audio)
            write_file(resumeFile, describe)
        cursor.close()

    except sqlite3.Error as error:
        print(f"Failed to read blob data from sqlite table {error}")
    finally:
        if sqlite_con:
            sqlite_con.close()
            print("sqlite connection is closed")

if __name__ == '__main__':
    audio_path = "G:/div_code/audio_env/from_db/"
    describe_path = "G:/div_code/audio_env/from_db/"
    read_blob(1, audio_path, describe_path)
    read_blob(2, audio_path, describe_path)