使用sqlacodegen 单数据入库 快捷

安装

pip install sqlacodegen

根据mysql数据表格 生成入库class 文件

sqlacodegen.exe --tables 表名 --outfile ./a.py mysql+pymysql://kf_user:kfxiyutest@服务器地址/Xbrl?charset=utf8

文件下载至a.py 查看复制入python project

$ cat a.py
# coding: utf-8
from sqlalchemy import Column, DateTime, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class CJZQSHDPRDATAEIAXBRL(Base):
    __tablename__ = ‘CJZQ_SH_DPR_DATA_EIA_XBRL‘
    __table_args__ = {‘comment‘: ‘DPR_DATA_EIA‘}

    GUID = Column(String(36), primary_key=True, comment=‘记录编码‘)
    DIQU = Column(String(30), comment=‘地区‘)
    TONGJI_SJ = Column(String(30), comment=‘统计时间‘)
    RigCount = Column(String(30), comment=‘钻井数量‘)
......

Python 文件import

import os
import sys

proj_dir = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))
sys.path.append(proj_dir) #config 路径
from ii.common.common import config

from sqlalchemy import create_engine, text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, VARCHAR, DATETIME, DECIMAL, String, DateTime
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

单条插入model引擎

def init_engine():
    engine = create_engine(
        "mysql+pymysql://{}:{}@{}:{}/{}".format(
            config[‘mysql‘][‘user‘], config[‘mysql‘][‘pwd‘],
            config[‘mysql‘][‘host‘], config[‘mysql‘][‘port‘], config[‘mysql‘][‘db‘]),
        encoding=config[‘mysql‘][‘charset‘], echo=True)
    Base.metadata.create_all(engine)
    return engine


def insert_data_to_db(table_name, data_dict):
    session = sessionmaker(bind=init_engine())()
    data_obj = globals()[table_name](**data_dict)
    session.add(data_obj)
    session.commit()
    session.close()

Python 文件中运行指令

insert_data_to_db(‘mysql 表名‘, datadictionary)

Python MySQL单条数据快速入库方法（使用 sqlacodegen）