python mariadb数据库 openpyxl模块，json模块，itchat模块练习

最新推荐文章于 2019-04-03 19:00:00 发布

MAGICbrook

最新推荐文章于 2019-04-03 19:00:00 发布

阅读量248

点赞数

分类专栏： python

本文链接：https://blog.csdn.net/qq_41829386/article/details/86532826

版权

python 专栏收录该内容

29 篇文章 2 订阅

订阅专栏

1. 面向对象，文件操作与数据库操作复习题目:

文件score.dat中保存的是100名学生的姓名和Python课、高数和英语成绩。
1）定义学生类，其中包含姓名、Python课、高数和英语成绩及总分、均分数据成员，成员函数根据需要确定。
2）读入这名学生的成绩，用对象列表进行存储。
3）求出各科和总分的最高分。
4）请按总分的降序（高成绩在前，低成绩在后）排序
5）在屏幕上显示各科及总分的最高分，排序后的成绩单（包括总分）保存到文件odered_score.dat中。
6）将文件中的所有学生信息，保存在mariadb数据库中;

import  random
import pymysql
def create_information(filename='score.dat'):
    information = ['name-python-math-english']+['name'+str(i)+'-'+str(random.randint(0,100))+'-'+str(random.randint(0,100))+'-'+str(random.randint(0,100))for i in range(100)]
    with open(filename,mode='w+') as f:
        for items in information:
            f.write(items+'\n')
# create_information()
def read_information(filename='score.dat'):
    with open(filename) as f:
        readlise=f.readlines()
        return ([i[0].split('-')for i in[items.split()for items in readlise]][1:])

# read_information()
class Student(object):
    def __init__(self,list):
        self.total_points = 0
        self.average_value = 0
        self.list = list

    def calculation_points(self):
        for items in self.list:
            self.total_points = int(items[1]) + int(items[2]) + int(items[3])
            self.average_value = self.total_points/3
            items.append(self.total_points)
            items.append(self.average_value)

    def high_points(self):
        self.high_python = max([int(i[1])for i in self.list])
        self.high_math = max([int(i[2])for i in self.list])
        self.high_english = max([int(i[3])for i in self.list])
        self.high_total_points = max([int(i[4])for i in self.list])
        print('python的最高分：%d\nmath的最高分：%d\nenglish的最高分：%d\n总分的最高分：%d\n' %(self.high_python,self.high_math,self.high_english,self.high_total_points))

    def sorte(self):
        return sorted(list,key=lambda x:-x[4])

def write_information(list,filename = 'odered_score.dat'):
    with open(filename,mode='w+') as f:
        for items in list:
            items[4] = str(items[4])
            items[5] = str(items[5])[0:6]
            f.write('-'.join(items)+'\n')

class save_information(object):
    def __init__(self, conn):
        self.conn = conn
        self.cur = conn.cursor()

    def save(self,list):
        for items in list:
            insert_sqli = "insert into information values('%s','%s','%s','%s','%s','%s');" %(items[0],items[1],items[2],items[3],items[4],items[5][0:6])
            print('insert语句:', insert_sqli)
            self.cur.execute(insert_sqli)

    def __del__(self):
        self.cur.close()
        self.conn.close()
if __name__ == '__main__':
    conn = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        db='studentinformation',
        charset='utf8',
        autocommit=True,  # 如果插入数据，， 是否自动提交? 和conn.commit()功能一致。
    )

    create_information()
    list=read_information()
    student = Student(list)
    student.calculation_points()
    student.high_points()
    new_list = student.sorte()
    write_information(new_list)
    save = save_information(conn)
    save.save(new_list)

openpyxl练习

1.

每一行代表一次单独的销售。列分别是销售产品的类型(A)、产品每磅的价格
(B)、销售的磅数©,以及这次销售的总收入。TOTAL 列设置为 Excel 公式,将每磅的成本乘以销售的磅数,
并将结果取整到分。有了这个公式,如果列 B 或 C 发生变化,TOTAL 列中的单元格将自动更新.

需要更新的价格如下:
Celery 1.19
Garlic 3.07
Lemon 1.27

现在假设 Garlic、 Celery 和 Lemons 的价格输入的不正确。这让你面对一项无聊
的任务:遍历这个电子表格中的几千行,更新所有 garlic、celery 和 lemon 行中每磅
的价格。你不能简单地对价格查找替换,因为可能有其他的产品价格一样,你不希
望错误地“更正”。对于几千行数据,手工操作可能要几小时。

import  openpyxl
def updatewb(wbname,sheetname = None):
    wb = openpyxl.load_workbook(filename=wbname)

    if not sheetname:
        sheet = wb.active
    else:
        sheet = wb[sheetname]
    i =0
    for row in sheet.rows:
        i+=1
        # print(i)
        cell = sheet.cell(row=i, column=1)
        if cell.value == 'Celery':
            # print(cell.value)
            cell = sheet.cell(row=i,column=2)
            cell.value = 1.19
            # print(cell.value)
        if cell.value == 'Garlic':
            cell = sheet.cell(row=i, column=2)
            cell.value = 3.07
        if cell.value == 'Lemon':
            cell = sheet.cell(row=i, column=2)
            cell.value = 1.27
    print('价格修改成功')
    wb.save('/home/kiosk/PycharmProjects/2018.11.17/py18/doc/produceSales.xlsx')
