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)) |