Sequelize -如何使用sequelize将csv数据插入mysql的多个表中

Question

我用的是

node.js, express, sequelize 6.3.3, fast-csv 4.3.1 and mysql database.

我所拥有的

我有一个CSV文件，文件的标题和数据如下。

no, name, email, township, father_name, father_phone, mother_name, mother_phone, parent_township

在这个CSV中，我有两种类型的数据：students和parents。

在我的MySQL数据库中，我有三个表：

students, parents and townships 

学生桌：

id, name, email, township_id

家长桌：

id, student_id, father_name, father_phone, mother_name, mother_phone, parent_township_id

乡镇桌：

id, name

我所做的

我用下面的代码读取带有fast-csv npm包的CSV。

let csvData = [];

fs.createReadStream(req.file.path)
    .pipe(
      csv.parse({
        ignoreEmpty: true,
        trim: true,
        skipLines: 6,
      })
    )
    .on("error", (error) => console.error(error))
    .on("data", (row) => {
      csvData.push(getHeaders(_.compact(row), csvStudentDataEntryFields));
    })
    .on("end", () => {
      fs.unlinkSync(req.file.path);

      const data = csvData.map((data) => {
        const student = _.pick(data, studentFields);
        const parent = _.pick(data, parentFields);
        return [student, parent];
      });

      return res.status(201).send({
        status: "success",
        data,
      });
    });

我得到了什么

使用上面的代码，我得到了具有以下值的data。

[
    [ // row 1 from CSV
        {
            "name": "John",
            "email": "john@gmail.com",
            "township": "NYC",
        },
        {
            "fatherName": "Smith",
            "fatherPhone": "9111111111",
            "motherName": "Mary",
            "motherPhone": "9111111111",
            "parentTownship": "NYC"
         }
    ],
    [ // row 2 from CSV
        {
            "name": "Cutter",
            "email": "cutter@gmail.com",
            "township": "NYC",
        },
        {
            "fatherName": "Laseli",
            "fatherPhone": "9111111111",
            "motherName": "Mary",
            "motherPhone": "9111111111",
            "parentTownship": "NYC"
        }
    ]
]

我想要的

我希望将这些row 1和row 2从data存储到数据库中的相应表中。

问题

我认为我需要用真正的id township 替换那些文本数据，因为我有如上所述的外键。

我怎样才能做到这一点？我想在数据库级别上这样做。我不想在单独的js模块中查找这个城镇名称的id。

更新的

学生模型

class Student extends Model {
    static associate(models) {
      Student.belongsTo(models.Township, {
        foreignKey: "townshipId",
        as: "township",
        targetKey: "townshipId",
      });

     Student.hasOne(models.Parent, {
        foreignKey: "studentId",
        as: "parent",
        sourceKey: "studentId",
      });
    }
  }

亲本模型

class Parent extends Model {
    static associate(models) {
      Parent.belongsTo(models.Student, {
        foreignKey: "studentId",
        as: "student",
        targetKey: "studentId",
      });

      Parent.belongsTo(models.Township, {
        foreignKey: "parentTownshipId",
        as: "township",
        targetKey: "townshipId",
      });
    }
  }

乡镇模式

class Township extends Model {
    static associate(models) {
      Township.hasMany(models.Student, {
        foreignKey: "townshipId",
        as: "township",
        sourceKey: "townshipId",
      });

      Township.hasMany(models.Parent, {
        foreignKey: "townshipId",
        as: "parentTownship",
        sourceKey: "townshipId",
      });
    }
  }

更新：

在我的控制器里，

let std = await models.Student.create(
    {
        nameEn: "John",
        email: "john@gmail.com",
        townshipId: 1,
        parent: [{ ...d[1], parentTownshipId: 1 }],
    },
    { include: ["parent"] }
);

Answer 1

我想你可以把所有的关系都插在一起。

这是从sequelize文档

const amidala = await User.create({
  username: 'p4dm3',
  points: 1000,
  profiles: [{
    name: 'Queen',
    User_Profile: {
      selfGranted: true
    }
  }]
}, {
  include: Profile
});

const result = await User.findOne({
  where: { username: 'p4dm3' },
  include: Profile
});

console.log(result);

设置源键

 const Foo = sequelize.define('foo', {
  name: { type: DataTypes.TEXT, unique: true }
}, { timestamps: false });
const Bar = sequelize.define('bar', {
  title: { type: DataTypes.TEXT, unique: true }
}, { timestamps: false });
const Baz = sequelize.define('baz', { summary: DataTypes.TEXT }, { timestamps: false });
Foo.hasOne(Bar, { sourceKey: 'name', foreignKey: 'fooName' });
Bar.hasMany(Baz, { sourceKey: 'title', foreignKey: 'barTitle' });
// [...]
await Bar.setFoo("Foo's Name Here");
await Baz.addBar("Bar's Title Here");

参考文献https://sequelize.org/master/manual/advanced-many-to-many.html#using-one-to-many-relationships-instead