SQLAlchemy relationship fields name constructor-CodePudding

I'm using SQLAlchemy 1.4 to build my database models (posgresql).

I've stablished relationships between my models, which I follow using the different SQLAlchemy capabilities. When doing so, the fields of the related models get aliases which don't work for me.

Here's an example of one of my models:

from sqlalchemy import Column, DateTime, ForeignKey, Integer, func
from sqlalchemy.orm import relationship

class Process(declarative_model()):
    """Process database table class.

    Process model. It contains all the information about one process
    iteration. This is the proces of capturing an image with all the
    provided cameras, preprocess the images and make a prediction for
    them as well as computing the results.
    """

    id: int = Column(Integer, primary_key=True, index=True, autoincrement=True)
    """Model primary key."""
    petition_id: int = Column(Integer, ForeignKey("petition.id", ondelete="CASCADE"))
    """Foreign key to the related petition."""
    petition: "Petition" = relationship("Petition", backref="processes", lazy="joined")
    """Related petition object."""
    camera_id: int = Column(Integer, ForeignKey("camera.id", ondelete="CASCADE"))
    """Foreign key to the related camera."""
    camera: "Camera" = relationship("Camera", backref="processes", lazy="joined")
    """Related camera object."""
    n: int = Column(Integer, comment="Iteration number for the given petition.")
    """Iteration number for the given petition."""
    image: "Image" = relationship(
        "Image", back_populates="process", uselist=False, lazy="joined"
    )
    """Related image object."""
    datetime_init: datetime = Column(DateTime(timezone=True), server_default=func.now())
    """Datetime when the process started."""
    datetime_end: datetime = Column(DateTime(timezone=True), nullable=True)
    """Datetime when the process finished if so."""

The model works perfectly and joins the data by default as expected, so far so good.

My problem comes when I make a query and I extract the results through query.all() or through pd.read_sql(query.statement, db). Reading the documentation, I should get aliases for my fields like "{table_name}.{field}" but instead of that I'm getting like "{field}_{counter}". Here's an example of a query.statement for my model:

SELECT process.id, process.petition_id, process.camera_id, process.n, process.datetime_init, process.datetime_end, asset_quality_1.id AS id_2, asset_quality_1.code AS code_1, asset_quality_1.name AS name_1, asset_quality_1.active AS active_1, asset_quality_1.stock_quality_id, pit_door_1.id AS id_3, pit_door_1.code AS code_2, petition_1.id AS id_4, petition_1.user_id, petition_1.user_code, petition_1.load_code, petition_1.provider_code, petition_1.origin_code, petition_1.asset_quality_initial_id, petition_1.pit_door_id, petition_1.datetime_init AS datetime_init_1, petition_1.datetime_end AS datetime_end_1, mask_1.id AS id_5, mask_1.camera_id AS camera_id_1, mask_1.prefix_path, mask_1.position, mask_1.format, camera_1.id AS id_6, camera_1.code AS code_3, camera_1.pit_door_id AS pit_door_id_1, camera_1.position AS position_1, image_1.id AS id_7, image_1.prefix_path AS prefix_path_1, image_1.format AS format_1, image_1.process_id 
FROM process LEFT OUTER JOIN petition AS petition_1 ON petition_1.id = process.petition_id LEFT OUTER JOIN asset_quality AS asset_quality_1 ON asset_quality_1.id = petition_1.asset_quality_initial_id LEFT OUTER JOIN stock_quality AS stock_quality_1 ON stock_quality_1.id = asset_quality_1.stock_quality_id LEFT OUTER JOIN pit_door AS pit_door_1 ON pit_door_1.id = petition_1.pit_door_id LEFT OUTER JOIN camera AS camera_1 ON camera_1.id = process.camera_id LEFT OUTER JOIN mask AS mask_1 ON camera_1.id = mask_1.camera_id LEFT OUTER JOIN image AS image_1 ON process.id = image_1.process_id

Does anybody know how can I change this behavior and make it alias the fields like “{table_name}_{field}"?

CodePudding user response：

SQLAlchemy uses label styles to configure how columns are labelled in SQL statements. The default in 1.4.x is LABEL_STYLE_DISAMBIGUATE_ONLY, which will add a "counter" for columns with the same name in a query. LABEL_STYLE_TABLENAME_PLUS_COL is closer to what you want.

Default:

q = session.query(Table1, Table2).join(Table2)
q = q.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)
print(q)

gives

SELECT t1.id, t1.child_id, t2.id AS id_1 
FROM t1 JOIN t2 ON t2.id = t1.child_id

whereas

q = session.query(Table1, Table2).join(Table2)
q = q.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
print(q)

generates

SELECT t1.id AS t1_id, t1.child_id AS t1_child_id, t2.id AS t2_id 
FROM t1 JOIN t2 ON t2.id = t1.child_id

If you want to enforce a style for all orm queries you could sublcass Session:

class MySession(orm.Session):
    _label_style = LABEL_STYLE_TABLENAME_PLUS_COL

and use this class for your sessions, or pass it it a sessionmaker, if you use one:

Session = orm.sessionmaker(engine, class_=MySession)

CodePudding user response：

You can use the label argument of the Column or the relationship method to specify the custom name for a field.

For example, to give a custom label for the process.petition_id field, you can use:

petition_id = Column(Integer, ForeignKey("petition.id", ondelete="CASCADE"), label='process_petition_id')

And for the petition relationship, you can use:

petition = relationship("Petition", backref="processes", lazy="joined", lazyload=True, innerjoin=True, viewonly=False, foreign_keys=[petition_id], post_update=False, cascade='all, delete-orphan', passive_deletes=True, primaryjoin='Process.petition_id == Petition.id', single_parent=False, uselist=False, query_class=None, foreignkey=None, remote_side=None, remote_side_use_alter=False, order_by=None, secondary=None, secondaryjoin=None, back_populates=None, collection_class=None, doc=None, extend_existing=False, associationproxy=None, comparator_factory=None, proxy_property=None, impl=None, _create_eager_joins=None, dynamic=False, active_history=False, passive_updates=False, enable_typechecks=None, info=None, join_depth=None, innerjoin=None, outerjoin=None, selectin=None, selectinload=None, with_polymorphic=None, join_specified=None, viewonly=None, comparison_enabled=None, useexisting=None, label='process_petition')

With this, the fields should be aliased to process_petition_id and process_petition respectively.