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 neveropen(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 = "neveropen" ) # 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() |
Output:
Let’s check the table is contains your updated data or not:
