What I wanted to create was a dynamic Table of Contents page where I could have a listing of all of my published articles, organized by category, and sorted such that categories with the most posts appear first, and the posts under each category are listed from most recent at the top to oldest at the bottom. I couldn’t find a WordPress plugin for this, so I thought I would try to create it myself using Python.

I was able to accomplish this using a Python script that makes use of the MySQLdb module to query the MySQL database that WordPress uses to store all of my articles. Then on the Table of Contents page, I use an iframe to automatically load and launch the Python script that creates the ToC.

Every time a new article is published and I load the Table of Contents page, the query is run and a new, possibly reordered Table of Contents is generated.

First I had to figure out how to get Python to connect to my MySQL database to run queries. This was made more difficult due to the fact that my web host only has Python 2.5 and supports a very limited number of modules. One of the modules it does support though is MySQL-python. Here is an example of the code used in Python to connect using this module:

#!/usr/bin/python
import MySQLdb

def connect():
    # Connect to the database
    connection = MySQLdb.connect('mywebhost.com', 'my_username', 'my_password', 'my_databasename')
    try:
        cursor = connection.cursor()
        cursor.execute(sql)
        result = cursor.fetchall()
    except:
        print("Error: unable to fetch data")
    connection.close()
    print_output(result)

if __name__ == '__main__':
    connect()

Here is the SQL code I needed in order to get the post titles, post dates, post name slugs (used for creating the static links), and categories. With those four things, I had everything I needed to create the ToC:

SELECT p.post_title as 'Post Title',
       t.name as 'Category',
       p.post_date as "Post Date",
       p.post_name as "Post Name"
FROM wp_posts p,
     wp_terms t,
     wp_term_relationships tr,
     wp_term_taxonomy tx
WHERE p.post_type = 'post'
AND p.post_status = 'publish'
AND tx.taxonomy = 'category'
AND p.ID = tr.object_id
AND tr.term_taxonomy_id = t.term_id
AND tx.term_id = t.term_id;

The last parts I needed to complete this project were:

1. Creating the logic in the Python script to build the links and organize the ToC based on categories
2. The Python code needed to point to all of the CSS resources that my site uses
3. The iframe in the Table of Contents page in order to load the Python script

The final result can be seen here:

Table of Contents