rust - 我们如何在 rusqlite 中使用带有外部 WHERE 参数的 SELECT 查询？

Question

我需要使用 SELECT 和 WHERE 从数据库中获取一行，我需要根据年龄获取一行，我使用其他教程尝试了这种方式。

use rusqlite::{params, Connection, Result};

#[derive(Debug)]
struct Person {
    id: i32,
    name: String,
    age: u8,
    data: String,
}

fn main() -> Result<()> {
    let conn = Connection::open_in_memory()?;

    conn.execute(
        "CREATE TABLE person (
                  id              INTEGER PRIMARY KEY,
                  name            TEXT NOT NULL,
                  age           INTEGER,
                  data            TEXT
                  )",
        [],
    )?;
    let me = Person {
        id: 0,
        name: "Steven".to_string(),
        age:1,
        data: "data1".to_string(),
    };
    conn.execute(
        "INSERT INTO person (name, age, data) VALUES (?1, ?2, ?3)",
        params![me.name, me.age, me.data],
    )?;

    conn.execute(
        "INSERT INTO person (name, age, data) VALUES (?1, ?2, ?3)",
        params!["john".to_string(), 2, "data2".to_string()],
    )?;
    
    let age:u8 = 1;
    
    let mut stmt = conn.prepare("SELECT id, name, age, data FROM person WHERE age=:age;")?;
    let person_iter = stmt.query_map([], |row| {
        Ok(Person {
            id: row.get(0)?,
            name: row.get(1)?,
            age: row.get(2)?,
            data: row.get(3)?,
        })
    })?;

    for person in person_iter {
        println!("Found person {:?}", person);
    }

    Ok(())
}

我们如何使用带有外部 WHERE 参数的 SELECT 查询？

Answer 1

您可以使用 params的 query_map()为此:

stmt.query_map(&[(":age", age.to_string().as_str())], |row| { ... }

基于您在问题中的代码的完整工作示例:

use rusqlite::{params, Connection, Result};

#[derive(Debug)]
struct Person {
    id: i32,
    name: String,
    age: u8,
    data: String,
}

fn main() -> Result<()> {
    let conn = Connection::open_in_memory()?;

    conn.execute(
        "CREATE TABLE person (
                  id              INTEGER PRIMARY KEY,
                  name            TEXT NOT NULL,
                  age           INTEGER,
                  data            TEXT
                  )",
        [],
    )?;
    let me = Person {
        id: 0,
        name: "Steven".to_string(),
        age:1,
        data: "data1".to_string(),
    };
    conn.execute(
        "INSERT INTO person (name, age, data) VALUES (?1, ?2, ?3)",
        params![me.name, me.age, me.data],
    )?;

    conn.execute(
        "INSERT INTO person (name, age, data) VALUES (?1, ?2, ?3)",
        params!["john".to_string(), 2, "data2".to_string()],
    )?;
    
    let age:u8 = 1;
    
    let mut stmt = conn.prepare("SELECT id, name, age, data FROM person WHERE age=:age;")?;
    let person_iter = stmt.query_map(&[(":age", age.to_string().as_str())], |row| {
        Ok(Person {
            id: row.get(0)?,
            name: row.get(1)?,
            age: row.get(2)?,
            data: row.get(3)?,
        })
    })?;

    for person in person_iter {
        println!("Found person {:?}", person);
    }

    Ok(())
}