SQLAlchemy：如何为包含该列的表的引用计算列的默认值？

Question

SQLAlchemy：我应该如何定义一个列的默认值，这个默认值是通过引用包含该列的表来计算的？

我们用这些表作为例子（SQLite）：

CREATE TABLE department (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employee (
  id INTEGER,
  name TEXT NOT NULL,
  department_id INTEGER NOT NULL,
  FOREIGN KEY (department_id) REFERENCES department(id),
  PRIMARY KEY (id, department_id)
);

我希望每个员工的ID在各自的部门中是唯一的。在INSERT时，新的员工ID应该比该部门之前最高的员工ID大1。

用原始SQL来说，我想做的是这个：

INSERT INTO employee(
    id,
    name,
    department_id
)
VALUES (
    (
        SELECT coalesce(MAX(id),0)+1
        FROM employee
        WHERE department_id=?
    ),
    ?,
    ?
)

用SQLAlchemy，最好的方法是什么呢？

我想找的东西有点像这里的第三列示例。类似这样：

employee_table = Table("employee", meta,
    Column('id', Integer, primary_key=True, autoincrement=False,
      default=keyvalues.select(
        func.max(employee_table.c.id)
      ).filter_by(department_id=??))
    Column('department_id', Integer, ForeignKey('department.id'),
        nullable=False, primary_key=True, autoincrement=False)
    Column('name', String(127), nullable=False),
    )

当然，这样做不行：因为我还在定义employee表，所以我还没有对这个表的引用，而且我不知道如何在filter_by子句中引用“当前”的department_id。（可能还有其他问题）

另外，如果通过Python API做不到，我有没有办法仅仅用原始SQL指定一个列的默认值（在INSERT时应用）？还是说我需要对整个插入使用原始SQL？

注意：我的情况基本上和这个问题是一样的，但我想要的解决方案不同：我想在插入时使用嵌套的SELECT，而不是创建一个数据库触发器。

编辑

我离解决这个问题更近了，但还没有完全解决。

agronholm在#sqlalchemy中解释说，仅仅使用default是无法填充department_id的，因为虽然可以在INSERT时将可选择的内容用作默认值，但没有办法填充参数（即department_id）。

相反，agronholm建议的最佳解决方案是在构造函数中创建子查询。通过分配查询（而不是运行它并分配结果！），ID将通过一个子SELECT获取。这避免了在Python端先执行SELECT然后再分配结果所导致的竞争条件。

我正在尝试这样的做法：

def __init__(self, department, name):
    self.id = db.select(
        db.func.max(Employee.id)
    ).filter_by(department_id=department.id).as_scalar()
    self.department = department
    self.data = data

不幸的是，这也不行，因为计算出的列是主键的一部分。它抛出了：

InvalidRequestError: Instance <XXXXX at 0x3d15d10> cannot be refreshed - it's not  persistent and does not contain a full primary key.

在我原来的原始SQLite版本中，我会通过游标的lastrowid访问新创建的行。在SQLAlchemy中，有类似的办法吗？

Answer 1

我遇到过类似的问题，最后找到了这个解决办法。虽然还有改进的空间——它是在插入之前先进行选择，而不是直接把它们放在一起——但看起来是有效的。

from sqlalchemy import sql

...

def default_employee_id(context):
    return context.connection.execute(
        sql.select(
            [sql.func.ifnull(sql.func.max(employee_table.c.id), 0) + 1]
        ).where(
            employee_table.c.department_id==context.current_parameters['department_id']
        )
    ).scalar()

employee_table = Table("employee", meta,
    Column('id', Integer, primary_key=True, autoincrement=False,
      default=default_employee_id),
    Column('department_id', Integer, ForeignKey('department.id'),
        nullable=False, primary_key=True, autoincrement=False),
    Column('name', String(127), nullable=False)
)

接下来我会尝试使用一个触发器，尽管文档上说这对于主键来说不是个好主意。插入前的“before_flush”事件可能也会有同样的选择问题。也许可以修改或替换context.compiled参数，以便把选择操作放进插入中，但这对于我们想要实现的目标来说似乎有点过于复杂。