How to retrieve data from sqlite Db and set to kivy TextInput fields?

I'm learning kivy by making small applications from kivy docs and other online resources. The current code has two textinput fields (UserID, UserName) to store values in the db, using RecycleView the stored data is displayed on buttons with the Kivy GUI.

On button press, I require corresponding data from the db to be set to the respective textinput fields. i.e. On pressing button with UserID 1 , textinput field should display values of UserID and UserNames from that row.

main.py

import sqlite3
from kivy.app import App
from kivy.uix.boxlayout import BoxLayout
from kivy.uix.recycleview.views import RecycleDataViewBehavior
from kivy.uix.button import Button
from kivy.properties import BooleanProperty, ListProperty, StringProperty, ObjectProperty
from kivy.uix.recyclegridlayout import RecycleGridLayout
from kivy.uix.behaviors import FocusBehavior
from kivy.uix.recycleview.layout import LayoutSelectionBehavior

class SelectableRecycleGridLayout(FocusBehavior, LayoutSelectionBehavior,
                                  RecycleGridLayout):
    ''' Adds selection and focus behaviour to the view. '''


class SelectableButton(RecycleDataViewBehavior, Button):
    ''' Add selection support to the Button '''
    index = None
    selected = BooleanProperty(False)
    selectable = BooleanProperty(True)

    def refresh_view_attrs(self, rv, index, data):
        ''' Catch and handle the view changes '''
        self.index = index
        return super(SelectableButton, self).refresh_view_attrs(rv, index, data)

    def on_touch_down(self, touch):
        ''' Add selection on touch down '''
        if super(SelectableButton, self).on_touch_down(touch):
            return True
        if self.collide_point(*touch.pos) and self.selectable:
            return self.parent.select_with_touch(self.index, touch)

    def apply_selection(self, rv, index, is_selected):
        ''' Respond to the selection of items in the view. '''
        self.selected = is_selected

class RV(BoxLayout):
    ''' Creates Db conn, table, and saves data, retrives stored data and
    displays in the RecycleView .'''
    data_items = ListProperty([])

    def __init__(self, **kwargs):
        super(RV, self).__init__(**kwargs)
        self.create_table()
        self.get_users()

    def create_table(self):
        connection = sqlite3.connect("demo.db")
        cursor = connection.cursor()
        sql = """CREATE TABLE IF NOT EXISTS Users(
        UserID integer PRIMAY KEY,
        UserName text NOT NULL)"""
        cursor.execute(sql)
        connection.close()

    def get_users(self):
        connection = sqlite3.connect("demo.db")
        cursor = connection.cursor()

        cursor.execute("SELECT * FROM Users ORDER BY UserID ASC")
        rows = cursor.fetchall()

        # create data_items
        for row in rows:
            for col in row:
                self.data_items.append(col)

    def save(self):
        connection = sqlite3.connect("demo.db")
        cursor = connection.cursor()

        UserID = self.ids.no.text
        UserName = self.ids.name.text

        try:
            save_sql="INSERT INTO Users (UserID, UserName) VALUES (?,?)"
            connection.execute(save_sql,(UserID, UserName))
            connection.commit()
            connection.close()
        except sqlite3.IntegrityError as e:
            print("Error: ",e)

class TestApp(App):
    title = "Kivy RecycleView & SQLite3 Demo"

    def build(self):
        return RV()


if __name__ == "__main__":
    TestApp().run()

test.kv

#:kivy 1.10.0
<TextInputPopup>:
    title: "Popup"
    size_hint: None, None
    size: 400, 400
    auto_dismiss: False

    BoxLayout:
        orientation: "vertical"
        TextInput:
            id: txtinput
            text: root.obj_text
        Button:
            size_hint: 1, 0.2
            text: "Save Changes"
            on_release:
                root.obj.update_changes(txtinput.text)
                root.dismiss()
        Button:
            size_hint: 1, 0.2
            text: "Cancel Changes"
            on_release: root.dismiss()


<SelectableButton>:
    # Draw a background to indicate selection
    canvas.before:
        Color:
            rgba: (.0, 0.9, .1, .3) if self.selected else (0, 0, 0, 1)
        Rectangle:
            pos: self.pos
            size: self.size

