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)