Hi, I'm ThadeusB.

I code stuff. I raise bees. I game.

Using fixtures in web2py

So you are ready to deploy your newly finished web2py app, but you don't like the idea of having to manually insert all of this fixture data again!

Create a new model, and name it x_fixtures.py. This way it will execute after all of your models.

Then for every table that you want to pre-populate use the following snippet. I will use an example for the auth_user table to pre-create a test user.

if db(db.auth_user.id > 0).count() == 0:
    db.auth_user.insert(
        first_name='Testers',
        last_name='Inc',
        email='test@user.com',
        password='<include a pre-encrypted password here>',
    )

Basically, if we have no records in the table, insert the defaults! This is probably more useful in situations where you have pre-defined data that can be dynamic, such as a list of status codes.

if db(db.status.id > 0).count() == 0:
   db.status.insert(name='pending', kkey='P')
   db.status.insert(name='processing', kkey='R')
   db.status.insert(name='done', kkey='D')

You can easily keep your data fixtures in a YAML file and load them into the database by using the pyyaml library.

Here is the example YAML file for what we just inserted.

auth_user:
    - first_name: Testers
      last_name: Inc
      email: test@user.com
      password: <include a pre-encrypted password here>
status:
    - name: In-Queue
      kkey: Q
    - name: Pending
      kkey: P
    - name: Processing
      kkey: R
    - name: Done
      kkey: D

We just open the file, pass this to pyyaml, and insert into the database, easy right?

import yaml

data = yaml.load(open('/path/to/applications/myapp/private/fixtures.yaml'))

for table_name, rows in data:
    for r in rows:
        db[table_name].insert(
            **r
        )

Video on Generate web2py model by using yaml -> http://vimeo.com/10837176

Now you have gone done it, you placed your code on the production server, and found a bug! Now we need to reset the database to square one.

Note: Your table indexes will not reset to 0

Add the following code to the top of your x_fixtures.py file, when you want to reset the database, just change RESET to True.

RESET = False

if RESET:
    for table in db.tables:
        # Make sure to cascade, or this will fail 
        # for tables that have FK references.
        db[table].truncate("CASCADE")    
    db.commit()

Make sure to remove your x_fixtures.py file when you are ready to go live! And compile that app!