How to add records in Table using Python?

How to add records in Table using Python?

To add a record to a table in MySQL using Python, you use the INSERT INTO SQL statement. This statement specifies the table name, columns to insert data into, and the corresponding values.

Let's Assume we have table:

with this query:

create_table_query = """
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    salary FLOAT
);

i.e

Syntax to insert Data:

cursor.execute("INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)")

Where,

• table_name: Name of the table where data will be inserted.
• column1, column2, ...: The columns in which data will be inserted.
• value1, value2, ...: The values corresponding to the specified columns.

Example:

# Insert a single record into the 'employees' table
cursor.execute("INSERT INTO employees (name, age, salary) VALUES ('John Doe', 30, 45000.00)")
db.commit()  # Commit the transaction

---

 

Example with Variables:

name = "Jane Doe"
age = 28
salary = 52000.00

# Insert using placeholders
cursor.execute("INSERT INTO employees (name, age, salary) VALUES (%s, %s, %s)", (name, age, salary))
db.commit()  # Commit the transaction

Where,

• %s: Placeholders for dynamically adding values into the query. This helps prevent SQL injection attacks.

---

 

Bulk Insert Example:

# Insert multiple records at once
records = [
    ("Alice", 25, 40000.00),
    ("Bob", 35, 55000.00),
    ("Charlie", 29, 48000.00)
]

cursor.executemany("INSERT INTO employees (name, age, salary) VALUES (%s, %s, %s)", records)
db.commit()  # Commit all changes

This approach efficiently inserts multiple rows into the table in one command.

---

 

Complete Code:

import mysql.connector

db = mysql.connector.connect(
  host="localhost",  
  user="root",     
  password="1234", 
  database="eagleye"
)

cursor = db.cursor()

records = [
    ("Alice", 25, 40000.00),
    ("Bob", 35, 55000.00),
    ("Charlie", 29, 48000.00)
]

cursor.executemany("INSERT INTO employees (name, age, salary) VALUES (%s, %s, %s)", records)

# Commit the changes
db.commit()

# Close the connection
cursor.close()
db.close()

print("Data Added.")

Output:

Data Added.

---

 

Some interview questions, answers, and examples related to adding records to a MySQL table using Python

Question 1. How can you insert a single record into a MySQL table using Python?

Answer: You use the INSERT INTO statement along with cursor.execute() to add a single record. You must specify the table name, column names, and values in the query.

Example:

cursor.execute("INSERT INTO employees (name, age, salary) VALUES ('John Doe', 30, 45000.00)")
db.commit()

 

Question 2. How do you prevent SQL injection when inserting data into a MySQL table?

Answer: You can use placeholders (%s) with parameterized queries to prevent SQL injection. This method ensures that user input is properly escaped.

Example:

name = "Jane Doe"
age = 28
salary = 52000.00

cursor.execute("INSERT INTO employees (name, age, salary) VALUES (%s, %s, %s)", (name, age, salary))
db.commit()

 

Question 3. What is the syntax for inserting multiple rows into a MySQL table in Python?

Answer: You can use the executemany() method to insert multiple rows at once. It reduces the number of queries sent to the database, improving performance.

Example:

records = [
    ("Alice", 25, 40000.00),
    ("Bob", 35, 55000.00),
    ("Charlie", 29, 48000.00)
]

cursor.executemany("INSERT INTO employees (name, age, salary) VALUES (%s, %s, %s)", records)
db.commit()

 

Question 4. What happens if you try to insert a record with a missing value for a column?

Answer: If the column allows NULL values or has a default value, it will use that value. If not, an error will occur.

Example:

cursor.execute("INSERT INTO employees (name, age) VALUES ('Mike', 40)")
db.commit()

This works only if the salary column allows NULL or has a default value. Otherwise, an error is raised.

 

Question 5. How do you check the number of rows affected by an insert operation?

Answer: You can use the cursor.rowcount property to check how many rows were affected by the last query.

Example:

cursor.execute("INSERT INTO employees (name, age, salary) VALUES ('John', 32, 46000.00)")
print(f"Rows affected: {cursor.rowcount}")
db.commit()

 

Question 6. How do you handle errors during an insert operation in Python?

Answer: You can use a try-except block to catch errors. The mysql.connector.Error module provides detailed error messages.

Example:

from mysql.connector import Error

try:
    cursor.execute("INSERT INTO employees (name, age, salary) VALUES ('Anna', 27, 47000.00)")
    db.commit()
except Error as e:
    print(f"Error: {e}")

 

Question 7. How do you insert data into an AUTO_INCREMENT column?

Answer: You don’t need to specify a value for an AUTO_INCREMENT column. MySQL will automatically generate a unique value for that column.

Example:

cursor.execute("INSERT INTO employees (name, age, salary) VALUES ('Chris', 34, 49000.00)")
db.commit()

 

Question 8. What happens if you try to insert a duplicate value in a column with a UNIQUE constraint?

Answer: MySQL will throw an error if you try to insert a duplicate value in a column that has a UNIQUE constraint.

Example:

try:
    cursor.execute("INSERT INTO employees (id, name, age, salary) VALUES (1, 'Jane', 30, 45000.00)")
    db.commit()
except Error as e:
    print(f"Error: {e}")

If the id column has a UNIQUE constraint, this will fail if id=1 already exists.

 

Question 9. How do you insert data with only specific columns?

Answer: You can specify only the columns you want to insert data into in the INSERT INTO statement.

Example:

cursor.execute("INSERT INTO employees (name, age) VALUES ('Emma', 31)")
db.commit()

The salary column will either use its default value or remain NULL.

 

Question 10. How do you roll back an insert operation if something goes wrong?

Answer: You can use db.rollback() to undo changes if an error occurs before committing the transaction.

Example:

try:
    cursor.execute("INSERT INTO employees (name, age, salary) VALUES ('Sophia', 29, 50000.00)")
    raise Exception("Simulating an error!")  # Simulating an error
    db.commit()
except Exception as e:
    print(f"Error: {e}")
    db.rollback()  # Undo the insert operation