no duplicates while inserting into table (python , sql)-CodePudding

I stepped over an problem while implementing a Database into my Python project.

I'm creating a new Table with the following Code:

    mycursor = mydb.cursor()
    sql = f"CREATE TABLE IF NOT EXISTS _{self.client_id} (tour_date DATE, tour_distance INT, tour_duration INT, tour_elevation_up INT, tour_elevation_down INT, tour_map_image TEXT, tour_name TEXT, tour_sport TEXT, tour_start_point TEXT, tour_type TEXT)"
    mycursor.execute(sql)
    mydb.commit()

I'm iterating over my Data and want to past it into the Table. But I won't want that if an entry already exists in the table it adds the same data again.

This is my code I currently have to Insert into my Table:

      mycursor = mydb.cursor()
      sql = f"INSERT INTO _{self.client_id} (tour_date, tour_distance, tour_duration, tour_elevation_up, tour_elevation_down, tour_map_image, tour_name, tour_sport, tour_start_point, tour_type) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) "
      val = (TourDate, TourDistance, TourDuration, TourElevation_up, TourElevation_down, TourMap_image, TourName, TourSport, TourStart_point, TourType)
      mycursor.execute(sql, val)
      mydb.commit()

So my question is how can I check if a entry already exists in the Table and then avoiding creating a duplicate?

CodePudding user response：

you can "select count() from your_table_name where client_id='current_id' if count() return int that is greater than 0, you should not insert it into the databse.

CodePudding user response：

First - avoid to use TEXT without it necessary
Second - create table with necessary indexes:

    CREATE TABLE IF NOT EXISTS _{self.client_id} (
        tour_date DATE, 
        tour_distance INT, 
        tour_duration INT, 
        tour_elevation_up INT, 
        tour_elevation_down INT, 
        tour_map_image TEXT, 
        tour_name VARCHAR(64) PRIMARY KEY, 
        tour_sport VARCHAR(64), 
        tour_start_point VARCHAR(64), 
        tour_type VARCHAR(64)
    );

Third - use INSERT IGNORE ... statement for prevent duplicates