Dealing with multiple inserts

[saaqibz]

Hello, Is there a command like executemany() for dealing with multiple inserts?

[SRombauts]

Hi, you can simply exec multiple SQL statements separated by semicolon (;)

[saaqibz]

Thanks for your quick response. That is what I have been doing in the past but I wanted to see if I could do something more efficiently. I know a lot of other libraries had an executemany that tended to be more efficient. Here's one thing I noticed that I ended up doing. Maybe in the future an execute many can be added that utilizes this.

I ended up creating the prepared statement and reusing the same prepared statement resetting the bindings (I also added the wrapper for clearBindings to clear all existing bindings) and iterating just the bind portion.... Below is the section referencing this technique from the documentation for sqlite.

Thank you again for making this useful library.

3.0 Binding Parameters and Reusing Prepared Statements

In prior discussion, it was assumed that each SQL statement is prepared once, evaluated, then destroyed. However, the SQLite allows the same prepared statement to be evaluated multiple times. This is accomplished using the following routines:

sqlite3_reset()
sqlite3_bind()
After a prepared statement has been evaluated by one or more calls to sqlite3_step(), it can be reset in order to be evaluated again by a call to sqlite3_reset(). Using sqlite3_reset() on an existing prepared statement rather than creating a new prepared statement avoids unnecessary calls to sqlite3_prepare(). In many SQL statements, the time needed to run sqlite3_prepare() equals or exceeds the time needed by sqlite3_step(). So avoiding calls to sqlite3_prepare() can result in a significant performance improvement.

Usually, though, it is not useful to evaluate exactly the same SQL statement more than once. More often, one wants to evaluate similar statements. For example, you might want to evaluate an INSERT statement multiple times though with different values to insert. To accommodate this kind of flexibility, SQLite allows SQL statements to contain parameters which are "bound" to values prior to being evaluated. These values can later be changed and the same prepared statement can be evaluated a second time using the new values.

In SQLite, wherever it is valid to include a string literal, one can use a parameter in one of the following forms:

?
?NNN
:AAA
$AAA
@aaa
In the examples above, NNN is an integer value and AAA is an identifier. A parameter initially has a value of NULL. Prior to calling sqlite3_step() for the first time or immediately after sqlite3_reset(), the application can invoke one of the sqlite3_bind() interfaces to attach values to the parameters. Each call to sqlite3_bind() overrides prior bindings on the same parameter.

An application is allowed to prepare multiple SQL statements in advance and evaluate them as needed. There is no arbitrary limit to the number of outstanding prepared statements.

[SRombauts]

Okay, I see what you mean, like the 'executemany()' of the Python binding.

I think this could be handy, but this will be in fact less efficient than doing this by yourself (because you will have to prepare the data into an intermediate dynamically allocated list of arguments).

I reopen this request.

[SRombauts]

For future reference, link to the python documentation:

# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

[xsacha]

If you were to make this function, would it be useful to have a bool option to start transaction and end transaction around the execution?
Right now I have a function myself that does this once per second.

[SRombauts]

Hi @xsacha, I am not sure of what you mean, can you give a simple example?

[xsacha]

I just mean to have:

db->exec("BEGIN");
query->reset();
for (auto statement: collection)
{
// bind everything in statement
query->bind(..);
}
db->exec("END");

[SRombauts]

This as finally been implemented and integrated, thanks to @maxbachmann!