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 :
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:
Python3
from sqlalchemy import create_engine user, password, host, database = 'root', '123', 'localhost', 'neveropen'engine = create_engine( connection = engine.connect() |
Step 2: Defining the table name
Let us now define the table name we will use:
Python3
table_name = 'students' |
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.
Python3
from sqlalchemy import create_engine user, password, host, database = 'root', '123', 'localhost', 'neveropen'engine = create_engine( connection = engine.connect() table_name = 'students' query = f'ALTER TABLE {table_name} ADD gender ENUM("m","f") ;'connection.execute(query) |
Output:
Example 2: Dropping the column
Let us drop the “gender” column which we have created above in this example.
Python3
from sqlalchemy import create_engine user, password, host, database = 'root', '123', 'localhost', 'neveropen'engine = create_engine( connection = engine.connect() table_name = 'students' query = f'ALTER TABLE {table_name} DROP gender ;'connection.execute(query) |
Output:
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.
Python3
from sqlalchemy import create_engine user, password, host, database = 'root', '123', 'localhost', 'neveropen'engine = create_engine( connection = engine.connect() table_name = 'students' query = f'ALTER TABLE {table_name} CHANGE sno id INT;'connection.execute(query) |
Output:
Example 4: Modifying a column
Let us modify our column “id” and make it a primary key.
Python3
from sqlalchemy import create_engine user, password, host, database = 'root', '123', 'localhost', 'neveropen'engine = create_engine( connection = engine.connect() table_name = 'students' query = f'ALTER TABLE {table_name} MODIFY id INT PRIMARY KEY;'connection.execute(query) |
Output:
