python sqlalchemy非重复列值

Question

我的SQLite数据库中有6个表，每个表有6列（Date, user, NormalA, specialA, contact, remarks）和1000多行。在

如何使用sqlalchemy对Date列进行排序以查找重复的日期并删除该行？在

Answer 1

受Find duplicate values in SQL table的启发，这可能有助于您选择重复的日期：

query = session.query(
    MyTable
).\
    having(func.count(MyTable.date) > 1).\
    group_by(MyTable.date).all()

如果您只想显示唯一的日期；^{}是您可能需要的

Answer 2

虽然我喜欢SQLAlchemy的整个面向对象方法，但有时我发现直接使用SQL更容易。 由于记录没有键，我们需要行号（_ROWID_）来删除目标记录，我不认为API提供了它。在

首先我们连接到数据库：

from sqlalchemy import create_engine
db = create_engine(r'sqlite:///C:\temp\example.db')
eng = db.engine

然后列出所有记录：

^{pr2}$

以及显示日期相同的所有重复记录：

for row in eng.execute("""
  SELECT * FROM {table}
  WHERE {field} IN (SELECT {field} FROM {table} GROUP BY {field} HAVING COUNT(*) > 1)
  ORDER BY {field};
  """.format(table="TableA", field="Date")) :
  print row

现在我们已经识别了所有重复项，如果其他字段不同，则可能需要修复它们：

eng.execute("UPDATE TableA SET NormalA=18, specialA=20 WHERE Date = '2016-18-12' ;");
eng.execute("UPDATE TableA SET NormalA=4,  specialA=8  WHERE Date = '2015-18-12' ;");

最后保留第一条插入的记录并删除最近的重复记录：

print eng.execute("""
  DELETE FROM {table} 
  WHERE _ROWID_ NOT IN (SELECT MIN(_ROWID_) FROM {table} GROUP BY {field});
  """.format(table="TableA", field="Date")).rowcount

或者保留最后插入的记录并删除其他重复的记录：

print eng.execute("""
  DELETE FROM {table} 
  WHERE _ROWID_ NOT IN (SELECT MAX(_ROWID_) FROM {table} GROUP BY {field});
  """.format(table="TableA", field="Date")).rowcount

Answer 3

假设这是您的模型：

class MyTable(Base):
    __tablename__ = 'my_table'
    id = Column(Integer, primary_key=True)
    date = Column(DateTime)
    user = Column(String)
    # do not really care of columns other than `id` and `date`
    # important here is the fact that `id` is a PK

以下是两种删除数据的方法：

1. 找到重复项，将其标记为删除并提交事务
2. 创建一个直接对数据库执行删除的SQL查询。在

对于这两者，将使用一个helper子查询：

^{pr2}$

选项1：查找重复项，将其标记为删除并提交事务

# query to find all duplicates
q_duplicates = (
    session
    .query(MyTable)
    .join(subq, and_(
        MyTable.date == subq.c.date,
        MyTable.id != subq.c.min_id)
    )
)

for x in q_duplicates:
    print("Will delete %s" % x)
    session.delete(x)
session.commit()

选项2：创建单个SQL查询，直接对数据库执行删除

sq = (
    session
    .query(MyTable.id)
    .join(subq, and_(
        MyTable.date == subq.c.date,
        MyTable.id != subq.c.min_id)
    )
).subquery("subq")

dq = (
    session
    .query(MyTable)
    .filter(MyTable.id.in_(sq))
).delete(synchronize_session=False)