Open In App

Python SQLAlchemy – Update table structure

Improve
Improve
Like Article
Like
Save
Share
Report

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', 'geeksforgeeks'
engine = create_engine(
    url=f'mysql+pymysql://{user}:{password}@{host}/{database}?charset=utf8')
  
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', 'geeksforgeeks'
engine = create_engine(
    url=f'mysql+pymysql://{user}:{password}@{host}/{database}?charset=utf8')
  
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', 'geeksforgeeks'
engine = create_engine(
    url=f'mysql+pymysql://{user}:{password}@{host}/{database}?charset=utf8')
  
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', 'geeksforgeeks'
engine = create_engine(
    url=f'mysql+pymysql://{user}:{password}@{host}/{database}?charset=utf8')
  
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', 'geeksforgeeks'
engine = create_engine(
    url=f'mysql+pymysql://{user}:{password}@{host}/{database}?charset=utf8')
  
connection = engine.connect()
  
table_name = 'students'
  
query = f'ALTER TABLE {table_name} MODIFY id INT PRIMARY KEY;'
connection.execute(query)


Output:



Last Updated : 18 Mar, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads