在Python中大容量插入MySQL

Question

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

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

这是我的一个值的代码：

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)