Mysql data into csv file

hello everyone!

i have a problem with the following code:

use mysql::*;
use mysql::prelude::*;
use serde::Serialize;
use serde::Deserialize;

#[derive(Debug, PartialEq, Serialize, Deserialize)]

pub struct Bookdata {
    pub id: u64,
    pub title: String,
    }

#[tokio::main]
async fn main() {

// connect to the database
let url = "mysql://<URL>";
let opts = Opts::from_url(url).unwrap();
let pool = Pool::new(opts).unwrap();
let mut conn = pool.get_conn().unwrap();

//// GET DATA FROM DATABASE
let selected_results = conn
    .query_map(
        "SELECT id, title FROM books",
        |(id, title )| {
            Bookdata {id, title}
        },
    );
      println!("{:?}", selected_results);
//    for d in selected_results.iter() {
//        println!("{:?}: {:?}", d.id, d.title);
//    }
//// END GET DATA FROM DATABASE

 }

This works. it prints out every id and title from the database in a json format.
when i uncomment the {:?} line i get an error that no valid field for id and title exist on struct bookdata:
error[E0609]: no field title on type &Vec<Bookdata>

ultimatly, i want to put the id and title into a csv file, i have found that there's a csv crate but most examples are about reading from/to CLI. i have yet to find a tutorial about grabbing data from mysql and put it into a csv file.

Does anybody know what im doing wrong?

There are several examples of writing to a CSV file in the csv crate docs on csv::Writer. Do any of them help?

2 Likes

i saw that one :slight_smile: but since i cant get the fields from the database, i see not how i can write it to csv :slight_smile:

otherwise i cpould use something like:

let mut wtr = csv::Writer::from_writer(&selected_results);

// When writing records with Serde using structs, the header row is written
// automatically.
wtr.serialize(Record {
    id: &selected_results.id.to_string(),
    title: &selected_results.title.to_string(),
});
wtr.flush()?;

i think?

this seems to work!

use mysql::*;
use mysql::prelude::*;

pub struct bookdata {
    pub id: u64,
    pub title: String,
    }

#[tokio::main]
async fn main() {

// connect to the database
let url = "mysql://<URL>";
let opts = Opts::from_url(url).unwrap();
let pool = Pool::new(opts).unwrap();
let mut conn = pool.get_conn().unwrap();

let mut bookresults: Vec<Bookdata> = vec![];

   let _selected_results = conn
        .query_map(
            "SELECT id, title from books",
            |(id, title)| {
                bookresults.push( Bookdata { id, title } );
            },
        ).unwrap();

 for r in bookresults.iter() {
     println!("id: {} - title: {}", r.id, r.title);
 }

 }

i had to put an underscore to silence a warning about an unused variable, so if someone knows a better way for this, im keen to learn :slight_smile:

You should read the documentation (or at the very least, the signature) of whatever function you are trying to call. query_map() is meant to be used like you did in your first attempt, i.e., it collects the values returned by the mapped function into a Vec. However, a DB query can fail, hence it returns a Result. In your first code snippet, you were trying to iterate over the Result (which has 1 single element – the Vec of all rows – if it's Ok and 0 elements if it's Err). You should have handled the error instead in order to get the Vec out of the Result.

1 Like

i have read the documentation, thank you. but you did point me into the right direction :slight_smile:
i added an .expect() and now it works

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.