In this article, we will discuss how we can update data in tables in the SQLite database using Python – sqlite3 module.
The UPDATE statement in SQL is used to update the data of an existing table in the database. We can update single columns as well as multiple columns using UPDATE statement as per our requirement.
Syntax:
UPDATE table_name SET column1 = value1, column2 = value2,…
WHERE condition;
In the above syntax, the SET statement is used to set new values to the particular column, and the WHERE clause is used to select the rows for which the columns are needed to be updated.
Below are some examples which depict how to update data in an SQLite table.
Example 1: Python SQLite program to update a particular column. In this example, we are first going to create an EMPLOYEE table and insert values into it. Then we are going to set the income of employees to 5000 whose age is less than 25
Python3
# Import module import sqlite3 # Connecting to sqlite conn = sqlite3.connect('gfg1.db') # Creating a cursor object using # the cursor() method cursor = conn.cursor() # Creating table table = """CREATE TABLE EMPLOYEE(FIRST_NAME VARCHAR(255), LAST_NAME VARCHAR(255),AGE int, SEX VARCHAR(255), INCOME int);"""cursor.execute(table) # Queries to INSERT records. cursor.execute( '''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Anand', 'Choubey', 25, 'M', 10000)''') cursor.execute( '''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Mukesh', 'Sharma', 20, 'M', 9000)''') cursor.execute( '''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Ankit', 'Pandey', 24, 'M', 6300)''') cursor.execute( '''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Subhdra ', 'Singh', 26, 'F', 8000)''') cursor.execute( '''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Tanu', 'Mishra', 24, 'F', 6500)''') # Display data inserted print("EMPLOYEE Table: ") data = cursor.execute('''SELECT * FROM EMPLOYEE''') for row in data: print(row) # Updating cursor.execute('''UPDATE EMPLOYEE SET INCOME = 5000 WHERE Age<25;''') print('\nAfter Updating...\n') # Display data print("EMPLOYEE Table: ") data = cursor.execute('''SELECT * FROM EMPLOYEE''') for row in data: print(row) # Commit your changes in the database conn.commit() # Closing the connection conn.close() |
Output:
SQLite:
Example 2: In this program, we create a similar table as that of the previous example. Here we assign the age of the female employees to 0.
Python3
# Import module import sqlite3 # Connecting to sqlite conn = sqlite3.connect('Lazyroar1.db') # Creating a cursor object using the cursor() method cursor = conn.cursor() # Creating table table = """CREATE TABLE EMPLOYEE(FIRST_NAME VARCHAR(255), LAST_NAME VARCHAR(255),AGE int, SEX VARCHAR(255), INCOME int);"""cursor.execute(table) # Queries to INSERT records. cursor.execute( '''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Anand', 'Choubey', 25, 'M', 10000)''') cursor.execute( '''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Mukesh', 'Sharma', 20, 'M', 9000)''') cursor.execute( '''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Ankit', 'Pandey', 24, 'M', 6300)''') cursor.execute( '''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Subhdra ', 'Singh', 26, 'F', 8000)''') cursor.execute( '''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Tanu', 'Mishra', 24, 'F', 6500)''') # Display data inserted print("EMPLOYEE Table: ") data = cursor.execute('''SELECT * FROM EMPLOYEE''') for row in data: print(row) # Updating cursor.execute('''UPDATE EMPLOYEE SET AGE = 0 WHERE SEX='F';''') print('\nAfer Updating...\n') # Display data print("EMPLOYEE Table: ") data = cursor.execute('''SELECT * FROM EMPLOYEE''') for row in data: print(row) # Commit your changes in the database conn.commit() # Closing the connection conn.close() |
Output:
SQLite:
Example 3: In the below program we update multiple columns using the UPDATE statement. In this example, we are first going to create a STAFF table and insert values into it. Then we are going to update all the columns i.e. all the attributes of the staff whose department is Computer.
Python3
# Import module import sqlite3 # Connecting to sqlite conn = sqlite3.connect('gfg3.db') # Creating a cursor object using the cursor() method cursor = conn.cursor() # Creating table table ="""CREATE TABLE STAFF(NAME VARCHAR(255), AGE int, DEPARTMENT VARCHAR(255));"""cursor.execute(table) # Queries to INSERT records. cursor.execute('''INSERT INTO STAFF VALUES('Anand', 45, 'Chemistry')''') cursor.execute('''INSERT INTO STAFF VALUES('Ravi', 32, 'Physics')''') cursor.execute('''INSERT INTO STAFF VALUES('Chandini', 32, 'Computer')''') cursor.execute('''INSERT INTO STAFF VALUES('Latika', 40, 'Maths')''') # Display data inserted print("STAFF Table: ") data=cursor.execute('''SELECT * FROM STAFF''') for row in data: print(row) # Updating cursor.execute('''UPDATE STAFF SET NAME = 'Ram', AGE = 30, DEPARTMENT = 'Biology' WHERE DEPARTMENT = 'Computer';''') print('\nAfter Updating...\n') # Display data print("STAFF Table: ") data=cursor.execute('''SELECT * FROM STAFF''') for row in data: print(row) # Commit your changes in the database conn.commit() # Closing the connection conn.close() |
Output:
SQLite:
Example 4: In the below program we create the previous table and update the name and age of the staff whose department is Chemistry.
Python3
# Import module import sqlite3 # Connecting to sqlite conn = sqlite3.connect('gfg4.db') # Creating a cursor object using the cursor() method cursor = conn.cursor() # Creating table table ="""CREATE TABLE STAFF(NAME VARCHAR(255), AGE int, DEPARTMENT VARCHAR(255));"""cursor.execute(table) # Queries to INSERT records. cursor.execute('''INSERT INTO STAFF VALUES('Anand', 45, 'Chemistry')''') cursor.execute('''INSERT INTO STAFF VALUES('Ravi', 32, 'Physics')''') cursor.execute('''INSERT INTO STAFF VALUES('Chandini', 32, 'Computer')''') cursor.execute('''INSERT INTO STAFF VALUES('Latika', 40, 'Maths')''') # Display data inserted print("STAFF Table: ") data=cursor.execute('''SELECT * FROM STAFF''') for row in data: print(row) # Updating cursor.execute('''UPDATE STAFF SET NAME = 'Chandini', AGE = 32 WHERE DEPARTMENT = 'Chemistry';''') print('\nAfter Updating...\n') # Display data print("STAFF Table: ") data=cursor.execute('''SELECT * FROM STAFF''') for row in data: print(row) # Commit your changes in the database conn.commit() # Closing the connection conn.close() |
Output:
SQLite:
Example 5: Below program depicts the use of the UPDATE statement without the WHERE statement. In this program, we create the STUDENT table and insert values into it. After that, we update the SECTION column of all the students by assigning it to X.
Python3
# Import module import sqlite3 # Connecting to sqlite conn = sqlite3.connect('gfg5.db') # Creating a cursor object using the cursor() method cursor = conn.cursor() # Creating table table ="""CREATE TABLE STUDENT(NAME VARCHAR(255), CLASS VARCHAR(255), SECTION VARCHAR(255));"""cursor.execute(table) # Queries to INSERT records. cursor.execute('''INSERT INTO STUDENT VALUES ('Raju', '7th', 'A')''') cursor.execute('''INSERT INTO STUDENT VALUES ('Shyam', '8th', 'B')''') cursor.execute('''INSERT INTO STUDENT VALUES ('Baburao', '9th', 'C')''') # Display data inserted print("STUDENT Table: ") data=cursor.execute('''SELECT * FROM STUDENT''') for row in data: print(row) # Updating cursor.execute('''UPDATE STUDENT SET SECTION = 'X';''') print('\nAfter Updating...\n') # Display data print("STUDENT Table: ") data=cursor.execute('''SELECT * FROM STUDENT''') for row in data: print(row) # Commit your changes in the database conn.commit() # Closing the connection conn.close() |
Output:
SQLite:
Example 6: In the below program we create a simple STUDENT table and update all the data into it using only UPDATE and SET query.
Python3
# Import module import sqlite3 # Connecting to sqlite conn = sqlite3.connect('gfg6.db') # Creating a cursor object using the cursor() method cursor = conn.cursor() # Creating table table ="""CREATE TABLE STUDENT(NAME VARCHAR(255), CLASS VARCHAR(255), SECTION VARCHAR(255));"""cursor.execute(table) # Queries to INSERT records. cursor.execute('''INSERT INTO STUDENT VALUES ('Raju', '7th', 'A')''') cursor.execute('''INSERT INTO STUDENT VALUES ('Shyam', '8th', 'B')''') cursor.execute('''INSERT INTO STUDENT VALUES ('Baburao', '9th', 'C')''') # Display data inserted print("STUDENT Table: ") data=cursor.execute('''SELECT * FROM STUDENT''') for row in data: print(row) # Updating cursor.execute('''UPDATE STUDENT SET NAME = 'X', CLASS = 'Y', SECTION = 'Z';''') print('\nAfter Updating...\n') # Display data print("STUDENT Table: ") data=cursor.execute('''SELECT * FROM STUDENT''') for row in data: print(row) # Commit your changes in the database conn.commit() # Closing the connection conn.close() |
Output:
SQLite:
