使用SQLAlchemy ORM处理非主键、唯一、自增ID

Question

当我运行以下代码时，我期望first_name和last_name成为一个复合主键，并且id成为行的自增索引，但不要作为主键，因为表中其余信息是我定义它唯一性所需的，而不是给定的ID。

Base = declarative_base()
Session = sessionmaker(bind=db)
session = Session()

class Person(Base):
    __tablename__ = "people"
    id = Column(Integer, index=True, unique=True, autoincrement=True, primary_key=False)
    first_name = Column(String(30), primary_key=True)
    last_name = Column(String(30), primary_key=True)

if __name__ == "__main__":
    Base.metadata.create_all(db)
    session.add_all([
        Person(first_name="Winston", last_name="Moy"),
        Person(first_name="Bill", last_name="Gates"),
        Person(first_name="Steve", last_name="Jobs"),
        Person(first_name="Quinten", last_name="Coldwater")
    ])
    session.commit()

当我在DataGrip中查看结果时，出现以下表格。数据未按添加顺序排列，id列为空，而不是预期的自增整数。 简单明了地说：我的问题是：如何为一个不是主键的SQLAlchemy ORM类创建自增索引？

- James

---

如果您不需要递增，可以使用UUID4。 - Iain Duncan

Answer 1

在编写本文时，SQLAlchemy 1.1不支持非主键字段上的自动递增。

- James

Answer 2

SQLAlchemy 1.4 及以上版本

除了序列，您还可以在一些支持的数据库中使用 sqlalchemy.schema.Identity。OP 的示例变为：

from sqlalchemy import Identity
...

Base = declarative_base()
Session = sessionmaker(bind=db)
session = Session()

class Person(Base):
    __tablename__ = "people"
    id = Column(Integer, Identity(), index=True, unique=True)
    first_name = Column(String(30), primary_key=True)
    last_name = Column(String(30), primary_key=True)

if __name__ == "__main__":
    Base.metadata.create_all(db)
    session.add_all([
        Person(first_name="Winston", last_name="Moy"),
        Person(first_name="Bill", last_name="Gates"),
        Person(first_name="Steve", last_name="Jobs"),
        Person(first_name="Quinten", last_name="Coldwater")
    ])
    session.commit()

截至目前，已知支持Identity构造的数据库有：

• PostgreSQL（版本10及以上）。
• Oracle（版本12及以上）。它还支持传递always=None以启用默认生成模式，并且参数on_null=True可与“BY DEFAULT”身份列一起指定“ON NULL”。
• Microsoft SQL Server。MSSQL使用自定义语法，只支持start和increment参数，并忽略其他所有参数。

有关参数和支持的数据库的更多信息，请参阅以下内容。

• https://docs.sqlalchemy.org/en/14/core/defaults.html#sqlalchemy.schema.Identity
• https://docs.sqlalchemy.org/en/14/core/defaults.html#identity-ddl

- mishnea

Answer 3

Postgresql 2022答案：

以下代码

import uuid
from sqlalchemy import Integer, Column, String, Sequence
from sqlalchemy.dialects.postgresql import UUID

class Test(Base):
    __tablename__ = 'Test'

    id_sec = Sequence(__tablename__ + "_id_seq")
    id = Column(Integer, id_sec, server_default=id_sec.next_value(), nullable=False)
    uuid = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)

    # your data
    .... 

生成以下表格：

                                      Table "public.Test"
    Column     |       Type        | Collation | Nullable |                   Default                    
---------------+-------------------+-----------+----------+----------------------------------------------
 id            | integer           |           | not null | nextval('"Test_id_seq"'::regclass)
 uuid          | uuid              |           | not null | 

这样您就可以插入行而无需指定id。

PostgreSQL 13.5
SQLAlchemy==1.4.29

- Ivan

Answer 4

mysql的解决方法：

在sqlalchemy创建表之后，
　手动使用DDL更改表（添加AUTO_INCREMENT属性）。
　（在表after_create事件中）。

然后它将像create_all()一样按需执行

测试代码（1.4.x）：

from sqlalchemy.ext.declarative import declarative_base

# --- 1. define table
base = declarative_base()
class store(base):
    __tablename__ = 'store'
    id  = Column(Integer,    autoincrement=True, unique=True, primary_key=False) # AI here not work

    did = Column(String(64), unique=False, nullable=False)
    fid = Column(String(64), unique=False, nullable=False)

    __table_args__ = (
        PrimaryKeyConstraint('did', 'fid', name='idx_did_fid'),
    )


print(store)

# --- 2. fix autoincre on non-primary key // will execute as need, like create_all() dose
from sqlalchemy import event, DDL
event.listen(
    base.metadata, 'after_create',  DDL('ALTER TABLE `store` CHANGE `id` `id` INT(11) NULL DEFAULT NULL AUTO_INCREMENT')
)

# --- 3. create table as need
base.metadata.create_all(engine)

- yurenchen