MySQL批量写

秒速五厘米 2022-05-26 03:23 192阅读 0赞

import MySQLdb

def insert_no(self,lis):#批量写的方法
            sql="REPLACE INTO tm_devicestatuspointone_test(DeviceNumber,DeviceState,LocationType,DeviceLat,DeviceLng,BeginTime,EndTime,TimeSpan,PointSource,CheckDate) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            self.cursor.executemany(sql,lis)
            self.con.commit()
    
        def insert(self,lis):
            sql="INSERT INTO tm_devicestatuspoint_test(Id,DeviceNumber,DeviceState,LocationType,DeviceLat,DeviceLng,BeginTime,EndTime,TimeSpan,PointSource,CheckDate) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            self.cursor.executemany(sql,lis)
            self.con.commit()
        def ext_dealjson(self,ss):
             lis=[]
             lis1=[]
             num =0
             for i in range(0,len(ss)):
                       ids=self.ids
                       dt_start = datetime.datetime.fromtimestamp(ss[i]['starttime'])
                       dt_end = datetime.datetime.fromtimestamp(ss[i]['endtime'])
                       str_starttime = dt_start.strftime("%Y-%m-%d %H:%M:%S")
                       str_endtime = dt_end.strftime("%Y-%m-%d %H:%M:%S")
                       str_checkdate = datetime.datetime.fromtimestamp(ss[i]['t']).strftime("%Y-%m-%d")
                       if ss[i]['endtime']==0:#根据字段分表
                           timespan=0
                           data2=(ss[i]['imei'],ss[i]['status'],ss[i]['loctype'],'%0.7f' % ss[i]['startlat'],'%0.7f' % ss[i]['startlng'],str_starttime,str_endtime,timespan,-1,str_checkdate)
                           lis1.append(data2)
                       else:
                           timespan = ss[i]['endtime'] - ss[i]['starttime']
                           et=1
                           data=(ids,ss[i]['imei'],ss[i]['status'],ss[i]['loctype'],'%0.7f' % ss[i]['startlat'],'%0.7f' % ss[i]['startlng'],str_starttime,str_endtime,timespan,-1,str_checkdate)
                           lis.append(data)
                           self.ids+=1
             if 1:
                       self.insert(lis)#执行批量写方法
                       self.insert_no(lis1)
                       lis=[]
                       lis1=[]