How do I "link" a web2py database?

Question

I am trying to make a database for stock data in web2py like, Company A holds data for equity in 2010, 2011, 2012... and Company B holds data for equity in 2010, 2011, 2012... This is what I come up with.

db.define_table(
    'company',
    Field('name'),
    format = '%(name)s')

db.define_table(
    'years',
    Field('fiscal_year'),
    format = '%(fiscal_year)s')

db.define_table(
    'stock_data',
    Field('company_id', db.company),
    Field('years_id', db.years),
    Field('equity', 'integer'),
    format = '%(equity)s %(years_id)s -> %(company_id)s')

db.company.name.requires = IS_NOT_IN_DB(db, 'company.name')
db.years.fiscal_year.requires = IS_NOT_IN_DB(db, 'years.fiscal_year')

The problem is that I can repeat the same year for a company many times with different values. How can I "link" a year to a company and the equity value for that year.

What is the purpose of the `years` table? Can't you put the year directly in the `stock_data` table? — User, Jan 01 '16 at 23:19

score 1 · Accepted Answer · answered Dec 31 '15 at 17:12

Assuming you want combinations of company_id and years_id to be unique, you can do something like:

db.define_table(
    'stock_data',
    Field('company_id', db.company),
    Field('years_id', db.years),
    Field('equity', 'integer'),
    format = '%(equity)s %(years_id)s -> %(company_id)s')

matching_company_id = db.stock_data.company_id == request.vars.company_id
unique_company_year = IS_NOT_IN_DB(db(matching_company_id), 'stock_data.years_id')
db.stock_data.years_id.requires = IS_IN_DB(db, 'years.id', db.years._format,
                                           _and=unique_company_year)

In the above, unique_company_year ensures that the submitted value of years_id is unique among the set of records whose company_id matches the submitted company_id (this guarantees that the company_id/years_id pair is unique while allowing non-unique values on either field alone). This IS_NOT_IN_DB validator is then passed as the _and argument to the standard IS_IN_DB validator for the field.

Thanks, it works, but what if i want to change the value or to add another field. — tompa, Jan 03 '16 at 14:50
If you want to change a value via an update form, it should still work. If you add another field and therefore need trios of values to be unique, the same logic applies -- set an `IS_NOT_IN_DB` validator on one field, defining the relevant set as those that match the submitted values on the other two fields. — Anthony, Jan 03 '16 at 20:13

How do I "link" a web2py database?

1 Answers1