Relationships in pony.orm with create_tables=False

Question

The intention is to have two simple classes representing two related tables that already exist in the db.
The code is:

from pony.orm import *
db = Database()

class System(db.Entity):
    _table_ = 'some', 'systems'
    
    system_id = PrimaryKey(int, auto=True)
    structures = Set('Structure')

class Structure(db.Entity):
    _table_ = 'some', 'structures'
    structure_id = PrimaryKey(int, auto=True)
    system_id = Required(int)
    system = Required(System)

db.bind(...)
db.generate_mapping(create_tables=False)

I've tried to follow the approach I've seen in the documentation, but executing the code above gives me:

psycopg2.ProgrammingError: column structures.system does not exist
LINE 1: ...ctures"."structure_id", "structures"."system_id", "structure...

HINT: Perhaps you meant to reference the column "structures.system_id".

What is missing here?

Answer 1

With Pony, you don't create two separate attributes for system_id and system. Instead, you need to specify system_id as a column for attribute system. By default, Pony assumes that the column name is equal to attribute name. Then the Structure class will look like in the following example:

class Structure(db.Entity):
    _table_ = 'some', 'structures'
    structure_id = PrimaryKey(int, auto=True)
    system = Required(System, column='system_id')