Python Forum
how do i store then call a mp3 from sqlite3 .db file
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
how do i store then call a mp3 from sqlite3 .db file
#1
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.
Reply
#2
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.
Reply
#3
https://pypi.org/project/playsound/
Reply
#4
(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)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Regex text file to store data in list TheSithSiggi 1 1,500 Dec-03-2020, 04:46 PM
Last Post: bowlofred
  subprocess call cannot find the file specified RRR 6 16,386 Oct-15-2020, 11:29 AM
Last Post: RRR
  ZIP file in Sqlite3 database chesschaser 4 3,425 Jul-23-2020, 09:53 PM
Last Post: chesschaser
  How do I store the data in another txt file blacklight 1 1,904 Jun-26-2020, 11:09 AM
Last Post: Larz60+
  module to store functions/variables and how to call them? mstichler 3 2,344 Jun-03-2020, 06:49 PM
Last Post: mstichler
  ChatterBot: How to store unanswered question in a text file? animrehrm 0 1,877 May-16-2020, 06:00 AM
Last Post: animrehrm
  Call a python file with anothr python file skaailet 3 2,264 Apr-30-2020, 03:41 PM
Last Post: deanhystad
  Call a .xlsx file outside a class criscferr 2 1,823 Apr-24-2020, 04:23 PM
Last Post: criscferr
  Read csv file, parse data, and store in a dictionary markellefultz20 4 4,487 Nov-26-2019, 03:33 PM
Last Post: DeaD_EyE
  How to call a function from exe file via CMD gahhon 0 1,569 Feb-19-2019, 03:21 PM
Last Post: gahhon

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020