使用openpyxl 操作Excel文件导出导入数据

1. 把Excel文件的数据，导入进数据库

# coding=utf-8
import pymysql
from openpyxl import load_workbook
# 把Excel,格式是xlsx数据导入到数据库

def getdata(sqllist, dbname):  # 根据sql语句,执行sql
    conn = pymysql.connect(host='192.168.1.53', user='user', password='pwd', database=dbname,
                           port=3306, charset="utf8")
    cur = conn.cursor()
    for sql in sqllist:
        cur.execute(sql)
    conn.commit()
    results = cur.fetchall()
    cur.close()
    conn.close()
    return results


def importdata(filename, dbname, sql):  # 读取Excel文件,返回insert语句
    wb = load_workbook(filename)  # 加载Excel文件
    ws = wb.active  # 获取第1个sheet,或者用ws = wb.worksheets[0] 根据工作表索引获取工作表。ws=wb['courier'] 根据工作表名称获取sheet
    # cols = ws.max_column  # 获取sheet总列数
    # rows = ws.max_row  # 获取sheet总行数
    # for row in range(2, rows + 1):  # 从第2行开始获取每行数据
    #   print(ws[row])
    #   for i in ws[row]:
    #       print(i.value)

    sqllist = []
    for value in ws.values:  # 输出所有数据,value是tuple类型，ws.values 是generator生成器类型
        insertsql = sql + str(value)
        sqllist.append(insertsql)
    wb.close()  # 读完数据关闭Excel
    insert_to_db = getdata(sqllist, dbname)
    return


if __name__ == '__main__':
    filename = '/Users/yuz/Documents/pyfiles/task.xlsx'  # 文件位置
    dbname = 'dbname'  # 数据库名
    sql = "insert into kpi_data (member_id,member_name,team_id,leader_id,leader_name,city_id,city_name) values "
    importdata(filename, dbname ,sql)
    print("done")

2. 从数据库中，导出数据到Excel文件

import pymysql
from openpyxl import Workbook
# 从数据库中导出数据到Excel,格式是xlsx
def getdata(sql, dbname):  # 根据sql语句,获取数据库的数据
    conn = pymysql.connect(host='192.168.1.53', user='user', password='pwd', database=dbname,
                           port=3306, charset="utf8")
    cur = conn.cursor()
    cur.execute(sql)
    conn.commit()
    results = cur.fetchall()
    cur.close()
    conn.close()
    return results


def writedata(filename, dbname, sql):  # 把数据写入Excel文件
    wb = Workbook()  # 创建文件对象
    ws = wb.active  # 激活第1个sheet
    wstitle = ["trans_no", "user_id", "remark", "city"]  # 表头
    ws.append(wstitle)  # 先写表头
    datas = getdata(sql, dbname)  # 从库里取出数据
    for row in datas:  # 每次写入一行数据
        row = list(row)
        ws.append(row)
    wb.save(filename)  # 保存


if __name__ == '__main__':
    filename = '/Users/yuz/Documents/pyfiles/datas.xlsx'
    dbname = 'trade8'
    sql = "SELECT trans_no,user_id,remark,city FROM withdraw_trades where account_type in (1,2)"
    writedata(filename, dbname, sql)

3. 从数据库中导出数据到txt文件

# coding=utf-8
import pymysql

def getdata(sql, dbname):  # 根据sql语句,获取数据库的数据
    conn = pymysql.connect(host='192.168.1.53', user='user', password='pwd', database=dbname,
                           port=3306, charset="utf8")
    cur = conn.cursor()
    cur.execute(sql)
    conn.commit()
    results = cur.fetchall()
    cur.close()
    conn.close()
    return results


def file_edit(wr_str): #写入txt文件,文件不存在则创建.
    f1 = open(r'/Users/yuz/Documents/pyfiles/ids.txt', 'a')
    f1.write(wr_str)
    f1.close()


if __name__ == "__main__":
    sql = "SELECT user_id FROM `couriers` ORDER BY id ASC LIMIT 20"
    ids = getdata(sql,'co1')
    for i in range(0, len(ids)):
        file_edit(str(ids[i][0]) + "," + "\n")
    print("done")

4. 使用ssh方式连接数据库

# -*- coding:utf-8 -*-
import conf
import pymysql
from sshtunnel import SSHTunnelForwarder

def get_data(env,sql):  # 根据sql语句，获取数据库的数据
    '''
    返回的数据类型为嵌套tuple,长度是sql的查询数据条数
    (('第1条数据字段1的值','第1条字段2的值'), ('第2条数据字段1的值','第2条数据字段2的值')）
    '''
    dbhost = conf.dbconfigs[env]['host']
    uname = conf.dbconfigs[env]['user']
    passwd = conf.dbconfigs[env]['passwd']

    with SSHTunnelForwarder(
            ("192.168.1.12", 22),
            ssh_username="username",
            ssh_pkey="/Users/yuz/Desktop/id_rsa.dat",
            # ssh_private_key_password="secret",
            remote_bind_address=(dbhost, 3306),
            local_bind_address=('0.0.0.0', 10022)
    ) as tunnel:
        conn = pymysql.connect(host='127.0.0.1',
                               port=10022,
                               user=uname,
                               passwd=passwd)

        cur = conn.cursor()
        cur.execute(sql)
        results = cur.fetchall()
        cur.close()
        conn.close()
        return results