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.
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("")
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.
def mk_db(self):
db = self.e.get()
if db.endswith(".db"):
pass
else:
db = db + ".db"
try:
conn = lite.connect(db)
self.lb.insert(tk.END, db)
self.e_string_var.set("")
return conn
except Error as e:
print(e)
finally:
conn.close()
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:
self.e_string_var = tk.StringVar() self.e = tk.Entry(self.root, textvariable=self.e_string_var) self.e.pack()
and also the button need some changes:
self.b = tk.Button(self.root, text="Create DB", command= lambda: self.mk_db()) self.b.pack()
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.
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
# entry containing the db name
self.l = tk.Label(self.root, text="Create a db [insert the name]")
self.l.pack()
self.e_string_var = tk.StringVar()
self.e = tk.Entry(self.root, textvariable=self.e_string_var)
self.e.pack()
self.b = tk.Button(self.root, text="Create DB", command= lambda: self.mk_db())
self.b.pack()
self.lb = tk.Listbox(self.root)
self.lb.pack()
self.show_db()
def mk_db(self):
db = self.e.get()
if db.endswith(".db"):
pass
else:
db = db + ".db"
try:
conn = lite.connect(db)
self.lb.insert(tk.END, db)
self.e_string_var.set("")
return conn
except Error as e:
print(e)
finally:
conn.close()
def show_db(self):
for file in glob("*.db"):
self.lb.insert(tk.END, file)
root = tk.Tk()
app = App(root)
root.mainloop()
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
def mk_fl(self):
self.fields.append(self.efl.get())
self.vfl.set("")
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
def mk_tb(self, dbn, tbn):
self.conn = lite.connect(dbn.get())
self.cur = self.conn.cursor()
self.fields = "".join(self.fields)
self.cur.execute("""create table {} (
{});""".format(tbn, self.fields))
self.fields = []
self.conn.close()
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
# 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()
The label and Entry for the table name
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()
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.
# FIELDS - vfl is the StringVar, efl is the Entry self.lflname = tk.Label(self.root, text="Insert Fields name and type\n followeb by a comma, one by one,\nclicking once for each field.") 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()
When you have finished inserting the fields, you click “Create the table”.
The button to create the table
self.btb = tk.Button(self.root, text="Create Table", command= lambda: self.mk_tb(self.dbn, self.tbn)) self.btb.pack()
The whole code until creation of the tables
import sqlite3 as lite
from sqlite3 import Error
import tkinter as tk
from glob import glob
class App:
# window
def __init__(self, root):
self.fields = []
self.root = root
# entry containing the db name
self.l = tk.Label(self.root, text="Create a db [insert the name]")
self.l.pack()
self.db = tk.StringVar()
self.e = tk.Entry(self.root, textvariable=self.db)
self.e.pack()
self.b = tk.Button(self.root, text="Create DB", command= lambda: self.mk_db())
self.b.pack()
self.lb = tk.Listbox(self.root)
self.lb.pack()
self.show_db()
# 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()
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()
# FIELDS - vfl is the StringVar, efl is the Entry
self.lflname = tk.Label(self.root, text="Insert Fields name and type\n followeb by a comma, one by one,\nclicking once for each field.")
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()
self.btb = tk.Button(self.root, text="Create Table", command= lambda: self.mk_tb(self.dbn, self.tbn))
self.btb.pack()
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()
self.fields = "".join(self.fields)
self.cur.execute("""create table {} (
{});""".format(tbn, self.fields))
self.fields = []
self.conn.close()
def mk_fl(self):
self.fields.append(self.efl.get())
self.vfl.set("")
root = tk.Tk()
win = App(root)
root.mainloop()
The window
Some restyiling to the code
Nothing is changed in this code from above, apart some methods to add readability.
import sqlite3 as lite
from sqlite3 import Error
import tkinter as tk
from glob import glob
class App:
# window
def __init__(self, root):
self.fields = []
self.root = root
self.label()
self.entry()
self.button()
self.listbox()
self.db_name_widgets()
self.tb_name_widgets()
self.fields_widgets()
self.btn_create_table()
def label(self):
self.l = tk.Label(self.root, text="Create a db [insert the name]")
self.l.pack()
def entry(self):
self.db = tk.StringVar()
self.e = tk.Entry(self.root, textvariable=self.db)
self.e.pack()
def button(self):
self.b = tk.Button(self.root, text="Create DB", command= lambda: self.mk_db())
self.b.pack()
def listbox(self):
self.lb = tk.Listbox(self.root)
self.lb.pack()
self.show_db()
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 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 name and type\n followeb by a comma, one by one,\nclicking once for each field.")
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()
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()
self.fields = "".join(self.fields)
self.cur.execute("""create table {} (
{});""".format(tbn, self.fields))
self.fields = []
self.conn.close()
def mk_fl(self):
self.fields.append(self.efl.get())
self.vfl.set("")
root = tk.Tk()
win = App(root)
root.mainloop()
