Inserting records into a database table using Python involves using SQL commands with the mysql-connector library. Here’s how you can perform basic insert operations, including inserting single and multiple rows, and retrieving the last inserted row ID.
To insert a single record into a table, use the INSERT INTO SQL statement. In Python, you can use the execute() method of the cursor object to run this statement.
import mysql.connector
# Create a connection object
myconn = mysql.connector.connect(
host="localhost",
user="root",
passwd="your_password",
database="your_database"
)
# Create a cursor object
cur = myconn.cursor()
# Define the SQL query with placeholders
sql = "INSERT INTO Employee (name, id, salary, department_id) VALUES (%s, %s, %s, %s)"
# Define the values to be inserted
values = ("John Doe", 1, 50000, 101)
# Execute the SQL query
cur.execute(sql, values)
# Commit the changes
myconn.commit()
# Print the number of rows affected
print(f"Record inserted, ID: {cur.lastrowid}")
# Close the cursor and connection
cur.close()
myconn.close()
To insert multiple rows at once, use the executemany() method of the cursor object. Each row is represented as a tuple within a list, and each tuple corresponds to one row's values.
import mysql.connector
# Create a connection object
myconn = mysql.connector.connect(
host="localhost",
user="root",
passwd="your_password",
database="your_database"
)
# Create a cursor object
cur = myconn.cursor()
# Define the SQL query with placeholders
sql = "INSERT INTO Employee (name, id, salary, department_id) VALUES (%s, %s, %s, %s)"
# Define the list of values to be inserted
values = [
("Alice Smith", 2, 55000, 102),
("Bob Johnson", 3, 60000, 103),
("Charlie Brown", 4, 65000, 104)
]
# Execute the SQL query for multiple rows
cur.executemany(sql, values)
# Commit the changes
myconn.commit()
# Print the number of rows affected
print(f"{cur.rowcount} records inserted.")
# Close the cursor and connection
cur.close()
myconn.close()
After inserting a record, you may want to retrieve the ID of the last inserted row. This is useful, for example, when using auto-incremented primary keys.
import mysql.connector
# Create a connection object
myconn = mysql.connector.connect(
host="localhost",
user="root",
passwd="your_password",
database="your_database"
)
# Create a cursor object
cur = myconn.cursor()
# Define the SQL query with placeholders
sql = "INSERT INTO Employee (name, id, salary, department_id) VALUES (%s, %s, %s, %s)"
# Define the values to be inserted
values = ("Jane Doe", None, 70000, 105) # Assuming 'id' is auto-incremented
# Execute the SQL query
cur.execute(sql, values)
# Commit the changes
myconn.commit()
# Get the last inserted row ID
print(f"Last inserted row ID: {cur.lastrowid}")
# Close the cursor and connection
cur.close()
myconn.close()
• Single Record Insertion: Use the execute() method with SQL INSERT INTO and pass values as a tuple.
• Multiple Records Insertion: Use the executemany() method with SQL INSERT INTO and pass values as a list of tuples.
• Retrieve Last Inserted ID: Use the lastrowid attribute of the cursor object to get the ID of the last inserted record.
These examples show how to perform basic insert operations and retrieve the last inserted row ID using Python and MySQL.