Python SQLite connection.executemany() Function

The Python SQLite connection.executemany() function executes a SQL statement multiple times with different sets of parameters.

A connection object is represented as an open connection to the database. We use this object to interact with different databases to execute SQL commands.

The executemany function is used to execute a different operation against all other parameters. This function is particularly useful for batch operations. We can execute SQL statements multiple times with different parameters.

Syntax

Following is the basic syntax for the connection.executemany() function.

connection.executemany(sql[, parameters])

Parameters

This function returns the SQL statements containing the string that will be executed.

Return Value

The connection.executemany() function returns the number of rows affected by the operation.

Example 1

This example inserts three records into the employees table and updates the table using the connection.executemany() function.

import sqlite3
conn sqlite3.connect('res.db')
conn.executemany('INSERT INTO employees VALUES (?, ?, ?, ?, ?, ?)', [(1, 'Ramesh', 32, 2000.00, 'Maryland', 'USA'), (2, 'Mukesh', 40, 5000.00, 'New York', 'USA'), (3, 'Sumit', 45, 4500.00, 'Muscat', 'Oman')])
conn.commit()

Output

The result is generated as follows −

(1, 'Ramesh', 32, 2000.0, 'Maryland', 'USA')
(2, 'Mukesh', 40, 5000.0, 'New York', 'USA')
(3, 'Sumit', 45, 4500.0, 'Muscat', 'Oman')

Example 2

In the below example, we are connecting to an SQLite database and using the connection.executemany() function to update the salary for multiple employees based on the ID of the table.

import sqlite3
conn = sqlite3.connect('res.db')
conn.executemany('UPDATE customers SET Salary = ? WHERE ID = ?', [(2220.00, 1), (5550, 2), (4440, 3)])
conn.commit()

Output

The output is obtained as follows −

(1, 'Ramesh', 32, 2220.0, 'Maryland', 'USA')
(2, 'Mukesh', 40, 5550.0, 'New York', 'USA')
(3, 'Sumit', 45, 4440.0, 'Muscat', 'Oman')

Example

Consider the following EMPLOYEES table, which stores employees ID, Name, Age, Salary, City and Country −

Example 3

Consider the above table, delete multiple rows from it using that connection.executemany() function.

import sqlite3
conn = sqlite3.connect('res.db')
conn.executemany('DELETE FROM customers WHERE ID = ?', [(4,), (5,), (6,)])
conn.commit()

Output

We will get the output as follows −

(1, 'Ramesh', 32, 2000.0, 'Maryland', 'USA')
(2, 'Mukesh', 40, 5000.0, 'New York', 'USA')
(3, 'Sumit', 45, 4500.0, 'Muscat', 'Oman')
(7, 'Ayush', 25, 3500.0, 'Delhi', 'India')

Example 4

Now, this program uses a dictionary instead of a tuple, this function throws a TypeError using the connection.executemany() function.

import sqlite3
conn = sqlite3.connect('res.db')
conn.executemany('INSERT INTO employees VALUES (?, ?, ?, ?, ?, ?)', {'ID': 1, 'Name': 'Ramesh'})

Output

When we run the above code, we will get the result as follows −

TypeError: parameters are of unsupported type.