Python MySQL - Insert data in Table

In this tutorial, we will learn how to insert a single row and insert multiple rows of data in a MySQL table using Python.

To Insert data into a MySQL Table or to add data to the MySQL Table which we have created in our previous tutorial, We will use the INSERT SQL statement.

If you are new to SQL, you should first learn about the SQL INSERT statement.

Python MySQL - INSERT Data

Let us see the basic syntax to use INSERT INTO statement to insert data into our table:

INSERT INTO table_name (column_names) VALUES(data)

We can insert data in a table in two ways:

• Inserting a single row at a time

• Inserting multiple rows at a time

Inserting Single Row in MySQL Table

In this section, we will see the code example for inserting a single row of data in a MySQL table:

We will be adding data to the students table we created in Python MySQL - Create Table tutorial.

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "root",
    passwd = "himaniroot@99",
    database = "studytonight"
)

cursor = db.cursor()
## defining the Query
query ="INSERT INTO students(name, branch, address) VALUES (%s, %s,%s)"
## There is no need to insert the value of rollno 
## because in our table rollno is autoincremented #started from 1
## storing values in a variable
values = ("Sherlock", "CSE", "220 Baker Street, London")

## executing the query with values
cursor.execute(query, values)

## to make final output we have to run 
## the 'commit()' method of the database object
db.commit()

print(cursor.rowcount, "record inserted")

The output of the above code will be:

1 record inserted

Inserting Multiple Rows in MySQL Table

In this section, we will see the code example for inserting multiple rows of data in a MySQL table.

To insert multiple rows into the table, the executemany() method is used. It takes a list of tuples containing the data as a second parameter and the query as the first argument.

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "root",
    passwd = "himaniroot@99",
    database = "studytonight"
)

cursor = db.cursor()
## defining the Query
query ="INSERT INTO students(Name, Branch,Address) VALUES (%s, %s, %s)"

## storing values in a variable
values = [
    ("Peter", "ME","Noida"),
    ("Amy", "CE","New Delhi"),
    ("Michael", "CSE","London")
]

## executing the query with values
cursor.executemany(query, values)

## to make final output we have to run 
## the 'commit()' method of the database object
db.commit()

print(cursor.rowcount, "records inserted")

If all three rows were entered successfully, the output of the above code will be:

3 records inserted

So in this tutorial we learned how we can insert data into MySQL table in Python.