Correct method to initialize a database table with web2py DAL.define_table()

Question

I am trying to dynamically build tables called db.blog and db.code with exactly the same SQL definitions. After I define them, I want to populate them with 10 rows of random data, and never execute that initialization code again.

My problem is that the initialization code executes every time I hit refresh on the browser while I view the newblog appadmin interface for db.code or db.blog: https://172.25.1.1/newblog/appadmin/select/db?query=db.code.id>0

I initialize db.blog and db.code in newblog/models/newblog.py:

from gluon import *
from gluon.contrib.populate import populate

## initialize db.blog and db.code: 
##     At runtime, build TAGGED_TABLES (once)
TAGGED_TABLES = set(['blog', 'code'])
for tt in TAGGED_TABLES:
    if not db.get(tt, False):
        db.define_table(tt,
            Field('name', length=32, notnull=True),
            Field('value', length=65535, notnull=True),
            Field('tags', type='list:reference tag', unique=False, notnull=False),
            )

        populate(db.get(tt), 10)

        ## cross-reference db.tagged_tables to this one
        db.tagged_tables.insert(name=tt,
            database_pointer='reference %s' % tt)
        db.commit()

Somehow if not db.get(tt, False): allows multiple executions of the routine below it. I don't understand why... if the table has already been created, then not db.get(tt, False) should be False. However, web2py never skips the initialization code, which means db.blog and db.code grow by 10 entries on each reload.

Question: Why isn't if not db.get(tt, False): preventing multiple executions?

I am running web2py 1.99.4 on Debian 6.0 / sqlite 3.7.3 / Cherokee 1.2.101 / uWSGI 0.9.9.3

Solution

Based on Interrobang's answer the correct way to write this is:

from gluon import *
from gluon.contrib.populate import populate


TAGGED_TABLES = set(['blog', 'code'])
for tt in TAGGED_TABLES:
    # db.define_table() must be called on **every page**
    #    this sets things up in memory...
    db.define_table(tt,
        Field('name', length=32, notnull=True),
        Field('value', length=65535, notnull=True),
        Field('tags', type='list:reference tag', unique=False, notnull=False),
        )

    ## initialize db.blog and db.code: 
    ##     At runtime, populate tables named in TAGGED_TABLES (once)
    if not (db(db.get(tt).id>0).select()):
        populate(db.get(tt), 10)
        ## cross-reference db.tagged_tables to this table (named in var tt)
        db.tagged_tables.insert(name=tt,
            database_pointer='reference %s' % tt)
        db.commit()

Now db.blog and db.code stay a constant size.

Summary

db.define_tables() must be called for every page rendering; my understanding (that it only needed to run once to write the table definition to disk) was incorrect.

Answer 1

You can add a fixtures file, which basically contains default data to be inserted once, when the table is created.

An example is given here: http://thadeusb.com/weblog/2010/4/21/using_fixtures_in_web2py