How to remove all duplicates in a database?

Question

SQLite

I have base :

sql_average.execute("""CREATE TABLE IF NOT EXISTS prices_average (
    Сервер TEXT,
    Категория TEXT,
    Предмет TEXT,
    Цена  BIGINT,
    День BIGINT,
    Час BIGINT
)""")

Tell me how you can remove duplicates from the database that match in all values?

Answer 1

Very simple. We create another table in this database with the name prices_average2 (for example).
Next, select all rows from the prices_average database. After the selection, we will have a list of tuples (rows). We create an empty list. Loop through the elements of the list. At each iteration, we check if the current element is in the list, if yes, then we do nothing, if not, we enter the current element (tuple) into the new table prices_average2 and add it to the list. If the database had a unique column, it would be possible to do without the second table (although it may be possible now - I'm just too lazy to strain my brain at night :))
Of course, I'm not a database expert, but something tells me that if in the database there is no unique column (I forgot what it is called) - this can cause some problems.

import sqlite3

conn = sqlite3.connect('1.db')
cursor = conn.cursor()

cursor.execute("""CREATE TABLE IF NOT EXISTS prices_average2 (
    Сервер TEXT,
    Категория TEXT,
    Предмет TEXT,
    Цена  BIGINT,
    День BIGINT,
    Час BIGINT
)""")

cursor.execute('SELECT * FROM prices_average')
rows = cursor.fetchall()

unique_list = []

for row in rows:
  if row in unique_list:
    pass
  else:
    cursor.execute('INSERT INTO prices_average2 VALUES(?,?,?,?,?,?)',row)
    unique_list.append(row)

conn.commit()
conn.close()

Make a backup before experimenting.