如何删除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)))
)