Python: 使用 ORM 向 MySQL 数据库写入数据

本文根据模型端的上线经验，简要介绍 Python 语言使用 ORM 向 MySQL 数据库写入数据的方式。
经验有限，仅作为笔记和代码分享。

调用第三方库：

from sqlalchemy import Column, Integer, BIGINT, SMALLINT, Boolean, String, DateTime, null
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

数据库连接：

engine = None
DBSession = sessionmaker(bind=engine)
session = DBSession()

def init_engine():
    global engine
    engine_str = "mysql+pymysql://{0}:{1}@{2}:{3}/{4}?charset=utf8" \
        .format(DB_USER, DB_PASSWORD, DB_HOST, DB_PORT, DB_NAME)
    engine = create_engine(engine_str, echo=False)
    return engine

ORM 对象：

table_name = "table_name"  # 输出表的表名

Base = declarative_base()

class ORMWrite(Base):
    __tablename__ = table_name

    id = Column(BIGINT, primary_key=True, nullable=False, autoincrement=True)
    field_str = Column(String(64), nullable=False)
    field_int = Column(Integer, default=null, nullable=True)
    field_small_int = Column(SMALLINT, default=null)
    create_time = Column(DateTime, default=null)
    update_time = Column(DateTime, default=null)

    comment = dict(field_str="字段_str",
                   field_int="字段_int",
                   field_small_int="字段_small_int",
                   create_time="创建时间",
                   update_time="更新时间")

向数据库写入数据（主程序）：

import pandas as pd

# 数据库连接
engine = init_engine()

# 写入数据
df_result.rename(dict([(v, k) for k, v in ORMWrite.comment.items()]), inplace=True, axis=1)
records = df_result.to_dict(orient='record')
self.engine.execute(ORMWrite.__table__.insert(), records)