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!

 

 

TABLE OF CONTENTS

 

 

QUICK SETUP

The “usual stuff”:

• Create a virtual environment virtualenv venv.
• Activate it – venv/Scripts/activate (Windows) venv/bin/activate (Linux/Mac)

 

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.

 

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

EXAMPLE CODE DOWNLOAD

Click here for the source code on GitHub gist, just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

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.