某讯公司Python开发面试题

某讯公司面试题

问题2.本机装有mysq1,root用户可以无密码访问其默认端口,
有一个名为 student的库里面，有张名为 user_info的表,字段有name\sex\age三个字段
要求:请编写 python函数 get_user_statisic() 统计user_info中各年龄区间段的人数,
区间段为(0,10],(10,20],(20,00]),
并按如下格式返回json数据{“0-10”:“xx”,10~20″:“xx”,“20+”:“xx”}

import pymysql


def get_user_statisic():

    db = pymysql.connect(
        host='localhost',
        port=3306, user='root',
        passwd='123456', db='mylianxi_527', charset='utf8'
    )
    # 创建游标对象
    cursors = db.cursor()
    # 设置SQL查询语句
    sql1 = 'select * from student where age between 0 and 10;'
    sql2 = 'select * from student where age between 11 and 20;'
    sql3 = 'select * from student where age>20;'
    list1 = [sql1, sql2, sql3]
    count = []
    for i in list1:
        cursors.execute(i)          # 使用execute() 方法执行sql查询
        # cursors.close()           # 关闭
        # data = cursors.fetchall() # 使用fetchall() 方法获取所有数据，可以通过遍历获取单条数据
        counts = cursors.rowcount   # 使用 rowcount 方法可以得到查询语句得到的条数
        count.append(counts)

    data = {"0-10": count[0], "10-20": count[1], "20+": count[2]}   #  {"0-10":"xx","10~20″:"xx","20+":"xx"}
    jsons = json.dumps(data, ensure_ascii=True, indent=2)
    print(jsons)


get_user_statisic()

问题3:下面是一段 apache访问日志,假设名称为 access.log
172.16.211.50–[10/sep/2008: 08: 55: 47+0800]“post/package/server/SerVer. php HttP/1.1” 200 1112
172.16.211.50–[10/sep/2008: 08: 55: 48 +0800]“post/package/server/serveR. php Http/1.1” 200 2323
172.30.38.61–[10/Sep/2008:08:55:49+0800]“GET/ frame/ image/ head bg. gif Http/1.1” 300 12112
172.30.38.61–[10/Sep/2008:08:55:50+0800]“GET/ frame/ Image/ logout. gif Http/1.1” 200 2323
172.16.211.50–[10/sep/2008: 08: 55: 50 +0800]“post/package/server/servEr. php Http/1.1” 200 1112
每个域的含义是:访问IP–[时间]“ POST GET资源HTP协议版本” apache返回码 返回流量节数
要求:请写 python代码读取日志按小时统计访问单IP的访问次数和单IP总流量(当http返回码是200时计算流量)

import time

pin = 0
while True:
    ips = []
    liangs = []
    httpms = []
    lines = []

    fr = open('access.log')
    # print(fr.readlines()[0])
    fr.seek(pin)
    # 取出单行日志数据
    for i, line in enumerate(fr):
        ip = line.split('--')[0]    # 切割字符串，获取ip
        liang = line.split(' ')[-1].strip()
        httpm = line.split(' ')[-2].strip()

        lines.append(line)
        ips.append(ip)
        liangs.append(liang)
        httpms.append(httpm)

    set_ips = set(ips)  # 对ip进行去重
    print(set_ips)
    for i, n in enumerate(set_ips):
        counts = ips.count(n)   # 查询一个列表中某个元素出现的次数
        print(n, counts)        # 打印单小时ip的访问次数

        if counts == 1:                  # 考虑 ip是否重复访问的问题
            httpm = int(httpms[ips.index(n)])
            if httpm == 200:
                ip_liang = liangs[ips.index(n)]                    # 取得一条日志数据的ip流量
                print('%s -- %s' % (n, ip_liang))
        elif counts > 1:
            index_ip = [x for x, y in enumerate(ips) if y == n]     # 求得所有相同ip的索引
            ip_liang = 0
            for m in index_ip:
                httpm = int(httpms[m])
                if httpm == 200:
                    ip_liang += int(liangs[m])

            print('%s -- %s' % (n, ip_liang))
        print('--------------------')
        
    pin = fr.tell()  # 记录读完的指针位置
    time.sleep(60*60)