def getmid(lid): sql_str = ''' select message_id from message_list ml where ml.list_id = %s ''' global_conn = mdb.get_global_conn() message_id = mdb.exe_sql(global_conn, sql_str % lid, False, True) return message_id[0][0]
def getcid(mid): sql_str = ''' select m.client_id, c.client_name from message m, client c where m.client_id = c.client_id and m.message_id = %s ''' global_conn = mdb.get_global_conn(); client_id = mdb.exe_sql(global_conn, sql_str % mid, False, True) return client_id[0][0]
def get_group_mail(group_id): get_group_sql = ''' select mg.group_name,m.client_id,group_concat(m.message_id) as messages from message m, message_group mg where mg.group_id = m.group_id and m.group_id = %s group by m.group_id; ''' global_conn = mdb.get_global_conn(); datas = mdb.exe_sql(global_conn, get_group_sql % (group_id), True, True); return datas
def getUserInfo(cid=None, email=None): get_user_info = ''' select u.client_id, u.email_address, u.password, c.client_name, c.branch_id from users u,client c where u.client_id = c.client_id and ''' if cid: get_user_info += ''' u.client_id = '%s' ''' % cid if email : get_user_info += '''u.email_address = '%s' ''' % email global_conn = mdb.get_global_conn(); datas = mdb.exe_sql(global_conn, get_user_info, True, True) return datas
def insertMachine(ip, server_type): sql_machine = ''' INSERT INTO `machine` (`ip`, `ssh_port`, `machine_group`, `passwd`, `remark`) VALUES ("%s", 22, "%s", 'WcLPHVvGyY327E8fRSlGRV14MBuM0skG0tN/mPpmxvX/L+YyTlSPzWGq/BIhtmyTvPAwqX4ewszLdySr1uqN6q3otreTNBja0pcB2FREdFjzcX31KjuX9u4F6UNUjZHk+bQ7w4fUDQ5VEhmVASsxxtIk/xouRFLPL2LkFagi4OI=', now()); ''' sql_service = ''' INSERT INTO `service` (`server_id`,`machine_ip`,`server_type`,`server_port`,`status`,`pressure`,`launch_on`,`shutdown_on`,`refresh_on`,`doSuspend`,`doStop`) VALUES ("%s", "%s", "%s", %s, 'down', 0, now(), now(), now(), 1, 1); ''' port=service_dict[server_type] server_id=ip.replace('.','')+port try: global_conn = mdb.get_global_conn(); mdb.exe_insert_sql(global_conn, sql_machine % (ip, server_type), False, False) mdb.exe_insert_sql(global_conn, sql_service % (server_id, ip, server_type, port), False, True) except Exception,e: print "Insert machine info failed!" sys.exit()
def get_bounceinfo(cid, mid, email=None, test = False): get_data = ''' select * from msg_%s_%s_h h where h.email = '%s' ''' get_test_data = ''' select * from msg_tester_result mr where mr.message_id = %s ''' if email : get_test_data += " and mr.email_address = '%s'" % (email) if test : sql = get_test_data % (mid) global_conn = mdb.get_global_conn() datas = mdb.exe_sql(global_conn, sql, True, True) else : sql = get_data % (cid, mid, email) bounce_conn = mdb.get_bounce_conn(cid); datas = mdb.exe_sql(bounce_conn, sql, True, True) # print datas for data in datas: for key,values in data.iteritems(): print '%-20s %s' %(key, values) print '-'*50
def getActiveService(server): sql_str = '''select group_concat(machine_ip) as ips from service s where s.status="active" and s.server_type = "%s" ''' global_conn = mdb.get_global_conn(); ips = mdb.exe_sql(global_conn, sql_str % server, True, True) return ips[0]['ips'].split(',')
def getBranchdomain(branch_id): sql = '''select bd.domain_name from branch_domain bd where bd.branch_id = %s ''' global_conn = mdb.get_global_conn(); datas = mdb.exe_sql(global_conn, sql % branch_id, True, True) return datas
def getipid_province(province): sql_str = "select place_id from ip_place ip where province='%s' order by place_id limit 1" global_conn = mdb.get_global_conn(); ip_place_id = mdb.exe_sql(global_conn, sql_str % province, False, True) return ip_place_id[0][0]
def getipid_city(city): sql_str = "select place_id from ip_place ip where city='%s' order by place_id limit 1 " global_conn = mdb.get_global_conn(); ip_place_id = mdb.exe_sql(global_conn, sql_str % city, False, True) return ip_place_id[0][0]