INSERT INTO statement is used to add new records to a MySQL table. In Python, you can execute INSERT queries using the mysql.connector module. After inserting data, you must call the commit() method to permanently save the changes to the database.
Syntax
The general syntax of the SQL INSERT INTO statement is:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
For parameterized queries in Python:
query = "INSERT INTO Student (Name, Course, Marks) VALUES (%s, %s, %s)"
values = ("Emma", "CSE", 92)cursor.execute(query, values)
connection.commit()
Sample Table
The following Student table is used in the examples below:
| Roll_No | Name | Course | Marks |
|---|---|---|---|
| 1 | John | CSE | 85 |
| 2 | Emma | IT | 72 |
| 3 | Michael | CSE | 91 |
| 4 | Sophia | ECE | 68 |
| 5 | Daniel | IT | 88 |
| 6 | Olivia | CSE | 79 |
Example 1: Insert a Single Record
The example below inserts a single student record into the Student table.
import mysql.connector
connection = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="college"
)
cursor = connection.cursor()
query = """
INSERT INTO Student (Name, Course, Marks)
VALUES (%s, %s, %s)
"""
values = ("James", "CSE", 90)
cursor.execute(query, values)
connection.commit()
print(cursor.rowcount, "record inserted.")
connection.close()
Output
1 record inserted.
Example 2: Insert Multiple Records
The example below inserts multiple student records using the executemany() method.
import mysql.connector
connection = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="college"
)
cursor = connection.cursor()
query = """
INSERT INTO Student (Name, Course, Marks)
VALUES (%s, %s, %s)
"""
students = [
("Charlotte", "IT", 87),
("Benjamin", "ECE", 81),
("Amelia", "CSE", 94)
]
cursor.executemany(query, students)
connection.commit()
print(cursor.rowcount, "records inserted.")
connection.close()
Output
3 records inserted.
Example 3: Insert User Input into the Table
The example below accepts values from the user and inserts them into the database.
import mysql.connector
connection = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="college"
)
cursor = connection.cursor()
name = input("Enter student name: ")
course = input("Enter course: ")
marks = int(input("Enter marks: "))
query = """
INSERT INTO Student (Name, Course, Marks)
VALUES (%s, %s, %s)
"""
cursor.execute(query, (name, course, marks))
connection.commit()
print("Record inserted successfully.")
connection.close()
Sample Input
Enter student name: Ethan
Enter course: IT
Enter marks: 89
Output
Record inserted successfully.
