如何在sqlalchemy中排序简单的SemVer版本字符串

Question

我对数据库和sqlalchemy还是个新手。

我有一个模型看起来像

class MySoftware(Base):
__tablename__ = "my_software"
version: Mapped[str] = mapped_column(primary_key=True)

其中version看起来像简单的semver(例如0.0.1,0.4.2,1.2.0)。

我需要的是使用这个字符串执行简单的排序，比如

db.scalars(select(MySoftware).order_by(asc("version"))).all()

我读了很多关于混合属性和这么多奇怪的概念，我找不到一个方法。

谁能告诉我怎么做到这一点?

这里是一个最小的代码来测试它。

from pprint import pprint
from sqlalchemy import asc, create_engine, desc, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column

class Base(DeclarativeBase):
pass

class MySoftware(Base):
__tablename__ = "my_software"
version: Mapped[str] = mapped_column(primary_key=True)
def __repr__(self):
return f"MySoftware(version={self.version})"

# Create the engine and the session
engine = create_engine("sqlite://")
Base.metadata.create_all(bind=engine)
db = Session(engine)

version_list = ["0.10.1", "0.2.0", "3.0.1", "2.0.1"]
expected_version_list = ["0.2.1", "0.10.1", "2.0.1", "3.0.1"]
# Write contents
with db.begin():
for v in version_list:
db.add(MySoftware(version=v))
# Print the contents with version sorting
with engine.begin():
s = select(MySoftware).order_by(asc("version"))
pprint(db.scalars(s).all())
# Close the DB
db.close()

这返回

[MySoftware(version=0.10.1),
MySoftware(version=0.2.0),
MySoftware(version=2.0.1),
MySoftware(version=3.0.1)]

Answer 1

使用Python的内置排序可以更有效，如:

sorted_versions = sorted(unsorted_versions, key=lambda v: semver_key(v.version))

要使用SQLite和SQLAlchemy实现类似的结果，需要修改数据库中的表结构，并更新现有数据，使版本字符串的每个部分具有单独的列。

class MySoftware(Base):
__tablename__ = "my_software"
id = Column(Integer, primary_key=True)
major = Column(Integer)
minor = Column(Integer)
patch = Column(Integer)

然后，您可以直接在SQL查询中使用带有这些列的ORDER BY子句对数据进行排序。

sorted_versions = db.query(MySoftware).order_by(MySoftware.major, MySoftware.minor, MySoftware.patch).all()