Re: Nested Menus

Discussion in 'Python' started by Dennis Lee Bieber, Oct 8, 2009.

  1. On Thu, 8 Oct 2009 10:38:02 -0500, Victor Subervi
    <> declaimed the following in
    gmane.comp.python.general:

    > Hi;
    > I have the following code:
    > sql = 'create table if not exists categories (ID int(3) unsigned primary
    > key, Category varchar(40), Parent varchar(40))'
    > cursor.execute(sql)
    > cursor.execute('select Category, Parent from categories;')
    > data = cursor.fetchall()
    > parents = []
    > Parents = []
    > for dat in data:
    > if dat[1] not in parents:
    > parents.append(dat[1])
    > Categories.append(dat[0])
    > Parents.append(dat[1])


    First suggestion... Get rid of the confusing dat[0] and dat[1] and
    use names that make sense... You can do this via tuple unpacking in the
    "for" statement...

    for (flda, fldb) in list_of_tuples:
    ...

    Second... What use is parents as it is never looked at
    again...

    > cursor.execute('select Category, Parent from categories;')
    > data = cursor.fetchall()
    > parents = [] <=========
    > Parents = []
    > for dat in data:
    > if dat[1] not in parents:
    > parents.append(dat[1]) <=========
    > Categories.append(dat[0])
    > Parents.append(dat[1])


    cursor.execute(
    "select Category from categories order by Parent, ID")
    Categories = [itm[0] for itm in cursor] #untuple single column
    cursor.execute(
    "select Parent from categories order by Parent, ID")
    Parents = [itm[0] for itm in cursor]

    The above will create the equivalent of your primary two lists --
    while also keeping them linked positionally.

    > Parents.sort()
    > families = []
    > for parent in Parents:
    > children = []
    > for dat in data:
    > if dat[0] == parent:
    > children.append(dat[1])
    > families.append([parent, children])
    >
    > Now, what I want to do is to capture all nested categories, such that if x
    > is the parent of y who is the parent of z, etc., I can print out the entire
    > family tree. I'm at a loss as to how to do it! Please advise.


    Granted, the above is probably not needed to solve your problem...
    That leaves the question of how much work you want the database/SQL to
    perform... And of your understanding of recursion and recursive data
    structures... (some of the following will depend upon the actual
    database engine -- and check your manual on the syntax for foreign key
    specification; I'm purloining from MySQL)

    MAXLEVEL = 15

    cursor.execute("""create table if not exists Categories
    ( ID integer auto_increment primary key,
    Name varchar(40) not null,
    unique (Name)
    )""" )

    cursor.execute("""create table if not exists Relationship
    ( ID integer auto_increment primary key,
    Parent integer not null
    foreign key (Categories.ID),
    Child integer not null
    foreign key (Categories.ID),
    check (Parent <> Child) )""" )

    # Note that in this schema you
    # 1) never duplicate NAMES
    # 2) separate the NAMES from the parent<>child linkage

    def expand(fetched):
    aDict = {}
    for (name, ) in fetched:
    aDict[name] = {}
    return aDict

    def getChildren(levelDict, level = 0):
    if level > MAXLEVEL:
    return #possibly the data has a cycle/loop
    for (nm, dt) in levelDict:
    cursor.execute("""select c.name from Categories as c
    inner join Relationship as r
    on c.ID = r.Child
    inner join Categories as p
    on r.Parent = p.ID
    where p.Name = %s
    order by c.name""",
    (nm,) )
    levelDict[nm] = expand(cursor.fetchall())
    #recursive call to do next level
    getChildren(levelDict[nm], level + 1)
    # no data return as we are mutating dictionaries in place

    #get top level
    cursor.execute("""select Name from Categories
    order by Name""")

    theTree = expand(cursor.fetchall())
    getChildren(theTree)
    connection.commit()

    #you now have a dictionary of names, each name being the key of a
    #dictionary of children names, each of those being key to...
    #when the child dictionary is empty, you are at the end of the chain

    #you may need to add connection.commit() calls after each .fetchall()
    #or live with a potentially long running transaction that might block
    #others from updating/accessing the database.

    def printTree(aTree, level=0):
    for name in sorted(aTree.keys()):
    print "%s%s" % ("\t" * level, name)
    printTree(aTree[name], level + 1)

    printTree(theTree)

    #if all you need is the output, you could combine getChildren() and
    #printTree(), and change the dictionary to a simple list that is not
    #updated (changing expand() to return a list). This saves /some/
    #memory as you don't have the full dictionary tree saved, only
    #a list for one parent at each level being processed. It also
    #maintains sorted order from the SQL; the above dictionary
    #loses the order -- you'd need to add sorting to the print function

    -=-=-=-=-=-

    def expand(fetched):
    return [itm[0] for itm in fetched]

    def generateTree(aList, level=0):
    if level > MAXLEVEL:
    return
    for name in aList:
    print "%s%s" % ("\t" * level, name)
    cursor.execute("""select c.name from Categories as c
    inner join Relationship as r
    on c.ID = r.Child
    inner join Categories as p
    on r.Parent = p.ID
    where p.Name = %s
    order by c.name""",
    (name,) )
    sublist = expand(cursor.fetchall())
    #recursive call to do next level
    generateTree(sublist, level + 1)

    #get top level
    cursor.execute("""select Name from Categories
    order by Name""")

    theList = expand(cursor.fetchall())
    generateTree(theList)
    connection.commit()
    --
    Wulfraed Dennis Lee Bieber KD6MOG
    HTTP://wlfraed.home.netcom.com/
    Dennis Lee Bieber, Oct 8, 2009
    #1
    1. Advertising

  2. Dennis Lee Bieber

    r Guest

    On Oct 8, 5:58 pm, Dennis Lee Bieber <> wrote:
    (snip: atrocious post formatting!)

    Dear Dennis,

    This is not a personal attack on you but your writing style is so
    horrendous i must at least notify you of it. Go to this link and view
    your post in GG's and let me know what you think of it.
    http://groups.google.com/group/comp.lang.python/browse_thread/thread/6301372f8e581a74?hl=en#

    As you can see the post is virtually impossible to read from the large
    and seemingly random indenting and tabbing going on.(notwitstanding
    the bombastic verbosity and unbridled quoting). I have seen many posts
    like this in the past so maybe you are just oblivious...? Are you
    writing like this on purpose or is your newsreader bugging out?

    Sincerely,
    concerned Pythonista
    r, Oct 9, 2009
    #2
    1. Advertising

  3. On Fri, 9 Oct 2009 11:45:22 -0500, Victor Subervi
    <> declaimed the following in
    gmane.comp.python.general:

    > Well, as sometimes happens, the response to Dennis' response caught my
    > attention (out of context) and I didn't notice Dennis' response! Thanks for
    > bringing it to my attention. I will look at it tonight, and follow-up
    > tomorrow after I've had a chance to digest it and work with it. (And thank
    > you Dennis!)


    It IS a large amount of near Python of what I believe you seek to
    produce, but based upon how I'd have normalized the database, and is
    making use of repeated queries to the database engine to do much of the
    work.


    Heh... And if it weren't for your response to that other response,
    I'd not have seen the complaint... It didn't make it into the message
    list from Gmane -- I had to do online search via the references header,
    whereupon it came up in comp.lang.python via Earthlink (subcontracted to
    Giganews I believe).

    > V
    >
    > On Fri, Oct 9, 2009 at 11:13 AM, Stephen Hansen <>wrote:
    >

    Hmmm, I don't think this persons response showed up either... odd...
    Since all three of you are using gmail addresses, yet only one seems to
    be showing up in my client...
    --
    Wulfraed Dennis Lee Bieber KD6MOG
    HTTP://wlfraed.home.netcom.com/
    Dennis Lee Bieber, Oct 10, 2009
    #3
  4. On Tue, 13 Oct 2009 14:00:01 -0500, Victor Subervi
    <> declaimed the following in
    gmane.comp.python.general:

    > This is probably more appropriate for the MySQL list, but since this is
    > Dennis' pseudo-code...
    >
    > Dennis wrote the following:
    >
    > cursor.execute("""create table if not exists Relationship
    > (ID integer auto_increment primary key,
    > Parent integer not null,
    > foreign key (Categories.ID),
    > Child integer not null,
    > foreign key (Categories.ID),
    > check (Parent <> Child) );""" )
    >
    > This code throws an error in MySQL around "Categories.ID". I have never
    > worked with foreign keys. It seems there should be a "references" statement
    > after that...but what does it reference? Also, why is it repeated twice?


    I may have mistranslated the BNF description of the options...

    ....
    [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name, ...)
    [references definition]


    ....
    REFERENCES tbl_name [(index_col_name, ...)]

    (with a few other items for referential integrity)


    I'd have sworn that [xxx] were optional items, whereas (xxxx) is
    required -- with the parens...

    So, let's see what other interpretation could be meant... Maybe?

    Parent integer not null foreign key references Categories (ID),

    or

    Parent integer not null,
    foreign key (Parent) references Categories (ID),

    --
    Wulfraed Dennis Lee Bieber KD6MOG
    HTTP://wlfraed.home.netcom.com/
    Dennis Lee Bieber, Oct 14, 2009
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Russ Perry Jr
    Replies:
    2
    Views:
    4,104
    Russ Perry Jr
    Aug 20, 2004
  2. news.west.cox.net

    Select menus above flyout menus? help?

    news.west.cox.net, Dec 8, 2004, in forum: HTML
    Replies:
    8
    Views:
    782
  3. =?Utf-8?B?dGJhZA==?=

    Nested Menus in a Sitemap with a repeater

    =?Utf-8?B?dGJhZA==?=, May 30, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    1,833
    =?Utf-8?B?dGJhZA==?=
    May 30, 2006
  4. Chad E. Dollins
    Replies:
    3
    Views:
    648
    Kai-Uwe Bux
    Nov 8, 2005
  5. David Shorthouse

    AJAX nested drop-down menus...IE formatting issues

    David Shorthouse, May 30, 2006, in forum: Javascript
    Replies:
    1
    Views:
    153
    David Shorthouse
    May 30, 2006
Loading...

Share This Page