Rusqlite insert multiple rows

Question

How can I insert multiple rows in a single query using rusqlite? Looking at the syntax below, I think it fails because the query expects there to be only one value in the Vec.

Can it be done with any kind of helper function, or would I have to create the query in its entirety?

    let mut names = Vec::new();
    names.push("Mittens");
    names.push("Jingle Paws");
    names.push("Sir Fluffy");
    match conn.execute(
        "INSERT INTO cats (name) VALUES (?1)",
        names,
    ) {
        Ok(_t) => (),
        Err(e) => {
            let gil = Python::acquire_gil();
            let py = gil.python();
            let error_message = format!("Error inserting cats into database! {}", e.to_string());
            PyIOError::new_err(error_message).restore(py);
            return Err(PyErr::fetch(py));
        }
    };

?

OSError: Error inserting cats into database! Wrong number of parameters passed to query. Got 2, needed 1

https://docs.rs/rusqlite/latest/rusqlite/struct.Batch.html – Dilawar Jul 26 '22 at 11:40 — Dilawar, Jul 26 '22 at 11:40

score 4 · Accepted Answer · answered Jan 19 '21 at 11:10

would I have to create the query in its entirety?

Reasonably sure it's this one.

As far as I can see, rusqlite doesn't provide any sort of batching API so you have two choices:

create a multi-VALUES query dynamically e.g.

let placeholders = names.iter().map(|_| "(?)").collect::<Vec<_>>().join(", ");
let q = format!("INSERT INTO cats (name) VALUES {}", placeholders);
... conn.execute(q, names) ...

prepare the query, then execute it in a loop (in a transaction)

The first would probably be the most efficient, but it's also gnarlier, somewhat less safe, and harder to debug.

Rusqlite insert multiple rows

1 Answers1