IslandT: Create a daily earning database with Python SQLite

• Download DB Browser to view the earning table
• Create earning table
• Insert very first data

In this chapter, we will start a project which will then record my daily earning in the future. We will create the earing table and populate the first row of data into that table. I can then view my earning table using DB Browser which is a browser uses to create, edit, plot and view the SQLite table’s items.

First of all, let us go to the homepage of DB Browser to download DB Browser through this link. I will temporarily use this tool to view my SQLite table but my final objective is to create my own SQLIte table viewer using the tkinter module. I will go phase by phase to accomplish my objective.

import sqlite3

conn = sqlite3.connect('daily_earning.db')
print ("Opened earning database successfully")

conn.execute('''CREATE TABLE DAILY_EARNING_CHART
         (ID INTEGER PRIMARY KEY AUTOINCREMENT,
         DESCRIPTION    TEXT (50)   NOT NULL,
         EARNING    DOUBLE  NOT NULL,
         TIME   TEXT NOT NULL);''')
print ("Table created successfully")

conn.close()

The next step is to create the SQLite table with a description of the item and the earning I have made every day!

The daily_earning.db file will be the database that we will keep in our computer, the table has been created in that database.

Finally, we will insert some data into the table above.

import sqlite3

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 
                          ('Selling Shoe',33.90, 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")

As you can see, we have inserted the earning description and earning into the above table. We will use tkinter to create a user interface that will accept the data from the user’s keyboard in the future instead of hardcoded the description and the earning into the insert query!

Now let us look at what we have at the moment, I start the DB Browser program, then open the database file, File->Open Database, open the daily_earning.db file, select the table under the Database Structure tab and view the table under the Browse Data tab. Don’t forget to select the table you want under the drop-down selection box.

Select the database’s table

Browse the data from a particular table!

DB Browser is a great program and if you have not installed the program just go ahead and do so, that is all for today.

I will start a few python projects at the same time, this is one of them, another one is the video editing project, if you have not subscribed to this python related topic yet, go ahead and subscribe this topic through the rss feed on the sidebar of this website.

from Planet Python
via read more