Insert Operation in Python with MySQL

Insert Operation in Python

Storing new information in a database is an essential part of almost every database-driven application. In MySQL, new records are added with the INSERT INTO statement. Python can execute these SQL commands through the mysql.connector library, making it possible to insert individual records as well as multiple records programmatically.

In this tutorial, we will explore the Insert Operation in Python with MySQL. You will learn how to add a single record, insert several records together, and retrieve the last inserted row ID using Python.

Inserting a Single Record

To add one record to a MySQL table, the INSERT INTO statement is used. Instead of directly placing values inside the SQL query, Python uses %s placeholders. The actual values are supplied separately through a tuple.

Example: Insert One Record

import mysql.connector

# Create the connection object
myconn = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="google",
    database="PythonDB"
)

# Create the cursor object
cur = myconn.cursor()

# SQL query with placeholders
sql = "INSERT INTO Employee(name, id, salary, dept_id, branch_name) VALUES (%s, %s, %s, %s, %s)"

# Values for the new record
val = ("John", 110, 25000.00, 201, "Newyork")

try:
    # Execute the insert query
    cur.execute(sql, val)

    # Save the transaction
    myconn.commit()

    print(cur.rowcount, "record inserted!")

except:
    # Undo changes if an error occurs
    myconn.rollback()

# Close the connection
myconn.close()

Output

1 record inserted!

In this example, one employee record is added to the Employee table. The commit() call makes the database change permanent after the query executes successfully.

Inserting Multiple Records

When several rows need to be stored, executing a separate query for every record is unnecessary. Python’s executemany() method allows multiple rows to be inserted using one SQL statement.

The values are supplied as a list of tuples. Each tuple represents one record that will be added to the table.

Example: Insert Multiple Records

import mysql.connector

# Create the connection object
myconn = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="google",
    database="PythonDB"
)

# Create the cursor object
cur = myconn.cursor()

# SQL query with placeholders
sql = "INSERT INTO Employee(name, id, salary, dept_id, branch_name) VALUES (%s, %s, %s, %s, %s)"

# Multiple records
val = [
    ("John", 102, 25000.00, 201, "Newyork"),
    ("David", 103, 25000.00, 202, "Port of Spain"),
    ("Nick", 104, 90000.00, 201, "Newyork")
]

try:
    # Insert all records
    cur.executemany(sql, val)

    # Save the changes
    myconn.commit()

    print(cur.rowcount, "records inserted!")

except:
    # Undo the transaction when an error occurs
    myconn.rollback()

# Close the connection
myconn.close()

Output

3 records inserted!

Here, three employee records are passed to executemany(). This approach is convenient when the application has several rows ready to be stored in the database.

Retrieving the Last Inserted Row ID

After inserting a record, an application may need to know the identifier associated with that newly added row. The cursor object provides the lastrowid attribute for retrieving the last inserted row ID returned by the database operation.

Example: Get the Last Inserted Row ID

import mysql.connector

# Create the connection object
myconn = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="google",
    database="PythonDB"
)

# Create the cursor object
cur = myconn.cursor()

# SQL query with placeholders
sql = "INSERT INTO Employee(name, id, salary, dept_id, branch_name) VALUES (%s, %s, %s, %s, %s)"

# Record to insert
val = ("Mike", 105, 28000, 202, "Guyana")

try:
    # Insert the record
    cur.execute(sql, val)

    # Commit the transaction
    myconn.commit()

    # Display row count and last inserted ID
    print(cur.rowcount, "record inserted! ID:", cur.lastrowid)

except:
    # Rollback if insertion fails
    myconn.rollback()

# Close the connection
myconn.close()

Output

1 record inserted! ID: 0

The lastrowid attribute is accessed through the cursor after the insertion. The exact value returned depends on how the table’s identifier is configured and how the database handles the inserted record.

Important Points to Remember

• Use placeholders: Use %s when passing dynamic values to the SQL statement.
• Commit successful changes: Call commit() after a successful insertion.
• Handle errors: Use exception handling so database problems can be managed safely.
• Rollback failed operations: Use rollback() when an insertion cannot be completed successfully.
• Use executemany(): This method is suitable when multiple rows need to be inserted.
• Close the connection: Release the database connection after completing the operation.
• Use lastrowid when needed: The cursor’s lastrowid attribute can be used to access the last inserted row identifier.

Conclusion

The Insert Operation in Python with MySQL is an important database concept for applications that need to store new information. Using mysql.connector, Python can connect to a MySQL database and execute INSERT INTO statements efficiently.

For individual records, execute() can be used with a tuple of values, while executemany() makes it convenient to insert multiple records. Python also provides lastrowid for accessing the identifier associated with the latest insertion. Proper use of commit(), rollback(), exception handling, and connection closing helps keep database operations organized.

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here
YT:- DecodeIT

Frequently Asked Questions (FAQ)

1. What is the INSERT operation in Python?

The INSERT operation is used to add new records to a database table. In Python, an SQL INSERT INTO query can be executed through a database connector such as mysql.connector.

2. Which SQL statement is used to insert records?

The INSERT INTO statement is used to add new rows to a MySQL table.

3. What is the purpose of %s in a Python MySQL query?

%s acts as a placeholder for values that are supplied separately when the cursor executes the query.

4. How can multiple records be inserted in Python?

The executemany() method can be used to insert multiple records with one SQL statement. The values are generally provided as a list of tuples.

5. Why is commit() used after INSERT?

The commit() method saves the changes made during the database transaction so that the successful insertion is applied to the database.

6. What does rollback() do?

rollback() reverses pending transaction changes when an error occurs, allowing the database operation to be cancelled.

7. What is cursor.lastrowid?

lastrowid is a cursor attribute that can provide the identifier associated with the most recent inserted row, depending on the database table configuration.

8. Why should the MySQL connection be closed?

Closing the connection after completing the database operation releases the resources associated with the connection.

Keywords: Insert Operation in Python, Insert Operation in Python with MySQL, Python MySQL insert, insert operation in python example, Python MySQL INSERT INTO, mysql.connector insert Python, Python insert database record, Python MySQL executemany, Python MySQL lastrowid, Python MySQL database operations, insert multiple records Python MySQL, MySQL connector Python example, Python CRUD operations MySQL, Python database tutorial, insert query in Python