Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

fix this code: SELECT inquiryid, FROM i2ms-debug.inquiryanalyticsvw as i WHERE i.type =…

  1. 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';

  2. 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';

  3. 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

  4. 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 the open_inq_vector vector.

    The most_similar() function returns the top-N most similar vectors and their corresponding similarity scores. In this case, the function is given open_inq_vector as the vector for which we want to find the most similar vectors, and len(rec_model.dv.vectors) as the number of most similar vectors to retrieve.

    The line saves the results of the most_similar() function in the id_tuple variable, which will be a list of tuples containing the inquiry ID and similarity score for the most similar vectors.

    For example, id_tuple may 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.

  5. User

    are the other emeddings in the rec_model?

  6. Assistant

    Yes, the other embeddings (vector representations) for all the inquiries are stored in the rec_model. The rec_model.dv.vectors attribute 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 to open_inq_vector from all the embeddings in the rec_model based on their similarity scores.

  7. 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

  8. 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 xq into 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 DataFrame
    

    Make sure to adjust the column names (['inquiry_id', 'clean_text']) in the columns parameter 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.