Update record in MySQL database table.

We will use SQL UPDATE to update records of our database student table.
We defined my_cursor as connection object.
my_cursor = my_connect.cursor() #
my_cursor.execute("UPDATE student SET class='Five' Where id=25")
my_connect.commit()
print("Rows updated = ",my_cursor.rowcount)
Output
Rows updated  =  1
We used rowcount to get the number of records updated in our student table.

Above code will update one record to our student table .

We can capture the error message if any returned by MySQL database by using try except. Here we have kept one wrong column name class1 to generate an error message. Here is the code.
my_cursor = my_connect.cursor() #
try:
    my_cursor.execute("UPDATE student SET class1='Five' Where id=25")
    my_connect.commit()
    print("Rows updated = ",my_cursor.rowcount)
except mysql.connector.Error as my_error:
    print(my_error)
my_connect.close()
Output is here
1054 (42S22): Unknown column 'class1' in 'field list'

Using parameterized queries

Place holders are used inside the query and while executing the query the variables are used. This way we can prevent injection attacks using our Query.
my_cursor = my_connect.cursor() #
try: 
    query = "UPDATE student SET class=%s Where id=%s"
    my_data=('Five',25)
    my_cursor.execute(query,my_data)
    my_connect.commit()
    print("Rows updated = ",my_cursor.rowcount)
except mysql.connector.Error as my_error:
    print(my_error)
my_connect.close()
Output is here
Rows updated = 1

Updating multiple records by using executemany()

We have more than one record to insert then we need to run the query multiple time. We can create tuple with our data and then run executemany(). Here is the code.
my_cursor = my_connect.cursor() #
try:
    query = "UPDATE student SET class=%s Where id=%s"
    my_data=[('Three',25),('Four',26),('Seven',27)]
    my_cursor.executemany(query,my_data)
    my_connect.commit()
    print("Rows updated = ",my_cursor.rowcount)
except mysql.connector.Error as my_error:
    print(my_error)
my_connect.close()
Output is here
Rows updated  =  3
More on Error handling

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2021 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer