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.<\/p>\n
To create a table we can do something like this in sql:<\/p>\n
create table names (\r\n\tid integer,\r\n\tname text,\r\n\tcity text\r\n\t);<\/pre>\nSo, 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’.<\/p>\n
What we need to do, in the essence, is to execute the code with this code in a function called mk_tb:<\/p>\n
conn = lite.connect(\"database.db\")\r\ncur = conn.cursor()\r\ncur.execute(\"create table name(id integer, name text, city text);\")<\/pre>\nStrangely, the code below to make this simple stuff is very long.<\/p>\n
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.<\/p>\n
The code should be something like this:<\/p>\n
cur.execute(\"\"\"create table {} (\r\n\t{}\r\n);\"\"\".format(name, fields))<\/pre>\nWe 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.<\/p>\n
Let’s take a look at the code until now<\/h2>\n
All will be explained in the video at the end, but let’s focus on the changes in the code.<\/p>\n
In the last post the code was this:<\/p>\n
import sqlite3 as lite\r\nfrom sqlite3 import Error\r\nimport tkinter as tk\r\nfrom glob import glob\r\n\r\nclass App:\r\n # window\r\n def __init__(self, root):\r\n self.root = root\r\n self.root.geometry(\"200x200\")\r\n self.root['bg'] = \"cyan\"\r\n tk.Button(self.root, text = \"Create a database\",\r\n command= lambda: self.new_window(Win1)).pack()\r\n tk.Button(self.root, text = \"Create a table\",\r\n command= lambda: self.new_window(Win2)).pack()\r\n \r\n def new_window(self, _class):\r\n self.new = tk.Toplevel(self.root)\r\n _class(self.new)\r\n\r\n\r\nclass Win:\r\n fields = [] # To create the records field in the databases table\r\n def listbox(self):\r\n self.lab_lb = tk.Label(self.root, text=\"Database in the folder\")\r\n self.lab_lb.pack()\r\n self.lb = tk.Listbox(self.root) # create a listbox\r\n self.lb.pack() # make it visible\r\n for file in glob(\"*.db\"): # insert all the db file in the listbox\r\n self.lb.insert(tk.END, file)\r\n\r\nclass Win1(Win):\r\n def __init__(self, root):\r\n self.root = root\r\n self.root.geometry(\"400x300\")\r\n self.root.title(\"Create Database\")\r\n self.widgets_db() # widgets to create the new db\r\n self.listbox()\r\n\r\n def widgets_db(self):\r\n \"\"\"Widgets to create a new database\r\n\r\n - a label \"Create a db\" ........ self.l\r\n - an entry ..................... self.e \r\n - a button ..................... self.b\r\n \r\n \"\"\"\r\n self.l = tk.Label(self.root, text=\"Create a db [insert the name]\")\r\n self.l.pack()\r\n self.db = tk.StringVar()\r\n self.e = tk.Entry(self.root, textvariable = self.db)\r\n self.e.pack()\r\n self.b = tk.Button(self.root, text=\"Create DB\", command= lambda: self.mk_db())\r\n self.b.pack()\r\n\r\n def mk_db(self):\r\n db = self.e.get()\r\n if db.endswith(\".db\"):\r\n pass\r\n else:\r\n db = db + \".db\"\r\n try:\r\n conn = lite.connect(db)\r\n if db in self.lb.get(0, tk.END):\r\n pass\r\n else:\r\n self.lb.insert(tk.END, db)\r\n return conn\r\n except Error as e:\r\n print(e)\r\n finally:\r\n self.db.set(\"\")\r\n conn.close()\r\n\r\nclass Win2(Win):\r\n def __init__(self, root):\r\n self.root = root\r\n self.root.geometry(\"400x500\")\r\n self.root.title(\"Create Tables\")\r\n self.listbox()\r\n\r\nroot = tk.Tk()\r\napp = App(root)\r\nroot.mainloop()<\/pre>\nThe new code<\/h2>\n
In this ‘episode’ we’ve added the widgets for the input of the name of the table<\/p>\n
def widgets_tb(self):\r\n \"\"\"Widgets to create tables\"\"\"\r\n self.frame1 = tk.Frame(self.root)\r\n self.frame1.pack()\r\n self.lbdbn = tk.Label(self.frame1, text = \"Choose a Database\")\r\n self.lbdbn.pack(side=\"left\")\r\n # entry + StringVar\r\n self.string_dbn = tk.StringVar() # THIS get() the text in the entry\r\n # to get the value in the entry => a = self.string_tbn.get()\r\n # or a = self.entry_tbn.get()\r\n # if we want to clear the text into the entry => self.string_tbn(\"\")\r\n self.entry_dbn = tk.Entry(self.frame1, textvariable = self.string_dbn)\r\n self.entry_dbn.pack(side=\"left\")\r\n \r\n # ========== insert table data ===================\r\n # name of table\r\n self.frame2 = tk.Frame(self.root)\r\n self.frame2.pack()\r\n self.lbtbn = tk.Label(self.frame2, text = \"Insert Table name\")\r\n self.lbtbn.pack(side=\"left\")\r\n self.string_tbn = tk.StringVar()\r\n self.entry_tbn = tk.Entry(self.frame2, textvariable = self.string_tbn)\r\n self.entry_tbn.pack(side=\"left\")<\/pre>\nWe needed also to input the fields text<\/p>\n
def widgets_fl(self):\r\n \"\"\"The widgets to insert fields\"\"\"\r\n self.frame3 = tk.Frame(self.root)\r\n self.frame3.pack()\r\n self.lfl = tk.Label(self.frame3, text = \"Insert the table fields\")\r\n self.lfl.pack()\r\n self.lfl['bg'] = 'gold'\r\n self.text = tk.Text(self.frame3, height=9)\r\n self.text.pack()\r\n self.btb = tk.Button(self.root, text=\"Create The table\", command= lambda: self.mk_tb())\r\n self.btb.pack()<\/pre>\nThe video explaining everything<\/h2>\n