It may be sometimes required to add a new column in a existing table. Suppose we have a “Students” table with columns such as Name, Age, Roll no. We want to add a new column “Address” into our existing table.
This can be done by using the ALTER command. The ALTER command is used to modify, drop or update columns in the database. This can also be used to add a new column into the table using ADD clause.
ALTER TABLE table_name ADD new_column_name column_definition [FIRST | AFTER exisiting_column]
Here, table_name refers to the name of the table, new_column_name refers to the name of the column to be added, column_definition refers to the datatype of the column.
The FIRST AND AFTER clause are optional.This are used to specify the particular position at which you want to add the new column. The FIRST will insert the new column at the first position. The AFTER existing_column will insert a new column after the existing_column.
By default, the new column is inserted at the end of the table.
import MySQL connector
establish connection with the connector using connect()
create the cursor object using cursor() method
create a query using the appropriate mysql statements
execute the SQL query using execute() method
close the connection
Suppose, we have a table named “Students”. We want to add a new column named “Address” of type VARCHAR(100) in the table.
import mysql.connector db=mysql.connector.connect(host="your host", user="your username", password="your password",database="database_name") cursor=db.cursor() query="ALTER TABLE Students ADD Address VARCHAR(100)" cursor.execute(query) db.commit() print("NEW COLUMN ADDED..") db.close()
The above code adds a new column named “Address” into the table. The column is inserted at the last of the existing columns.
NEW COLUMN ADDED..