In part 1 we covered basic tasks like connecting with MySQL using mysql connector package, inserting, updating and deleting the records from table. Let's jump onto the next level.

Batch insert into table - Inserting multiple records in one query

To insert a batch of data into a MySQL database using Python3, you can use the following steps:

- Connect to the database using the mysql-connector-python module.

- Define the SQL query to insert the data into the table.

- Create a list of tuples that contains the data to be inserted.

- Use the executemany() method of the cursor object to execute the query with the list of tuples as its parameter.

- Commit the changes to the database using the commit() method of the connection object.

Here is an example code snippet that demonstrates how to insert a batch of data into a MySQL database using Python3:

import mysql.connector

# Connect to the database
cnx = mysql.connector.connect(user='your_username', password='your_password',
                              host='localhost', database='your_database')
cursor = cnx.cursor()

# Define the SQL query to insert data
query = "INSERT INTO your_table (col1, col2, col3) VALUES (%s, %s, %s)"

# Create a list of tuples containing the data to be inserted
data = [
  ('value1', 'value2', 'value3'),
  ('value4', 'value5', 'value6'),
  ('value7', 'value8', 'value9')
]

# Execute the query with the list of tuples as its parameter
cursor.executemany(query, data)

# Commit the changes to the database
cnx.commit()

# Close the cursor and connection
cursor.close()
cnx.close()

# Define the SQL query to create the table
query = """
CREATE TABLE your_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  age INT
)
"""

# Execute the query
cursor.execute(query)

# Commit the changes to the database
cnx.commit()

# Define the SQL query to truncate the table
query = "TRUNCATE TABLE your_table"

# Execute the query
cursor.execute(query)

# Commit the changes to the database
cnx.commit()

# Define the SQL query to drop the table
query = "DROP TABLE your_table"

# Execute the query
cursor.execute(query)

# Commit the changes to the database
cnx.commit()