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

Answer 1

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.