UPDATE or INSERT MySQL Python

Question

I need to update a row if a record already exists or create a new one if it dosen't. I undersant ON DUPLICATE KEY will accomplish this using MYSQLdb, however I'm having trouble getting it working. My code is below

        cursor = database.cursor()
        cursor.execute("INSERT INTO userfan (user_id, number, round VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE user_id =%s, number=%s, round=%s", (user_id, number, round))
        database.commit()

primary key is user_id

And what are the columns of the table? It seems you have round and roun_id. Is that a typo or are these 2 columns? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Mar 17, 2013 at 20:07

heman123 · Accepted Answer · 2023-07-18 19:36:29Z

23

A parenthesis was missing. You can also use the VALUES(column) in the ON DUPLICATE KEY UPDATE section of the statement:

    cursor = database.cursor()
    cursor.execute("""
        INSERT INTO userfan 
            (user_id, number, round)
        VALUES 
            (%s, %s, %s) 
        ON DUPLICATE KEY UPDATE 
                                          -- no need to update the PK
            number  = VALUES(number), 
            round   = VALUES(round) ;
                   """, (user_id, number, round)     # python variables
                  )
    database.commit()

edited Jul 18, 2023 at 19:36

heman123

3,0034 gold badges27 silver badges40 bronze badges

answered Mar 17, 2013 at 20:11

ypercubeᵀᴹ

116k19 gold badges181 silver badges247 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DavidJB Over a year ago

Thanks, I thought it was something simple! One more question, do you know if it is possible to enter the statement on multiple lines as you have in your answer, otherwise I get very long lines of code.. I use eclipse..

ypercubeᵀᴹ Over a year ago

@john See the edit. Read this about Python strings

24_saurabh sharma · Accepted Answer · 2022-01-27 12:13:39Z

def insertAndUpdateData(lVideoList, no_of_gate):
    connection = sqlite3.connect('db.sqlite',
                                 detect_types=sqlite3.PARSE_DECLTYPES |
                                              sqlite3.PARSE_COLNAMES)
    cursor = connection.cursor()
    success = 200
    unsuccess = 500
    default_value = 0
    lDefaultEntry = None
    for i in range(no_of_gate):
        gate_id = i+1
        for videofilename in lVideoList:            
            cursor.execute("SELECT * FROM dailyfootfall WHERE csv_name=? AND gate_id=?", [videofilename, gate_id])
            lDefaultEntry = cursor.fetchone()
            try:
                
                if lDefaultEntry is not None:
                    
                    print ('Entry found...!!!')

                    cursor.execute("UPDATE dailyfootfall SET video_download=?, processed=?, send_status=? ,male_footfall=?, send_status_male=?, "
                                   "female_footfall =?,send_status_female=?, outsiders=?, send_status_outsiders=? "
                                   "WHERE csv_name=? AND gate_id=? AND footfall=0", [unsuccess,unsuccess,unsuccess,default_value,unsuccess,
                                                                                    default_value,unsuccess,default_value,unsuccess,videofilename,gate_id])
                    print("Data_Updated..!!!")
                    
                else:
                    cursor = connection.cursor()
                    print ('Entry Not found...!!!')
                    print("videofilename: ", videofilename)
                    insert_query = ("INSERT or IGNORE INTO dailyfootfall(csv_name, video_download, processed, footfall, send_status, "
                                    "male_footfall, send_status_male, female_footfall, send_status_female, gate_id,outsiders, send_status_outsiders) "
                                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)")

                    cursor.execute(insert_query,[videofilename, unsuccess, unsuccess, default_value, unsuccess, default_value,
                                                 unsuccess, default_value, unsuccess, gate_id, default_value, unsuccess])

                    print("Data_Inserted..!!")
                    print("="*20)
                    
                
                
            except Exception as e:
                
                exc_type, exc_obj, exc_tb = sys.exc_info()
                fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
                print("Entry found: ",exc_type, fname, exc_tb.tb_lineno)
                
    print("Data Inserted Successfully !")
    connection.commit()
    cursor.close()
    connection.close()

if __name__ == "__main__":

  
    lVideoList = ['2022_01_27_10_00_00-2022_01_25_10_30_00', '2022_01_27_10_30_00-2022_01_25_11_00_00',
                '2022_01_27_11_00_00-2022_01_25_11_30_00', '2022_01_27_11_30_00-2022_01_25_12_00_00']
    no_of_gate = 3


    UpdateData(lVideoList, no_of_gate)


    print("default_value inserted!!!!")

Hi and thanks for the answer. Your answer would be much more valuable to us if you added an explanation about what your code does and why it helps the OP

Collectives™ on Stack Overflow

UPDATE or INSERT MySQL Python

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related