How to insert json output into mysql with node js

Question

var express = require('express');
var fs = require('fs');
var mysql = require('mysql');
var request = require('request');
var cheerio = require('cheerio');
var bodyParser = require('body-parser');
var app     = express();

var output;
app.use(bodyParser.json())

app.get('/scrape', function(req, res){
    url = 'https://raitalumni.dypatil.edu/events/?tag=live';

    request(url, function(error, response, html){
        if(!error){
            var $ = cheerio.load(html);
            var json = {
                            title : [],
                            date  : [],
                            month : [],
                            venue : [],
                            link : []
                       };
             output = {
                            events : []
                         };

            $('p.event_name').each(function(){
                json.title.push($(this).text());
            });




            $('p.calendar_date').each(function(){
                json.date.push($(this).text());
            });

            $('span.calendar_month').each(function(){
                json.month.push($(this).text());
            });


            //var fulldate = $('p.calendar_date').concat($('p.calendar_day')).text();
            //console.log('all records: ' + fulldate);

            $('p.event_venue').each(function(){
                json.venue.push($(this).text());
            });

        // var title = $('p.event_name').each(function(){$(this).text()});



            for(var i=0; i<json.title.length; i++){
                output.events[i] = {
                    title : json.title[i],
                    date : json.date[i],
                    month : json.month[i],
                    venue : json.venue[i],
                    link : url
                }
            }

         var connection = mysql.createConnection({
        host: '127.0.0.1',
        port: 3306,
        user: 'root',
        password: '',
        database: 'raithub'
    });


connection.connect(function(error){
   if(!!error){
      console.log('Error');
   }else{
      console.log('Connected to the database!');
   }
});



       var scrape = JSON.stringify(output, null, 4);
       console.log(scrape);

       var query = connection.query("INSERT INTO scrapped ('title','date','month','venue','link') VALUES ('" + output.title + "', '" + output.date + "', '" + output.month + "', '" + output.venue + "', '" + output.link + "');", scrape, function(err, result) {
     if(err) throw err;
     console.log('data inserted');
});


            fs.writeFile('output4.json', JSON.stringify(output, null, 4), function(err){
                console.log('File successfully written! - Check your project directory for the output.json file');
            })

            res.send('Check your console!')         
        }
        else {
            console.log("Network Error, please try again later")
        }
    })
})





app.listen('8000')
console.log('Server running on port 8081');
exports = module.exports = app;

Where am I going wrong?

Getting this error,

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''title','date','month','venue','li
nk') VALUES ('undefined', 'undefined', 'undefi' at line 1 error

score 4 · Accepted Answer · answered Apr 03 '17 at 11:11

4

The INSERT statement should look this:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

where the column names should not be in quotes. Your specific statement would like this:

    var query = connection.query("INSERT INTO scrapped (title,date,month,venue,link) VALUES ('" + output.title + "', '" + output.date + "', '" + output.month + "', '" + output.venue + "', '" + output.link + "');", scrape, function(err, result) {
     if(err) throw err;
     console.log('data inserted');
});

See this for the right syntax

answered Apr 03 '17 at 11:11

Shiraz A.

157
1
3

This worked. thanks. but when I checked the database I'm getting 'undefined' value for all rows. – VChandra Apr 03 '17 at 11:25
Any idea why this happened? Also only one row is updated when there are two records in output. – VChandra Apr 03 '17 at 11:25
Thats probably because output.title is not a property. instead output.event[i].title might be because it looks like output has an array property called event and each of those has title etc property. And your query only does one query. You need to use a for loop to create a bigger query and that goes over all the elements in the output.events array – Shiraz A. Apr 03 '17 at 11:33
What should be the correct syntax? I want to use title, date etc to insert into different cols in mysql? – VChandra Apr 03 '17 at 11:34
Can you suggest how to code that? I'm new to node.js – VChandra Apr 03 '17 at 11:37
See this: http://stackoverflow.com/questions/8899802/how-do-i-do-a-bulk-insert-in-mysql-using-node-js – Shiraz A. Apr 03 '17 at 12:03
this way of JSON inserting you have to escape it manually by `connection.escape` – primee Sep 22 '18 at 15:03

score 1 · Answer 2 · answered May 25 '17 at 15:01

There is a JSON datatype in MySQL.

mysql> CREATE TABLE t1 (jdoc JSON);
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.01 sec)

This would be especially helpful if you had a very large json object and didn't want to create a huge INSERT statement. It would also be useful if you are unsure of the exact data that will be coming in via json but you want to capture all of it. I'm currently working on a project where we occasionally add and remove items from the user input fields and this is very helpful so that I'm not constantly having to ALTER tables and edit other statements in MySQL.

More information can be found here: https://dev.mysql.com/doc/refman/5.7/en/json.html

score 0 · Answer 3 · answered Feb 28 '22 at 02:42

0

const queryString = `insert into table_name(meta) values ('${JSON.stringify(meta)}');`;

answered Feb 28 '22 at 02:42

Hai Nguyen

1,675
19
14

Your answer could be improved by adding more information on what the code does and how it helps the OP. – Tyler2P Mar 01 '22 at 10:06

How to insert json output into mysql with node js

3 Answers3

Linked