python基础____学习笔记19 (操作数据库---远程查询访问， 读写xlsx表格)

一、xlsx文件访问

python 提供了openpyxl来访问xlsx表格. Xlsx表格最重要的两个部分是工作簿workbook 和 worksheet。
首先需要创建工作簿操作对象

import openpyxl
wb = openpyxl.load_workbook('personinfoAll.xlsx')

工作簿提供了许多方式与属性，
属性:

• active ： 获取活跃的 Worksheet;
• read_only ：是否以 read_only 模式打开 Excel 文档；
• encoding ：文档的字符集编码；
• properties ：文档的元数据，如标题，创建者，创建日期等；
• worksheets ：以列表的形式返回所有的 Worksheet 

方法:

• get_sheet_names ：获取所有表格的名称；
• get_sheet_by_name ：通过表格名称获取 Worksheet 对象；
• get_active_sheet ：获取活跃的表格；
• remove_sheet ：删除一个表格；
• create_sheet ：创建一个空的表格；
• copy_worksheet ：在 Workbook 内拷贝表格 。

工作表提供了下面常用的属性及方法:
属性:

• title ： 表格的标题；
• dimensions 表格的大小 ， 这里的大小是指有含有数据的 表格大小.
• max_row 表格的最大行；
• min_row 表格的最小行；
• max_column 表格的最大列；
• min_column 表格的最小列；
• rows 按行获取单元格（Cell对象 ）；
• columns 按列获取单元格（Cell对象）；
• freeze_panes 冻结窗格；
• values 按行获取表格的内容（数据)。

方法:

• iter rows ：按行获取所有单元格（ Cell 对象）；
• iter_columns ：按列获取所有的单元格；
• append ：在表格末尾添加数据；
• merged_cells ：合并多个单元格；
• unmerge_cells ：移除合并的单元格 。

Cell 对象比较简单，其常用的属性如下：

row ：单元格所在的行；
column ：单元格所在的列；
value ：单元格的取值；
cordinate ：单元格的坐标 。

import openpyxl
wb = openpyxl.load_workbook('personinfoAll.xlsx')
curworksheet= wb.active
print('read_only:',wb.read_only)
print('encoding:',wb.encoding)
print('properties:',wb.properties)

for ws in wb.worksheets:
    print('title:',ws.title)
    print('dimensions:',ws.dimensions)
    print('max row:',ws.max_row,'max colunum:',ws.max_column)
    print('min row:', ws.max_row, 'min colunum:', ws.max_column)

    print('ws.rows:', ws.rows)
    print('ws.columns:', ws.columns)
    print('ws.values:', ws.values)

    #获取1-3行 所有的数据
    for cellrow in ws.iter_rows(min_row=1,max_row=3,min_col=1,max_col=8):
        for cell in cellrow:
            print(cell.value,'({}.{})'.format(cell.row, cell.column), end=' ')
        print('\n')

输出:

read_only: False
encoding: utf-8
properties: <openpyxl.packaging.core.DocumentProperties object>
Parameters:
creator='dukai', title=None, description=None, subject=None, identifier=None, language=None, created=datetime.datetime(2020, 9, 19, 1, 37, 50), modified=datetime.datetime(2020, 9, 19, 2, 27, 44), lastModifiedBy='dukai', category=None, contentStatus=None, version=None, revision=None, keywords=None, lastPrinted=None
title: personinfo1
dimensions: A1:H17
max row: 17 max colunum: 8
min row: 17 min colunum: 8
ws.rows: <generator object Worksheet._cells_by_row at 0x0000022FE8DC0C48>
ws.columns: <generator object Worksheet._cells_by_col at 0x0000022FE8DC0C48>
ws.values: <generator object Worksheet.values at 0x0000022FE8DC0C48>
name (1.1) age (1.2) gender (1.3) height (1.4) city (1.5) addr (1.6) personId (1.7) proabb (1.8) 

xiaozhang (2.1) 20 (2.2) 男 (2.3) 172 (2.4) 成都 (2.5) 成都高新 (2.6) 5.10131198605136e+17 (2.7) 3 (2.8) 

xiaozhang (3.1) 20 (3.2) 男 (3.3) 172 (3.4) 成都 (3.5) 成都高新 (3.6) 5.10131198605136e+17 (3.7) 3 (3.8) 

属性 row 、colunum、values 都是生成器 ，避免数量太大时 取数据耗内存。 columns 与 rows 返回的是 Cell 对象， values 返回的是数据
    freeze_panes 这个参数比较特别，主要用于在表格较大时冻结顶部的行或左边的列 。对于冻结的行或列，就算用户滚动电子表格，也是始终可见的 。 每个 Worksheet 对象都有一个 freeze_panes 属性，可以设置为一个 Cell 对象或一个单元格坐标的字符串，单元格上面的行和左边的列将会冻结（注意单元格所在的行和列并不会冻结）。例如，我们需要冻结第一行，那么 freeze_panes 取值应该为 A2 ，如果要冻结第一列， freeze_panes 取值为 Bl 。 如果要同时冻结第一行和第一列，则 freeze_panes 取值为 B2 。 freeze_panes 取值为 None 表示不冻结任何窗格 。

例： 将本地xlsx文件中的所有数据插入进数据库 personInfo 的表  personinfo中, 主体代码已经在上一节中实现。

import openpyxl

import click
import pymysql
import contextlib
import collections


@contextlib.contextmanager
def myConnectionContextManager(**kwargs):
    try:
        conn = pymysql.connect(host=kwargs.get('serverip'), port=kwargs.get('serverport'), user=kwargs.get('user'),
                               password=kwargs.get('passwd'), database='personInfo', charset='utf8')
        yield conn
    except Exception as  error:
        print(error)
    finally:
        if conn:
            conn.close()


