Overview
- The SQL statement ALTER provided by SQLite can modify a number of aspects of a table schema.
- Using the RENAME TO clause of the ALTER statement the name of the SQLite table can be changed.
- The ADD COLUMN clause of the ALTER statement helps in adding a new column to an existing table.
- Detailed knowhow of SQLite is required to understand how altering the name or schema of a table affects the existing indexes, triggers and constraints on the table.

Altering a SQLite table using Python
- The ALTER SQL statement can be executed through a cursor object obtained from a database connection object.
- Database connection to an SQLite database is established by calling connect() method of the sqlite3 module and passing the database file name as argument.
- Two example Python programs are given here. One example program alters the name of an SQLite table and another example program adds a new column into two of the SQLite tables.
Example 1 – Changing the name of the SQLite table using Python
# -----Example Python Program to alter an SQLite Table-----
# import the sqlite3 module
import sqlite3
# Create a connection object
connection = sqlite3.connect("school.db")
# Get a cursor
cursor = connection.cursor()
# Rename the SQLite Table
renameTable = "ALTER TABLE stud RENAME TO student"
cursor.execute(renameTable)
# Query the SQLite master table
tableQuery = "select * from sqlite_master"
cursor.execute(tableQuery)
tableList = cursor.fetchall()
# Print the updated listed of tables after renaming the stud table
for table in tableList:
print("Database Object Type: %s"%(table[0]))
print("Name of the database object: %s"%(table[1]))
print("Name of the table: %s"%(table[2]))
print("Root page: %s"%(table[3]))
print("SQL Statement: %s"%(table[4]))
# close the database connection
connection.close()Output
Database Object Type: table
Name of the database object: teacher
Name of the table: teacher
Root page: 2
SQL Statement: CREATE TABLE teacher(id int,firstname varchar(32),lastname varchar(32))
Database Object Type: table
Name of the database object: student
Name of the table: student
Root page: 3
SQL Statement: CREATE TABLE "student"(id int,firstname varchar(32),lastname varchar(32))Example 2 – Adding one or more new columns to an SQLite table using Python
# -----Example Python Program to add new columns to an existing SQLite Table-----
# import the module sqlite3
import sqlite3
# Make a connection to the SQLite DB
dbCon = sqlite3.connect("school.db")
# Obtain a Cursor object to execute SQL statements
cur = dbCon.cursor()
# Add a new column to student table
addColumn = "ALTER TABLE student ADD COLUMN Address varchar(32)"
cur.execute(addColumn)
# Add a new column to teacher table
addColumn = "ALTER TABLE teacher ADD COLUMN Address varchar(32)"
cur.execute(addColumn)
# Retrieve the SQL statment for the tables and check the schema
masterQuery = "select * from sqlite_master"
cur.execute(masterQuery)
tableList = cur.fetchall()
for table in tableList:
print("Database Object Type: %s"%(table[0]))
print("Database Object Name: %s"%(table[1]))
print("Table Name: %s"%(table[2]))
print("Root page: %s"%(table[3]))
print("**SQL Statement**: %s"%(table[4]))
# close the database connection
dbCon.close()Output
Database Object Type: table
Database Object Name: teacher
Table Name: teacher
Root page: 2
**SQL Statement**: CREATE TABLE teacher(id int,firstname varchar(32),lastname varchar(32), Address varchar(32))
Database Object Type: table
Database Object Name: student
Table Name: student
Root page: 3
**SQL Statement**: CREATE TABLE "student"(id int,firstname varchar(32),lastname varchar(32), Address varchar(32))