Alter a SQLite table using Python

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.

SQLite Alter statement

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