Python - 读Excel数据落库 - 手把手教学

背景：

• 有一批数据业务只能线excel的方式给
• excel与数据表结构不一致

当然可以直接通过navicat导入excel数据,but、、python也得用起来呀
let us go ~

准备：

• 编辑器 - 写代码得有开发工具啊，pyCharm也行、本文idea 2020.3
• python 解释器 - 直接通过idea插件方式安装即可
• 导包 - 需要啥导啥

步骤：

• 1 idea 安装python 插件

File -> Settings -> plugins -> Marketplace
输入 python -> install 安装完重启idea

• 2、创建python项目

File -> New -> Project…
此时发现左侧已经有Python选项了，
Project SDK默认我们刚安装的Python最新版本，自己手动安装在本地的可以通过Add Python SDK 选择
然后一路下一步就可以了

• 3、 环境验证

新建个文件（后缀为.py）
随便写点啥，打印一下
打印成功、环境已经准备完毕

• 4、 建包、搭架子

我们的任务大致分为两步
1、读excel
2、写DB

读 Excel:

• 新建一个module (概念同idea一致)
• 安装openpyxl - 我们通过openpyxl处理excel

打开终端
输入 pip install openpyxl，回撤
会有下载进度条提示，（我是先卸载了，重新安装的）

• 读excel demo
  

from openpyxl import load_workbook

# 读当前目录下excel
excel = load_workbook('./panda.xlsx')

# ['Sheet1', 'Sheet2', 'Sheet3']
tables = excel.sheetnames

# 表头
table = excel[tables[0]]

# 列数
columns = table.max_column

# 行数
rows = table.max_row

# 逐行读excel
for i in range(1, rows+1):
    # 行数组
    row = []

    # 第一列
    column1 = table.cell(row=i, column=1).value
    row.append(column1)

    # 第二列
    column2 = table.cell(row=i, column=2).value
    row.append(column2)

    # 第三列
    column3 = table.cell(row=i, column=3).value
    row.append(column3)

    # 打印
    print(row)

执行结果：说明excel数据已经成功读取到了

写DB:

• 安装pymysql
• 连接数据库
• 执行sql

安装pymysql: 同上 pip install pymysql

连接数据库：新建一个配置文件dbconfig.py

import pymysql
# 连接db
conn = pymysql.connect(
    host="10.10.10.10",
    port=3306,
    user="panda",
    password="panda",
    database="panda",
    charset="utf8")

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = conn.cursor()

验证DB连接：新建与DB 交互文件

from panda import dbconfig

# 获取连接
conn = dbconfig.conn
# 获取游标
cursor = dbconfig.cursor

# 使用 execute()  方法执行 SQL 查询
cursor.execute("SELECT * from panda_test")
# 使用fetchone 获取一条数据
result = cursor.fetchone()

# 打印结果
print(result)

# 关闭游标
cursor.close()
# 关闭数据库链接
conn.close()

执行结果：db连接成功

读excel + 写库：

步骤：

• 连接数据库配置
• 落库方法定义
• 处理excel方法

目录结构：

• 连接数据库配置
• 落库方法定义
• 处理excel方法

dbconfig.py

import pymysql
# 连接db
conn = pymysql.connect(
    host="10.10.10.10",
    port=3306,
    user="panda",
    password="panda",
    database="panda",
    charset="utf8")

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = conn.cursor()

• 连接数据库配置
• 落库方法定义
• 处理excel方法

db.py

from config import dbconfig
# 获取连接
conn = dbconfig.conn
# 获取游标
cursor = dbconfig.cursor
def insertData(data):
    # 表名
    table = 'panda_test'
    # 列名
    keys = ', '.join(data.keys())
    # 数据
    values = ', '.join(['%s'] * len(data))
    try:
        print(tuple(data.values()))
        # 组装sql
        sql = "INSERT INTO {table}({keys}) VALUES ({values})".format(table=table, keys=keys, values=values)
        # 执行sql
        cursor.execute(sql, tuple(data.values()))
        # 提交事务
        conn.commit()
    # 捕获异常
    except Exception as e:
        # 打印异常
        print("error..", e)
        # 回滚
        conn.rollback()

• 连接数据库配置
• 落库方法定义
• 处理excel方法

panda.py

from openpyxl import load_workbook
from db import db

# 获取连接
conn = db.conn
# 获取游标
cursor = db.cursor
def dealexcel():
    # 读当前目录下excel
    excel = load_workbook('../excel/panda.xlsx')
    # ['Sheet1', 'Sheet2', 'Sheet3']
    tables = excel.sheetnames
    # 表头
    table = excel[tables[0]]
    # 行数
    rows = table.max_row
    # 逐行读excel
    for i in range(1, rows+1):
        # 行数组
        row = []
        # 第一列
        column1 = table.cell(row=i, column=1).value
        row.append(column1)
        # 第二列
        column2 = table.cell(row=i, column=2).value
        row.append(column2)
        # 第三列
        column3 = table.cell(row=i, column=3).value
        row.append(column3)
        # 打印行
        print(row)
        # 组装数据
        data = {
            'name': str(column1),
            'cname': str(column2),
            'age': str(column3)
        }
        # 落库
        db.insertData(data)
dealexcel()

# 关闭游标
cursor.close()
# 关闭连接
conn.close()

执行结果：

怕无归期~~ 怕空欢喜~~
怕来的不是你~~ 怕没有奇迹~~