SQLite Database
A few steps to show how to use Rust to open a SQLite database and run a query.
The examples which follow make use of a database called ‘shows’.
This database contains a table called ‘schedule’ - a list of TV Shows made up of the following columns:
| Column | Type |
|---|---|
| id | NUMERIC NOT NULL UNIQUE |
| name | TEXT NOT NULL |
| airing | INTEGER |
PRIMARY KEY = “id”
SQLite Rust Crate
An existing Crate called sqlite was used for managing the database.
Example
Opening a connection, executing a simple select statement and processing the rows
use crate::show::Show;
use std::collections::HashMap;
use sqlite::{Connection, State};
// this returns a Hashmap of a collection of TV Shows
pub fn getshows(self) -> HashMap<u32, Show> {
// Open a new connection
let connection = Connection::open(self.location).unwrap();
let mut shows: HashMap<u32, Show> = HashMap::new();
// Create a prepared statement to select specified columns from the table Schedule
let mut statement = connection
.prepare("SELECT id,name,airing FROM SCHEDULE WHERE airing = 1;")
.unwrap();
// Execute the statement and iterate through the rows returned
while let State::Row = statement.next().unwrap() {
let airing: bool
// String matching for saving a boolean datatype
match statement.read::<String>(2).unwrap().as_str() {
"1" => airing = true,
"0" => airing = false,
_ => airing = false,
// Create a new Show object
let show = Show::new(
statement.read::<String>(0).unwrap().parse::<u32>().unwrap(), // id
statement.read::<String>(1).unwrap(), // name
airing, // airing
);
// insert the show into the Hashmap using the id as the key and the Show object as the value
shows.insert(show.id, show);
}
//finally return the Hashmap
shows
}
Show = a struct which is made up the following fields, corresponding to each column found in the ‘schedule’ table.
pub struct Show {
pub id: u32,
pub name: String,
pub airing: bool,
}