Open In App

Python SQLite - Update Data

Last Updated : 08 May, 2021
Comments
Improve
Suggest changes
Like Article
Like
Report

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('geeks1.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:


Next Article
Python SQLite - DROP Table
author
riturajsaha
Improve
Article Tags :
Practice Tags :

Similar Reads

We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox