CODEWITHSUNDEEP
pythonmysqlmysql-python
DavidJB
DavidJB

Reputation: 2362

UPDATE or INSERT MySQL Python

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

Upvotes: 9

Views: 23995

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

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()

Upvotes: 23

24_saurabh sharma
24_saurabh sharma

Reputation: 201

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!!!!")

Upvotes: -2

Related Questions