如何删除sqlalchemy中重复的行

Question

我想删除具有相同文章名称(articulo(的行，或者如果它已经在表中，则不添加它

型号：

class MetaArt(db.Base):
__tablename__ = 'metadata'
id = Column(Integer, primary_key=True)
fecha = Column(Date)
articulo = Column(String(1000))
autores = Column(String(1000))
affili = Column(String(2000))
categoria_articulo = Column(String(200))
version = Column(String(500))
version2 = Column(String(500))

我想运行此SQL查询：

WITH articulos_repetidos AS (
SELECT MIN(id) as id, articulo FROM metadata 
GROUP BY articulo
HAVING COUNT(*)>1
)
DELETE FROM metadata
WHERE id not IN (
SELECT id FROM articulos_repetidos
) and articulo IN (SELECT articulo FROM articulos_repetidos);

我的问题是如何在SQLAlchemy核心中构建这样的语句？

Answer 1

subq = (
select(func.min(MetaArt.id).label("id"), MetaArt.articulo)
.group_by(MetaArt.articulo)
.having(func.count(literal("*")) > 1)
)
cte = subq.cte("articulos_repetidos")
stmt = (
delete(MetaArt)
.where(~MetaArt.id.in_(select(cte.c.id)))
.where(MetaArt.articulo.in_(select(cte.c.articulo)))
)