def do_insert_audio(index_client, conn, cursor, table_name, audio_path):
try:
init_table(index_client, conn, cursor, table_name)
wavs = os.listdir(audio_path)
wavs.sort()
embeddings = []
ids_audio = []
for wav in wavs:
# print("---wav:", wav)
if ".wav" in wav:
ids_wav, vectors_wav = get_audio_embedding(audio_path + '/' + wav)
if vectors_wav:
get_spectorgram(audio_path, wav)
embeddings.append(vectors_wav)
ids_audio.append(ids_wav)
# print("len of embeddings", len(embeddings))
ids_milvus = insert_vectors(index_client, table_name, embeddings)
file_name = str(uuid.uuid1()) + ".csv"
get_ids_file(ids_milvus, ids_audio, file_name)
print("load data to mysql:", file_name)
load_data_to_mysql(conn, cursor, table_name, file_name)
return "insert successfully!"
except Exception as e:
write_log(e, 1)
return "Error with {}".format(e)
def delete_table(conn, cursor, table_name):
sql = "drop table if exists " + table_name + ";"
try:
cursor.execute(sql)
print("MYSQL delete table.")
except:
print("MYSQL ERROR:", sql, e)
write_log(e, 1)
def create_table_mysql(conn, cursor, table_name):
sql = "create table if not exists " + table_name + "(milvus_id bigint, audio_id text, index ix_milvus (milvus_id));"
try:
cursor.execute(sql)
print("MYSQL create table.")
except Exception as e:
print("MYSQL ERROR:", sql, e)
write_log(e, 1)
def delete_all_data(conn, cursor, table_name):
sql = 'delete from ' + table_name + ';'
try:
cursor.execute(sql)
conn.commit()
print("MYSQL delete all data.")
except:
print("MYSQL ERROR:", sql, e)
write_log(e, 1)
def do_count_table(index_client, conn, cursor, table_name):
if not table_name:
table_name = DEFAULT_TABLE
write_log("doing count, table_name:" + table_name)
print("doing count, table_name:", table_name)
num_milvus = count_collection(index_client, table_name)
num_mysql = count_table(conn, cursor, table_name)
return num_milvus, num_mysql
def count_table(conn, cursor, table_name):
sql = "select count(milvus_id) from " + table_name + ";"
try:
cursor.execute(sql)
results = cursor.fetchall()
print("MYSQL count table.")
return results[0][0]
except Exception as e:
print("MYSQL ERROR:", sql, e)
write_log(e, 1)
def get_spectorgram(audio_path, wav):
try:
sound_info, frame_rate = get_wav_info(audio_path, wav)
pylab.figure(num=None, figsize=(19, 12))
pylab.subplot(111)
# pylab.title('spectrogram of %r' % wav_file)
pylab.specgram(sound_info, Fs=frame_rate)
pylab.savefig(audio_path + '/' + wav.replace('.wav', '.jpg'))
except Exception as e:
write_log(e, 1)
print("Error with", e)
def connect_mysql():
try:
# conn = pymysql.connect(host="127.0.0.1",user="******",port=3306,password="******",database="mysql", local_infile=True)
conn = pymysql.connect(host=MYSQL_HOST,
user=MYSQL_USER,
port=MYSQL_PORT,
password=MYSQL_PWD,
database=MYSQL_DB,
local_infile=True)
return conn
except Exception as e:
print("MYSQL ERROR: connect failed", e)
write_log(e, 1)
def get_audio_embedding(path):
try:
audio, _ = librosa.core.load(path, sr=32000, mono=True)
audio = audio[None, :]
at = AudioTagging(checkpoint_path=None, device='cuda')
_, embedding = at.inference(audio)
embedding = embedding / np.linalg.norm(embedding)
embedding = embedding.tolist()[0]
return path, embedding
except Exception as e:
print("error with embedding:", path)
write_log(e, 1)
return path, None
def search_by_milvus_ids(conn, cursor, ids, table_name):
str_ids = str(ids)
str_ids = str(str_ids).replace('[', '').replace(']', '')
sql = "select audio_id from " + table_name + " where milvus_id in (" + str_ids + ") order by field (milvus_id," + str_ids + ");"
# print(sql)
try:
cursor.execute(sql)
results = cursor.fetchall()
results = [res[0] for res in results]
print("MYSQL search by milvus id.")
return results
except Exception as e:
print("MYSQL ERROR:", sql, e)
write_log(e, 1)
def load_data_to_mysql(conn, cursor, table_name, file_name):
sql = "load data local infile '" + file_name + "' into table " + table_name + " fields terminated by ',';"
try:
cursor.execute(sql)
conn.commit()
print("MYSQL load table.")
except Exception as e:
print("MYSQL ERROR:", sql, e)
write_log(e, 1)
finally:
if os.path.exists(file_name):
with open(file_name) as f:
line = f.readlines()
print("-----------MySQL insert info--------len:" +
str(len(line)))
write_log("-----------MySQL insert info--------len:" +
str(len(line)))
os.remove(file_name)
from audio.indexer.logs import write_log
write_log("Start app service.")
# import logging
# logging.basicConfig(filename='app.log', filemode='w', format='%(asctime)s - %(name)s - %(levelname)s - %(message)s', level=logging.INFO)
# import tensorflow as tf
# config = tf.ConfigProto()
# config.gpu_options.allow_growth = True
# config.gpu_options.per_process_gpu_memory_fraction = 0.5
# sess = tf.Session(config=config)
