Categories Subcategories In Python (Simple Example)

Welcome to a tutorial on how to deal with categories and subcategories in Python. So you have to work with categories and subcategories in your project? Well, the bad news is that it involves recursion. Let us walk through a simple example – Read on!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

 

 

QUICK NOTES

• Create a project folder, e.g. D:\cat, unzip the code inside this folder.
• Navigate to the project folder in the command line cd D:\cat, create a virtual environment to not mess up your other projects.
  • virtualenv venv
  • Windows – venv\scripts\activate
  • Mac/Linux – venv/bin/activate
• Run python S1B_create.py to create the category database.
• See python S3_draw.py and python S4_actions.py.

If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

 

PART 1) THE DATABASE

1A) CATEGORY TABLE

CREATE TABLE category (
  category_id INTEGER,
  parent_id INTEGER DEFAULT 0,
  category_name TEXT NOT NULL,
  PRIMARY KEY("category_id" AUTOINCREMENT)
);
CREATE INDEX parent_id ON category (parent_id);

First, let us begin with the database. To keep things simple, we will be using the newbie-friendly SQLite. In your own project, you can use MYSQL, Oracle, Postgre, Mongol, and whatever else. But yep, a simple parent_id field is all we need.

• Categories with parent_id=0 are “main categories”.
• Categories with parent_id>0 are “subcategories”.

 

1B) DUMMY CATEGORIES

INSERT INTO `category` (`category_id`, `parent_id`, `category_name`) VALUES
(1, 0, 'Electronics'),
(2, 1, 'Computers'),
(3, 1, 'Cameras'),
(4, 2, 'Desktop'),
(5, 2, 'Laptop');

A quick example of how the parent_id work:

 

 

1C) CREATE THE DATABASE

# (A) LOAD PACKAGES
import sqlite3, os
from sqlite3 import Error

# (B) DATABASE + SQL FILE
DBFILE = "category.db"
SQLFILE = "S1A_category.sql"

# (C) DELETE OLD DATABASE IF EXIST
if os.path.exists(DBFILE):
  os.remove(DBFILE)

# (D) IMPORT SQL
conn = sqlite3.connect(DBFILE)
  with open(SQLFILE) as f:
conn.executescript(f.read())
conn.commit()
conn.close()
print("Database created!")

Just run this to create the actual category.db database file.

 

PART 2) RECURSIVE GET CATEGORY

# (A) LOAD SQLITE MODULE
import sqlite3
DBFILE = "category.db"
 
# (B) GET CATEGORIES
  def get (parent=0):
  # (B1) CONNECT
  conn = sqlite3.connect(DBFILE)
  cursor = conn.cursor()
 
  # (B2) GET CATEGORY WITH PARENT ID
  cursor.execute(
    "SELECT * FROM `category` WHERE `parent_id`=?",
    (parent,)
  )
  rows = cursor.fetchall()
 
  # (B3) NO CHILDREN IN THIS CATEGORY
  if len(rows)==0:
    return None

  # (B4) ARRANGE DATA & RECURSIVE
  data = {}
  for r in rows:
    data[r[0]] = {
      "n" : r[2],
      "c" : get(r[0])
    }
    return data

Now comes the ugly part of getting the categories. For those who are lost, here’s a quick trace of working with this example:

• When we call get(), this will run SELECT * FROM `category` WHERE `parent_id`=0.
• The data will be arranged into data[1] = { n : ELECTRONICS, c : get(1) }.
• In the second round, get() will run SELECT * FROM `category` WHERE `parent_id`=1.
• The data will be arranged into data[CATEGORY-ID] = { n : CATEGORY-NAME, c : RECURSION }.

Yes, get() is a recursive function that will “drill down” to get all the child categories.

 

 

PART 3) DRAW CATEGORIES

# (A) LOAD MODULES
import S2_lib as cat
 
# (B) DRAW CATEGORIES
def draw (data, level=0):
  for id, cat in data.items():
    # (B1) INDENTATION
    if level>0:
      for i in range(level):
        print(" ", end="")
 
    # (B2) CATEGORY ID & NAME
    print(f"({id}) {cat['n']}")
    if cat["c"] is not None:
      draw(cat["c"], level+1)
 
