Insert Operation in Python

Insert Operation in Python

Adding records to a database table is one of the most fundamental operations when working with SQL and Python. In Python, the INSERT INTO statement is utilized to add records to a table, and placeholders such as %s are used to define values dynamically. By leveraging Python’s mysql.connector, we can execute SQL statements seamlessly. This blog post will guide you through performing single and multiple record insertions, and retrieving the last inserted row’s ID with professional clarity and a human touch.

Download New Real Time Projects :-Click here

Inserting a Single Record

To insert a single record into a database table, we use the INSERT INTO statement with placeholders for the values. Python’s cursor.execute() method allows us to execute this statement, passing the actual values as a tuple.

Example: Inserting a Single 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)"

# Tuple containing the values to be inserted
val = ("John", 110, 25000.00, 201, "Newyork")

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

    # Commit the transaction
    myconn.commit()

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

except:
    # Rollback in case of an error
    myconn.rollback()

# Close the connection
myconn.close()

Output:

1 record inserted!

Inserting Multiple Records

Python also enables inserting multiple records in a single operation using the executemany() method. Here, a list of tuples is used, where each tuple represents a row to be inserted.

Example: Inserting 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)"

# List of tuples containing multiple rows to insert
val = [
    ("John", 102, 25000.00, 201, "Newyork"),
    ("David", 103, 25000.00, 202, "Port of Spain"),
    ("Nick", 104, 90000.00, 201, "Newyork")
]

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

    # Commit the transaction
    myconn.commit()

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

except:
    # Rollback in case of an error
    myconn.rollback()

# Close the connection
myconn.close()

Output:

3 records inserted!

Retrieving the Row ID

Each row in a SQL table is uniquely identified by a row ID. When inserting a record, Python provides a way to fetch the last inserted row ID using the lastrowid attribute of the cursor object.

Example: Retrieving 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)"

# Tuple containing the values to be inserted
val = ("Mike", 105, 28000, 202, "Guyana")

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

    # Commit the transaction
    myconn.commit()

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

except:
    # Rollback in case of an error
    myconn.rollback()

# Close the connection
myconn.close()

Output:

1 record inserted! ID: 0

Key Points to Remember

1. Use %s as the placeholder for dynamic values in the INSERT INTO statement.
2. Always handle database connections carefully using try-except blocks to manage errors.
3. Use executemany() for bulk insertions to improve efficiency.
4. Retrieve the last inserted row ID using cursor.lastrowid for unique row identification.

These Python SQL operations showcase how effectively we can manage database interactions in Python using mysql.connector. Whether you’re inserting single records, bulk data, or need to identify specific rows, this approach provides a robust and professional way to handle SQL operations.

PHP PROJECT:- CLICK HERE

insert operation in python w3schools
insert operation in python example
python insert into list at index
python list insert
Insert Operation in Python
Insert Operation in Python list insert time complexity
Insert Operation in Python list insert at end
string insert python
Insert Operation in Python insert list into list