In this article, we will discuss how can we alter tables in the SQLite database from a Python program using the sqlite3 module.
We can do this by using ALTER statement. It allows to:
- Add one or more column to the table
Change the name of the table.
Adding a column to a table
The syntax of ALTER TABLE to add a new column in an existing table in SQLite is given below:
ALTER TABLE table_name ADD COLUMN column_name colume_type
The column is added but will have all the values to be NULL.
To create a table:
import sqlite3
# Connecting to sqlite connection_obj = sqlite3.connect( 'geek.db' )
# cursor object cursor_obj = connection_obj.cursor()
# Drop the GEEK table if already exists. cursor_obj.execute( "DROP TABLE IF EXISTS GEEK" )
# Creating table table = """ CREATE TABLE GEEK (
Email VARCHAR(255) NOT NULL,
Name CHAR(25) NOT NULL,
Score INT
); """
cursor_obj.execute(table) # Inserting data into geek table connection_obj.execute( """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk1@gmail.com","Geek1",25)""" )
connection_obj.execute( """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk2@gmail.com","Geek2",15)""" )
connection_obj.execute( """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk3@gmail.com","Geek3",36)""" )
connection_obj.execute( """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk4@gmail.com","Geek4",27)""" )
connection_obj.execute( """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk5@gmail.com","Geek5",40)""" )
connection_obj.execute( """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk6@gmail.com","Geek6",14)""" )
connection_obj.execute( """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk7@gmail.com","Geek7",10)""" )
# Display table data = cursor_obj.execute( """SELECT * FROM GEEK""" )
print ( 'GEEK Table:' )
for row in data:
print (row)
connection_obj.commit() # Close the connection connection_obj.close() |
Output:
Now we add a new column “UserName”:
import sqlite3
# Connecting to sqlite connection_obj = sqlite3.connect( 'geek.db' )
# cursor object cursor_obj = connection_obj.cursor()
# Add a new column to geek table new_column = "ALTER TABLE GEEK ADD COLUMN UserName CHAR(25)"
cursor_obj.execute(new_column) # Display table data = cursor_obj.execute( "SELECT * FROM GEEK" )
print ( 'GEEK Table:' )
for row in data:
print (row)
connection_obj.commit() # Close the connection connection_obj.close() |
Output:
Changing the name of the table
The syntax of ALTER TABLE to change the name of the table in SQLite is given below:
ALTER TABLE table_name RENAME TO newTableName;
We will use the same GEEK table that we created above:
import sqlite3
# Connecting to sqlite connection_obj = sqlite3.connect( 'geek.db' )
# cursor object cursor_obj = connection_obj.cursor()
# select from sqlite_master cursor_obj.execute( "SELECT * FROM sqlite_master" )
table = cursor_obj.fetchall()
print ( "Before changing the name of Table" )
print ( "The name of the table:" , table[ 0 ][ 2 ])
# Rename the SQLite Table renameTable = "ALTER TABLE GEEK RENAME TO GFG"
cursor_obj.execute(renameTable) # select from sqlite_master cursor_obj.execute( "SELECT * FROM sqlite_master" )
table = cursor_obj.fetchall()
print ( "After changing the name of Table" )
print ( "The name of the table:" , table[ 0 ][ 2 ])
connection_obj.commit() connection_obj.close() |
Output: