python实例pyspark以及python中文显示

柔光的暖阳◎ 2022-06-09 09:06 162阅读 0赞

%pyspark  
  
  
\#查询认证用户  
  
  
import sys  
\#import MySQLdb  
import mysql.connector  
import pandas as pd  
import datetime  
import time  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'G4d',  
                'dbhost' : '172.12.112.5',  
                'dbport' : 3306,  
                'dbname' : 'GMDB'  
                 \}  
  
def sql\_select(reqsql):  
    ret = ''  
    try:  
        db\_conn = mysql.connector.connect(user=optmap\['dbuser'\], password=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], database=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        count = db\_cursor.execute(reqsql)  
        ret = db\_cursor.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor.close()  
        db\_conn.close  
        return ret  
  
  
userlist = \[\]  
def renzhengsingger(startday,endday):  
    t1 = int(time.mktime(time.strptime(startday,'%Y-%m-%d %H:%M:%S')) )  
    t2 = int(time.mktime(time.strptime(endday,'%Y-%m-%d %H:%M:%S')))  
     for n in range(0,10):  
        reqsql = "select PERFORMERID,sum(DURATION)/3600 from PERFORMERSHOWTIMERECORD%d where STARTTIME >=%s and STARTTIME <%s group by PERFORMERID ;" %(n,t1,t2)  
        ret = sql\_select(reqsql)  
          
         userlist.append(ret)  
    \#print userlist      
     for i in range(0,10):  
        for p in userlist\[i\]:  
            print p\[0\],p\[1\]  
              
renzhengsingger('2017-08-01 00:00:00','2017-09-01 00:00:00')    
  
  
  
  
======================================================================================================================  
  
%pyspark  
  
\#查询认证用户  
  
import sys  
\#import MySQLdb  
import mysql.connector  
import pandas as pd  
import datetime  
import time  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'G4d',  
                'dbhost' : '172.12.112.8',  
                'dbport' : 3306,  
                'dbname' : 'IMDB'  
                 \}  
  
optmap1 = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'G4d',  
                'dbhost' : '172.12.112.5',  
                'dbport' : 3306,  
                'dbname' : 'GMDB'  
                 \}  
  
  
def sql\_select(reqsql):  
    ret = ''  
    try:  
        db\_conn = mysql.connector.connect(user=optmap\['dbuser'\], password=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], database=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        count = db\_cursor.execute(reqsql)  
        ret = db\_cursor.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor.close()  
        db\_conn.close  
        return ret  
  
def sql\_select1(reqsql1):  
    ret = ''  
    try:  
        db\_conn1 = mysql.connector.connect(user=optmap1\['dbuser'\], password=optmap1\['dbpass'\], host=optmap1\['dbhost'\], port=optmap1\['dbport'\], database=optmap1\['dbname'\])  
        db\_cursor1=db\_conn1.cursor()  
        count = db\_cursor1.execute(reqsql1)  
        ret1 = db\_cursor1.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor1.close()  
        db\_conn1.close  
        return ret1  
  
  
\#定义查询认证用户函数  
def renzhengsingger(startday,endday):  
    t1 = int(time.mktime(time.strptime(startday,'%Y-%m-%d %H:%M:%S')) )  
    t2 = int(time.mktime(time.strptime(endday,'%Y-%m-%d %H:%M:%S')))  
    reqsql = "select PERFORMERID,from\_unixtime(ADDTIME) from PERFORMERINFO where ADDTIME >=%s and ADDTIME < %s" %(t1,t2)  
    ret = sql\_select(reqsql)  
    for i in ret:  
        \#print i\[0\]  
         id = int(i\[0\])%10  
        reqsql1 = "select sum(DURATION)/3600 from PERFORMERSHOWTIMERECORD%d where STARTTIME >=%s and STARTTIME <%s and PERFORMERID=%d" %(id,t1,t2,i\[0\])  
        \#print reqsql1  
         ret1 = sql\_select1(reqsql1)  
        print i\[0\],",",i\[1\],",",ret1\[0\]\[0\]

renzhengsingger('2017-08-01 00:00:00','2017-09-01 00:00:00')

================================================================================================  
%pyspark  
  
\#encoding=gbk  
\#-\*-coding:gbk-\*-  
  
import sys  
\#import MySQLdb  
import mysql.connector  
import pandas as pd  
import datetime  
import time  
  
optmap = \{  
                'dbuser' : 'haoren',  
                'dbpass' : 'G4d',  
                'dbhost' : '172.17.12.8',  
                'dbport' : 3306,  
                'dbname' : 'IMDB'  
                 \}  
  
optmap1 = \{  
                'dbuser' : 'haoren',  
                'dbpass' : 'G4d',  
                'dbhost' : '172.17.12.5',  
                'dbport' : 3306,  
                'dbname' : 'GMDB'  
                 \}  
  
