SQLAlchemy：修剪常见数据库列前缀

Question

我有一个数据库，每个表的所有列都有一个共同的前缀（可能是为了避免使用别名）。像这样：

CREATE TABLE
    PERSON
    (
       PER_GUID RAW(16) DEFAULT SYS_GUID() NOT NULL,
       PER_FIRSTNAME NVARCHAR2(50),
       PER_LASTNAME NVARCHAR2(50),
       PRIMARY KEY (PER_GUID)
     )

现在，当我把这个结构映射到SQLAlchemy的ORM（使用declarative_base）时，我想在映射到对象时去掉这个前缀。

当然，我可以手动去掉前缀：

class Person(Base):
    __tablename__ = 'person'

    guid = Column('per_guid', RAW, primary_key = True)
    firstname = Column('per_firstname', String)
    lastname = Column('per_lastname', String)

但这样做会绕过一些声明式映射的好处，还要多打很多字。基本上，我需要的是__mapper_args__ = {'column_prefix': 'per_'}的反向操作，这个操作是给我的属性加上前缀。

那么，正确的做法是什么呢？我需要捕捉一些映射事件吗？还是需要做一些自定义的基类？前缀的长度不一样，但它们都以一个下划线结尾，所以如果有一些通用的方法，不需要指定确切的表前缀也可以。

Answer 1

如果列的前缀很容易计算出来，我们可以通过一个叫做列反射事件监听器的东西来调整每一列的 key 属性，设置成我们想要的值。这个属性是用来把ORM模型中的列和数据库表中的列对应起来的。

需要注意的是，要让这个方法有效，表必须被明确反射——也就是说，使用 Base.metadata.create_all 是不会触发这个监听器的。

import re
import sqlalchemy as sa
from sqlalchemy import orm

engine = sa.create_engine('postgresql+psycopg2:///test')


class Base(orm.DeclarativeBase):
    pass


@sa.event.listens_for(Base.metadata, "column_reflect")
def column_reflect(inspector, table, column_info):
    # Determine the prefix; here we assume all characters up to 
    # and including the first underscore in the column name.
    prefix = re.match(r'^([a-z]+_)', column_info['name'])[1]
    column_info["key"] = column_info["name"].removeprefix(prefix)


# Reflect tables of interest.
Base.metadata.reflect(engine, only=['person'])


class Person(Base):
    __table__ = Base.metadata.tables['person']


q = sa.select(Person)
print(q)

输出

SELECT person.per_raw, person.per_firstname, person.per_lastname 
FROM person

有时候，前缀可能不太好计算，比如说给定一个列名 foo_bar_baz，它的前缀可能是 foo_ 或者 foo_bar_。在这种情况下，显而易见的解决办法是比较表中所有的列名，找出共同的前缀，但列反射监听器一开始并不能访问所有的列。为了绕过这个问题，我们可以等到映射器快要被处理时，使用一个叫做 instrument_class 的监听器，而不是列反射监听器。

import os.path
...
@sa.event.listens_for(Base, 'instrument_class', propagate=True)
def receive_instrument_class(mapper, class_):
    table = class_.__table__
    column_names = [c.name for c in table.columns]
    prefix = os.path.commonprefix(column_names)
    for c in table.columns:
        c.key = c.name.removeprefix(prefix)

需要注意的是，这种方法不会影响映射类之外的表，而列反射的方法则会影响到表的任何访问方式。无论使用哪种方法，像 Column(Integer, ForeignKey('table.pk_col')) 这样的字符串化外键引用必须使用数据库中的实际列名。