要插入多條記錄，SQL 格式類(lèi)似于：
INSERT INTO table_name (column_name_1, column_name_2, column_name_3)
VALUES
(?, ?, ?),
(?, ?, ?),
(?, ?, ?)
...
;
所以我們需要按照上面的格式構(gòu)造查詢，根據(jù)它我已經(jīng)用更改重寫(xiě)了你的部分代碼
var tweets__modified = tweets__contentText.match(/\b(\w+)\b/g);
console.log(tweets__modified);
// creating a string for numbers of records we want to create
var sql_insert_statement = tweets__modified.map((record) => '(?, ?, ?, ?, ?, ?, ?, ?)').join(', ');
// appending the above string to create a final SQL query
var insertStatement = `INSERT INTO ctdata ("tweets__singleWord", "tweets__contentText", "tweets__conversationId", "tweets__replies", "tweets__retweets", "tweets__favorites", "tweets__dateTime", "tweets__tweetId") values ${sql_insert_statement}`;
// creating a SQL query data in which
// we have all the columns data for a record
// times the number of recrods
var insertStatementItems = tweets__modified.reduce((acc, record) => {
acc = [...acc, record, tweets__contentText, tweets__conversationId, tweets__replies, tweets__retweets, tweets__favorites, tweets__dateTime, tweets__tweetId];
return acc; // was missing
}, []);
console.log({ insertStatement, insertStatementItems });
// Insert data of current row into database
db.query(insertStatement, insertStatementItems, (err, results, fields) => {
if (err) {
console.log("Unable to insert item at row ", i + 1);
return console.log(err);
}
});
注意：但是請(qǐng)注意，您的代碼還有另一個(gè)問(wèn)題，如果您期望console.log("All items stored into database successfully!");在所有插入操作之后打印這一行，那是錯(cuò)誤的。由于它是異步代碼，因此在執(zhí)行任何操作之前將首先打印此行。我添加了兩段代碼來(lái)解決這個(gè)問(wèn)題。
如果您的環(huán)境支持異步/等待，那么這段代碼可能對(duì)您有所幫助
const fileName = "items.csv";
csvtojson().fromFile(fileName)
.then(async (source) => {
// Console log initial CSV data
// console.log(source);
db_insert_promises = []
for (var i = 0; i < source.length; i++) {
var tweets__contentText = source[i]["tweets__contentText"],
tweets__conversationId = source[i]["tweets__conversationId"],
tweets__replies = source[i][" tweets__replies"],
tweets__retweets = source[i]["tweets__retweets"],
tweets__favorites = source[i]["tweets__favorites"],
tweets__dateTime = source[i]["tweets__dateTime"],
tweets__tweetId = source[i]["tweets__tweetId"]
var tweets__modified = tweets__contentText.match(/\b(\w+)\b/g);
console.log(tweets__modified);
var sql_insert_statement = tweets__modified.map((record) => '(?, ?, ?, ?, ?, ?, ?, ?)').join(', ');
var insertStatement = `INSERT INTO ctdata values ${sql_insert_statement}`;
var insertStatementItems = tweets__modified.reduce((acc, record) => {
acc = [...acc, record, tweets__contentText, tweets__conversationId, tweets__replies, tweets__retweets, tweets__favorites, tweets__dateTime, tweets__tweetId];
return acc; // was missing
}, []);
// Insert data of current row into database
db_insert_promise_for_tweet = await new Promise(function(resolve, reject) {
db.query(insertStatement, insertStatementItems, (err, results, fields) => {
if (err) return reject(err);
return resolve(results, fields);
});
});
}
console.log("All items stored into database successfully!");
})
.catch(console.error);
如果您的環(huán)境不支持異步/等待，那么這段代碼可能會(huì)對(duì)您有所幫助
const fileName = "items.csv";
csvtojson().fromFile(fileName)
.then(source => {
// Console log initial CSV data
// console.log(source);
db_insert_promises = []
for (var i = 0; i < source.length; i++) {
var tweets__contentText = source[i]["tweets__contentText"],
tweets__conversationId = source[i]["tweets__conversationId"],
tweets__replies = source[i][" tweets__replies"],
tweets__retweets = source[i]["tweets__retweets"],
tweets__favorites = source[i]["tweets__favorites"],
tweets__dateTime = source[i]["tweets__dateTime"],
tweets__tweetId = source[i]["tweets__tweetId"]
var tweets__modified = tweets__contentText.match(/\b(\w+)\b/g);
console.log(tweets__modified);
var sql_insert_statement = tweets__modified.map((record) => '(?, ?, ?, ?, ?, ?, ?, ?)').join(', ');
var insertStatement = `INSERT INTO ctdata values ${sql_insert_statement}`;
var insertStatementItems = tweets__modified.reduce((acc, record) => {
acc = [...acc, record, tweets__contentText, tweets__conversationId, tweets__replies, tweets__retweets, tweets__favorites, tweets__dateTime, tweets__tweetId];
return acc; // was missing
}, []);
// Insert data of current row into database
db_insert_promise_for_tweet = new Promise(function(resolve, reject) {
db.query(insertStatement, insertStatementItems, (err, results, fields) => {
if (err) return reject(err);
return resolve(results, fields);
});
});
db_insert_promises.push(db_insert_promise_for_tweet);
}
return Promise.all(db_insert_promises);
})
.then((result_of_all_insert_query) => {
console.log({ result_of_all_insert_query });
console.log("All items stored into database successfully!");
})
.catch(console.error);
