Sqlite error - table data has no column named date

help
1

I have a program that reads in a CSV file line by line. As each line is read, I am attempting to insert the fields into an SQLite database. The program reads the first line of the CSV file and prints out the first date value correctly. When it attempts to insert this date value into the database an error is generated "table data has no column named date". I have reviewed the code and do not see an error. Any assistance will be appreciated. Below are the functions used to create the table and insert CSV data into an SQLite database via rusqlite.

use rusqlite::{Connection, Error};

pub fn create_table(conn: &Connection) -> Result<(), Error> {

    match conn.execute(
        "CREATE TABLE IF NOT EXISTS data (
            date TEXT NOT NULL PRIMARY KEY,
            open REAL NOT NULL,
            high REAL NOT NULL,
            low REAL NOT NULL,
            close REAL NOT NULL,
            volume INTEGER NOT NULL
        )",
        [],
    ) {
        Ok(_) => Ok(()),
        Err(e) => Err(e),
    }

}

use std::fs::File;
use std::io::{BufReader, BufRead};
use rusqlite::{Connection, named_params};
use crate::utils::enums::io_sql_error::IoSqlError;

pub fn csv_to_sql(csv_file: &File, conn : &Connection) -> Result<(), IoSqlError> {

    let reader = BufReader::new(csv_file);
    let mut lines = reader.lines().skip(1);
    let mut fields: Vec<String>;

    while let Some(line) = lines.next() {

        match line {
            Ok(lineitem) => {
                fields = lineitem.split(',').map(|s| s.trim().to_string()).collect();
                eprintln!("Read line date: {}", fields[0]);
                // prints out the corret date
            },
            Err(e) => { 
                eprintln!("Error reading line: {}", e);
                return Err(From::from(e));
            }
        }

        match conn.execute (
            "Insert INTO data (date, open, high, low, close, volume)
                  values (:date, :open, :high, :low, :close, :volume)",
                  named_params! {"date": fields[0].to_string(), 
                                         "open": fields[1].parse::<f64>().unwrap_or(0.0), 
                                         "high": fields[2].parse::<f64>().unwrap_or(0.0), 
                                         "low": fields[3].parse::<f64>().unwrap_or(0.0), 
                                         "close": fields[4].parse::<f64>().unwrap_or(0.0), 
                                         "volume": fields[5].parse::<i64>().unwrap_or(0)},
        )
        {
            Ok(_) => {
                eprintln!("Record added successfully");
            },
            Err(e) => {  
                eprintln!("Error inserting into database: {}", e);
                return Err(From::from(e));
                // Error inserting into database: table data has no column named date
            }       
        };

    } // end of while loop

    Ok(())

} // end of function
2

One possible way this could occur (and seem really baffling) is if the table already existed with different columns, likely from a previous run. CREATE TABLE IF NOT EXISTS would therefore silently skip creating the table, and INSERT would then fail due to the schema mismatch. I suggest double-checking that or simply removing the .sqlite3 file and running again.

3

Good thought. So I deleted the dB and got the same problem. Then I updated the create_table function as shown below. It now is coded to insert one record into the created table. It compiles and indicates at run time that the record was inserted. If I run the program again it indicates that the insert failed because of the unique constraint i.e. cannot have two dates that are equal. This confirms to me that the table is being created and that data can be inserted. I think the problem lies solely with this line ""date": fields[0].to_string()" in the css_to_sql function as shown above. I think there is some type of mismatch but do not know what it is. One additional note, the error message is now "invalid parameter name: date".

pub fn create_table(conn: &Connection) -> Result<(), Error> {
    match conn.execute(
        "CREATE TABLE IF NOT EXISTS data (
            date TEXT NOT NULL PRIMARY KEY,
            open REAL NOT NULL,
            high REAL NOT NULL,
            low REAL NOT NULL,
            close REAL NOT NULL,
            volume INTEGER NOT NULL
        )",
        [],
    ) {
        Ok(_) => {
            match conn.execute(
                "Insert INTO data (date, open, high, low, close, volume)
                  values ('2001-01-01', 100.0, 110.0, 90.0, 105.0, 1000000)",
                [],         
            ){
                Ok(_) => {
                    eprintln!("Sample record added successfully");
                }
                Err(e) => {
                    eprintln!("Error inserting sample record into database: {}", e);
                    return Err(e);
                }           
            }
            Ok(())
        },
        Err(e) => Err(e),
    }
}
4

Ah. I haven’t personally used rusqlite, so I can’t speak to that section of your code. Perhaps someone else will see this and be of help. Best of luck!

5

I have not used SQLite with Rust, but column name "date" seems suspicious. It could be that "date" is special reserved keyword for some function or data type. Try renaming that column to something else and test (or quote it appropriately).

6

In the named_params example, it appears that the $param_name values are preceded by colons just as in the SQL statement. Perhaps instead of:

named_params! {"date": fields[0].to_string(), ...

You might need to write:

named_params! {":date": fields[0].to_string(), ...

The Params > named parameters docs seem to support this as well. I'm not sure whether that's the issue or I'm missing something and you had a good reason to omit the colons.

7

You hit the nail on the head. I completely overlooked that.
Below is the updated/working code snippet. Thank you.

            "Insert INTO data (date, open, high, low, close, volume)
                  values (:date, :open, :high, :low, :close, :volume)",
                  named_params! {":date": fields[0],   
                                         ":open": fields[1].parse::<f64>().unwrap_or(0.0), 
                                         ":high": fields[2].parse::<f64>().unwrap_or(0.0), 
                                         ":low": fields[3].parse::<f64>().unwrap_or(0.0), 
                                         ":close": fields[4].parse::<f64>().unwrap_or(0.0), 
                                         ":volume": fields[5].parse::<i64>().unwrap_or(0)},
        )
1 Like