Python MySQL Insert Into Table

When working with databases in Python, one of the most common tasks is inserting data into a MySQL table. Whether you’re a beginner or an experienced developer, understanding how to efficiently perform these operations is crucial. In this guide, we’ll explore how to insert a single row, multiple rows, and retrieve the inserted ID in MySQL using Python. Let’s dive in!

1. Insert Into Table

Inserting data into a MySQL table using Python begins with establishing a connection to your database. Once connected, you can execute an INSERT statement to add new data.

Example:

import mysql.connector

# Establish connection to MySQL database
conn = mysql.connector.connect(
    host="your_host",
    user="your_username",
    password="your_password",
    database="your_database"
)
cursor = conn.cursor()

# SQL query to insert data
insert_query = "INSERT INTO your_table (column1, column2) VALUES (%s, %s)"
data = ("value1", "value2")

# Executing the query
cursor.execute(insert_query, data)

# Committing the changes
conn.commit()

print("Data inserted successfully.")

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

2. Insert Multiple Rows

To insert multiple rows at once, you can use the executemany() method. This is not only efficient but also helps in reducing the number of server trips.

Example:

# Data to be inserted
data = [
    ("value1", "value2"),
    ("value3", "value4"),
    ("value5", "value6")
]

# Executing the query for multiple rows
cursor.executemany(insert_query, data)

# Commit and close as before
conn.commit()
print("Multiple rows inserted successfully.")

3. Get Inserted ID

After inserting a row into a MySQL table, you might want to get the ID of the inserted row. This is particularly useful for tables with auto-increment primary keys.

Example:

# Insert data as before
cursor.execute(insert_query, data)

# Fetching the last inserted id
inserted_id = cursor.lastrowid

print("Inserted row ID:", inserted_id)

# Commit and close as usual
conn.commit()

Conclusion

Incorporating these methods into your Python applications allows you to interact with MySQL databases efficiently. Practice these techniques to enhance your database management skills. Remember, the key to mastering database operations is consistent practice and exploration.

Stay tuned for more Python and MySQL tips and tricks!