fix this code: SELECT inquiryid, FROM i2ms-debug.inquiryanalyticsvw as i WHERE i.type =…
- User
fix this code: SELECT inquiry_id, FROM
i2ms-debug.inquiry_analytics_vw as i WHERE i.type = 'TINQ' AND i.received_date > '2010-01-01 00:00:00'; - Assistant
The code you provided is missing the column name after the SELECT keyword. Additionally, it seems like you are providing the correct table and filter conditions. Here's the corrected code:
SELECT inquiry_id FROM
i2ms-debug.inquiry_analytics_vw AS i WHERE i.type = 'TINQ' AND i.received_date > '2010-01-01 00:00:00'; - User
def find_sim(open_inquiry_id:int) -> dict: mydbconfig = db.get_config() mydb = connection.MySQLConnection()
# In the Azure Function App which is on Linux, files can only be installed in the temporary folder, which on Linux is /tmp temp_file_path = tempfile.gettempdir() """ Input: Open inquiry_id Goal: Based on open inquiry_id, find clean_text for that inquiry, tokenize it to be list of strings instead of one string. Tokenized clean_text is used to infer a vector (using the infer_vector function) for the inquiry using information learned in the pretrained model. Using the most_similar function, return list of inquiry_id numbers in order from most to least similar. Output: List of recommended inquiry_id numbers sorted from highest to lowest similarity. """ open_inquiry_id = [open_inquiry_id] query_open_inq = """SELECT inquiry_id, clean_text FROM inquiry_analytics_vw i WHERE i.inquiry_id = %s;""" query_link_inq = """SELECT inquiry_id, closed_id FROM inquiry_reference_closed_tbl i where i.inquiry_id = %s;""" try: # Here we are connecting to the database to query the data for an open inquiry which we aim to find recommendations for, with recommendations coming from # closed inquiries. We also will be removing already linked inquiries from being recommended for an open inquiry. mydb = connection.connect(**mydbconfig) mycursor = mydb.cursor() mycursor.execute(query_open_inq, open_inquiry_id) result_open = [mycursor.fetchone()] mycursor = mydb.cursor() mycursor.execute(query_link_inq, open_inquiry_id) result_link = mycursor.fetchall() mydb.close() except Exception as e: mydb.close() raise RuntimeError(str(e)) else: if result_open[0] == None: return { 'error': { 'message': 'The inquiry is not eligible for inquiry recommendations.', 'code': 400 } } model_file_name = 'ti_rec_model_doc2vec' model_file_path = temp_file_path + '/' + model_file_name # Checks if doc2vec file exist; if not download file from Azure Storage container is_model_file_found = os.path.isfile(model_file_path) if is_model_file_found == False: azure_helper.download_file('ml-models', model_file_name, model_file_path) rec_model = Doc2Vec.load(model_file_path) open_inq_clean_text = result_open[0][1] if open_inq_clean_text == None: clean_text_result = clean_inquiry_text.clean_text_now(int(open_inquiry_id[0])) if clean_text_result['status'] == True: open_inq_clean_text = clean_text_result['clean_text'] if open_inq_clean_text != None: open_inq_text = word_tokenize(open_inq_clean_text) open_inq_vector = rec_model.infer_vector(open_inq_text) id_tuple = rec_model.dv.most_similar(open_inq_vector, topn = len(rec_model.dv.vectors)) results = pd.DataFrame(id_tuple, columns = ['inquiry_id', 'similarity']) results['inquiry_id'] = results['inquiry_id'].astype(int) linked_inqs = [] for i in range(len(result_link)): linked_inqs.append(result_link[i][1]) # [1] is the linked inquiry_id, [0] is the original inquiry_id results = results[~(results['inquiry_id'].isin(linked_inqs))] recs = results.head(20).inquiry_id.tolist() print('execute_ti_rec_model executed.') return { 'records': recs, 'count': len(recs) } else: print('The open inquiry subject or description does not have enough information to generate inquiry recommendations.') return { 'error': { 'message': 'The inquiry subject, description, or notes does not have enough information to generate recommendations. Please update the inquiry and try again.', 'code': 400 } }WHAT DOES id_tuple = rec_model.dv.most_similar(open_inq_vector, topn = len(rec_model.dv.vectors)) DO
- Assistant
The line "id_tuple = rec_model.dv.most_similar(open_inq_vector, topn = len(rec_model.dv.vectors))" in the given code uses the
most_similar()function of the Doc2Vec model to find the most similar vectors to theopen_inq_vectorvector.The
most_similar()function returns the top-N most similar vectors and their corresponding similarity scores. In this case, the function is givenopen_inq_vectoras the vector for which we want to find the most similar vectors, andlen(rec_model.dv.vectors)as the number of most similar vectors to retrieve.The line saves the results of the
most_similar()function in theid_tuplevariable, which will be a list of tuples containing the inquiry ID and similarity score for the most similar vectors.For example,
id_tuplemay contain [(1001, 0.95), (2003, 0.92), (3005, 0.89), ...] where each tuple represents an inquiry ID and its similarity score.Note: The provided code seems to be using the Gensim library's Doc2Vec implementation.
- User
are the other emeddings in the rec_model?
- Assistant
Yes, the other embeddings (vector representations) for all the inquiries are stored in the
rec_model. Therec_model.dv.vectorsattribute contains the embeddings for all the inquiries in the model.So, when the line
id_tuple = rec_model.dv.most_similar(open_inq_vector, topn = len(rec_model.dv.vectors))is executed, it retrieves the most similar vectors toopen_inq_vectorfrom all the embeddings in therec_modelbased on their similarity scores. - User
with connection.connect(**mydbconfig) as mydb: mycursor = mydb.cursor()
mycursor.execute(query_open_inq, (open_inquiry_id,)) xq = mycursor.fetchone()how do i convert the data to a df in python
- Assistant
To convert the fetched data into a DataFrame in Python, you can use the pandas library. Here's an example of how you can convert the fetched data
xqinto a DataFrame:import pandas as pd # Assuming `xq` contains the fetched data df = pd.DataFrame(xq, columns=['inquiry_id', 'clean_text']) # Now `df` will contain the fetched data as a DataFrameMake sure to adjust the column names (
['inquiry_id', 'clean_text']) in thecolumnsparameter based on the actual column names returned by the database query.Also, ensure that you have the pandas library installed. If you don't, you can install it using
pip install pandas.