def sql\_select(reqsql):  \#定义数据库连接执行函数  
    ret = ''  
    try:  
        db\_conn = mysql.connector.connect(user=optmap\['dbuser'\], password=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], database=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        count = db\_cursor.execute(reqsql)  
        ret = db\_cursor.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor.close()  
        db\_conn.close  
        return ret  
  
  
def sql\_select1(reqsql1):   \#定义数据库连接执行函数  
    ret = ''  
    try:  
        db\_conn1 = mysql.connector.connect(user=optmap1\['dbuser'\], password=optmap1\['dbpass'\], host=optmap1\['dbhost'\], port=optmap1\['dbport'\], database=optmap1\['dbname'\])  
        db\_cursor1=db\_conn1.cursor()  
        count = db\_cursor1.execute(reqsql1)  
        ret1 = db\_cursor1.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor1.close()  
        db\_conn1.close  
        return ret1  
  
  
\#批量查询消费者的昵称  
  
def getnickname(uid):    \#定义函数  
    id = int(uid)%10  
    reqsql = "select CHANNELNICKNAME from  CHARBASE%d where ID=%d" %(id,uid)    \#数据库字段可能含有中文，需要处理后方可正常显示  
    \#reqsql = "select NICKNAME from  CHARBASE%d where ID=%d" %(id,uid)  
    ret = sql\_select(reqsql)  
    return ret  
  
userlist = \[49072058,54332450,23154709\]         \#定义一个字典  
  
for i in userlist:  
    \#查询消费者昵称  
    ret1 = getnickname(i)  
    ss = ret1\[0\]\[0\]  
    ss = ss.encode('unicode-escape').decode('string\_escape')  \#开始处理带有中文的字符  
    nick\_name = ss.decode('gbk')         \#解码后方可正常显示  
    print i ,nick\_name  
      
  
=====================================================================================================  
\#!/usr/bin/python  
\#-\*-coding:utf-8-\*-  
  
import sys  
import MySQLdb  
\#import pandas as pd  
import datetime  
import time  
  
optmap = \{  
                'dbuser' : 'haoren',  
                'dbpass' : 'qwomet',  
                'dbhost' : '172.17.12.8',  
                'dbport' : 3306,  
                'dbname' : 'IMDB'  
                 \}  
  
  
optmap1 = \{  
                'dbuser' : 'haoren',  
                'dbpass' : 'qwomet',  
                'dbhost' : '172.17.12.5',  
                'dbport' : 3306,  
                'dbname' : 'GMDB'  
                 \}  
  
  
def sql\_select(reqsql):  
    try:  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname'\])  
        count = db\_cursor.execute(reqsql)  
        ret = db\_cursor.fetchall()   \#获取结果集  
        db\_cursor.close()  
        db\_conn.close  
        return ret  
    except MySQLdb.Error,e:  
        print "Mysql ERROR %d:%s"  %(e.args\[0\], e.args\[1\])  
    return ''  
  
  
\#或者这样写代替MySQLdb模块的写法  
import mysql.connector  
  
def sql\_select(reqsql):  
    ret = ''  
    try:  
        db\_conn = mysql.connector.connect(user=optmap\['dbuser'\], password=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], database=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        count = db\_cursor.execute(reqsql)  
        ret = db\_cursor.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor.close()  
        db\_conn.close  
        return ret  
  
\#遍历文件夹  
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'                                
  
def main():   
\#显示昨天的时间    例如 20170920  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
        print "cur\_day=" ,cur\_day  
  
\#换一种格式显示昨天的时间  例如 170920  
        log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        print "log\_day=" ,log\_day  
  
        if len(sys.argv)>1:  
                cur\_day = sys.argv\[1\]  
                log\_day = sys.argv\[1\]\[2:\]  
  
        tabletime = cur\_day  
        print "tabletime=" ,tabletime  
  
\#截取  例如 2017-09-20  
        date = str(tabletime\[0:4\]) + "-" + str(tabletime\[4:6\]) + "-" + str(tabletime\[6:\])  
        print "date=" ,date  
  
         \#显示0点0分0秒  例如2017-09-20 00:00:00  
        datetmp = date + " 00:00:00"  
        print "datetmp=" ,datetmp  
  
         \#显示时间数组，例如 time.struct\_time(tm\_year=2017, tm\_mon=9, tm\_mday=20, tm\_hour=0, tm\_min=0, tm\_sec=0, tm\_wday=2, tm\_yday=263, tm\_isdst=-1)  
        timeArray = time.strptime(datetmp,"%Y-%m-%d %H:%M:%S")  
        print "timeArray=" ,timeArray  
  
        \#显示时间戳 例如 1505836800  
        timeStamp = int(time.mktime(timeArray))  
        print "timeStamp=" ,timeStamp  
main()