How to insert HashMap into PostgreSQL as JSON type?
Answer a question 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 wh
Answer a question
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
- Like the error message says, your query has VALUE but it needs to be VALUES.
- 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`
Answers
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.
更多推荐
所有评论(0)