HomeLanguagesInserting data into a new column of an already existing table in...

Inserting data into a new column of an already existing table in MySQL using Python

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 Lazyroar(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="Lazyroar"
)
  
# 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:

RELATED ARTICLES

3 COMMENTS

Most Popular

Dominic
32703 POSTS0 COMMENTS
Milvus
131 POSTS0 COMMENTS
Nango Kala
7076 POSTS0 COMMENTS
Nicole Veronica
12211 POSTS0 COMMENTS
Nokonwaba Nkukhwana
12303 POSTS0 COMMENTS
Shaida Kate Naidoo
7197 POSTS0 COMMENTS
Ted Musemwa
7457 POSTS0 COMMENTS
Thapelo Manthata
7174 POSTS0 COMMENTS
Umr Jansen
7161 POSTS0 COMMENTS