Python SQLite cursor.executemany() Function
The Python cursor.executemany() function executes a database operation multiple times with different parameters. This is efficient for inserting or updating the database.
A cursor is an object that is used to interact with the database. This allows executing SQL queries, retrieving data, and managing the data from the retrieved operation. We are creating a cursor using connection.cursor() and close it with the cursor.close() function.
Execute runs a single SQL query, that is used for SQL operations like INSERT, UPDATE, or DELETE.
Syntax
Following is the syntax for the cursor.executemany() function.
cursor.executemany(sql, seq_of_parameters)
Parameters
Each parameter sequence contains the values for a single execution of the SQL query.
Return Value
This function returns multiple operations with different parameters.
Example
Consider the following EMPLOYEES table, which stores employees ID, Name, Age, Salary, City and Country −
| ID | Name | Age | Salary | City | Country |
|---|---|---|---|---|---|
| 1 | Ramesh | 32 | 2000.00 | Maryland | USA |
| 2 | Mukesh | 40 | 5000.00 | New York | USA |
| 3 | Sumit | 45 | 4500.00 | Muscat | Oman |
| 4 | Kaushik | 25 | 2500.00 | Kolkata | India |
| 5 | Hardik | 29 | 3500.00 | Bhopal | India |
| 6 | Komal | 38 | 3500.00 | Saharanpur | India |
| 7 | Ayush | 25 | 3500.00 | Delhi | India |
Example 1
This program inserts two rows into the employee table using cursor.executemany() function.
data = [(1, 'Ramesh', 32, 2000.00, 'Maryland', 'USA'),(2, 'Mukesh', 40, 5000.00, 'New York', 'USA')]
cursor.executemany("INSERT INTO employees(ID, Name, Age, Salary, City, Country) Values(?, ?, ?, ?, ?, ?)", data)
conn.commit()
Output
The result is generated as follows −
(1, 'Ramesh', 32, 2000.00, 'Maryland', 'USA') (2, 'Mukesh', 40, 5000.00, 'New York', 'USA')
Example 2
The following program updates the salaries of employees with IDs 1 and 2 to 3000.00 and 5000.00 using cursor.executemany() function.
updates = [(3000.00, 1), (5500.00, 2)]
cursor.executemany("UPDATE employees SET Salary = ? WHERE ID = ?", updates)
conn.commit()
Output
When we run the above code we will get the output as follows −
(1, 'Ramesh', 32, 3000.00, 'Maryland', 'USA') (2, 'Mukesh', 40, 5500.00, 'New York', 'USA')
Example 3
In the below example, we are deleting employees with IDs 1 and 2 from the employees table using cursor.executemany() function.
ids_to_delete = [(1), (2)]
cursor.executemany("Delete From employees WHere ID = ?", ids_to_delete)
conn.commit()
Output
The output is obtained as follows −
(3, 'Sumit', 45, 4500.00, 'Muscat', 'Oman') (4, 'Kaushik', 25, 2500.00, 'Kolkata', 'India') (5, 'Hardik', 29, 3500.00, 'Bhopal', 'India') (6, 'Komal', 38, 3500.00, 'Saharanpur', 'India') (7, 'Ayush', 25, 3500.00, 'Delhi', 'India')
Example 4
When we insert a string instead of a tuple in the dataset using the cursor.executemany() function, it throws a TypeError.
data = [(1, 'Ramesh', 32, 2000.00, 'Maryland', 'USA'), 'Sanjay']
cursor.executemany("INSERT INTO employees (ID, Name, Age, Salary, City, Country) VALUES(?, ?, ?, ?, ?, ?)", data)
conn.commit()
Output
We will get the output as follows −
TypeError: parameters are of unsupported type