Formatting mysql results into json?

help
1

has anyone some good links to tutorials about getting data from a mysql database and send it into a JSON object?

I am trying to get data from mysql (this i succeeded in) to form into JSON which i can then post on a web API. i looked into Serde, and into structs, looping through the rows and pushing them into a string.. but i always seem to get errors. :slight_smile:

2

Can you post the code you have tried so far?

1 Like
3

thank you for responding! Here is my best for now:

use std::fmt;
use mysql::*;
use mysql::prelude::*;

#[derive(Debug, PartialEq, Eq)]
struct Auditresult {
    audit_id: i32,
    title: String,
    actualvalue: String,
    expectedvalue: String,
    ok: String,
    flowid: String,
   clientid: String,
   fqdn: String
}

fn main() {
// maak verbinding met de mysql database
let opts = Opts::from_url("mysql://<DBURL>").unwrap();
let pool = Pool::new(opts).unwrap();
let mut conn = pool.get_conn().unwrap();

// haal de data uit de database
let selected_auditresults = conn
    .query_map(
        "SELECT audit_id, title, actualvalue, expectedvalue, ok, flowid, clientid, fqdn FROM auditresults WHERE inserted_at >= NOW() - INTERVAL 1 DAY",
        |(audit_id, title, actualvalue, expectedvalue, ok, flowid, clientid, fqdn)| {
            Auditresult { audit_id, title, actualvalue, expectedvalue, ok, flowid, clientid, fqdn }
        },
    ).unwrap();

// maak json van de data uit de database
let mut str = String::from("\"Customer\": \"<CUSTOMER>\",\"token\": \"<TOKEN>\",\"Audit\": [");
    for r in selected_auditresults.iter() {
let w = { auditid: r.audit_id, title: r.title};
str.push_str(&w);
      };

str.push_str("]}");

// print het uit!
println!("result: {}", str);
}

But this gives the error:

error: casts cannot be followed by a field access
  --> src/main.rs:45:11
   |
45 | let w = { auditid: r.audit_id, title: r.title};
   |           ^^^^^^^^^^
   |
help: try surrounding the expression in parentheses
   |
45 | let w = { (auditid: r).audit_id, title: r.title};
   |           +          +

error: struct literal body without path
  --> src/main.rs:45:9
   |
45 | let w = { auditid: r.audit_id, title: r.title};
   |         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   |
help: you might have forgotten to add the struct literal inside the block
   |
45 | let w = { SomeStruct { auditid: r.audit_id, title: r.title} };
   |         ++++++++++++                                        +

warning: unused import: `std::fmt`
 --> src/main.rs:1:5
  |
1 | use std::fmt;
  |     ^^^^^^^^
  |
  = note: `#[warn(unused_imports)]` on by default

warning: `mysql-test` (bin "mysql-test") generated 1 warning
error: could not compile `mysql-test` due to 2 previous errors; 1 warning emitted

But when i did use Structs, i got different errors depending on the tutorial i followed :frowning:

4

Hey,

  1. Add the following to your cargo.toml
    serde = { version = "1.0.130", features = ["derive"] }
    serde_json = "1.0"

  2. Make the code look like the one below :slight_smile:

use std::fmt;
use mysql::*;
use mysql::prelude::*;
use serde::Serialize;


#[derive(Debug, PartialEq, Eq, Serialize)]
struct Auditresult {
    audit_id: i32,
    title: String,
    actualvalue: String,
    expectedvalue: String,
    ok: String,
    flowid: String,
   clientid: String,
   fqdn: String
}

fn main() {
// maak verbinding met de mysql database
let opts = Opts::from_url("mysql://<DBURL>").unwrap();
let pool = Pool::new(opts).unwrap();
let mut conn = pool.get_conn().unwrap();

// haal de data uit de database
let selected_auditresults = conn
    .query_map(
        "SELECT audit_id, title, actualvalue, expectedvalue, ok, flowid, clientid, fqdn FROM auditresults WHERE inserted_at >= NOW() - INTERVAL 1 DAY",
        |(audit_id, title, actualvalue, expectedvalue, ok, flowid, clientid, fqdn)| {
            Auditresult { audit_id, title, actualvalue, expectedvalue, ok, flowid, clientid, fqdn }
        },
    ).unwrap();

    // maak json van de data uit de database
    let mut str = String::from("\"Customer\": \"<CUSTOMER>\",\"token\": \"<TOKEN>\",\"Audit\": [");
    for r in selected_auditresults.iter() {
        str.push_str(&serde_json::to_string(&r).unwrap());
    };

    str.push_str("]}");

    // print het uit!
    println!("result: {}", str);
}
1 Like
5

Got it. First things first, you should listen to what the compiler says. It's correct like 99.999% of the time and it will help you to speed up your learning of Rust.

That being said, the compiler is telling you that you can't create an instance of a struct without a path (like an anonymous struct), this is how you create new instances of a struct:

struct User {
    active: bool,
    username: String,
    email: String,
    sign_in_count: u64,
}

let user1 = User {
  email: String::from("someone@example.com"),
  username: String::from("someusername123"),
  active: true,
  sign_in_count: 1,
};

After fixing that, take notice on the push_str method that you are passing w to. Here's the documentation for it.

In the signature, you can see that it receives a string slice (&str), but you are not giving it a string slice. You are giving it a SomeStruct. Rust is a strongly typed language, and you will need to type this intermediate struct type as well. After doing that, you will need to make it possible to get a string representation of your struct.

Only you know your domain but, from what I can see in your code, you want a json-like string representation of w:

{ auditid: r.audit_id, title: r.title}

So you could do something like this:

format!("{{ auditid: {}, title: {}}}", r.audit_id, r.title);

But manually building json strings is not how things are usually done. You need to understand how serialization works, and that's usually done using the Serde crate in Rust.

For the record, this is not valid json:

// maak json van de data uit de database
let mut str = String::from("\"Customer\": \"<CUSTOMER>\",\"token\": \"<TOKEN>\",\"Audit\": [")
1 Like
6

thank you! you are a life saver! this works :slight_smile:

7

Thank you :slight_smile:
i know about Serde, and i very much want to learn it :slight_smile:

Truth is, i ws not involved in this part of the project untill long after it was supposed to be finished. That sort of got me a bit by the balls :frowning:

It's not how i expected my first Hello World to go, but heh :slight_smile: I will take time to learn :slight_smile:

8

and truth be told.. the company that is supposed to get the json data "doesnt do" normal JSON.. :slight_smile:

The program works, the JSON works, i send it to the correct URL through post request with reqwest. and still it failed :slight_smile:

BUT, with everybody's help i think i can manage to figure out that part on my own, and in the meantime do it correctly with Structs :slight_smile:

9

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.