Python MySQL: Insert Into Table

Introduction

Inserting data into a MySQL table is a fundamental task when working with databases. Python, with its extensive library support, makes it easy to interact with MySQL databases. In this guide, we will use the mysql-connector-python library to insert data into a MySQL table.

Setting Up

Install MySQL Connector

First, you need to install the MySQL connector for Python. You can install it using pip:

pip install mysql-connector-python

Connecting to MySQL

To insert data into a table, you need to connect to the MySQL server and the specific database where the table is located. You will need the following details:

Hostname (usually localhost)
Username
Password
Database name

Example: Connecting to MySQL

import mysql.connector

# Connect to the MySQL server and database
connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

if connection.is_connected():
    print("Connected to MySQL database")

# Close the connection
connection.close()

Inserting Data

Once you have connected to the MySQL database, you can insert data into a table by executing an SQL INSERT statement using a cursor object.

Example: Inserting Data

import mysql.connector

# Connect to the MySQL server and database
connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

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

# Insert data into the table
insert_query = """
INSERT INTO employees (name, age, gender, position)
VALUES (%s, %s, %s, %s)
"""

data = ("John Doe", 28, "Male", "Software Engineer")
cursor.execute(insert_query, data)

# Commit the transaction
connection.commit()
print("Data inserted successfully")

# Close the connection
connection.close()

Inserting Multiple Rows

You can insert multiple rows of data at once by using the executemany() method.

Example: Inserting Multiple Rows

import mysql.connector

# Connect to the MySQL server and database
connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

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

# Insert multiple rows into the table
insert_query = """
INSERT INTO employees (name, age, gender, position)
VALUES (%s, %s, %s, %s)
"""

data = [
    ("John Doe", 28, "Male", "Software Engineer"),
    ("Jane Smith", 32, "Female", "Project Manager"),
    ("Mike Johnson", 45, "Male", "CTO")
]

cursor.executemany(insert_query, data)

# Commit the transaction
connection.commit()
print("Multiple rows inserted successfully")

# Close the connection
connection.close()

Handling Exceptions

It’s important to handle exceptions that might occur during the database operations to ensure that your program can handle errors gracefully.

Example: Handling Exceptions

import mysql.connector
from mysql.connector import Error

try:
    # Connect to the MySQL server and database
    connection = mysql.connector.connect(
        host="localhost",
        user="your_username",
        password="your_password",
        database="your_database"
    )

    if connection.is_connected():
        print("Connected to MySQL database")

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

    # Insert data into the table
    insert_query = """
    INSERT INTO employees (name, age, gender, position)
    VALUES (%s, %s, %s, %s)
    """

    data = ("John Doe", 28, "Male", "Software Engineer")
    cursor.execute(insert_query, data)

    # Commit the transaction
    connection.commit()
    print("Data inserted successfully")

except Error as e:
    print(f"Error: {e}")

finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Complete Example

Here is a complete example that includes connecting to the MySQL server, inserting data into a table, inserting multiple rows, and handling exceptions.

import mysql.connector
from mysql.connector import Error

# Database connection details
host = "localhost"
user = "your_username"
password = "your_password"
database = "your_database"

try:
    # Connect to the MySQL server and database
    connection = mysql.connector.connect(
        host=host,
        user=user,
        password=password,
        database=database
    )

    if connection.is_connected():
        print("Connected to MySQL database")

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

    # Insert data into the table
    insert_query = """
    INSERT INTO employees (name, age, gender, position)
    VALUES (%s, %s, %s, %s)
    """

    data = ("John Doe", 28, "Male", "Software Engineer")
    cursor.execute(insert_query, data)
    connection.commit()
    print("Data inserted successfully")

    # Insert multiple rows into the table
    data = [
        ("Jane Smith", 32, "Female", "Project Manager"),
        ("Mike Johnson", 45, "Male", "CTO"),
        ("Emma Davis", 29, "Female", "Data Scientist")
    ]

    cursor.executemany(insert_query, data)
    connection.commit()
    print("Multiple rows inserted successfully")

except Error as e:
    print(f"Error: {e}")

finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Conclusion

Inserting data into a MySQL table using Python is straightforward with the mysql-connector-python library. By following the steps outlined above, you can easily connect to a MySQL database, insert single and multiple rows of data, and handle exceptions effectively. This provides a solid foundation for managing your data programmatically using Python.