I'm building a simple API using Express and SQLite. I managed to build the database and add data from a CSV file that looks like this:
|Timestamp |Email |Name |Year|Make |Model |Car_ID|Judge_ID|Judge_Name|Racer_Turbo|Racer_Supercharged|Racer_Performance|Racer_Horsepower|Car_Overall|Engine_Modifications|Engine_Performance|Engine_Chrome|Engine_Detailing|Engine_Cleanliness|Body_Frame_Undercarriage|Body_Frame_Suspension|Body_Frame_Chrome|Body_Frame_Detailing|Body_Frame_Cleanliness|Mods_Paint|Mods_Body|Mods_Wrap|Mods_Rims|Mods_Interior|Mods_Other|Mods_ICE|Mods_Aftermarket|Mods_WIP|Mods_Overall|
|--------------|-------------------------|----------|----|--------|---------|------|--------|----------|-----------|------------------|-----------------|----------------|-----------|--------------------|------------------|-------------|----------------|------------------|------------------------|---------------------|-----------------|--------------------|----------------------|----------|---------|---------|---------|-------------|----------|--------|----------------|--------|------------|
|8/5/2018 14:10|[email protected] |Hernando |2015|Acura |TLX |48 |J04 |Bob |0 |0 |2 |2 |4 |4 |0 |2 |4 |4 |2 |4 |2 |2 |2 |2 |2 |0 |4 |4 |4 |6 |2 |0 |4 |
|8/5/2018 15:11|[email protected] |Noel |2015|Jeep |Wrangler |124 |J02 |Carl |0 |6 |4 |2 |4 |6 |6 |4 |4 |4 |6 |6 |6 |6 |6 |4 |6 |6 |6 |6 |6 |4 |6 |4 |6 |
|8/5/2018 17:10|[email protected] |Edan |2015|Lexus |Is250 |222 |J05 |Adrian |0 |0 |0 |0 |0 |0 |0 |0 |6 |6 |6 |0 |0 |6 |6 |6 |0 |0 |0 |0 |0 |0 |0 |0 |4 |
|8/5/2018 17:34|[email protected] |Hieronymus|1993|Honda |Civic eG |207 |J06 |Aaron |0 |0 |2 |2 |2 |2 |2 |2 |0 |4 |2 |2 |2 |2 |2 |2 |4 |2 |2 |0 |0 |0 |2 |2 |0 |
|8/5/2018 14:30|[email protected] |Nickolas |2016|Ford |Mystang |167 |J02 |Carl |0 |0 |2 |2 |0 |2 |2 |0 |0 |0 |0 |2 |0 |2 |2 |2 |0 |0 |2 |0 |0 |0 |0 |0 |2 |
|8/5/2018 16:12|[email protected] |Martin |2013|Hyundai |Gen coupe|159 |J04 |Bob |0 |0 |2 |0 |0 |0 |2 |0 |0 |0 |0 |2 |0 |2 |2 |0 |2 |0 |2 |0 |0 |0 |0 |0 |0 |
|8/5/2018 17:00|[email protected]|Aldridge |2009|Infiniti|G37 |20 |J06 |Aaron |2 |0 |2 |2 |0 |0 |2 |0 |0 |2 |2 |2 |2 |2 |2 |2 |2 |2 |4 |2 |2 |0 |2 |0 |2 |
|8/5/2018 16:11|[email protected] |Ambros |2009|Honda |Oddesy |178 |J06 |Aaron |2 |0 |2 |2 |2 |2 |2 |0 |4 |4 |2 |2 |2 |4 |4 |4 |2 |2 | |6 |4 |4 |6 |4 |6 |
...
I used the csvtojson package to do so.
My database structure has 7 columns derived from the CSV data (Car_ID, Email, Name, Year, Make, Model, Score). The score column is the sum of all values from the Racer_Turbo column to Mods_Overall from the csv file. I managed to accurately add the car_id, email, name, year, make, and model data correctly into the database, but I can't seem to get the total to map to the score column.
Here's my code:
let db = new sqlite3.Database(config.database_name, (err) => {
if (err) {
console.error(err.message)
throw err
} else {
console.log('Connected to database...'.blue)
db.run(`CREATE TABLE cars (
car_id INT PRIMARY KEY,
email TEXT UNIQUE,
name TEXT,
year INT,
make TEXT,
model TEXT,
score INT
)`,
(err) => {
if (err) {
} else {
csvtojson().fromFile(DATA_CSV)
.then(data => {
var insert = 'INSERT INTO cars (Car_ID, Email, Name, Year, Make, Model, Score) VALUES (?,?,?,?,?,?,?)';
for(const item of data) {
db.run(insert, [
item.Car_ID,
item.Email,
item.Name,
item.Year,
item.Make,
item.Model,
[item.Racer_Turbo item.Racer_Supercharged item.Racer_Performance item.Racer_Horsepower item.Car_Overall item.Engine_Modifications item.Engine_Performance item.Engine_Chrome item.Engine_Detailing item.Engine_Cleanliness item.Body_Frame_Undercarriage item.Body_Frame_Suspension item.Body_Frame_Chrome item.Body_Frame_Detailing item.Body_Frame_Cleanliness item.Mods_Paint item.Mods_Body item.Mods_Wrap item.Mods_Rims item.Mods_Interior item.Mods_Other item.Mods_ICE item.Mods_Aftermarket item.Mods_WIP item.Mods_Overall]
]);
}
}).catch(err => {
// log error
console.log(err);
});
=
}
});
}
});
But the output that I get when call the GET request to display the data is:
{
"car_id": 48,
"email": "[email protected]",
"name": "Hernando",
"year": 2015,
"make": "Acura",
"model": "TLX",
"score": 2.2440244242222204e 22
},
{
"car_id": 167,
"email": "[email protected]",
"name": "Nickolas",
"year": 2016,
"make": "Ford",
"model": "Mystang",
"score": 2.2022000020222005e 22
},
{
"car_id": 222,
"email": "[email protected]",
"name": "Edan",
"year": 2015,
"make": "Lexus",
"model": "Is250",
"score": 66600666000000000
},
...
See how the score value is not an integer but some super long decimal value. Each entry should just have a whole integer value. The expected output is:
{
"car_id": 48,
"email": "[email protected]",
"name": "Hernando",
"year": 2015,
"make": "Acura",
"model": "TLX",
"score": 62
},
{
"car_id": 167,
"email": "[email protected]",
"name": "Nickolas",
"year": 2016,
"make": "Ford",
"model": "Mystang",
"score": 20
},
{
"car_id": 159,
"email": "[email protected]",
"name": "Martin",
"year": 2013,
"make": "Hyundai",
"model": "Gen coupe",
"score": 14
}
...
Any help is greatly appreciated.
CodePudding user response：
for(const item of data) {
int temp = item.Racer_Turbo item.Racer_Supercharged item.Racer_Performance item.Racer_Horsepower item.Car_Overall item.Engine_Modifications item.Engine_Performance item.Engine_Chrome item.Engine_Detailing item.Engine_Cleanliness item.Body_Frame_Undercarriage item.Body_Frame_Suspension item.Body_Frame_Chrome item.Body_Frame_Detailing item.Body_Frame_Cleanliness item.Mods_Paint item.Mods_Body item.Mods_Wrap item.Mods_Rims item.Mods_Interior item.Mods_Other item.Mods_ICE item.Mods_Aftermarket item.Mods_WIP item.Mods_Overall;
db.run(insert, [
item.Car_ID,
item.Email,
item.Name,
item.Year,
item.Make,
item.Model,
temp
]);
CodePudding user response：
@Hogan asked me a really good question that pointed me in the right direction, and that was that one or more of the columns was not numeric in the data.
What I first did was start adding each column into the score individually to see what was happening. When I got to item.Racer_Horsepower, for whatever reason, some of the entries in the database were correctly adding the values while some where adding by the hundreds instead of as single digits. I think this is what caused the final output for each cars score to be a really odd number.
What I did was wrap each column I was adding with parseInt() when doing the addition as to ensure that the values where integers/numeric. What I ended up with was not the prettiest but got the correct outputs for each cars score.
for(const item of data) {
let total = parseInt(item.Racer_Turbo) parseInt(item.Racer_Supercharged) parseInt(item.Racer_Performance) parseInt(item.Racer_Horsepower) parseInt(item.Car_Overall) parseInt(item.Engine_Modifications) parseInt(item.Engine_Performance) parseInt(item.Engine_Chrome) parseInt(item.Engine_Detailing) parseInt(item.Engine_Cleanliness) parseInt(item.Body_Frame_Undercarriage) parseInt(item.Body_Frame_Suspension) parseInt(item.Body_Frame_Chrome) parseInt(item.Body_Frame_Detailing) parseInt(item.Body_Frame_Cleanliness) parseInt(item.Mods_Paint) parseInt(item.Mods_Body) parseInt(item.Mods_Wrap) parseInt(item.Mods_Rims) parseInt(item.Mods_Interior) parseInt(item.Mods_Other) parseInt(item.Mods_ICE) parseInt(item.Mods_Aftermarket) parseInt(item.Mods_WIP) parseInt(item.Mods_Overall);
db.run(insert, [
item.Car_ID,
item.Email,
item.Name,
item.Year,
item.Make,
item.Model,
total
]);
}