IslandT: Retrieve data from the sqlite3 database and display it on the message box with Python

In this article let us continue to develop our previous python Tkinter application by creating a search box and a button to allow the user to search for a particular symbol on the database and display it with the Tkinter message box! If the symbol which the user has entered is under the red zone then a warning message box will show up or else it will be a normal information message box.

First let us modify the previous main.py file to include the search box and the search button as well as add in the search method which calls the search method of the data object with two parameters, the symbol and the message box which will be used by the data.py file to display the search result!

import tkinter as tk
from tkinter import ttk
from data import Data
from tkinter import messagebox

def subMit():
    submit.submit(entry.get(), color.get(), entry1.get(), entry2.get())

def search():
    submit.search(entry3.get(), messagebox)

win = tk.Tk()
win.title('STData')
win.resizable(0,0)

txt_label = ttk.Label(win, text="Enter data...")
txt_label.grid(column=0, row=0)

# symbol text box
entry = tk.StringVar(win, value='symbol')
entry_txt = ttk.Entry(win, width=12, textvariable=entry)
entry_txt.grid(column=0, row=1)
entry_txt.focus()

# type text box
entry1 = tk.StringVar(win, value='type')
entry_txt1 = ttk.Entry(win, width=12, textvariable=entry1)
entry_txt1.grid(column=1, row=1)

# price text box
entry2 = tk.StringVar(win, value='unit price')
entry_txt2 = ttk.Entry(win, width=12, textvariable=entry2)
entry_txt2.grid(column=2, row=1)

# select a color in combo box
color = tk.StringVar()
color_combo = ttk.Combobox(win, width=12, textvariable=color, state='readonly')
color_combo['values'] = ('Red', 'Green')
color_combo.grid(column=3, row=1)
color_combo.current(0)

search_label = ttk.Label(win, text="Enter symbol...")
search_label.grid(column=0, row=2)

# search text box
entry3 = tk.StringVar(win, value='symbol')
entry_txt3 = ttk.Entry(win, width=12, textvariable=entry3)
entry_txt3.grid(column=1, row=2)

ttk.Button(win, text="Submit", command=subMit).grid(column=4, row=1)
ttk.Button(win, text="Search", command=search).grid(column=2, row=2)

submit = Data() #initialize/create database

win.mainloop()

Next, let us modify the previous data.py file to include the search method…

import sqlite3

class Data:

    def __init__(self):

        conn = sqlite3.connect('stdata.db')

        try:
            conn.execute('''CREATE TABLE IF NOT EXISTS Stdata
                         (id INTEGER PRIMARY KEY AUTOINCREMENT,
                         symbol    TEXT (50)   NOT NULL,
                         color    TEXT  NOT NULL,
                         type TEXT NOT NULL,
                         price TEXT NOT NULL,
                         time   TEXT NOT NULL);''')
        except:
            print("error creating table!")

        conn.close()

    def search(self, symbol, message):

        try:

            sqliteConnection = sqlite3.connect('stdata.db')
            cursor = sqliteConnection.cursor()
            print("Successfully Connected to SQLite")

            cursor.execute("SELECT * FROM stdata WHERE symbol=?", (symbol,))
            rows = cursor.fetchall()

            data = ''
            buysell = ''

            for row in rows:
                data+= 'Symbol : ' + row[1] + ' Industry : ' + row[3] + ' Unit Price : ' + row[4] + ' Date : ' + row[5]
                buysell = row[2]

            if buysell == 'Green':
                message.showinfo(title="Data", message=data)
            else:
                message.showwarning(title="Data", message=data)



        except sqlite3.Error as error:
            print("Failed to SHOW data", error)
        finally:
            if (sqliteConnection):
                sqliteConnection.close()

    def submit(self,symbol, color, type, price): # Insert values into stdata table

        self.symbol = symbol
        self.color = color
        self.type = type
        self.price = price

        try:
            sqliteConnection = sqlite3.connect('stdata.db')
            cursor = sqliteConnection.cursor()
            print("Successfully Connected to SQLite")
            sqlite_insert_query = "INSERT INTO stdata (symbol,color,type, price, time) VALUES ('" +\
                                  self.symbol + "','" + self.color + "','" + self.type + "','" + self.price + \
                                  "',datetime('now', 'localtime'))"
            cursor.execute(sqlite_insert_query)
            sqliteConnection.commit()
            print("Record inserted successfully into stdata table", cursor.rowcount)
            cursor.close()

        except sqlite3.Error as error:
            print("Failed to insert earning data into sqlite table", error)
        finally:
            if (sqliteConnection):
                sqliteConnection.close()

Next, run the main.py file and see the result…

Enter the item symbol and select RED in the combo box

As you can see there is a warning sign appears beside the message

When the data which has been entered is in the green zone then a normal message box will appear!

With that, this project’s article has now been completed but I will continue to develop this application behind the scene for my own usage!

from Planet Python
via read more