时间戳

首页 > 解决方案 > 如何从 sqlite Db 检索数据并设置为 kivy TextInput 字段？

python - 如何从 sqlite Db 检索数据并设置为 kivy TextInput 字段？

问题描述

我通过使用 kivy 文档和其他在线资源制作小型应用程序来学习 kivy。当前代码有两个文本输入字段（UserID、UserName）来存储数据库中的值，使用 RecycleView 存储的数据通过 Kivy GUI 显示在按钮上。

按下按钮时，我需要将数据库中的相应数据设置为相应的文本输入字段。即按下带有 UserID 1 的按钮时，文本输入字段应显示该行中的 UserID 和 UserNames 的值。

主文件 

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

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

标签： pythonsqlitekivytextinput

解决方案

解决方案

解决方法如下：详情请参考代码片段、示例和输出。

.kv 文件

  1. on_press在类规则下添加事件， <SelectableButton>:. 当 on_press 事件被触发时，它会调用root.on_press(self)根类RV中的方法，并传递一个buttonself实例。
  2. 将两个 ObjecProperty 挂钩 ( user_no_text_input: no, user_name_text_input: name) 移动到正确的位置，即从下方BoxLayout:到<RV>:。
  3. 替换data: [{'text': str(x)} for x in root.data_items]为data: root.data_items

片段 - 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代码

在 RV 类中，执行以下操作：

  1. 添加NumericProperty到 kivy.properties 的导入语句。
  2. 为和添加ObjecPropertyuser_no_text_input = ObjectProperty(None)user_name_text_input = ObjectProperty(None)
  3. 添加NumericProperty为total_col_headings = NumericProperty(0)
  4. 为事件创建一个on_press()方法。on_press
  5. 创建一个get_table_column_headings()方法来获取表中的总列标题。这用于创建列范围。
  6. 增强get_users()方法，创建一个包含 db 列值、db 主键值和 db 列范围的列表。使用数据库列范围的技巧是使用单击/按下的行中的值填充TextInput小部件。

片段 - 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]

例子

主文件

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

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

输出

图像01 图像02

推荐阅读