SQLAlchemy, one to one relationship on the same ta

Question

I have a Location class. Locations can have default "bill to addresses" which are also locations. The fields that I am working with are bill_to_id and bill_to in class CustomerLocation. I have included the parent class a well for completeness. How can I set one location as the bill-to of another location? The relationship should be one-to-one (a location will only ever have one bill-to). No backref is needed.

TIA

class Location(DeclarativeBase,TimeUserMixin):
    __tablename__ = 'locations'

    location_id = Column(Integer,primary_key=True,autoincrement=True)
    location_code = Column(Unicode(10))
    name = Column(Unicode(100))
    address_one = Column(Unicode(100))
    address_two = Column(Unicode(100))
    address_three = Column(Unicode(100))
    city = Column(Unicode(100))
    state_id = Column(Integer,ForeignKey('states.state_id'))
    state_relate = relation('State')
    zip_code = Column(Unicode(100))
    phone = Column(Unicode(100))
    fax = Column(Unicode(100))
    country_id = Column(Integer,ForeignKey('countries.country_id'))
    country_relate = relation('Country')
    contact = Column(Unicode(100))
    location_type = Column('type',Unicode(50))

    __mapper_args__ = {'polymorphic_on':location_type}

class CustomerLocation(Location):
    __mapper_args__ = {'polymorphic_identity':'customer'}
    customer_id = Column(Integer,ForeignKey('customers.customer_id',
                                            use_alter=True,name='fk_customer_id'))
    customer = relation('Customer',
                        backref=backref('locations'),
                        primaryjoin='Customer.customer_id == CustomerLocation.customer_id')
    tbred_ship_code = Column(Unicode(6))
    tbred_bill_to = Column(Unicode(6))
    ship_method_id = Column(Integer,ForeignKey('ship_methods.ship_method_id'))
    ship_method = relation('ShipMethod',primaryjoin='ShipMethod.ship_method_id == CustomerLocation.ship_method_id')
    residential = Column(Boolean,default=False,nullable=False)
    free_shipping = Column(Boolean,default=False,nullable=False)
    collect = Column(Boolean,default=False,nullable=False)
    third_party = Column(Boolean,default=False,nullable=False)
    shipping_account = Column(Unicode(50))
    bill_to_id = Column(Integer,ForeignKey('locations.location_id'))
    bill_to = relation('CustomerLocation',remote_side=['locations.location_id'])

Answer 1

See my answer to a related question. You can have self-referential relationships in declarative by declaring a self-referential foreign key in the table, and either "monkey-patching" the class just after it is declared or specifying the foreign column names as strings rather than class fields. Example:

class Employee(Base):
  __tablename__ = 'employee'
  id = Column(Integer, primary_key=True)
  name = Column(String(64), nullable=False)
Employee.manager_id = Column(Integer, ForeignKey(Employee.id))
Employee.manager = relationship(Employee, backref='subordinates',
    remote_side=Employee.id)

I've successfully used this technique before which gives you both directions of the parent-child tree relationship (where a single parent can have multiple child records). If you omit the backref argument it may or may not work for you. You could always simply choose to use only one direction of the relationship in your application.