如何使用SQLalchemist创建自动生成的价值？

Question

我正在尝试使用SQLalchemist创建DB，以与Snowflake和Alembic连接到FastApi创建的应用程序的迁移。我创建了一些模型，所有模型都可以在雪花上创建这个模型以示例：

create or replace TABLE PRICE_SERVICE.FP7.LOCATION (
    ID NUMBER(38,0) NOT NULL autoincrement,
    CREATED_AT TIMESTAMP_NTZ(9),
    UPDATED_AT TIMESTAMP_NTZ(9),
    ADDRESS VARCHAR(16777216),
    LATITUDE VARCHAR(16777216) NOT NULL,
    LONGITUDE VARCHAR(16777216) NOT NULL,
    unique (LATITUDE),
    unique (LONGITUDE),
    primary key (ID)
);

但是当我尝试为此表创建一个新的OBJ时，我得到了：

sqlalchemy.orm.exc.FlushError: Instance <Location at 0x7fead79677c0> 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.

我的模型是：

class Location(Base):
    id = Column(Integer, primary_key=True)
    address = Column(String)
    latitude = Column(String, unique=True, nullable=False)
    longitude = Column(String, unique=True, nullable=False)

    buildings = relationship("Building", back_populates="location")
    quotes = relationship("Quote", back_populates="location")
    binds = relationship("Bind", back_populates="location")

而且我正在尝试这样做：

def create_location(db: Session, data: Dict[str, Any]) -> Location:
    location = Location(
        address=data["address"],  # type: ignore
        latitude=data["lat"],  # type: ignore
        longitude=data["lng"],  # type: ignore
    )
    db.add(location)
    db.commit()
    
    return location

我也是我。尝试使用：

id = Column(Integer, Sequence("id_seq"), primary_key=True)

但是我得到了：

 sqlalchemy.exc.StatementError: (sqlalchemy.exc.ProgrammingError) (snowflake.connector.errors.ProgrammingError) 000904 (42000): SQL compilation error: error line 1 at position 7
backend_1  | invalid identifier 'ID_SEQ.NEXTVAL'

Answer 1

您忘了在模型中定义序列。当您定义雪花中表创建的序列值时，序列将在架构级别生成。

from sqlalchemy import Column, Integer, Sequence
...


class Location(Base):
    id = Column(Integer, Sequence("Location_Id"), primary_key=True, 
        autoincrement=True)
    address = Column(String)
...

确保您的用户角色具有使用该顺序的权限，并且应该照顾您的问题，为您的主键设置下一个值。

一种可以帮助我使用表主键的方法是定义一个使用dectrared_attr自动根据表名称定义我的主键的Mixin类。

from sqlalchemy import Column, Integer, Sequence
from slqalchemy.ext.declarative import declared_attr


class SomeMixin(object):
    @declared_attr
    def record_id(cls):
        """
        Use table name to define pk
        """"
        return Column(
            f"{cls.__tablename__} Id",
            Integer(),
            primary_key=True, 
            autoincrement=True
        )

然后，您将所述mixin传递到您的模型中

from sqlalchemy import Column, Integer, String, Sequence
from wherever import SomeMixin

class Location(Base, SomeMixin):
    address = Column(String)
    ...

location.record_id通过您在混音中定义的序列设置。

希望这有所帮助