<RV>:
    BoxLayout:
        orientation: "vertical"
        user_no_text_input: no
        user_name_text_input: name

        Label:
            text: "USER NUMBER"
            size_hint: (.5, None)
            height: 30
        TextInput:
            id: no
            size_hint: (.5, None)
            height: 30
            multiline: False
        Label:
            text: "USER NAME"
            size_hint: (.5, None)
            height: 30
        TextInput:
            id: name
            size_hint: (.5, None)
            height: 30
            multiline: False
        Button:
            id: save_btn
            text: "SAVE BUTTON"
            height: 50
            width: 100
            on_press: root.save()

        GridLayout:
            size_hint: 1, None
            size_hint_y: None
            height: 25
            cols: 2

            Label:
                text: "User ID"
            Label:
                text: "User Name"

        BoxLayout:
            RecycleView:
                viewclass: 'SelectableButton'
                data: [{'text': str(x)} for x in root.data_items]
                SelectableRecycleGridLayout:
                    cols: 2
                    default_size: None, dp(26)
                    default_size_hint: 1, None
                    size_hint_y: None
                    height: self.minimum_height
                    orientation: 'vertical'
                    multiselect: True
                    touch_multiselect: True

Solution

The solution is as follow: Please refer to snippets, example, and output for details.

kv file

Add on_press event under class rule, <SelectableButton>:. When on_press event is fire, it will invoke root.on_press(self) method in the root class, RV, and pass an instance of the button, self.
Move the two ObjecProperty hooks (user_no_text_input: no, user_name_text_input: name) to the right location i.e. from under BoxLayout: to <RV>:.
Replace data: [{'text': str(x)} for x in root.data_items] with data: root.data_items

Snippet - kv

<SelectableButton>:
    ...
    on_press:
        app.root.on_press(self)

<RV>:
    user_no_text_input: no
    user_name_text_input: name

    BoxLayout:
        orientation: "vertical"

        Label:
        ...

        BoxLayout:
            RecycleView:
                viewclass: 'SelectableButton'
                data: root.data_items
                SelectableRecycleGridLayout:

Python Code

In class RV, do the following:

Add NumericProperty to import statement for kivy.properties.
Add ObjecProperty for user_no_text_input = ObjectProperty(None) and user_name_text_input = ObjectProperty(None)
Add NumericProperty for total_col_headings = NumericProperty(0)
Create a on_press() method for the on_press event.
Create a get_table_column_headings() method to get the total column headings in the table. This is used to create the column range.
Enhance get_users() method, to create a list with db column value, db primary key value, and db column range. The trick of using database column range is for populating the TextInput widgets with values from the row clicked/pressed.

Snippet - Python

from kivy.properties import BooleanProperty, ListProperty, NumericProperty, ObjectProperty
...

class RV(BoxLayout):
    ''' Creates Db conn, table, and saves data, retrives stored data and
    displays in the RecycleView .'''
    data_items = ListProperty([])
    user_no_text_input = ObjectProperty(None)
    user_name_text_input = ObjectProperty(None)
    total_col_headings = NumericProperty(0)

    def __init__(self, **kwargs):
        super(RV, self).__init__(**kwargs)
        self.create_table()
        self.get_table_column_headings()
        self.get_users()

    def on_press(self, instance):
        columns = self.data_items[instance.index]['range']
        self.user_no_text_input.text = self.data_items[columns[0]]['text']
        self.user_name_text_input.text = self.data_items[columns[1]]['text']

    def get_table_column_headings(self):
        connection = sqlite3.connect("demo.db")

        with connection:
            # With the with keyword, the Python interpreter automatically releases the resources.
            # It also provides error handling
            cursor = connection.cursor()
            cursor.execute("PRAGMA table_info(Users)")
            col_headings = cursor.fetchall()
            self.total_col_headings = len(col_headings)
    ...

    def get_users(self):
        connection = sqlite3.connect("demo.db")
        cursor = connection.cursor()

        cursor.execute("SELECT * FROM Users ORDER BY UserID ASC")
        rows = cursor.fetchall()

        # create list with db column, db primary key, and db column range
        data = []
        low = 0
        high = self.total_col_headings - 1

        for row in rows:
            for col in row:
                data.append([col, row[0], [low, high]])
            low += self.total_col_headings
            high += self.total_col_headings

        # create data_items
        self.data_items = [{'text': str(x[0]), 'Index': str(x[1]), 'range': x[2]} for x in data]

Example

main.py

import sqlite3
from kivy.app import App
from kivy.uix.boxlayout import BoxLayout
from kivy.uix.recycleview.views import RecycleDataViewBehavior
from kivy.uix.button import Button
from kivy.properties import BooleanProperty, ListProperty, NumericProperty, ObjectProperty
from kivy.uix.recyclegridlayout import RecycleGridLayout
from kivy.uix.behaviors import FocusBehavior
from kivy.uix.recycleview.layout import LayoutSelectionBehavior


class SelectableRecycleGridLayout(FocusBehavior, LayoutSelectionBehavior,
                                  RecycleGridLayout):
    ''' Adds selection and focus behaviour to the view. '''


