sqlite - 如何在 rusqlite 中取回一行数据？

Question

• rustc 1.38.0 (625451e37 2019-09-23)
• rusqlite 0.20.0

我正在编写一个程序，我需要从 sqlite 刚刚创建的最后一次插入中取回 id。

db.execute("insert into short_names (short_name) values (?1)",params![short]).expect("db insert fail");

let id = db.execute("SELECT id FROM short_names WHERE short_name = '?1';",params![&short]).query(NO_PARAMS).expect("get record id fail");

let receiver = db.prepare("SELECT id FROM short_names WHERE short_name = "+short+";").expect("");
let id = receiver.query(NO_PARAMS).expect("");
println!("{:?}",id);

我应该返回的是 sqlite 使用 AUTOINCREMENT 自动分配的 id 值。

我遇到了这个编译器错误:

error[E0599]: no method named `query` found for type `std::result::Result<usize, rusqlite::Error>` in the current scope
  --> src/main.rs:91:100
   |
91 |         let id = db.execute("SELECT id FROM short_names WHERE short_name = '?1';",params![&short]).query(NO_PARAMS).expect("get record id fail");
   |                                                                                                    ^^^^^

error[E0369]: binary operation `+` cannot be applied to type `&str`
  --> src/main.rs:94:83
   |
94 |         let receiver = db.prepare("SELECT id FROM short_names WHERE short_name = "+short+";").expect("");
   |                                   ------------------------------------------------^----- std::string::String
   |                                   |                                               |
   |                                   |                                               `+` cannot be used to concatenate a `&str` with a `String`
   |                                   &str
help: `to_owned()` can be used to create an owned `String` from a string reference. String concatenation appends the string on the right to the string on the left and may require reallocation. This requires ownership of the string on the left
   |
94 |         let receiver = db.prepare("SELECT id FROM short_names WHERE short_name = ".to_owned()+&short+";").expect("");
   |                                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^

error[E0277]: `rusqlite::Rows<'_>` doesn't implement `std::fmt::Debug`
  --> src/main.rs:96:25
   |
96 |         println!("{:?}",id);
   |                         ^^ `rusqlite::Rows<'_>` cannot be formatted using `{:?}` because it doesn't implement `std::fmt::Debug`
   |
   = help: the trait `std::fmt::Debug` is not implemented for `rusqlite::Rows<'_>`
   = note: required by `std::fmt::Debug::fmt`

第 94 行:我知道 rust 的 String 不是 execute 调用的正确类型，但我不确定该怎么做。

我怀疑需要发生的是 short_names 表需要从数据库中提取，然后从表的 Rust 表示中获取匹配的 id short 我正在尝试使用。 I've been going off this example as a jumping off point, but It's dereferenced it's usefulness.我正在编写的程序调用另一个程序，然后在另一个程序运行时照看它。为了减少开销，我尝试不对当前程序使用 OOP。

我应该如何构造我对数据库的请求以获取我需要的 id？

Answer 1

好的。首先，我们将使用一个struct，因为与Java不同，在这种情况下它实际上等同于不使用一个结构，除了你能够保持事物整洁。

您正在尝试模拟 Connection::last_insert_rowid() ，这不是一件非常聪明的事情，特别是如果你不在交易中。我们还将以一种简洁明了的方式为您解决这个问题:

use rusqlite::{Connection};

pub struct ShortName {
    pub id: i64,
    pub name: String
}

pub fn insert_shortname(db: &Connection, name: &str) -> Result<ShortName, rusqlite::Error> {
    let mut rtn = ShortName {
        id: 0,
        name: name.to_string()
    };
    db.execute("insert into short_names (short_name) values (?)",&[name])?;
    rtn.id = db.last_insert_rowid();
    Ok(rtn)
}

您可以说服自己它适用于此测试:

#[test]
fn it_works() {
    let conn = Connection::open_in_memory().expect("Could not test: DB not created");
    let input:Vec<bool> = vec![];
    conn.execute("CREATE TABLE short_names (id INTEGER PRIMARY KEY AUTOINCREMENT, short_name TEXT NOT NULL)", input).expect("Creation failure");
    let output = insert_shortname(&conn, "Fred").expect("Insert failure");
    assert_eq!(output.id, 1);
}