首先，如果标题不准确，我深表歉意，但我正在努力寻找能够准确描述我的问题的词语。我正在做一个项目，我试图记录我从爬虫编译的 Twitter 信息，目前是 CSV 格式。我使用 csvtojson 将该信息转换为 JSON，但我遇到了问题。我试图将推文中的每个词单独分开，然后将 CSV 中该特定行的其余信息应用于该特定词。这是“成功”结果的示例：我只是不确定如何拆分我创建的数组，我可以在其中将每个单词作为一个新行应用到数组中。我假设我需要分开插入数据的方式，但我不确定该怎么做？const fileName = "items.csv";csvtojson().fromFile(fileName).then(source => {// Console log initial CSV data// console.log(source);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 insertStatement = `INSERT INTO ctdata values(?, ?, ?, ?, ?, ?, ?, ?)`; var items = [tweets__modified, tweets__contentText, tweets__conversationId, tweets__replies, tweets__retweets, tweets__favorites, tweets__dateTime, tweets__tweetId]; // Insert data of current row into database db.query(insertStatement, items, (err, results, fields) => { if (err) { console.log("Unable to insert item at row ", i + 1); return console.log(err); } });}console.log("All items stored into database successfully!");});
1 回答
芜湖不芜
TA贡献1796条经验 获得超7个赞
要插入多条记录，SQL 格式类似于：
INSERT INTO table_name (column_name_1, column_name_2, column_name_3)
VALUES
(?, ?, ?),
(?, ?, ?),
(?, ?, ?)
...
;
所以我们需要按照上面的格式构造查询，根据它我已经用更改重写了你的部分代码
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);
}
});
注意：但是请注意，您的代码还有另一个问题，如果您期望console.log("All items stored into database successfully!");在所有插入操作之后打印这一行，那是错误的。由于它是异步代码，因此在执行任何操作之前将首先打印此行。我添加了两段代码来解决这个问题。
如果您的环境支持异步/等待，那么这段代码可能对您有所帮助
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);
如果您的环境不支持异步/等待，那么这段代码可能会对您有所帮助
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);
添加回答
举报
0/150
提交
取消