Alter a table schema using Python - PyMySQL

Overview:

The ALTER statement in SQL is one of the Data Definition Language-DDL statements along with the CREATE, DROP, TRUNCATE, COMMIT and others.
The ALTER statement changes the structure or schema of an already created database table by adding or removing one or more columns.
The ALTER statement can be used to change several other attributes of the tables such as the name of the table and name of the columns.
The ALTER also adds or removes one or more constraints and indexes as well.
A python program can execute an ALTER statement using the PyMySQL.

# import the pymysql module

import pymysql

# Making connection to the MySQL Server

hostIP = "127.0.0.1"

userName = "root"

userPassword = ""

database = "test"

charset = "utf8mb4"

cursorType = pymysql.cursors.DictCursor

con = pymysql.connect(host=hostIP,

user=userName,

password=userPassword,

db=database,

charset=charset,

cursorclass=cursorType,

autocommit=True)

try:

# Creation of cursor object

cursorObject = con.cursor()

# Alter the student table by adding one more column

alterStatement = "ALTER TABLE Student ADD courseid2 int(11);"

# Execute the SQL ALTER statement

cursorObject.execute(alterStatement)

# Verify the new schema by issuing a DESCRIBE statament

descibeStatement = "DESC Student"

# Execute the SQL SELECT query

cursorObject.execute(descibeStatement)

# Fetch the updated row

columns = cursorObject.fetchall()

# Print the updated row...

for column in columns:

print(column)

except Exception as e:

print("Exeception occured:{}".format(e))

finally:

con.close()

{'Field': 'id', 'Type': 'int(11) unsigned', 'Null': 'NO', 'Key': 'PRI', 'Default': None, 'Extra': 'auto_increment'}

{'Field': 'firstname', 'Type': 'varchar(32)', 'Null': 'YES', 'Key': '', 'Default': None, 'Extra': ''}

{'Field': 'lastname', 'Type': 'varchar(32)', 'Null': 'YES', 'Key': '', 'Default': None, 'Extra': ''}

{'Field': 'courseid', 'Type': 'int(11)', 'Null': 'YES', 'Key': '', 'Default': None, 'Extra': ''}

{'Field': 'score', 'Type': 'int(11)', 'Null': 'YES', 'Key': '', 'Default': None, 'Extra': ''}

{'Field': 'courseid1', 'Type': 'int(11)', 'Null': 'YES', 'Key': '', 'Default': None, 'Extra': ''}

{'Field': 'courseid2', 'Type': 'int(11)', 'Null': 'YES', 'Key': '', 'Default': None, 'Extra': ''}