sqlite: Calling database each time function is called

Question

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.

Ah okay. Thank you! When I want to select a row in my column tag you would then think that you could just do: "SELECT whatever FROM intents WHERE tag = {}".format(results[0][0])) (result[0][0] is the tag I want to get the row corresponding to) However I get the error: "no such coloumn" Does WHERE only work with integers? — Arthos
– Arthos, Commented Jan 24, 2020 at 21:09
You should be binding user-supplied values to parameters in a query, not trying to insert random stuff directly in a query string. See the many examples in the documentation and here. — Shawn
– Shawn, Commented Jan 24, 2020 at 21:11

Barmar · Accepted Answer · 2020-01-24 20:54:48Z

1

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("..."))

answered Jan 24, 2020 at 20:54

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

glglgl · Accepted Answer · 2020-01-24 21:12:19Z

0

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.

answered Jan 24, 2020 at 21:12

glglgl

91.5k13 gold badges157 silver badges230 bronze badges

2 Comments

Arthos Over a year ago

Okay good to know, thank you very much. Regarding the row_num it is currently a column I have made by enumarting a number while looping through the rest of my data. Is this the correct way to do it or does sqlite actually have a way built in I have missed?

Barmar Over a year ago

@Arthos You can select a range like WHERE row_num BETWEEN 1 AND 10, and use ORDER BY row_num. This is useful for paging.

Collectives™ on Stack Overflow

sqlite: Calling database each time function is called

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related