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 sqliteconnection_obj = sqlite3.connect('geek.db')# cursor objectcursor_obj = connection_obj.cursor()# Drop the GEEK table if already exists.cursor_obj.execute("DROP TABLE IF EXISTS GEEK")# Creating tabletable = """ CREATE TABLE GEEK ( Email VARCHAR(255) NOT NULL, Name CHAR(25) NOT NULL, Score INT ); """cursor_obj.execute(table)# Inserting data into geek tableconnection_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 tabledata = cursor_obj.execute("""SELECT * FROM GEEK""")print('GEEK Table:')for row in data: print(row)connection_obj.commit()# Close the connectionconnection_obj.close() |
Output:
Now we add a new column “UserName”:
Python3
import sqlite3# Connecting to sqliteconnection_obj = sqlite3.connect('geek.db')# cursor objectcursor_obj = connection_obj.cursor()# Add a new column to geek tablenew_column = "ALTER TABLE GEEK ADD COLUMN UserName CHAR(25)"cursor_obj.execute(new_column)# Display tabledata = cursor_obj.execute("SELECT * FROM GEEK")print('GEEK Table:')for row in data: print(row)connection_obj.commit()# Close the connectionconnection_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 sqliteconnection_obj = sqlite3.connect('geek.db')# cursor objectcursor_obj = connection_obj.cursor()# select from sqlite_mastercursor_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 TablerenameTable = "ALTER TABLE GEEK RENAME TO GFG"cursor_obj.execute(renameTable)# select from sqlite_mastercursor_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() |
Output:
