我正在使用 ExpressJS 和 SQLite 構建一個簡單的 API。
我正在添加一個 router.patch 方法，該方法將使用 id 作為主鍵更新 API 資料庫中的資料條目。在我的資料庫中，有一個名為 score 的列，由其他列的值的總和及其整數型別的值填充。
我需要我的方法來更新資料庫中的一個或多個列，這些列又應該更新分數列以反映新值。
到目前為止的方法如下：
router.patch('/cars/:id', (req, res) => {
try {
let sql = `UPDATE cars SET email = COALESCE(?,email), name = COALESCE(?,name), year = COALESCE(?,year), make = COALESCE(?,make), model = COALESCE(?,model), racer_turbo = COALESCE(?,racer_turbo), racer_supercharged = COALESCE(?,racer_supercharged), racer_performance = COALESCE(?,racer_performance), racer_horsepower = COALESCE(?,racer_horsepower), car_overall = COALESCE(?,car_overall), engine_modifications = COALESCE(?,engine_modifications), engine_performance = COALESCE(?,engine_performance), engine_chrome = COALESCE(?,engine_chrome), engine_detailing = COALESCE(?,engine_detailing), engine_cleanliness = COALESCE(?,engine_cleanliness), body_frame_undercarriage = COALESCE(?,body_frame_undercarriage), body_frame_suspension = COALESCE(?,body_frame_suspension), body_frame_chrome = COALESCE(?,body_frame_chrome), body_frame_detailing = COALESCE(?,body_frame_detailing), body_frame_cleanliness = COALESCE(?,body_frame_cleanliness), mods_paint = COALESCE(?,mods_paint), mods_body = COALESCE(?,mods_body), mods_wrap = COALESCE(?,mods_wrap), mods_rims = COALESCE(?,mods_rims), mods_interior = COALESCE(?,mods_interior), mods_other = COALESCE(?,mods_other), mods_ice = COALESCE(?,mods_ice), mods_aftermarket = COALESCE(?,mods_aftermarket), mods_wip = COALESCE(?,mods_wip), mods_overall = COALESCE(?,mods_overall) where car_id = ?`
let params = [req.body.email,req.body.name,req.body.year,req.body.make,req.body.model,req.body.racer_turbo,req.body.racer_supercharged,req.body.racer_performance,req.body.racer_horsepower,req.body.car_overall,req.body.engine_modificationsreq.body.engine_performancereq.body.engine_chromereq.body.engine_detailingreq.body.engine_cleanlinessreq.body.body_frame_undercarriagereq.body.body_frame_suspensionreq.body.body_frame_chromereq.body.body_frame_detailingreq.body.body_frame_cleanlinessreq.body.mods_paintreq.body.mods_bodyreq.body.mods_wrapreq.body.mods_rimsreq.body.mods_interiorreq.body.mods_otherreq.body.mods_icereq.body.mods_aftermarketreq.body.mods_wipreq.body.mods_overallreq.params.id]
db.run(sql, params, (err, row) => {
res.status(200).json({
message: 'success',
data: {
car_id: req.params.id,
updates: req.body
}
})
})
// Catch error
} catch(error) {
// Log the error to the console
console.log(error.message.red.bold)
// Return a server error code and message
res.status(500).json({
message: 'Not found'
})
}
})
該方法的作業原理是因為它更新了“racer_turbo”或“engine_chrome”等列的值。這些更改反映在資料庫中。但是，每個被修改的條目的分數列并沒有改變，它仍然顯示舊值。我不確定如何修改我的代碼以確保分數也更新。
這是我的 sqlite3 資料庫配置代碼，以防它有助于繪制更清晰的畫面。
// Importing sqlite3
const sqlite3 = require('sqlite3')
// Importing path
const path = require('path')
// Importing csvtojson
const csv = require('csvtojson')
// Path to config file
const config = require('../config')
// Path to data.csv file
const DATA_CSV = path.join(__dirname, '../data/data.csv')
// Enabling colors
config.colors.enable()
let db = new sqlite3.Database(config.database_name, (err) => {
// If we encounter an error while creating the database
if (err) {
// Log the error message
console.error(err.message.red)
// Throw the error as well
throw err
// Otherwise proceed with database creation
}
else {
// Alert the user that they've successfully connected to the database
console.log('Connected to database...'.blue)
// Creating the cars table within our database with the columns from the CSV file
db.run(`CREATE TABLE cars (
car_id INT PRIMARY KEY,
email TEXT UNIQUE,
name TEXT,
year INT,
make TEXT,
model TEXT,
racer_turbo INT,
racer_supercharged INT,
racer_performance INT,
racer_horsepower INT,
car_overall INT,
engine_modifications INT,
engine_performance INT,
engine_chrome INT,
engine_detailing INT,
engine_cleanliness INT,
body_frame_undercarriage INT,
body_frame_suspension INT,
body_frame_chrome INT,
body_frame_detailing INT,
body_frame_cleanliness INT,
mods_paint INT,
mods_body INT,
mods_wrap INT,
mods_rims INT,
mods_interior INT,
mods_other INT,
mods_ice INT,
mods_aftermarket INT,
mods_wip INT,
mods_overall INT,
score INT
)`,
(err) => {
// If we get an error
if (err) {
// Console log the eror
console.log(err.message.red)
}
// Otherwise add the data from our csv file to the database cars table
else {
// Use the csvtojson package to convert the csv data into json format
csv().fromFile(DATA_CSV)
.then(data => {
// SQL Insert query command to put data from the columns into the database table
let insert = 'INSERT INTO cars (Car_ID, Email, Name, Year, Make, Model, 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, Score) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
// Loop through all rows inside the csv file and add them into the table
for(const entry of data) {
// Calculating entry score
let score = [parseInt(entry.Racer_Turbo) parseInt(entry.Racer_Supercharged) parseInt(entry.Racer_Performance) parseInt(entry.Racer_Horsepower) parseInt(entry.Car_Overall) parseInt(entry.Engine_Modifications) parseInt(entry.Engine_Performance) parseInt(entry.Engine_Chrome) parseInt(entry.Engine_Detailing) parseInt(entry.Engine_Cleanliness) parseInt(entry.Body_Frame_Undercarriage) parseInt(entry.Body_Frame_Suspension) parseInt(entry.Body_Frame_Chrome) parseInt(entry.Body_Frame_Detailing) parseInt(entry.Body_Frame_Cleanliness) parseInt(entry.Mods_Paint) parseInt(entry.Mods_Body) parseInt(entry.Mods_Wrap) parseInt(entry.Mods_Rims) parseInt(entry.Mods_Interior) parseInt(entry.Mods_Other) parseInt(entry.Mods_ICE) parseInt(entry.Mods_Aftermarket) parseInt(entry.Mods_WIP) parseInt(entry.Mods_Overall)]
// Use the run function from sqlite database
db.run(insert, [
entry.Car_ID, entry.Email, entry.Name, entry.Year, entry.Make, entry.Model, entry.Racer_Turbo, entry.Racer_Supercharged, entry.Racer_Performance, entry.Racer_Horsepower, entry.Car_Overall, entry.Engine_Modifications, entry.Engine_Performance, entry.Engine_Chrome, entry.Engine_Detailing, entry.Engine_Cleanliness, entry.Body_Frame_Undercarriage, entry.Body_Frame_Suspension, entry.Body_Frame_Chrome, entry.Body_Frame_Detailing, entry.Body_Frame_Cleanliness, entry.Mods_Paint, entry.Mods_Body, entry.Mods_Wrap, entry.Mods_Rims, entry.Mods_Interior, entry.Mods_Other, entry.Mods_ICE, entry.Mods_Aftermarket, entry.Mods_WIP, entry.Mods_Overall,
score
]);
}
}).catch(err => {
// log any error we might encounter if any
console.log(err);
});
}
});
}
});
// Export our created database
module.exports = db
任何幫助都非常感謝，因為我很難過。
uj5u.com熱心網友回復：
如果您的 SQLite 版本是 3.31.0，您可以將該列定義score為生成的列：
CREATE TABLE cars (
car_id INT PRIMARY KEY,
email TEXT UNIQUE,
.......................
score INT GENERATED ALWAYS AS (col1 col2 .....) VIRTUAL -- or STORED if you want the value stored in the table
);
更改col1 col2 .....為計算列值的運算式score。
對于以前版本的 SQLite，要么使用觸發器，要么在UPDATE陳述句內部也更新列score。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/470340.html
上一篇：我有點困惑，為什么我的一些值在它們周圍有花括號，但其中一些沒有
下一篇：如何選擇總價值最高的物件組