Let’s continue out example of possible use of sqlite3 (database module) for Python. We created this GUI for our database with tkinter to show the basic use of both module, sqlite3 and tkinter. We added some widgets and some basic functions to create a database and a table. Now we want to make the GUI more clear and add some features like adding values. To see the table in the database you can use this free software DB Browser for Sqlite.
Video about the app until now
Click on the arrow in the middle to start the video with the way the app works explained.
Let’s re-organize our code
We want to get back and remake our code so that we can have different windows for different funtions, so that it is easier to manage the program.
This is the code for the main window, the menu window
import sqlite3 as lite from sqlite3 import Error import tkinter as tk from glob import glob class App: def __init__(self, root): self.root = root self.root.geometry("300x100") self.root['bg'] = "cyan" self.butnew("Create Database", Win1) self.butnew("Create Table", Win2) def butnew(self, text, _class): tk.Button(self.root, text = text, command=lambda: self.new_window(_class)).pack() def new_window(self, _class): self.new = tk.TopLevel(self.root) _class(self.new) root = tk.Tk() win = App(root) root.mainloop()
This is the output until now
The App class code video
This is the video of the live coding of the menu window (see the code above) that will call the other 2 windows:
To be continued… ?
This is the final output…
The whole code
import sqlite3 as lite from sqlite3 import Error import tkinter as tk from glob import glob class App: # window def __init__(self, root): self.root = root self.root.geometry("200x200") self.root['bg'] = "cyan" self.butnew("Create Database", Win1) self.butnew("Create Table", Win2) def butnew(self, text, _class): """Create a new button with text and class as argument""" tk.Button( self.root, text=text, command=lambda: self.new_window(_class)).pack() def new_window(self, _class): self.new = tk.Toplevel(self.root) _class(self.new) class Win: fields = [] def show_db(self): for file in glob("*.db"): self.lb.insert(tk.END, file) def mk_db(self): db = self.e.get() if db.endswith(".db"): pass else: db = db + ".db" try: conn = lite.connect(db) if db in self.lb.get(0, tk.END): pass else: self.lb.insert(tk.END, db) return conn except Error as e: print(e) finally: self.db.set("") conn.close() def mk_tb(self, dbn, tbn): self.conn = lite.connect(dbn.get()) self.cur = self.conn.cursor() Win.fields = "".join(Win.fields) self.cur.execute("""create table {} ( {} );""".format(tbn, Win.fields)) Win.fields = [] self.conn.close() def mk_fl(self): Win.fields.append(self.efl.get()) self.vfl.set("") def show_selection(self): x = self.lb.curselection()[0] x = self.lb.get(x) self.dbn.set(x) class Win1(Win): def __init__(self, root): self.root = root self.root.geometry("400x300") self.root['bg'] = "pink" self.label() self.entry() self.button() self.listbox() def label(self): self.label = tk.Label(self.root, text="Create a db [insert the name]") self.label.pack() def entry(self): self.db = tk.StringVar() self.e = tk.Entry(self.root, textvariable=self.db) self.e.pack() def button(self): """Create db""" self.b = tk.Button( self.root, text="Create DB", command=lambda: self.mk_db()) self.b.pack() def db_name_widgets(self): # label and Entry for Database name self.ldbname = tk.Label(self.root, text="Insert Database name") self.ldbname.pack() self.dbn = tk.StringVar() self.edb = tk.Entry(self.root, textvariable=self.dbn) self.edb.pack() def listbox(self): self.lb = tk.Listbox(self.root) self.lb.pack() self.show_db() self.lb.bind("<Double-Button>", lambda x: self.show_selection()) class Win2(Win): def __init__(self, root): Win.__init__(self) self.root = root self.root.geometry("400x500") self.root['bg'] = "green" self.listbox() self.db_name_widgets() self.tb_name_widgets() self.fields_widgets() self.btn_create_table() def db_name_widgets(self): # label and Entry for Database name self.ldbname = tk.Label( self.root, text="Insert Database name") self.ldbname.pack() self.dbn = tk.StringVar() self.edb = tk.Entry( self.root, textvariable=self.dbn) self.edb.pack() def listbox(self): self.lb = tk.Listbox(self.root) self.lb.pack() self.show_db() self.lb.bind("<Double-Button>", lambda x: self.show_selection()) def tb_name_widgets(self): self.ltbname = tk.Label(self.root, text="Insert Table name") self.ltbname.pack() self.tbn = tk.StringVar() self.etb = tk.Entry(self.root, textvariable=self.tbn) self.etb.pack() def fields_widgets(self): self.lflname = tk.Label(self.root, text="""Insert Fields (2 at least) name and type\n followeb by a comma, one by one, \nclicking once for each field\nThe last field must be without comma \nExample:\nid integer,""") self.lflname.pack() self.vfl = tk.StringVar() self.efl = tk.Entry(self.root, textvariable=self.vfl) self.efl.pack() self.bfl = tk.Button( self.root, text="Create Field", command=lambda: self.mk_fl()) self.bfl.pack() def btn_create_table(self): self.btb = tk.Button( self.root, text="Create Table", command=lambda: self.mk_tb(self.dbn, self.tbn)) self.btb.pack() root = tk.Tk() win = App(root) root.mainloop()
The pdf with the code: DBManager
How the windows looks now
In this short video you can see the three windows in with the widgets are divided now, to make the program more easy to manage and visually more intuitive than it was before, before we add other widgets in another window to add our data.
Next time we will make a better look for this application. Now it’s a little messy.