0

I have made this python method:

def move_player_list_item(start_position,end_position,player_list_item):
    conn = create_connection()
    query = "DELETE FROM `player_list` WHERE `position`=?;"
    cur = conn.cursor()
    cur.execute(query,(str(start_position),))
    conn.commit()    
    
    if start_position<end_position:
        query = "UPDATE `player_list` SET `position`=`position`-1 WHERE `position`>? AND `position`<=?;"
        cur = conn.cursor()
        cur.execute(query,(str(start_position),str(end_position)))
        conn.commit()    
    elif end_position<start_position:
        query = "UPDATE `player_list` SET `position`=`position`+1 WHERE `position`>=? AND `position`<?;"
        cur = conn.cursor()
        cur.execute(query,(str(end_position),str(start_position)))
        conn.commit()  
    
  
    query = query = "INSERT INTO `player_list` (`play`, `relative_type`, `relative_number`, `repeats`, `duration_milliseconds`, `duration_human`,`position`) VALUES (?,?,?,?,?,?,?)"
    cur = conn.cursor()
    cur.execute(query,(str(player_list_item["play"]),str(player_list_item["relative_type"]),str(player_list_item["relative_number"]),str(int(player_list_item["repeats"])),str(int(player_list_item["duration_milliseconds"])),str(player_list_item["duration_human"]),str(end_position)))
    conn.commit()
    
    return 1

When start_position<end_position works with no error. But when end_position<start_position there is an error:

Traceback (most recent call last):
  File "C:\Users\Χρήστος Παππάς\Έγγραφα\projects\Papinhio player\Αρχεία βάσης δεδομένων (Sqlite3)\Έλεγχος συναρτήσεων sqlite3 (check sqlite3 functions).py", line 977, in <module>
    main()
  File "C:\Users\Χρήστος Παππάς\Έγγραφα\projects\Papinhio player\Αρχεία βάσης δεδομένων (Sqlite3)\Έλεγχος συναρτήσεων sqlite3 (check sqlite3 functions).py", line 925, in main
    sqlite3_functions.move_player_list_item(30,20,player_list_items_db[29])
  File "C:/Users/Χρήστος Παππάς/Έγγραφα/projects/Papinhio player/Αρχεία βάσης δεδομένων (Sqlite3)/../Αρχεία πηγαίου κώδικα εφαρμογής (Python)/Αρχεία κώδικα python (Python files)/Συναρτήσεις sqlite3 (Sqlite3 functions).py", line 1125, in move_player_list_item
    cur.execute(query,(str(end_position),str(start_position)))
sqlite3.IntegrityError: UNIQUE constraint failed: player_list.position

The only solution i have thought about is to remove the unique constraint. Is there any better solution?

Chris P
  • 2,059
  • 4
  • 34
  • 68
  • possible helpful or related [sqlite3, IntegrityError: UNIQUE constraint failed when inserting a value](https://stackoverflow.com/questions/36518628/sqlite3-integrityerror-unique-constraint-failed-when-inserting-a-value) – chickity china chinese chicken Oct 18 '21 at 00:39
  • If the purpose of the column `position` is to provide some kind of row ordering then this is a bad design. Tables are unordered datasets. Use window functions like ROW_NUMBER() when querying the table to get the order that you want. – forpas Oct 18 '21 at 06:46

0 Answers0