CODEWITHSUNDEEP
javascriptmysqljsonnode.js
VChandra
VChandra

Reputation: 29

How to insert json output into mysql with node js

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

Upvotes: 2

Views: 27361

Answers (3)

Hai Nguyen
Hai Nguyen

Reputation: 1833

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

Where meta is a JSON object, we need to convert it to a JSON tring by using JSON.stringify first then insert the value as JSON string.

Upvotes: 2

Glen Pierce
Glen Pierce

Reputation: 4841

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

Upvotes: 1

Shiraz A.
Shiraz A.

Reputation: 157

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

Upvotes: 4

Related Questions