Prerequisite: Python: MySQL Create Table
Python allows the integration of a wide range of database servers with applications. A database interface is required to access a database from Python. MySQL Connector-Python module is an API in python for communicating with a MySQL database.
ALTER statement of SQL
With the ALTER statement, one can add, drop, or modify a column of an existing table as well as modify table constraints.
The syntax for adding a column with ALTER statement:
ALTER TABLE table_name ADD new_column_name column_definition [FIRST | AFTER column_name];
Here, the table_name is the name of the table to which the column is being added, new_column_name is the name of the column to be added and column_definition is the datatype and definition of the column to be added. FIRST and AFTER are optional statements that tell MySQL the position for the new column in the table. If this parameter is not specified then the new column is added to the end of the table.
Implementation:
To add a column to a MySQL table in Python, first establish a connection with the database server. Then create a cursor object. This cursor object interacts with the MySQL server and can be used to perform operations such as execute SQL statements, fetch data and call procedures. So, using this cursor object, execute the ALTER statement to add a column either at the end or at a specific position. Let’s see some examples for better understanding.
Database in use:
We will use a database with a students table describing student details such as roll number and name. Before we learn to add a column to a table, create such a sample table (inserting values is optional).
Example 1:
This example shows the addition of a column at the end of the table. Steps are as follows:
- Use the connect() function to establish a connection with the database server. Pass the host, user (root or your username), password (if present), and database parameters to the connect() method.
- Then to create a cursor object, use the cursor() function.
- Execute the ALTER statement for adding the streaming column to the students table.
- To check if the column has been added execute and fetch the result of the DESC statement to describe the structure of the table.
Code:
Python
# Import required packages import mysql.connector # Establish connection to MySQL database mydb = mysql.connector.connect( host = "localhost", user = "username", password = "neveropen", database = "College") # Create a cursor object mycursor = mydb.cursor() # MySQL query for adding a column query = "ALTER TABLE students \ ADD stream VARCHAR(100) DEFAULT 'CS'" # Execute the query mycursor.execute(query) # Print description of students table mycursor.execute("desc students") myresult = mycursor.fetchall() for row in myresult: print(row) # Close database connection mydb.close() |
Output:
Example 2:
This example shows the addition of a column at the beginning of the table. Follow the same steps as the above example for establishing a connection and creating a cursor object. Use the keyword FIRST in the ALTER statement to append a column, enrollment year, at the beginning of the students table.
Code:
Python
# Import required packages import mysql.connector # Establish connection to MySQL database mydb = mysql.connector.connect( host = "localhost", user = "username", password = "neveropen", database = "College") # Create a cursor object mycursor = mydb.cursor() # MySQL query for adding a column # at the beginning of table query = "ALTER TABLE students \ ADD enrollment_year VARCHAR(100) \ FIRST" # Execute the query mycursor.execute(query) # Print description of students table mycursor.execute("desc students") myresult = mycursor.fetchall() for row in myresult: print(row) # Close database connection mydb.close() |
Output:
Example 3:
This example shows addition of a column at a specific position in the table. Follow the same steps as example 1 for establishing a connection and creating a cursor object. Use the keyword AFTER in the ALTER statement to append an email column after the name column in the students table.
Python
# Import required packages import mysql.connector # Establish connection to MySQL database mydb = mysql.connector.connect( host = "localhost", user = "username", password = "neveropen", database = "College") # Create a cursor object mycursor = mydb.cursor() # MySQL query for adding a column # after a specific column query = "ALTER TABLE students \ ADD email VARCHAR(100) \ AFTER Name" # Execute the query mycursor.execute(query) # Print description of students table mycursor.execute("desc students") myresult = mycursor.fetchall() for row in myresult: print(row) # Close database connection mydb.close() |
Output:
Database after adding columns:
Note: If there are existing values or rows in the table, the new column is Null or the specified default value.
