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

 


Copyright 2020 © pythontic.com