MySQL if exists (select), update, else, insert with python using date comparison

Question

i am trying to perform a db update on the condition that a db record exists in for multiple columns with values of type DATE in the event that their respective conditions are satisfied, but i appear to be having trouble. any help is much appreciated. Thanks!

the following code

    select_cmd = "SELECT * FROM " + self.designator + " WHERE player_fkid=" + str(fixed['player_fkid']) + \
                    " AND team_fkid=" + str(fixed['team_fkid']) + " AND season_fkid=" + str(fixed['season_fkid'])

    update_start_cmd = "UPDATE " + self.designator + " SET date_start='" + str(update['date_start']) + \
                    "' WHERE date_start>'" + str(update['date_start']) + "'"

    update_end_cmd = "UPDATE " + self.designator + " SET date_end='" + str(update['date_end']) + "' WHERE date_end<'" + \
                    str(update['date_end']) + "'"

    insert_cmd = "INSERT INTO " + self.designator + " (player_fkid, team_fkid, season_fkid, date_start, date_end) VALUES (" + \
                    str(fixed['player_fkid']) + ", " + str(fixed['team_fkid']) + ", " + str(fixed['season_fkid']) + ", '" + \
                    str(update['date_start']) + "', '" + str(update['date_end']) + "')"

    cmd = "IF EXISTS (" + select_cmd + ")" + "\n" + update_start_cmd + "\n" + update_end_cmd + "\nELSE " + insert_cmd

    try:
        cursor.execute(cmd)
        cnx.commit()
    except mysql.connector.errors.ProgrammingError:
        print "Error: invalid command '" + cmd + "'"

returns the following error:

Error: invalid command 'IF EXISTS (SELECT * FROM roster WHERE player_fkid=1 AND team_fkid=1 AND season_fkid=1)
UPDATE roster SET date_start='2010-04-13' WHERE date_start>'2010-04-13'
UPDATE roster SET date_end='2010-04-13' WHERE date_end<'2010-04-13'
ELSE INSERT INTO roster (player_fkid, team_fkid, season_fkid, date_start, date_end) VALUES (1, 1, 1, '2010-04-13', '2010-04-13')'

i'd like to add that when i execute said statements one by one in the mysql terminal i have no issue. not sure if the \n is the proper delimiter. — scagnetti
– scagnetti, Commented Nov 13, 2013 at 23:36
What you're looking for is a bit unclear. "update on the condition that a db record exists in for multiple columns with values of type DATE in the event that their respective conditions are satisfied". Could you try to explain that part clearer? — Maria
– Maria, Commented Nov 13, 2013 at 23:40
sure. sorry about that. there are two updates to execute in event that if exists condition is satisfied. execute first update if its conditional statement is satisfied. execute second udpate if its conditional statement is satisfied. i've reformatted the error in bold at the bottom to improve readability and to make this more clear. — scagnetti
– scagnetti, Commented Nov 13, 2013 at 23:46
@SimeonVisser, thanks! i've found this solution (stackoverflow.com/questions/13373843/…) for the if exists task. should i separate my updates into two separate statements to execute, or is there some way to combine these into one? — scagnetti
– scagnetti, Commented Nov 13, 2013 at 23:51

scagnetti · Accepted Answer · 2013-11-14 19:19:08Z

i've found that the following works:

    cmd = "SELECT COUNT(1) FROM roster WHERE player_fkid=" + str(fixed['player_fkid']) + " AND team_fkid=" + str(fixed['team_fkid']) + " AND season_fkid=" + str(fixed['season_fkid'])
    cursor.execute(cmd)
    if cursor.fetchone()[0]:
        udpate_sub_cmd = "UPDATE roster"
        date_start_sub_cmd = "SET date_start= IF(date_start > '" + str(update['date_start']) + "', '" + str(update['date_start']) + "', values(date_start)),"
        date_end_sub_cmd = "date_end= IF(date_end > '" + str(update['date_end']) + "', '" + str(update['date_end']) + "', values(date_end));"
        where_sub_cmd = "WHERE player_fkid=" + str(fixed['player_fkid']) + " AND team_fkid=" + str(fixed['team_fkid']) + " AND season_fkid=" + str(fixed['season_fkid'])
        cmd = udpate_sub_cmd + " " + date_start_sub_cmd + " " + date_end_sub_cmd + " " + where_sub_cmd
        cursor.execute(cmd, multi=True)
    else:
        insert_sub_cmd = "INSERT INTO roster (player_fkid, team_fkid, season_fkid, date_start, date_end)" 
        values_sub_cmd = "VALUES (" + str(fixed['player_fkid']) + ", " + str(fixed['team_fkid']) + ", " + str(fixed['season_fkid']) + ", '" + str(update['date_start']) + "', '" + str(update['date_end']) + "')"
        cmd = insert_sub_cmd + " " + values_sub_cmd
        cursor.execute(cmd)

using a portion of the solution from the previously mentioned link.

Gerrat · Accepted Answer · 2013-11-13 23:53:39Z

0

I think you want to use the Insert... on duplicate key update syntax.
(and specifically, conditional duplicate key updates)

Something like:

INSERT INTO roster (player_fkid, team_fkid, season_fkid, date_start, date_end) 
VALUES (1, 1, 1, '2010-04-13', '2010-04-13')
ON DUPLICATE KEY UPDATE
date_start= IF(date_start > '2010-04-13', '2010-04-13', values(date_start)),
date_end= IF(date_end > '2010-04-13', '2010-04-13', values(date_end));

answered Nov 13, 2013 at 23:53

Gerrat

29.9k9 gold badges78 silver badges104 bronze badges

1 Comment

scagnetti Over a year ago

correction. i came to find out that ON DUPLICATE KEY would not serve as a solution in this case because the table cannot have unique or primary keys. this solution therefore results in unwanted redundant entries because no keys are considered duplicates. the solution i'm currently working with is posted above (for now). if somehow this can be executed as one statement without adding a new primary key upon which the ON DUPLICATE KEY UPDATE would be leveraged, i am still somewhat curious.

Collectives™ on Stack Overflow

MySQL if exists (select), update, else, insert with python using date comparison

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related