【Rust Cookbook系列】七 数据库

1. SQLite

  需要安装rusqlite库，可通过cargo add rusqlite --features bundled 命令安装

[dependencies]
rusqlite = { version = "0.29.0", features = ["bundled"] }

1.1 创建 SQLite 数据库

  使用rusqlite crate 打开 SQLite 数据库连接。如果数据库不存在，Connection::open方法将创建它。

// use rusqlite::NO_PARAMS; 已弃用：改用空数组;stmt.execute（NO_PARAMS） => stmt.execute（[]）
use rusqlite::{Connection, Result};

fn main() -> Result<()> {
    let conn = Connection::open("cats.db")?;

    conn.execute(
        "create table if not exists cat_colors (
             id integer primary key,
             name text not null unique
         )",
        [],
    )?;
    conn.execute(
        "create table if not exists cats (
             id integer primary key,
             name text not null,
             color_id integer not null references cat_colors(id)
         )",
        [],
    )?;

    Ok(())
}

• 运行cargo run生成cats.db文件

1.2 数据插入和查询

  Connection::open 将打开在前一章节实例中创建的数据库 cats 的连接。下面的实例使用 Connection 的 execute 方法将数据插入 cat_colors 和 cats 两张表中。首先，将数据插入到 cat_colors 表中。随后，使用 Connection 的 last_insert_rowid 方法来获取 cat_colors 表最后插入记录的 id。当向 cats 表中插入数据时，使用此 id。然后，使用 prepare 方法准备执行 select 查询操作，该方法提供 statement 结构体。最后，使用 statement 的 query_map 方法执行查询。

use rusqlite::{Connection, Result};
use std::collections::HashMap;

#[derive(Debug)]
struct Cat {
    name: String,
    color: String,
}

fn main() -> Result<()> {
    let conn = Connection::open("cats.db")?;

    let mut cat_colors = HashMap::new();
    cat_colors.insert(String::from("Blue"), vec!["Tigger", "Sammy"]);
    cat_colors.insert(String::from("Black"), vec!["Oreo", "Biscuit"]);

    for (color, catnames) in &cat_colors {
        conn.execute(
            "INSERT INTO cat_colors (name) values (?1)",
            &[&color.to_string()],
        )?;
        let last_id: String = conn.last_insert_rowid().to_string();

        for cat in catnames {
            conn.execute(
                "INSERT INTO cats (name, color_id) values (?1, ?2)",
                &[&cat.to_string(), &last_id],
            )?;
        }
    }
    let mut stmt = conn.prepare(
        "SELECT c.name, cc.name from cats c
         INNER JOIN cat_colors cc
         ON cc.id = c.color_id;",
    )?;

    let cats = stmt.query_map([], |row| {
        Ok(Cat {
            name: row.get(0)?,
            color: row.get(1)?,
        })
    })?;

    for cat in cats {
        println!("找到猫 {:?}", cat);
    }

    Ok(())
}

• 运行cargo run输出

找到猫 Ok(Cat { name: "Oreo", color: "Black" })
找到猫 Ok(Cat { name: "Biscuit", color: "Black" })
找到猫 Ok(Cat { name: "Tigger", color: "Blue" })
找到猫 Ok(Cat { name: "Sammy", color: "Blue" })

1.3 事务处理

  Connection::open 将打开来自前述实例的数据库 cats.db。使用 Connection::transaction 开始事务，除非使用 Transaction::commit 显式提交，否则事务将回滚。在下面的实例中，颜色表对颜色名称具有唯一性约束。当尝试插入重复的颜色时，事务会回滚。

use rusqlite::{Connection, Result};

fn main() -> Result<()> {
    let mut conn = Connection::open("cats.db")?;

    successful_tx(&mut conn)?;

    let res = rolled_back_tx(&mut conn);
    assert!(res.is_err());

    Ok(())
}

fn successful_tx(conn: &mut Connection) -> Result<()> {
    let tx = conn.transaction()?;

    tx.execute("delete from cat_colors", [])?;
    tx.execute("insert into cat_colors (name) values (?1)", &[&"lavender"])?;
    tx.execute("insert into cat_colors (name) values (?1)", &[&"blue"])?;

    tx.commit()
}

