Select integer from MySQL Database

I started using the mysql crate... And I'm trying to select an integer value from the database... This is the only example the documentation gives:

// Let's select payments from database
let selected_payments: Vec<Payment> =
pool.prep_exec("SELECT customer_id, amount, account_name from payment", ())
.map(|result| { // In this closure we will map `QueryResult` to `Vec<Payment>`
    // `QueryResult` is iterator over `MyResult<row, err>` so first call to `map`
    // will map each `MyResult` to contained `row` (no proper error handling)
    // and second call to `map` will map each `row` to `Payment`
    result.map(|x| x.unwrap()).map(|row| {
        // ⚠️ Note that from_row will panic if you don't follow your schema
        let (customer_id, amount, account_name) = my::from_row(row);
        Payment {
            customer_id: customer_id,
            amount: amount,
            account_name: account_name,
        }
    }).collect() // Collect payments so now `QueryResult` is mapped to `Vec<Payment>`
}).unwrap(); // Unwrap `Vec<Payment>`

My eyes are almost bleeding from all the map/wrap/unwrap/... There must be more easier ways and examples on the internet on how to use this crate, no?

Anyway, this is what I currently have:

        let selected_race_id : u16 = 
            self.conn.prep_exec("SELECT id FROM races WHERE race_name = :race_name", params!("race_name" => cat.race.to_string())).unwrap();

How do I convert mysql::QueryResult to an integer? And how to check if a value is returned in case it doesn't exist?

A more detailed tutorial/guide on how to use the mysql-crate with basic examples would be appreciated as well! Thanks!

Grief that is horrendous. All that functional programming style "map..map..unwrap..map..collect..unwrap" makes my eyes bleed and my head spin.

Still, as far as I can tell at the end of that example you have "selected_payments" which is a Vector of "Payment" structs. It contains one element for every row of the table that matched the query. If there were only one matching row in the table you still get a vector result, containing one element of "Payment"

So all you need to do is iterate over that "selected_payments" vector and access the ".id" field. Which presumably is an i64 or some such.

This all looks much nicer in old fashioned imperative style to my eyes. Like so (Sorry postgres example):

    struct Person {
        id: i64,
        name: String,
        data: Option<Vec<u8>>,
    }

    let conn = model.connect();
    let rows = conn
        .query("SELECT id, name, data FROM person", &[])
        .unwrap();

    let mut results = Persons { persons: vec![] };

    for row in &rows {
        let person = Person {
            id: row.get(0),
            name: row.get(1),
            data: row.get(2),
        };
        results.persons.push(person);
    }
1 Like

Thanks a lot! This works! Your code looked a lot cleaner and easier to understand than the one in the documentation. :smiley:

            //Check if race of cat is already known in database
        let selected_races_rows : std::vec::Vec<std::result::Result<mysql::Row, mysql::Error>> = 
            self.conn.prep_exec("SELECT id FROM races WHERE race_name = :race_name", params!("race_name" => cat.race.to_string())).unwrap().collect();

        let mut results : std::vec::Vec<u16> = std::vec::Vec::new();
        for row in selected_races_rows {
            results.push(row.unwrap().take("id").unwrap());
        }

        println!("Result: {}", results[0]);

If there are more elegant/shorter/more efficient ways to write/optimize my code, please let me know.

Generally I would recommend importing your types instead of using complete paths, but in general it looks good. Regarding efficiency I'll just mention Vec::with_capacity for your results vector, but it's not a big deal.

You might also want to consider putting your code in a function that returns a result so you can use the question mark operator instead of unwrap().

1 Like

No thanks to me. I just cobbled that up from the examples in the rust-postgres crate :slight_smile:

I'm glad to hear it's not just me that has trouble with all that functional programming style obfuscation. Unfortunately it's something we are going to live with in the Rust world.

For now I still try and pull things straight until the look like C/Pascal/Algol so that I can see what is going on and don't need to add a ton of comments to explain it to others. This often as the pleasant side effect of running faster as well (Not always mind).

1 Like

This code is part of a function that returns a bool. So then I cant use the ? operator. Maybe I'll split the code up in functions that return a Result so I can use that ?-operator, good idea.

Perhaps you might like to rethink that idea of returning a bool?

What does that bool mean? Does it mean that the required query found what you were looking for or not?

Or does it mean the database connection failed or some other error occurred, or not?

How does the caller, or anyone in the system know the difference between not finding the data and having some error occur?

For this reason Rust has this neat idea of returning a Result type that can contain valid result data like found/not found or some error indication.

With such a Result type the caller gets to know what happened and can deal with it accordingly.

The code is actually part of a bigger function called 'Add'.
It adds a specific object to a table, if it's added successfully to the table the bool returns true.

I just needed to select another foreign key id from another table, that's the reason for the select-statement.

But I'm thinking of now separating the select-part for the foreign-key id in a separate function.