如何将 HashMap 作为 JSON 类型插入 PostgreSQL? [英] How to insert HashMap into PostgreSQL as JSON type?

问题描述

contacts 的数据结构为 HashMap，我使用的是 PostgreSQL 客户端 -rust-postgres 将 contact 的键和值插入表中，然后我想从表中进行选择.以下是我到目前为止所尝试的.我需要帮助编写正确的语法.

使用 postgres::{Client, NoTls};使用 std::collections::HashMap;fn main() ->结果<(), Box<dyn std::error::Error>>{let mut client = Client::connect("host=127.0.0.1 user=postgres", NoTls)?;client.simple_query("掉落表如果存在以下关系；")?;client.simple_query("创建表 following_relation (id SERIAL NOT NULL 主键，关系 JSON 非空)")?;让 mut 联系人 = HashMap::new();让 mut v: Vec= Vec::new();v = vec!["jump".to_owned(), "jog".to_string()];联系人.插入(阿什利"，v.clone())；对于(姓名，爱好)在contacts.iter(){//1. 带参数的sql语句怎么写?客户端.执行(插入以下_关系(关系)VALUE ('{"name" : $1, "hobby" : $2}')",&[&name, &hobby],)?;}对于 client.query("SELECT id,relation FROM following_relation", &[]) 中的行?{//2. 如何从解析结果中读取?让 id: i32 = row.get(0);让关系 = row.get(1);//println!("找到的人:{} {} {:?}", id, relation["name"],relation["hobby"]);}好的(())}

我得到了提示

<块引用>

1. 如错误消息所述，您的查询有 VALUE，但必须是 VALUES.
2. 查询参数不能插入到字符串中.您应该在 Rust 中构建对象，并使用 https://docs.rs/postgres/0.17.0/postgres/types/struct.Json.html 在插入时包装类型.

我不知道如何在此处应用 pub struct Json(pub T);.

如何构建函数 query="nofollow noreferrer">执行?

pub fn execute(&mut 自我，查询:&T，参数:&[&(dyn ToSql + Sync)]) ->结果在哪里T:ToStatement，

<小时>

已更新，我尝试使用更简短的代码示例

使用 postgres::{Client, NoTls};使用 postgres::types::Json;使用 serde::{Deserialize, Serialize};#[派生(调试，序列化，反序列化)]结构关系{名称:字符串，爱好:Vec}pub struct Json<T>(pub T);fn main() ->结果<(), Box<dyn std::error::Error>>{let mut client = Client::connect("host=127.0.0.1 user=postgres", NoTls)?;client.simple_query("掉落表如果存在以下关系；")?;client.simple_query("创建表 following_relation (id 序列主键，关系 JSON 非空)")?;让 rel = 关系 {名称:czfzdxx".to_string(),爱好:vec！[篮球".to_string(),慢跑".to_string()],};客户端.执行(r#"INSERT INTO following_relation(relation)价值 ($1)"#,&[&Json(&rel)])?;好的(())}

我明白了

error[E0432]: 未解析的导入`postgres::types::Json`

解决方案

这里是 main.rs:

使用 postgres::{Client, NoTls};使用 serde::{Deserialize, Serialize};使用 postgres_types::Json;使用 postgres_types::{FromSql};#[派生(调试，反序列化，序列化，FromSql)]结构关系{名称:字符串，爱好:Vec}fn main() ->结果<(), Box<dyn std::error::Error>>{let mut client = Client::connect("host=127.0.0.1 user=postgres", NoTls)?;client.simple_query("掉落表如果存在以下关系；")?;client.simple_query("创建表 following_relation (id 序列主键，关系 JSON 非空)")?;让 rel = 关系 {名称:czfzdxx".to_string(),爱好:vec！[篮球".to_string(),慢跑".to_string()],};客户端.执行(插入以下关系(关系)值($ 1)"，&[&Json::<关系>(rel)])?;对于 &client.query("SELECT关系 FROM following_relation", &[]).unwrap() {让 rel: Json<Relation>= row.get(0);println!("{:?}", rel);}好的(())}

和Cargo.toml:

[包]名称 = "测试应用"版本 = "0.1.0"版本 = "2018"# 在 https://doc.rust-lang.org/cargo/reference/manifest.html 查看更多键及其定义[依赖项]postgres = {version = "0.17.0"}tokio-postgres = "0.5.1"serde = {version = "1.0.104", features = ["derive"]}postgres-types = {version = "0.1.0", features = ["derive", "with-serde_json-1"]}serde_json = "1.0.45"

这里是使用的相关文档:postgres_types 和 postgres.搜索 serde_json、ToSql 和 FromSql 特征是针对此第三方类型实现的.

contacts has a data structure as HashMap, I'm using PostgreSQL client -rust-postgres to insert contact's key and value into a table, then I want to select from the table. Below is what I tried so far. I need help with writing the right syntax.

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

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let mut client = Client::connect("host=127.0.0.1 user=postgres", NoTls)?;

    client.simple_query("
        DROP TABLE 
        IF EXISTS following_relation;
        ")?;

    client.simple_query("
        CREATE TABLE following_relation (
            id               SERIAL NOT NULL PRIMARY KEY,
            relation         JSON NOT NULL
        )
    ")?;

    let mut contacts = HashMap::new();
    let mut v: Vec<String> = Vec::new();

    v = vec!["jump".to_owned(), "jog".to_string()];
    contacts.insert("Ashley", v.clone());

    for (name, hobby) in contacts.iter() {
        // 1. How to write sql statement with parameters?
        client.execute(
        "INSERT INTO following_relation(relation) 
         VALUE ('{"name" : $1, "hobby" : $2}')", 
        &[&name, &hobby],
    )?;  
    }

    for row in client.query("SELECT id, relation FROM following_relation", &[])? {
        // 2. How to read from parse the result?
        let id: i32 = row.get(0);
        let relation = row.get(1);
        //println!("found person: {} {} {:?}", id, relation["name"], relation["hobby"]); 
    }
    Ok(())
}

I've been given the hints

1. Like the error message says, your query has VALUE but it needs to be VALUES.
2. Query parameters cannot be interpolated into strings. You should build the object in Rust, and use https://docs.rs/postgres/0.17.0/postgres/types/struct.Json.html to wrap the types when inserting.

I have no idea how to apply pub struct Json<T>(pub T); here.

How to build the query required in function execute?

pub fn execute<T: ?Sized>(
    &mut self,
    query: &T,
    params: &[&(dyn ToSql + Sync)]
) -> Result<u64, Error>
where
    T: ToStatement, 

---

UPDATED, I tried with a more brief code sample

use postgres::{Client, NoTls};
use postgres::types::Json;
use serde::{Deserialize, Serialize};

#[derive(Debug, Serialize, Deserialize)]
struct relations {
    name : String,
    hobby: Vec<String>
}
pub struct Json<T>(pub T);

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let mut client = Client::connect("host=127.0.0.1 user=postgres", NoTls)?;

    client.simple_query("
        DROP TABLE 
        IF EXISTS following_relation;

        ")?;

    client.simple_query("
        CREATE TABLE following_relation (
            id      SERIAL PRIMARY KEY,
            relation    JSON NOT NULL
        )
    ")?;

    let rel = relations {
        name: "czfzdxx".to_string(),
        hobby: vec![
            "basketball".to_string(),
            "jogging".to_string()
        ],
    };

    client.execute(
        r#"INSERT INTO following_relation(relation)
             VALUE ($1)"#,
        &[&Json(&rel)]
    )?;

    Ok(())
}

I get

error[E0432]: unresolved import `postgres::types::Json`

解决方案

Here is main.rs:

use postgres::{Client, NoTls};
use serde::{Deserialize, Serialize};
use postgres_types::Json;
use postgres_types::{FromSql};


#[derive(Debug, Deserialize, Serialize, FromSql)]
struct Relation {
    name : String,
    hobby: Vec<String>
}

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let mut client = Client::connect("host=127.0.0.1 user=postgres", NoTls)?;

    client.simple_query("
        DROP TABLE 
        IF EXISTS following_relation;

        ")?;

    client.simple_query("
        CREATE TABLE following_relation (
            id      SERIAL PRIMARY KEY,
            relation    JSON NOT NULL
        )
    ")?;

    let rel = Relation {
        name: "czfzdxx".to_string(),
        hobby: vec![
            "basketball".to_string(),
            "jogging".to_string()
        ],
    };

    client.execute(
        "INSERT INTO following_relation (relation) VALUES ($1)",
        &[&Json::<Relation>(rel)]
    )?;

    for row in &client.query("SELECT relation FROM following_relation", &[]).unwrap() {
        let rel: Json<Relation> = row.get(0);
        println!("{:?}", rel);
    }

    Ok(())
}

and Cargo.toml:

[package]
name = "testapp"
version = "0.1.0"
edition = "2018"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
postgres = {version = "0.17.0"}
tokio-postgres = "0.5.1"
serde = {version = "1.0.104", features = ["derive"]}
postgres-types = {version = "0.1.0", features = ["derive", "with-serde_json-1"]}
serde_json = "1.0.45"

And here is the relevant documentation used: postgres_types and postgres. Search for serde_json, ToSql and FromSql traits are implemented for this third-party type.

这篇关于如何将 HashMap 作为 JSON 类型插入 PostgreSQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！