my table looks something like this

id effect1(int) effect2(int) effect3(int) 
1     0           1               5
2     1           0               0

i have a function, which return 1to3 int value representing effect1,effect2,effect3 and "id"

i am trying to increment value of each effect by 1 when my function's returning "id" matches the "id" from my dataset otherwise creates new row and increase value of effect

for example

if my function returns (1,3) my table will become

id effect1(int) effect2(int) effect3(int) 
1     0           1               6
2     1           0               0

if my function returns (3,3) my table will become

id effect1(int) effect2(int) effect3(int) 
1     0           1               6
2     1           0               0
3     0           0               1

i have tried the following code using python, but failed. can someone please help me out here

i = (1,3) # the returning value from my function
if i[1] == 1:
    sql = "update test set effect1=effect1 1 WHERE id= ({})".format((str(i[0])))
    db.execute_query(myDb, sql)
elif i[1] == 2:
    sql = "update test set effect2=effect2 1 WHERE id= ({})".format((str(i[0])))
    val = (i[0],)
    db.execute_query(myDb, sql )
elif i[1] == 3:
    sql = "update test set effect3=effect3 1 WHERE id= ({})".format((str(i[0])))
    val = (i[0],)
    db.execute_query(myDb, sql )


def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query successful")
    except Exception as err:
        print(f"Error: '{err}'")

CodePudding user response：

Assuming that id is the primary key of the table or it is defined as unique, you can use MySql's INSERT ... ON DUPLICATE KEY UPDATE Statement like this:

i = (1, 3)
sql = """
INSERT INTO test (id, effect1, effect2, effect3)
SELECT id, (n = 1), (n = 2), (n = 3) FROM (SELECT ? id, ? n) t
ON DUPLICATE KEY UPDATE effect1 = effect1   (n = 1), 
                        effect2 = effect2   (n = 2), 
                        effect3 = effect3   (n = 3);
"""
db.execute_query(myDb, sql, i)

def execute_query(connection, query, i):
    cursor = connection.cursor()
    try:
        cursor.execute(query, i)
        connection.commit()
        print("Query successful")
    except Exception as err:
        print(f"Error: '{err}'")

See a demo of how this works in MySql.

CodePudding user response：

In your implementation, you did not check if the id already exists or not.

I updated your code with an SQL statement that performs insertion/update as you mentioned-

• in case the id exists then only the specific effect needs to be increased
• if id does not exist then a new row with that id needs to be added/inserted into the test table)

I also replaced the variable i (a list of two elements), with two variables named id and effect_no to improve the readability of the code. The if else statements are also not needed as the effect_no can be used/incorporated into the sql statement itself.

Following is the updated code:

id, effect_no = (1,3) # the returning value from your function
sql = f"""INSERT INTO test (id, effect1, effect2, effect3)
VALUES ({id}, 0, 0, 0)
ON DUPLICATE KEY UPDATE effect{effect_no} = effect{effect_no}   1"""
db.execute_query(myDb, sql)

def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query successful")
    except Exception as err:
        print(f"Error: '{err}'")

Also, I found the INSERT ... ON DUPLICATE KEY UPDATE SQL statement here https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/#using-insert--on-duplicate-key-update