Here we are with the final part of the re-organization of the code. Now we can create (again) the tables. Next time we will look at inserting data.
The sql code
To create a table we can do something like this in sql:
create table names ( id integer, name text, city text );
So, what we need it to input a name and the text with the id, name and city. It’s so simple. But… to create widgets to get the input and open the database and execute that code… could take some time… once you get the habit to this code, you will make it in no time, but the start can be a little ‘messy’.
What we need to do, in the essence, is to execute the code with this code in a function called mk_tb:
conn = lite.connect("database.db")
cur = conn.cursor()
cur.execute("create table name(id integer, name text, city text);")
Strangely, the code below to make this simple stuff is very long.
First of all we need to make the name ‘abstract’ using a variable. The same for the fields. We also need to get the specific name and fields by the input to the user. We will use an entry for the name of the table and a text widgets for the fields name. So, it will be all very simple.
The code should be something like this:
cur.execute("""create table {} (
{}
);""".format(name, fields))
We will use this into a method of a class, so the variables will have a self. before their names, as you will see in the next part of this post and in the video.
Let’s take a look at the code until now
All will be explained in the video at the end, but let’s focus on the changes in the code.
In the last post the code was this:
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"
tk.Button(self.root, text = "Create a database",
command= lambda: self.new_window(Win1)).pack()
tk.Button(self.root, text = "Create a table",
command= lambda: self.new_window(Win2)).pack()
def new_window(self, _class):
self.new = tk.Toplevel(self.root)
_class(self.new)
class Win:
fields = [] # To create the records field in the databases table
def listbox(self):
self.lab_lb = tk.Label(self.root, text="Database in the folder")
self.lab_lb.pack()
self.lb = tk.Listbox(self.root) # create a listbox
self.lb.pack() # make it visible
for file in glob("*.db"): # insert all the db file in the listbox
self.lb.insert(tk.END, file)
class Win1(Win):
def __init__(self, root):
self.root = root
self.root.geometry("400x300")
self.root.title("Create Database")
self.widgets_db() # widgets to create the new db
self.listbox()
def widgets_db(self):
"""Widgets to create a new database
- a label "Create a db" ........ self.l
- an entry ..................... self.e
- a button ..................... self.b
"""
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()
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()
class Win2(Win):
def __init__(self, root):
self.root = root
self.root.geometry("400x500")
self.root.title("Create Tables")
self.listbox()
root = tk.Tk()
app = App(root)
root.mainloop()
The new code
In this ‘episode’ we’ve added the widgets for the input of the name of the table
def widgets_tb(self):
"""Widgets to create tables"""
self.frame1 = tk.Frame(self.root)
self.frame1.pack()
self.lbdbn = tk.Label(self.frame1, text = "Choose a Database")
self.lbdbn.pack(side="left")
# entry + StringVar
self.string_dbn = tk.StringVar() # THIS get() the text in the entry
# to get the value in the entry => a = self.string_tbn.get()
# or a = self.entry_tbn.get()
# if we want to clear the text into the entry => self.string_tbn("")
self.entry_dbn = tk.Entry(self.frame1, textvariable = self.string_dbn)
self.entry_dbn.pack(side="left")
# ========== insert table data ===================
# name of table
self.frame2 = tk.Frame(self.root)
self.frame2.pack()
self.lbtbn = tk.Label(self.frame2, text = "Insert Table name")
self.lbtbn.pack(side="left")
self.string_tbn = tk.StringVar()
self.entry_tbn = tk.Entry(self.frame2, textvariable = self.string_tbn)
self.entry_tbn.pack(side="left")
We needed also to input the fields text
def widgets_fl(self):
"""The widgets to insert fields"""
self.frame3 = tk.Frame(self.root)
self.frame3.pack()
self.lfl = tk.Label(self.frame3, text = "Insert the table fields")
self.lfl.pack()
self.lfl['bg'] = 'gold'
self.text = tk.Text(self.frame3, height=9)
self.text.pack()
self.btb = tk.Button(self.root, text="Create The table", command= lambda: self.mk_tb())
self.btb.pack()
The video explaining everything
The entire new code
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("200x200")
self.root['bg'] = "cyan"
tk.Button(
self.root, text="Create a database",
command=lambda: self.new_window(Win1)).pack()
tk.Button(
self.root, text="Create a table",
command=lambda: self.new_window(Win2)).pack()
def new_window(self, _class):
self.new = tk.Toplevel(self.root)
_class(self.new)
class Win:
fields = []
def listbox(self):
self.lab_lb = tk.Label(self.root, text="Database in the folder")
self.lab_lb['bg'] = 'gold'
self.lab_lb.pack()
self.lb = tk.Listbox(self.root)
self.lb.pack()
for file in glob("*.db"):
self.lb.insert(tk.END, file)
self.lb.bind("<Double-Button>", lambda x: self.show_selection())
def show_selection(self):
print("This function here does not works")
class Win1(Win):
def __init__(self, root):
self.root = root
self.root.geometry("400x300")
self.root.title("Create Database")
self.widgets_db()
self.listbox()
def widgets_db(self):
self.label = tk.Label(
self.root, text="Create a db [insert the name]")
self.label.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()
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()
class Win2(Win):
def __init__(self, root):
self.root = root
self.root.geometry("400x500")
self.root.title("Create Tables")
self.listbox()
self.widgets_tb()
self.widgets_fl()
def widgets_tb(self):
"""Widgets to create tables"""
self.frame1 = tk.Frame(self.root)
self.frame1.pack()
self.lbdbn = tk.Label(
self.frame1, text="Choose a Database")
self.lbdbn.pack(side="left")
# entry + StringVar
self.string_dbn = tk.StringVar()
# to get the value in the entry => a = self.string_tbn.get()
# or a = self.entry_tbn.get()
# if we want to clear the text into the entry => self.string_tbn("")
self.entry_dbn = tk.Entry(
self.frame1, textvariable=self.string_dbn)
self.entry_dbn.pack(side="left")
# ========== insert table data ===================
# name of table
self.frame2 = tk.Frame(self.root)
self.frame2.pack()
self.lbtbn = tk.Label(
self.frame2, text="Insert Table name")
self.lbtbn.pack(side="left")
self.string_tbn = tk.StringVar()
self.entry_tbn = tk.Entry(
self.frame2, textvariable=self.string_tbn)
self.entry_tbn.pack(side="left")
def widgets_fl(self):
"""The widgets to insert fields"""
self.frame3 = tk.Frame(self.root)
self.frame3.pack()
self.lfl = tk.Label(
self.frame3, text="Insert the table fields")
self.lfl.pack()
self.lfl['bg'] = 'gold'
self.text = tk.Text(self.frame3, height=9)
self.text.pack()
self.btb = tk.Button(
self.root, text="Create The table", command=lambda: self.mk_tb())
self.btb.pack()
def show_selection(self):
x = self.lb.curselection()[0]
x = self.lb.get(x)
self.string_dbn.set(x)
def mk_tb(self):
self.conn = lite.connect(self.string_dbn.get())
self.cur = self.conn.cursor()
self.cur.execute(
"""create table {}(
{});""".format(
self.string_tbn,
self.text.get("1.0", 'end-1c')))
root = tk.Tk()
app = App(root)
root.mainloop()
