Python SQLite cursor.executemany() Function

The Pythoncursor.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 usingconnection.cursor() and close it with thecursor.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 thecursor.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 −

Example 1

This program inserts two rows into the employee table usingcursor.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 usingcursor.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 usingcursor.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 thecursor.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