python - 在Python中批量插入MySQL

Question

我目前有一些代码，它获取数据帧中的第一个值并将其插入MySQL，或更新它(取决于id是否已经在数据库中)。

但是，我需要能够创建一个循环来循环数据帧中的所有值，而不是仅循环一个值。但我不知道该怎么做。

这是我的代码，仅用于一个值:

class Example(db.Model):
        __tablename__ = 'sessionAttendances'
        _id = db.Column('_id', db.Unicode, primary_key=True)
        wondeID = db.Column('wondeID', db.Unicode)
        date = db.Column('date', db.Unicode)
        timezoneType = db.Column('timezoneType', db.Unicode)
        timezone = db.Column('timezone', db.Unicode)
        createdAt = db.Column('createdAt', db.Date)
        session = db.Column('session', db.Unicode)
        updatedAt = db.Column('updatedAt', db.Date)

        def __init__(self, _id, wondeID, date, timezoneType, timezone, createdAt, session, updatedAt):
            self._id = _id
            self.wondeID = wondeID
            self.date = date
            self.timezoneType = timezoneType
            self.timezone = timezone
            self.createdAt = createdAt
            self.session = session
            self.updatedAt = updatedAt

        @classmethod
        def add_or_update(cls, _id, wondeID, date, timezoneType, timezone, createdAt, session, updatedAt):
            entity = cls.query.filter_by(_id=sessionAttendance._id.iloc[0]).first()

            if not entity:
                entity = cls(sessionAttendance._id.iloc[0], sessionAttendance.wondeID.iloc[0], sessionAttendance.date.iloc[0], sessionAttendance.timezoneType.iloc[0], sessionAttendance.timezone.iloc[0], sessionAttendance.createdAt.iloc[0], sessionAttendance.session.iloc[0], sessionAttendance.updatedAt.iloc[0])
                db.session.add(entity)
                db.session.commit()
                print("Adding Record")
            else:
                entity.attendanceCode = 'late'
                db.session.commit()
                print("Updating Record")

            return entity

example = Example(sessionAttendance._id.iloc[0], sessionAttendance.wondeID.iloc[0], sessionAttendance.date.iloc[0], sessionAttendance.timezoneType.iloc[0], sessionAttendance.timezone.iloc[0], sessionAttendance.createdAt.iloc[0], sessionAttendance.session.iloc[0], sessionAttendance.updatedAt.iloc[0])
example.add_or_update(sessionAttendance._id.iloc[0], sessionAttendance.wondeID.iloc[0], sessionAttendance.date.iloc[0], sessionAttendance.timezoneType.iloc[0], sessionAttendance.timezone.iloc[0], sessionAttendance.createdAt.iloc[0], sessionAttendance.session.iloc[0], sessionAttendance.updatedAt.iloc[0])

examples = Example.query.all()
for ex in examples:
    print (ex.date)

Answer 1

显然，用于批量更新插入的 MySQL 方法相当慢，因此循环可能是您的最佳选择。

以下是使用 .iterrows() 的方法:

for idx,row in sessionAttendance.iterrows():
    example = Example(row._id, row.wondeID, row.date, row.timezoneType, 
                      row.timezone, row.createdAt, row.session, row.updatedAt)
    example.add_or_update(row._id, row.wondeID, row.date, row.timezoneType, 
                          row.timezone, row.createdAt, row.sessio, row.updatedAt)