CODEWITHSUNDEEP
pythontkintersqlite
coder188642
coder188642

Reputation: 145

How to output results from a sqlite3 query on seperate rows in a tkinter GUI

I am currently coding my Computer Science coursework, which is a diary and week planner that takes input from the user into a sqlite3 database, then when the name is searched, it outputs every record that matches with the query to the GUI (Tkinter). Currently, it prints all the rows on one line, however I want them to output one row, then transfer to the next line. 

def searched():
    Main_Screen.delete('1.0', END)
    searched_for = search.get()
    timeslot = cursor.execute('''SELECT * FROM dates WHERE Name = ?''',(searched_for,))
    list1 = list(cursor.fetchall())
    list1 = str(list1)
    cursor.execute('''SELECT COUNT(*) FROM dates WHERE Name = ?''',(searched_for,))
    result = cursor.fetchone()
    conn.commit()
    print(result)
    str.replace(')','\n', END )

    Main_Screen.insert(tkinter.END, list1)

I tested using the keyword 'test', and got this through my GUI:

[('2017-06-06', 'test', '11:00:00', '12:00:00'), ('2017-06-    06', 'test', '10:00:00', '12:00:00'), ('2017-05-04', 'test', '11:00:00', '16:00:00'), ('2017-04-03', 'test', '11:00:00', '14:00:00')]

Where am I going wrong?

Upvotes: 0

Views: 2560

Answers (1)

asongtoruin
asongtoruin

Reputation: 10359

When you do list1 = list(cursor.fetchall()) you return a list of values from your database, as you see in your output:

[('2017-06-06', 'test', '11:00:00', '12:00:00'), ('2017-06-    06', 'test', '10:00:00', '12:00:00'), ('2017-05-04', 'test', '11:00:00', '16:00:00'), ('2017-04-03', 'test', '11:00:00', '14:00:00')]

You don't want to wrap this as a string (list1 = str(list1)), because that results in the output you're seeing - you want to combine the results in a neat way for displaying.

The str.join() method combines elements of an iterable by putting the string you specify between them. So, if you were to do, say, '-'.join(['Dan Simons', 'asongtoruin']), your output would be Dan Simons-asongtoruin.

As your fetchall call returns a list of tuples, we need to us this method twice:

  1. To put spaces between the columns for each row
  2. To put each row on a new line

Step 1, then, would be something like ' '.join(row) for each of your rows. For example,

' '.join(('2017-06-06', 'test', '11:00:00', '12:00:00'))

would give us 2017-06-06 test 11:00:00 12:00:00 as a string.

If we use a list comprehension, we can do all of these in one go, like so:

joined = [' '.join(row) for row in list1]`

which would give us:

['2017-06-06 test 11:00:00 12:00:00', '2017-06-    06 test 10:00:00 12:00:00', '2017-05-04 test 11:00:00 16:00:00', '2017-04-03 test 11:00:00 14:00:00']

We can then put each one onto a new line by using '\n'.join(joined), and we can even do both steps in one line. Your code could then become:

def searched():
    Main_Screen.delete('1.0', END)
    searched_for = search.get()
    timeslot = cursor.execute('''SELECT * FROM dates WHERE Name = ?''',(searched_for,))
    list1 = cursor.fetchall()
    cursor.execute('''SELECT COUNT(*) FROM dates WHERE Name = ?''',(searched_for,))
    result = cursor.fetchone()
    # conn.commit() - probably not needed
    print(result)
    output_text = '\n'.join([' '.join(row) for row in list1])

    Main_Screen.insert(tkinter.END, output_text)

This should insert the block of text returned form your database into the widget (though without seeing the wider code I can't confirm it)

Upvotes: 2

Related Questions