Adding a new NOT NULL column in MySQL using Python

Last Updated : 24 Feb, 2021

Prerequisite: Python: MySQL Create Table

In this article, we are going to see how to add a new NOT NULL column in MySQL using Python. Python allows the integration of a wide range of database servers with applications. A database interface is required to access a database from Python. MySQL Connector Python module is an API in python for communicating with a MySQL database.

Database table in use:

We are going to use geeks(Database name) database and table describing the salary.

Approach:

Import module.
Make a connection request with the database.
Create an object for the database cursor.
Execute the following MySQL query:

ALTER TABLE table_name ADD colunm_name datatype NOT NULL;
insert into table_name value('Value');

And print the result.

Before starting let do the same in SQL:

Step 1: Adding a not null column in the table.

ALTER TABLE employee ADD mobile_number varchar(255) not null;

Step 2: When you create a NOT NULL column then you can not insert NULL value on that column. See the error.

Step 3: Add the Not Null value into the column.

And check the table is contains your updated data or not:

Below is the implementation in python:

Python3

# Establish connection to MySQL database
import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="root123",
  database = "geeks"
  )

# getting the cursor by cursor() method
mycursor = db.cursor()

query = "ALTER TABLE employee ADD Place varchar(255) not null;"
query_1 = "insert into employee value('Rahul', 'Kumar', 25, 'M', '5999','98347000', 'Delhi');"

mycursor.execute(query)
mycursor.execute(query_1)

mycursor.execute("select * from employee;") 
myresult = mycursor.fetchall() 
for row in myresult: 
    print(row)
db.commit() 

# close the Connection
db.close()