How to add a column to a MySQL table in Python?

Adding new columns to existing MySQL tables is a common database operation. This can be accomplished in Python using the ALTER TABLE command with the MySQL connector library.

Syntax

ALTER TABLE table_name
ADD new_column_name column_definition
[FIRST | AFTER existing_column]

Here, table_name refers to the name of the table, new_column_name refers to the name of the column to be added, and column_definition refers to the datatype of the column.

The FIRST and AFTER clauses are optional and specify the position where the new column should be added. By default, the new column is inserted at the end of the table.

Steps to Add a Column

• 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

Example

Let's add a new column named "Address" of type VARCHAR(100) to an existing "Students" table −

import mysql.connector

# Establish connection
db = mysql.connector.connect(
    host="localhost",
    user="your_username", 
    password="your_password",
    database="school_db"
)

cursor = db.cursor()

# SQL query to add new column
query = "ALTER TABLE Students ADD Address VARCHAR(100)"
cursor.execute(query)
db.commit()

print("NEW COLUMN ADDED...")

# Close connection
db.close()

The output of the above code is −

NEW COLUMN ADDED...

Adding Column at Specific Position

You can also specify where the new column should be placed using FIRST or AFTER clauses −

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password", 
    database="school_db"
)

cursor = db.cursor()

# Add column after specific column
query = "ALTER TABLE Students ADD Phone VARCHAR(15) AFTER Name"
cursor.execute(query)
db.commit()

print("COLUMN ADDED AFTER 'Name' COLUMN...")
db.close()

The output of the above code is −

COLUMN ADDED AFTER 'Name' COLUMN...

Conclusion

Use the ALTER TABLE command with ADD clause to add new columns to MySQL tables in Python. Remember to commit the changes and close the database connection properly.