Skip to content
Related Articles

Related Articles

Improve Article

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

Output:

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning – Basic Level Course




My Personal Notes arrow_drop_up
Recommended Articles
Page :