if __name__ == '__main__':
    updatewb('/home/kiosk/PycharmProjects/2018.11.17/py18/doc/produceSales.xlsx')

2.

1).获取doc里面的文件201901python学员博客整理情况.xlsx, 将作业评分为A的所有学员名称和连接获取;
2). 将获取的信息保存至Excel表格或者文件中;

import openpyxl


def create_to_ecael(wbname,data,sheetname='sheet1',):
    print('正在创建表格')

    wb = openpyxl.Workbook()
    # print(wb.active)
    sheet = wb.active
    sheet.title = sheetname

    print('正在写入数据')

    for row,item in enumerate(data):
        for column,cellvalue in enumerate(item):
            cell = sheet.cell(row=row+1,column= column+1,value = cellvalue)

    wb.save(wbname)
    print('保存成功')

def readwb(wbname,sheetname = None):
    wb = openpyxl.load_workbook(filename=wbname)

    if not sheetname:
        sheet = wb.active
    else:
        sheet = wb[sheetname]
    new_rowvalues = [['姓名','博客地址']]
    for row in sheet.rows:
        rowvalues = [cell.value for cell in row ]
        # print(rowvalues)
        # print(rowvalues[6])
        if rowvalues[6] == 'A':
            new_rowvalues.append([rowvalues[0],rowvalues[2]])
        # new_rowvalues=[[rowvalues[0],rowvalues[2]]for i in rowvalues if rowvalues[6] == 'A']
            # print(new_rowvalues)
    # goodsInfo.append(new_rowvalues)
    # print(new_rowvalues)
    # new_rowvalues = {rowvalue[0]:rowvalue[2] for rowvalue in goodsInfo if rowvalue[6] == 'A'}
    # print(new_rowvalues)
    # return goodsInfo
    return new_rowvalues
if __name__ == '__main__':

    goodsInfo = readwb('/home/kiosk/PycharmProjects/2018.11.17/py18/doc/201901python学员博客整理情况.xlsx')
    # print(goodsInfo,type(goodsInfo))
    create_to_ecael('/home/kiosk/PycharmProjects/2018.11.17/py18/doc/new_python学院博客整理情况.xlsx',goodsInfo,'作业评分为A的学生信息')

json模块，itchat模块

百度提供的获取天气的api url : http://api.map.baidu.com/telematics/v3/weather?location= xian &output=json&ak=TueGDhCvwI6fOrQnLM0qmXxY9N0OkOiQ&callback=?'
获取当前日期，及当日温馨提示，和未来几天的温度；
并通过微信发送给制定好友.

import json
import itchat
from urllib.request import urlopen

url = "http://api.map.baidu.com/telematics/v3/weather?location=%20xian%20&output=json&ak=TueGDhCvwI6fOrQnLM0qmXxY9N0OkOiQ&callback=?%27"
urlObj = urlopen(url)

# 服务端返回的页面信息, 此处为字符串类型
pageContent = urlObj.read().decode('utf-8')
# print(pageContent)
# print(type(pageContent))


dict_weather = json.loads(pageContent)
# print(dict_weather)
# print(type(dict_weather))
weather =("""
    当前日期：%s
    当前天气状况：%s
    风力：%s
    温度：%s
    当日温馨提示：1.%s
                2.%s
                3.%s
                4.%s
                5.%s
    pm2.5：%s
    未来几天温度:周五：%s
                周六：%s
                周天：%s

"""%(dict_weather['results'][0]['weather_data'][0]['date'],
     dict_weather['results'][0]['weather_data'][0]['weather'],
     dict_weather['results'][0]['weather_data'][0]['wind'],
     dict_weather['results'][0]['weather_data'][0]['temperature'],
     dict_weather['results'][0]['index'][0]['des'],
     dict_weather['results'][0]['index'][1]['des'],
     dict_weather['results'][0]['index'][2]['des'],
     dict_weather['results'][0]['index'][3]['des'],
     dict_weather['results'][0]['index'][4]['des'],
     dict_weather['results'][0]['pm25'],
     dict_weather['results'][0]['weather_data'][1]['temperature'],
     dict_weather['results'][0]['weather_data'][2]['temperature'],
     dict_weather['results'][0]['weather_data'][3]['temperature']))

#hotReload=True#,会保留登陆状态，在短时间内重新登陆不用
# 再次扫描二维码
itchat.auto_login()
# 给手机助手发送消息
res = itchat.search_friends('好友名称')
yy= res[0]['UserName']
itchat.send(weather,toUserName=yy)