Python SQLite – Update Data
Last Updated :
08 May, 2021
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 sqlite3
conn = sqlite3.connect( 'gfg1.db' )
cursor = conn.cursor()
table =
cursor.execute(table)
cursor.execute(
)
cursor.execute(
)
cursor.execute(
)
cursor.execute(
)
cursor.execute(
)
print ( "EMPLOYEE Table: " )
data = cursor.execute( )
for row in data:
print (row)
cursor.execute( )
print ( '\nAfter Updating...\n' )
print ( "EMPLOYEE Table: " )
data = cursor.execute( )
for row in data:
print (row)
conn.commit()
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 sqlite3
conn = sqlite3.connect( 'geeks1.db' )
cursor = conn.cursor()
table =
cursor.execute(table)
cursor.execute(
)
cursor.execute(
)
cursor.execute(
)
cursor.execute(
)
cursor.execute(
)
print ( "EMPLOYEE Table: " )
data = cursor.execute( )
for row in data:
print (row)
cursor.execute( )
print ( '\nAfer Updating...\n' )
print ( "EMPLOYEE Table: " )
data = cursor.execute( )
for row in data:
print (row)
conn.commit()
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 sqlite3
conn = sqlite3.connect( 'gfg3.db' )
cursor = conn.cursor()
table =
cursor.execute(table)
cursor.execute( )
cursor.execute( )
cursor.execute( )
cursor.execute( )
print ( "STAFF Table: " )
data = cursor.execute( )
for row in data:
print (row)
cursor.execute(
)
print ( '\nAfter Updating...\n' )
print ( "STAFF Table: " )
data = cursor.execute( )
for row in data:
print (row)
conn.commit()
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 sqlite3
conn = sqlite3.connect( 'gfg4.db' )
cursor = conn.cursor()
table =
cursor.execute(table)
cursor.execute( )
cursor.execute( )
cursor.execute( )
cursor.execute( )
print ( "STAFF Table: " )
data = cursor.execute( )
for row in data:
print (row)
cursor.execute(
)
print ( '\nAfter Updating...\n' )
print ( "STAFF Table: " )
data = cursor.execute( )
for row in data:
print (row)
conn.commit()
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 sqlite3
conn = sqlite3.connect( 'gfg5.db' )
cursor = conn.cursor()
table =
cursor.execute(table)
cursor.execute( )
cursor.execute( )
cursor.execute( )
print ( "STUDENT Table: " )
data = cursor.execute( )
for row in data:
print (row)
cursor.execute( )
print ( '\nAfter Updating...\n' )
print ( "STUDENT Table: " )
data = cursor.execute( )
for row in data:
print (row)
conn.commit()
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 sqlite3
conn = sqlite3.connect( 'gfg6.db' )
cursor = conn.cursor()
table =
cursor.execute(table)
cursor.execute( )
cursor.execute( )
cursor.execute( )
print ( "STUDENT Table: " )
data = cursor.execute( )
for row in data:
print (row)
cursor.execute(
)
print ( '\nAfter Updating...\n' )
print ( "STUDENT Table: " )
data = cursor.execute( )
for row in data:
print (row)
conn.commit()
conn.close()
|
Output:
SQLite:
Share your thoughts in the comments
Please Login to comment...