Python MySQL – Insert Data

We add data to a MySQL table with the SQL INSERT INTO command. This chapter uses the users table created in the previous chapter.

---

Insert one row

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="student"
)
cursor = conn.cursor()

sql = "INSERT INTO users (name, mobile, email) VALUES (%s, %s, %s)"
values = ("Robin", "9876543210", "robin@example.com")

cursor.execute(sql, values)
conn.commit()

print(cursor.rowcount, "record inserted")
print("Last id:", cursor.lastrowid)

cursor.close()
conn.close()

• %s is a placeholder. MySQL puts the real values in its place safely.
• conn.commit() is required. Without it the data is not saved in the table.
• cursor.rowcount tells how many rows were added.
• cursor.lastrowid is the auto increment id of the new row. It is 1 here, because the table was empty.

---

Insert many rows

Use executemany() with a list of tuples to add many rows with one command.

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="student"
)
cursor = conn.cursor()

sql = "INSERT INTO users (name, mobile, email) VALUES (%s, %s, %s)"
values = [
    ("Maria", "9123456780", "maria@example.com"),
    ("John", "9012345678", "john@example.com")
]

cursor.executemany(sql, values)
conn.commit()

print(cursor.rowcount, "records inserted")

cursor.close()
conn.close()

---

Summary

• Use INSERT INTO with %s placeholders and pass the values as a tuple.
• Call conn.commit() to save the changes.
• Use executemany() for many rows.
• Next: select data from the table.

• Previous Page
• Next Page

---

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.