Inserting data into a new column of an already existing table in MySQL using Python (original) (raw)

Last Updated : 24 Feb, 2021

Prerequisite: Python: MySQL Create Table

In this article, we are going to see how to Inserting data into a new column of an already existing table 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 person ADD salary int(20); UPDATE persons SET salary = '145000' where Emp_Id=12;

• And print the result.

Before starting let do the same in SQL:

Step 1: Create a new column with alter command.

ALTER TABLE table_name ADD column_name datatype;

Step 2: Insert data in a new column.

Below is the full 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_1 = "ALTER TABLE person ADD salary int(20);" query_2 = "UPDATE persons SET salary = '145000' where Emp_Id=12;"

execute the queries

mycursor.execute(query_1) mycursor.execute(query_2)

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

db.commit()

close the Connection

db.close()

`

Output: