How to fetch data into textbox from python MySQL database?

Question

I have stored data from textboxes to python mysql database. And trying to get stored data from python mysql database into textboxes. I wrote following code to do so.

cur = con.cursor()

data = cur.execute("SELECT First FROM Mydb")

self.txt1.text = data

Where Mydb is the table name and First is a column into that table. After running my application I got following error(Traceback) :

Traceback (most recent call last):

File "main.py", line 221, in

 MySlam1App().run()

File "/usr/lib/python2.7/dist-packages/kivy/app.py", line 792, in run

 runTouchApp()

File "/usr/lib/python2.7/dist-packages/kivy/base.py", line 481, in runTouchApp

 EventLoop.window.mainloop()

File "/usr/lib/python2.7/dist-packages/kivy/core/window/window_pygame.py", line 381, in mainloop

 self._mainloop()

File "/usr/lib/python2.7/dist-packages/kivy/core/window/window_pygame.py", line 287, in _mainloop

 EventLoop.idle()

File "/usr/lib/python2.7/dist-packages/kivy/base.py", line 324, in idle

 self.dispatch_input()

File "/usr/lib/python2.7/dist-packages/kivy/base.py", line 309, in dispatch_input

 post_dispatch_input(*pop(0))

File "/usr/lib/python2.7/dist-packages/kivy/base.py", line 275, in post_dispatch_input

 wid.dispatch('on_touch_up', me)

File "_event.pyx", line 316, in kivy._event.EventDispatcher.dispatch (kivy/_event.c:4543)

File "/usr/lib/python2.7/dist-packages/kivy/uix/behaviors.py", line 110, in on_touch_up

 self.dispatch('on_release')

File "_event.pyx", line 312, in kivy._event.EventDispatcher.dispatch (kivy/_event.c:4497)

File "/usr/lib/python2.7/dist-packages/kivy/lang.py", line 1262, in custom_callback exec(kvlang.co_value, idmap)

File "./myslam1.kv", line 311, in

 on_release: root.show1()

File "main.py", line 121, in show1

 self.txt1.text = data

File "properties.pyx", line 322, in kivy.properties.Property.set (kivy/properties.c:3582)

File "properties.pyx", line 1196, in kivy.properties.AliasProperty.set (kivy/properties.c:19322)

File "/usr/lib/python2.7/dist-packages/kivy/uix/textinput.py", line 2365, in _set_text self._refresh_text(text)

File "/usr/lib/python2.7/dist-packages/kivy/uix/textinput.py", line 1420, in _refresh_text _lines, self._lines_flags = self._split_smart(text)

File "/usr/lib/python2.7/dist-packages/kivy/uix/textinput.py", line 1786, in _split_smart

 lines = text.split(u'\n')

AttributeError: 'tuple' object has no attribute 'split'

What is the proper way to get data from database directly to display into textboxes or any other controls.

Answer 1

cur.execute returns the number of rows affected, not the data itself. You need to use fetchone():

cur.execute("SELECT First FROM Mydb")
data = cur.fetchone()

Answer 2

I have solved my error which was fetching one value(field in record) and display it in textbox or whatever control you want. Here is the code :

with con:

cur = con.cursor()
cur.execute("SELECT * FROM DB")
for tup in cur:
    self.data1.text = tup[0]

Where data1 is the assigned name of the textbox in python file to get access to that textbox from kv file. And tup[0] is the first value of first row in table DB. The above code is only for one row in table.

Answer 3

I see it is an old Post but there will be for sure someone who is looking for such a function. so, this would be a full function!

def show_item(self):
    db = sqlite3.connect("mydb.db")
    cur = db.cursor()

    nbr = int(self.txt_id.text())
               
    command = ''' SELECT column1, column2, column3 FROM my_tbl WHERE id <=?'''

    cur.execute(command, [nbr])
    
    data = cur.fetchone()
    
    for data in cur:
        self.txt_column1.setText(str(data[0]))
        self.txt_column2.setText(str(data[1]))
        self.column3.setText(str(data[2]))