INSERT JSON array into MYSQL Database with NodeJS

Question

i'm trying to pump a JSON array into a MYSQL Database, i already found something similiar but the Solution is not working for me. Similiar issue i found

Here is my try of first stringify the body from the Request and after that push that via

INSERT INTO table SET ?

Table is created like:

CREATE TABLE IF NOT EXISTS db.AStockHistory 
( Symbol text NOT NULL, 
Date timestamp NOT NULL, 
Open double, 
High double, 
Low double, 
Close double, 
Volume double, 
Dividends double, 
Stock_Splits double );

my Code from NodeJS:

async function create(stockHistory){

  var data = JSON.stringify(stockHistory);
  const result = await db.query(
    `INSERT INTO db.AStockHistory SET ?`, data); 
  return {message};
}

Here is my full JSON array i try to push via postman. The gereated structure of the JSON is made by a pandas dataframe.

 [
  {
    Symbol: 'msft',
    Date: '2022-07-20 00:00:00',
    Open: 259.8999938965,
    High: 264.8699951172,
    Low: 258.9100036621,
    Close: 262.2699890137,
    Volume: 22788300,
    Dividends: 0,
    Stock_Splits: 0
  },
  {
    Symbol: 'msft',
    Date: '2022-07-21 00:00:00',
    Open: 259.7900085449,
    High: 264.8900146484,
    Low: 257.0299987793,
    Close: 264.8399963379,
    Volume: 22404700,
    Dividends: 0,
    Stock_Splits: 0
  },
  {
    Symbol: 'msft',
    Date: '2022-07-22 00:00:00',
    Open: 265.2399902344,
    High: 265.3299865723,
    Low: 259.0700073242,
    Close: 260.3599853516,
    Volume: 21871000,
    Dividends: 0,
    Stock_Splits: 0
  },
  {
    Symbol: 'msft',
    Date: '2022-07-25 00:00:00',
    Open: 261,
    High: 261.5,
    Low: 256.8099975586,
    Close: 258.8299865723,
    Volume: 21056000,
    Dividends: 0,
    Stock_Splits: 0
  },
  {
    Symbol: 'msft',
    Date: '2022-07-26 00:00:00',
    Open: 259.8599853516,
    High: 259.8800048828,
    Low: 249.5700073242,
    Close: 251.8999938965,
    Volume: 38096200,
    Dividends: 0,
    Stock_Splits: 0
  }
]

I'm getting the following Output:

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 ''[{"Symbol":"msft","Date":"2022-07-20 00:00:00","Open":259.8999938965,' at line 1 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 ''[{"Symbol":"msft","Date":"2022-07-20 00:00:00","Open":259.8999938965,' at line 1 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 ''[{"Symbol":"msft","Date":"2022-07-20 00:00:00","Open":259.8999938965,' at line 1 at PromiseConnection.query

did you try db.query(`INSERT INTO db.AStockHistory VALUES (?)`, data);? — GrafiCode
– GrafiCode, Commented Aug 13, 2022 at 12:11
Use INSERT .. SELECT. In SELECT part parse your array to separate rows and column values using JSON_TABLE(). PS. Do not use DOUBLE for integer properties and TEXT for short strings. And, if date value is not local, use DATETIME (if timepart is always zero - use DATE). — Akina
– Akina, Commented Aug 13, 2022 at 13:22
@Akina thanks for the idea, i will try that as well. i just saw that this way should work, like i mentioned in the other stackoverflow Question. For that i just wanted to keep it simple. and push it without any further manipulation into the db. But yes ok now i try to getting it done by seperating. The table adjustments, good point. will check that. — Tungri
– Tungri, Commented Aug 13, 2022 at 20:35
Pay attention! Shown parameter value (array of objects) IS NOT valid JSON value from MySQL looking point. — Akina
– Akina, Commented Aug 13, 2022 at 20:41

Tungri · Accepted Answer · 2022-08-13 22:25:06Z

for me i found a workaround for my issue and now it works thanks @Akina

async function create(stockHistory){
    var response = [];
    for (let i = 0; i < stockHistory.length; i++) {
      var data = stockHistory[i]
      try {
        const result = await db.execute(
          `INSERT INTO db.AStockHistory (Symbol, Date, Open, High, Low, Close, Volume, Dividends, Stock_Splits) VALUES ('${data.Symbol}', '${data.Date}', ${data.Open}, ${data.High}, ${data.Low}, ${data.Close}, ${data.Volume}, ${data.Dividends}, ${data.Stock_Splits});`); 
      
        if (result.affectedRows) {
          response [i] = 'Stock History Symbol: '+ data.Symbol + ', Date: ' + data.Date +' created successfully';
        }
      } catch {
      response [i] = 'Error in creating Stock History for Symbol: '+ data.Symbol + ', Date: ' + data.Date;
      }
  }
  return {response};

Collectives™ on Stack Overflow

INSERT JSON array into MYSQL Database with NodeJS

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related