How to Alter a SQLite Table using Python ?

Last Updated : 03 Jun, 2021

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:

Python3

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":

Python3

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:

Python3

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