def batch_insert(self,entrys,table,db,info_fields=INFO_FIELDS): LOG.info("Begin To Insert Into [%s.%s]..."%(db,table)) con = None large_values = [] for entry in entrys: value = [] for field in info_fields: value.append(entry.get(field)) large_values.append(value) values_part = ','.join(['%s'] * len(info_fields)) fields_part = ','.join(info_fields) sql = "insert into %s.%s (%s) values(%s)"%(db,table,fields_part,values_part) #设定一批插入的条数 count = 0 batch_size = BATCH_SIZE values = [] flag = False for i in xrange(0, len(large_values), batch_size): values = large_values[i:i+batch_size] try: con = self.get_connection() cur = con.cursor() cur.executemany(sql,values) con.commit() count += 1 except mdb.Error, e: LOG.error(e) flag = True finally:
def recr_table(self, table, database, t_table, t_database): LOG.info("Re-Create Table [%s.%s]"%(database,table)) sql = "DROP TABLE IF EXISTS %s.%s;" % (database, table) self.exe_sql(sql) sql = "CREATE TABLE %s.%s LIKE %s.%s;" % (database, table, t_database, t_table) self.exe_sql(sql)
def exe_sql(self,sql): con = None res = [] try: con = self.get_connection() cur = con.cursor() cur.execute(sql) res = list(cur.fetchall()) con.commit() except mdb.Error, e: LOG.error(e) LOG.error(traceback.format_exc())
def insert_entry(self,entry,table=TABLE,database=DB): if entry is None: LOG.error("None entry to insert!") sql = self.gen_ins_sql_stm(entry.keys(),table) con = None id = None try: con = self.get_connection(database=database) cur = con.cursor() cur.execute(sql,entry.values()) id = cur.lastrowid con.commit() except mdb.Error, e: LOG.error(traceback.format_exc())
def get_results_by_clms(self,columns,table,database=DB,isdict=False): res = [] cursor_type = MySQLdb.cursors.Cursor if columns is not None: if columns == '*': columns_str = '*' elif isinstance(columns,list): columns_str = ",".join(columns) else: LOG.error("Parameter colums type is not validated!") sys.exit(-1) limit = "" sql = "select " + columns_str + " from " + table + limit + ";" #LOG.debug(sql) else: LOG.error("IN Func[get_results_by_clms]:None columns!") return res con = None try: con = self.get_connection(database,isdict) cur = con.cursor() cur.execute(sql) res = cur.fetchall() except Exception,e: LOG.error(traceback.format_exc()) LOG.error(e)
def get_max_group_id(self,table,database): con = None sql = "select group_id from %s.%s\ order by group_id desc limit 1;" % (database,table) print sql try: con = self.get_connection(database=database) cur = con.cursor() cur.execute(sql) res = cur.fetchone() #deal the situation where table is empty or #without valid group_id if res is None: id = 0 else: id = res[0] except mdb.Error, e: LOG.error(traceback.format_exc())
def get_groupid_by_url(self,url,table,database): res = None url_list = [url] # 构建sql语句 start = "select group_id from " + table end = ";" cond = " where url in (%s)" in_p=', '.join(list(map(lambda x: '%s', url_list))) sql = start + cond + end sql = sql % in_p try: con = self.get_connection(database,isdict) cur = con.cursor() cur.execute(sql,id_list) res = cur.fetchone() except Exception,e: LOG.error(traceback.format_exc()) LOG.error(e) sys.exit(-1)
def sendmail(mailfrom,mailto, title, text): msg = MIMEMultipart() #text_msg = MIMEText(text, 'plain', 'utf-8') text_html = MIMEText(text, 'html') #msg.attach(text_msg) msg.attach(text_html) toad = mailto.split(";") msg['to'] = mailto msg['from'] = mailfrom msg['subject'] = title try: s = smtplib.SMTP() s.connect(HOST,PORT) s.login(USER, PASSWD) try: s.sendmail(msg['from'],toad,msg.as_string()) LOG.info("Send Email Successfully, To:%s"%msg['to']) except Exception,e: LOG.error(e) s.quit()
def get_id_by_name(self,name,table,database=DB): cur = None con = None start = "select ID from " + table end = ";" id = None if table == T_collegeInfo: cond = " where collegeName='%s' "%(name) if table == T_companyInfo: cond = " where companyName='%s' "%(name) sql = start + cond + end try: con = self.get_connection(database=database) cur = con.cursor() cur.execute(sql) res = cur.fetchone() if res is not None: id = res[0] except Exception,e: LOG.error(traceback.format_exc()) LOG.error(e)
def get_data_by_infoids(self,id_list,table,database=DB,isdict=False): res = [] con = None # the situation the id_list is empty if len(id_list) == 0: LOG.debug("IN Fuc[get_data_by_infoids]: id_list is empty!") return res cursor_type = MySQLdb.cursors.Cursor if isdict is True: cursor_type = MySQLdb.cursors.DictCursor # generate sql sentence start = "select * from " + table end = ";" cond = " where info_id in (%s)" in_p=', '.join(list(map(lambda x: '%s', id_list))) sql = start + cond + end sql = sql % in_p try: con = self.get_connection(database,isdict) cur = con.cursor() cur.execute(sql,id_list) res = list(cur.fetchall()) except Exception,e: LOG.error(traceback.format_exc()) LOG.error(e)
def sendmail(mailfrom,mailto, title, text): msg = MIMEMultipart() #text_msg = MIMEText(text, 'plain', 'utf-8') text_html = MIMEText(text, 'html') #msg.attach(text_msg) msg.attach(text_html) toad = mailto.split(";") msg['to'] = mailto msg['from'] = mailfrom msg['subject'] = title try: s = smtplib.SMTP() s.connect(HOST,PORT) s.login(USER, PASSWD) try: s.sendmail(msg['from'],toad,msg.as_string()) LOG.info("Send Email Successfully, To:%s"%msg['to']) except Exception,e: LOG.error(e) s.quit() except Exception,e: LOG.error(e) if __name__ == "__main__": mailfrom = '*****@*****.**' mailto = "[email protected];[email protected];[email protected]" title = unicode("何伟-测试","utf-8") text = "<font color='red'><b>hewei</b></font><br>asdfasdf" sendmail(mailfrom,mailto,title,text)
count += 1 except mdb.Error, e: LOG.error(e) flag = True finally: if con: con.close() if count == 0: if flag: total_num = 0 else: total_num = len(values) else: if flag: total_num = (count - 1) * batch_size else:total_num = (count - 1) * batch_size + len(values) LOG.info("Finish Inserting [%s] Entrys Has Been Done."%(total_num)) #@param columns 为"*",或者为list def get_results_by_clms(self,columns,table,database=DB,isdict=False): res = [] cursor_type = MySQLdb.cursors.Cursor if columns is not None: if columns == '*': columns_str = '*' elif isinstance(columns,list): columns_str = ",".join(columns) else: LOG.error("Parameter colums type is not validated!") sys.exit(-1) limit = "" sql = "select " + columns_str + " from " + table + limit + ";"