fn rolled_back_tx(conn: &mut Connection) -> Result<()> {
    let tx = conn.transaction()?;

    tx.execute("delete from cat_colors", [])?;
    tx.execute("insert into cat_colors (name) values (?1)", &[&"lavender"])?;
    tx.execute("insert into cat_colors (name) values (?1)", &[&"blue"])?;
    tx.execute("insert into cat_colors (name) values (?1)", &[&"lavender"])?;

    tx.commit()
}

• 运行cargo run输出

Error: SqliteFailure(Error { code: ConstraintViolation, extended_code: 787 }, Some("FOREIGN KEY constraint failed"))

2. Postgres (暂未测试)

  需要安装postgres库，可通过cargo add postgres 命令安装

[dependencies]
postgres = "0.19.5"

2.1 Postgres 数据库中创建表

  Postgres 数据库中，使用 postgres crate 创建表。
Client::connect用于连接到现有数据库。本实例中使用 Client::connect 格式化连接数据库的 URL 字符串。假设存在一个数据库：名为 library，用户名为 postgres，密码为 postgres。

use postgres::{Client, NoTls, Error};

fn main() -> Result<(), Error> {
    let mut client = Client::connect("postgresql://postgres:postgres@localhost/library", NoTls)?;
    // let mut client = Client::connect("host=localhost user=postgres", NoTls)?; //待测，官方连接数据库示例
    
    client.batch_execute("
        CREATE TABLE IF NOT EXISTS author (
            id              SERIAL PRIMARY KEY,
            name            VARCHAR NOT NULL,
            country         VARCHAR NOT NULL
            )
    ")?;

    client.batch_execute("
        CREATE TABLE IF NOT EXISTS book  (
            id              SERIAL PRIMARY KEY,
            title           VARCHAR NOT NULL,
            author_id       INTEGER NOT NULL REFERENCES author
            )
    ")?;

    Ok(())

}

2.2 数据插入和查询

  下述实例中使用 Client 的 execute 方法将数据插入到 author 表中。然后，使用 Client 的 query 方法查询 author 表中的数据。

use postgres::{Client, NoTls, Error};
use std::collections::HashMap;

struct Author {
    _id: i32,
    name: String,
    country: String
}

fn main() -> Result<(), Error> {
    let mut client = Client::connect("postgresql://postgres:postgres@localhost/library", 
                                    NoTls)?;
    
    let mut authors = HashMap::new();
    authors.insert(String::from("Chinua Achebe"), "Nigeria");
    authors.insert(String::from("Rabindranath Tagore"), "India");
    authors.insert(String::from("Anita Nair"), "India");

    for (key, value) in &authors {
        let author = Author {
            _id: 0,
            name: key.to_string(),
            country: value.to_string()
        };

        client.execute(
                "INSERT INTO author (name, country) VALUES ($1, $2)",
                &[&author.name, &author.country],
        )?;
    }

    for row in client.query("SELECT id, name, country FROM author", &[])? {
        let author = Author {
            _id: row.get(0),
            name: row.get(1),
            country: row.get(2),
        };
        println!("Author {} is from {}", author.name, author.country);
    }

    Ok(())

}

2.3 数据聚合

  下述实例按照降序列出了美国纽约州现代艺术博物馆数据库中首批 7999 位艺术家的国籍。

use postgres::{Client, Error, NoTls};

struct Nation {
    nationality: String,
    count: i64,
}

fn main() -> Result<(), Error> {
    let mut client = Client::connect(
        "postgresql://postgres:postgres@127.0.0.1/moma",
        NoTls,
    )?;

    for row in client.query 
    ("SELECT nationality, COUNT(nationality) AS count 
    FROM artists GROUP BY nationality ORDER BY count DESC", &[])? {
        
        let (nationality, count) : (Option<String>, Option<i64>) 
        = (row.get (0), row.get (1));
        
        if nationality.is_some () && count.is_some () {

            let nation = Nation{
                nationality: nationality.unwrap(),
                count: count.unwrap(),
        };
            println!("{} {}", nation.nationality, nation.count);
            
        }
    }

    Ok(())
}