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.
Example:
|
# 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() |
Output:
|
{'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': ''} |