# (C) GO!
draw(cat.get())

Now that we have all the categories, the last step is to draw them out nicely. You may think that we are done with recursion, but no… We need yet another recursive method to draw the categories.

(1) Electronics
  (2) Computers
   (4) Desktop
   (5) Laptop
  (3) Cameras

P.S. I am just being lazy here. Feel free to draw your own “nice list” in HTML, or whatever GUI interface.

 

 

PART 4) ADD EDIT DELETE CATEGORY

Lastly, this part is kind of optional. But if you have to deal with the “category admin”, here are a couple more examples to help you.

 

4A) ADD & EDIT CATEGORY

# (C) SAVE CATEGORY
def save (name, parent, id=None):
  # (C1) CONNECT TO DATABASE
  conn = sqlite3.connect(DBFILE)
  cursor = conn.cursor()

  # (C2) ADD OR UPDATE SQL
  if id is None:
    sql = "INSERT INTO `category` (`category_name`, `parent_id`) VALUES (?,?)"
    data = (name, parent,)
  else:
    sql = "UPDATE `category` SET `category_name`=?, `parent_id`=? WHERE `category_id`=?"
    data = (name, parent, id,)

  # (C3) GO!
  cursor.execute(sql, data)
  conn.commit()
  conn.close()
  return True

Haiyaa. So easy, just do the usual SQL insert and update.

 

4B) THE “CHANGE PARENT CATEGORY” PAIN

# (D) GET CHILD CATEGORIES
def getchildren (id):
  conn = sqlite3.connect(DBFILE)
  cursor = conn.cursor()
  cursor.execute(
    "SELECT `category_id` FROM `category` WHERE `parent_id`=?",
    (id,)
  )
  cat = ()
  for r in cursor.fetchall():
    cat = cat + r
  for id in cat:
    cat = cat + getchildren(id)
  return cat

# (E) GET "LEGAL" PARENT IDS
def getswitchable (id):
  # (E1) PARENT ID CANNOT BE SELF AND CHILDREN
  illegal = getchildren(id)
  illegal = illegal + (id,)
  illegal = ",".join([str(v) for v in illegal])

  # (E2) GET ALL "LEGAL" PARENT ID
  conn = sqlite3.connect(DBFILE)
  cursor = conn.cursor()
  cursor.execute(f"SELECT `category_id` FROM `category` WHERE `category_id` NOT IN ({illegal})")
  cat = (0,)
  for r in cursor.fetchall():
    cat = cat + r
  return cat

Now, the SQL insert and update are easy. But consider these:

• If we change “computers” to a “root category” – Ok, no problem.
• If we change “computers” to a child of “camera” – Strange, but the system can still work.
• When we change “computers” to a child of “laptop” – Both laptop and desktop become orphans.

So yes, there must be checks and rules when changing the parent category. Very simply – A category can only “move up” or “move beside”. Never into a child category within the same branch.

 

 

4C) DELETE CATEGORY

# (F) DELETE CATEGORY
def delete (id):
  # (F1) CONNECT TO DATABASE
  conn = sqlite3.connect(DBFILE)
  cursor = conn.cursor()

  # (F2) REVERT ALL CHILDREN TO ROOT
  children = getchildren(id)
  if (len(children)>0):
    children = ",".join([str(v) for v in children])
    cursor.execute(f"UPDATE `category` SET `parent_id`=0 WHERE `category_id` IN ({children})")

  # (F3) DELETE CATEGORY
  cursor.execute(
    "DELETE FROM `category` WHERE `category_id`=?",
    (id,)
  )
  conn.commit()
  conn.close()
  return True

Deleting the category should be straightforward, but remember to deal with the child categories as well. In this example, we revert all the children to “root”. If you want, you can do a cascade delete.

 

 

SINGLE PARENT ONLY

As you can see, parent_id is easy to implement, but the limitation is – A category can only have one parent. If you want to allow multiple parents, another way is to create a category_to_category table with 2 fields – category_id and parent_id. This way, one category can have multiple parents. But be warned, the complexity will also go up a whole load.