如何映射具有 UNIQUEIDENTIFIER 主键的 MS SQL Server 表？

Question

使用 sqlalchemy 1.4，我想将一个类映射到具有 UNIQUEIDENTIFIER 主键的表。

使用 sqlalchemy.String 不起作用（抱怨无法递增）。

检查方言，我尝试使用 sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER，但这也不起作用：

class Result(Base):

    __tablename__ = os.environ["RESULT_TABLE_NAME"]
    __table_args__ = {"schema": "myschema"}

    id = Column(UNIQUEIDENTIFIER)

sqlalchemy.exc.ArgumentError: Mapper mapped class Result->DT_ODS_RESULT could not assemble any primary key columns for mapped table 'DT_ODS_RESULT'

使用主键参数：

class Result(Base):

    __tablename__ = os.environ["RESULT_TABLE_NAME"]
    __table_args__ = {"schema": "SIDODS"}

    id = Column(UNIQUEIDENTIFIER, primary_key=True)

sqlalchemy.orm.exc.FlushError: Instance <Result at 0x7fbf9ab99d60> has a NULL identity key.  If this is an auto-generated value, check that the database table allows generation of new primary key values, and that the mapped Column object is configured to expect these generated values.  Ensure also that this flush() is not occurring at an inappropriate time, such as within a load() event.

我不理解，因为我的列有一个默认值，就像这个查询部分所示：

ALTER TABLE [SIDODS].[DT_ODS_RESULT] ADD  DEFAULT (newid()) FOR [ID]

以下是插入的行：

r = Result(
    query_id=query_id,
    trigger_id=trigger_id,
    insertion_datetime=get_datetime_at_timezone()
)
session.add(r)
session.commit()

如何正确映射我的SQLAlchemy模型类，以便我可以插入一个UNIQUEIDENTIFIER类型的行，而不需要手动指定？

- Itération 122442

Answer 1

指定primary_key=True和server_default=text("newid()")就足以让SQLAlchemy知道服务器会负责分配主键值：

from sqlalchemy import Column, String, create_engine, select, text
from sqlalchemy.dialects.mssql import UNIQUEIDENTIFIER
from sqlalchemy.orm import Session, declarative_base

engine = create_engine("mssql+pyodbc://scott:tiger^5HHH@mssql_199")
Base = declarative_base()


class Thing(Base):
    __tablename__ = "thing"
    id = Column(UNIQUEIDENTIFIER, primary_key=True, server_default=text("newid()"))
    description = Column(String(100))


Base.metadata.drop_all(engine, checkfirst=True)
engine.echo = True
Base.metadata.create_all(engine)
"""DDL emitted:
CREATE TABLE thing (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT newid(), 
    description VARCHAR(100) NULL, 
    PRIMARY KEY (id)
)
"""
engine.echo = False

with Session(engine) as sess:
    sess.add(Thing(description="a thing"))
    sess.commit()

with engine.begin() as conn:
    print(conn.execute(select(Thing.__table__)).all())
    # [('32570502-7F18-40B5-87E2-7A60232FE9BD', 'a thing')]

- Gord Thompson