How to INSERT data into a MySQL database using Python

A guide on how to insert data into a MySQL database table using Python and the mysql-connector-python library.

This article guides you through connecting to a MySQL database and using Python to insert data into a table via an SQL INSERT statement.

import mysql.connector

# Connect to the MySQL database
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="test_db"
)

# Create a cursor object
cursor = conn.cursor()

# SQL INSERT statement
sql = "INSERT INTO students (name, age, grade) VALUES (%s, %s, %s)"
values = ("Nguyen Van A", 21, "A")

# Execute the INSERT statement
cursor.execute(sql, values)

# Commit changes to the database
conn.commit()

print(f"{cursor.rowcount} record inserted into the table.")

# Close the connection
cursor.close()
conn.close()

Detailed explanation:

1. import mysql.connector: Imports the mysql.connector library to allow MySQL connections.
2. conn = mysql.connector.connect(...): Connects to the MySQL database using connection details such as host, user, password, and database.
3. cursor = conn.cursor(): Creates a cursor object to execute SQL queries.
4. sql = "INSERT INTO students (name, age, grade) VALUES (%s, %s, %s)": Defines the SQL INSERT statement with placeholders %s.
5. values = ("Nguyen Van A", 21, "A"): Creates a tuple containing the values to be inserted into the table.
6. cursor.execute(sql, values): Executes the INSERT statement with the provided values.
7. conn.commit(): Saves changes to the database.
8. print(f"{cursor.rowcount} record inserted into the table."): Prints the number of records inserted.
9. cursor.close() and conn.close(): Closes the cursor and the connection to the database.

System Requirements:

• Python 3.x
• Library: mysql-connector-python

How to install the libraries needed to run the Python code above:

Use pip to install the library:

pip install mysql-connector-python

Tips:

• Ensure that your table structure and columns match the values you intend to insert.
• Always use conn.commit() after inserting data to ensure changes are saved to the database.