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 + ";"
