def main(): #黑名单 sql1 = """ select * from phone_black_list """ #黄牛党 sql2 = """ select * from phone_blacklist_fraud_group """ #消费金融 sql3 = """ select * from risk_number_label """ try: conn = pysql.conn_mysql() data = pd.read_sql_query(sql1,con = conn) data.to_csv(getTheFile('data/phone_black_list.csv'),index=False,encoding='utf_8_sig') data = pd.read_sql_query(sql2,con = conn) data.to_csv(getTheFile('data/phone_blacklist_fraud_group.csv'),index=False,encoding='utf_8_sig') data = pd.read_sql_query(sql3,con = conn) data.to_csv(getTheFile('data/risk_number_label.csv'),index=False,encoding='utf_8_sig') conn.close() except Exception,e: print "update_: Mysql Error %d: %s" % (e.args[0], e.args[1])
def getdata(sql): data = pd.DataFrame() try: conn = pysql.conn_mysql() data = pd.read_sql_query(sql, con=conn) conn.close() except MySQLdb.Error, e: log.log('数据库链接错误!', 'warning')
def wait_audit_user(start_time, end_time): sql = """ select user_id,merchant_id from ci_cash_submit_apply WHERE create_date >= "{}" and create_date < "{}" """ sql = sql.format(start_time, end_time) try: conn = pysql.conn_mysql() data = pd.read_sql_query(sql, con=conn) conn.close() except MySQLdb.Error, e: logging.warning("wait_audit: Mysql Error %d: %s" % (e.args[0], e.args[1]))
def extract_user_info(user_id): if type(user_id) == list: user_id = "','".join(user_id) sql = """select id 'user_id',name,id_num,phone,edu_level,marital_status,house_hold,company_nature,age,date_created from user where id in ('{}')""".format( user_id) data = pd.DataFrame() try: conn = pysql.conn_mysql() data = pd.read_sql_query(sql, con=conn) conn.close() except MySQLdb.Error, e: logging.warning("user_info: Mysql Error %d: %s" % (e.args[0], e.args[1]))
def extact_third_rawdata(user_id): if type(user_id) == list: user_id = "','".join(user_id) sql = """select user_id,trade_type,trade_time,call_time,receive_phone,call_type,created_time from third_mobile_tel_data where call_time is not null and receive_phone is not null and user_id in ('{}')""".format( user_id) data = pd.DataFrame() try: conn = pysql.conn_mysql() data = pd.read_sql_query(sql, con=conn) conn.close() except MySQLdb.Error, e: logging.warning("third_rawdata: Mysql Error %d: %s" % (e.args[0], e.args[1]))