SQLAlchemy将前一个默认值插入下一个默认值。

Question

我在uuid4类中有一个默认值为declarative_base的列。当我运行脚本文件时，前面的UUID值将保留下一个UUID值。如何在默认值上每次提供一个新的UUID？

import os
import uuid
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import declarative_base


Base = declarative_base()

class Test(Base):
    __tablename__ = "test"
    id = Column(Integer, autoincrement=True, primary_key=True)
    msg = Column(String(100), nullable=False)
    uuid = Column(String(32), default=uuid.uuid4().hex, nullable=True)


engine = create_engine(
    os.getenv('SQLALCHEMY_DATABASE_URI'),
    echo=True
)
Session = sessionmaker(bind=engine)

for x in range(2):
    model = Test(msg=f"Hey! - {x}")
    with Session() as session:
        session.add(model)
        session.commit()
        session.close()

回波输出

2022-10-10 14:42:44,963 INFO sqlalchemy.engine.Engine SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
2022-10-10 14:42:44,963 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-10-10 14:42:45,005 INFO sqlalchemy.engine.Engine SELECT schema_name()
2022-10-10 14:42:45,005 INFO sqlalchemy.engine.Engine [generated in 0.00022s] ()
2022-10-10 14:42:45,081 INFO sqlalchemy.engine.Engine SELECT CAST('test max support' AS NVARCHAR(max))
2022-10-10 14:42:45,081 INFO sqlalchemy.engine.Engine [generated in 0.00032s] ()
2022-10-10 14:42:45,202 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-10-10 14:42:45,204 INFO sqlalchemy.engine.Engine INSERT INTO test (msg, uuid) OUTPUT inserted.id VALUES (?, ?)
2022-10-10 14:42:45,204 INFO sqlalchemy.engine.Engine [generated in 0.00020s] ('Hey! - 0', '58a2f6d418a9428b92c0b81ad81c4585')
2022-10-10 14:42:45,311 INFO sqlalchemy.engine.Engine COMMIT
2022-10-10 14:42:45,371 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-10-10 14:42:45,371 INFO sqlalchemy.engine.Engine INSERT INTO test (msg, uuid) OUTPUT inserted.id VALUES (?, ?)
2022-10-10 14:42:45,371 INFO sqlalchemy.engine.Engine [cached since 0.1675s ago] ('Hey! - 1', '58a2f6d418a9428b92c0b81ad81c4585')
2022-10-10 14:42:45,478 INFO sqlalchemy.engine.Engine COMMIT

我所期望的输出

2022-10-10 14:56:18,458 INFO sqlalchemy.engine.Engine SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
2022-10-10 14:56:18,458 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-10-10 14:56:18,518 INFO sqlalchemy.engine.Engine SELECT schema_name()
2022-10-10 14:56:18,519 INFO sqlalchemy.engine.Engine [generated in 0.00022s] ()
2022-10-10 14:56:18,599 INFO sqlalchemy.engine.Engine SELECT CAST('test max support' AS NVARCHAR(max))
2022-10-10 14:56:18,599 INFO sqlalchemy.engine.Engine [generated in 0.00018s] ()
2022-10-10 14:56:18,704 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-10-10 14:56:18,706 INFO sqlalchemy.engine.Engine INSERT INTO test (msg, uuid) OUTPUT inserted.id VALUES (?, ?)
2022-10-10 14:56:18,706 INFO sqlalchemy.engine.Engine [generated in 0.00019s] ('Hey! - 0', '58a2f6d418a9428b92c0b81ad81c4585')
2022-10-10 14:56:18,846 INFO sqlalchemy.engine.Engine COMMIT
2022-10-10 14:56:18,862 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-10-10 14:56:18,862 INFO sqlalchemy.engine.Engine INSERT INTO test (msg, uuid) OUTPUT inserted.id VALUES (?, ?)
2022-10-10 14:56:18,863 INFO sqlalchemy.engine.Engine [cached since 0.1569s ago] ('Hey! - 1', '13f37ce5a48c44bbaa0c883f2f35da3d')
2022-10-10 14:56:18,929 INFO sqlalchemy.engine.Engine COMMIT

提前感谢！

Answer 1

当SQLAlchemy设置ORM类时，它会查看列default=条目。如果它们是一个标量值或返回标量值的函数调用，则只计算它们一次，该值将用于类的每个新实例。但是，如果default=是可调用的，那么对每个新实例都会调用它。

在您的示例中，uuid.uuid4().hex返回一个标量(str)值，因此它只被计算一次，并且对每个实例使用相同的值。取决于您可以使用的DBAPI层

    uuid = Column(String(32), default=uuid.uuid4, nullable=True)

注意uuid.uuid4，而不是uuid.uuid4() --但是由于您的列是String，所以可能无法工作。(至少对我来说，sqlite://不管用。)

另一种将default=“转换”为可调用的方法是使用lambda：

    uuid = Column(String(32), default=lambda: uuid.uuid4().hex, nullable=True)

我对sqlite://的测试产生了您想要的效果。