与 Postgres 合作

在 Postgres 数据库中,创建表

[![postgres-badge]][postgres] cat-database-badge

使用postgres在 Postgres 数据库中,创建表。

Connection::connect帮助连接到现有数据库。该食谱的Connection::connect使用一个 URL 字符串格式。 它假定一个名为library,用户名是postgres,密码是postgres.

extern crate postgres;

use postgres::{Connection, TlsMode, Error};

fn main() -> Result<(), Error> {
    let conn = Connection::connect("postgresql://postgres:postgres@localhost/library",
                                    TlsMode::None)?;

     conn.execute("CREATE TABLE IF NOT EXISTS author (
                    id              SERIAL PRIMARY KEY,
                    name            VARCHAR NOT NULL,
                    country         VARCHAR NOT NULL
                  )", &[])?;

    conn.execute("CREATE TABLE IF NOT EXISTS book  (
                    id              SERIAL PRIMARY KEY,
                    title           VARCHAR NOT NULL,
                    author_id       INTEGER NOT NULL REFERENCES author
                )", &[])?;

    Ok(())

}

插入和查询数据

[![postgres-badge]][postgres] cat-database-badge

该食谱,用Connectionexecute方法,将数据插入author表。 然后,用Connectionquery方法,显示author表。

extern crate postgres;

use postgres::{Connection, TlsMode, Error};
use std::collections::HashMap;

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

fn main() -> Result<(), Error> {
    let conn = Connection::connect("postgresql://postgres:postgres@localhost/library",
                                    TlsMode::None)?;

    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()
        };

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

    for row in &conn.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(())

}

汇总数据

[![postgres-badge]][postgres] cat-database-badge

该食谱,列出了数据库中,现代艺术博物馆的前 7999 名艺术家的国籍,按降序排列。

extern crate postgres;
use postgres::{Connection, Error, TlsMode};

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

fn main() -> Result<(), Error> {
    let conn = Connection::connect(
        "postgresql://postgres:postgres@127.0.0.1/moma",
        TlsMode::None,
    )?;

    for row in &conn.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(())
}