class SelectableButton(RecycleDataViewBehavior, Button):
    ''' Add selection support to the Button '''
    index = None
    selected = BooleanProperty(False)
    selectable = BooleanProperty(True)

    def refresh_view_attrs(self, rv, index, data):
        ''' Catch and handle the view changes '''
        self.index = index
        return super(SelectableButton, self).refresh_view_attrs(rv, index, data)

    def on_touch_down(self, touch):
        ''' Add selection on touch down '''
        if super(SelectableButton, self).on_touch_down(touch):
            return True
        if self.collide_point(*touch.pos) and self.selectable:
            return self.parent.select_with_touch(self.index, touch)

    def apply_selection(self, rv, index, is_selected):
        ''' Respond to the selection of items in the view. '''
        self.selected = is_selected


class RV(BoxLayout):
    ''' Creates Db conn, table, and saves data, retrives stored data and
    displays in the RecycleView .'''
    data_items = ListProperty([])
    user_no_text_input = ObjectProperty(None)
    user_name_text_input = ObjectProperty(None)
    total_col_headings = NumericProperty(0)

    def __init__(self, **kwargs):
        super(RV, self).__init__(**kwargs)
        self.create_table()
        self.get_table_column_headings()
        self.get_users()

    def on_press(self, instance):
        columns = self.data_items[instance.index]['range']
        self.user_no_text_input.text = self.data_items[columns[0]]['text']
        self.user_name_text_input.text = self.data_items[columns[1]]['text']

    def get_table_column_headings(self):
        connection = sqlite3.connect("demo.db")

        with connection:
            # With the with keyword, the Python interpreter automatically releases the resources.
            # It also provides error handling
            cursor = connection.cursor()
            cursor.execute("PRAGMA table_info(Users)")
            col_headings = cursor.fetchall()
            self.total_col_headings = len(col_headings)

    def create_table(self):
        connection = sqlite3.connect("demo.db")
        cursor = connection.cursor()
        sql = """CREATE TABLE IF NOT EXISTS Users(
        UserID integer PRIMAY KEY,
        UserName text NOT NULL)"""
        cursor.execute(sql)
        connection.close()

    def get_users(self):
        connection = sqlite3.connect("demo.db")
        cursor = connection.cursor()

        cursor.execute("SELECT * FROM Users ORDER BY UserID ASC")
        rows = cursor.fetchall()

        # create list with db column, db primary key, and db column range
        data = []
        low = 0
        high = self.total_col_headings - 1

        for row in rows:
            for col in row:
                data.append([col, row[0], [low, high]])
            low += self.total_col_headings
            high += self.total_col_headings

        # create data_items
        self.data_items = [{'text': str(x[0]), 'Index': str(x[1]), 'range': x[2]} for x in data]

    def save(self):
        connection = sqlite3.connect("demo.db")
        cursor = connection.cursor()

        UserID = self.user_no_text_input.text
        UserName = self.user_name_text_input.text

        try:
            save_sql = "INSERT INTO Users (UserID, UserName) VALUES (?,?)"
            cursor.execute(save_sql, (UserID, UserName))
            connection.commit()
            connection.close()
        except sqlite3.IntegrityError as e:
            print("Error: ", e)


class TestApp(App):
    title = "Kivy RecycleView & SQLite3 Demo"

    def build(self):
        return RV()


if __name__ == "__main__":
    TestApp().run()

test.kv

#:kivy 1.11.0

<SelectableButton>:
    # Draw a background to indicate selection
    canvas.before:
        Color:
            rgba: (.0, 0.9, .1, .3) if self.selected else (0, 0, 0, 1)
        Rectangle:
            pos: self.pos
            size: self.size
    on_press:
        app.root.on_press(self)

<RV>:
    user_no_text_input: no
    user_name_text_input: name

    BoxLayout:
        orientation: "vertical"

        Label:
            text: "USER NUMBER"
            size_hint: (.5, None)
            height: 30
        TextInput:
            id: no
            size_hint: (.5, None)
            height: 30
            multiline: False
        Label:
            text: "USER NAME"
            size_hint: (.5, None)
            height: 30
        TextInput:
            id: name
            size_hint: (.5, None)
            height: 30
            multiline: False
        Button:
            id: save_btn
            text: "SAVE BUTTON"
            height: 50
            width: 100
            on_press: root.save()

        GridLayout:
            size_hint: 1, None
            size_hint_y: None
            height: 25
            cols: 2

            Label:
                text: "User ID"
            Label:
                text: "User Name"

        BoxLayout:
            RecycleView:
                viewclass: 'SelectableButton'
                data: root.data_items
                SelectableRecycleGridLayout:
                    cols: 2
                    default_size: None, dp(26)
                    default_size_hint: 1, None
                    size_hint_y: None
                    height: self.minimum_height
                    orientation: 'vertical'
                    multiselect: True
                    touch_multiselect: True