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

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

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

Subhendu Mohapatra