Legacy databases and keyed tables

web2py can connect to legacy databases under some conditions.

The easiest way is when these conditions are met:

  • Each table must have a unique auto-increment integer field called “id”
  • Records must be referenced exclusively using the “id” field.

When accessing an existing table, i.e., a table not created by web2py in the current application, always set migrate=False.

If the legacy table has an auto-increment integer field but it is not called “id”, web2py can still access it but the table definition must declare the auto-increment field with ‘id’ type (that is using FIeld('...', 'id')).

Finally if the legacy table uses a primary key that is not an auto-increment id field it is possible to use a “keyed table”, for example:

  1. db.define_table('account',
  2.                 Field('accnum', 'integer'),
  3.                 Field('acctype'),
  4.                 Field('accdesc'),
  5.                 primarykey=['accnum', 'acctype'],
  6.                 migrate=False)
  • primarykey is a list of the field names that make up the primary key.
  • All primarykey fields have a NOT NULL set even if not specified.
  • Keyed tables can only reference other keyed tables.
  • Referencing fields must use the reference tablename.fieldname format.
  • The update_record function is not available for Rows of keyed tables.

Currently keyed tables are only supported for DB2, MSSQL, Ingres and Informix, but others engines will be added.

At the time of writing, we cannot guarantee that the primarykey attribute works with every existing legacy table and every supported database backend. For simplicity, we recommend, if possible, creating a database view that has an auto-increment id field.