(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)