Specifies a SQL statement to execute multiple times, based on a list of argument values.
Specify a SQL statement to execute multiple times, based on a list of argument values. Each element in the arg list corresponds to an arg list that would be passed to execute(), but not only saves you from having to loop over multiple execute() calls but can also take advantage of the server's support for multiple argument execution that will speed up the requests.
The executemany() call only supports INSERT, UPDATE, and DELETE statements.
Given the following "sample" table in database "test":
CREATE TABLE sample ( num INTEGER, str VARCHAR(64), ts DATE );
The following example takes a list of 3 rows and INSERTs all the data into the sample table:
import mariadb conn = mariadb.connect( host = 'localhost', user = 'root', password = 'secret', database = 'test', ) cursor = conn.cursor() # The values must always be a list of tuples. values = [ (1, "A 'string' with single quotes.", '2020-01-01'), (2, 'A "string" with double quotes.', '2020-01-02'), (3, "Another simple string value...", '2020-01-03'), ] cursor.executemany("INSERT INTO sample VALUES (?, ?, ?)", values) cursor.execute("SELECT * FROM sample") # This will output all the column names for the above SELECT: print([x for x in cursor.description]) # ... and then output all the rows (as tuples): for row in cursor: print(row) conn.close()
The output when starting with an empty table would look like this:
['num', 'str', 'ts'] (1, "A 'string' with single quotes.", datetime.date(2020, 1, 1)) (2, 'A "string" with double quotes.', datetime.date(2020, 1, 2)) (3, 'Another simple string value...', datetime.date(2020, 1, 3))