[node.js] express 설정 db연동(API 불러와서 DB에 저장)

1. 프로젝트 경로에서 npm init --yes 명령어 입력

npm init --yes

2. 익스프레스 설치

npm install express mysql

3. pakage.json 서 아래와 같이 수정

{
  "name": "kair",
  "version": "0.0.1",
  "description": "",
  "main": "index.js",
  "scripts": {
    "start": "node index"
  },
  "dependencies": {
    "express": "^4.17.1",
    "mysql": "^2.18.1"
  }
}

4. 필요한 라이브러리 설치 

npm install node-schedule
npm install request
npm install axios

5. 디비 설정

//경로 ./config/database.js
"module.exports = {
  host     : 'localhost',
  user     : 'id',
  password : 'pw',
  database : 'test'
};"

6. index.js 수정

//index.js 수정
const express    = require('express');
const schedule = require('node-schedule');
const mysql      = require('mysql');
const dbconfig   = require('./config/database.js');
const connection = mysql.createConnection(dbconfig);
const request = require(""request"");
const app = express();

const axios = require('axios');

function apiCall(method, params, cb) {
    params.api_key = api_key;
    request.post({url: `${api_base}${method}`, form: params}, cb);
  }

//api사용관련 메서드  
async function getOpenApiData(url) { 
    try {
      const response = await axios.get(url);
      return response.data;
    } catch (error) {
      console.error(`Error: ${error}`);
      return null;
    }
  }
// configuration =========================
app.set('port', process.env.PORT || 3000);

app.get('/', (req, res) => {
  res.send('Root');
});

//localhost:3000/test?yyyy=2023&mmdd=0705&hhmm=1313
app.get('/test', (req, res) => {

    //let regNum=req.query.regNum;

    //let sql = 'SELECT * FROM air_line_info WHERE reg_number=?';
    let yyyy=req.query.yyyy;
    let mmdd=req.query.mmdd;
    let hhmm=req.query.hhmm;
    let sql ='select * from table_name where 1=1 and load_year =? and load_mmdd = ? and load_time like ?';
    let params = [yyyy,mmdd,hhmm+'%']; 
    console.log(params);
  connection.query(sql,params, (error, rows) => {
    if (error) throw error;
    res.send(rows);
  });
});




app.listen(app.get('port'), () => {

  console.log('Express server listening on port ' + app.get('port'));
  //타임스케줄
  schedule.scheduleJob('0 * * * * *', () => {
    console.log(new Date() + ' scheduler running!');
  
    const openApiUrl = 'https://apis.data.go.kr/B551177/StatusOfPassengerFlightsOdp/getPassengerDeparturesOdp?serviceKey=서비스키입력&from_time=0000&to_time=2400&flight_id=&airline=KE&lang=K&type=json';
    getOpenApiData(openApiUrl)
        .then(data => {

            let temp1 = data.response;

            console.log(temp1);
              for( i = 0 ; i<temp1.length;i++){
                console.log(temp1[i]);
                let sql =`INSERT INTO table_name (column1, column2, column3, column4, column5, column6, column7, column8, column9, column10, column11, column12, column13, load_date,load_year, load_mmdd, load_time)VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now() ,DATE_FORMAT(now(), '%Y'), DATE_FORMAT(now(), '%c%d'), DATE_FORMAT(now(), '%H%i%S') )`;
                let params = [temp1[i].1, temp1[i].2, temp1[i].3,temp1[i].4,temp1[i].5,temp1[i].6,temp1[i].7,temp1[i].8,temp1[i].9,temp1[i].10,temp1[i].11,temp1[i].12,temp1[i].13]; 
                connection.query(sql,params, (error, rows) => {
                  if (error){
                    console.log(error);
                  }
                  else{
                    console.log(rows);
                  }
                });
              }
        })  
        .catch(error => {
            console.error(`Error: ${error}`);
        });

    });

});

[참고: MySQL 연동 | PoiemaWeb]