Python, Sqlite and Tkinter: how to put data into a listbox

New post about sqlite with python

Making database, working visually

Hi, today we are going to make a tutorial that will lead you to make a very simple sqlite database controlled by Python with a GUI, graphic user interface, realized with the built-in module tkinter.

The code

In this first part, we will create a GUI to create databases. We will also have a list of the databases created in the window that we are going to create. You will learn to:

  • create a database with sqlite
  • create a window with tkinter
  • populate the window with widgets:
    • labels
    • entry
    • buttons
    • listbox
  • add a command to a button to make the computer act on the database
  • insert values (populate) a listbox with data from the folder
  • get values from an entry and insert them in the listbox
  • create a class
  • access to a method or attribute of the class
  • and other little skills

Create a database

To create a database with sqlite3, after you imported it

import sqlite3 as lite

you do it this way using connet with the name of the database you want to create if it does not exist yet:

conn = lite.connect("mydatabase.db")

Now the database is created, go to look in the folder where you are coding and you’ll see the file. Good job!

It is a good behaviour to close the database when you do not use it, like this:

conn.close()

You could also do use with:

filename = "db1.db"
with lite.connect(filename) as conn:
    print(f"I created my database named {filename}")

output:

I created my database named db1.db

This way the db will close automatically and in a cleaner way, without having troubles if the files should be called by another process before it is closed.

We could create a simple function that opens the bd, does something with it and close it:

import sqlite3 as lite


def db_open(filename):
    "Opens and close the database"
    with lite.connect(filename) as conn:
        print(f"I created my database named {filename}")


db_open("db1.db")

This is very neat and it make you use your database and close it just with this simple call of the db_open function passing the name of the database you want to create or open.

Let’s make a GUI to create the database

The code below shows how to create a db. It will be called when you will press a button.

  • you click the button (the code to make the button will be shown after the one below)
  • the click of the button calls the create function passing the window obj as an argument
  • the function will get the text (the database name) that you’ve written into the ‘e’ object (a text input widget – Entry)
  • it will check if there is the .db extension to this database filename and will add to it if not
  • it will create the database with that name
  • it will add the name of the database to a listbox

In the GUI we will build, in fact, there will be an entry where the user will write the name of the database and when he presses the button it sends all the window object (window) to this function below so that we can take the name of the database with window.e.get(), where e is the name of the entry. I will give more explanations after the following code:

import sqlite3 as lite
from sqlite3 import Error
import tkinter as tk
from glob import glob

def create(window):
	db = window.e.get()

	if db[-3] == ".db":
		pass
	else:
		db = db + ".db"
	try:
		conn = lite.connect(db)
		return conn
	except Error as e:
		print(e)
	finally:
		conn.close()
		window.lb.insert(tk.END, db)
		window.db.set("")

The above funtion gets from the user (in db) the text with the name of the database from an entry (called e) that will be in the GUI (in the following code below). In practice window is the istance of the GUI class, e is the entry in that GUI and get() is the method to get the text in e. Now that we have it in db, we can check if the user has used the .db extension in the name (in window.e). If not, the code adds the “.db” extension at the end.

After this initial checks, the database is created (conn = lite.connect(db)). lite is the module sqlite3 (we gave it that name in the import line, take a look at the 1st line).

P.S. :With the try except block we test a block of code for errors. If all goes right it creates the db and finally (at the end) it closes it, adds the name of the db in lb (the listbox that we will add) and wil delete the name the user has input in the entry (window.db.set).

Now let’s see the code for the GUI window tha will make everything clearer.

class Window:
	"""Creates the widgets of the window"""
	def __init__(self):
		self.win = tk.Tk()
		self.label()
		self.entry()
		self.button()
		self.listbox()

	def label(self):
		self.l = tk.Label(self.win, text="Create a db [insert the name]")
		self.l.pack()

	def entry(self):
		self.db = tk.StringVar()
		self.e = tk.Entry(self.win, textvariable=self.db)
		self.e.pack()

	def button(self):
		self.b = tk.Button(self.win, text="Create DB", command= lambda: create(self))
		self.b.pack()

	def listbox(self):
		self.lb = tk.Listbox(self.win)
		self.lb.pack()
		self.show_db()

	def show_db(self):
		for file in glob("*.db"):
			self.lb.insert(tk.END, file)

		
		self.win.mainloop()

win = Window()

In this class, that I rewrote differently from the one you see in the video, creates a label, an entry, a button, a listbox. I created a method for each widget to make more readable the code.

When the user hits the button you see the command is a lambda function (an anonymous function) that runs the code in the create(db) funtion that we’ve seen previously.

The whole code

Now we can take a look at the whole code.

import sqlite3 as lite
from sqlite3 import Error
import tkinter as tk
from glob import glob

def create(obj):
	db = obj.e.get()

	if db[-3] == ".db":
		pass
	else:
		db = db + ".db"
	try:
		conn = lite.connect(db)
		return conn
	except Error as e:
		print(e)
	finally:
		conn.close()
		obj.lb.insert(tk.END, db)
		obj.db.set("")

class Window:
	"""Creates the widgets of the window"""
	def __init__(self):
		self.win = tk.Tk()
		self.label()
		self.entry()
		self.button()
		self.listbox()

	def label(self):
		self.l = tk.Label(self.win, text="Create a db [insert the name]")
		self.l.pack()

	def entry(self):
		self.db = tk.StringVar()
		self.e = tk.Entry(self.win, textvariable=self.db)
		self.e.pack()

	def button(self):
		self.b = tk.Button(self.win, text="Create DB", command= lambda: create(self))
		self.b.pack()

	def listbox(self):
		self.lb = tk.Listbox(self.win)
		self.lb.pack()
		self.show_db()

	def show_db(self):
		for file in glob("*.db"):
			self.lb.insert(tk.END, file)

		
		self.win.mainloop()

win = Window()

Live coding to make a db with sqlite and Python with tkinter

This video explains the code above to make a database with sqlite3. It lasts 20 minutes.

In the next post we will see how to create a table with the tkinter GUI.

New post and video about sqlite tutorial

For you who are interested in Sqlite3 and Python, I got some brand new stuff for your here.

Published by pythonprogramming

Started with basic on the spectrum, loved javascript in the 90ies and python in the 2000, now I am back with python, still making some javascript stuff when needed.