CODEWITHSUNDEEP
node.jssqlite
Ranjan
Ranjan

Reputation: 71

Insert multiple rows into sqlite database at once

I want to insert a multiple rows into sqlite database using a single insert statement.

In Node JS the sqlite3 library allows us to do that referred from here.

But in this tutorial only one single column is there, I'm facing problem while inserting rows with multiple columns.

var csv = require('csv');
var fs = require('fs');
const sqlite3 = require('sqlite3').verbose();


// opening the database connection
let db = new sqlite3.Database('CSVtoDB_CSVParse//sample.db');

var parser = csv.parse({delimiter: ','}, function(err, data) {
    console.log(data);    
    languages = data;
    let placeholders = languages.map((language) => '(?,?)').join(',');
    let sql = 'INSERT INTO sample VALUES ' + placeholders;

    // output the INSERT statement
    console.log(sql);

    db.run(sql, languages, function(err) {
    if (err) {
        return console.error(err.message);
    }
    console.log(`Rows inserted ${this.changes}`);
    });

});

fs.createReadStream('CSVtoDB_CSVParse/sample1.csv').pipe(parser);

What I'm getting in the data object after parsing the CSV looks like this, 

[ [ 'id', 'name' ],
  [ '1', 'A' ],
  [ '2', 'B' ],
  [ '3', 'C' ],
  [ '4', 'D' ],
  [ '5', 'E' ],
  [ '6', 'F' ],
  [ '7', 'G' ],
  [ '8', 'H' ],
  [ '9', 'I' ],
  [ '10', 'J' ] ]

But after the insertion my database table's data appearing like this,

sqlite> select * from sample;
|
|
|
|
|
|
|
|
|
|
|

Output:

  [ '1', 'A' ],
  [ '2', 'B' ],
  [ '3', 'C' ],
  [ '4', 'D' ],
  [ '5', 'E' ],
  [ '6', 'F' ],
  [ '7', 'G' ],
  [ '8', 'H' ],
  [ '9', 'I' ],
  [ '10', 'J' ] ]
INSERT INTO sample VALUES (?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?),(?,?)
Rows inserted 11

Anyone faced issue like this? Thanks in advance for help.

Upvotes: 0

Views: 4270

Answers (1)

Maghalakshmi Saravana
Maghalakshmi Saravana

Reputation: 811

TO insert the username and password into the SqliteDB using Node js

login.html

<form action="auth" method="POST">
<input type="text" name="username" placeholder="Username" autocomplete="off" required>
<input type="password" name="password" placeholder="Password" autocomplete="off" required>
<input type="submit">   
</form>

login.js

    const sqlite3 = require('sqlite3').verbose();//getting the Sqlite3 DB
    var express = require('express');//require the Express framework
    var session = require('express-session');
    var bodyParser = require('body-parser');
    var path = require('path');
    let db = new sqlite3.Database('./NodeTest.db');//getting the DataBase from Databasename
    var app = express();
    app.use(session({
        secret: 'secret',
        resave: true,
        saveUninitialized: true
    }));
    app.use(bodyParser.urlencoded({extended : true}));
    app.use(bodyParser.json());    
    app.get('/', function(request, response) {
        response.sendFile(path.join(__dirname + '/login.html'));
    });
    app.post('/auth', function(request, response) {     
        var username = request.body.username;//getting the username from the request
        var password = request.body.password;//getting the password from the request
    db.run('Insert into Credentials(Username,Password) Values(?),(?)',[username,password],function(error){});
    response.end();
    });
    app.listen(3000);//port number is mentioned

Upvotes: -2

Related Questions