CODEWITHSUNDEEP
pythondatabaseweb2py
tompa
tompa

Reputation: 3

How do I "link" a web2py database?

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.

Upvotes: 0

Views: 109

Answers (1)

Anthony
Anthony

Reputation: 25536

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.

Upvotes: 1

Related Questions