Python SQLAlchemy - Update table structure

In this article, we are going to update the structure of the table using the sqlalchemy module.

The structure of the table includes name of columns, the datatype of columns, constraints, keys, etc which we are going to update in this post.

Installing the requirements:

Let us first install the SQLAlchemy module by running the following pip command in the terminal:

pip install sqlalchemy pymysql

Note: pymysql is a dependency of sqlalchemy which we need to install for this post

database used :

👁 Image

Stepwise Implementation

Step 1: Importing and connecting

The first step includes importing the sqlalchemy module and connection to the database. This we can do by the following code:

Step 2: Defining the table name

Let us now define the table name we will use:

Step 3: Defining and executing the query

So now we will define the sql query to update the structure of the table. For performing our task we will use alter command of sql which is used to update the table. 

Syntax of altering command:

ALTER TABLE table_name CLAUSE change_in_structure;

Here, clauses can be: ADD, RENAME, CHANGE, DROP, MODIFY

The use of each clause is as follows :

• ADD: used to add a new column
• RENAME: used to rename the table
• CHANGE:  used to rename a column
• DROP: used to drop a column
• MODIFY: used to modify a column

Syntax of a query with add clause:

ALTER TABLE table_name ADD column_name DATATYPE CONSTRAINTS ; 

Syntax of quea ry with rename clause:

ALTER TABLE table_name RENAME new_name_of_table ;

Syntax of a query with change clause:

ALTER TABLE table_name CHANGE old_column new_column DATATYPE CONSTRAINTS ;

Syntax of a query with drop clause:

ALTER TABLE table_name DROP column_name;

Syntax of a query with modifying clause:

ALTER TABLE table_name MODIFY column_name DATATYPE CONSTRAINTS ;

Let us see an example of each of the above :

Example 1: Adding a new column

Let us add a new column to our table named "gender" which can accept 2 values namely "m" for male and "f" for female.

Output:

👁 Image

Example 2: Dropping the column

Let us drop the "gender" column which we have created above in this example.

Output:

👁 Image

Example 3: Renaming a column

Let us rename the column "sno" to "id" with the help of the CHANGE clause in ALTER command of sql.

Output:

👁 Image

Example 4: Modifying a column

Let us modify our column "id" and make it a primary key. 

Output:

👁 Image