How to extract data returned from a SQL query

Hello All,
SuperRustNoob here. I have been busy recreating simple programs I originally created in C now in Rust and have gotten stuck on trying to extract the data from from a SQL query so that I can format it to print to my console.

I have a very simple sqlite3 database with 50 records in 1 table that have basic info on US states. All of the data is in string/text format (for number values, I deal with that after it is in my program). My code is a modified version from the rusqlite documentation here: rusqlite - Rust

My code:

use rusqlite::{Connection, Result, NO_PARAMS};

#[derive(Debug)]
struct State {
    key: String,
    name: String,
    capital: String,
    population: String,
    area: String,
    date: String,
    bird: String,
}

fn main() -> Result<()> {
let conn = Connection::open("StateInfo.sqlite3")?;

let mut stmt = conn
    .prepare("SELECT key, name, capital, population, area, date, bird FROM Table_Main")?;
let state_iter = stmt
    .query_map(NO_PARAMS, |row| Ok(State {
        key: row.get(0)?,
        name: row.get(1)?,
        capital: row.get(2)?,
        population: row.get(3)?,
        area: row.get(4)?,
        date: row.get(5)?,
        bird: row.get(6)?,
    }))?;

for state in state_iter {
    println!("{:?}", state.unwrap());
}

Ok(())

}

When I run the program, I get the result shown below. All of the data is there. My question is how do I extract it from how it is being held? That is, I want to be able to run a for loop to print out all of the data in an order such as this:

for i in 0..numrecords {
    println!("{}\t {}\t {}\t", state[i].name, state[i].population, state[i].bird);
}

I am stuck on how to get the data out where I can use it.

Sorry for the stupid question :frowning:
Thanks,
Jerry

1 Like

Hi, you can't use a for i in 0..len loop in this situation because the type returned by query_map doesn't give you a length. You can iterate over it though (as you already are), you just have to make sure your value is Result::Ok before accessing the fields you want:

for state in state_iter {
    let state = state.unwrap();
    println!("{}\t {}\t {}\t", state.name, state.population, state.bird);
}

But we can do better:

for state in state_iter.flatten() {
    println!("{}\t {}\t {}\t", state.name, state.population, state.bird);
}

This way you will skip all Result::Err.

Iterators in Rust are (a lot) more used (and useful) than for i in 0..len loops.
Also if you want to know the type of a variable (for example state in your original loop) you can assign it () and the compile will tell you the type.

for state in state_iter {
    let _: () = state;
    println!("{:?}", state.unwrap());
}

The compiler should tell you something like:

expected type `()`
found type `Result<State>`

And from here you know Result is your problem.

1 Like

Awesome! Thank you so much for your help Leudz! Both of your snippets worked well for my problem. The last snippet was good information I was not aware of - I tried it and it works exactly as you say.

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.