How to access data from SQLite database

Rasa Open Source
1

Hi,

I’m a newbie. Can anyone tell me how to access or retrieve data from SQLite database ( user asks the status of their ticket and bot fetches the status of the ticket from database). I have followed Retail demo bot but in that project BOT replies a message. I want to print entire row. Thanks in advance.

2

Check rasa livecoding videos and examples done by @rctatman personal_website_bot.

3

@Shan https://www.youtube.com/watch?v=spyKFz6pKM8&t=2861s check it.

5

@Shan Not get you? Can you share some screenshot and process you followed?

7

@Shan Did you created some sqlite connection file or you mentioned directly in actions file? @Shan Did you have too much data to read ?

9

@Shan I hope you imported this right?

import sqlite3
from sqlite3 import Error

@Shan Try give relative path copy eng.db in the same file.

conn = create_connection(“eng.db”)

Can be a permission issue.

10

I changed the path but it didn’t work.

11

@Shan Can you share that snippet code again with all import libraries files. Thanks

13

@Shan I hope you changed this path (“C:/Users/SHANVIJ/rasacb/actions/eng.db”) and copy the eng.db in actions folder with action.py file + eng.db and simply give “eng.db”.

@Shan Change this user_details_form to action_details_form (change this in domain.yml file also)

15

@Shan Great!! Can you share the updated code for the others? It will help other user. It can be your Rasa Version also. It good you solved it.

16

db = database file path

class ActionQuery(Action):

def name(self) -> Text:
    return "action_query"

def run(
        self,
        dispatcher: CollectingDispatcher,
        tracker: Tracker,
        domain: Dict[Text, Any],
) -> List[Dict[Text, Any]]:

    connection = sqlite3.connect(db)
    cursor = connection.cursor()
    Qtno = [tracker.get_slot("tno")]

     cursor.execute("SELECT * FROM englishdata WHERE TicketId=?", Qtno)
    data_row = cursor.fetchall()
    if len(list(data_row)) < 1:
        dispatcher.utter_message('Your  ticket Id is not in database')
    else:
        for row  in random.sample(data_row, 1):
            dispatcher.utter_message(f'{row[0]}')