pandas转换数据Excel、DataFrame、MySQL

pandas转换数据Excel、DataFrame、MySQL

网上搜了许久[pandas如何读取Excel为嵌套字典的列表]等等问题，做下笔记

Excel转list[map]

如：存在Excel表student

id	name	age	address
1001	zhangsan	15	Beijing
1002	lisi	16	Shanghai
1003	wangwu	17	Nanjing

需要将其转为python处理格式：

import pandas as pd

def read_excel_as_dict(file_name, sheet_name):
    df = pd.read_excel(io=file_name, sheet_name=sheet_name)
    head_list = list(df.columns)  # 获取数据列名
    list_dic = []  # 列表
    for line in df.values:
        aline = dict(zip(head_list, line))  # 字典
        list_dic.append(aline)
        return list_dic  # list[map]

转换效果如下：

[{"id":1001, "name": "zhangsan", "age": 15, "address": "Beijing"},
{"id":1002, "name": "lisi", "age": 16, "address": "Beijing"},
{"id":1003, "name": "wangwu", "age": 17, "address": "Beijing"}]

list[map]转Excel

如上，需要转成Excel

import pandas as pd

def py_to_excel(py_list, excel_name, sheet_name):
    pd.DataFrame(py_list).to_excel(excel_writer=excel_name, sheet_name=sheet_name, index=False)

MySQL读取数据为list[map]

如上，需要转成list[map]

import pymysql


# 读取db数据为list[map]
class DataBaseMySQL:
    def __init__(self, db_info):
        self.conn = pymysql.connect(host=db_info['host'], port=db_info['port'], user=db_info['user'],
                                    password=db_info['password'], db=db_info['dbname'],
                                    charset='utf8', autocommit=True)
        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)  # 读取为列表+字典格式

    def query(self, query_string):
        self.cursor.execute(query_string)
        return self.cursor.fetchall()

    def __del__(self):
        self.cursor.close()
        self.conn.close()

list[map]存入数据库MySQL

from sqlalchemy import create_engine
import pandas as pd


engine = create_engine('mysql://scott:tiger@localhost/test')

def py_to_sql(py_list, excel_name, sheet_name):
    pd.DataFrame(py_list).to_sql(name=table_name, con=engine, if_exists="append")

Excel导入数据库MySQL

#!/usr/bin/env python3
# -*- coding:utf-8 -*-
import pandas as pd
import sqlalchemy


# 将excel数据导入db
class Excel:
    @staticmethod
    def database_engine(db_info):
        """
        database_info = {
            "host": "192.168.109.128",
            "user": "root",
            "password": "123456",
            "port": "3306",
            "dbname": "test",
            "dbtype": "mysql",
            "dbengine": "pymysql"
        }
        """
        try:
            SQLALCHEMY_DATABASE_URI = '{}+{}://{}:{}@{}:{}/{}?charset=utf8'.format(
                db_info["dbtype"], db_info["dbengine"], db_info["user"], db_info["password"],
                db_info["host"], db_info["port"], db_info["dbname"]
            )
            sql_engine = sqlalchemy.create_engine(SQLALCHEMY_DATABASE_URI)
        except Exception as e:
            raise Exception("connect to sql failed:", e)
        return sql_engine

    @staticmethod
    def database_write(sql_engine, excel_path, sheet_name, db_table_name):
        """
        :param sql_engine: 需要导入的数据库连接
        :param excel_path: 需要导入sql的excel全路径
        :param sheet_name: excel的sheet页
        :param db_table_name: 需要导入sql的数据库表名称
        """
        try:
            df = pd.read_excel(io=excel_path, sheet_name=sheet_name)
        except Exception as e:
            raise Exception("read excel failed:", e)

        try:
            df.to_sql(db_table_name, sql_engine, index=False, if_exists="append")
        except Exception as e:
            raise Exception("write sql failed:", e)