In this chapter, we will create the Input class which has a few methods used to create a database and the earning table as well as an insert method to insert earning values into that earning table. After that, we will create an object of that class inside the main program which can be used to create a database and the earning table and then insert the user inputs into that earning table.

First of all, this will be the final folder structure once we have created the input class and created the database’s table with some values in it.

The folder and files of this project

I am using PyCharm 2019.3.1 Community Edition which is a free python editor with a great user interface to create this project.

We have already created the python program used to generate the database, earning table and to submit the user input to the earning table in the previous chapter. In this chapter, we just need to create a class which will interact with the Tkinter program to perform the above tasks.

import sqlite3
from datetime import datetime

class Input:
    def __init__(self, description, earning):
        self.description = description
        self.earning = earning

    def setting(self):
        conn = sqlite3.connect('daily_earning.db')
        print("Opened database successfully")
        try:
            conn.execute('''CREATE TABLE DAILY_EARNING_CHART
                 (ID INTEGER PRIMARY KEY AUTOINCREMENT,
                 DESCRIPTION    TEXT (50)   NOT NULL,
                 EARNING    TEXT  NOT NULL,
                 TIME   TEXT NOT NULL);''')
        except:
            pass

        print("Table created successfully")

        conn.close()

    def submit(self): # Insert values into earning table
        try:
            sqliteConnection = sqlite3.connect('daily_earning.db')
            cursor = sqliteConnection.cursor()
            print("Successfully Connected to SQLite")

            sqlite_insert_query = "INSERT INTO DAILY_EARNING_CHART (DESCRIPTION,EARNING,TIME) VALUES ('" + self.description + "','"+ self.earning + "',datetime('now', 'localtime'))"

            count = cursor.execute(sqlite_insert_query)
            sqliteConnection.commit()
            print("Record inserted successfully into DAILY_EARNING_CHART 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()
                print("The SQLite connection is closed")

The above class will allow us to create an object in which the main program will be able to use to call various methods that will then create the database, earning table and insert values into that table.

import tkinter as tk
from tkinter import ttk

from Input import Input

win = tk.Tk()

win.title("Earning Input")

def submit():
    if(description.get()!='' and earning.get()!=""):
        sub_mit = Input(description.get(), earning.get())
        sub_mit.setting()
        sub_mit.submit()
    else:
        print("You need to enter a value!")

#create label frame for ui
earn= ttk.Labelframe(win, text = "Daily Earning Input")
earn.grid(column=0, row=0, padx=4, pady=4)
# create label for description
dLabel = ttk.Label(earn, text="Description:").grid(column=0, row=0)
# create text box for description
description = tk.StringVar()
descriptionEntry = ttk.Entry(earn, width=13, textvariable=description)
descriptionEntry.grid(column=1, row=0)

# create label for earning
eLabel = ttk.Label(earn, text="Earning:").grid(column=2, row=0)
# create text box for earning
earning = tk.StringVar()
earningEntry = ttk.Entry(earn, width=13, textvariable=earning)
earningEntry.grid(column=3, row=0)
# create the action button
action = ttk.Button(earn, text="submit", command=submit)
action.grid(column=5, row=0)

win.resizable(0,0)

win.mainloop()

The Input class and the main python program has taken care of the followings:-

  • If the table and database have not been created yet, then create them.
  • If there is no input from the user then just skip the submit process.
  • Always inform the programmer whether the program has successfully created a database or inserted values or not.
Let us start the Tkinter user interface and insert some values
The program has succeeded in creating a database, earning table and inserting the values into the earning table
Open DB Browser and view the outcome

DB Browser is a great tool and we will stick with this tool for a while, we will continue to modify our project in the next chapter before moving onward.

Please follow and like us:

3 Comments

    1. Am I not doing that? Have you read through the entire code on Github or on this blog before writing your comment?

      # Never do this — insecure!
      symbol = ‘RHAT’
      c.execute(“SELECT * FROM stocks WHERE symbol = ‘%s'” % symbol)

      # Do this instead
      t = (‘RHAT’,)
      c.execute(‘SELECT * FROM stocks WHERE symbol=?’, t)

      I am doing exactly what the page asks the programmer to do!

      1. Actually, no, you don’t. You use string concatenation which is dangerous.

        “`
        sqlite_insert_query = “INSERT INTO DAILY_EARNING_CHART (DESCRIPTION,EARNING,TYPE, LOCATION, TIME) VALUES (‘” + self.description + “‘,'”+ self.earning + “‘,'” + self.cc + “‘,'” + self.location + “‘,datetime(‘now’, ‘localtime’))”
        “`

        try to enter in the “earning” field “DROP TABLE DAILY_EARNING_CHART;” and see what happens.

        To make it secure you need to put “?” as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method.

        Like this:
        sqlite_insert_query = “INSERT INTO DAILY_EARNING_CHART (DESCRIPTION,EARNING,TYPE, LOCATION, TIME) VALUES (?, ?, ?, ?, ?)”
        cursor.execute(sqlite_insert_query, (self.description, self.earning, self.cc, self.location, datetime(“now”, “localtime)))

Leave a Reply

Your email address will not be published. Required fields are marked *