Insert Data in Table

Welcome to another tutorial on Python MySQL. Here you will learn how to insert a single row and insert multiple rows of data in a MySQL table with Python.

Now, we will Insert data into a MySQL Table and possibly add data to the MySQL table that was created in our previous tutorial. We will use the INSERT SQL statement.

Please, if you are a novice, you have to learn about the SQL Insert statement.

Python MySQL - INSERT Data

The basic syntax to use the INSERT INTO statement to insert data into our table is shown below:

INSERT INTO table_name (column_names) VALUES(data)

Data can be inserted into the table in two ways.

By inserting a single row at a time
By inserting multiple rows at a time

Inserting Single Row in MySQL Table

To insert a single row in MySQL, we will be adding data to the student's table we created in our previous tutorial on Python MySQL – Create Table.

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database="mydatabase1"
)

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 = ("Mike", "Computer Science", "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")

Output:

1 record inserted

Inserting Multiple Rows in MySQL Table

Here will learn the code for inserting multiple rows of data in a MySQL table.

Now, for us to insert multiple rows into the table, the method executemany() is used. Also, it takes a list of tuples containing the data as a second parameter and the query as the initial or first argument.

import mysql.connector as mysql

db = mysql.connect(
     host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database="mydatabase1"
)

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

## storing values in a variable
values = [
    ("Mike", "Computer Science","London"),
    ("Ads", "Computer Science","Hong Kong"),
    ("Michael", "Computer Science","USA")
]

## 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")

Output:

3 records inserted