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:
How to Alter a SQLite Table using Python ?
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