解决pandas使用to_sql，传入sqlalchemy的create_engine后无法保存数据的问题

解决方法

调用连接后需要手动提交，将原有程序：

from sqlalchemy import create_engine

engine = create_engine("xxxxx")
_db_conn = engine.connect()
dataframe.to_sql("数据表", if_exists="append", con=_db_conn, index=False)
_db_conn.close()

改为：

from sqlalchemy import create_engine

engine = create_engine("xxxxx")
_db_conn = engine.connect()
dataframe.to_sql("数据表", if_exists="append", con=_db_conn, index=False)
_db_conn.commit() # !!!!! 添加这一行 !!!!!
_db_conn.close()

问题解析

新版本的sqlalchemy需要手动commit()一下即可

首先，获取一个engine:

import yaml
import os
from sqlalchemy import create_engine


def get_sql_engine():
    # 数据库
    parent = os.path.dirname(os.path.realpath(__file__))

    with open(os.path.join(parent, "config.yaml"), encoding="utf-8") as f:
        config = yaml.load(f.read(), Loader=yaml.FullLoader)
        mysql_config = {
            "db": config['mysql_config']['db'],
            "host": config['mysql_config']['host'],
            "user": config["mysql_config"]['username'],
            "password": config["mysql_config"]['password'],
            "port": config['mysql_config']['port'],
        }
    engine = create_engine(
        "mysql+pymysql://{}:{}@{}:{}/{}".format(mysql_config['user'], mysql_config['password'], mysql_config['host'],
                                                mysql_config['port'], mysql_config['db']))
    return engine

然后在保存的时候使用：

engine = create_engine()
_db_conn = engine.connect()
dataframe.to_sql("数据表", if_exists="append", con=_db_conn, index=False)
_db_conn.commit() # 添加这一行
_db_conn.close()