我可以动态地将一个数组插入到我从正则表达式匹配生成的 mysql 中吗？

Question

首先，如果标题不准确，我深表歉意，但我正在努力寻找能够准确描述我的问题的词语。我正在做一个项目，我试图记录我从爬虫编译的 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!");});

Answer 1

要插入多条记录，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);