Sqlite3 & Tkinter – Part II

Spread the love

After some days from the first post about sqlite, we are back at the code writing to finish the app we wanted to. Tkinter is one of the topic that I often write about in this blog, as you could be aware of searching into my posts, and I always try to write about it’s features making practical examples, rather than talking in abstract of his widgets and stuffs. This three tools together can do a lot of good things, I think. Let’ go into more details about it through a little app to create databases with sqlite3.

This second post about our little application to use sqlite3 database with python and tkinter. The tkinter module is a built-in one of python that allows us to create graphic user interfaces. We want to create this GUI to:

  • create a sqlite database
  • create a table
  • insert data in the table
  • do other stuffs

In the first post and video(python and sqlite with tkinter) (go here to read and watch if you do not have done it yet) we created a window to generate databases with nothing inside.

This is how the windows appeared. It does what it looks like it does.

Create tables with sqlite and tkinter

Now we will add tables through the interface. Before we start we will include the function create into the class to create the window called Win.

Let’s do this (the most relevant part of this code is conn = lite.connect(db) that opens / creates the database.

The function above will be in included in the Win class (that we will call now App). The function will be a method of App and we will change the name from create to mk_db. Instead of ‘obj’ we will put self as it’s in the class. There are other changes explained in the video.

This are other changes, consequential to the decisions we took previously:

We change the name of ‘db’ (StringVar object) in ‘e_string_var’, for more intuitive stuff:

and also the button need some changes:

So, nothing dramatic until know. We just moved some lines of code into the main class and changed some names.

There is a last thing. We put the creation of the Tk() object outside of the App, for reason that will be declared later. This is the complete code at the end.

Functionality are the same. Nothing changed. Time to create tables.

The mk_tb method to create tables, finally

To create the fields we use this

The fields will go into a list called self.fields. In the mk_tb method to create the table, this fields list will become a string.

Here is the function for the table

Simple:

  • open the database
  • start the cursor()
  • join the list of fields in a string
  • execute the string with the sql command
  • close the database

When this function is called there are two arguments:

  • the name of the database (to open it with lite.connect(dbn)
  • the name of the table (that will go in the string with the commands

The fields will be taken from the attribute self.fields, that was a list, transformed in string with join. We don’t need to pass them.

The widgets

Now we just need the widgets to insert the name of the database, of the table and to define the fields.

The fields have a label (name) and then the type of fields (text or integer, float etc) and must be followed by a comma (we could add some code to avoid forgetting it).

The label and Entry for the db name

The label and Entry for the table name

The label, the entry and the butto for the fields

You create a fields like

name text,

and then click the button “Create fields”…

Repeat for all the fields.

 

When you have finished inserting the fields, you click “Create the table”.

The button to create the table

The whole code until creation of the tables

The window

Some restyiling to the code

Nothing is changed in this code from above, apart some methods to add readability.