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.

Francophile

+1 for nice summary of solution. – Frankpledge

@Interrobang, thank you. I upvoted as well. Your understanding was key to making this work right – Francophile

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

Frankpledge

that is good information, and I likely will go this route in the future; however, I'm still wondering why my code is broken. – Francophile

While I'm not sure on the db.get() syntax, db.define_table isn't something you run just once to create the table. It also defines your model and should run on every page load (though web2py is smart enough to not recreate your table if it exists, it will attempt to update the table if the model changes). So db.get() has nothing to get, since you haven't defined the model yet. – Frankpledge

db.get(<tablename>, False) was my attempt at checking to see whether the table had been initialized in the database. Since db behaves like a python dictionary, that looked like the right incantation; however, based on your input, I need to do something else. – Francophile