@click.command()
@click.option('--serverip', default='192.168.2.172', required=False, type=str, prompt='Input remote server ip')
@click.option('--serverport', required=False, type=int, default=3306, prompt='Input remote server port')
@click.option('--user', default='kaikai', required=False, type=str, prompt='Input login user name')
@click.option('--passwd', default='654321', required=False, type=str, prompt='Input login user passwd')
def main(serverip, serverport, user, passwd):
    with myConnectionContextManager(serverip=serverip, serverport=serverport, user=user, passwd=passwd) as conn:
        cursor = conn.cursor()
        cursor.execute("select * from personinfo;")
        for result in cursor.fetchall():
            print(result)
        insertdataFromXls(cursor, 'personinfoAll.xlsx')
        conn.commit()
        # 获取远程所有数据并保存至新的文件


# 返回排除某个字段的SQL select 语句
def excludeSomefieldSQL(cursor, databaseName, tableName, *excludefieldNames):
    mysql_format = f""" SELECT CONCAT(' select ',GROUP_CONCAT(COLUMN_NAME),' from ', TABLE_NAME,' ;') FROM information_schema.COLUMNS WHERE  TABLE_SCHEMA = '{databaseName}' And table_name = '{tableName}' """
    if len(excludefieldNames) > 0:
        for excludefieldName in excludefieldNames:
            mysql_format += f''' and COLUMN_NAME != '{excludefieldName}' '''
    cursor.execute(mysql_format + ';')
    # 返回值是  (' select name,age,gender,height,city,addr,personId,proabb from personinfo ;',) 元组
    return cursor.fetchone()


# 返回所有的字段名 不包含某些字段excludefieldNames
def getALLfieldname(cursor, databaseName, tableName, *excludefieldNames):
    mysqlformat = f''' select COLUMN_NAME from information_schema.COLUMNS where table_schema = '{databaseName}' and table_name = '{tableName}'; '''
    cursor.execute(mysqlformat)
    fieldnames = []
    for field in cursor.fetchall():
        if field[0] not in excludefieldNames:
            fieldnames.append(field[0])
    return fieldnames;


# 将元组数据 转换真正写入的数据，字符串打引号
def convertTuple2RealData(onerow, fieldnames):
    ROW = collections.namedtuple('INSERTDATA', fieldnames)
    t = ROW(*onerow)
    datalist = []
    datalist.append(str(t.name))
    datalist.append(t.age)
    datalist.append(str(t.gender))
    datalist.append(t.height)
    datalist.append(str(t.city))
    datalist.append(str(t.addr))
    datalist.append(str(t.personId))
    datalist.append(str(t.proabb))
    return datalist


def insertValue(mycursor, item):
    try:
        mysql = f'''insert into personinfo value(0,'{item.name}',{item.age},'{item.gender}',{item.height},'{item.city}','{item.addr}','{item.personId}',{item.proabb})'''
        mycursor.execute(mysql)
    except Exception as err:
        print('insert error:', err)



def insertdataFromXls(cursor, filename):
    wb = openpyxl.load_workbook(filename)
    print('read_only:', wb.read_only)
    print('encoding:', wb.encoding)
    print('properties:', wb.properties)
    allfieldname = getALLfieldname(cursor, 'personInfo', 'personinfo', 'id')
    for ws in wb.worksheets:
        print('title:', ws.title)
        print('dimensions:', ws.dimensions)
        print('max row:', ws.max_row, 'max colunum:', ws.max_column)
        print('min row:', ws.min_row, 'min colunum:', ws.min_column)

        print('ws.rows:', ws.rows)
        print('ws.columns:', ws.columns)
        print('ws.values:', ws.values)
        # 获取1-3行 所有的数据
        for cellrow in ws.iter_rows(min_row=ws.min_row + 1, max_row=ws.max_row, min_col=ws.min_column,
                                    max_col=ws.max_column):
            rowvalue = [x.value for x in cellrow]
            ROW = collections.namedtuple('INSERTDATA', allfieldname)
            t = ROW(*rowvalue)
            insertValue(cursor, t)
    wb.close()
if __name__ == '__main__':
    main()

现在我将服务器中数据下载下来保存到xlsx中间中
首先需要导入workbook

from openpyxl import Workbook
wb =Workbook()

创建worksheet

wb.create_sheet(index= O, title='personinfo')

def querydataAndsave(cursor, filename):

    wb = Workbook()
    ws=wb.create_sheet(index=0, title='personinfo')
    allfieldname = getALLfieldname(cursor, 'personInfo', 'personinfo', 'id')
    # mysqlFormat="select name,age,gender,height,city,addr,personId,proabb from personinfo;"
    mysqlFormat = excludeSomefieldSQL(cursor, 'personInfo', 'personinfo', 'id')
    cursor.execute(mysqlFormat[0])
    for onerow in cursor.fetchall():
        print(onerow)
        ws.append(onerow)
    wb.save(filename)

 

储存数据方式
    # 方式一：数据可以直接分配到单元格中(可以输入公式)
    ws['A1'] = 42
    # 方式二：可以附加行，从第一列开始附加(从最下方空白处，最左开始)(可以输入多行)
    ws.append([1, 2, 3])

单一单元格访问
    方法1:
    c = ws['A4']
    # 方法二：row 行；column 列
    d = ws.cell(row=4, column=2, value=10)

多单元格访问
    cell_range = ws['A1':'C2']
    # 通过行(列)
    colC = ws['C']
    col_range = ws['C:D']
    row10 = ws[10]
    row_range = ws[5:10]
    # 通过指定范围(行 → 行)
    for row in  ws.iter_rows(min_row=1, max_col=3, max_row=2):
            for cell in  row:
                print(cell)