Inserting json into postgresql using a for loop doesn't insert all my data?

Question

so I've been working on inserting my JSON data into google cloud postgresql for a few days now and am running across an issue where not even close to all of my data is inserted. Here is my code:

//prior to this i am connecting to cloud using pg and creating tables InterestClubs and FilterClubs

//alldata a json file, an array of about 3000 objects

let count = 0; //incrementing this every time i loop through

for(const club of alldata){
        client.query("INSERT INTO InterestClubs (name, clublink, university, description, logo, interests) VALUES ('"+club.title+"', '"+club.clubLink+"', '"+club.university+"', '"+club.descriptionSnippet+"', '"+club.logoLink+"', '"+club.interests+"')")

        client.query("INSERT INTO FilterClubs (name, clublink, university, description, logo, polfilters, relfilters, culfilters) VALUES ('"+club.title+"', '"+club.clubLink+"', '"+club.university+"', '"+club.descriptionSnippet+"', '"+club.logoLink+"', '"+club.politicalFilters+"', '"+club.religiousFilters+"', '"+club.culturalFilters+"')");

        count++;
    }

console.log(count); //outputs 3000 (or however many clubs there are in the json file)

I seem to be successfully looping through the data 3000 times (leading me to believe that I have inserted 3000 objects), but when I run a query such as SELECT * FROM InterestClubs (using either node/express or the cloud shell), I only receive 19 objects in return. I thought it may have something to do with having to wait a certain amount of time to allow the client.queries to successfully connect and insert, but when I used async/await (awaiting in front of each query), nothing changed. Also, I am getting this error every time I try and insert (after the count is printed)

3611
events.js:174
      throw er; // Unhandled 'error' event
      ^

error: syntax error at or near "s"
    at Connection.parseE (C:\Users\User\Documents\Code\Personal_Projects\clubhaus\node_modules\pg\lib\connection.js:539:11)
    at Connection.parseMessage (C:\Users\User\Documents\Code\Personal_Projects\clubhaus\node_modules\pg\lib\connection.js:366:17)
    at Socket.<anonymous> (C:\Users\User\Documents\Code\Personal_Projects\clubhaus\node_modules\pg\lib\connection.js:105:22)
    at Socket.emit (events.js:198:13)
    at addChunk (_stream_readable.js:288:12)
    at readableAddChunk (_stream_readable.js:269:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
Emitted 'error' event at:
    at Query.handleError (C:\Users\User\Documents\Code\Personal_Projects\clubhaus\node_modules\pg\lib\query.js:108:8)
    at Connection.emit (events.js:198:13)
    at Socket.<anonymous> (C:\Users\User\Documents\Code\Personal_Projects\clubhaus\node_modules\pg\lib\connection.js:109:12)
    at Socket.emit (events.js:198:13)
    [... lines matching original stack trace ...]
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)

This makes me think that something about the 19th object could be triggering a syntax error, causing the queries to stop inputting but still looping through them, but I'm not sure. Any help would be appreciated!!

Marcos Luis Delgado · Accepted Answer · 2019-09-04 23:28:31Z

1

Check if that 20th object has unescaped quote characters in one of the properties you are using. If u are using npm package pg you can automatically escape those characters passing variable arguments to your insert statements like this:

client.query("INSERT INTO InterestClubs (name, clublink, university, description, logo, interests) VALUES ($1, $2, $3, $4, $5, $6)", [club.title, club.clubLink, club.university, club.descriptionSnippet, club.logoLink, club.interests])

answered Sep 4, 2019 at 23:28

Marcos Luis Delgado

1,4498 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inserting json into postgresql using a for loop doesn't insert all my data?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related