CODEWITHSUNDEEP
pythonsqlite
Arthos
Arthos

Reputation: 453

sqlite: Calling database each time function is called

I got a function that is called to calculate a response every time the user inputs something. The function gets the response from a database. What I don't understand is, why I have to redefine my variable (I have called it intents_db) that contains all the data from the database each time the function is called? I have tried putting it outside the function, but then my program only works the first time, but the returns an empty answer the second time the user inputs something.

def response(sentence, user_id='1'):
    results = classify_intent(sentence)

    intents_db = c.execute("SELECT row_num, responses, tag, responses, intent_type, response_type, context_set,\
                                    context_filter FROM intents")

    if results:
        # loop as long as there are matches to process
        while results:

            if results[0][1] > answer_threshold:
                for i in intents_db:
                    # print('tag:', i[2])
                    if i[2] == results[0][0]:
                        print(i[6])
                        if i[6] != 'N/A': 
                            if show_details:
                                print('context: ', i[6]) 
                            context[user_id] = i[6] 
                            responses = i[1].split('&/&')
                            print(random.choice(responses))

                        if i[7] == 'N/A' in i or \
                                (user_id in context and i[7] in i and i[7] == context[
                                    user_id]):
                            # a random response from the intent
                            responses = i[1].split('&/&')
                            print(random.choice(responses))

                        print(i[4], i[5])
                print(results[0][1])
            elif results[0][1] <= answer_threshold:
                print(results[0][1])
                for i in intents_db:
                    if i[2] == 'unknown':
                        # a random response from the intent
                        responses = i[1].split('&/&')
                        print(random.choice(responses))
                        initial_comm_output = random.choice(responses)
                        return initial_comm_output
            else:
                initial_comm_output = "Something unexpected happened when calculating response. Please restart me"
                return initial_comm_output
            results.pop(0)

    return results

Also, I started getting into databases and sqlite3 because I want to make a massive database long term. Therefore it also seems inefficient that I have to load the whole database at all. Is there some way I can only load the row of data I need? I got a row_number column in my database, so if it was somehow possible to say like: "SELECT WHERE row_num=2 FROM intents" that would be great, but I can't figure out how to do it.

Upvotes: 0

Views: 57

Answers (2)

glglgl
glglgl

Reputation: 91017

Therefore it also seems inefficient that I have to load the whole database at all. Is there some way I can only load the row of data I need? I got a row_number column in my database, so if it was somehow possible to say like: "SELECT WHERE row_num=2 FROM intents" that would be great, but I can't figure out how to do it.

You nearly got it: it is

intents_db = c.execute("SELECT row_num, responses, tag, responses, intent_type,
                            response_type, context_set, context_filter
                        FROM intents WHERE row_num=2")

But don't do the mistake other database beginners make and try to put in some variable from your program directly into that string. This makes the program prone to SQL injections.

Rather, do

row_num = 2
intents_db = c.execute("SELECT row_num, responses, tag, responses, intent_type,
                            response_type, context_set, context_filter
                        FROM intents WHERE row_num=?", (row_num,))

Of course, you can also set conditions for other fields.

Upvotes: 0

Barmar
Barmar

Reputation: 780861

cursor.execute() returns an iterator, and you can only loop over it once.

If you want to reuse it, turn it into a list:

intents_db = list(c.execute("..."))

Upvotes: 1

Related Questions