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

Interactive Companion Jupyter Notebook

Explore interactive code cells, execute parameterized UPDATE queries, test error handling, and run Pandas bulk upsert operations directly in Google Colab.

View on GitHub

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 (2.0+ Context Manager)

from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

my_conn = create_engine("sqlite:///my_db.db")

try:
  with my_conn.begin() as connection:
    q = text("UPDATE student set mark=mark+1")
    r_set = connection.execute(q)
    print("No of records updated : ", r_set.rowcount)
except SQLAlchemyError as e:
  print(e)

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

Pandas Bulk Update & Upsert Workflow

When working with bulk datasets inside Pandas DataFrames, you can efficiently update or insert existing records in SQLite using `to_sql()` with temporary staging tables and SQLite's `ON CONFLICT` strategy:

import pandas as pd
from sqlalchemy import create_engine, text

my_conn = create_engine("sqlite:///my_db.db")

# Prepare update dataset inside Pandas
df_update = pd.DataFrame({
    'id': [1, 2, 3],
    'name': ['John Deo', 'Max Ruin', 'Arnold'],
    'class': ['Five', 'Six', 'Four'],
    'mark': [80, 90, 65],
    'gender': ['female', 'male', 'male']
})

with my_conn.begin() as conn:
    df_update.to_sql('temp_student', conn, if_exists='replace', index=False)
    upsert_sql = text("""
        INSERT INTO student (id, name, class, mark, gender)
        SELECT id, name, class, mark, gender FROM temp_student
        ON CONFLICT(id) DO UPDATE SET
            name = excluded.name,
            class = excluded.class,
            mark = excluded.mark,
            gender = excluded.gender;
    """)
    conn.execute(upsert_sql)
    conn.execute(text("DROP TABLE temp_student"))

Interactive Companion Jupyter Notebook

Explore interactive code cells, execute parameterized UPDATE queries, test error handling, and run Pandas bulk upsert operations directly in Google Colab.

View on GitHub

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: