python-mysqlHow do I execute a batch insert into a MySQL database using Python?
To execute a batch insert into a MySQL database using Python, you need to do the following:
- Establish a connection to the database:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword"
)
mycursor = mydb.cursor()
- Create a list of values to insert:
sql_values = [
('John', 'Highway 21'),
('Peter', 'Lowstreet 4'),
('Amy', 'Apple st 652'),
('Hannah', 'Mountain 21'),
('Michael', 'Valley 345'),
('Sandy', 'Ocean blvd 2'),
('Betty', 'Green Grass 1'),
('Richard', 'Sky st 331'),
('Susan', 'One way 98'),
('Vicky', 'Yellow Garden 2'),
('Ben', 'Park Lane 38'),
('William', 'Central st 954'),
('Chuck', 'Main Road 989'),
('Viola', 'Sideway 1633')
]
- Create the SQL query:
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
- Execute the query using the
executemany()
method:
mycursor.executemany(sql, sql_values)
mydb.commit()
print(mycursor.rowcount, "record inserted.")
Output example
14 records inserted.
- Close the connection:
mycursor.close()
mydb.close()
Helpful links
More of Python Mysql
- How can I use Python to retrieve data from MySQL?
- How can I connect Python to a MySQL database?
- How do I use Python to query MySQL with multiple conditions?
- How can I use Python to interact with a MySQL database using YAML?
- How do I use Python to authenticate MySQL on Windows?
- How can I troubleshoot a Python MySQL OperationalError?
- How do I connect to a MySQL database using XAMPP and Python?
- How can I resolve the "no database selected" error when using Python and MySQL?
- How do I use Python to access MySQL binlogs?
- How do Python MySQL and SQLite compare in terms of performance and scalability?
See more codes...