How to Use Python MySQL Insert Query (With Example)

If you're building a Python-based application that interacts with a database, inserting data into MySQL is one of the most important tasks. In this article, we’ll walk through how to insert records into a MySQL table using Python.

Prerequisites

Before you can insert data into MySQL using Python, ensure you have:

• Python installed (preferably version 3+)
• MySQL server installed and running
• MySQL Connector installed (Install via pip: pip install mysql-connector-python)

Step 1: Import MySQL Connector

import mysql.connector

Step 2: Connect to MySQL Database

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

Replace the placeholders with your actual database credentials.

Step 3: Prepare a Cursor Object

mycursor = mydb.cursor()

Step 4: Write and Execute the Insert Query

sql = "INSERT INTO students (name, age, city) VALUES (%s, %s, %s)"
val = ("John Doe", 22, "New York")
mycursor.execute(sql, val)

This will insert one row into the students table.

Step 5: Commit the Transaction

mydb.commit()

This step is important! Without committing, the data won't be saved to your database.

Step 6: Print the Inserted Record Info

print(mycursor.rowcount, "record inserted.")

Full Working Example

import mysql.connector

# Establish connection
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="school"
)

# Create cursor
mycursor = mydb.cursor()

# Insert query
sql = "INSERT INTO students (name, age, city) VALUES (%s, %s, %s)"
val = ("Alice Smith", 21, "Los Angeles")

# Execute and commit
mycursor.execute(sql, val)
mydb.commit()

print(mycursor.rowcount, "record inserted.")

Tips

• Use parameterized queries (%s) to avoid SQL injection.
• Always commit after executing insert queries.
• Handle exceptions using try-except blocks to manage errors gracefully.