复制一个由单列分组的独特的SQLite表中的完整性Error：列不是唯一的（Python）

发布于 2025-02-07 05:16:05 字数 963 浏览 3 评论 0原文

我有一个相对较小的SQLITE3数据库（〜2.6GB），带有820k行和26列（单个表）。在此数据库中，有一个名为old_table的表，目前我创建了此表，我没有主键，因此添加新行很容易添加重复项。

在效率方面，我再次创建了相同的数据库，但是这次将列REF集作为主键：'ref varchar（50）主键，'。根据许多资源，我们应该能够仅根据单个列选择QUERY select *从off ref中选择 * select *的唯一行。我想保留唯一的值，因此我将它们插入一个带有的新表中，并将其插入new_table。之后，我想用drop table old_table删除旧表。最后，使用Alter Table new_table重命名为new_table，应将new_table重命名为old_table。

为什么我的SQL表明列Ref并非唯一？

#Transferring old database to new one, with ref as unique primary key

#And delting old_table

conn = connect_to_db("/mnt/wwn-0x5002538e00000000-part1/DATABASE/database.db")
c = conn.cursor()   
c.executescript(""" 
    INSERT INTO new_table SELECT * from old_table GROUP BY Ref;
    DROP TABLE old_table;
    RENAME TO new_table
    """)

conn.close()

---------------------------------------------------------------------------
IntegrityError: column Ref is not unique

原文

I have a relatively small sqlite3 database (~2.6GB) with 820k rows and 26 columns (single table). Within this database there is a table named old_table, at the moment I created this table I had no primary key, and therefore adding new rows was prone to having duplicates being added.

In terms of efficiency, I created the same database again, but this time with the column Ref set as primary key: 'Ref VARCHAR(50) PRIMARY KEY,'. According to many resources, we should be able to select only the unique rows based on a single column with the query SELECT * from old_table GROUP BY Ref. I want to keep the unique values so I insert them into a new table with INSERT INTO new_table. Afterwards I would like to drop the old table with DROP TABLE old_table. Finally, the new_table should be renamed to old_table with ALTER TABLE new_table RENAME TO new_table.

Why does my sql state that column Ref is not unique?

#Transferring old database to new one, with ref as unique primary key

#And delting old_table

conn = connect_to_db("/mnt/wwn-0x5002538e00000000-part1/DATABASE/database.db")
c = conn.cursor()   
c.executescript(""" 
    INSERT INTO new_table SELECT * from old_table GROUP BY Ref;
    DROP TABLE old_table;
    RENAME TO new_table
    """)

conn.close()

---------------------------------------------------------------------------
IntegrityError: column Ref is not unique

分享到QQ

分享到微博