Mysql,Excel练习

***Mysql：将图片写入数据库，读取出来并保存***

import os

import pymysql

#提取图片
def search_imginfo(dir):
    imgInfo = []
    lis = [file for file in os.listdir(dir) if file.endswith(('.jpg', '.png'))]
    for i in lis:
        with open(dir + i, 'rb') as f:
            imgInfo.append([i, f.read()])
    return imgInfo


conn = pymysql.connect(host='localhost', user='root', passwd='wl1009', db='wanglang', charset='utf8')
cur = conn.cursor()
#写入图片文件
try:
    insert_info = 'insert into images (imgName,imgData) values(%s,%s)'
    cur.executemany(insert_info, search_imginfo('/root/Desktop/img/'))
    print('图片添加完成...')
except Exception as e:
    print(e)
#读取图片文件
try:
    reaf_sql = 'select * from images'
    cur.execute(reaf_sql)
    for img in cur.fetchall():
        with open('/root/Desktop/img111/%s' % img[1], 'wb') as f:
            f.write(img[2])
    print('图片读取成功...')

except Exception as e:
    print(e)
finally:
    conn.commit()
    cur.close()
    conn.close()

Excel：
目标:
    编写一个脚本,从人口普查电子表格文件中读取数据,并在几秒钟内计算出每个县的统计值。


步骤:
    从 Excel 电子表格中读取数据。
    计算每个县中普查区的数目。
    计算每个县的总人口。
    打印结果。

import openpyxl


def refer():
    wb = openpyxl.load_workbook('/root/Desktop/day14/excelDemo/P.xlsx')
    sheet = wb.active
    dic = {'AL':{'Autauga':[0,0]}}

    for row in sheet.rows:
        if [cell.value for cell in row][1] not in dic:

            dic[[cell.value for cell in row][1]] = {}
        else:
            if [cell.value for cell in row][2] not in dic[[cell.value for cell in row][1]]:
                dic[[cell.value for cell in row][1]][[cell.value for cell in row][2]]=\
                    [[cell.value for cell in row][3], 1]
            else:
                dic[[cell.value for cell in row][1]][[cell.value for cell in row][2]][0] += \
                    [cell.value for cell in row][3]
                dic[[cell.value for cell in row][1]][[cell.value for cell in row][2]][1] += 1
    
    while 1:
        State= input('区县所在州：')
        County=input('区县:')
        try:
            print( '%s州%s县的总人数：%s 有%s个人口普查区'%(State,County,dic[State][County][0],dic[State][County][1]))
        except Exception as e:
            print(e)




refer()