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:
# Connect to the database
cnx = mysql.connector.connect(user='your_username', password='your_password',
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
# Commit the changes to the database
# Close the cursor and connection
In the example above, replace your_username, your_password, localhost, your_database, your_table, col1, col2, and col3 with the appropriate values for your MySQL database and table. Also, replace the data list with your own data.
Creating a new Table
query = """
CREATE TABLE your_table (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
Truncate a Table:
query = "TRUNCATE TABLE your_table"
Drop a Table:
query = "DROP TABLE your_table"
In next part, we will cover more advanced concepts.
Host your Django Application for free on PythonAnyWhere.
If you want complete control of your application and server, you should consider DigitalOcean. Create an account with this link and get $200 credits.