Python sqlite updating data using where query and getting number of records updated of a table

We are using our student table. Check how to create database and student table here.

Read more on UPDATE query here.

Update single column

We will add 5 mark to student with id=7

q="update student set mark=mark+5 WHERE id =7"
r_set=my_conn.execute(q)
print("Records updated")

UPDATE Query to change single or multiple data table & get number of rows affected by rowcount

Updating multiple columns with error printing

We can display error message if there is any error by using try except . We will also update all class Four to class Five.

q="UPDATE student set class='Five' WHERE class='Four' "
try:
    r_set=my_conn.execute(q)
    print("Records updated")
except sqlite3.Error as my_error:
  print("error: ",my_error)

Output

Records updated

Number of records updated

We can display number of rows updated by using rowcount, output is 9

print(r_set.rowcount) # 9

Using SQLAlchemy

from sqlalchemy.exc import SQLAlchemyError
q="UPDATE STUDENT set mark=mark+1"
try:
  r_set=my_conn.execute(q)
except SQLAlchemyError as e:
  error=str(e.__dict__['orig'])
  print(error)
else:
  print("No of records updated : ",r_set.rowcount)

Using Parameters

Always use parameterized query when the data is coming from unknown sources. Use ? as placeholder and a provide a tuple to pass the value to query or execute() method. This is required to prevent injection attack.

We have used placeholders ( ? ) in our query and note that my_data is a tuple used for passing value to execute() method for our query.

We have used rowcount to display number of rows affected or updated.

my_data=('Seven','Six') # tuple to pass values to execute method
q="UPDATE student set class=? WHERE class=? "
try:
    r_set=my_conn.execute(q,my_data)
    print("Records updated : ",r_set.rowcount)
except sqlite3.Error as my_error:
  print("error: ",my_error)

Output

Records updated :  1

Updating single records

q="update student set mark=mark+5 WHERE id =9"
r_set=my_conn.execute(q)
print("Records updated : ",r_set.rowcount)

Output

Records updated :  1

Sqlite Update Binary data by using Blob Column
Connection insert Delete select Order By

Numpy Pandas Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

SQLite Updating records

Update single column

Updating multiple columns with error printing

Number of records updated

Using SQLAlchemy

Using Parameters

Subscribe