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.

 

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': ''}

 

 


Copyright 2020 © pythontic.com